Getting Started

@JuanitoFatas

The database was originally called Ingres, name Postgres is coming from "Post-Ingres". Read more: A Brief History of PostgreSQL.

As of 2020 Dec 25th: PostgreSQL 13.1, 12.5, 11.10, 10.15, 9.6.20, and 9.5.24 released. Read more: PostgreSQL-releases

The Can I use X for PostgreSQL: Feature Matrix.

List of PostgreSQL glossary

Postgres runs on an instance (computer), say EC2 instance.

Data stored in Postgres = Database. A Postgres can have many databases. This is called database cluster.

Arrays
Range Types
Autocompress for Text > 2000-char long

Data types JSON, ENUM, UUID, CIDR, MACADDR
SET parameters
Asynchronous Commit
(Sub)Partitioning
Transactional DDL
CREATE INDEX CONCURRENTLY, INSERT ... ON CONFLICT DO UPDATE
Row-level security
Logical Replication
SCRAM Auth
Multi-column statistics
Covering Indexes
Hot Backup (logical and physical)
Point in Time Recovery
LATERAL JOIN

Full Text Search
Snowball text parser, GIN - Inverted Search index, or GIST - high concurrency.
GIS (Geographical Information System)

They roughly mean the same thing:

"page" in disk space. Defaults to 8 kB. Minimum unit to read/write data. Can be changed up to 32kB but no one does that.
"buffer" in memory
Relation = Table, Index
TOAST — A row must fit in a page. To store something larger than a page. Postgres uses The Oversized Attributes Storage Technique, TOAST, to store row longer than a page.
Tuple = Row
Project(ion) = Select
XID = transaction ID, 32-bit integer = 4096M XIDs = 4 billion XIDs

OID = Unique Object ID (of row, table, database), hidden by default

SELECT oid, relname
FROM pg_class
where relname = 'table';

Once you got the oid, you can find all the locks by process from that table

SELECT locktype, database, pid, relation, mode
FROM pg_locks
WHERE relation = 123456;

OLTP On-Line Transaction Processing
OLAP On-Line Analytical Processing
Free Space Map — FSM, stores info about free space of each page of the table or index file.
FSM file name is something like 12345_fsm
Visibility Map — VM, Each table has this, to tell you the visibility of each page in this table, so you know if a page is live/dead tuple. Vacuum process asks VM to skip process a page that does not have dead tuples!
VM looks like [0, 0, 1, 0] and 1 indicates the page it points at has no dead tuples. VM file name is something like 12345_vm
```
SELECT size
FROM pg_stat_file('/usr/local/pgsql/data/base/1234/12345_vm');
```
Index has no visibility map.
XID
Transaction IDs. They are limited and must be reused. VACUUM removes references to old XIDs so that we can reuse them to not run out of XIDs. Run out of XID will be fatal data loss. VACUUM cannot remove reference of an old XID when it’s still in use.
DML—Data Manipulation Language
You can view FSM, VM files on /opt/homebrew/var/postgres/base on macOS

Their names are generic but actually from Postgres. Postgres called them "app", Postgres Client Applications.

createdb
dropdb
createuser
dropuser
clusterdb
reindexdb
vacuumdb
vacuumlo
pg_dump
pg_dumpall
pg_basebackup
pgbench
pg_config

The interface to use SQL to interact with Postgres database.

psql [DBNAME [USERNAME]]
psql -h -p  connect to specific host and port
psql -c "SELECT 1 FROM users;"

\q to quit
\l
\db
\d table
\dt+ table
\sf func — Show function definition
\x, \x auto
\?
\h CREATE TABLE
\timing
\watch

CREATE ROLE
CREATE TABLESPACE
CREATE DATABASE

True is one of TRUE, t, true, y, yes, on, 1.
False is one of FALSE, f, false, n, no, off, 0.

VACUUM — VERY IMPORTANT!
Microvacuum — On-the-fly deletion (remove dead index from B-tree pages)
VACUUM in Memory
Typical queries runtime
ACID of Transaction
- Atomic—Do all or nothing
- Consistent—Database transfer from a consistent state to another consistent state
- Isolated—All transactions should not affect each other
- Durable—Changes made should stay in database, even when database has issue(s).
MVCC Multi-Version Concurrency Control (No block locking)
- dead tuple — When updated a row, a new version of row is created and inserted into the table. Old version of the row is marked as invisible for future transactions (not physically removed), then it becomes dead tuple. Read more
SELECT ... FROM LATERAL (Aggregate functions are not supported)

EXPLAIN see Postgres EXPLAIN Visualizer

EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON)
SELECT * FROM POSTS

Common Table Expressions (CTE)

WITH valid_users as (
  SELECT id FROM users WHERE valid = 1
)
SELECT * FROM valid_users

