Architecture & Internals

PostgreSQL traces back to 1986 at UC Berkeley. Michael Stonebraker's POSTGRES project (Post-Ingres) aimed to solve the limitations of the relational model by adding extensibility — custom types, operators, and access methods. The "object-relational" in PostgreSQL means you can define your own data types and teach the database how to index them.

The core philosophy: correctness first, performance second. PostgreSQL will never sacrifice data integrity for speed. This is why it's the default choice when you need to trust your data — financial systems, healthcare, anything where "eventually consistent" isn't acceptable.

When PostgreSQL is the Wrong Choice

PostgreSQL uses a multi-process model — one OS process per client connection. This is fundamentally different from thread-based databases like MySQL. Each backend process has its own memory space, which provides isolation but costs ~5–10 MB per connection.

-- See all active connections
SELECT pid, usename, application_name, state, query_start
FROM pg_stat_activity
WHERE state = 'active';

-- Check max connections setting
SHOW max_connections;  -- default: 100

-- Connection count by state
SELECT state, COUNT(*)
FROM pg_stat_activity
GROUP BY state;

PostgreSQL's memory is split into shared memory (accessible by all processes) and per-process memory (private to each backend). Understanding this split is essential for tuning.

-- See shared buffer hit ratio (should be > 99%)
SELECT
  sum(blks_hit) * 100.0 / sum(blks_hit + blks_read) AS hit_ratio
FROM pg_stat_database
WHERE datname = current_database();

-- Check current memory settings
SHOW shared_buffers;
SHOW work_mem;
SHOW effective_cache_size;

Tables in PostgreSQL are stored as heap files — unordered collections of 8 KB pages. Each page contains a header, item pointers (line pointers), free space, and the actual tuple data. There is no clustered index by default — rows are stored in insertion order.

TOAST — The Oversized Attribute Storage Technique

When a row exceeds ~2 KB, PostgreSQL automatically moves large column values to a separate TOAST table. This keeps the main table's pages efficient for scanning. TOAST uses compression (LZ) by default and stores values out-of-line.

-- See physical size of a table (including TOAST and indexes)
SELECT
  pg_size_pretty(pg_total_relation_size('orders')) AS total,
  pg_size_pretty(pg_relation_size('orders')) AS table_only,
  pg_size_pretty(pg_indexes_size('orders')) AS indexes;

-- See TOAST table for a relation
SELECT relname, reltoastrelid::regclass
FROM pg_class
WHERE relname = 'orders';

The WAL is PostgreSQL's fundamental durability mechanism. The rule is simple: write the log before writing the data. Before any change to a data page is flushed to disk, the corresponding WAL record must be on stable storage. This guarantees that after a crash, PostgreSQL can replay the WAL to recover any committed transaction.

-- Current WAL position
SELECT pg_current_wal_lsn();

-- WAL generation rate
SELECT pg_wal_lsn_diff(
  pg_current_wal_lsn(),
  '0/0'
) / 1024 / 1024 AS total_wal_mb;

-- Check checkpoint frequency
SELECT checkpoints_timed, checkpoints_req,
       checkpoint_write_time, checkpoint_sync_time
FROM pg_stat_bgwriter;

MVCC (Multi-Version Concurrency Control) is how PostgreSQL achieves non-blocking reads. Instead of locking rows when they're being modified, PostgreSQL keeps multiple versions of each row. Readers see a consistent snapshot without waiting for writers. Writers create new row versions without blocking readers.

-- See xmin/xmax on rows
SELECT xmin, xmax, * FROM orders LIMIT 5;

-- See dead tuples accumulating
SELECT relname, n_live_tup, n_dead_tup,
       round(n_dead_tup * 100.0 / nullif(n_live_tup, 0), 1) AS dead_pct
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

-- Check transaction ID age (wraparound risk)
SELECT datname, age(datfrozenxid) AS xid_age
FROM pg_database
ORDER BY xid_age DESC;

This comparison matters because it's the most common interview question about database choice. The differences are architectural, not superficial.