Skip to content

Explanation

How PostgreSQL works

PostgreSQL uses a process-per-connection model supervised by the postmaster. Shared memory structures (buffer pool, WAL buffers, lock tables, commit log) are visible to all backend processes. Each backend parses, plans and executes its own queries. Durability comes from the Write-Ahead Log (WAL). Concurrency comes from MVCC with row versions stored in the heap, which is why VACUUM exists. PostgreSQL 18 added an asynchronous I/O (AIO) subsystem to this long-standing design.

Related pages: PostgreSQL hub, How-to Guides, Reference (parameter defaults, limits, version matrix).

Architecture Overview

The diagram below shows the main runtime components of a PostgreSQL 18 primary and how WAL leaves it for archives and standbys.

flowchart TB
    subgraph Clients["Clients"]
        APP["Application (libpq / JDBC)"]
        POOL["PgBouncer (optional pooler)"]
    end

    subgraph Primary["PostgreSQL 18 primary"]
        PM["postmaster<br/>(listens on 5432, forks children)"]
        subgraph Backends["Client backends (one process per connection)"]
            BE1["backend 1"]
            BE2["backend N"]
        end
        subgraph BG["Auxiliary and background processes"]
            CKPT["checkpointer"]
            BGW["background writer"]
            WALW["walwriter"]
            AVL["autovacuum launcher + workers"]
            IOW["io workers<br/>(io_method = worker)"]
            WS["walsender"]
            ARCH["archiver"]
        end
        subgraph SHM["Shared memory"]
            SB["shared_buffers"]
            WB["WAL buffers"]
            LOCKS["lock tables / ProcArray / CLOG"]
        end
        subgraph DISK["PGDATA on disk"]
            HEAP["base/ (heap + index files)"]
            WAL["pg_wal/ (16 MB segments)"]
        end
    end

    STBY["Hot standby<br/>(walreceiver + startup process)"]
    REPO["WAL archive / backup repo<br/>(pgBackRest, object storage)"]

    APP --> POOL --> PM
    PM --> BE1
    PM --> BE2
    BE1 --- SB
    BE2 --- SB
    BE1 --> WB
    WALW --> WAL
    WB --> WALW
    CKPT --> HEAP
    BGW --> HEAP
    IOW -->|"async reads"| HEAP
    SB --- IOW
    AVL --- SB
    WAL --> WS -->|"streaming replication"| STBY
    WAL --> ARCH -->|"archive_command / archive_library"| REPO

    style PM fill:#e8f4f8,stroke:#2196f3,color:#000
    style SB fill:#fff3e0,stroke:#ff9800,color:#000
    style WAL fill:#fce4ec,stroke:#e91e63,color:#000

Process Architecture

When PostgreSQL starts, the postmaster initializes shared memory and starts the auxiliary processes. For each incoming client connection, it forks a dedicated backend process. That backend stays with the session until it disconnects. Because of this, connections cost real memory and fork overhead, and connection pooling is standard practice (see Design Decisions and Trade-offs).

The next diagram shows the postmaster's children and which of them attach to shared memory.