Transaction
```
BEGIN;
...
END/COMMIT/ROLLBACK;
```
Subquery v.s. WITH
Bulk export with COPY
COPY to import data fast (but no replace yet as PostgreSQL 12)
RETURNING to decide what fields to return
Upsert: INSERT INTO ... ON CONFLICT UPDATE
Random Row
TABLESAMPLE (SYSTEM, BERNOULLI) over ORDER BY random() for sampling data
tsm_system_rows probably the best for the job.

MAX by OVER

SELECT first_value(field) OVER ( PARTITION BY table ORDER BY field),
       *
FROM table

LATERAL JOIN Official doc
Reuse computations after FROM (v.s. Subquery)
Calculate similarity: fuzzystrmatch or pg_similarity
Selective aggregates
tsvector & tsquery types are important for Full Text Search
COALESCE to remove NULL
Window Functions
Managing Freezing in PostgreSQL
Terminate Postgres Process pg_terminate_backend or pg_cancel_backend
if you kill -9, postgres will need to recover, during recover you cannot access postgres.
Explicit Locking
WITH RECURSIVE result AS () ... SELECT ... FROM result;
bloat — When a table has so many dead tuples, its size grows more than it actually needs is called bloat. When index points to dead tuples instead of tuple, it is bloat. Bloated index results in more unnecessary I/O fetches. Slowing down index lookup and scanning.
pg_repack for table bloat
Create a temp table as target table. COPY target table data to temp table. Create indexes on temp table. Then swap temp table and target table. In the process, pg_repack will free unused spaces to Operating System.
Slony, PostGIS, JDBC
CLOG buffers
Reuse execution plans
Explaining the Postgres Query Optimizer
Incremental sorting (PostgreSQL 13)
AWR: pg_statsinfo (by NTT)
ASH: pgsentinel
R = S + W Response time = Service time + Wait time
Monitoring
- PoWA
Wait Events
CTE
Usually in this format: <not-recursive> UNION [ALL] <recursive>
Moving Average via Window functions

work_mem — used in ORDER BY, DISTINCT
maintenance_work_mem — used in VACUUM, REINDEX, CREATE INDEX

Added since PostgreSQL 9.6+, thriving since 10+. This is not silver bullet.
Parallel Query does use more CPU, IO, Memory.
If you have spare CPU, IO, Memory to splurge, enjoy the benefit.

Configs we can change for Parallel Query.

max_parallel_workers_per_gather

0 means disabled Parallel Query; defaults to 2 (enabled).

max_worker_processes

defaults to 8. Max number of workers for System.

max_parallel_workers

defaults to 8. Max number of workers to use for Parallel Query.

If max_parallel_workers set to 2, but you have max_parallel_workers_per_gather set to 6, then you still only get 2 workers.

parallel_setup_cost

defaults to 1000. Cost to start parallel process.

parallel_tuple_cost

defaults to 0.1. Cost to process a row.

min_parallel_table_scan_size

defaults to 8MB. How big a table is to start using Parallel Query (bigger than 8MB).

min_parallel_index_scan_size

defaults to 512kb.

force_parallel_mode

Don’t enable this. This hurts OLTP.

max_worker_processes > max_parallel_workers > max_parallel_workers_per_gather

PostgreSQL query table in these ways and its parallel equivalent you can benefit when you enable Parallel Query:

Sequential Scan
Index Scan
Index-only Scans — When the selected field and condition matched the indexed conditioned.
Index Only Scan Backward
Bitmap Index Scan (when SQL’s WHERE has OR or AND)
Bitmap Heap Scan (when SQL’s WHERE has OR or AND)
Parallel Sequential Scan
Parallel Index Scan
Parallel Index-only Scan
Parallel Bitmap Index Scan
Parallel Bitmap Heap Scan

Aggregate functions can also run in parallel (COUNT, SUM, MIN, MAX).

plpgsql
pg_stat_statements CREATE extension pg_stat_statements;
pg_stat_activity
pgcrypto

Parallel index creation: CREATE INDEX CONCURRENTLY
GIN index can make LIKE queries run fast even if the % is not in the end
psql
- \x auto (auto format output nicely)
- \dx List all extensions
- \d Listing all relations
- \dt Listing all tables
- \d users Describe users tables
- \pset format csv Output as CSV, delimiter as tab: \pset csv_fieldsep '\t'
- \timing Show query execution time
Prefer jsonb over json
Should not use PREPARE TRANSACTION
Index: Partial Index
If index not supports btree, use include
Single transaction runs in a single-threaded fashion
shared_buffers 25% of RAM (check SHOW shared_buffers)

B-tree indexes being as much as 40% smaller on PostgreSQL 12.

PostgreSQL Person of the Week

EnterpriseDB (EDB)
2ndQuadrant (acquired by EnterpriseDB)
Percona
Citus Data (acquired by Microsoft)
Crunchy Data
pganalyze

TimescaleDB for timeline analysis
PipelineDB for dashboard, realtime monitoring
CitusDB for OLAP, OLTP, large data analysis
Crunchy Data for managed Postgres

pgMustard for reviewing Postgres query plans quickly

Please see PostgreSQL derived databases for more.