99% of Developers Don't Get PostgreSQL

Summary

The video explains why understanding PostgreSQL’s internal design—not just SQL or the distinction between SQL and NoSQL—can help developers reason about correctness, concurrency, durability, and performance.

PostgreSQL’s role and design

• PostgreSQL is a relational database that stores structured data and lets applications query and modify it with SQL.
• It is also an object-relational database. Alongside tables, rows, and foreign keys, it supports custom data types, functions, inheritance, arrays, JSON, and geometric data.
• Its architecture is designed to uphold the ACID properties:
  • Atomicity: A transaction succeeds completely or has no effect.
  • Consistency: Transactions preserve valid database states.
  • Isolation: Concurrent transactions are kept from interfering in ways that violate the database’s rules.
  • Durability: Committed changes survive crashes.

Client-server architecture and storage

• PostgreSQL follows a client-server model. Applications, APIs, and tools such as psql connect to the server, often over TCP.
• The server manages client connections and runs backend processes to execute queries. Other processes support tasks such as vacuuming, checkpointing, and persistence.
• Database files, indexes, and write-ahead logs reside in the data cluster on disk. PostgreSQL separates query execution from the physical storage of data.

Tables, pages, rows, and large values

• Tables are stored in heap files made up of fixed-size pages, typically 8 KB each.
• Pages contain metadata and item pointers that locate rows, which PostgreSQL internally represents as tuples.
• Tuples include transaction-related metadata used to determine whether a row version is visible to a query.
• PostgreSQL’s TOAST mechanism handles values too large to fit efficiently in a table row:
  1. PostgreSQL may compress a large value, using PGLZ by default. LZ4 is also available in newer PostgreSQL versions.
  2. If compression is insufficient or out-of-line storage is preferred, PostgreSQL stores the value in a separate TOAST table and leaves a reference in the main row.
• Keeping large values out of the main table can make operations that do not need those values more efficient.

MVCC and vacuuming

• PostgreSQL uses multi-version concurrency control (MVCC) to support concurrent reads and writes.
• Rather than overwriting a row during an update, PostgreSQL creates a new version and marks the previous version as obsolete.
• Transactions work from a consistent snapshot, so they can continue reading the version appropriate to that snapshot while other transactions make changes.
• Tuple metadata, including xmin and xmax, helps PostgreSQL determine which versions are visible to a transaction.
• This approach reduces conflicts between readers and writers, but leaves obsolete row versions, known as “dead tuples,” that must be cleaned up.
• VACUUM, commonly run automatically by autovacuum, reclaims space associated with versions no longer needed and helps keep the database efficient.

Write-ahead logging and crash recovery

• PostgreSQL uses write-ahead logging (WAL) to support durability: changes are recorded in the log before corresponding data pages are written to their data files.
• After a crash, PostgreSQL can replay WAL records to recover changes that had not yet reached the data files.
• WAL also supports replication: standby servers can replay the log stream to stay consistent with a primary server.
• Checkpoints periodically flush dirty pages—modified pages in memory—to disk and record a recovery point in the WAL.
• The checkpoint’s log sequence number helps PostgreSQL resume recovery from a recent point rather than replaying the entire log history. Checkpoint frequency involves a trade-off between write performance and recovery time.

Promotional segment

• The video includes a sponsored segment for Supabase, described as an open-source backend built on PostgreSQL.
• The host highlights Supabase tools for AI features such as vector search, production monitoring, row-level security, APIs, SDKs, and real-time updates.
• The video also recommends CodeCrafters as a project-based way to learn by building systems such as Docker, Redis, and compilers.

Speakers and sources featured

• The Coding Gopher: The video’s narrator and presenter.
• Supabase: Sponsor discussed in the video. The narrator refers to comments by its co-founders at Supabase Select 2025, but the subtitles do not identify them by name or indicate that they speak directly in the video.
• CodeCrafters: Mentioned in a promotional recommendation.