paulserban.eu

Writing Edition

Paul Serban

Writing, snippets & book notes

Vol. 2026

PostgreSQL Fundamentals: A Practical Guide to How It Actually Works

The core concepts every developer should understand before writing production queries against Postgres

Introduction

PostgreSQL has become the default relational database for a huge share of new backend systems, and for good reason: it's open source, standards-compliant, extraordinarily extensible, and mature enough that its behavior under load is well understood rather than mysterious. But "default choice" and "well understood" are not the same thing, and a large number of engineers who use Postgres daily have never looked past the SQL they write into the mechanics that make that SQL behave the way it does. That gap doesn't matter until it suddenly does - until a migration locks a table longer than expected, a query plan changes after a data volume grows, or a transaction isolation level produces a result nobody predicted.

This article is a fundamentals guide aimed squarely at professional developers, not first-time database users. It assumes you already know what a table, a join, and a transaction are, and instead focuses on the concepts that explain why Postgres behaves the way it does: its process architecture, its MVCC concurrency model, how indexes actually get chosen and used, and the small set of operational habits that separate teams who run Postgres confidently from teams who are quietly afraid of their own database. Wherever a concept benefits from seeing it in code, we'll use Python, TypeScript, and SQL together, since that combination reflects how most real applications actually talk to Postgres.

Context: What PostgreSQL Is and Where It Fits

PostgreSQL is an open-source, object-relational database management system with a development history stretching back to the POSTGRES project at the University of California, Berkeley, in the 1980s, and it has been released and maintained as PostgreSQL since the mid-1990s under the PostgreSQL Global Development Group. It implements a large subset of the SQL standard, adds a substantial number of its own extensions on top of that standard, and - unlike many relational databases that grew primarily around a fixed set of built-in types - was designed from early on to be extensible, which is why concepts like custom data types, custom operators, and procedural languages other than its native PL/pgSQL are first-class citizens rather than bolted-on afterthoughts.

Positioning Postgres correctly matters for architectural decisions. It is fundamentally an OLTP-oriented relational database: it excels at the transactional workload of a typical application - many concurrent, relatively short-lived reads and writes that need strong consistency guarantees - rather than at large-scale analytical scans across billions of rows, which is the domain of purpose-built OLAP or columnar systems. That said, Postgres sits closer to that analytical world than most general-purpose relational databases, partly because of extensions like pg_partman for partition management and the availability of foreign data wrappers, and partly because of genuinely useful built-in features like window functions, common table expressions, and a query planner sophisticated enough to handle moderately complex analytical queries reasonably well without a separate system.