graph TB
    POSTMASTER["postmaster<br/>(supervisor daemon)"]

    subgraph "Background Processes"
        BGWRITER["Background Writer<br/>(bgwriter)"]
        CHECKPT["Checkpointer"]
        WALWRITER["WAL Writer<br/>(walwriter)"]
        AUTOVAC["Autovacuum Launcher"]
        AUTOWORK["Autovacuum Workers<br/>(dynamic)"]
        IOWORK["I/O Workers<br/>(PG 18+, io_method = worker)"]
        LOGICAL["Logical Replication<br/>Launcher / Apply Workers"]
    end

    subgraph "Client Backends"
        BE1["Backend Process<br/>(session 1)"]
        BE2["Backend Process<br/>(session 2)"]
        BEN["Backend Process<br/>(session N)"]
    end

    subgraph "Shared Memory"
        SHARED["shared_buffers<br/>WAL Buffer<br/>Lock Tables<br/>ProcArray / CLOG<br/>Cumulative statistics (PG 15+)"]
    end

    POSTMASTER --> BGWRITER
    POSTMASTER --> CHECKPT
    POSTMASTER --> WALWRITER
    POSTMASTER --> AUTOVAC
    AUTOVAC --> AUTOWORK
    POSTMASTER --> IOWORK
    POSTMASTER --> LOGICAL
    POSTMASTER --> BE1
    POSTMASTER --> BE2
    POSTMASTER --> BEN

    BE1 --- SHARED
    BE2 --- SHARED
    BEN --- SHARED
    BGWRITER --- SHARED
    CHECKPT --- SHARED
    WALWRITER --- SHARED
    IOWORK --- SHARED

    style POSTMASTER fill:#e8f4f8,stroke:#2196f3,color:#000
    style SHARED fill:#fff3e0,stroke:#ff9800,color:#000
    style BGWRITER fill:#e8f5e9,stroke:#4caf50,color:#000

Background Processes

Process Role
Background Writer (bgwriter) Writes dirty pages from shared_buffers to the OS page cache ahead of time, so backends seldom have to evict dirty pages themselves. Tuned with bgwriter_delay and bgwriter_lru_maxpages.
Checkpointer Flushes all dirty pages in shared_buffers to disk (with fsync) and writes a checkpoint record to the WAL. WAL segments older than the checkpoint's redo point can then be recycled or archived. Triggered by checkpoint_timeout (default 5 min) or when WAL exceeds max_wal_size.
WAL Writer (walwriter) Flushes WAL records from the WAL buffers to WAL segment files every wal_writer_delay (default 200 ms). This mostly helps asynchronous-commit transactions. Synchronous commits flush their own WAL at commit.
Autovacuum Launcher Watches table statistics and starts autovacuum workers for tables past their dead-tuple or XID-age thresholds. Essential for MVCC garbage collection.
I/O Workers (PG 18+) Carry out asynchronous reads for backends when io_method = worker, the default. There are io_workers of them (default 3). PG 19 scales them automatically.
Statistics Collector (removed) Before PG 15 a separate process collected runtime statistics over UDP. PG 15 replaced it with a cumulative statistics system in shared memory.
walsender / walreceiver Stream WAL to standbys and logical subscribers, or receive it on the standby side.
Archiver Copies finished WAL segments to an archive with archive_command or archive_library.

Shared Memory Structures

PostgreSQL allocates its main shared memory at startup. The most important regions are shown below.

graph LR
    subgraph "Shared Memory"
        SB["shared_buffers<br/>(8 KiB page frames)"]
        WALB["WAL Buffer<br/>(circular)"]
        LT["Lock Tables"]
        PA["ProcArray<br/>(active txn XIDs)"]
        CL["CLOG / pg_xact<br/>(txn status bits)"]
        MT["MultiXact<br/>(shared row locks)"]
    end

    style SB fill:#e3f2fd,stroke:#2196f3,color:#000
    style WALB fill:#fce4ec,stroke:#e91e63,color:#000
Region Purpose
shared_buffers Main buffer pool. Default 128 MiB, and production systems commonly use about 25% of RAM. Pages are 8 KiB. Eviction uses a clock-sweep algorithm. PostgreSQL also relies on the OS page cache (double buffering), which is why effective_cache_size exists.
WAL Buffer Circular buffer that holds WAL records until they are written to disk. The default wal_buffers = -1 autotunes to 1/32 of shared_buffers, with a minimum of 64 kB and a maximum of one WAL segment (usually 16 MB).
Lock Tables Shared lock manager that tracks relation-level, page-level, tuple-level and advisory locks for all backends. Sized by max_locks_per_transaction (default 64, raised to 128 in PG 19).
ProcArray Array of all active backends and their transaction IDs. Used to build snapshots, which determine which XIDs a transaction can see.
CLOG (Commit Log) Commit status of each transaction ID (in progress, committed, aborted), as 2-bit entries in SLRU buffers. Stored on disk in pg_xact/.
MultiXact Tracks sets of transactions that lock the same row, for example with SELECT ... FOR SHARE.

