How Databases Work
IntermediateA relational database like PostgreSQL is a sophisticated engine that takes a SQL query, figures out the most efficient way to fetch data, retrieves it from disk pages, ensures multiple users can work simultaneously without corrupting each other's data, and guarantees that even a power failure won't corrupt your records. Understanding these internals is the difference between writing queries that take 2ms and ones that take 20 seconds.
Think of it like a library with a very smart librarian
The librarian (query planner) doesn't just randomly search shelves — they check the index card catalog (B-tree index) first to find exactly which shelf (page) holds your book. The library also has a strict check-in/check-out system (transactions) so two people can't simultaneously modify the same record, and a disaster recovery log (WAL) so if the lights go out, nothing is lost.
Step by Step
Key Concepts
Page (Block)
The fundamental unit of storage. PostgreSQL reads and writes data in 8KB pages. A table with 1 million rows might span tens of thousands of pages. The shared buffer cache holds frequently used pages in RAM.
B-Tree Index
The default index type in PostgreSQL. A self-balancing tree where leaf nodes contain index key values and pointers to the actual table rows (heap tuples). Supports equality and range queries in O(log n) time.
Query Planner / Optimiser
The component that takes a parsed query and finds the most efficient execution plan. It considers index availability, table statistics, join strategies, and estimated row counts. Run EXPLAIN ANALYZE to see the chosen plan.
MVCC (Multi-Version Concurrency Control)
PostgreSQL's concurrency model. Instead of locking rows, it creates new versions on update. Each transaction sees a snapshot of the database as of when it started, ensuring consistent reads without blocking writers.
WAL (Write-Ahead Log)
An append-only log that records every change before it's applied to data pages. Used for crash recovery, replication, and point-in-time recovery. The foundation of durability in PostgreSQL.
ACID
Atomicity (all-or-nothing), Consistency (data stays valid), Isolation (transactions don't see each other's in-progress changes), Durability (committed data survives crashes). The four guarantees every relational database provides.
Buffer Cache (Shared Buffers)
PostgreSQL's in-memory page cache. Frequently accessed pages are kept here to avoid repeated disk reads. Typically set to 25% of total RAM. A cache hit is ~100x faster than reading from disk.
Vacuum
Because MVCC keeps old row versions for concurrent readers, dead row versions accumulate over time. VACUUM reclaims this space. AUTOVACUUM runs automatically in the background to prevent table bloat and keep statistics fresh.
Key Facts
- PostgreSQL's query planner uses statistics tables (pg_statistic) that track column value distributions to estimate how many rows a filter will return — these statistics are updated by ANALYZE.
- A full table scan on a 10-million-row table with 8KB pages requires reading up to 800MB of data from disk. A B-tree index reduces this to a few kilobytes.
- PostgreSQL's MVCC means a long-running transaction that never commits will prevent VACUUM from cleaning up dead row versions, causing table bloat — this is called a "transaction ID wraparound" problem.
- The WAL is also the mechanism behind PostgreSQL replication — standby servers replay the primary's WAL stream to stay in sync.
- Connection establishment in PostgreSQL involves a TCP handshake + authentication + process fork, taking ~5–10ms. With PgBouncer connection pooling, this is reduced to near-zero.
- Indexes are not free — every write (INSERT/UPDATE/DELETE) must also update every index on that table, adding write overhead. Only add indexes that queries actually use.
Real-World Applications
EXPLAIN ANALYZE for query optimisation
Running EXPLAIN ANALYZE before any slow query shows the exact plan chosen, rows estimated vs actual, and time spent at each step. Finding a "Seq Scan" where you expected an "Index Scan" reveals missing indexes.
Connection Pooling in production
Most backend frameworks open a new database connection per request. At scale this exhausts PostgreSQL's max_connections. PgBouncer or pgpool-II sit between the app and database, maintaining a small pool of long-lived connections and multiplexing many application connections through them.
Read replicas for scale
PostgreSQL streaming replication sends the primary's WAL to one or more standby servers in near real-time. Read-heavy applications route SELECT queries to replicas, leaving the primary for writes only.
Partial and composite indexes
Instead of indexing an entire large column, partial indexes (CREATE INDEX ... WHERE status = 'pending') index only a filtered subset of rows, making the index smaller and faster for specific query patterns.
Frequently Asked Questions
When should I use an index vs not?
Add indexes on columns used in WHERE clauses, JOIN conditions, and ORDER BY clauses that return a small percentage of rows. Avoid indexing low-cardinality columns like boolean flags — a full table scan is often faster when more than 10–15% of rows match. Always measure with EXPLAIN ANALYZE before and after.
What is the difference between a clustered and non-clustered index?
A clustered index determines the physical order of rows on disk — a table can only have one. PostgreSQL doesn't have clustered indexes by default (unlike SQL Server), but the CLUSTER command can reorder a table according to an index. Non-clustered (heap) indexes are separate structures that point back to the table rows.
What causes N+1 query problems and how do you fix them?
An N+1 problem occurs when you fetch N parent records and then execute one query per record to fetch children — resulting in N+1 total queries. Fix it with a JOIN or by using eager loading (e.g., JPA's fetch = EAGER or Hibernate's @BatchSize) so all data is fetched in one or two queries.
What is the difference between TRUNCATE and DELETE?
DELETE removes rows one by one, writes WAL for each row, and respects foreign key constraints — it's slow but safe. TRUNCATE drops and recreates the data pages in one operation, is much faster, but cannot be rolled back in some databases and bypasses row-level triggers.
How does PostgreSQL handle concurrent writes to the same row?
The first transaction to write a row acquires a row-level lock. A second transaction trying to update the same row will block and wait. If using SELECT FOR UPDATE SKIP LOCKED, the second transaction will skip locked rows instead of waiting — useful for job queue patterns.