The third piece of context worth naming explicitly is what Postgres is not, because comparisons with other databases in this space are a constant source of confusion. It is not the same thing as MySQL under a different name - the two engines make meaningfully different trade-offs around concurrency control, replication, and standards compliance, and code or intuition built around one does not transfer cleanly to the other. It is also not the same thing as a managed offering built on top of it, such as Amazon RDS for PostgreSQL or Amazon Aurora PostgreSQL-Compatible Edition; those services run the Postgres engine (or, in Aurora's case, a storage-layer-compatible variant of it) but wrap it in different operational, replication, and storage models, and understanding core Postgres is a prerequisite for reasoning correctly about any of those managed variants, not a substitute for it.

Core Architecture: Processes, MVCC, and the Write-Ahead Log

Postgres uses a process-based architecture, not a thread-based one: every client connection is handled by its own backend process, spawned by a central postmaster process that listens for incoming connections and forks a new backend for each one. This design has real operational consequences. Each backend process consumes its own memory overhead, which is part of why a large number of idle-but-open connections can degrade a Postgres instance's performance even if none of them are actively running queries - the fix in production systems is almost always a connection pooler, such as PgBouncer, sitting between the application and Postgres, multiplexing a large number of application-level connections onto a much smaller number of actual backend processes.

The concurrency model underneath all of this is MVCC (Multi-Version Concurrency Control), and it's arguably the single most important concept for understanding how Postgres behaves under concurrent load. Rather than using read locks that block writers, Postgres gives every transaction a consistent snapshot of the database as it existed at a particular point in time, and instead of overwriting a row in place, an UPDATE creates a new row version and marks the old one as no longer visible to new transactions, while a DELETE simply marks a row version as invisible rather than immediately reclaiming its space. This is why readers in Postgres essentially never block writers and writers essentially never block readers - each transaction is looking at its own consistent view of the data - but it's also why Postgres needs a background process called autovacuum to periodically clean up the old, no-longer-visible row versions ("dead tuples") that this model leaves behind; a misconfigured or overwhelmed autovacuum process is one of the most common root causes of unexplained performance degradation and table bloat in real Postgres deployments.

Durability is handled through the Write-Ahead Log (WAL): before any change is applied to the actual data files on disk, Postgres first writes a record of that change to the WAL, and only after the WAL record is safely persisted does the change get applied and eventually flushed to the underlying table storage. This ordering guarantees that if the server crashes mid-operation, Postgres can replay the WAL on restart and recover to a consistent state rather than leaving data half-written. The WAL is also the foundation for Postgres's built-in replication: physical streaming replication ships WAL records to replica servers, which apply them to maintain a continuously up-to-date copy of the primary, and this same mechanism underlies point-in-time recovery, where a base backup plus a sequence of WAL segments lets you restore a database to any specific moment rather than only to the time of the last full backup.

Deep Technical Explanation: Indexes, Transactions, and Isolation Levels

Indexing in Postgres is a broader topic than most developers give it credit for, because Postgres supports several fundamentally different index types suited to different query shapes, not just one general-purpose structure. The default and most commonly used type is the B-tree, well suited to equality and range queries on sortable data, and it's what you get automatically on a primary key or a plain CREATE INDEX. Beyond that, GIN (Generalized Inverted Index) indexes are built for cases where a single column can contain multiple searchable values - full-text search vectors, JSONB documents, or array columns - and GiST (Generalized Search Tree) indexes support more exotic query types like geometric containment or range overlap. Choosing the wrong index type doesn't just fail to help; a B-tree index on a JSONB column, for instance, will typically not be used at all by queries that search inside that JSON structure, leading to sequential scans that a developer assumed indexing had already solved.

Transactions in Postgres follow the standard SQL isolation level model, but with an important Postgres-specific detail: its default isolation level, Read Committed, guarantees that each statement within a transaction sees data committed before that statement began, but different statements in the same transaction can see different snapshots if other transactions commit in between. This is different from Repeatable Read, which gives the entire transaction one consistent snapshot for its whole duration, and from Serializable, which adds full protection against concurrency anomalies at the cost of needing to handle serialization failures (transactions that must be retried because Postgres detected a conflict it couldn't otherwise resolve safely). Many production bugs attributed to "flaky" application logic are actually isolation-level mismatches - code written with Repeatable Read assumptions running under the Read Committed default, silently seeing a different row-version snapshot partway through a multi-statement transaction than the developer expected.

Locking is the third pillar of this picture, and it interacts with MVCC rather than replacing it. Row-level locks (acquired implicitly by UPDATE, DELETE, and explicitly by SELECT ... FOR UPDATE) prevent two transactions from modifying the same row concurrently, and table-level locks of varying strength are acquired for schema-changing operations like adding a column or creating an index. The practically important detail here is that certain DDL operations - most notably creating a plain index without the CONCURRENTLY option - acquire locks strong enough to block ordinary reads and writes against the table for the duration of the operation, which is precisely the kind of thing that turns a routine migration into a production incident on a busy table if it isn't run with the right options or during a low-traffic window.

Practical Implementation Examples

Seeing these concepts expressed in real application code makes them concrete rather than abstract. The Python example below uses psycopg (the modern, actively maintained Postgres driver for Python, sometimes referred to as psycopg3) inside a connection-pooled context to demonstrate an explicit transaction with a chosen isolation level - the kind of code you'd write for an operation, like transferring a balance between two accounts, where the default isolation level isn't strong enough to prevent a subtle race condition.

import psycopg
from psycopg_pool import ConnectionPool

pool = ConnectionPool(
    "postgresql://app_user:REDACTED@db.internal:5432/ledger",
    min_size=2,
    max_size=10,
)

def transfer_funds(from_account_id: str, to_account_id: str, amount_cents: int):
    with pool.connection() as conn:
        conn.execute("SET TRANSACTION ISOLATION LEVEL REPEATABLE READ")
        with conn.transaction():
            cur = conn.execute(
                "SELECT balance_cents FROM accounts WHERE id = %s FOR UPDATE",
                (from_account_id,),
            )
            balance = cur.fetchone()[0]
            if balance < amount_cents:
                raise ValueError("Insufficient funds")

            conn.execute(
                "UPDATE accounts SET balance_cents = balance_cents - %s WHERE id = %s",
                (amount_cents, from_account_id),
            )
            conn.execute(
                "UPDATE accounts SET balance_cents = balance_cents + %s WHERE id = %s",
                (amount_cents, to_account_id),
            )
        # transaction commits automatically on successful exit from the `with` block

The FOR UPDATE clause here explicitly takes a row lock on the source account, which prevents a second concurrent transfer from reading a stale balance before the first one commits - this is the kind of guarantee that the default Read Committed isolation level alone does not provide across multiple statements in the same transaction, which is exactly the gap the earlier section on isolation levels described.

The second example demonstrates an index type choice in practice: a TypeScript service using node-postgres (pg) to query a jsonb column of product attributes, backed by a GIN index rather than the B-tree that would be created by default. Without the GIN index and its @> containment operator, this query pattern would force a sequential scan across the entire products table as it grows.

import { Pool } from "pg";

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 15,
});

