PostgreSQL is an object-relational DBMS with full SQL standard compliance, extensions (JSONB, GIS), and MVCC. MySQL is simpler and faster for basic operations.
Multi-Version Concurrency Control. Each transaction sees a consistent snapshot of the data. Outdated row versions are removed by VACUUM.
DELETE is logged, fires triggers, supports WHERE, and can be rolled back. TRUNCATE is instant, has no WHERE/triggers, and resets AUTO_INCREMENT.
Write-Ahead Logging — all changes are written to the log before being applied to the data. Guarantees recovery after a crash.
B-tree (default), Hash, GiST, GIN (JSONB, full-text search), BRIN (for large sorted tables), Partial (WHERE condition).
GIN — for exact lookups in arrays/JSONB (fast lookup). GiST — for spatial data and partial matching (GIS, full-text).
Shows the query execution plan with actual timing metrics. seq_scan is a bad sign for large tables.
Removes dead tuples (dead rows from UPDATE/DELETE). AUTOVACUUM runs automatically for all tables.
JSON is stored as plain text. JSONB is binary, with indexing and validation, and is faster for reads/searches.
-> (get field as an object), ->> (as text), @> (contains), ? (has key), #>> (path as text).
CREATE INDEX ON table USING GIN (jsonb_column). Supports @>, ?| (any key), ?& (all keys).
Many queries instead of one. Solution: JOINs, LATERAL, window functions, materialized CTEs.
INNER — intersection. LEFT — everything from the left + NULL on the right. LATERAL — a correlated subquery (works like a JOIN with a function).
WITH my_cte AS MATERIALIZED (SELECT ...) — computed once and cached. A regular CTE is recomputed on every use.
work_mem — memory for sorting/hash joins per operation. maintenance_work_mem — for VACUUM/INDEX. Overallocating leads to OOM.
Read Uncommitted (dirty reads), Read Committed (default), Repeatable Read, Serializable (blocks conflicting transactions).
Deadlock — mutual blocking. Livelock — processes are active but make no progress (constantly yielding to each other).
pg_stat_activity (state='active', query_start), pg_locks, pg_blocking_pids().
An extension for geodata. Supports points, polygons, distances, ST_*. Uses GIST indexes.
An extension for time-series data. Hypertables are automatically partitioned by time, plus compression.
tsvector + tsquery. to_tsvector('english', text) @@ plainto_tsquery('english', 'search'). Uses a GIN index.
A stream of changes between databases (not a physical dump). Supports different PG versions and table filtering.
Primary-standby with streaming replication. wal_level=replica, max_wal_senders, hot_standby=on.
pg_dump is a logical dump (SQL). pg_basebackup is a physical dump (DB files for replication).
pg_stat_activity, pg_stat_bgwriter, pg_stat_database (tup_fetched/returned), checkpoints, WAL size, cache hit ratio (>99%).
ROW_NUMBER(), RANK(), LAG(), LEAD(), aggregates with OVER(). Grouping without GROUP BY.
UNION ALL is simple concatenation. UNION is concatenation + DISTINCT (slower).