WAL (Write-Ahead Log)

The WAL protocol is the foundation of PostgreSQL's crash recovery, replication and point-in-time recovery. The core rule: a dirty data page is never written to disk before the WAL records that describe its changes are flushed.

WAL Record Lifecycle

The sequence below traces one committed change from backend to durable storage and then to a checkpoint.

sequenceDiagram
    participant BE as Backend Process
    participant WB as WAL Buffer
    participant WF as WAL Segment Files<br/>(pg_wal/)
    participant CK as Checkpointer
    participant DF as Data Files<br/>(base/)

    BE->>BE: Modify tuple in shared_buffers
    BE->>WB: XLogInsertRecord (WAL entry)
    Note over BE,WB: WAL record appended to WAL buffer
    BE->>WF: XLogFlush at COMMIT
    Note over BE,WF: Durable before the client gets its ack (synchronous_commit = on)
    CK->>DF: Write all dirty pages to disk (fsync)
    CK->>WF: Write checkpoint record
    Note over CK,WF: Old WAL segments before the checkpoint<br/>can be recycled or archived

WAL configuration defaults are in Reference — Configuration Parameters.

Full-Page Writes (FPW)

After a checkpoint, the first change to any data page writes a full-page image (FPI) of that page into the WAL. This protects against torn pages: if an 8 KiB page write is interrupted, recovery rebuilds the page from the FPI. FPIs are the main reason WAL volume jumps right after each checkpoint. Longer checkpoint_timeout and wal_compression (lz4/zstd) reduce the effect.

MVCC and VACUUM

PostgreSQL implements Multi-Version Concurrency Control (MVCC) by storing row versions (tuples) in the table heap itself. There is no undo log. Each tuple header has:

  • xmin: XID of the transaction that created this tuple version.
  • xmax: XID of the transaction that deleted or updated it (0 if it is still live).
  • infomask: status hint bits (committed, aborted, locked, and others).

Readers never block writers and writers never block readers. The sequence below shows how an uncommitted UPDATE stays invisible to a concurrent reader.

sequenceDiagram
    participant TX1 as Transaction 1 (xid=100)
    participant Heap as Table Heap
    participant TX2 as Transaction 2 (xid=101, READ COMMITTED)

    TX1->>Heap: UPDATE row - writes new version (xmin=100)
    Note over Heap: Old version gets xmax=100<br/>New version has xmin=100, xmax=0
    TX2->>Heap: SELECT - sees the old version (100 not committed)
    TX1->>TX1: COMMIT
    TX2->>Heap: SELECT (new statement, new snapshot) - sees the new version

Visibility Rules