// Migration (run once):
//   CREATE INDEX idx_products_attributes_gin ON products USING GIN (attributes);

interface ProductFilter {
  color?: string;
  size?: string;
}

async function findProductsByAttributes(filter: ProductFilter) {
  const attributeMatch: Record<string, string> = {};
  if (filter.color) attributeMatch.color = filter.color;
  if (filter.size) attributeMatch.size = filter.size;

  const result = await pool.query(
    `SELECT id, name, attributes
     FROM products
     WHERE attributes @> $1::jsonb
     ORDER BY created_at DESC
     LIMIT 50`,
    [JSON.stringify(attributeMatch)]
  );

  return result.rows;
}

Both examples share a pattern worth naming explicitly: the interesting engineering decision isn't the SQL syntax itself, which is simple in each case, but the choice of isolation level and index type sitting behind that syntax - decisions that are invisible in the query text but determine whether the code is correct under concurrency and fast at scale.

Trade-offs and Pitfalls

The most common Postgres pitfall in real production systems is autovacuum neglect, usually caused by default settings that don't match a table's actual write volume rather than autovacuum being disabled outright. Tables with very high update or delete rates can accumulate dead tuples faster than the default autovacuum configuration cleans them up, leading to table and index bloat that silently degrades query performance over weeks or months until someone finally investigates why a previously fast query has gotten steadily slower with no code changes involved. The signal is almost always visible in advance through the pg_stat_user_tables view, which exposes dead tuple counts per table - but almost nobody looks at it until performance has already degraded.

A second recurring pitfall is running schema migrations without accounting for lock strength, exactly as described in the deep technical explanation above. Creating an index without CONCURRENTLY takes a lock that blocks writes for the duration of the build, which can be a genuinely long time on a large table, and other schema-altering statements carry their own lock implications that vary by Postgres version. Teams that treat every migration as equivalent, without checking what lock a given DDL statement acquires and how long that lock might realistically be held against current table size, are exposed to migrations that pass instantly in staging and take a production system down during deployment.

A third pitfall, more architectural than operational, is using Postgres's flexibility as a substitute for schema discipline. The jsonb type is genuinely powerful and the earlier code example relies on it directly, but teams that push increasing amounts of core business data into loosely structured JSONB columns to avoid writing migrations eventually lose most of the benefits a relational database was chosen for in the first place - foreign key constraints, NOT NULL guarantees, and type checking all become much harder to enforce once critical fields live inside a JSON blob rather than as typed columns. JSONB is an excellent tool for genuinely variable, sparse, or user-defined attributes; it's a poor substitute for modeling data that has a known, stable shape.

Best Practices

Treat connection management as a first-class architectural concern rather than an afterthought, particularly for applications with high concurrency or serverless-style compute where connection churn is heavy. A connection pooler such as PgBouncer, sitting between application instances and the database, addresses the per-connection process overhead described earlier in the architecture section, and is close to mandatory for any application-per-request or function-per-invocation deployment model talking to Postgres directly.

