Cultural Learnings of PostgreSQL for Make Benefit Glorious�Company Product

Amir More - Founder & CEO @ dubhub.io�PyWebIL 12/09/2022

Background

• For a beginner-intermediate developers already working with Postgresql�
• No background with databases =�
• Database expert =�
• Lots of experience with databases =�
• Love NoSQL & Hate SQL =

Postgres for Programmers

• Part 1: Postgres in Theory and Practice�
• Part 2: Introduction to Database Optimization for Programmers�
• Part 3: Database Optimization - Indexes and Schemas�
• Part 4: Advanced Database Optimization - Queries

Previously at PyWebIL

How would you solve transactions?

• Start simple: Single process & thread
• Two files: Data and Commit “Log”/Journal
• Write set of changes to the Data and commit to them by writing the number to the log

Data

Committed Number

​

How would you add:

• Deletes?
• Multiple readers?
• Multiple writers?

What’s the problem with this architecture?

MVCC in Postgresql

• Multi-Version Concurrency Control - store multiple required versions of data
• Provides isolation in most databases
• Remember our example
• Advantage: Allows for all isolation levels
• Disadvantage: Space and management -> autovacuum

Accessing Relational Data - SQL

Structured Query Language (SQL): The standard method of interacting with a relational database. Standardized by ANSI and ISO since the 1980s

Declarative - Say what you want, let an executor figure out how (given constraints)

select * from emp inner join dept on emp.dept_id = dept.id

Want all results, have ~10k row memory

Today

• Why we need to “optimize”�
• Some Examples�
• A Framework for App/DB Optimization�
• The Optimizer�
• Live Examples�
• Row Level Security Gotchas

The Problem

• Junior Developer Memes: Coffee + Tasks = Code

Translation: Take an idea and make it work for 1 data and on your PC

Result: data written “literally” into database�

• All good until the model is full of data

Outcome #1 - Reading data is slow (Developer: Mistakes were made)

<starts adding indices>

Outcome #2 - Writing data is slow

The Real Problem

The Real Problem

When do we “optimize”?

• Queries take too long to return�- and/or -�Too many computational resources (CPU, memory, i/o)�
• Data insertion/updating/deletion takes too long�… and/or too many resources�
• Space becomes an issue�
• (sometimes) hard to change code due to legacy or 3rd party�
• (sometimes) Mistakes. Were. Made.

Trivial Example: Add an Index

Data: NASA Cassini Mission used by bigmachine.io�

�

​

​

Slow Query:�SELECT * FROM master_plan WHERE start_time_utc = $1

​

CREATE INDEX idx ON master_plan (start_time_utc);

“Advanced” Example: Add a Covering Index

�

​

​

select start_time_utc from master_plan�where team = $1 and target = $2�order by start_time_utc desc limit $3

CREATE INDEX cover_idx ON master_plan�(team, target, start_time_utc desc);

App/DB Optimization Framework

DB Optimization Mental Framework

• Changing your position in the tradeoff of resources, flexibility, and goals�Typically Trading Time vs Time and/or Time vs Space�����
• Examples:
• Fast Write & Slow Read -> Slow Write & Fast Read (Index)
• Less Memory & Lots of I/O -> More Memory & Less I/O (Larger Cache)
• More Space on Disk & Lots of CPU & IO ->�Less Space on Disk & Less Computation on Read (Pre-computed / Materialized View)

Insertion Time

Retrieval�Time

Add Index

Disk & CPU

Retrieval Time

Precompute Query

The Query Execution Pipeline

Optimize This

Optimizer Step

The (Reduced) Hierarchy of Computation

• Let n = number of elements
• O(1) - sweet!�
• O(logn) - pshh… not bad�
• O(n) - mmm okay�
• O(nlogn) - umm, guys?�
• O(n^p) - Oh No�
• O(2ⁿ) - Oh please no make it stop

Hash Lookup

Index (B-Tree) Lookup

Table Scan

Sort

The DB’s Toolbox - Query Execution Methods

Access Methods

• Sequential Scan�O(n) Read / “O(1) Write”�Pro: Always Possible�Con: Expensive in I/O and Time
• Index Scan�O(logn) Read / O(logn) Write�Pro: Fast�Con: Doesn’t Always Exist, Needs Table
• … there are others

Computations

• Sort�O(nlogn) Read, Write N/A in PG

Join Methods

• Nested Loop�For i in 1...n; For j in 1..m�O(n*m)�Pro: Can use index on inner table & No Mem�*Best when there’s an index on inner table
• Hash Join�O(1) for lookup but�O(m) space in memory�O(m) time to build (usually with Seq. Scan)
• Merge Sort�O(nlogn) + O(mlogm) time and space�Pro: Can spill to disk

The Optimizer

• Receives a Set of Paths = ways to use the toolbox to run the query

Chooses one path over all others, keeping in mind their cost

*Cost is an estimation; infeasible to know the true cost in advance

**You can’t even estimate all possibilities (General case is NP-Complete!)

• Question - How would you estimate the cost of an access method?
• What about joins? Join Order?
• Cheat code: Get some statistics about the data before hand!
• Compute on a subset of options. Prune options (like a chess engine)

ANALYZE and CREATE STATISTIC

• ANALYZE <table> gathers counts and histograms per column�Runs automatically in the background with autovacuum�
• CREATE STATISTIC <…> gathers histograms on subsets of columns�How can it help?�Bonus: Why doesn’t it run automatically?�
• Data stored in pg_statistic�User-friendly query with pg_stats

Optimizer Plans & Explain

• The result of the optimizer can be shown with the EXPLAIN prefix:
• Explain Select …;*
• Result is a tree of operations�Data flows from the innermost “leaves” up to the top�
• Example:�explain select * from master_plan m where m.start_time_utc = ‘2005-02-19 08:36:00.000’�Gather (cost=1000.00..317163.15 rows=861 width=400)� Workers Planned: 2� -> Parallel Seq Scan on master_plan (cost=0.00..316077.05 rows=359 width=400)� Filter: (start_time_utc = 2005-02-19 08:36:00.000)���*Can actually be used for any query i.e. update/delete/insert etc.

When Things Go Wrong

Live DB Examples

Postgresql Visibility Map

Per table, two bits per page (8KB)

One of the bits = all tuples visible to�current transaction

Updated by VACUUM

.. which runs automatically

https://www.interdb.jp/pg/pgsql06.html

VACUUM

INSERTS

Index Only Scan & Vis. Map

“PostgreSQL has to check the visibility�of the tuples in principle, and the index�tuples do not have any information�about transactions … Therefore,�PostgreSQL has to access the table data�to check the visibility of the data in the�index tuples.”

https://www.interdb.jp/pg/pgsql07.html#_7.2.

Row Level Security

https://pganalyze.com/blog/postgres-row-level-security-django-python

Tools for Mitigation - Next Time

• Database:�Verify statistics - Explain w/ ANALYZE/Create Statistic�Optimizer hints�Create Indices�Pre-compute - Materialized View or Manually�Change Model Design - Risky�Query Tuning (Part 3)�Temporary Tables, Partitioning, Table Functions, Sampling … (also in Part 3)
• Out-of-the-box Solutions:�Moare Hardware! (or switch slow & cheap for fast & expensive)�Switch DB Software - MongoDB for documents, IMDB for High TPS� Analytics DB - Redshift, Vertica, Google BigTable�

Questions?

Shameless Plug

dubhub.io�local database copies made simple and easy