When a backend reads a table, it takes a snapshot that says which transaction IDs count as committed for it:

  1. A tuple is visible if its xmin committed before the snapshot and its xmax is 0, aborted, or not yet committed in the snapshot.
  2. A tuple whose xmin is still in progress (another session's uncommitted insert) is invisible.
  3. A tuple whose xmax committed before the snapshot is invisible, because it was deleted or replaced by an update.

Dead Tuples and VACUUM

Updated or deleted rows leave dead tuples behind. They stay on disk until VACUUM reclaims the space.

  • VACUUM marks dead-tuple space as reusable and updates the visibility map and free space map. It does not shrink the file, except for empty pages at the end.
  • VACUUM FULL rewrites the whole table under an ACCESS EXCLUSIVE lock and returns space to the OS. PG 19 adds REPACK, which unifies VACUUM FULL and CLUSTER, and REPACK (CONCURRENTLY), which does not block reads and writes.
  • Autovacuum runs VACUUM and ANALYZE automatically, based on autovacuum_vacuum_scale_factor, autovacuum_vacuum_threshold, and since PG 18 autovacuum_vacuum_max_threshold. It is on by default. PG 18 adds eager freezing of all-visible pages. PG 19 adds parallel index vacuuming in autovacuum and a priority scoring system.

Bloat from long-running transactions

Long-running transactions, including idle-in-transaction sessions, abandoned replication slots, and prepared transactions, stop VACUUM from removing tuples that are newer than the oldest snapshot still in use. The result is table and index bloat.

Transaction ID Wraparound

PostgreSQL uses 32-bit transaction IDs compared modulo 2^32, so any XID can only look about 2 billion transactions into the past. Without freezing, old rows would suddenly look like they were written "in the future" and disappear (data loss). To prevent this:

  • Freezing marks old tuples as visible to everyone (frozen), so their xmin no longer needs to be compared.
  • Autovacuum starts an aggressive, anti-wraparound vacuum when a table's XID age passes autovacuum_freeze_max_age (default 200 million), even if autovacuum is disabled.
  • As a last resort the server stops assigning new XIDs as the limit gets close. Single-user-mode recovery then becomes necessary. Monitoring age(datfrozenxid) prevents this.

Query Processing Pipeline

Each backend handles a query in these stages:

  1. Parser: turns the SQL text into a parse tree.
  2. Analyzer: resolves table and column references and checks types. The result is a query tree.
  3. Rewriter: applies rules. For example, views are expanded and RLS policies are attached.
  4. Planner/Optimizer: builds the execution plan with a cost-based optimizer. It considers:
    • Sequential scans vs. index scans vs. index-only scans. PG 18 adds B-tree skip scan.
    • Join methods: nested loop, hash join, merge join.
    • Join order, by dynamic programming or GEQO for joins of many tables. PG 18 also removes some redundant self-joins.
    • Parallel query paths (parallel sequential scan, parallel hash join).
  5. Executor: runs the plan as a pull-based tree in which each node asks its children for tuples. Scans get their pages through the buffer manager, which since PG 18 can issue asynchronous reads (next section).

Asynchronous I/O (PostgreSQL 18)

Before PostgreSQL 18, a backend that needed a page not in shared_buffers made a synchronous read and waited. PG 17 introduced the read stream API and I/O combining (io_combine_limit), but reads were still synchronous, and posix_fadvise hints were the only prefetching. PostgreSQL 18 puts a real AIO subsystem under the read stream. A scan tells the stream which blocks it will need. The stream then issues reads ahead of time, combines adjacent blocks into larger I/Os (up to io_combine_limit, default 128 kB), and hands buffers back as they complete.

How a Read Flows Through AIO

The sequence below shows a sequential scan with the default io_method = worker. With io_uring, the backend submits to the kernel ring directly, without an I/O worker in between.

sequenceDiagram
    participant EX as Executor (SeqScan)
    participant RS as Read stream
    participant BM as Buffer manager
    participant IOW as I/O worker process
    participant FS as Kernel / storage

    EX->>RS: next block please
    RS->>BM: pin buffers for blocks N..N+15 (look-ahead)
    BM->>IOW: queue combined read (up to io_combine_limit)
    IOW->>FS: preadv() of up to 128 kB
    RS-->>EX: return block N (already cached)
    FS-->>IOW: data ready
    IOW-->>BM: mark buffers valid, wake waiters
    EX->>RS: next block please
    RS-->>EX: return block N+1 without waiting

io_method Choices

io_method How it works When to use
worker (default) Backends hand read requests to a pool of io_workers processes Portable, works on every platform. Raise io_workers if pg_aios shows queueing
io_uring Backends submit to a Linux io_uring ring directly, so no worker hop Linux builds with --with-liburing. Lowest overhead
sync Reads are done synchronously, as in PG 17 Troubleshooting, or to compare behavior with older versions

What AIO does not change: it covers reads (sequential scans, bitmap heap scans, VACUUM, and some maintenance paths). WAL writes and ordinary buffer writes still go through the existing write path. It gives the biggest gains on high-latency storage such as cloud network block volumes, where waiting for each read dominated. The PGDG PostgreSQL 18 announcement reported up to 3x faster reads from storage in its tests. Treat that figure as a best case, not something to plan capacity on.

Replication

PostgreSQL has two native replication mechanisms.

Streaming (Physical) Replication

  • Ships WAL byte streams from walsender to the standby's walreceiver.
  • Standbys are byte-for-byte copies of the primary (same major version and architecture).
  • Supports synchronous (synchronous_standby_names, including ANY n quorum) and asynchronous modes.
  • Standbys can serve read-only queries (hot standby). PG 19 adds WAIT FOR LSN for read-your-writes consistency on standbys.
  • There is no built-in automatic failover. Patroni, CloudNativePG, or repmgr-style tools handle leader election and promotion.

Logical Replication

  • Decodes WAL into logical changes (INSERT, UPDATE, DELETE, TRUNCATE) through an output plugin such as pgoutput. Since 18.6/17.11 and the other August 2026 minors, only plugins listed in output_plugin_libraries may be loaded.
  • Publications define what is replicated. Subscriptions consume it.
  • Replication slots record each consumer's position (LSN). The primary keeps WAL until every slot has consumed it.
  • Works across major versions and supports selective tables, row filters and column lists. That makes it the usual near-zero-downtime path for major upgrades.
  • PG 18 adds parallel streaming by default, conflict logging and replication of generated columns. PG 19 adds sequence replication and enabling logical decoding on demand while wal_level = replica.

The diagram below shows one publisher feeding two subscribers, each with its own slot.

graph LR
    PUB["Primary<br/>(Publisher)"]
    WALD["Logical decoding<br/>(pgoutput plugin)"]
    SLOT1["Replication slot A<br/>(confirmed_flush_lsn)"]
    SLOT2["Replication slot B<br/>(confirmed_flush_lsn)"]
    SUB1["Subscriber 1<br/>(apply worker)"]
    SUB2["Subscriber 2<br/>(apply worker)"]

    PUB --> WALD
    WALD --> SLOT1 -->|"walsender stream"| SUB1
    WALD --> SLOT2 -->|"walsender stream"| SUB2

    style PUB fill:#e8f5e9,stroke:#4caf50,color:#000
    style SLOT1 fill:#fff3e0,stroke:#ff9800,color:#000
    style SLOT2 fill:#fff3e0,stroke:#ff9800,color:#000

Replication slot dangers

If a subscriber stays offline for a long time, its replication slot stops WAL recycling on the primary. Monitor pg_replication_slots, and set max_slot_wal_keep_size and, since PG 18, idle_replication_slot_timeout to prevent disk exhaustion.

Extension API

PostgreSQL can gain new types, functions, operators, index access methods and procedural languages without changes to core code:

  • Extensions: packaged and installed with CREATE EXTENSION, for example pgcrypto, PostGIS, pg_stat_statements and vector (pgvector). PG 18 adds extension_control_path for installing extensions outside the main share directory, which is useful for immutable container images.
  • Procedural languages: PL/pgSQL (built in), PL/Python, PL/Perl, PL/Tcl, PL/v8 (JavaScript, third party).
  • Index access methods: GiST, SP-GiST, GIN and BRIN are extensible frameworks. pgvector adds HNSW and IVFFlat.
  • Hooks and custom scan providers: let extensions replace or add to planning and execution. Citus uses them for distributed queries, and PG 19's pg_plan_advice uses planner hooks.
  • Table access methods: an alternative storage format can replace the heap, for example Percona's tde_heap or columnar stores.
  • Foreign Data Wrappers (FDW): expose external data sources as local tables, for example postgres_fdw and file_fdw.

Design Decisions and Trade-offs

Decision Benefit Cost Mitigation
Process per connection Strong isolation. A crashing backend is contained, and the postmaster resets shared memory Memory and fork cost per connection. Thousands of idle connections waste resources PgBouncer in transaction mode, or built-in poolers in managed services
Heap-based MVCC (no undo log) Instant rollback, cheap reads of old versions Dead tuples, bloat, VACUUM overhead, XID wraparound Autovacuum tuning, eager freezing (18), REPACK CONCURRENTLY (19)
Double buffering (shared_buffers + OS cache) Simple design that uses the kernel's readahead and caching Some memory is duplicated Moderate shared_buffers (about 25%), AIO in 18
No built-in failover or sharding Small, stable core High availability and scale-out need external tools Patroni, CloudNativePG, Citus
Extensibility as a first-class feature Extensions such as pgvector, PostGIS and TimescaleDB Extension version compatibility during major upgrades Check extension support before pg_upgrade
Permissive PostgreSQL License, community governance No single-vendor relicensing risk No single commercial owner of the roadmap Many vendors (EDB, Crunchy, Microsoft, AWS, Google) contribute

Security Model

PostgreSQL enforces security in layers: network (listen_addresses, pg_hba.conf), transport (TLS, GSSAPI encryption), authentication (SCRAM, certificates, Kerberos, LDAP, OAuth), authorization (roles, privileges, RLS) and data protection (pgcrypto, and disk or TDE encryption). Setup tasks are in How-to Guides — Security Setup. Method tables and the hardening checklist are in Reference.

Connection Authentication Flow

This sequence shows how a new connection is accepted, matched against pg_hba.conf and authenticated before a backend serves queries.

sequenceDiagram
    participant C as Client (libpq)
    participant PM as postmaster
    participant BE as New backend
    participant HBA as pg_hba.conf rules

    C->>PM: TCP connect
    PM->>BE: fork backend for this connection
    C->>BE: SSLRequest or GSSENCRequest (or direct TLS, PG 17+)
    BE-->>C: TLS handshake if the server accepts TLS
    C->>BE: StartupMessage (user, database, protocol 3.0 or 3.2)
    BE->>HBA: first matching line by type, database, user, address
    HBA-->>BE: method (scram-sha-256, cert, oauth, reject)
    BE-->>C: AuthenticationSASL (SCRAM-SHA-256[-PLUS])
    C->>BE: client-first / client-final messages (proof)
    BE-->>C: server-final (server signature) + AuthenticationOk
    BE-->>C: ParameterStatus, BackendKeyData, ReadyForQuery

pg_hba.conf -- Host-Based Authentication

pg_hba.conf controls which clients can connect, from which addresses, and how they authenticate. It is the first gate in PostgreSQL's access control chain. Rules are evaluated top to bottom and the first matching rule wins, with no fall-through. If a rule matches and authentication fails, the connection is rejected. Put specific rules before broad ones and end with an explicit reject. Changes take effect on reload. pg_hba_file_rules shows how the server parsed the file.

SCRAM-SHA-256 Authentication

SCRAM-SHA-256 (PG 10+, the default password_encryption since PG 14) is the recommended password method:

  • Mutual authentication: the client proves it knows the password, and the server proves it holds the stored verifier.
  • Salted, iterated hashing: pg_authid.rolpassword stores a salted SCRAM verifier, not the password or a plain hash.
  • Replay protection: each exchange uses fresh nonces.
  • Channel binding (PG 11+, SCRAM-SHA-256-PLUS): ties the exchange to the TLS session. With channel_binding=require on the client, a man-in-the-middle cannot relay the authentication.

MD5 is weaker. A stolen MD5 hash is enough to log in, because it is effectively password-equivalent. MD5 is deprecated in 18, and 19 warns after every successful MD5 login.

Role System

PostgreSQL has one role concept: "users" and "groups" are both roles. A role with LOGIN can connect. Roles inherit privileges from roles they are members of (INHERIT). Objects have one owner, and only the owner (or a superuser) can alter or drop them. Privileges are granted on databases, schemas, tables, sequences, functions and other objects. ALTER DEFAULT PRIVILEGES covers objects created later. Predefined roles such as pg_read_all_data, pg_monitor, pg_maintain (17) and pg_signal_autovacuum_worker (18) grant common capabilities without superuser.

Row-Level Security (RLS)

RLS attaches per-row predicates to a table. The rewriter adds them to every query, so applications cannot forget the filter. It suits multi-tenant schemas and regulatory isolation. Two things to keep in mind: the table owner bypasses RLS unless FORCE ROW LEVEL SECURITY is set, and superusers and BYPASSRLS roles always bypass it. Policies are only as trustworthy as the session variable or role that feeds them, so set app.current_tenant-style settings on the server side (in the pooler or middleware), never from client input.

SSL / TLS Encryption

PostgreSQL encrypts client and replication traffic with TLS (OpenSSL). The minimum version defaults to TLS 1.2, and PG 18 adds TLS 1.3 cipher-suite control (ssl_tls13_ciphers). The client's sslmode decides whether the server is authenticated. Only verify-full checks the CA and the host name, so require alone still allows a man-in-the-middle. PG 17 adds sslnegotiation=direct, which starts TLS immediately without the PostgreSQL-specific SSLRequest round trip.

Auditing with pgAudit

Core logging (log_statement) records statement text but not which objects were touched, and it misses statements inside functions. The pgaudit extension adds session and object audit logging to the standard server log, classified by class (READ, WRITE, DDL, ROLE, and others). That output is what compliance regimes usually expect. Example log lines:

LOG:  AUDIT: SESSION,1,1,WRITE,INSERT,TABLE,public.orders,INSERT INTO orders (id, total) VALUES (42, 99.50);,<none>
LOG:  AUDIT: OBJECT,2,1,READ,SELECT,TABLE,public.customers,SELECT * FROM customers WHERE id = 42,<none>

Encryption at Rest and Transparent Data Encryption (TDE)

Community PostgreSQL has no built-in TDE. The options are:

  • Column-level: pgcrypto functions, with keys held by the application. Upgrade to 18.6 or a later minor: earlier minors could silently skip encryption for PGP ciphers that OpenSSL rejected (CVE-2026-14663).
  • Block or filesystem level: LUKS/dm-crypt, fscrypt, or cloud volume encryption (AWS EBS, GCP PD, Azure Disk). Transparent to PostgreSQL and the most common choice.
  • TDE distributions: Percona pg_tde (the tde_heap access method, encrypts tuples, WAL and indexes, requires Percona Server for PostgreSQL 17/18), EDB's TDE in its commercial distributions, and Cybertec's PostgreSQL TDE. These tie you to a specific distribution.

Performance Characteristics

What drives PostgreSQL performance, and how to read the rough figures in Reference — Benchmarks:

  • Working set vs. memory. Once hot data fits in shared_buffers plus the OS cache, read-only TPS is limited by CPU and locking, not by I/O. That is why read-only pgbench numbers grow roughly with core count.
  • Commit latency. Write TPS for small transactions is limited by WAL flush (fsync) latency, which makes NVMe and battery-backed caches matter. Group commit and synchronous_commit = off, which trades a small durability window for speed, change the picture.
  • Connections. Past a few hundred active backends, context switching, snapshot computation and lock contention reduce throughput. A pooler keeps the number of active backends close to the core count.
  • Synchronous replication adds a network round trip to every commit. Keep synchronous standbys in the same region, or use quorum (ANY 1) commits.
  • AIO (18) helps scan-heavy and VACUUM-heavy workloads on high-latency storage most. It does not speed up point lookups that are already cached.

Sources