Monitor the signals that predict problems before they become incidents rather than only the ones that confirm an incident already happened. pg_stat_user_tables for dead tuple counts and autovacuum activity, pg_stat_statements (a widely used extension for tracking per-query execution statistics) for identifying the queries actually consuming the most cumulative time rather than just the slowest individual query, and EXPLAIN (ANALYZE, BUFFERS) for understanding whether a specific query's plan is actually using the indexes you expect, are the three most consistently useful diagnostic tools available in a stock Postgres installation.

Analogies and Mental Models

MVCC is easiest to internalize through a library with photocopied editions analogy. Instead of every reader fighting over one physical copy of a book (a lock-based model where readers and writers contend for the same resource), the library hands each reader a photocopy reflecting the book's exact state at the moment they checked it out. Someone can be actively revising the master copy while other readers are still reading their photocopies undisturbed, and only once nobody needs an old photocopy anymore does the library recycle it. Autovacuum is the librarian responsible for noticing which photocopies are no longer being read and recycling that paper - and if the librarian falls behind, the shelves (disk space, index size) fill up with photocopies nobody needs anymore.

The WAL and replication model maps well onto a flight recorder and black-box replay analogy. Every change to the aircraft's state gets written to the flight recorder before it takes effect, in strict order, precisely so that if something goes wrong, the exact sequence of events can be replayed from that recording to reconstruct what happened - this is exactly what WAL replay does during crash recovery. A replica server is like a second aircraft receiving a live feed of that same recorder output and re-flying the identical sequence of maneuvers in near real time, which is why streaming replication produces a replica that mirrors the primary's state with only a small, measurable delay rather than requiring its own independent decision-making.

Isolation levels are best understood as how much of the world you're allowed to see change mid-conversation. Read Committed is like glancing at a shared whiteboard between each sentence you speak - someone else might have erased and rewritten part of it since your last glance, and you'll see the update. Repeatable Read is like taking a photograph of the whiteboard the moment the conversation starts and referring only to that photograph for the entire conversation, even if the real whiteboard changes underneath. Serializable goes further still, behaving as though every conversation happened one at a time in some order, even though in reality several were happening concurrently - which is powerful, but means some conversations occasionally have to be thrown out and restarted when the database can't find a consistent ordering that makes all of them valid simultaneously.

The 80/20 Insight

A relatively small set of ideas accounts for most of the practical benefit of understanding Postgres deeply, and they're worth prioritizing over memorizing the full breadth of its feature set. Understanding MVCC and autovacuum together is the single highest-leverage piece of knowledge, because it explains both why Postgres handles concurrent reads and writes gracefully and why "the database got slower over time with no code changes" is almost always a vacuum or bloat problem rather than a mysterious one.

Understanding that index type is a deliberate choice, not an automatic consequence of adding an index, is the second highest-leverage idea - a huge share of "why isn't Postgres using my index" questions trace back to a B-tree index sitting uselessly next to a query pattern that needed GIN or GiST instead. And understanding that the default Read Committed isolation level does not give you a single consistent snapshot across an entire transaction resolves most of the subtle concurrency bugs that show up in financial, inventory, or booking systems, where a stronger isolation level or an explicit row lock is often exactly what the business logic actually needed.

Key Takeaways

The following five habits translate the concepts above into concrete, immediately applicable engineering practice:

Conclusion

PostgreSQL rewards the kind of engineer who's willing to look one layer beneath the SQL syntax, and it punishes, slowly and quietly, the kind of engineer who treats it as an interchangeable black box. The concepts covered here - process-based architecture, MVCC and autovacuum, the write-ahead log, index type selection, and isolation levels - aren't academic trivia; they are the specific, well-documented mechanisms that determine whether your application behaves correctly under concurrency and performs consistently as your data grows, and every one of them has directly observable consequences in real production incidents.

None of this requires becoming a database internals specialist to benefit from. It requires knowing that autovacuum activity and dead tuple counts are worth checking before they become a problem, that index type is a decision rather than a default, that the isolation level your transaction runs under is a choice with real consequences, and that a schema migration's lock behavior deserves the same scrutiny as its logical correctness. Teams that build those habits get a database that stays fast, predictable, and boring in the best sense of the word - which, for the transactional core of a production system, is exactly the outcome worth engineering for.

References

Resources