Skip to content
Cloud, DevOps & Infrastructure

PostgreSQL interview questions and answers

79 questions, 30 with code. About 7 h 9 min to read them all.

By TestLab · Updated

Plan for an interview
79 questions

Track what you know and what to revise.

Sign in to track progress

You can open a few full answers without signing in. Sign in to read all

1

What is PostgreSQL and how does it differ from MySQL?

PostgreSQL is an open-source relational database distinguished by SQL standards support, rich data types, extensibility, and MVCC concurrency. MySQL is also a mature transactional database, especially common in web applications, but PostgreSQL generally provides broader indexing, procedural extensibility, and more flexible server-side programming.

Read full answer

Applies toPostgreSQL 16/17 and MySQL 8.4 LTS (InnoDB)

Core Capabilities

  • Relational model: PostgreSQL organizes data into tables, schemas, rows, typed columns, foreign keys, views, sequences, and indexes.

  • ACID transactions: It supports rollback, savepoints, multiple isolation levels, deferred constraints, and generally transactional DDL.

  • Rich types: Built-in types include UUID, JSONB, arrays, ranges, network addresses, geometric types, NUMERIC, and composite types.

  • Advanced indexes: PostgreSQL supports B-tree, hash, GiST, GIN, BRIN, partial, expression, and multicolumn indexes.

  • Extensibility: Installable extensions such as PostGIS, pgcrypto, TimescaleDB, and pgvector add specialized functionality without modifying core server code.

Architecture and Concurrency

  • Processes and memory: Each client connection normally has a PostgreSQL backend process. Backends coordinate through shared memory containing the shared buffer pool and heavyweight-lock structures; the operating system may also cache relation pages.

  • WAL and checkpoints: Changes are represented as write-ahead log records. PostgreSQL acknowledges commits only after satisfying the configured WAL durability level. WAL supports crash recovery, replication, and PITR; checkpoints flush dirty buffers and provide restart points that reduce recovery work.

  • MVCC: PostgreSQL preserves row versions so readers normally use a consistent snapshot without blocking ordinary writers. Long transactions retain obsolete tuples, increasing table and index bloat and preventing vacuum from reclaiming space.

  • Locks and planning: Writers still take tuple-level locks, DDL takes stronger locks, and serializable transactions can use predicate locks. EXPLAIN (ANALYZE, BUFFERS) compares estimates with actual execution, although ANALYZE runs the query and can add production load.

PostgreSQL Compared with MySQL

Area PostgreSQL MySQL 8.4
Design Relational with strong object-relational features and extensibility Relational OLTP system optimized especially for web workloads
Data types Arrays, ranges, JSONB, composites, and extension-defined types Native JSON, spatial types, ENUM, and SET, but no native array type
Indexes B-tree, hash, GiST, GIN, BRIN, partial, expression, and multicolumn B-tree, full-text, spatial, functional, and multicolumn indexes
DDL Generally transactional and rollback-able Individual DDL statements are atomic, but implicit commits mean DDL is generally not transactionally rollback-able
Concurrency MVCC with row, tuple, predicate, and advisory locks InnoDB MVCC with row, gap, next-key, and metadata locks
Optimizer Cost-based optimizer with extensible types and advanced join strategies Mature cost-based optimizer with histogram-based selectivity
Partitioning Declarative range, list, and hash partitioning with subpartitioning Range, list, and hash partitioning
Replication Synchronous or asynchronous physical streaming replication and logical replication Binary-log source/replica replication, asynchronous by default with optional semi-synchronous mode
PITR Base backups plus continuously archived WAL Physical or logical backups plus retained binary logs
Extensibility Installable extensions and multiple procedural languages Plugin-oriented storage engines, but fewer general-purpose extensions
Licensing PostgreSQL License Community Server is GPL v2; commercial editions have separate terms

Both databases are production-grade and can support the same core workload. PostgreSQL often fits complex schemas, geospatial data, analytics, and specialized extensions; MySQL is frequently selected for straightforward web services or organizations already operating it at scale.

Operational Failure Modes

  • Bloat: Updates and deletes grow indexes and leave dead tuples. pg_stat_user_tables.n_dead_tup is an estimate, while pg_stat_progress_vacuum shows active vacuum progress.

  • Transaction ID wraparound: Anti-wraparound autovacuum is automatically prioritized. Monitor age(datfrozenxid) and age(relfrozenxid), then investigate old snapshots, long transactions, or blocked vacuum rather than routinely forcing manual vacuum.

  • Lock contention: pg_stat_activity.wait_event_type and wait_event identify waits, while pg_locks shows blockers and blocked processes.

  • Replication lag: On a primary, pg_stat_replication exposes replica state and WAL lag. Heavy write traffic, network latency, and slow replicas can cause delay.

  • Connection storms: Every connection consumes a backend and memory. Use PgBouncer or an application pool, and ensure the selected pooling mode is compatible with session state and advisory locks.

  • Capacity exhaustion: Track WAL generation, temporary files, checkpoint activity, disk latency, and free space. A failed WAL archive first causes pg_wal growth; a full data or WAL filesystem can stop writes.

  • Partitioning mistakes: Poor predicates prevent partition pruning, many partitions increase planning overhead, and partition maintenance can require disruptive locks.

Production Practices

  • Backups and recovery: Use pg_basebackup or pgBackRest and archive WAL to separate durable storage. Regularly test full restores and measure actual recovery time and recovery point.

  • Security: Require TLS, use SCRAM authentication, apply least privilege, and consider row-level security. Keep superuser access separate and store credentials in a secrets manager.

  • Monitoring: Inspect pg_stat_activity, pg_stat_database, pg_stat_user_tables, pg_stat_wal, pg_stat_progress_vacuum, and pg_stat_replication, alongside disk, filesystem, and network telemetry.

  • Upgrades: Minor PostgreSQL upgrades require restarting the server processes; major upgrades normally use pg_upgrade or a dump-and-restore procedure. Validate extension compatibility, rehearse rollback, and test restores before production deployment.

2

How does PostgreSQL process architecture work?

PostgreSQL uses a process-per-connection architecture: a single supervisor process (the postmaster) listens for client connections and forks a dedicated backend process for each one, while a small set of long-lived background workers handle checkpointing, WAL flushing, vacuuming, and replication. All of these processes share a common region of shared memory for buffers, locks, and statistics.

Read full answer

Applies toPostgreSQL 16 and 17 on Linux.

How It Works

  • The postmaster is the cluster’s main supervisor process. It owns the data directory, binds the listening socket (default port 5432), and is the only process that forks new backends and background workers.

  • When a client connects, the postmaster authenticates it, then fork()s a backend process that executes that session’s queries. Backends are independent OS processes with their own address space, but they attach to the shared memory segment.

  • Background workers are forked at startup or on demand and run for the life of the cluster (or a replication slot’s lifetime).

  • Shared memory holds shared_buffers, the WAL buffers, the lock manager tables, the process array, and (since PostgreSQL 15) cumulative statistics, so backends coordinate through latches and LWLocks rather than message passing.

Key Processes

Process Role
postmaster Supervisor, listener, process spawner
postgres: user db ... (backend) One per client connection; parses, plans, executes queries
checkpointer Writes dirty shared buffers to disk at checkpoints
background writer Steadily flushes dirty pages to reduce checkpoint spikes
walwriter Flushes WAL records to disk
autovacuum launcher Schedules autovacuum workers per database
autovacuum worker Reclaims dead tuples, updates planner stats, prevents XID wraparound
logical replication launcher/worker Applies or sends logical replication changes
parallel worker Short-lived helpers for parallel query plans

Shared Memory and Memory Model

  • shared_buffers is the page cache shared by every backend; sizing it (typically 25% of RAM) is the single biggest tuning lever.

  • Each backend also has private memory: work_mem per sort/hash node, maintenance_work_mem for vacuum and index builds, and temp_buffers for temporary tables.

  • Because memory is per-backend, a high max_connections with large work_mem can exhaust RAM even when idle connections do nothing.

Connection Lifecycle and Fork Cost

  • Forking a backend is cheap on Linux but not free; thousands of short-lived connections cause CPU and memory pressure and can exhaust max_connections (default 100).

  • Production deployments almost always put PgBouncer (or pgpool) in front to multiplex many client connections onto a smaller pool of server connections, especially in transaction pooling mode.

  • Long-running idle backends still hold a process slot and shared-memory state, so application connection leaks show up as process buildup.

Operational Considerations

  • Process limits: max_connections caps backends; background workers are separate and governed by max_worker_processes, max_parallel_workers, and max_logical_replication_workers.

  • OOM risk: Linux OOM killer can terminate a backend or the postmaster; set vm.overcommit_memory and size memory conservatively, and monitor per-process RSS.

  • Autovacuum starvation: if workers cannot keep up, tables bloat and transaction IDs approach wraparound; the launcher spawns workers up to autovacuum_max_workers.

  • Checkpoint spikes: tune checkpoint_timeout, max_wal_size, and checkpoint_completion_target so the checkpointer spreads I/O.

  • Upgrades: major version upgrades restart the whole cluster (new postmaster), so plan connection draining and use pg_upgrade with --link for speed.

Monitoring

  • pg_stat_activity shows every backend, its state, query, and wait event; long idle in transaction sessions are a classic cause of bloat and lock retention.

  • pg_stat_bgwriter reports checkpoint and background-writer activity; rising buffers_backend indicates backends are doing their own writes.

  • OS-level ps, /proc/<pid>, and pgrep -f postgres confirm process counts; alert when backend count approaches max_connections.

  • pg_stat_database and pg_stat_user_tables (dead tuples, last autovacuum) track vacuum health.

Failure Modes

  • A crashing backend triggers a cluster-wide restart by the postmaster; if a critical background process dies, the postmaster shuts the cluster down. Clients see connection drops.

  • Killing the postmaster stops the whole cluster; always use pg_ctl stop or systemd.

  • Shared-memory sizing failures at startup usually mean kernel shm limits (or cgroup limits) are too small.

3

What are PostgreSQL shared buffers and how do you size them?

Shared buffers are PostgreSQL’s main in-memory cache of data pages: every read and write of table and index data passes through this pool, so its size largely determines how much of the working set stays in RAM.

Read full answer

Applies toPostgreSQL 16 and 17 on Linux.

What Shared Buffers Are

PostgreSQL uses a process-per-connection architecture where all backends share a single memory segment, the shared buffer pool. When a query needs a page, the backend checks this pool first; on a miss, PostgreSQL reads the page from disk into a free buffer (evicting an old one if needed) and may also rely on the OS page cache. Writes are not flushed immediately: dirty buffers are written by background writer and checkpointer processes, and durability is guaranteed by the WAL.

Key points:

  • Default is 128 MB, which is far too small for almost any production workload.

  • The pool is allocated at startup, so changing shared_buffers requires a server restart.

  • It is separate from effective_cache_size, which is only a planner hint about total cache (shared buffers plus OS cache) and costs no memory.

  • Very large values can hurt: beyond a point, PostgreSQL spends more time managing the pool and the OS cache shrinks, causing double caching and worse performance.

Sizing Guidelines

A common starting point is 25% of total RAM, then tune with measurements. Practical rules:

  • Leave enough RAM for the OS page cache, connection memory, work_mem (per sort/hash), maintenance_work_mem, and other processes.

  • On systems with huge pages enabled, large pools (tens of GB) allocate faster and reduce TLB misses.

  • Watch for diminishing returns: if the buffer cache hit ratio is already above ~99%, increasing further rarely helps.

  • For read-heavy OLTP, 25-40% of RAM is typical; for analytics with large sequential scans, a smaller pool plus OS cache often works better.

Monitoring

Use pg_stat_database and pg_buffercache (extension) to measure effectiveness:

SELECT datname,
       blks_hit,
       blks_read,
       round(100.0 * blks_hit / nullif(blks_hit + blks_read, 0), 2) AS hit_ratio
FROM pg_stat_database;

A sustained hit ratio below ~95% on the primary workload usually signals undersizing or a missing index. pg_buffercache shows which relations occupy the pool, helping spot a single large table evicting everything else.

Configuration

Set in postgresql.conf (or via ALTER SYSTEM), then restart:

shared_buffers = 4GB
effective_cache_size = 12GB
huge_pages = try

Best Practices and Pitfalls

  • Restart required: plan the change in a maintenance window; use pg_ctl restart or your orchestrator.

  • Don’t set it to most of RAM: leaves no room for OS cache and per-backend memory, risking OOM kills.

  • Enable huge pages for pools above a few GB to reduce page-table overhead; set vm.nr_hugepages accordingly.

  • Measure before and after: capture blks_hit/blks_read and query latency, not just the setting.

  • Combine with effective_cache_size: keep it at roughly 50-75% of RAM so the planner chooses index scans appropriately.

  • Watch for bloat: a huge pool can mask table bloat; monitor pg_stat_user_tables and run VACUUM regularly.

4

What is PostgreSQL work_mem and why can it be too high?

work_mem is PostgreSQL’s per-query-node memory allowance for operations such as sorts and hash tables; it is not a total memory cap for the server or database. It can be too high because many concurrent sessions and eligible nodes can each consume an allowance, allowing aggregate memory use to exhaust the host and cause swapping, latency spikes, or OOM termination.

Read full answer

Applies toPostgreSQL 16/17

How It Works

work_mem controls process-local memory used by memory-intensive executor nodes, including sorts, hash aggregation, hash joins, and some materialization or distinct processing. Each eligible node can have its own allowance, so one query can consume several times the nominal work_mem value.

Hash-related nodes have an additional nuance: hash_mem_multiplier is applied to their memory usage. Their effective allowance can therefore be work_mem × hash_mem_multiplier, while sort nodes use work_mem directly.

Related settings have different purposes:

Parameter Purpose
work_mem Base memory allowance for query executor operations such as sorts
hash_mem_multiplier Multiplier applied to hash-related memory usage
maintenance_work_mem Memory used by operations such as VACUUM and index creation
temp_buffers Memory used to cache temporary files; it is not a total temporary-file size limit
shared_buffers PostgreSQL’s shared buffer cache

If a sort cannot fit within its allowance, PostgreSQL performs an external merge sort using temporary files. This protects total memory consumption but increases disk I/O and query latency. The planner uses the applicable memory settings in its cost estimates, although EXPLAIN without ANALYZE does not report actual memory consumption.

Why High Values Are Dangerous

work_mem is allocated on demand, so setting it high does not immediately reserve that amount of RAM. The danger is aggregate demand when many sessions execute expensive queries concurrently.

For example, 100 sessions each performing a 128 MB sort can approach 12.8 GB before process overhead and additional query nodes are included. Hash operations may consume more because of hash_mem_multiplier, and parallel workers can each use the inherited memory setting while executing separate portions of the query.

Production consequences include:

  • Host memory exhaustion: The Linux OOM killer may terminate PostgreSQL processes. Killing the postmaster can cause a cluster restart and interrupt all connections.

  • Swap usage: Paging can produce severe latency and create a cascading incident.

  • Temporary-file pressure: Spilled sorts and hash batches increase storage I/O and can saturate disks.

  • Correlated failures: Many sessions can fail together rather than only the workload responsible for the pressure.

  • Poor resilience: A setting optimized for one reporting query can make the database unstable under normal concurrency.

Choosing a Safe Value

Start with the server’s existing value and change it only when representative measurements show that spills are expensive. Consider workload concurrency rather than choosing a value from a single query’s EXPLAIN output.

  • Use a conservative value for high-concurrency OLTP workloads.

  • Allow a higher session- or role-level value for reporting queries whose concurrency is bounded.

  • Prefer narrowly scoped session, role, or database settings when workload classes differ materially.

  • Evaluate hash_mem_multiplier separately for hash-heavy analytical workloads.

  • Do not use work_mem to compensate for missing indexes, poor join conditions, or unbounded result sets.

A connection pool can limit total server concurrency, but only if its configured server-side connection limit is appropriate. Workload queues can further restrict the number of memory-heavy analytical queries running simultaneously.

Production Monitoring

Monitor temporary-file rates through pg_stat_database. Its temp_files and temp_bytes counters are cumulative, so trends and deltas are more useful than absolute totals. Temporary files can also come from operations other than work_mem spills, so correlate them with query behavior.

Use pg_stat_activity to identify sessions waiting in the temp_file_read or temp_file_write wait events. Run EXPLAIN (ANALYZE, BUFFERS) in a representative environment to identify external sorts and hash spills, while noting that ANALYZE executes the statement.

Also monitor host-level RSS, memory pressure, swap activity, OOM events, disk utilization, temporary-file throughput, and connection saturation. A temporary-file event is not itself an incident; it becomes significant when it correlates with latency, resource exhaustion, or repeated expensive queries.

Best Practices and Failure Modes

  • Test under concurrency: Benchmark memory-heavy workloads with realistic session counts rather than tuning from one query alone.

  • Bound expensive work: Use a pooler, workload queue, or external Linux cgroup controls to prevent many large sorts or hashes from overlapping.

  • Set execution limits: Use statement timeouts to stop runaway queries, while recognizing that they do not impose a direct memory limit.

  • Restrict untrusted changes: PostgreSQL 15 and later support parameter-specific privileges, so untrusted roles can be prevented from setting work_mem arbitrarily.

  • Change settings gradually: Compare latency, temporary I/O, and host memory before and after each adjustment.

  • Avoid global inflation: A large global value may improve one analytical query while making the entire cluster less resilient during normal production load.

5

What is PostgreSQL's write-ahead log (WAL)?

PostgreSQL’s write-ahead log (WAL) is a sequential transaction log that records changes to data and indexes before those changes are considered durable. It enables crash recovery, replication, and point-in-time recovery (PITR).

Read full answer

Applies toPostgreSQL 16/17

How WAL Works

  • A backend modifies pages in shared buffers and creates WAL records describing those changes.

  • WAL records are written to WAL buffers and then flushed to WAL segment files in pg_wal.

  • With the normal durable configuration, PostgreSQL flushes relevant WAL before reporting a transaction as committed.

  • Each WAL record has a log sequence number (LSN). LSNs identify a position in the WAL stream and are used for replication, backup coordination, and recovery.

  • During crash recovery, PostgreSQL replays WAL from the last consistent checkpoint, repeating committed changes and discarding incomplete transactions.

WAL is not a replacement for relation files. It records changes; the primary table and index data is eventually written into heap and index files. This separation allows write-ahead logging to provide durability without synchronously writing every modified page to disk on every commit.

WAL and Checkpoints

A checkpoint records a recovery position and ensures that all dirty shared buffers required for recovery are written to disk. The checkpoint also records information needed to safely remove obsolete WAL.

After a checkpoint, PostgreSQL can reuse or remove older WAL segments, subject to replication-slot, backup, and archiving requirements.

Important settings include:

  • wal_level: Controls how much information is written to WAL. replica supports physical streaming replication; logical adds logical-decoding capability.

  • archive_mode and archive_command: Control whether completed WAL is copied to a durable archive for PITR.

  • max_wal_size: Guides checkpoint frequency by influencing how much WAL may accumulate.

  • min_wal_size: Defines a target amount of WAL retained for reuse.

  • full_page_writes: Protects recovery after a checkpoint by writing a full page image when a page is first modified.

  • synchronous_commit: Controls whether commit waits for WAL flush. Disabling synchronous waiting improves performance but can lose recently committed transactions after an operating-system or power failure.

Replication and Point-in-Time Recovery

For physical replication, a standby receives WAL through streaming or WAL shipping and replays it. The primary and standby may be synchronous or asynchronous, depending on availability and durability requirements.

For PITR, combine:

  1. A base backup, such as one produced by pg_basebackup or a backup tool.

  2. A continuous, reliable archive of WAL segments.

  3. A restore procedure that replays WAL up to a chosen timestamp or LSN.

WAL archives are often stored on separate storage from the PostgreSQL data directory. archive_command must be idempotent: if the archive operation is retried, it must not fail merely because the target already exists.

Operational Monitoring

Useful PostgreSQL 16 views and functions include:

  • pg_stat_wal: WAL record and byte counts, buffer-full events, write activity, and synchronization timing.

  • pg_stat_replication: Primary-side replication state and WAL positions.

  • pg_stat_archiver: Archive success or failure, including the last failed WAL file.

  • pg_stat_activity: Long-running transactions that may delay cleanup and increase bloat.

  • pg_current_wal_lsn(), pg_wal_lsn_diff(), and pg_walfile_name(): LSN inspection and conversion to WAL filenames.

  • pg_ls_waldir(): Files currently present in the WAL directory.

Operators can use psql to inspect wal_level, query the current LSN and corresponding WAL filename, and review pg_stat_archiver. Example result shapes are:

wal_level pg_current_wal_lsn() pg_walfile_name()
replica 0/5000060 000000010000000000000005

The LSN and filename are examples and vary between systems.

Production Failure Modes and Practices

  • WAL disk exhaustion: Monitor the filesystem containing pg_wal; a full disk can stop writes and cause an outage.

  • Archive failures: Alert on pg_stat_archiver.failed_count and last_failed_wal; test archive restores regularly.

  • Replication lag: Monitor WAL-position differences and replay progress, not just network connectivity.

  • Replication-slot retention: An inactive slot can prevent WAL removal and fill the primary’s disk.

  • Checkpoint pressure: Excessive dirty buffers, low memory, or frequent checkpoints can increase I/O and WAL volume.

  • Vacuum interaction: Long-running transactions can prevent vacuum cleanup and increase table and index bloat, which can in turn increase write and WAL activity.

Use encrypted connections and restricted access for replication and archive credentials. Keep base backups and WAL archives on independent, durable storage, and regularly verify both backup integrity and recovery procedures.

6

What are PostgreSQL checkpoints and how do they impact performance?

A checkpoint is a periodic event where PostgreSQL flushes all dirty data pages from shared buffers to disk and writes a checkpoint record to the WAL, establishing a safe recovery starting point and allowing older WAL segments to be recycled.

Read full answer

Applies toPostgreSQL 16 and 17

How Checkpoints Work

  • The checkpointer process periodically writes dirty buffers to data files and fsyncs them.

  • A checkpoint record is appended to the WAL; recovery only needs to replay WAL from the last checkpoint.

  • Full page writes (FPW): the first modification to a page after a checkpoint writes the entire page image to WAL, protecting against torn pages from OS-level partial writes.

  • After a checkpoint, WAL segments older than the previous checkpoint can be recycled, unless archiving or replication retains them.

What Triggers a Checkpoint

  • checkpoint_timeout (default 5 minutes): time-based trigger.

  • max_wal_size (default 1 GB): WAL-volume trigger; if WAL grows beyond this, a checkpoint is forced.

  • min_wal_size: minimum WAL retained to avoid frequent recycling.

  • Manual: CHECKPOINT command (superuser).

Performance Impact

  • Checkpoints concentrate I/O; if they complete too quickly, disk latency spikes for concurrent queries.

  • checkpoint_completion_target (default 0.9) spreads checkpoint writes over that fraction of the interval, smoothing I/O.

  • Forced checkpoints (checkpoints_req high) indicate max_wal_size is too small for the workload, causing extra I/O and WAL churn.

  • FPW increases WAL volume right after each checkpoint, temporarily raising write amplification.

  • Slow or stalled checkpoints can delay WAL recycling, increasing disk usage and, in extreme cases, contributing to bloat-related pressure.

Monitoring

  • PostgreSQL 16: pg_stat_bgwriter exposes checkpoints_timed, checkpoints_req, checkpoint_write_time, checkpoint_sync_time, and buffers_checkpoint.

  • PostgreSQL 17: checkpointer statistics moved to the dedicated pg_stat_checkpointer view.

  • pg_stat_wal shows wal_write and wal_sync counts and timings.

  • Alert when checkpoints_req is a large fraction of total checkpoints, or when checkpoint_sync_time is high.

Best Practices

  • Size max_wal_size so normal load triggers time-based, not forced, checkpoints.

  • Keep checkpoint_completion_target around 0.9 on most workloads.

  • Place WAL and data on fast, low-latency storage; monitor I/O saturation during checkpoints.

  • Avoid very short checkpoint_timeout values; they increase checkpoint frequency and FPW overhead.

  • Ensure archiving and replication slots do not retain WAL indefinitely, which can stall recycling.

7

What are the main data types in PostgreSQL 16 and 17?

PostgreSQL data types determine what values a column can store, how they are represented on disk, and which operators and indexes support them. PostgreSQL provides scalar, array, range, JSON, spatial, and user-defined types for structured and semi-structured data.

Read full answer

Applies toPostgreSQL 16 and 17

Built-in Type Families

Category Common types Characteristics and typical uses
Numeric smallint, integer, bigint, numeric, real, double precision integer uses 4 bytes and covers most identifiers and counters. bigint supports much larger values. numeric stores exact fractional values and is appropriate for financial calculations. real and double precision use floating-point approximations.
Character character, varchar, text Store strings. varchar(n) and character(n) enforce a maximum or fixed length; text has no declared limit. PostgreSQL treats text and varchar similarly for storage and performance.
Boolean boolean Stores TRUE, FALSE, or NULL; useful for flags and conditional state.
Date/time date, time, timestamp, timestamptz, interval date stores a calendar date. timestamp stores a date and time without time-zone context. timestamptz stores an absolute instant and converts it for display. interval represents durations such as 90 days or 2 hours.
Binary bytea Stores arbitrary binary data, including images or encrypted payloads. PostgreSQL does not encrypt bytea automatically.
UUID uuid Stores a 128-bit identifier. Useful when identifiers must be generated independently across multiple systems.
Network cidr, inet, macaddr Store IP networks, IP addresses, and MAC addresses. inet supports both host addresses and network masks.
JSON json, jsonb json preserves the original JSON text. jsonb parses and stores a decomposed binary representation, supports equality, and is generally preferable for querying and indexing.
Arrays integer[], text[], and other array types Store ordered collections of values of the same type. Arrays are flexible but can produce large rows and awkward query plans.
Ranges int4range, numrange, tsrange, tstzrange, daterange Represent lower and upper bounds, optionally with inclusion and exclusion semantics. Useful for scheduling and temporal constraints.
Geometric point, line, box, path, polygon, circle Provide basic geometric primitives. PostGIS is usually preferred for production geospatial workloads.
Full-text search tsvector, tsquery Represent analyzed documents and search expressions.
Enumerated user-defined ENUM Stores one of a fixed, ordered set of labels.
Composite user-defined row types Treat multiple columns as a single value.

Type Selection Considerations

  • Prefer timestamptz for event times: It represents an unambiguous instant across time zones. A timestamp without time zone may be appropriate when a local wall-clock time is intentionally meaningful, such as a store’s opening hours.

  • Use numeric for exact money: Avoid floating-point types for currency because binary floating-point cannot represent many decimal fractions exactly. The built-in money type is locale-sensitive and is generally avoided in portable applications.

  • Use text for most strings: PostgreSQL does not automatically save storage compared with varchar. Add CHECK constraints or a DOMAIN when a business rule requires a maximum length.

  • Use identity columns for generated keys: GENERATED ALWAYS AS IDENTITY is generally preferred over the legacy serial pattern because it provides explicit identity semantics.

  • Choose bigint deliberately: It is common for externally supplied identifiers, event counters, and large application keys. Integer identifiers usually consume less space and create smaller indexes.

  • Distinguish NULL from empty values: NULL means unknown or absent; '' and 0 are known values. Use IS NULL rather than = NULL.

Specialized and User-Defined Types

  • JSONB: Appropriate for documents whose fields vary. jsonb supports GIN indexes, containment operators, and expression indexes, but frequently updated JSON documents can increase write amplification and storage.

  • Arrays: Useful for small, bounded collections. Normalize repetitive data into child tables when records need independent updates, foreign keys, or efficient joins.

  • Ranges: Well suited to reservations, billing periods, and validity windows. Exclusion constraints can prevent overlapping ranges, such as duplicate room bookings.

  • ENUM: Compact and self-documenting for small, stable label sets. Adding or removing labels requires schema migrations, and label changes should be treated as data-model changes rather than routine configuration.

  • Domains: Apply reusable constraints to an existing base type without introducing a new storage representation.

  • Extensions: citext provides case-insensitive text, pg_trgm supports fuzzy matching, ltree stores hierarchical paths, hstore stores key-value data, and PostGIS provides advanced geospatial types.

Indexing by Data Type

  • B-tree: Default index for text, numeric, UUID, date/time, and other sortable types. It efficiently supports equality, ranges, sorting, and prefix matching with text_pattern_ops when required.

  • GIN: Common for jsonb, arrays, and full-text search. It is flexible but usually has higher write and storage costs than B-tree.

  • GiST: Useful for ranges, geometric values, and some nearest-neighbor searches, often with extension-specific operator classes.

  • BRIN: Effective for very large tables whose physical row order correlates with indexed values, such as append-only logs with increasing creation times.

Production Guidance

  • Choose explicit types based on business semantics, not merely the values currently present.

  • Add constraints so invalid states cannot be stored. Use foreign keys, CHECK, UNIQUE, and exclusion constraints where appropriate.

  • Monitor relation and index sizes with pg_total_relation_size() and pg_relation_size(). Use pg_stat_user_tables to track dead tuples and vacuum/analyze activity, and pg_statio_user_tables for block I/O counters. Frequent updates and deletes can create bloat and dead tuples that require VACUUM.

  • Avoid very wide text, jsonb, array, or composite columns in high-volume tables; PostgreSQL stores larger values using TOAST when necessary.

  • Test type migrations on production-sized data because rewriting columns, changing types, or adding indexes can generate substantial WAL, I/O, and temporary disk usage.

8

When should PostgreSQL use JSONB instead of JSON or columns?

Use JSONB when data is structured but has variable attributes, optional fields, or nested metadata that must be queried efficiently. Use separate typed columns when fields are stable, validated, joined, constrained, or central to the business model; use json mainly when the exact original text and formatting must be preserved.

Read full answer

Applies toPostgreSQL 16 and 17

Choosing the right data model

Requirement Separate columns jsonb json
Fixed business attributes Best Possible, but weaker modeling Poor
Required values Native constraints CHECK or application validation CHECK or application validation
Foreign-key relationships Native constraints Not directly supported Not directly supported
Variable or nested attributes Requires schema changes Best fit Best only for archival text
Containment and JSON-path matching Ordinary relational predicates GIN and expression indexes Cast to jsonb for containment and @?/@@; simple ->/->> remains available
Preserve formatting, key order, and original representation Not applicable No Yes
Schema evolution Migration required Flexible Flexible
Storage and write overhead Usually lowest Higher, especially with indexes Stores the original text

Use separate columns for core entities such as customer_id, status, created_at, and monetary amounts. These columns provide strong types, statistics, foreign keys, unique constraints, and efficient joins.

Use jsonb for extensions, integration payloads, feature flags, audit metadata, or objects whose shape differs by type. A common design stores stable attributes in columns and variable attributes in jsonb.

JSONB Versus JSON

json preserves the exact input text, including whitespace and object key order. It also retains duplicate object keys. This makes it useful for storing raw API responses, configuration documents, or audit data that must remain byte-for-byte equivalent to the input.

jsonb parses the input into PostgreSQL’s internal binary representation:

  • Original formatting is discarded.

  • Object key order is not preserved.

  • Duplicate object keys are resolved, with the last value retained.

  • Values can be compared and queried with containment and path operators.

  • The parsed representation can be indexed more effectively.

Because jsonb stores a parsed structure, queries do not need to parse the original JSON text on every access. Converting json to jsonb also normalizes it, but the conversion itself has a cost and prevents later modifications from retaining the original representation.

Indexing and Query Planning

For jsonb workloads, a GIN index is usually the default choice:

  • The default jsonb_ops operator class supports key-existence, containment, and JSON-path operators.

  • jsonb_path_ops supports containment and path-match operations and can produce a smaller, faster index for those specific workloads.

  • Expression B-tree indexes are effective for frequently queried scalar values, such as payload->>'status'.

For json, containment (@>) and JSON-path matching (@?, @@) require casting to jsonb; simple key extraction (->, ->>) is supported directly and can use a matching expression B-tree index without a cast. An index on the json column itself does not accelerate operators requiring jsonb.

Inspect actual execution behavior with EXPLAIN (ANALYZE, BUFFERS). Watch for sequential scans, heap fetches, expensive expression evaluation, and large GIN indexes. For a json column, a predicate such as payload::jsonb->>'status' needs a matching expression index; otherwise, PostgreSQL may correctly choose a sequential scan.

Generated columns can bridge the two models by exposing a frequently searched jsonb field as a typed, indexed column. Keep such bridges limited to fields with clear, stable semantics.

Production and Operational Consequences

Updating a jsonb value normally writes a new tuple because PostgreSQL uses MVCC. Large documents, frequent updates, and multiple indexes can therefore increase:

  • Heap and index bloat

  • WAL generation and replication traffic

  • Vacuum and index-maintenance work

  • Storage consumption

Monitor pg_stat_user_tables for estimated dead tuples (n_dead_tup), last_autovacuum, and autovacuum_count; track relation growth with pg_total_relation_size over time. With pg_stat_statements enabled, identify expensive query shapes, and use EXPLAIN (ANALYZE, BUFFERS) to distinguish index scans from sequential access. Long-running transactions can prevent dead tuples from being reclaimed and should be investigated separately.

For very large event tables, partition by a stable, typed dimension such as time. Partitioning does not replace jsonb indexes and should not be based on a flexible JSON attribute without clear operational requirements.

Best Practices

  • Model stable, validated, frequently joined data as columns.

  • Use jsonb, not json, when the database must query or index the content.

  • Validate important jsonb fields with CHECK constraints, application validation, or a stable generated-column interface.

  • Avoid indexing every JSON path; index the queries the application actually executes.

  • Measure index size and write amplification before adding a GIN index to an update-heavy table.

  • Keep secrets out of JSON documents unless the same controls protect all database storage, WAL, backups, and replicas.

  • Treat JSON values as untrusted input and bind SQL parameters; never construct SQL by concatenating JSON content.

  • Use PostgreSQL backups and WAL archiving for PITR; jsonb and json participate normally in physical backups, logical decoding, and replication.

9

How do you index and query JSONB in PostgreSQL?

JSONB stores structured documents in a decomposed binary format, enabling equality, containment, key-existence, and JSONPath queries. PostgreSQL typically uses a GIN index for flexible JSONB searches or a B-tree expression index when querying one specific extracted value.

Read full answer

Applies toPostgreSQL 16 and 17

Choosing an Index

Index Best suited for Important behavior
GIN (payload jsonb_ops) Containment, root-level key existence, JSONPath Default JSONB GIN opclass; supports the broadest set of operators and nested containment
GIN (payload jsonb_path_ops) Indexed containment, @?, and @@ Usually smaller and faster, but does not support ?, ‘? or?&‘
B-tree ((payload ->> 'level')) Equality or ordering on one extracted text value More compact than indexing the entire document when only one field matters

Use a B-tree expression index for queries such as payload ->> 'level' = 'error'. A general GIN index does not efficiently support arbitrary extraction with ->>.

A root-level jsonb_ops GIN index can accelerate nested containment such as payload @> '{"details":{"code":"E42"}}'. Use a GIN expression index only when the query applies the indexed operator directly to the extracted subdocument, such as (payload -> 'details') @> '{"code":"E42"}'. For an extracted nested scalar, use a B-tree expression index such as B-tree ((payload -> 'details' ->> 'code')).

JSONB Query Operators

Operator Purpose
@> Tests whether the left JSONB contains the right JSONB
?, ‘? ,?&‘ Tests for one top-level key, any top-level keys, or all top-level keys
@? Returns whether a JSONPath expression returns any item
@@ Returns whether a JSONPath predicate matches
->, ->> Extracts a JSON value as JSONB or text

Containment is often simpler and more index-friendly than rewriting the same condition as a complex JSONPath expression.

Code Example

CREATE TABLE events (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    payload jsonb NOT NULL
);

INSERT INTO events (payload) VALUES
  ('{"service":"api","level":"error","latency_ms":820,"trace_id":"t-101"}'),
  ('{"service":"api","level":"info","latency_ms":42,"trace_id":"t-102"}'),
  ('{"service":"worker","level":"error","latency_ms":310,"trace_id":"t-103"}');

-- Broad JSONB searching.
CREATE INDEX events_payload_gin
    ON events USING GIN (payload jsonb_ops);

-- Searching one frequently used extracted value.
CREATE INDEX events_payload_level_btree
    ON events ((payload ->> 'level'));

-- Containment query.
SELECT id, payload ->> 'service' AS service, payload ->> 'level' AS level
FROM events
WHERE payload @> '{"service":"api","level":"error"}'::jsonb;

-- Result:
-- id | service | level
--  1 | api     | error

-- Top-level key existence plus an extracted-value condition.
SELECT id
FROM events
WHERE payload ? 'trace_id'
  AND payload ->> 'level' = 'error';

-- JSONPath predicate.
SELECT id
FROM events
WHERE payload @@ '$.service == "api" && $.level == "error"'::jsonpath;

For a sufficiently large table and a selective predicate, the plan may resemble:

Bitmap Heap Scan on events
  Recheck Cond: (payload @> '{"service":"api","level":"error"}'::jsonb)
  Rows Removed by Index Recheck: 0
Bitmap Index Scan on events_payload_gin
  Index Cond: (payload @> '{"service":"api","level":"error"}'::jsonb)

A small table may still receive a sequential scan because that is cheaper. Validate with EXPLAIN (ANALYZE, BUFFERS) and compare estimated versus actual rows.

Production Behavior and Monitoring

  • GIN write amplification: Updating payload adds new index entries. GIN can buffer updates in a pending list, but excessive pending work increases read overhead and storage.

  • Vacuum interaction: Autovacuum processes dead tuples and GIN pending-list entries. Monitor pg_stat_all_tables.n_dead_tup, last_autovacuum, and pg_stat_progress_vacuum.

  • Index usage: Use pg_stat_user_indexes to inspect idx_scan, idx_tup_read, and idx_tup_fetch. These counters are cumulative and should be correlated with workload duration.

  • Bloat inspection: The pgstattuple extension provides pgstatindex and pgstatginindex. For example:

CREATE EXTENSION pgstattuple;
SELECT * FROM pgstatginindex('events_payload_gin'::regclass);
  • GUC tuning: gin_pending_list_limit controls pending-list growth. Change it only after measuring write volume, query latency, and storage usage.

  • Large documents: Oversized JSONB values may be compressed and stored out of line in TOAST, adding detoasting costs. Keep frequently queried attributes in dedicated columns when their access pattern is stable.

  • Maintenance: Rebuild a severely bloated index during a controlled maintenance window with REINDEX INDEX CONCURRENTLY events_payload_gin;.

Security and Best Practices

  • Parameterize both JSONB values and JSONPath expressions; never concatenate user input into SQL or JSONPath text.

  • For example, use jsonb_build_object('service', $1::text) and jsonb_path_exists(payload, $1::jsonpath) with typed parameters.

  • Prefer partial or expression indexes over indexing an entire high-cardinality document when queries consistently use a few fields.

  • Include JSONB index changes in physical backup and PITR testing. Validate restore time because rebuilding large GIN indexes can be expensive.

  • Review query plans after schema and workload changes; an unused GIN index still adds write and vacuum overhead.

10

How do PostgreSQL arrays and composite types work?

PostgreSQL arrays store multiple values of a single SQL type, while composite types define a typed record containing named fields with potentially different types. Both can be stored in columns, passed to functions, returned from queries, and indexed when suitable operator classes are available.

Read full answer

Applies toPostgreSQL 16 and 17

How They Work

  • Array: Every array has one element type. The type declaration does not fix the dimensionality: runtime arrays can contain no initialized dimensions through six dimensions, and each populated dimension has lower and upper bounds.

  • PostgreSQL supports one-dimensional and multidimensional arrays.

  • Elements retain their order, and duplicates are allowed.

  • Array elements can independently be NULL.

  • Common functions include unnest, cardinality, array_length, array_position, and array_agg.

  • Arrays can contain composite values, such as inventory_item[], but this is usually less manageable than normalized tables.

  • Composite type: A composite type groups related fields under one type name.

  • Each field has its own name and SQL type.

  • Every table also has an associated composite row type, but a named composite type is reusable across tables and functions.

  • Named composite types are useful for function signatures, structured records, and values that must retain strict field types.

  • Composite values support field access, row construction, and comparison. Comparisons between values of the same composite type examine their fields in order.

Arrays Versus Composite Types

Feature Array Composite type
Structure Repeated elements Named fields
Element types All elements share one type Each field can have a different type
Field access By position, such as tags[1] By name, such as (item).sku
Typical use Lists, sets, tags, matrix-like data Structured records with fixed semantics
Nested structures Can contain arrays or composite values Fields can contain arrays or other composite values
Validation Element type is enforced Every field type is enforced

An array does not give each element a meaningful identity or description. A composite type is preferable when values such as sku, warehouse, and quantity belong together and have distinct business meanings.

Indexes and Query Planning

  • Array indexes: The default GIN array_ops operator class for text[] supports operators including @>, <@, =, and &&. It is appropriate for containment and overlap searches.

  • Composite indexes: PostgreSQL does not automatically provide a default B-tree operator class for a user-defined composite type, so btree (item) is not valid for this type. Use an expression index on an extracted field, such as (item).warehouse, or normalize the fields into ordinary columns.

  • Planner behavior: A tiny table will normally use a sequential scan because an index is not cost-effective. EXPLAIN (ANALYZE, BUFFERS) shows the chosen plan, actual rows, loops, and buffer usage; ANALYZE executes the statement and therefore adds real workload and overhead.

Code Example

The following SQL runs on PostgreSQL 16 and 17:

CREATE TYPE public.inventory_item AS (
    sku text,
    warehouse text,
    quantity integer
);

CREATE TABLE public.inventory (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    tags text[] NOT NULL DEFAULT '{}'::text[],
    item public.inventory_item
);

CREATE INDEX inventory_tags_gin_idx
    ON public.inventory USING gin (tags);

CREATE INDEX inventory_warehouse_idx
    ON public.inventory (((item).warehouse));

INSERT INTO public.inventory (tags, item)
VALUES (
    ARRAY['critical', 'fragile']::text[],
    ROW('SKU-1', 'west', 12)::public.inventory_item
);

SELECT
    id,
    tags,
    (item).sku,
    (item).warehouse,
    (item).quantity
FROM public.inventory
ORDER BY id;

Example result:

id tags sku warehouse quantity
1 {critical,fragile} SKU-1 west 12

An array can be expanded into rows with unnest:

SELECT
    inventory.id,
    tag.tag
FROM public.inventory AS inventory
CROSS JOIN LATERAL unnest(inventory.tags) AS tag(tag)
ORDER BY inventory.id, tag.tag;

Example result:

id tag
1 critical
1 fragile

A GIN index can support array containment:

SET enable_seqscan = off;

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT id
FROM public.inventory
WHERE tags @> ARRAY['fragile']::text[];

RESET enable_seqscan;

A shortened plan will resemble:

Bitmap Heap Scan on public.inventory
  Recheck Cond: (tags @> '{fragile}'::text[])
  -> Bitmap Index Scan on inventory_tags_gin_idx
       Index Cond: (tags @> '{fragile}'::text[])

SET enable_seqscan = off is only for demonstrating the available index path; it should not be a routine production workaround.

Production Design and Operational Trade-offs

  • Choose arrays for bounded, homogeneous lists: Arrays work well for tags, identifiers, and ordered values. Avoid them when each element needs a foreign key, independent metadata, or frequent updates.

  • Prefer normalized tables for relationships: A junction table provides explicit constraints and simpler joins. Updating one array element creates a new array value and row version; for a large TOASTed value, PostgreSQL may rewrite the entire value.

  • Use composite types for fixed records: They preserve database-level type checking. Adding or renaming fields requires ALTER TYPE, so deployments must coordinate schema changes with application versions.

  • Consider TOAST and size: Large arrays or composite values can be compressed or stored out of line. Excessive sizes increase storage, network transfer, detoasting, and applicable index overhead.

  • Monitor usage and bloat: Review pg_stat_user_indexes for unused indexes and pg_stat_user_tables for scan and update patterns. Use pg_column_size() for individual values and the pgstattuple extension for detailed bloat analysis.

  • Protect data appropriately: Table privileges, column privileges, row-level security, encryption, and backup controls still apply. Composite types also require appropriate USAGE privileges where they are used.

  • Protect backups and replication: pg_dump includes composite type definitions, while physical WAL replication carries schema and data changes. Test logical backup and restore procedures regularly for tables and dependent types.

11

How do PostgreSQL SERIAL and identity columns differ?

The key difference is that SERIAL is PostgreSQL-specific shorthand for a sequence-backed default, while identity columns implement SQL-standard automatic value generation. Both allocate values from sequences, but they differ in dependency management, catalog semantics, and rules for accepting explicitly supplied values.

Read full answer

Applies toPostgreSQL 16 and 17

How They Work

  • SERIAL: PostgreSQL creates a named sequence, gives the column a nextval() default, and records the sequence as OWNED BY that column. Dropping the column consequently drops its sequence. SERIAL also adds NOT NULL, but not UNIQUE.

  • Identity column: PostgreSQL creates an associated sequence as an internal dependency of the table. The sequence cannot be dropped or altered independently; identity options are managed through ALTER TABLE.

  • Available types: SERIAL provides SMALLSERIAL, SERIAL, and BIGSERIAL. Identity columns support smallint, integer, and bigint.

  • Uniqueness: Both make the column NOT NULL, but neither automatically guarantees uniqueness. Add PRIMARY KEY or UNIQUE when identifiers must be unique.

Behavioral Comparison

Feature SERIAL Identity column
Standard PostgreSQL-specific shorthand SQL-standard syntax
Sequence relationship Named sequence OWNED BY the column Internal dependency of the table
Default generation Uses nextval() as the column default Uses an associated identity sequence
Explicit values Accepted GENERATED BY DEFAULT accepts them; GENERATED ALWAYS rejects them by default
Intentional override Insert the explicit value directly Use OVERRIDING SYSTEM VALUE with GENERATED ALWAYS
Sequence administration Can use ALTER SEQUENCE, setval(), or ALTER SEQUENCE ... RESTART Use ALTER TABLE ... ALTER COLUMN ... RESTART WITH
Constraints Automatic NOT NULL; no automatic uniqueness Automatic NOT NULL; no automatic uniqueness
Sequence gaps Expected Expected

GENERATED BY DEFAULT AS IDENTITY therefore resembles SERIAL during ordinary inserts, while GENERATED ALWAYS AS IDENTITY prevents accidental identifier insertion. Supplying an explicit value does not advance either sequence.

Production and Operational Consequences

  • Sequence gaps are normal: Rolled-back inserts and cached sequence values leave unused numbers. Identity columns do not provide gap-free numbering.

  • Explicit values can cause collisions: A manually supplied identifier does not move the sequence forward, so a later generated value may eventually collide with it.

  • High insert rates can contend on a sequence: Increasing the sequence cache can reduce lock contention, at the cost of larger gaps after abnormal termination.

  • Allocation order is not commit order: Generated values do not establish transaction commit order and must not be treated as timestamps.

  • Monitor sequences and tables: pg_stat_user_sequences provides sequence usage information, pg_stat_user_tables exposes insert and update activity, and pg_stat_database provides transaction, conflict, rollback, and deadlock counters.

  • Backups preserve state: pg_dump includes the column definition, sequence definition, and sequence state, allowing a restore to resume near the previous value.

  • Logical replication does not replicate sequences: For UPDATE and DELETE, REPLICA IDENTITY is configured on the publisher to identify old row versions. Subscriber schemas must be compatible, and replicated values must be tested against subscriber constraints, defaults, and identity-generation rules.

Best Practices

  • Prefer GENERATED ALWAYS AS IDENTITY when application code should never supply identifiers.

  • Use GENERATED BY DEFAULT AS IDENTITY when imports or other controlled workflows legitimately need explicit values.

  • Add PRIMARY KEY or UNIQUE when uniqueness is required.

  • Use bigint for high-volume or long-lived tables, while monitoring remaining sequence capacity.

  • Correct identity sequences with ALTER TABLE ... ALTER COLUMN ... RESTART WITH; correct conventional SERIAL sequences with ALTER SEQUENCE or setval().

  • Avoid casually converting existing SERIAL columns to identity. Migrations must preserve sequence position, dependencies, constraints, defaults, and logical-replication behavior.

12

How do UUID primary keys compare with integer keys in PostgreSQL?

A UUID primary key supports independently generated IDs across systems with negligible collision risk, while an integer primary key is smaller and generally more efficient for conventional single-database PostgreSQL workloads. UUIDs avoid shared sequence allocation, but random UUID v4 values have poor B-tree locality and can increase index size, cache pressure, WAL traffic, and write amplification; time-ordered UUID v7 reduces that locality penalty.

Read full answer

Applies toPostgreSQL 16 and 17

Key Trade-offs

Aspect Integer primary key UUID primary key
Storage integer: 4 bytes; bigint: 8 bytes 16 bytes in PostgreSQL’s binary representation
Generation Usually GENERATED ALWAYS AS IDENTITY Application-generated or gen_random_uuid()
Distribution Uses a shared identity sequence by default Can be generated independently by multiple writers
Index locality Sequential values normally pack well Random UUID v4 values create new index pages
Join and foreign-key cost Lower memory and comparison cost Wider indexes, hashes, and foreign-key columns
Sequence contention Usually low, although cache misses and WAL writes can add contention Avoided when generation does not use a shared sequence
Partitioning Often aligns with allocation order Random UUIDs do not provide time-based routing
Enumeration Values expose allocation order and permit enumeration UUID v4 values are not meaningfully enumerable
Collision risk Effectively none with a correctly managed sequence Negligible for ordinary volumes, but collisions remain possible

PostgreSQL stores UUIDs as 16 bytes; the canonical 36-character representation is only their text form. UUID equality lookups can still be fast when the relevant index fits in cache.

Performance and Storage

PostgreSQL B-tree indexes store entries in sorted order. Sequential integer values usually fill index pages before moving onward, producing good locality. Random UUID v4 values have little ordering, so inserts may split pages, dirty more shared buffers, and increase physical I/O.

  • Larger structures: Primary keys, foreign keys, and secondary indexes require more pages.

  • Higher WAL traffic: Wider tuples and index entries increase WAL volume and may increase full-page-image writes.

  • More buffer activity: Random index insertion can produce more dirty-page writes and cache pressure.

  • Hash joins: A 16-byte UUID key occupies twice the raw key space of an 8-byte bigint.

  • Primary-key updates: Changing the key creates a new B-tree entry. Referencing foreign keys change only when configured with ON UPDATE CASCADE; the default NO ACTION rejects the update while references exist.

  • Larger backups: Wider rows and indexes increase base-backup and WAL-archival volumes.

Key width alone does not determine query performance. Selectivity, cardinality, caching, index design, and contention usually matter more.

Generation and Distribution

PostgreSQL identity sequences allocate integer keys centrally. nextval() is nontransactional, so rolled-back inserts leave expected gaps. Concurrent values are unique, and distinct inserts do not row-lock one another on the key; contention can still arise from sequence cache misses, WAL insertion, or unrelated row locks.

Generated values expose allocation order, which may approximate insertion order but can differ from commit order under concurrency. They also permit simple enumeration.

UUIDs are appropriate when records must be created by independent databases or services, an ID must exist before insertion, data from separate systems is merged, or retries need a stable idempotency identifier.

PostgreSQL 16 and 17 provide gen_random_uuid(), which generates UUID version 4 values without a shared sequence. Core PostgreSQL 16/17 does not provide a built-in UUID v7 generator; a trusted application library must generate v7 values. V7 sorts approximately by embedded creation timestamp, improving locality, although clock skew and same-millisecond randomness affect ordering. Its timestamp is also visible, which may expose creation-time information.

UUID uniqueness still requires a correct generator and an enforced unique constraint. After a collision, generate a fresh UUID rather than retrying the conflicting value.

Security and Operational Consequences

UUIDs are not secrets, authorization mechanisms, or automatic privacy controls. Security must still derive from tenant boundaries, access controls, and authorization checks.

  • Bloat: Random UUID index insertion and dead tuples can leave underused pages. Monitor dead-row estimates and ensure autovacuum keeps pace.

  • Partition routing: Random UUIDs distribute records unpredictably across range partitions. UUID v7 is more time-oriented, while identity values follow allocation order.

  • Backup and replication cost: Wider keys increase relation sizes and WAL traffic.

  • Collision handling: Keep the primary-key constraint and use retry logic that generates a new value.

Production Monitoring and Benchmarking

  • pg_stat_user_tables: Track n_live_tup, n_dead_tup, n_tup_ins, n_tup_upd, and n_tup_del.

  • pg_stat_user_indexes: Review idx_scan, idx_tup_read, and idx_tup_fetch.

  • pg_stat_activity and pg_locks: Diagnose waits involving sequences, indexes, transactions, or application row locks.

  • pg_stat_wal: Track WAL records, bytes, and wal_buffers_full.

  • pg_sequences: Inspect identity-sequence configuration and the last allocated value.

  • pg_total_relation_size() and pg_relation_size(): Compare table, index, and total relation sizes.

  • pgstattuple(): Inspect tuple and free-space statistics after enabling the optional pgstattuple extension; this can be I/O-intensive on large tables.

Run EXPLAIN (ANALYZE, BUFFERS) against a safe, representative workload and compare buffer activity, temporary I/O, and execution time. That command does not report checkpoint impact; measure WAL and checkpoint effects separately, or use the WAL option where applicable.

Selection Guidance

Use an integer identity key for most conventional single-database, storage-sensitive applications. Use a UUID key when distributed generation, client-side creation, or merging independent data streams is a core requirement. For write-heavy distributed systems, UUID v7 is a practical compromise, but benchmark it with production-like cardinality, concurrency, and partition layouts.

13

What are PostgreSQL sequences?

A sequence is a PostgreSQL object that generates sequential numeric values, usually integer or bigint keys. It generates values independently of table rows, supports concurrent use, and commonly supplies values for primary keys or other increasing identifiers.

Read full answer

Applies toPostgreSQL 16 and 17

How Sequences Work

PostgreSQL stores a sequence as a special relation with configurable start, increment, minimum, maximum, cache, and cycle behavior. Its principal functions are:

  • nextval('sequence_name'): Returns the next value and advances the sequence.

  • currval('sequence_name'): Returns the last value obtained by nextval() in the current session. It errors if the session has not called nextval() for that sequence.

  • setval('sequence_name', value): Changes the sequence’s position and marks the supplied value as returned.

Sequence values are normally allocated in increasing order, but sequences do not guarantee gap-free numbering. A value consumed by nextval() remains consumed if its transaction fails or is rolled back. Concurrent transactions can also receive values in one order but commit in another order, so sequence values do not establish commit order.

Types, Ownership, and Identity Columns

A sequence’s data type determines its range:

Type Typical use Maximum value
smallint Small identifiers with limited capacity 32,767
integer Moderate-volume identifiers 2,147,483,647
bigint High-volume or distributed identifiers 9,223,372,036,854,775,807

OWNED BY links a sequence to a table column. Such a sequence is dropped automatically when its owning column or table is dropped. Without that dependency, dropping the table leaves the sequence behind.

For new schemas, prefer an identity column over the older serial pseudo-type. GENERATED ALWAYS rejects normal insertion of an explicit identity value unless the statement uses OVERRIDING SYSTEM VALUE; GENERATED BY DEFAULT accepts explicit values.

An explicit value inserted into an identity column does not advance the internal sequence. If manually inserted identifiers are higher than its current position, generated values can subsequently collide with them.

serial creates a separate sequence and assigns nextval() as the column default. Its sequence is automatically owned by the column, but application roles still require appropriate privileges on that sequence.

Transactions and Concurrency

nextval() is designed for concurrent callers and avoids the sustained row-lock contention caused by implementing a counter with UPDATE counter SET value = value + 1. However:

  • Sequence consumption is nontransactional; rollback does not return values.

  • Failed inserts may consume sequence values.

  • Avoid running setval() concurrently with normal generation, particularly when backends hold cached ranges, because this can cause later values to collide with previously returned identifiers.

  • A sequence does not enforce uniqueness; use a primary key or unique constraint.

  • A sequence declared with CYCLE can wrap and generate duplicate values if existing identifiers are retained.

Monitoring, Recovery, and Replication

The pg_sequences view exposes each sequence’s configuration and current state, including last_value, max_value, increment_by, and cache_size. Its last_value is the last value written to disk, not necessarily the last value returned to an application. With caching, the on-disk value may be ahead. It can also be NULL if the sequence has never been read or the viewer lacks sufficient privileges.

Production consequences include:

  • Exhaustion: Reaching MAXVALUE with CYCLE disabled causes subsequent nextval() calls to fail.

  • Caching gaps: Unused cached values are lost when a backend terminates abnormally or crashes. Larger caches reduce WAL activity but increase potential gaps.

  • Blocked generation: Use pg_stat_activity and pg_locks to identify sessions waiting on the sequence relation; pg_sequences alone does not show active or blocked calls.

  • Backups: pg_dump records sequence configuration and its set value. Physical base backups combined with WAL replay restore sequence state to the recovery point; after point-in-time recovery, verify the state against restored data.

  • Replication: Physical streaming replication replays sequence WAL, but a standby can lag behind the primary, so sequence state should be checked after promotion. Logical replication does not replicate sequence values; synchronize each subscriber’s sequence above the highest replicated identifier before allowing writes.

Operational Best Practices

  • Prefer identity columns for new primary keys and use bigint unless integer capacity is demonstrably sufficient.

  • Schema-qualify sequence names to avoid search_path ambiguity.

  • Grant USAGE where callers need nextval() or currval(). Do not grant UPDATE to ordinary application roles because it permits setval().

  • Monitor pg_sequences for proximity to MAXVALUE and unexpected position changes, and use pg_stat_activity and pg_locks for contention.

  • Treat gaps as expected, and choose the cache size according to throughput, WAL volume, and tolerance for gaps.

  • After restores, imports, explicit identity inserts, or logical replication, verify and synchronize sequence state before reopening writes.

  • Test backup restoration and failover procedures, including sequence state, to prevent identifier reuse.

14

How do PostgreSQL roles and privileges work?

A PostgreSQL role is a cluster-wide principal that can own objects, hold privileges, connect to databases, or inherit privileges from other roles. PostgreSQL uses one entity for both users and groups: a user is simply a role with the LOGIN attribute, while authorization comes from object ACLs and role memberships.

Read full answer

Applies toPostgreSQL 16/17

Core Role Model

Important role attributes include:

  • LOGIN: Permits the role to authenticate and start a session; it does not itself grant CONNECT to a database.

  • SUPERUSER: Bypasses most permission checks and can SET ROLE to any role. Use it only for controlled administration.

  • CREATEDB: Permits creating databases.

  • CREATEROLE: Permits creating roles and administering eligible non-superuser roles. In PostgreSQL 16/17 it cannot create a SUPERUSER role, and membership operations remain constrained by admin options.

  • REPLICATION: Permits initiating physical or logical replication connections.

  • BYPASSRLS: Bypasses row-level security policies.

  • INHERIT: Controls automatic inheritance of privileges from memberships.

  • CONNECTION LIMIT: Restricts the number of concurrent sessions for the role.

A role without LOGIN can still own objects or act as a group role. CREATE USER is effectively CREATE ROLE ... LOGIN, but CREATE ROLE makes the distinction explicit.

Privilege Scopes

Privileges are additive and evaluated using the current role, its memberships, and applicable object ACLs.

Object Common privileges Operational meaning
Database CONNECT, CREATE, TEMPORARY Connect, create schemas, or create temporary objects
Schema USAGE, CREATE Resolve or create objects in the schema
Table SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER Operate on data or related objects
View SELECT and applicable DML Query or modify an updatable view, subject to underlying-table checks
Sequence USAGE, SELECT, UPDATE Use or change sequence values
Function or procedure EXECUTE Invoke the routine
Type or domain USAGE Use the type in definitions and casts

Object owners control grants on their objects. PUBLIC is a pseudo-role representing every role, so accidental grants to it can expose data broadly. Two defaults matter especially in production:

  • New databases do not grant CREATE on the public schema to PUBLIC; USAGE remains. Older databases upgraded in place may retain legacy grants.

  • Functions and procedures grant EXECUTE to PUBLIC by default, so sensitive routines should explicitly revoke it.

Membership and Effective Privileges

Roles can be granted to other roles. With inheritance enabled, analyst receives the privileges of reporting without additional grants on each object.

  • INHERIT: Controls whether privileges are inherited automatically from a membership.

  • SET: Controls whether the member can SET ROLE to the granted role. PostgreSQL 16/17 expose these as membership options.

  • SET ROLE: Changes current_user and current_role; session_user remains the original session identity. It requires membership with SET privilege, except that superusers may set any role.

  • RESET ROLE: Restores current_user to session_user.

  • ADMIN OPTION: Allows a member to grant the role to other roles, subject to PostgreSQL’s admin-option rules.

  • No explicit deny: Revoking access through one path does not remove privileges still inherited through another membership path.

This makes membership chains a common privilege-escalation path. Review them before granting additional memberships.

Code Example

Run this on a fresh test cluster while connected as a superuser:

CREATE ROLE app_owner NOLOGIN;
CREATE ROLE reporting NOLOGIN;
CREATE ROLE analyst LOGIN;

CREATE SCHEMA app_owner AUTHORIZATION app_owner;

SET ROLE app_owner;
CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    customer text NOT NULL,
    total numeric(10, 2) NOT NULL CHECK (total >= 0)
);
INSERT INTO orders VALUES
    (1, 'ACME', 125.50),
    (2, 'Example Corp', 80.00);
RESET ROLE;

GRANT USAGE ON SCHEMA app_owner TO reporting;
GRANT SELECT ON app_owner.orders TO reporting;
GRANT reporting TO analyst;

-- Apply SELECT to future tables created by app_owner in this schema.
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app_owner
    GRANT SELECT ON TABLES TO reporting;

SET ROLE analyst;
SELECT current_user;

SELECT order_id, customer, total
FROM app_owner.orders
ORDER BY order_id;

EXPLAIN (ANALYZE, COSTS OFF)
SELECT order_id, customer, total
FROM app_owner.orders
WHERE customer = 'ACME';

RESET ROLE;

The first query returns:

current_user
--------------
 analyst
(1 row)

The data query returns:

order_id | customer      | total
---------+---------------+-------
1        | ACME          | 125.50
2        | Example Corp  | 80.00

The shortened EXPLAIN ANALYZE output is:

Seq Scan on orders
  Filter: (customer = 'ACME'::text)
  Rows Removed by Filter: 1

A sequential scan is expected here because customer is not indexed; the planner may choose the primary-key index for a selective order_id lookup.

Security and Production Operations

  • Least privilege: Keep ownership separate from runtime login roles, grant only required DML privileges, and use group roles for shared access.

  • Authentication versus authorization: pg_hba.conf controls authentication; role attributes, memberships, and ACLs control authorization.

  • Password security: Use SCRAM authentication, TLS, managed credentials or secret injection, and rotate credentials without storing them in source control.

  • Monitoring: Audit pg_roles, information_schema.role_table_grants, information_schema.table_privileges, and has_table_privilege(). Enable statement logging or pgaudit where grant auditing is required.

  • Backup and restore: pg_dump preserves object ownership and ACLs but not cluster-wide role definitions. Use a protected pg_dumpall --globals-only process for roles and memberships. Physical PITR restores global catalogs from the base backup; logical restoration from dumps requires roles to be restored separately.

  • Incident response: Revoke suspicious memberships and grants, rotate affected credentials, use CONNECTION LIMIT or NOLOGIN to contain compromised roles, terminate active sessions when necessary, and verify effective access with has_*_privilege().

15

How does row-level security work in PostgreSQL?

Row-level security (RLS) is PostgreSQL’s database-enforced mechanism for limiting which rows a role may select or modify. Ordinary privileges still control whether a role can access a table, and policies then filter or validate individual rows, so an application cannot bypass them merely by omitting a WHERE clause.

Read full answer

Applies toPostgreSQL 16 and 17

How It Works

  • ALTER TABLE ... ENABLE ROW LEVEL SECURITY enables RLS for a table.

  • CREATE POLICY associates an expression with a command such as SELECT, INSERT, UPDATE, DELETE, or ALL, and optionally restricts it to named roles or PUBLIC.

  • PostgreSQL incorporates applicable policy expressions into query execution as security qualifiers. A SELECT cannot retrieve a row that fails its policy, and UPDATE or DELETE cannot target such a row.

  • Ordinary GRANT privileges are still required. Enabling RLS does not grant table access, and a policy cannot grant a privilege the role lacks.

  • If no applicable policy permits an operation, it does not grant access. Filtered SELECT queries commonly appear empty, while an INSERT or UPDATE that fails a WITH CHECK expression is rejected with an error.

Policy Expressions and Types

Command Policy expression used
SELECT USING controls which existing rows are visible
INSERT WITH CHECK validates the new row
UPDATE USING selects existing rows; WITH CHECK validates the updated row
DELETE USING selects rows that may be deleted

For an UPDATE policy that omits WITH CHECK, the USING expression is used for both purposes. Permissive policies, the default, are combined with OR; restrictive policies are combined with AND and do not grant access by themselves.

Roles, Owners, and Views

  • The table owner normally bypasses RLS. ALTER TABLE ... FORCE ROW LEVEL SECURITY also subjects the owner to policies, but an owner can still disable RLS on a table it owns. Superusers and roles with BYPASSRLS always bypass it.

  • In a policy, current_user is the effective role and changes under SET ROLE; session_user remains the original login role. This permits role-based tenancy, although a tenant-ID mapping table is often safer than one shared login role per tenant.

  • A normal view checks the base tables using the view owner’s privileges, so base-table RLS is also evaluated as that owner. With security_invoker = true, available from PostgreSQL 15, the querying role’s privileges and policies are used instead.

  • security_barrier prevents optimizer-driven predicate leakage through a view but does not replace security_invoker for tenant isolation.

  • Referential-integrity checks bypass RLS to preserve data integrity. Security-definer functions can likewise operate with an owner’s privileges and may bypass policies.

Production Behaviour and Monitoring

  • Policy predicates can be evaluated per row. Index columns used by policy expressions and monitor plans with EXPLAIN (ANALYZE) using the actual application role, because a superuser may bypass RLS and produce a different plan.

  • pg_policies is the primary catalog for auditing policy definitions. pg_stat_user_tables contains global cumulative counters such as n_tup_ins, n_tup_upd, and n_tup_del; it does not provide per-role or per-tenant RLS counts.

  • A denied SELECT is silent. Failed INSERT or UPDATE checks commonly produce SQLSTATE 42501 and messages such as new row violates row-level security policy. Configure server error logging appropriately and use pgaudit when its audit scope is required and tested.

  • Physical backups contain all database blocks and therefore include rows regardless of RLS. Logical pg_dump output depends on the dump role and row_security settings, including whether --enable-row-security is used; policy definitions are dumped as schema objects.

Best Practices

  • Keep ownership separate from runtime roles, enable FORCE ROW LEVEL SECURITY, and grant only the required table or column privileges.

  • Create explicit policies for every required command and role instead of relying on PUBLIC. Test each role under normal transaction boundaries, including attempted cross-tenant reads and writes.

  • In pooled deployments, prefer transaction-local role or tenant context, such as SET LOCAL ROLE inside an explicit transaction, rather than depending on session state surviving in transaction-pooled connections.

  • Monitor policy definitions, indexing, query plans, error rates, and backup behavior. RLS is an authorization control, not encryption or a substitute for separating and protecting privileged roles.

16

How do you configure client authentication in PostgreSQL pg_hba.conf?

pg_hba.conf is PostgreSQL’s host-based access control file: it decides which clients may connect, to which databases, as which users, from which hosts, and by which authentication method. Each connection is matched against the rules top to bottom, and the first matching rule wins.

Read full answer

Applies toPostgreSQL 16 and 17

How It Works

When a client connects, the postmaster reads pg_hba.conf (located in the data directory, or pointed to by hba_file) and evaluates records in order. A record matches when type, database, user, and address all match the connection; the method then determines how (or whether) the client proves its identity. Because matching stops at the first hit, put specific rules before broad ones. Changes take effect on reload (SELECT pg_reload_conf(); or pg_ctl reload) without restarting the server.

Record Format

Each non-comment line has five or more fields:

TYPE DATABASE USER ADDRESS METHOD [options]

  • TYPE: local (Unix socket), host (TCP), hostssl (TCP + TLS required), hostnossl (TCP without TLS).

  • DATABASE: database name, all, or comma list; replication for physical replication connections.

  • USER: role name, all, or +group for role membership.

  • ADDRESS: IP/CIDR for host types (e.g. 10.0.0.0/16); omitted for local.

  • METHOD: scram-sha-256, md5, password, cert, peer, trust, reject, ldap, etc.

Authentication Methods

Method Use case Notes
scram-sha-256 Default password auth Strong; requires password_encryption = scram-sha-256
md5 Legacy clients Weaker; avoid for new setups
cert mTLS client certs CN must match username (or use pg_ident.conf map)
peer Local socket OS-user mapping Maps OS username to DB role
trust Dev only No password; never use in production
reject Explicit deny Useful to block before a broader allow

Ordering and Reload

Rules are evaluated top-down; the first match wins, so place denies and specific allows before catch-all rules. After editing, reload with SELECT pg_reload_conf(); (superuser) or pg_ctl reload -D /var/lib/postgresql/data. A malformed file is logged and the old rules stay active, so always validate with pg_ctl reload and check logs.

Security Best Practices

  • Prefer scram-sha-256 over md5; set password_encryption = 'scram-sha-256' in postgresql.conf.

  • Use hostssl instead of host to force TLS; pair with ssl = on and a valid server certificate.

  • Restrict ADDRESS to known CIDRs; never use 0.0.0.0/0 with password methods.

  • Avoid trust outside local dev; never expose it on network interfaces.

  • Use cert for service accounts and automation; map CNs via pg_ident.conf.

  • Keep replication rules (replication database) separate and limited to replica IPs.

Common Pitfalls and Monitoring

  • Lockout: a bad edit can block all access; keep a local peer rule for the postgres OS user on the socket as a recovery path, or use single-user mode.

  • Ordering mistakes: a broad host all all 0.0.0.0/0 md5 above a specific deny silently overrides it.

  • TLS mismatch: hostssl rejects non-TLS clients; ensure clients use sslmode=require or higher.

  • Monitor failed auth via server logs (enable log_connections); inspect pg_stat_activity for connection churn and check logs for reload errors. There is no dedicated auth-failure counter view, so rely on logs and your SIEM.

Code Example

# $PGDATA/pg_hba.conf
# TYPE  DATABASE        USER            ADDRESS          METHOD
local   all             postgres                             peer
local   all             all                                  scram-sha-256
hostssl all             appuser         10.0.0.0/16      scram-sha-256
hostssl replication     replicator      10.0.1.5/32      scram-sha-256
host    all             all             127.0.0.1/32     reject

Reload and verify:

SELECT pg_reload_conf();
SELECT * FROM pg_hba_file_rules;
17

Which index types does PostgreSQL 16/17 support?

PostgreSQL 16 and 17 provide six core index access methods: B-tree, Hash, GiST, SP-GiST, GIN, and BRIN. They differ in supported operators, storage layout, write cost, and operational behavior.

Read full answer

Applies toPostgreSQL 16 and 17

Core Index Access Methods

Index type Best suited for Key characteristics and limitations
B-tree Equality, ranges, ordering, joins, and unique constraints The default and most general-purpose type. Supports multicolumn keys, and primary-key and unique constraints are implemented with B-tree indexes.
Hash Equality lookups May be smaller than a B-tree for some distributions. It supports equality only, with no multicolumn, unique, range, or ordering support.
GiST Geometry, ranges, nearest-neighbor searches, and exclusion constraints An extensible search tree whose capabilities depend on the operator class. Built-in operator classes cover geometric and range data.
SP-GiST Sparse or irregularly distributed key spaces A generic search tree whose performance depends strongly on the data distribution and operator class. It is distinct from declarative table partitioning.
GIN Arrays, JSONB, full-text search, and extension-provided trigram matching An inverted index that maps keys to multiple entries. It commonly supports @>, ?, ?&, full-text operators, and trigram similarity searches.
BRIN Very large tables whose values correlate with physical order Stores block-level summaries rather than every key. It is effective for append-only, time-correlated data such as many time-series tables and may recheck the heap for false positives.

Extensions can add access methods beyond these six. For example, compatible versions of pgvector provide HNSW and IVFFlat indexes, while bloom provides a Bloom-filter index. These are extension features rather than core PostgreSQL index types.

Index Features and Constraints

  • Unique indexes: Enforce uniqueness. PostgreSQL 16 and 17 support both NULLS DISTINCT, the default, and NULLS NOT DISTINCT, which treats null values as equal for uniqueness checks.

  • Partial and expression indexes: Partial indexes contain only rows matching a predicate, while expression indexes index a computed expression.

  • Multicolumn and covering indexes: B-tree multicolumn keys are most effective when queries constrain columns in leftmost-prefix order. INCLUDE can store payload columns for index-only scans where the access method supports it.

  • Exclusion constraints: Typically use GiST or SP-GiST operator classes to prevent overlapping ranges or other conflicting data.

  • Full-text and JSON indexes: These are generally GIN indexes with specialized operator classes, not separate index access methods.

Planning and Execution

The planner selects an access method using estimated row counts, selectivity, correlation, sort requirements, and execution cost. These estimates depend on PostgreSQL statistics, so stale statistics can produce poor plans.

EXPLAIN displays the selected plan, while EXPLAIN (ANALYZE, BUFFERS) executes the statement and reports actual rows, loops, timing, buffer hits, and physical block reads. BUFFERS helps identify indexes that are read frequently but yield few rows.

GIN and GiST may participate in bitmap index scans. The resulting plan constructs a bitmap of matching pages and can recheck the heap for lossy matches. An index-only scan is possible only when the selected index can return every required column and the relevant heap pages are marked all-visible in the visibility map.

Production Trade-offs

  • Write cost and HOT updates: More indexes increase CPU, storage, WAL volume, and potentially replication lag. An update can remain heap-only only when no indexed column changes and the new tuple fits on the same heap page.

  • Bloat: Updates and deletes can leave dead or poorly packed index entries. VACUUM can remove dead entries and maintain visibility information but usually does not return space to the operating system. REINDEX INDEX CONCURRENTLY can rebuild an index when justified.

  • DDL locking: On an ordinary table, CREATE INDEX blocks writes while building the index. CREATE INDEX CONCURRENTLY permits normal writes, takes longer, cannot run inside a transaction block, and can leave an invalid index after failure. Concurrent builds are not supported for partitioned tables.

  • Replication: Physical replication reproduces index changes through WAL replay. Logical replication does not automatically create or replicate indexes; the target schema and indexes must be managed separately.

  • Partitioning and security: Indexes declared on a partitioned parent are propagated to its partitions, while local indexes require separate lifecycle management. Untrusted extensions with server-side C code execute in PostgreSQL backend processes under the server operating-system account and must be treated as privileged dependencies.

Monitoring and Maintenance

  • pg_stat_user_indexes: Reports idx_scan, idx_tup_read, and idx_tup_fetch; use these to identify indexes with little or no ongoing benefit.

  • pg_statio_user_indexes: Reports index block reads and cache hits through idx_blks_read and idx_blks_hit.

  • pg_stat_user_tables: Tracks row changes, live tuples, and estimated dead tuples, helping identify tables where index bloat deserves investigation.

  • pg_stat_progress_vacuum: Reports vacuum phases, including index vacuuming and cleanup. pg_stat_activity and pg_locks help diagnose DDL waits.

  • pg_relation_size measures index storage. Do not rebuild an index solely because it is large; first check usage, bloat evidence, cache behavior, and the operational cost of the rebuild.

18

When should I use a PostgreSQL GIN index?

A GIN index is appropriate when queries search for values contained within composite values, such as arrays, JSONB documents, or full-text search vectors. It is especially useful for selective containment and overlap queries, but its higher write and maintenance cost makes it less suitable for update-heavy tables.

Read full answer

Applies toPostgreSQL 16/17

How It Works

GIN is an inverted-index access method. Instead of ordering scalar values like a B-tree, it stores posting lists that map keys or lexemes to the rows containing them.

For example, a GIN index on tags text[] allows PostgreSQL to find rows containing wal without scanning every array. GIN normally produces a Bitmap Index Scan, followed by a Bitmap Heap Scan to retrieve matching rows.

When to Use It

Query requirement Recommended index
Array containment or overlap GIN with array_ops
JSONB containment or JSON path matching GIN with jsonb_ops or jsonb_path_ops
Full-text search GIN with tsvector_ops
Substring, ILIKE, regex, or similarity search GIN with pg_trgm extension
Scalar equality or ordered range scans B-tree instead
Anchored prefix search such as title LIKE 'Post%' B-tree with text_pattern_ops

Important operator differences include:

  • jsonb_path_ops: Usually smaller and faster for @>, @?, and @@; it does not support key-existence operators such as ?, ?|, and ?&.

  • jsonb_ops: Supports a wider set of JSONB operators, including key-existence queries.

  • Full-text search: The indexed value must be a tsvector; PostgreSQL does not use the GIN index directly against a raw tsquery.

Trade-offs

  • Write amplification: Inserting or updating indexed data creates additional index entries and WAL, increasing CPU, I/O, and storage use.

  • No ordering: GIN cannot provide sorted scans, ORDER BY optimization, or unique constraints.

  • Bloat: Frequent updates can leave dead entries until vacuum removes them.

  • Pending-list latency: With gin_fastupdate enabled, new entries may remain in a pending list until cleanup or the pending-list limit is reached.

  • Long transactions: Old snapshots can prevent cleanup, increasing index size and causing stale or slow scans.

  • Not always beneficial: A GIN index may be ignored when a query is unselective or the table is small because a sequential scan is cheaper.

Production Operations

Monitor whether the index is used and whether it is accumulating work. In pg_stat_user_indexes, relname identifies the table and indexrelname identifies the index, so filter on indexrelname when checking a specific GIN index. Use EXPLAIN (ANALYZE, BUFFERS) to verify the plan and compare estimated versus actual row counts.

For GIN pending-list growth, monitor pg_stat_gin_indexes, including pending pages and pending tuples. Investigate long-running transactions that can delay vacuum cleanup by checking pg_stat_activity for sessions with a non-null backend_xmin. Also monitor database-wide transaction ID age with pg_database and age(datfrozenxid).

Best Practices

  • Create GIN indexes only for predicates using supported operators.

  • Run ANALYZE after loading or substantially changing indexed data.

  • Build large production indexes with CREATE INDEX CONCURRENTLY to reduce write blocking.

  • Monitor pending-list growth, index size, scan counts, vacuum activity, and long transactions.

  • Use pg_trgm GIN indexes for substring searches, not ordinary B-tree indexes.

  • Reassess unnecessary GIN indexes on frequently updated columns because their storage and cleanup costs can exceed their benefit.

19

When should you use a GiST index in PostgreSQL?

A GiST (Generalized Search Tree) index is the right choice when your queries need operators that B-trees cannot support: range overlaps, geometric containment, full-text search, nearest-neighbor ordering, or exclusion constraints. It is a balanced, extensible tree that stores predicates rather than plain sorted values, and it can be lossy, returning candidate rows that PostgreSQL rechecks against the heap.

Read full answer

Applies toPostgreSQL 16 and 17.

How It Works

  • GiST is a template index method: each data type or operator class defines how keys are compared, split, and penalized.

  • Internal nodes hold bounding predicates (for example, a bounding box for geometries or a union of ranges); leaf nodes hold the indexed values.

  • Searches descend the tree using consistent, union, and penalty functions, then recheck candidates in the heap when the index is lossy.

  • Because it is lossy for some types, EXPLAIN may show Heap Fetches and a Recheck Cond.

When to Use GiST

  • Range types (tsrange, int4range, daterange): overlap (&&), containment (@>), adjacency (-|-).

  • Geometric/spatial data via PostGIS: bounding-box and distance operators.

  • Full-text search on tsvector with @@, especially when combined with ranking or ordering.

  • Nearest-neighbor queries: ORDER BY location <-> point LIMIT k.

  • Exclusion constraints: EXCLUDE USING gist (room WITH =, during WITH &&) to prevent overlapping bookings.

  • Trigram similarity (pg_trgm) when you need similarity ordering, not just filtering.

GiST vs GIN vs B-tree

Aspect B-tree GiST GIN
Best for Equality, ordering, ranges on scalars Overlap, containment, nearest-neighbor, exclusion Multi-value containment, full-text filtering
Lossy No Sometimes No (but pending list)
Build speed Fast Moderate Slow
Index size Small Moderate Large
Update cost Low Moderate High (mitigated by fastupdate)
Nearest-neighbor No Yes No

Operational Considerations

  • GiST indexes are larger and slower to build than B-trees; build them with CREATE INDEX CONCURRENTLY on busy tables.

  • Lossy indexes can produce many heap rechecks; if selectivity is poor, queries degrade. Check EXPLAIN (ANALYZE, BUFFERS).

  • Like all indexes, GiST indexes bloat under heavy updates; monitor pg_stat_user_indexes and reindex with REINDEX INDEX CONCURRENTLY.

  • GiST does not participate in transaction-ID wraparound directly, but bloat and dead tuples still require healthy VACUUM.

Monitoring

  • pg_stat_user_indexes: idx_scan, idx_tup_read, idx_tup_fetch to confirm the index is used.

  • pg_stat_all_indexes for system catalogs if indexing system tables.

  • pg_index plus pg_relation_size() to track index bloat over time.

  • EXPLAIN (ANALYZE, BUFFERS) to inspect Heap Fetches and recheck cost.

Best Practices

  • Prefer GIN for pure full-text filtering on read-heavy workloads; prefer GiST when you need ordering or nearest-neighbor.

  • Use exclusion constraints instead of application-level locking for non-overlapping reservations. When an exclusion constraint mixes a scalar equality such as room WITH = with a range overlap, install btree_gist first.

  • Build concurrently in production and monitor pg_stat_progress_create_index.

  • Keep statistics fresh with ANALYZE after large loads so the planner chooses GiST appropriately.

20

What is a BRIN index in PostgreSQL and when is it effective?

A BRIN (Block Range Index) is a PostgreSQL index type that stores summary metadata for ranges of heap pages rather than an entry for every row. It is most effective on very large tables whose physical page order closely correlates with the indexed column, particularly append-only time-series data.

Read full answer

Applies toPostgreSQL 16 and 17

How It Works

  • PostgreSQL groups heap pages into ranges. With the default pages_per_range of 128 pages, each range covers approximately 1 MiB on the usual 8 KiB block-size installation.

  • Each range receives a summary tuple. With the default min/max summarizer and a compatible operator class, PostgreSQL stores the smallest and largest indexed values in that range.

  • During a bitmap index scan, the executor compares query predicates with the stored summaries and excludes ranges that cannot contain matching rows. Ranges that cannot be excluded are rechecked against the heap.

  • BRIN does not provide B-tree-style direct navigation to individual rows. Its advantage is a very small index with low insertion and update overhead.

When It Is Effective

  • Very large append-only tables: BRIN is especially useful for event logs, measurements, audit records, and other tables that grow primarily by appending rows.

  • Strong physical correlation: A timestamp or monotonically increasing identifier is effective when new rows are inserted in roughly the same order as their indexed values.

  • Range predicates: Queries such as created_at >= $1 or searches within a time window can eliminate many page ranges before touching the heap.

  • Equality predicates with clustered values: Equality can also benefit when each value is confined to a small number of ranges, although B-tree indexes are generally preferable for highly selective lookups.

  • Mixed access patterns: BRIN can handle broad range filtering while a B-tree serves selective point lookups. Partition pruning may provide additional savings.

When It Is Not Effective

  • Random physical ordering: If adjacent pages contain unrelated values, every summary may span most of the value domain, allowing few ranges to be excluded.

  • Small tables: The planner and execution savings are usually insufficient to justify an additional index.

  • Highly selective point lookups: BRIN cannot directly locate a single heap tuple as efficiently as a B-tree.

  • Poorly correlated update patterns: Updates do not inherently invalidate BRIN, but changing values randomly can weaken physical correlation and make summaries less selective.

Operational Considerations

  • Summarization: The autosummarize storage parameter is off by default. Enable it when vacuuming should summarize pending ranges; otherwise call brin_summarize_new_values explicitly after bulk loads.

  • Range size: pages_per_range defaults to 128. Smaller ranges improve pruning selectivity but produce more summaries and a larger index. Treat this as a design-time choice and rebuild the index when materially retuning it.

  • Plan verification: Use EXPLAIN (ANALYZE, BUFFERS) and compare heap blocks read with the table’s total size. A bitmap heap scan with substantially fewer heap blocks demonstrates useful range elimination; Rows Removed by Index Recheck indicates additional heap rechecks.

  • Monitoring: Track pg_stat_user_indexes.idx_scan for usage and pg_stat_user_tables for vacuum and modification statistics. A zero scan count is not automatically a fault because the workload may not need the index or the planner may prefer another path. Record index size with pg_relation_size.

  • Rebuilding: PostgreSQL 16/17 do not support REINDEX INDEX CONCURRENTLY for BRIN. A regular REINDEX INDEX blocks writes to the table, so busy systems may need a replacement index built concurrently and a planned rename-and-drop cutover.

  • Constraints: BRIN cannot enforce primary-key or unique constraints because it does not provide exact row-level uniqueness checks.

Best Practices

  • Confirm physical correlation with pg_stats.correlation after running ANALYZE; values near +1 or -1 generally indicate strong linear correlation.

  • Benchmark against a B-tree using production-like data and a representative mix of range and point queries rather than relying on index size alone.

  • Use time-based partitioning for retention and pruning, with BRIN reducing heap access within each partition.

  • Keep autovacuum healthy and verify that summaries are advancing; stale or missing summaries can make a correctly configured index ineffective.

  • Retain a B-tree when the workload includes many highly selective lookups that BRIN cannot serve efficiently.

21

What are partial indexes in PostgreSQL?

A partial index is a PostgreSQL index that contains entries only for rows satisfying a specified WHERE predicate. It can reduce storage and maintenance overhead while accelerating queries whose predicates logically imply the indexed subset.

Read full answer

Applies toPostgreSQL 16 and 17

How It Works

A partial index is created with a WHERE predicate after the indexed columns. Only rows for which the predicate evaluates to TRUE receive index entries; rows evaluating to FALSE or NULL are excluded.

Partial indexes may be B-tree indexes, may contain multiple columns or expressions, and may enforce uniqueness within the indexed subset. For example, a unique partial index on accounts(email) with a predicate of status = 'active' permits duplicate email values among inactive accounts while enforcing uniqueness among active accounts. Primary keys cannot be partial.

The predicate may refer only to columns of the indexed table and cannot contain subqueries. Its functions and operators must be immutable because the indexed population must not change over time. Consequently, functions such as now() cannot be used in a partial-index predicate.

Index tuples contain the index values and heap tuple identifiers. Visibility still follows PostgreSQL’s normal MVCC rules: the index does not independently guarantee transaction visibility. An index-only scan may avoid heap access when the visibility map shows that all required heap pages are visible to all transactions.

Comparison With Other Indexes

Index type Indexed content Typical use
Full B-tree index Every row General-purpose lookups and joins
Partial B-tree index Rows satisfying a predicate Large tables with a selective subset
Expression index Results of expressions Case normalization or computed search values
Composite index Multiple columns Multi-column predicates and ordering

A partial index is most useful when the qualifying rows form a stable, selective subset of a large table. If most rows qualify, a full index may be simpler and offer little or no performance advantage.

Query Planner Usage

The planner can use a partial index only when it can prove that the query’s conditions imply the index predicate. For an index defined with WHERE status = 'active':

  • A query filtering on status = 'active' is eligible.

  • Adding email = $1 remains eligible.

  • A query filtering on status = 'pending' is not eligible.

  • A query filtering only on email = $1 is not eligible because it might match inactive rows.

The query need not repeat the predicate literally, but the planner must establish logical implication. Parameterized queries can behave differently between custom and generic prepared plans because a generic plan may not know the parameter value well enough to prove that it satisfies a constant index predicate.

Use EXPLAIN (ANALYZE, BUFFERS) to confirm index selection and measure buffer activity. ANALYZE executes the statement, so it must be used with safe queries and representative parameters in production. PostgreSQL may still prefer a sequential scan on a small table or when it estimates that scanning the heap is cheaper.

Production Operations

  • Write overhead: Inserting a qualifying row creates an index entry, while changing a row so that it leaves the indexed subset makes its old index entry obsolete.

  • HOT updates: An update may qualify for a heap-only tuple update when it changes no indexed columns or predicate columns and no other HOT-update restrictions are violated.

  • Bloat: Frequent changes in predicate membership can produce dead index entries. Monitor n_dead_tup in pg_stat_user_tables and index sizes; autovacuum removes obsolete heap tuples and their associated index entries.

  • Usage monitoring: idx_scan, idx_tup_read, and idx_tup_fetch in pg_stat_user_indexes help identify usage. Low usage alone does not prove an index is unnecessary because workload patterns and statistics collection periods matter.

  • Replication and backups: Physical replication, base backups, WAL archiving, and PITR preserve partial indexes with the database. Logical replication does not replicate indexes; subscriber indexes must be created and maintained separately.

  • Best practice: Keep predicates selective and stable, align them with application query semantics and compatible data types, and validate them with production-like statistics and EXPLAIN (ANALYZE, BUFFERS) before rollout.

22

What are expression indexes in PostgreSQL?

An expression index (also called a functional index) stores the result of an expression computed over one or more columns rather than the raw column values, letting queries that filter, join, or sort on that expression use the index instead of recomputing it per row.

Read full answer

Applies toPostgreSQL 16/17

How It Works

  • Created with CREATE INDEX ... ON table (expression). The expression is evaluated at write time and the resulting value is stored in the index.

  • At query time, the planner uses the index only when the query’s predicate matches the indexed expression. The expression in the query must match what was indexed.

  • Every function used in the expression must be marked IMMUTABLE; PostgreSQL checks this at index creation and refuses otherwise, because a non-immutable result could make the index silently stale.

  • On INSERT/UPDATE/DELETE, the expression is recomputed and the index entry maintained, so writes carry extra cost proportional to expression complexity.

Common Use Cases

  • Case-insensitive lookups: lower(email) so WHERE lower(email) = ... is indexed.

  • Extracting from JSONB: (payload->>'status') for frequent filters on a key.

  • Computed values: (price * quantity) or (first_name || ' ' || last_name).

  • Date bucketing: date_trunc('day', created_at) for daily aggregation queries.

Expression vs. Regular and Partial Indexes

Type Indexed content Best for
Regular index Raw column value Equality/range on a column
Expression index Result of an expression Queries filtering on computed values
Partial index Subset of rows (via WHERE) Skewed data, soft deletes
Expression + partial Computed value on a subset Combining both benefits

Operational Considerations

  • Write amplification: every write recomputes the expression; keep expressions cheap and avoid heavy functions.

  • Bloat: expression indexes bloat like any index under churn. Monitor with pg_stat_user_indexes (idx_scan, idx_tup_read) and pgstattuple/pgstatindex for bloat estimates.

  • Rebuilds: use REINDEX INDEX CONCURRENTLY idx_name; to rebuild without long locks; schedule during low traffic.

  • Planner matching: if the query writes lower(email) but the index used lower(trim(email)), the planner will not use it. Keep expressions identical.

  • Immutability: a function that reads the current time or session state cannot be indexed; mark only truly deterministic functions IMMUTABLE.

Best Practices

  • Index only expressions that appear in frequent WHERE, JOIN, or ORDER BY clauses.

  • Prefer partial expression indexes (... WHERE active) when only a subset of rows is queried.

  • Verify usage with EXPLAIN (ANALYZE, BUFFERS) and check pg_stat_user_indexes.idx_scan over time; drop unused indexes to reduce write cost.

  • Use CONCURRENTLY for creation on large tables to avoid blocking writes.

23

What are covering indexes with INCLUDE in PostgreSQL?

A covering index in PostgreSQL is a B-tree index whose key columns locate rows and whose INCLUDE columns are stored in leaf entries, allowing a query to be answered with an index-only scan without reading heap tuples.

Read full answer

Applies toPostgreSQL 16/17 (B-tree `INCLUDE` since PostgreSQL 11)

How It Works

  • Key columns define ordering and predicate matching.

  • INCLUDE columns are non-key payload stored in leaf pages; they do not affect sort order or prefix matching.

  • The planner can choose an index-only scan when all referenced columns are available in the index and the visibility map shows the relevant heap pages are all-visible.

  • If heap pages are not all-visible, PostgreSQL performs heap fetches, reducing the benefit.

  • In a unique index, uniqueness is enforced only on the key columns; included columns may repeat.

Syntax and Example

The syntax is CREATE INDEX ... ON table (key_column) INCLUDE (payload_column, ...);, for example an index on orders(customer_id) INCLUDE (status, total). A useful plan shows Index Only Scan with Heap Fetches: 0, confirming that the query was served from the index.

When to Use

  • Frequent queries select a small set of columns and filter on an indexed key.

  • Large tables where heap I/O dominates latency.

  • Avoid including very wide columns, such as large text or jsonb values, because they increase index size and write amplification.

  • If a query filters or sorts on an included column, make it a key column or use a separate index instead.

  • Combine with partial indexes when only a subset of rows is queried.

Operational Considerations

  • INCLUDE increases index size, WAL volume, and maintenance cost.

  • Monitor pg_stat_user_indexes for idx_scan and idx_tup_read, and use pg_relation_size() to track index growth.

  • Use EXPLAIN (ANALYZE, BUFFERS) to confirm index-only scans and check Heap Fetches.

  • Keep autovacuum effective so the visibility map stays current; monitor pg_stat_user_tables for dead tuples and recent vacuum activity.

  • Build or rebuild indexes with CREATE INDEX CONCURRENTLY and REINDEX INDEX CONCURRENTLY to reduce locking in production.

  • Covering indexes do not remove the need for vacuuming or transaction-ID wraparound protection.

Best Practices

  • Include only columns needed by hot query paths.

  • Re-check plans after adding columns; more included columns are not always better.

  • Include a primary-key or frequently selected column when a query needs it and it is not already the index key.

  • Prefer INCLUDE over adding a column to the key when its ordering is not needed.

24

How do you create an index without locking the table in PostgreSQL?

CREATE INDEX CONCURRENTLY builds an index while allowing ordinary reads and writes to continue. It is not completely lock-free: the build takes a SHARE UPDATE EXCLUSIVE lock and can wait for older transactions to finish.

Read full answer

Applies toPostgreSQL 16/17

How It Works

  • A normal CREATE INDEX takes a SHARE lock on the table. Ordinary SELECT statements can continue, but INSERT, UPDATE, DELETE, and conflicting DDL are blocked.

  • CREATE INDEX CONCURRENTLY takes SHARE UPDATE EXCLUSIVE, which permits normal reads and writes but conflicts with another concurrent index build, VACUUM, and several forms of DDL.

  • PostgreSQL performs two table scans. The first collects entries from the initial snapshot; the second waits for transactions that could have modified the table, then rescans the heap to include newer changes.

  • The new index becomes valid only after the second scan succeeds. Concurrent builds support ordinary, unique, expression, and partial indexes, but cannot directly build an index on a partitioned table.

Operational Constraints and Failure Modes

  • CREATE INDEX CONCURRENTLY cannot run inside an explicit transaction. A migration tool that automatically wraps statements in BEGIN/COMMIT must provide a non-transactional execution path.

  • A concurrent unique build can fail if duplicates are found during validation. A failed build normally leaves an invalid index behind.

  • Invalid indexes are ignored by the planner but still consume space and can impose index-maintenance overhead on writes.

  • Concurrent builds generally generate more CPU, I/O, and WAL than ordinary builds, so monitor disk capacity, write latency, and replica lag.

  • Long-running transactions that modified the table can delay the second scan. Long-running read-only transactions that did not modify it generally do not cause that specific wait.

  • Direct concurrent creation on a partitioned table is unsupported. The usual approach is to create the parent index with CREATE INDEX ... ON ONLY parent_table (it starts invalid and builds nothing), create each partition’s index concurrently, and attach each one with ALTER INDEX ... ATTACH PARTITION; the parent index becomes valid once every partition is attached.

Monitoring and Recovery

  • pg_stat_progress_create_index reports the build phase and scanned or processed tuples and blocks.

  • pg_index.indisvalid identifies indexes that are not valid. An in-progress concurrent build is also not yet valid, so cross-check the progress view before classifying an index as a failed leftover.

  • Monitor the backend through pg_stat_activity, including wait_event, and monitor replication lag through pg_stat_replication.

  • After a failed concurrent build, remove the invalid index with DROP INDEX CONCURRENTLY index_name, then retry the build as a standalone statement.

Code Example

Create the example table and data first:

CREATE TABLE orders (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id bigint NOT NULL,
    status text NOT NULL
);

INSERT INTO orders (customer_id, status)
VALUES
    (101, 'new'),
    (101, 'paid'),
    (202, 'new');

Run the concurrent build as a separate top-level statement in autocommit mode:

-- Do not wrap this statement in BEGIN/COMMIT.
CREATE INDEX CONCURRENTLY idx_orders_customer_id
ON orders (customer_id);

Verify that the index is valid and ready:

SELECT indexrelid::regclass AS index_name,
       indisvalid,
       indisready
FROM pg_index
WHERE indexrelid = 'idx_orders_customer_id'::regclass;

Expected result:

index_name        | indisvalid | indisready
-------------------------+------------+-----------
 idx_orders_customer_id | t          | t

The table data can be summarized with:

SELECT customer_id, count(*) AS order_count
FROM orders
GROUP BY customer_id
ORDER BY customer_id;
customer_id | order_count
------------+------------
 101        | 2
 202        | 1

While the index build is running, query progress from a second session:

SELECT pid,
       phase,
       blocks_total,
       blocks_done,
       tuples_total,
       tuples_done
FROM pg_stat_progress_create_index
WHERE relid = 'orders'::regclass;

Confirm the planner’s chosen access path with shortened EXPLAIN output:

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT id, status
FROM orders
WHERE customer_id = 101;

On this three-row table, the planner will normally prefer a sequential scan:

Seq Scan on orders
  Filter: (customer_id = 101)
  Rows Removed by Filter: 2

Best Practices

  • Prefer CREATE INDEX CONCURRENTLY for large or active production tables; use an ordinary build for new, empty tables or during a controlled maintenance window.

  • Monitor build duration, lock waits, WAL generation, disk headroom, and replica lag. Lower-traffic periods reduce impact but do not make the operation risk-free.

  • After any failed attempt, confirm whether the index is invalid, remove it concurrently, and retry.

  • Use REINDEX INDEX CONCURRENTLY for a supported bloated standalone index when rebuilding is preferable to dropping and recreating it. It substantially reduces write blocking but still acquires locks and cannot run inside a transaction.

  • Do not assume an index will be used merely because it exists; inspect EXPLAIN (ANALYZE, BUFFERS) and maintain accurate statistics with ANALYZE.

25

How do you find unused or duplicate PostgreSQL indexes?

Unused indexes are identified by reviewing pg_stat_user_indexes.idx_scan over a representative workload period, while duplicate-index candidates can be found with pg_duplicate_indexes. Neither check is sufficient by itself: statistics are cumulative, and an index with no recorded scans may enforce a constraint or support an infrequent workload.

Read full answer

Applies toPostgreSQL 16/17

How Index Usage Statistics Work

pg_stat_user_indexes reports cumulative index activity, including:

  • idx_scan: Number of index scans initiated against the index.

  • idx_tup_read: Index entries read by those scans.

  • idx_tup_fetch: Live table rows fetched by simple index scans.

A value of idx_scan = 0 means the index has not been scanned since the relevant statistics were reset. It does not prove the index is unnecessary. Common causes include low traffic, a recent statistics reset, seasonal workloads, or queries executed on another server.

Take snapshots over a meaningful interval instead of resetting production statistics. Accessing statistics for objects owned by other users may require membership in the pg_monitor or pg_read_all_stats role.

How Duplicate Detection Works

pg_duplicate_indexes groups indexes on the same table whose key-column definitions and predicates match. Treat its output as a candidate list, not proof that every grouped index is interchangeable: uniqueness and included (INCLUDE) columns can still affect semantics and performance.

For example, these indexes have matching keys and predicates:

CREATE INDEX orders_customer_id_idx ON orders (customer_id);
CREATE INDEX orders_customer_id_idx2 ON orders (customer_id);

Review each candidate’s complete definition with pg_get_indexdef(). Differences in uniqueness, included columns, operator classes, collations, sort options, expressions, or predicates can make indexes non-interchangeable.

How to Validate Candidates

Use several signals before deleting an index:

  • Confirm idx_scan remains zero over a representative period.

  • Run EXPLAIN (ANALYZE, BUFFERS) for important queries against production-scale data or a recent snapshot.

  • Check whether the index backs a primary key, unique constraint, exclusion constraint, or replica identity.

  • Verify that removal will not make foreign-key operations, cascading deletes, or reporting queries scan the entire table.

  • Monitor each PostgreSQL replica separately; query traffic on a replica does not appear in the primary’s counters.

EXPLAIN ANALYZE executes the statement, so use it carefully with expensive statements or statements with side effects. On a tiny table, PostgreSQL may correctly choose a sequential scan even when a suitable index exists.

Code Example

The following PostgreSQL 16-compatible example creates a small table, two duplicate indexes, and demonstrates the relevant catalog views:

CREATE SCHEMA index_audit_demo;

CREATE TABLE index_audit_demo.orders (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id bigint NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now()
);

INSERT INTO index_audit_demo.orders (customer_id)
VALUES (101), (202);

CREATE INDEX orders_customer_id_idx
    ON index_audit_demo.orders (customer_id);

CREATE INDEX orders_customer_id_idx2
    ON index_audit_demo.orders (customer_id);

ANALYZE index_audit_demo.orders;

-- Cumulative index usage.
SELECT s.schemaname,
       s.relname AS table_name,
       s.indexrelid::regclass AS index_name,
       s.idx_scan
FROM pg_stat_user_indexes AS s
ORDER BY s.indexrelid;

-- Duplicate-index candidates.
SELECT i.indexrelid::regclass AS index_name,
       i.indrelid::regclass AS table_name,
       d.duplicates
FROM pg_duplicate_indexes AS d
JOIN pg_index AS i ON i.indexrelid = d.indexrelid
ORDER BY i.indexrelid;

-- Zero-scan candidates, excluding important index semantics.
SELECT s.schemaname,
       s.relname AS table_name,
       s.indexrelid::regclass AS index_name,
       s.idx_scan
FROM pg_stat_user_indexes AS s
JOIN pg_index AS i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0
  AND NOT i.indisprimary
  AND NOT i.indisunique
  AND NOT i.indisexclusion
  AND NOT i.indisreplident
  AND NOT EXISTS (
      SELECT 1
      FROM pg_constraint AS c
      WHERE c.conindid = i.indexrelid
  )
ORDER BY s.indexrelid;

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT *
FROM index_audit_demo.orders
WHERE customer_id = 101;

DROP SCHEMA index_audit_demo CASCADE;

Typical result rows before EXPLAIN are:

  • Statistics: index_audit_demo.orders_customer_id_idx | 0 and index_audit_demo.orders_customer_id_idx2 | 0.

  • Duplicate detection: both indexes are returned for index_audit_demo.orders.

  • Table rows: (1, 101) and (2, 202).

A shortened EXPLAIN result on this tiny table may show:

Seq Scan on index_audit_demo.orders
Filter: (customer_id = 101)
Rows Removed by Filter: 1

That plan does not demonstrate that the index is unused; the planner selected the cheaper plan for a two-row table.

Production Removal Checklist

  • Save the definition with pg_get_indexdef() so the index can be recreated during rollback.

  • Check dependencies using pg_constraint.conindid and inspect pg_index.indisprimary, indisunique, indisexclusion, and indisreplident.

  • Prefer concurrent removal for a non-partitioned index with DROP INDEX CONCURRENTLY schema_name.index_name;. It reduces write blocking but takes longer, cannot run inside a transaction block, and may leave an invalid index if interrupted. It is not supported for partitioned indexes.

  • Monitor plans and latency after removal. Recreate immediately if regressions appear.

  • Account for replication: physical standbys receive the DDL, while logical replication subscribers require coordinated index changes.

  • Reduce operational overhead: removing duplicates saves index storage, cache pressure, write amplification, WAL volume, and maintenance time.

26

How do you interpret EXPLAIN ANALYZE output in PostgreSQL?

EXPLAIN ANALYZE executes the statement and merges the planner’s estimated costs with the executor’s measured timings, row counts and buffer usage. Reading the plan tells you where time and I/O were spent and whether the planner’s row estimates matched what actually happened.

Read full answer

Applies toPostgreSQL 16 and 17

Reading the Plan Tree

The plan is printed with the root node at the top; the executor pulls tuples upward from the leaf nodes, so a parent’s timing includes its children’s work.

  • Seq Scan: reads every row of the table; often correct for small tables or queries returning a large share of rows.

  • Index Scan: reads matching index entries, then fetches the heap rows.

  • Index Only Scan: uses only the index, if it covers the query and the visibility map allows skipping heap fetches.

  • Bitmap Heap Scan: builds a bitmap of index matches, then reads the heap in physical order.

  • Nested Loop: runs the inner side once per outer row; a large loops= value exposes repeated work.

  • Hash Join / Merge Join: builds a hash table or sorts inputs; compare their memory and temp-file usage when alternatives exist.

  • Gather: merges output produced by parallel workers.

Because ANALYZE runs the statement to completion, the reported timings cover all rows, not a sample. PostgreSQL 17 also adds the SERIALIZE option, written EXPLAIN (ANALYZE, SERIALIZE), which measures the time and bytes needed to convert the result rows into the wire format sent to the client.

Understanding the Measurements

Output Meaning Operational interpretation
actual time=start..total Time to the first tuple, and time to finish the node Both include child-node time; total is inclusive, so never add start to it
rows=... loops=... Average rows per loop, and number of executions Multiply rows × loops for the node’s total output
Buffers: shared hit Page found in the shared buffer pool CPU/memory work; PostgreSQL issued no read
Buffers: shared read Page read from the operating system High values suggest a cold cache or too small a shared_buffers
Buffers: shared dirtied / written Page modified in shared buffers / page flushed to disk Dirty-page and checkpoint pressure
Buffers: temp read/written Temporary file I/O Usually large sorts, hashes or poor join choices
WAL WAL records and bytes generated by the statement Write amplification; only reported with EXPLAIN (WAL)
Planning Time Time spent choosing the plan Matters with many partitions or complex schemas
Execution Time Total executor time Main runtime metric; excludes planning

A shared hit means the page was found in PostgreSQL’s shared buffer pool (server RAM). A shared read means PostgreSQL had to ask the OS for the page, and that read may still be served from the OS page cache, so it does not necessarily mean physical disk I/O.

Comparing Estimates with Actual Results

Compare the estimated rows= with the actual rows= reported by EXPLAIN ANALYZE; width= is an estimated average row width in bytes, not a row count. A large gap points to an estimation problem rather than automatically to a bad plan.

Typical causes:

  • Stale table statistics; run ANALYZE table_name.

  • Correlated columns the planner assumes are independent.

  • Skewed or poorly distributed data.

  • Generic prepared plans whose parameter values differ from those examined at planning time.

  • Missing or unsuitable indexes for selective predicates.

Use EXPLAIN (ANALYZE, BUFFERS, WAL, SETTINGS) when investigating execution behavior; SETTINGS shows the non-default planner settings that affected the decision.

Production Workflow and Monitoring

First run plain EXPLAIN to inspect a safe estimated plan; add ANALYZE only when running the query is acceptable.

For production incidents:

  • Correlate slow plans with pg_stat_statements, using mean_exec_time and calls.

  • Check pg_stat_user_tables for seq_scan, idx_scan, n_live_tup and n_dead_tup.

  • Inspect pg_stat_activity for active queries, wait_event_type/wait_event and parallel workers, and pg_locks for lock holders and waiters.

  • Check pg_statio_user_tables for read and hit ratios.

  • Compare plans before and after statistics or index changes.

  • Use log_min_duration_statement or auto_explain to capture recurring slow plans.

Code Example

This PostgreSQL 16-compatible example creates and analyzes a small table:

CREATE TABLE orders (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id integer NOT NULL,
    amount numeric(10,2) NOT NULL
);

CREATE INDEX orders_amount_idx ON orders (amount);

INSERT INTO orders (customer_id, amount) VALUES
    (1, 25.00), (1, 75.50), (1, 45.00), (2, 40.00);

ANALYZE orders;

SELECT customer_id, count(*) AS order_count
FROM orders
WHERE amount >= 40.00
GROUP BY customer_id
ORDER BY customer_id;

Result:

customer_id | order_count
-------------+-------------
           1 |           2
           2 |           1

Inspect its actual execution:

EXPLAIN (ANALYZE, BUFFERS, WAL)
SELECT customer_id, count(*)
FROM orders
WHERE amount >= 40.00
GROUP BY customer_id;

A shortened plan may look like:

GroupAggregate (actual time=0.03..0.04 rows=2 loops=1)
  -> Sort (actual time=0.02..0.03 rows=2 loops=1)
        Sort Key: orders.customer_id
        -> Seq Scan on orders (actual time=0.01..0.02 rows=3 loops=1)
              Filter: (amount >= 40.00)
              Rows Removed by Filter: 1
              Buffers: shared hit=1
Planning Time: 0.15 ms
Execution Time: 0.07 ms

The exact plan depends on statistics, indexes, settings and hardware.

Common Pitfalls and Best Practices

  • Avoid production DML with EXPLAIN ANALYZE: it takes locks, fires triggers and generates WAL. Wrap reversible statements in a transaction, but sequence values consumed by rolled-back inserts are not restored.

  • Do not judge a plan only by scan type: sequential scans are correct and efficient for many workloads.

  • Do not treat timings as a benchmark: instrumentation, cache state and concurrent activity affect results.

  • Avoid sharing raw plans publicly: filters and verbose plans can expose sensitive values.

  • Fix the root cause: stale statistics need ANALYZE; repeated inner scans may need a different join order, index or rewritten query.

27

How do PostgreSQL sequential, index, and bitmap heap scans differ?

PostgreSQL distinguishes sequential scans, index scans, and bitmap heap scans by how they locate and read heap tuples. The planner normally chooses among them using cost estimates, selectivity, index availability, and the physical relationship between index entries and heap pages.

Read full answer

Applies toPostgreSQL 16/17

Core Differences

Scan type How the heap is read Index usage Usually most suitable for
Sequential scan Reads heap pages in physical order and evaluates the predicate for each row None Queries matching a large portion of a table, missing indexes, or inexpensive filters
Index scan Uses index entries containing tuple identifiers to fetch matching heap tuples, often in scattered physical order Directly reads matching index entries Highly selective predicates where random heap access costs less than scanning the table
Bitmap heap scan Builds a bitmap of matching tuple identifiers, then visits the relevant heap pages in physical order Uses one or more bitmap-capable indexes Moderately selective predicates whose matches are scattered across many heap pages
  • Sequential scan: Provides good physical locality, supports read-ahead, and can use parallel workers through a Parallel Seq Scan node. Its disadvantage is reading and filtering many rows that do not match.

  • Index scan: Can be efficient for a small number of matches and can avoid an additional sort when the B-tree index already provides the required ordering. Its disadvantage is potentially random heap I/O and additional index-page reads.

  • Bitmap heap scan: Reduces random heap I/O by reading relevant pages in physical order. It generally does not preserve the index’s ordering, so the planner may need an additional sort. Multiple bitmap index scans can be combined with bitmap-and or bitmap-or operations.

Planner Selection and Plan Reading

  • The planner estimates matching rows and compares the cost of sequential pages, index entries, and heap-page access.

  • A sequential scan becomes attractive when a predicate matches many rows or when the table is too small for index overhead to pay off.

  • An index scan generally wins for a very selective predicate, although poor heap correlation can make it less efficient than expected.

  • A bitmap heap scan is often a middle ground: it reads more heap data than an index scan but usually with better physical locality.

  • An Index Only Scan is a separate plan type. It avoids heap access only when the required columns are available from the index and the heap pages are marked all-visible in the visibility map.

  • EXPLAIN identifies these methods through plan-node names such as Seq Scan, Index Scan, Bitmap Heap Scan, and Bitmap Index Scan. A bitmap plan normally has a child Bitmap Index Scan that supplies the tuple bitmap.

Planner settings such as enable_seqscan, enable_indexscan, and enable_bitmapscan can help isolate access-method behavior during diagnosis. They should not be used as routine production tuning because they replace cost-based selection with forced plans.

Operational Consequences

  • I/O behavior: Sequential and bitmap heap scans favor physical locality, while index scans may perform many random heap reads. Buffer-cache hit rates and storage latency therefore affect which method is cheapest.

  • Bitmap memory pressure: Bitmap construction is influenced by work_mem. Under memory pressure, PostgreSQL can create a lossy bitmap that records page numbers but not exact tuple positions. It must then recheck the predicate for candidate rows, increasing CPU work.

  • Bloat: Heap and index bloat increases the number of pages visited, including when only a small number of rows match.

  • MVCC and vacuum: Long-running queries hold snapshots that can prevent removal of dead tuples. Long-running transactions are especially problematic because they can block vacuum cleanup and contribute to transaction-ID wraparound risk.

  • Planning statistics: Stale or insufficient statistics can lead to a poor access-method choice. ANALYZE keeps planner estimates aligned with major data changes.

  • Execution risk: EXPLAIN ANALYZE executes the statement and therefore consumes real resources. An ordinary SELECT does not modify ordinary tables, but it can invoke side-effecting functions. For data-modifying statements, use an explicit transaction and roll it back when the effects must not be retained.

Monitoring and Best Practices

  • Use pg_stat_user_tables to monitor cumulative seq_scan and idx_scan activity along with n_live_tup and n_dead_tup. These counters do not distinguish ordinary index scans from bitmap index scans.

  • Use pg_statio_user_tables to compare heap_blks_read, idx_blks_read, and their buffer-cache hit counterparts.

  • With pg_stat_statements enabled, inspect execution time, rows returned, and shared-buffer reads for expensive statements.

  • Diagnose with EXPLAIN (ANALYZE, BUFFERS) against representative data and realistic statistics. A plan that looks appropriate on a small test table may perform differently under production volume and concurrent load.

  • Treat unusually high sequential-scan counts as a signal to investigate, not automatic proof that indexes are missing. Large reporting queries, missing indexes, stale statistics, and genuine low-selectivity workloads can all make sequential scans appropriate.

28

How do index-only scans and PostgreSQL's visibility map work?

An index-only scan lets PostgreSQL obtain all values needed by a query from an index, potentially avoiding heap-page access. The visibility map records whether heap pages are entirely visible to every transaction, allowing the executor to determine when that heap access can safely be skipped.

Read full answer

Applies toPostgreSQL 16/17

How an Index-Only Scan Works

A regular index scan usually performs two kinds of work:

  1. It reads index entries to locate matching rows.

  2. It fetches the corresponding heap tuples to retrieve columns not stored in the index.

An index-only scan is possible when the selected index contains every column required by the query. For example, an index on (email, id) can satisfy a query selecting email and id without needing those values from the heap.

The executor still must establish that each index entry represents a tuple visible to the current transaction’s MVCC snapshot. PostgreSQL uses the visibility map to make this check cheaper:

Visibility-map state Executor behavior
Page is marked all-visible The index tuple can be returned without fetching the heap page
Page is not marked all-visible, or the bit is unknown PostgreSQL fetches the heap page and checks tuple visibility

The Heap Fetches value in EXPLAIN (ANALYZE, BUFFERS) shows how many heap fetches occurred. A value of zero indicates that the scan avoided the heap, although index pages were still read.

The planner can use a covering index, an index with INCLUDE columns, a partial index whose predicate is implied by the query, or a suitable expression index. Bitmap index scans do not provide index-only execution.

The Visibility Map

The visibility map is an auxiliary structure associated with a heap relation. It is stored in the relation’s vm fork and contains per-page state, including:

  • All-visible: all tuples on the page are visible to every transaction.

  • All-frozen: all tuples on the page are frozen, helping anti-wraparound vacuum work.

The map is conservative. A cleared bit means “not known to be all-visible,” not that a particular tuple is invisible or that the page contains dead tuples. It does not replace MVCC visibility checks for individual tuples.

VACUUM and autovacuum update the visibility map. A long-running transaction can prevent cleanup and all-visible marking on pages affected by its changes, while also retaining dead tuples and increasing bloat. The all-frozen state reduces the freezing work required as transaction IDs advance.

Visibility-map changes are not WAL-logged, so physical standbys do not receive newly set bits. After promotion, VACUUM can repopulate the map; after crash recovery, recently set bits may be absent until VACUUM runs. This affects index-only-scan performance, not MVCC correctness.

Code Example

The following example runs on PostgreSQL 16:

DROP TABLE IF EXISTS customers;

CREATE TABLE customers (
    id bigint PRIMARY KEY,
    email text NOT NULL,
    status text NOT NULL
);

CREATE UNIQUE INDEX customers_email_id_idx
    ON customers (email, id);

INSERT INTO customers (id, email, status) VALUES
    (1, 'alice@example.com', 'active'),
    (2, 'bob@example.com', 'active'),
    (3, 'carol@example.com', 'inactive');

ANALYZE customers;

-- Demonstration only: discourage a sequential scan on this tiny table.
SET enable_seqscan = off;

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT id, email
FROM customers
WHERE email = 'alice@example.com';

VACUUM (ANALYZE) customers;

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT id, email
FROM customers
WHERE email = 'alice@example.com';

-- Restore the session's normal planner configuration.
RESET enable_seqscan;

A shortened result before vacuum is typically:

Index Only Scan using customers_email_id_idx on customers
  Index Cond: (email = 'alice@example.com'::text)
  Heap Fetches: 1

After VACUUM, it is typically:

Index Only Scan using customers_email_id_idx on customers
  Index Cond: (email = 'alice@example.com'::text)
  Heap Fetches: 0

The query returns:

id | email
----+-----------------
  1 | alice@example.com

For direct inspection, the optional pg_visibility extension provides pg_visibility_map():

CREATE EXTENSION IF NOT EXISTS pg_visibility;
SELECT * FROM pg_visibility_map('customers');

The extension is part of PostgreSQL’s optional contrib packages and may require administrative privileges to install.

Production Operations

  • Monitoring: Track n_live_tup, n_dead_tup, last_vacuum, last_autovacuum, vacuum_count, and autovacuum_count in pg_stat_all_tables. Use pg_stat_progress_vacuum to observe a vacuum currently running.

  • Capacity and bloat: An index-only scan does not eliminate index or heap bloat. Monitor index size, cache-hit ratios, and unexpected heap fetches; use REINDEX or a carefully planned VACUUM FULL only when appropriate because the latter requires strong locks and rewrites the table.

  • Maintenance: Do not assume a newly loaded or heavily modified table has accurate visibility-map state. Schedule or tune autovacuum for write-heavy tables and ensure long transactions are identified as a likely cause of delayed cleanup.

  • Backups and upgrades: The visibility map is not a substitute for backups. Test PITR procedures separately, and verify vacuum and index maintenance after major-version upgrades.

  • Security: An index-only scan is not an authorization boundary. The user still needs the appropriate privileges on the table, and operational tools exposing EXPLAIN, statistics, or schema metadata should not be made public.

29

Why does PostgreSQL ignore an index during query planning?

PostgreSQL may ignore an index when the cost-based planner believes a sequential scan is cheaper, or when the query predicate is incompatible with the index definition. An available index is not an instruction to use it; the planner compares estimated costs and chooses the lowest-cost execution plan.

Read full answer

Applies toPostgreSQL 16 and PostgreSQL 17

How the Planner Chooses an Index

PostgreSQL analyzes the query, estimates its selectivity using statistics, and generates candidate plans. These commonly include:

  • Index Scan: Reads matching index entries and fetches corresponding heap tuples.

  • Index Only Scan: Avoids heap access when the index contains every required column and the relevant heap pages are marked all-visible in the visibility map.

  • Bitmap Heap Scan: Builds a bitmap of matching locations, then visits heap pages in physical order.

  • Sequential Scan: Reads the entire table and evaluates the filter.

The planner chooses based on estimated row counts, page counts, correlation, random I/O cost, CPU cost, and sort requirements. Statistics errors can therefore produce the wrong choice.

Common Reasons an Index Is Rejected

Reason Example or operational consequence
Low selectivity A predicate matching most rows may make a sequential scan cheaper. This is normal for small tables.
Unsupported expression An index on email cannot normally support LOWER(email) = ...; an expression index on LOWER(email) may.
Leading wildcard A B-tree index on email cannot accelerate LIKE '%@example.com'. pg_trgm can support substring searches.
Partial-index mismatch A partial index is usable only when the query predicate implies its WHERE condition. Generic prepared plans generally cannot establish that implication from parameter values.
Wrong column order A multicolumn B-tree normally requires suitable query predicates on its leading columns; predicates on later columns may then be usable.
Type, cast, or operator mismatch Wrapping an indexed column in an incompatible expression can prevent use of the original index.
Collation or operator-class mismatch Index ordering depends on the operator class and, for ordered comparisons and pattern matching, the collation. The query must use operators supported by the index.
Stale statistics Incorrect row estimates can make an index appear more expensive than it is. Run ANALYZE after significant data changes.
Invalid index A failed CREATE INDEX CONCURRENTLY can leave an invalid index that the planner ignores.
Ordering or join requirements The planner may prefer another index that already satisfies an ORDER BY or a join strategy.

Index-Type Compatibility

Different PostgreSQL index types support different operators and access patterns:

  • B-tree: Equality, ranges, ordering, and prefix matching with suitable operator classes.

  • GIN: Arrays, JSON containment, full-text search, and other specialized operators.

  • GiST: Ranges, geometry, nearest-neighbor searches, and other extensible types.

  • BRIN: Very large tables whose physical row order correlates with indexed values.

An index can exist and be valid but still be inappropriate for the query’s operator or access pattern. Extensions such as pg_trgm or PostGIS can provide access methods that ordinary B-tree indexes do not support.

Diagnosing the Decision

Use EXPLAIN (ANALYZE, BUFFERS) to compare estimated and actual row counts, inspect the chosen node type, and see buffer activity. Large differences between estimated and actual rows usually indicate stale statistics, correlated predicates that require extended statistics, or a distribution different from the planner’s assumptions.

EXPLAIN ANALYZE executes the statement, so use it carefully with expensive queries and never use it casually for modifying statements. For a leading-wildcard search, a sequential scan is expected even when a normal B-tree index exists; a trigram index is a more appropriate alternative.

To identify invalid indexes, inspect pg_index.indisvalid and pg_index.indisready, joining pg_index.indexrelid to pg_class.oid. An invalid index is not considered by the planner and should be repaired or removed.

Production Monitoring and Best Practices

  • Compare estimated and actual rows using EXPLAIN (ANALYZE, BUFFERS). Large differences usually indicate stale or insufficient statistics.

  • Monitor pg_stat_user_indexes.idx_scan, idx_tup_read, and idx_tup_fetch. These counters show index activity, not indexes the planner considered and rejected.

  • Monitor pg_stat_user_tables.seq_scan and seq_tup_read. Sequential scans on small tables are normal; repeated scans across large tables can indicate a missing or ineffective index.

  • Use extended statistics for important multicolumn dependencies and distinctness estimates, then run ANALYZE.

  • Do not permanently disable sequential scans with enable_seqscan = off; use that setting only for short diagnostic experiments.

  • Remember that indexes consume storage and shared-buffer capacity, and index changes generate WAL. Remove an unused index only after checking workload and replica behavior.

  • Replicas can select different plans when their statistics or data distribution differ, so compare plans on each production node.

30

What do PostgreSQL table statistics and ANALYZE do?

Table statistics are planner estimates about relation size, row distribution, nullability, and value frequency. ANALYZE samples table data and refreshes those statistics in PostgreSQL system catalogs without changing user data.

Read full answer

Applies toPostgreSQL 16 and 17

Statistics Collected

PostgreSQL maintains several kinds of planner statistics:

  • Relation statistics: pg_class.reltuples estimates the number of rows, while relpages estimates the relation’s total pages. relallvisible counts pages known to be visible to VACUUM; it is not a general visibility count.

  • Column statistics: Include null fraction, average value width, estimated distinct-value count, most-common values, histograms, and physical correlation. PostgreSQL stores them internally in pg_statistic and exposes filtered results through the pg_stats view.

  • Index and constraint information: Index size and row estimates help estimate lookup selectivity. Unique constraints and primary keys are backed by unique indexes, whose uniqueness metadata also helps the planner.

  • Extended statistics: Multicolumn statistics can model functional dependencies, cross-column distinctness, and multivariate most-common values. PostgreSQL also supports statistics on a single expression.

What ANALYZE Does

ANALYZE performs the following work:

  1. Acquires a SHARE UPDATE EXCLUSIVE lock on each table being analyzed.

  2. Samples rows, normally reading only a portion of the table; small tables may be read in full.

  3. Calculates relation, column, index-related, and applicable extended statistics.

  4. Updates pg_class, pg_statistic, and, where applicable, pg_statistic_ext_data.

  5. Makes the refreshed estimates available to subsequent planning operations.

The default_statistics_target setting controls the desired amount of statistical detail and defaults to 100. It influences sampling effort, histogram resolution, and the number of most-common values retained. A column can have a different target with ALTER TABLE ... ALTER COLUMN ... SET STATISTICS 500. Higher targets can improve estimates for skewed data but increase analysis time and catalog storage.

Effect on Query Planning

The planner uses these estimates to estimate:

  • How many rows a filter will return.

  • Whether an index scan, bitmap heap scan, or sequential scan is cheaper.

  • Join cardinality and join order.

  • The number of rows affected by an UPDATE or DELETE.

Statistics are estimates, not exact counts. Rare values, correlated columns, rapidly changing distributions, and poorly sampled data can produce inaccurate plans. Extended statistics can improve estimates for supported multicolumn cases, but they do not solve every correlation problem.

Production and Monitoring

Autovacuum automatically runs ANALYZE when the estimated number of inserted, updated, or deleted rows since the previous analysis exceeds autovacuum_analyze_threshold plus autovacuum_analyze_scale_factor multiplied by the table’s estimated row count. Per-table settings can override the global values.

Manual analysis is appropriate after a large bulk load, before a performance-sensitive migration, or when autovacuum cannot keep up. Useful fields in pg_stat_user_tables and pg_stat_all_tables include:

  • last_analyze: Time of the last manual ANALYZE.

  • last_autoanalyze: Time of the last autovacuum-triggered analysis.

  • analyze_count and autoanalyze_count: Execution counts.

  • n_live_tup and n_dead_tup: Estimated live and dead row counts.

A long-running transaction can delay vacuum-related cleanup, but it does not normally prevent ANALYZE from sampling the table. Analysis can contend with schema changes because its lock conflicts with many ACCESS EXCLUSIVE DDL operations, although ordinary reads and writes remain possible.

Best Practices and Common Pitfalls

  • Analyze after bulk loads: Otherwise, the planner may continue using estimates based on the table’s previous distribution.

  • Do not treat statistics as exact: Distinct counts, selectivity, and row counts can be sampled estimates.

  • Keep ANALYZE distinct from VACUUM: Analysis refreshes planner metadata; it does not reclaim dead tuples, repair visibility-map problems, or resolve transaction ID wraparound.

  • Use EXPLAIN (ANALYZE, BUFFERS) carefully: It executes the statement, so it can cause real side effects.

  • Raise statistics targets selectively: More detail helps skewed or multicolumn predicates, but increases analysis cost and catalog storage.

31

What is Multi-Version Concurrency Control in PostgreSQL?

PostgreSQL uses Multi-Version Concurrency Control (MVCC) to give readers snapshots of row versions, allowing consistent reads while concurrent transactions write. Depending on the isolation level, a snapshot lasts for one statement or the whole transaction; ordinary reads generally do not wait on row-level writer locks, while writes and explicit locking queries still acquire locks.

Read full answer

Applies toPostgreSQL 16 and 17

How PostgreSQL Implements MVCC

MVCC is implemented primarily in the storage layer using transaction metadata stored in each heap tuple:

  • xmin: transaction identifier of the transaction that inserted the row version.

  • xmax: identifier of the transaction that deleted, updated, or locked the row; it can hold a MultiXact ID when multiple transactions hold locks.

  • cid: command counter used to order commands within a transaction.

An UPDATE normally marks the old tuple obsolete and creates a new tuple version. A DELETE marks the tuple as deleted. A heap-only update can place the new version on the same page, avoiding an index update, provided there is sufficient free space.

PostgreSQL determines visibility by comparing tuple metadata, transaction status, and the reader’s snapshot:

  • Uncommitted changes are invisible to other sessions.

  • Aborted changes are ignored.

  • Committed changes are visible if they fall within the snapshot.

  • MVCC controls logical visibility; it does not eliminate write locks or DDL locks.

Old row versions remain physically present until no relevant snapshot needs them and VACUUM removes or makes their space reusable. WAL provides crash recovery and streaming-replication durability, while tuple hint bits can cache known transaction status and reduce repeated status checks.

Isolation Levels and Locking

Isolation level Snapshot behavior Important consequence
Read Committed Each statement receives a new snapshot Default; concurrent transactions can see different committed states
Repeatable Read One snapshot is retained for the transaction Concurrent updates can cause a serialization failure
Serializable Uses snapshots plus Serializable Snapshot Isolation Dangerous dependency structures can cause transaction aborts

An UPDATE or DELETE locks the target tuple. Under Read Committed, a blocked writer waits and then re-evaluates its condition against the newer row version; if the row no longer matches, the statement can update zero rows. Under Repeatable Read and Serializable, a conflicting update generally produces a serialization failure with SQLSTATE 40001.

Plain SELECT statements do not wait for row-level writer locks, although DDL and other relation-level conflicts can still block them. SELECT FOR UPDATE, SELECT FOR SHARE, and similar queries intentionally acquire locks and can create contention or serialization anomalies.

Operational Consequences

  • Bloat: Updates and deletes leave obsolete tuples and associated index entries that consume space until maintenance.

  • Space reuse: VACUUM makes dead-tuple space reusable by PostgreSQL, but returning space to the operating system is limited and can be delayed.

  • Long transactions: An old snapshot can prevent removal and freezing of row versions, increasing bloat, query latency, and storage pressure.

  • Transaction-ID wraparound: Old transaction IDs must eventually be frozen. A transaction that blocks anti-wraparound vacuum can create a serious availability risk.

  • Statistics lag: n_dead_tup and related values are estimates and can differ temporarily from exact tuple counts.

  • Partitioning: Partitioning can simplify retention, but every partition still requires vacuum and index maintenance.

VACUUM removes or reclaims obsolete tuples and performs freezing when required. autovacuum normally performs this work, including anti-wraparound vacuum, but aggressive write rates, insufficient worker capacity, or long-running transactions can exceed its effective capacity.

Monitoring MVCC Health

  • pg_stat_activity: Inspect xact_start, state, backend_xmin, wait fields, and the active query. A non-null backend_xmin indicates a snapshot horizon that may hold back tuple cleanup.

  • pg_stat_user_tables: Track n_live_tup, n_dead_tup, last_autovacuum, autovacuum_count, and related statistics. Dead-tuple counts are approximate, so confirm growth with disk and table-size trends.

  • pg_database: Track datfrozenxid and the transaction-ID age of datfrozenxid to alert before wraparound becomes urgent.

  • Additional signals: Monitor pg_stat_progress_vacuum, transaction age, table and index size, WAL generation, disk saturation, replication lag, and backup or archive failures.

A long transaction with an old xact_start is not necessarily preventing vacuum; backend_xmin and the corresponding table’s cleanup progress provide more direct evidence.

Production Practices

  • Do not disable autovacuum globally. Tune per-table thresholds, storage parameters, and fillfactor for high-churn tables, especially where heap-only updates are useful.

  • Set statement_timeout and idle_in_transaction_session_timeout according to workload behavior. The latter can terminate stuck clients and roll back their transactions, but an aggressive value can also disrupt healthy long-running work.

  • With PgBouncer transaction pooling, require applications to commit or roll back promptly. A transaction left open pins its server-side snapshot and associated resources.

  • Use pg_stat_statements for workload analysis and the optional pgstattuple extension for detailed bloat inspection.

  • Deleted values may remain in dead heap space, WAL, and existing physical backups. VACUUM cannot alter an already-taken backup; enforce separate backup-expiration and WAL-retention policies.

  • Restrict filesystem, superuser, backup, WAL, and monitoring access, and encrypt data in transit and at rest.

  • MVCC consistency does not replace backups or point-in-time recovery. Retain the required WAL, archive it reliably, and test full and point-in-time restores regularly.

32

What do PostgreSQL heap tuples, xmin, and xmax represent?

In PostgreSQL, a heap tuple is one physical version of a table row. xmin records the transaction ID (XID) that inserted that version, while xmax identifies the deleting transaction or the row-level locking information associated with it.

Read full answer

Applies toPostgreSQL 16 and 17

Tuple and System Columns

A heap tuple contains the row’s column values, tuple metadata, and transaction-visibility information. Important system columns include:

Column Meaning
xmin XID of the transaction that inserted the tuple
xmax XID of the deleting transaction or a MultiXact ID representing row-level lockers; 0 means no such transaction
ctid Current physical location, represented as a block number and tuple offset
tableoid OID of the table or partition containing the tuple

ctid is not a permanent row identifier. An update normally creates a new tuple with a new ctid, and VACUUM FULL can also relocate tuples while rewriting the table.

An INSERT creates a tuple whose xmin is the inserting transaction’s XID. An UPDATE normally creates a new tuple with the updater’s xmin and marks the previous tuple with an xmax. A DELETE similarly marks the affected tuple with xmax.

A row lock can also be represented through xmax. With one locker, PostgreSQL can store the locker’s XID; when multiple locking transactions must be represented, xmax can contain a MultiXact ID. Therefore, xmax is not always a single transaction ID.

MVCC Visibility

PostgreSQL does not determine visibility from xmin and xmax alone. It compares them with the transaction snapshot and consults transaction-status information:

  • A tuple inserted by a committed transaction before the snapshot can be visible.

  • A tuple whose inserting transaction is still running or was aborted is not visible to another session.

  • Once the deleting transaction commits, later snapshots do not see the old tuple.

  • While the deleting transaction is running, other sessions can normally continue seeing the old version.

  • If the deleting transaction aborts, its xmax does not hide the tuple.

After freezing, xmin may become FrozenTransactionId, whose value is 2. It then no longer identifies the original inserting transaction and can be ignored during routine MVCC checks.

Operational Consequences

  • Bloat: Dead row versions consume space until vacuuming can reuse it. VACUUM FULL rewrites the table but requires strong locks and temporarily needs additional disk space.

  • Long transactions: An old snapshot prevents obsolete tuples from being removed. Long-running transactions and sessions that are idle in transaction are therefore more relevant than ordinary idle sessions.

  • Transaction-ID wraparound: Old xmin and xmax values require freezing. PostgreSQL can schedule anti-wraparound autovacuum even on tables with little recent activity.

  • Lock contention: pg_stat_activity identifies active and waiting sessions. pg_locks shows relation locks and transaction-ID waits; individual held tuple locks are generally not listed separately.

  • Indexes: Index entries refer to heap tuples through TIDs. A HOT update can avoid adding an index entry when no indexed column changes and the update fits on the same page.

  • Replication: Physical replication replays WAL and preserves corresponding tuple versions and transaction IDs. Logical replication and pg_dump restore create new row versions with new transaction IDs on the destination.

Monitoring and Best Practices

Use pg_stat_user_tables to monitor estimated n_live_tup and n_dead_tup values, along with vacuum and analyze timestamps. These are estimates rather than exact counts. For a more precise assessment, the pgstattuple extension can inspect tables with a full-table scan, but its operational cost should be considered.

Track transaction-ID age with age(datfrozenxid) from pg_database, and investigate long-running or idle-in-transaction sessions with pg_stat_activity. The predefined pg_monitor role provides broad read-only access to PostgreSQL monitoring information.

Tune per-table autovacuum settings for high-churn tables, alert on transaction-ID age and dead-tuple growth, and use physical backups with continuous WAL archiving when point-in-time recovery is required.

33

Why is VACUUM needed in PostgreSQL?

PostgreSQL’s VACUUM reclaims obsolete row-version space for reuse, maintains visibility metadata, and freezes sufficiently old tuples to prevent transaction-ID wraparound. It is essential because PostgreSQL’s MVCC design does not immediately remove obsolete row versions when rows are updated or deleted.

Read full answer

Applies toPostgreSQL 16 and 17

How VACUUM Works

Under PostgreSQL’s MVCC architecture, an UPDATE creates a new tuple version and marks the old version obsolete, while a DELETE marks the existing tuple dead. An obsolete tuple cannot be removed immediately because an existing transaction or snapshot might still need it.

VACUUM:

  • Scans heap pages through the shared buffer manager.

  • Determines which tuple versions are dead to all remaining snapshots.

  • Marks dead tuple and index-entry space as reusable within the existing relations.

  • Updates the visibility map, allowing index-only scans to skip pages marked all-visible.

  • Freezes old tuples when necessary to limit transaction-ID age and prevent wraparound.

  • Generates WAL activity, so large vacuums can increase replication lag.

A regular VACUUM generally does not return most space to the operating system. It makes that space available for future rows within the existing relation; VACUUM FULL is required to rewrite the relation and return substantial space to the filesystem.

Why PostgreSQL Needs It

Without regular maintenance, obsolete tuples and index entries accumulate, causing:

  • Heap and index bloat: Scans visit more pages, increasing I/O and cache pressure.

  • Poor index-only scans: The visibility map becomes stale, so index-only scans must visit more heap pages.

  • Transaction-ID wraparound risk: An old snapshot can prevent tuples from being frozen, causing transaction IDs to become excessively old.

  • Slower queries and higher resource usage: Larger relations require more buffer, CPU, and temporary-space resources.

  • Storage pressure: Space remains allocated to bloated relations and cannot readily be reused by other relations.

Long-running transactions, idle transactions holding old snapshots, and prepared transactions can hold back tuple removal and freezing by retaining an old xmin horizon.

VACUUM Variants

Operation Behavior Operational impact
VACUUM Reclaims dead tuple and index space for reuse Usually non-disruptive, but consumes I/O and WAL capacity
VACUUM ANALYZE Vacuums the relation and refreshes planner statistics Useful after bulk changes when statistics are stale
VACUUM FULL Rewrites the table and rebuilds its indexes Requires an ACCESS EXCLUSIVE lock and substantial temporary disk space
VACUUM FREEZE Aggressively freezes tuples Useful when transaction IDs are approaching the wraparound threshold
VACUUM VERBOSE Reports detailed processing information Useful for diagnosing long-running maintenance

VACUUM FULL blocks concurrent reads and writes on the table. It needs enough free space for the original and rewritten relation files and should therefore be reserved for planned maintenance rather than used as an automatic response to bloat.

Production Monitoring

Important monitoring sources include:

  • pg_stat_user_tables: Shows estimated n_live_tup and n_dead_tup, row-modification counters, and vacuum and analyze timestamps.

  • pg_stat_progress_vacuum: Shows the current phase, heap-block totals and progress, dead-tuple counts, and index_vacuum_count.

  • pg_stat_activity: Identifies long-running transactions and, through fields such as backend_xmin, snapshots that may prevent cleanup.

  • pg_stat_database: Provides transaction, conflict, temporary-file, and deadlock counters.

  • pg_stat_replication: Helps identify replica lag caused by WAL-intensive maintenance.

  • pg_database: age(datfrozenxid) reports transaction-ID age, while age(datminmxid) reports multixact age.

n_dead_tup is an estimate, not an exact count, so it should be evaluated alongside relation size, query latency, I/O saturation, WAL generation, and replication lag. PostgreSQL can also log autovacuum activity through log_autovacuum_min_duration.

Best Practices and Failure Modes

  • Keep autovacuum enabled and tune per-table thresholds for high-churn relations rather than disabling it globally.

  • Investigate long-running transactions when dead-tuple counts remain high; VACUUM cannot remove versions that an old snapshot still requires.

  • Refresh planner statistics after bulk changes when autovacuum has not yet analyzed the table.

  • Avoid using VACUUM FULL automatically for bloat because its lock and disk-space requirements can turn maintenance into an outage.

  • Control vacuum I/O with autovacuum cost-delay and cost-limit settings when maintenance competes with production traffic.

  • On PostgreSQL 16, grant the MAINTAIN privilege on required relations to a dedicated maintenance role. On PostgreSQL 17, the predefined pg_maintain role provides broader maintenance-command permissions without requiring ownership or superuser.

  • Monitor vacuum duration, bloat, transaction-ID age, WAL volume, disk headroom, and replica lag.

  • Run VACUUM outside an explicit transaction block because PostgreSQL rejects it inside one.

34

How is PostgreSQL autovacuum configured and tuned?

PostgreSQL autovacuum is a background maintenance system that reclaims space from dead row versions, updates table statistics, tracks frozen transaction IDs, and maintains the visibility map without requiring manual VACUUM and ANALYZE commands. It runs automatically on writable servers and can be tuned globally or per table.

Read full answer

Applies toPostgreSQL 16 and 17

How Autovacuum Works

  • The autovacuum launcher wakes periodically—every autovacuum_naptime, normally one minute—and identifies databases and relations requiring work.

  • Autovacuum workers process one relation at a time, subject to autovacuum_max_workers.

  • Vacuum removes obsolete row versions and index entries when no snapshot needs them, updates freeze information, and sets all-visible pages in the visibility map.

  • Analyze refreshes planner statistics so EXPLAIN can choose more accurate plans.

  • Vacuum generally makes space reusable inside PostgreSQL; it does not normally return space to the operating system. VACUUM FULL rewrites and locks the table, while pg_repack can rebuild it with less operational disruption.

Configuration and Tuning

Vacuum is triggered approximately when:

dead tuples >= autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × reltuples

Important controls include:

  • autovacuum_naptime: How frequently the launcher checks for work. Lowering it improves responsiveness but increases catalog and statistics overhead.

  • autovacuum_max_workers: Maximum concurrent vacuum workers. Raising it requires a restart; ensure max_worker_processes is high enough to cover autovacuum workers and other background workers. Changing max_worker_processes also requires a restart.

  • autovacuum_work_mem: Memory available to each worker for vacuum operations. Workers can consume substantial memory collectively.

  • Cost controls: autovacuum_vacuum_cost_delay and autovacuum_vacuum_cost_limit throttle vacuum I/O so it does not overwhelm production traffic.

  • Per-table reloptions: Lower thresholds and scale factors for rapidly changing tables; larger values for large, low-churn tables.

  • Insert-only tables: autovacuum_vacuum_insert_threshold and autovacuum_vacuum_insert_scale_factor trigger vacuum so the visibility map and freeze information remain current.

Production Failure Modes

  • Long-running transactions hold back xmin, preventing dead tuples from being removed. Inspect pg_stat_activity.backend_xmin and prepared transactions.

  • Lock contention: VACUUM takes SHARE UPDATE EXCLUSIVE, which conflicts with some DDL and can delay schema changes.

  • Transaction ID wraparound: Long-lived snapshots, replication connections, or abandoned transactions can make age(datfrozenxid) grow. Anti-wraparound vacuums have priority over ordinary autovacuum work.

  • Bloat: Frequent updates and delayed vacuum produce heap and index bloat. A bloated table can consume substantially more disk than its live data.

  • Operational impact: Vacuum generates WAL and performs index cleanup and I/O, so aggressive settings can affect replication lag and foreground latency.

Monitoring Autovacuum

Use these views to identify stale rows, active vacuum work, blockers, and wraparound risk:

SELECT relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

Representative result:

relname | n_live_tup | n_dead_tup | last_autovacuum
orders  | 100000     | 2500       | 2026-03-10 12:00:00

Other useful views are:

  • pg_stat_progress_vacuum: phase, pages scanned, pages vacuumed, and dead tuples.

  • pg_stat_activity: running vacuum queries, waits, and backend_xmin.

  • pg_stat_database: age(datfrozenxid) for wraparound monitoring.

  • pgstattuple and pgstattuple_approx: deeper bloat analysis when the pgstattuple extension is installed.

n_dead_tup is an estimate, not an exact row count.

Code Example

ALTER SYSTEM must be executed by a superuser or by a role with the privileges of pg_write_all_settings:

ALTER SYSTEM SET autovacuum_naptime = '30s';
ALTER SYSTEM SET autovacuum_work_mem = '512MB';
ALTER SYSTEM SET autovacuum_vacuum_cost_delay = '2ms';
ALTER SYSTEM SET autovacuum_vacuum_cost_limit = 1000;

SELECT pg_reload_conf();

Tune a high-churn table independently:

CREATE TABLE autovacuum_demo (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    status text NOT NULL
);

ALTER TABLE autovacuum_demo SET (
    autovacuum_vacuum_threshold = 5000,
    autovacuum_vacuum_scale_factor = 0.01,
    autovacuum_analyze_threshold = 1000,
    autovacuum_analyze_scale_factor = 0.02
);

ANALYZE autovacuum_demo;

Best Practices

  • Start with per-table tuning based on update rate and table size; avoid aggressive global settings.

  • Do not disable autovacuum routinely, and never assume vacuum replaces backups or WAL archiving required for PITR.

  • Ensure the database has sufficient free disk, watch replication lag during vacuum, and alert on sustained dead tuples, vacuum duration, and transaction ID age.

  • Audit global configuration changes, review autovacuum settings during upgrades, and use EXPLAIN (ANALYZE) after ANALYZE to verify planner estimates.

35

What causes PostgreSQL table and index bloat, and how do you fix it?

Bloat in PostgreSQL means that relation pages contain too little useful data because of dead row versions, obsolete index entries, page splits, fragmentation, or low fill. Regular VACUUM makes dead heap space reusable, but it does not compact pages or shrink relation files; physical compaction requires a rewrite, an index rebuild, or pg_repack.

Read full answer

Applies toPostgreSQL 16 and 17; pg_repack 1.5.x for online repacking

How Bloat Develops

PostgreSQL uses MVCC, so an UPDATE normally creates a new row version while retaining the old version until no snapshot needs it and VACUUM can remove it. A DELETE also leaves an obsolete heap tuple.

  • Table bloat: dead tuples and fragmented or poorly utilized pages.

  • Index bloat: obsolete entries and sparsely populated pages, commonly caused by indexed-column updates, deletes, or page splits from nonsequential insertion.

  • HOT updates: PostgreSQL avoids creating a new index entry when no indexed column changes and the new version fits on the same page.

  • Long transactions: old snapshots prevent vacuum from removing obsolete tuples and can increase transaction-ID wraparound risk.

  • Fill factor: too little reserved space causes extra page splits; excessive reserved space increases relation size.

Bloat consumes disk and shared buffers, increases pages read by scans, and adds vacuum and write I/O. Some free space is normal because PostgreSQL retains it for future reuse.

Diagnosing Bloat

pg_stat_user_tables.n_dead_tup is an estimate, not an exact count; ANALYZE and VACUUM refresh dead-row estimates. Also monitor:

  • pg_stat_user_indexes for index usage and tuple-fetch statistics.

  • pg_stat_activity using xact_start to identify long transactions and idle in transaction sessions.

  • pg_stat_progress_vacuum for vacuum progress.

  • pg_locks together with pg_blocking_pids(pid) for blocking relationships.

  • age(pg_class.datfrozenxid) for transaction-ID age.

The pgstattuple extension provides exact page-level statistics. Use pgstattuple_approx when a full scan of a large relation is too expensive. For B-tree indexes, pgstatindex reports leaf density, fragmentation, deleted pages, and other measurements. Installing pgstattuple may require administrative privileges on managed PostgreSQL services.

Repair Options

Action Returns disk space to the OS Locking Appropriate use
VACUUM No Normal writes continue Routine dead-tuple cleanup
VACUUM FULL Yes Strong ACCESS EXCLUSIVE lock Small tables needing compaction; also rebuilds indexes
REINDEX Yes for the index Blocks table writes Rebuilding a bloated or damaged index
REINDEX ... CONCURRENTLY Yes for the index Brief locks remain B-tree indexes in busy systems
pg_repack Yes Brief strong locks Large-table and index maintenance

VACUUM FULL requires substantial temporary disk space because it writes a replacement relation. REINDEX INDEX CONCURRENTLY cannot run inside a transaction block and, in PostgreSQL 16/17, supports B-tree indexes. If it fails, inspect for an invalid index before retrying. pg_repack generates additional WAL, can cause replication lag, and must be installed separately.

Code Example

This creates a small table, changes indexed data, checks the estimated dead tuples and page statistics, and obtains a shortened execution plan:

CREATE EXTENSION IF NOT EXISTS pgstattuple;

CREATE TABLE public.orders (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id integer NOT NULL,
    total numeric(12,2) NOT NULL
);

CREATE INDEX orders_customer_id_idx
    ON public.orders (customer_id);

INSERT INTO public.orders (customer_id, total) VALUES
    (101, 25.00),
    (102, 40.50),
    (103, 15.25);

-- Changes indexed data, creating old heap and index versions.
UPDATE public.orders
SET customer_id = customer_id + 1
WHERE customer_id IN (101, 102, 103);

ANALYZE public.orders;
SELECT pg_stat_force_next_flush();
SELECT pg_stat_clear_snapshot();

SELECT relname, n_live_tup, n_dead_tup
FROM pg_stat_user_tables
WHERE relname = 'orders';

SELECT * FROM pgstattuple('public.orders');
SELECT * FROM pgstatindex('public.orders_pkey');

EXPLAIN (ANALYZE, BUFFERS)
SELECT id
FROM public.orders
WHERE id = 2;

Representative statistics and shortened plan:

relname | n_live_tup | n_dead_tup
---------+------------+------------
 orders  |          3 |          3

Index Scan using orders_pkey on public.orders
  (cost=0.28..8.30 rows=1 width=8)
  (actual time=0.015..0.016 rows=1 loops=1)

Then perform non-rewriting maintenance and rebuild the affected index:

VACUUM (VERBOSE, ANALYZE) public.orders;
REINDEX INDEX CONCURRENTLY public.orders_pkey;

Production Practices

  • Tune autovacuum for hot or rapidly changing tables rather than weakening it globally:

ALTER TABLE public.orders SET (
    autovacuum_vacuum_scale_factor = 0.02,
    autovacuum_vacuum_threshold = 50,
    autovacuum_vacuum_insert_scale_factor = 0.02,
    autovacuum_vacuum_insert_threshold = 50,
    fillfactor = 80
);
  • fillfactor affects newly written or rewritten pages; it does not compact existing pages automatically.

  • Investigate long transactions and configure a finite idle_in_transaction_session_timeout for suitable application roles.

  • Before VACUUM FULL or pg_repack, verify free disk space, WAL capacity, replication lag, backup and PITR coverage, and expected lock time.

  • Monitor pg_stat_progress_vacuum for vacuum and pg_stat_progress_cluster for operations such as VACUUM FULL; use pg_repack logs for its own progress.

  • Avoid repeated rebuilds without evidence of bloat. Ordinary VACUUM and writes do not compact sparse index pages, but unnecessary rebuilds increase WAL, I/O, and operational risk.

36

How does PostgreSQL handle transaction ID wraparound risks?

Transaction ID wraparound is prevented by PostgreSQL freezing old row versions and maintaining a conservative boundary around transaction IDs that may still affect MVCC visibility. If it cannot advance that boundary safely, PostgreSQL refuses work that requires new transaction IDs and, in severe conditions, may refuse startup or shut down rather than risk data corruption.

Read full answer

Applies toPostgreSQL 16/17

How Wraparound Becomes a Risk

  • PostgreSQL uses a 32-bit transaction ID (xid) space and compares transaction IDs with wraparound-aware ordering.

  • Transaction IDs are assigned when transactions require them for writes; subtransactions can also have transaction IDs. Ordinary sequence values from nextval() do not consume transaction IDs.

  • If an old transaction ID were reused while an old snapshot could still observe affected rows, MVCC visibility could be corrupted.

  • PostgreSQL therefore cannot simply wrap the counter whenever it reaches its maximum value. It must first ensure that sufficiently old row versions no longer depend on their original xmin values.

How PostgreSQL Prevents Unsafe Reuse

  • Freezing: VACUUM can replace an old row version’s xmin with the special FrozenXID value. Frozen rows are treated as older than every normal transaction ID.

  • Frozen-XID cutoffs: pg_database.datfrozenxid records the database-wide freezing cutoff, while pg_class.relfrozenxid records the equivalent cutoff for each relation.

  • Routine freezing: The default vacuum_freeze_min_age is 50 million transactions. A normal vacuum can freeze sufficiently old tuples while also collecting dead rows.

  • Anti-wraparound vacuum: The default autovacuum_freeze_max_age is 200 million transactions. When a relation reaches this age, autovacuum forces a wraparound-prevention vacuum even if ordinary vacuum-skip rules would have skipped the relation.

  • Safe boundary enforcement: If freezing cannot advance sufficiently, PostgreSQL stops accepting transactions that would assign new transaction IDs. At a substantially older boundary, it can refuse to start or terminate to prevent wraparound-related data loss.

The 200-million setting is therefore an operational threshold that gives autovacuum time to act, not the point at which transaction IDs become unsafe to reuse.

Monitoring the Risk

  • pg_database.datfrozenxid: The database-wide frozen-XID cutoff. age(datfrozenxid) derives the number of transactions since that cutoff; pg_stat_database does not expose either value as datfrozenxid or frozen_xid_age.

  • pg_class.relfrozenxid: The per-relation cutoff. An old relation can require anti-wraparound attention even when its table contains little data.

  • pg_stat_activity.backend_xmin: The snapshot horizon associated with a backend. Long-running transactions can retain old row versions and impede vacuum progress.

  • pg_prepared_xacts: Prepared transactions can retain transaction IDs and should not be left indefinitely without investigation.

  • pg_replication_slots.xmin and catalog_xmin: Inactive or lagging replication slots can retain old WAL and catalog state and delay cleanup.

  • pg_stat_all_tables.n_dead_tup: An estimate of dead tuples, useful for identifying relations that vacuum is failing to keep up with.

Alert on frozen-XID age, the age of the oldest relation, anti-wraparound vacuum failures, long-running snapshots, prepared transactions, replication-slot lag, and sustained dead-tuple growth.

Production Best Practices

  • Keep autovacuum enabled and ensure it has sufficient CPU, I/O capacity, worker slots, and WAL throughput. Do not routinely cancel anti-wraparound vacuums.

  • Investigate long-running transactions, abandoned prepared transactions, and inactive logical replication slots before forcing manual maintenance.

  • Use manual VACUUM with freezing when autovacuum cannot meet the deadline. It may generate substantial I/O and WAL while rewriting frozen tuple metadata.

  • Avoid VACUUM FULL as a routine response because it rewrites the table and requires stronger locking.

  • Give monitoring systems the pg_monitor role and maintenance automation pg_maintain or narrowly scoped ownership privileges.

  • Record frozen-XID age and transaction-consumption trends over time; reaching the soft threshold should trigger investigation before the hard safety boundary is approached.

37

How does VACUUM compare with VACUUM FULL in PostgreSQL?

VACUUM makes space from obsolete row versions reusable without normally rewriting the table, while VACUUM FULL rewrites the table and rebuilds its indexes to compact them and return space to the operating system. Both operations are affected by MVCC visibility and long-running transactions.

Read full answer

Applies toPostgreSQL 16–17

Key Differences

Behavior VACUUM VACUUM FULL
Main purpose Remove dead tuples and make space reusable Rewrite and compact the table and indexes
Table rewrite No Yes
Indexes Removes dead index entries for reuse; generally does not return index-file space to the OS Rebuilds indexes from scratch
Locking Generally allows concurrent reads and writes Requires ACCESS EXCLUSIVE
Disk space Little additional space Requires substantial temporary space
Operational impact Usually safe for production use Can cause an outage for the affected table
Space returned to OS Usually little or none Usually substantial, subject to filesystem behavior
Transaction behavior Cannot run inside a transaction block Cannot run inside a transaction block

How It Works

PostgreSQL uses MVCC, so an UPDATE creates a new tuple version and marks the old one as dead, while a DELETE marks the existing version as dead. The old version must remain visible to transactions that might still need it.

  • VACUUM scans relation pages and removes dead tuples when no transaction can see them.

  • The resulting free space is recorded in the relation’s free-space map and can later be reused by inserts and updates.

  • VACUUM can also remove obsolete index entries and maintain visibility information used by index-only scans.

  • It generally does not move live tuples to close gaps, so a heavily fragmented or bloated table may remain physically large.

VACUUM FULL creates a new, compact copy containing the live rows and rebuilds the indexes. The original files are removed after the operation completes, so unused space is normally returned to the operating system.

Neither operation can reclaim tuples that are still required by an open transaction. For example, a long-running report that started before a delete may prevent VACUUM from removing that row and therefore prevent VACUUM FULL from making the table smaller.

Production and Operational Consequences

VACUUM is normally the preferred maintenance operation:

  • It is compatible with ordinary application reads and writes.

  • It can still consume CPU and I/O, especially on large tables.

  • Autovacuum normally handles routine vacuuming and statistics maintenance.

  • Autovacuum never issues VACUUM FULL.

VACUUM FULL is more disruptive:

  • The ACCESS EXCLUSIVE lock blocks queries and writes against the table.

  • Applications waiting for the lock can create a lock queue and increase incident duration.

  • The rewrite temporarily requires space approximately comparable to the table and its indexes, plus WAL and filesystem overhead.

  • It causes substantial I/O and can make recovery from a failed operation harder to schedule.

  • It should be treated as a planned maintenance operation with application coordination, capacity checks, and a tested recovery plan.

A table may appear bloated even when vacuuming is working. The important distinction is between logical bloat, where dead tuples occupy space that can be reused, and physical fragmentation or wasted file space that requires a rewrite.

Monitoring and Common Pitfalls

Monitor vacuum activity with PostgreSQL statistics views:

  • pg_stat_user_tables.n_dead_tup: estimated dead tuples in each table.

  • pg_stat_user_tables.n_live_tup: estimated live tuples.

  • last_autovacuum, last_vacuum, and last_autoanalyze: maintenance timestamps.

  • pg_stat_activity: long-running transactions that may prevent tuple cleanup.

  • pg_stat_progress_vacuum: progress information for ordinary VACUUM operations.

Do not rely solely on n_dead_tup; it is an estimate and may lag reality. Investigate transaction age, table size, index size, autovacuum configuration, and replication or backup activity when vacuuming is not keeping up.

Avoid running VACUUM FULL automatically during peak traffic or without checking available disk space. If reclaiming disk space is not urgent, tune autovacuum and use ordinary VACUUM first. VACUUM FULL must be executed outside a transaction block and should be preceded by a disk-capacity and locking-risk assessment.

38

Which isolation levels does PostgreSQL 16 and 17 support?

PostgreSQL 16 and 17 support Read Committed, Repeatable Read, and Serializable isolation. READ UNCOMMITTED is also accepted, but PostgreSQL treats it exactly like READ COMMITTED rather than permitting dirty reads.

Read full answer

Applies toPostgreSQL 16 and 17

How PostgreSQL Implements Isolation

PostgreSQL uses multiversion concurrency control (MVCC). Instead of overwriting a row immediately, an update creates a new row version. Each transaction sees a snapshot that determines which row versions are visible based on their transaction IDs and commit status.

MVCC allows ordinary reads to proceed without blocking writers or vice versa. Isolation controls the scope of that snapshot and whether PostgreSQL detects conflicting transaction dependencies.

The isolation level is selected per transaction, for example with BEGIN ISOLATION LEVEL REPEATABLE READ. In PostgreSQL’s default autocommit mode, each statement is its own transaction.

Supported Isolation Levels

Level Snapshot behavior Prevents Can still occur
Read Committed A new snapshot is taken for each statement Dirty reads Non-repeatable reads, phantoms, write skew
Repeatable Read One snapshot is used for the entire transaction Dirty reads and non-repeatable reads Write skew and other serialization anomalies
Serializable Snapshot isolation plus Serializable Snapshot Isolation (SSI) Dirty reads, non-repeatable reads, phantoms, write skew and other serialization anomalies Conflicts are prevented by aborting one transaction
Read Uncommitted Treated as Read Committed No additional protection Same anomalies as Read Committed

Read Committed is PostgreSQL’s default and is suitable for most web applications. A transaction may see one version of a row in one statement and a newer version in a later statement.

Repeatable Read gives every statement in the transaction the same snapshot. It is useful for consistent multi-query reports or calculations, but it does not guarantee that concurrent transactions produce a serializable result.

Serializable uses SSI to track dependencies between reads and writes. If PostgreSQL finds a dangerous combination—such as two transactions reading overlapping data and then changing different rows in ways that violate an invariant—it aborts one transaction.

Operational Consequences

  • Write skew: Under Read Committed and Repeatable Read, two transactions can read the same condition and modify different rows. For example, two clinic appointments may both observe one remaining appointment slot and then insert conflicting bookings. Serializable prevents this by aborting one transaction.

  • Serialization failures: Serializable transactions can fail with SQLSTATE 40001, meaning serialization_failure. The application must retry the entire transaction, not merely the failed statement.

  • Deadlocks: Deadlocks can occur at every isolation level and normally produce SQLSTATE 40P01, meaning deadlock_detected. A deadlock victim is rolled back and should also be retried.

  • Long transactions: Repeatable Read and Serializable transactions retain their snapshot until commit or rollback. A long-running transaction can prevent VACUUM from removing obsolete row versions, causing table and index bloat.

  • Transaction ID wraparound: Very old transactions can delay cleanup of transaction IDs. Monitoring long transactions and transaction-ID age is essential even when normal transaction IDs are not close to the wraparound limit.

  • Lock waits: SELECT normally does not lock returned rows against concurrent updates. Statements such as SELECT FOR UPDATE, SELECT FOR SHARE, UPDATE, and DELETE acquire row-level locks and can wait on conflicting transactions.

Choosing an Isolation Level

  • Use Read Committed for ordinary OLTP workloads where each statement needs current data and invariant enforcement is handled by a unique constraint, conditional update, or other database mechanism.

  • Use Repeatable Read when all statements must observe one stable database snapshot and serializable guarantees are unnecessary.

  • Use Serializable when correctness depends on a multi-row or multi-table invariant. Ensure the application recognizes 40001 and retries with bounded exponential backoff and jitter.

  • Avoid long idle transactions. Configure an appropriate idle_in_transaction_session_timeout, and use statement_timeout or lock_timeout where blocking sessions could affect availability.

Monitoring and Incident Diagnosis

Use the following PostgreSQL system views and functions during contention incidents:

  • pg_stat_activity: Inspect xact_start, state, wait_event_type, and wait_event to identify long transactions and sessions waiting on locks.

  • pg_locks: Identify conflicting lock modes and the relations involved.

  • pg_blocking_pids(pid): Return the session IDs blocking a waiting process, which is useful for terminating a carefully selected blocker.

  • Application logs: Record SQLSTATE values such as 40001 and 40P01; the database views do not provide a complete historical count of serialization failures.

  • pg_stat_user_tables: Monitor update and delete activity when diagnosing bloat or autovacuum pressure.

Changing isolation levels does not replace proper transaction design. Short transactions, suitable constraints, retry handling for retryable failures, and alerting on transaction age are still required for predictable production behavior.

39

What does PostgreSQL SERIALIZABLE isolation guarantee?

PostgreSQL’s SERIALIZABLE isolation level guarantees that a successfully committed execution is equivalent to transactions running one after another in some serial order. PostgreSQL implements this with snapshot-based MVCC plus Serializable Snapshot Isolation (SSI), aborting a transaction with SQLSTATE 40001 when necessary to prevent a serialization anomaly such as write skew.

Read full answer

Applies toPostgreSQL 16 and 17

What Is Guaranteed

  • Serial equivalence: The committed transactions appear to have executed serially, even if they ran concurrently. The order is not predetermined and need not follow real-time start or commit order; this is not strict serializability.

  • Consistent invariants: Transactions cannot collectively commit an outcome that violates a serializable interpretation, such as the classic write-skew anomaly.

  • Permitted failures: PostgreSQL may conservatively abort a transaction even when its work could have been part of a valid serial execution. It favors preserving the guarantee over maximizing commit throughput.

  • Limited scope: The guarantee concerns transactional consistency, not authorization, durability configuration, or protection against deadlocks and ordinary lock waits.

How SSI Works

  • Transaction snapshot: Under SERIALIZABLE, the transaction takes a transaction-wide snapshot when it executes its first statement that requires one. It sees the same database state throughout the transaction, along with its own changes.

  • Read tracking: Reads acquire predicate-lock metadata, internally associated with SIREAD locks. These represent logical read dependencies and are not ordinary row or gap locks, so they generally do not appear in pg_locks.

  • Read/write conflicts: SSI detects when one transaction writes data that another transaction previously read, even if the reader never sees that write because of its snapshot.

  • Dangerous-structure detection: During commit processing, PostgreSQL checks whether the dependency graph contains a dangerous structure: a cycle of read/write conflicts together with a pivot transaction having conflicts entering and leaving the cycle.

  • Serialization failure: When such a structure exists, PostgreSQL aborts one of the participating transactions. The error commonly reads could not serialize access due to read/write dependencies among transactions and uses SQLSTATE 40001. A concurrent update conflict can also cause an earlier 40001 error.

  • Durability remains unchanged: A successful transaction follows the normal WAL and commit-durability rules, including the configured synchronous_commit behavior.

Isolation Levels Compared

Level Snapshot behavior Write-skew prevention
READ COMMITTED A new snapshot for each statement No
REPEATABLE READ One snapshot for the whole transaction No; some update conflicts can still fail
SERIALIZABLE One snapshot plus SSI dependency tracking Yes, through serialization failures

PostgreSQL defaults to READ COMMITTED. If concurrent non-serializable transactions participate in the same dependencies, the overall execution is not guaranteed to be serializable; affected concurrent transactions should generally use SERIALIZABLE.

Production Behavior

  • Retries: Treat 40001 as a normal concurrency outcome. Retry the entire transaction with bounded exponential backoff and jitter, and ensure externally visible side effects are idempotent.

  • Contention: SSI does not eliminate row-lock waits or deadlocks. It also uses conservative conflict detection, so valid transactions may be aborted unnecessarily.

  • Long transactions: Old snapshots can prevent vacuum cleanup, increase table and index bloat, retain old xmin values, and delay transaction-ID wraparound protection.

  • Monitoring: Serialization failures are exposed to clients and logged by the server at error level. Track their rate, retry amplification, and transaction duration using application telemetry, database logs, and pg_stat_activity.

Operational Guidance

  • Keep transactions short and perform network calls or user interaction outside them.

  • Prefer a unique constraint, a check constraint, or targeted SELECT ... FOR UPDATE locks when they can enforce the invariant directly. Explicit locks require careful row-set analysis because they do not automatically protect predicates or nonexistent rows.

  • Use SERIALIZABLE for invariants involving multiple rows or predicates that cannot be protected reliably by a single lock.

  • Do not mix isolation levels casually in the same invariant’s concurrency domain.

  • Exercise retry handling during upgrades and incident drills; scheduling, query-plan, and workload changes can alter conflict frequency.

  • Remember that isolation is not authorization. Continue enforcing access with PostgreSQL roles and, where appropriate, row-level security.

40

Which PostgreSQL lock levels and modes exist?

PostgreSQL has distinct lock scopes and modes rather than one linear hierarchy: relation locks protect database objects, row locks protect tuples, and internal or advisory locks serve specialized purposes. Lock compatibility, rather than a simple “low-to-high” ranking, determines whether compatible requests can proceed concurrently.

Read full answer

Applies toPostgreSQL 16 and 17

Relation-Level Locks

Relation locks protect an entire relation, such as a table, index, view, materialized view, or sequence. They are managed by PostgreSQL’s shared-memory lock manager and are normally retained until the transaction ends.

Lock mode Typical use Conflict behavior
ACCESS SHARE Ordinary SELECT Conflicts only with ACCESS EXCLUSIVE
ROW SHARE SELECT FOR UPDATE, SELECT FOR SHARE Conflicts with EXCLUSIVE and ACCESS EXCLUSIVE
ROW EXCLUSIVE INSERT, UPDATE, DELETE, data-modifying MERGE Conflicts with SHARE and more restrictive modes
SHARE UPDATE EXCLUSIVE VACUUM, ANALYZE, CREATE INDEX CONCURRENTLY Conflicts with itself and several more restrictive modes
SHARE CREATE INDEX without CONCURRENTLY Conflicts with row-modifying operations and more restrictive modes
SHARE ROW EXCLUSIVE CREATE TRIGGER, some ALTER TABLE operations Conflicts with every relation mode except ACCESS SHARE and ROW SHARE
EXCLUSIVE Explicit LOCK TABLE ... IN EXCLUSIVE MODE and certain maintenance operations Conflicts with everything except ACCESS SHARE
ACCESS EXCLUSIVE TRUNCATE, DROP, VACUUM FULL, most heavy DDL Conflicts with every relation lock

A transaction requesting ACCESS EXCLUSIVE can stop reads and writes immediately. For example, an ALTER TABLE requiring ACCESS EXCLUSIVE queues behind active DML, while new reads may also queue behind the pending DDL. This prevents the DDL from being continuously bypassed by new transactions.

Row-Level Locks

Row locks are implemented as tuple locks, stored with tuple metadata rather than as separate heavyweight lock-manager entries.

SQL clause Lock strength Effect
FOR KEY SHARE Weakest row lock Blocks deletion or key-changing updates, but permits updates that leave key columns unchanged
FOR SHARE Shared-strength row lock Blocks row updates and deletion
FOR NO KEY UPDATE Strong update lock Allows KEY SHARE locks but conflicts with SHARE, NO KEY UPDATE, and UPDATE
FOR UPDATE Strongest row lock Conflicts with all other row-lock modes

An UPDATE normally takes FOR NO KEY UPDATE. It takes FOR UPDATE when it modifies key columns of a unique index eligible to support a foreign key, including a primary key, because such changes can affect referential integrity. Foreign-key checks commonly acquire FOR KEY SHARE on referenced rows to protect keys without blocking unrelated updates.

FOR SHARE and FOR KEY SHARE have different conflict rules: SHARE excludes updates and deletion, while KEY SHARE excludes deletion and key-changing updates.

Internal and Application Locks

  • Buffer content locks: Synchronize access to buffer contents while PostgreSQL reads or modifies tuples. They are usually short-lived implementation details rather than user-requested relation locks.

  • Predicate locks: Track data read by serializable transactions so PostgreSQL can detect dangerous serialization dependencies.

  • Advisory locks: Application-managed locks using either one 64-bit key or two 32-bit keys. They have exclusive and shared modes, with session-level functions such as pg_advisory_lock and pg_advisory_lock_shared and transaction-level counterparts such as pg_advisory_xact_lock and pg_advisory_xact_lock_shared. Transaction-level locks end when the transaction ends; session-level locks require explicit release or session termination.

  • Transaction-ID locks: Coordinate waits on transactions that modified particular rows and appear as transactionid entries in pg_locks.

Advisory locks appear in pg_locks but do not automatically conflict with relation, row, or other advisory locks in a different key space.

MVCC, Transactions, and Deadlocks

Ordinary MVCC reads do not lock rows against other readers. A SELECT mainly acquires a relation-level ACCESS SHARE lock, while writers create new tuple versions and protect changed rows with tuple locks.

Row locks acquired by UPDATE and DELETE are usually retained until transaction commit or rollback, even under READ COMMITTED. Long transactions can pin old snapshots and row versions, delay VACUUM cleanup, increase bloat, and create transaction-ID wraparound pressure.

Lock waits are not automatically deadlocks. When PostgreSQL detects a cycle, it aborts one transaction with a deadlock error. At REPEATABLE READ, an update involving a row changed after the transaction’s snapshot can instead produce a serialization failure. At SERIALIZABLE, predicate locking also detects dangerous read/write dependency patterns.

Production Monitoring and Mitigation

Use pg_stat_activity to find sessions whose wait_event_type is Lock. pg_blocking_pids(pid) identifies blocking sessions, while pg_locks shows granted heavyweight locks and their types, including relation, transactionid, advisory, object, and virtualxid. Ordinary tuple locks are not shown in pg_locks in PostgreSQL 16/17 because they are stored with tuple metadata rather than managed by the lock manager.

Operational safeguards include:

  • Set bounded lock_timeout and statement_timeout values for services that must fail quickly.

  • Use NOWAIT when immediate failure is preferable to waiting.

  • Use SKIP LOCKED for queue-oriented workers claiming jobs.

  • Keep transactions short and avoid network calls while transactions are open.

  • Schedule heavy DDL carefully and monitor lock queues during deployments.

  • Track pg_stat_database.deadlocks, long-running transactions, dead tuples, replication-slot lag, and vacuum progress.

  • Grant monitoring roles only required privileges, such as pg_read_all_stats, rather than superuser access.

Connection pooling reduces connection pressure but does not eliminate lock contention. Pool limits should therefore be coordinated with transaction duration and the database’s capacity to serve concurrent statements.

41

How do advisory locks work in PostgreSQL?

PostgreSQL advisory locks are application-defined coordination locks that prevent selected operations from running concurrently. They are useful for leader election, singleton processing, and preventing duplicate work, but they do not replace table locks, transaction isolation, or a durable job-coordination system.

Read full answer

Applies toPostgreSQL 16 and 17

Core Concepts

Advisory locks exist in two scopes:

Scope Acquisition Release
Session-level pg_advisory_lock() Explicitly with pg_advisory_unlock(), or when the session ends
Transaction-level pg_advisory_xact_lock() Automatically when the transaction commits or rolls back

A session-level lock survives both COMMIT and ROLLBACK. A transaction-level lock is safer for short operations because it cannot remain held after the transaction ends.

Each lock has a mode:

  • Exclusive: conflicts with exclusive and shared locks using the same key.

  • Shared: multiple sessions can hold it concurrently, but it conflicts with an exclusive lock.

  • Try functions: pg_try_advisory_lock(), pg_try_advisory_xact_lock(), pg_try_advisory_lock_shared(), and pg_try_advisory_xact_lock_shared() return false immediately when the lock is unavailable.

  • Shared acquisition: pg_advisory_lock_shared() and pg_advisory_xact_lock_shared() acquire shared locks. pg_advisory_unlock_shared() releases a session-level shared lock.

Keys have two overloads:

  • One bigint key, such as pg_advisory_lock(814723).

  • Two integer keys, such as pg_advisory_lock(42, 7).

The one-key and two-key namespaces do not overlap. Participants must use the same key namespace, key values, and compatible modes; their lock scopes should reflect the intended lifetime.

Behavior Under Contention

Advisory locks are managed by PostgreSQL’s lock manager and appear in pg_locks with locktype = 'advisory'. Unlike ordinary locks, they are not associated with a table, row, or index and do not prevent changes to those objects.

A blocking acquisition waits until the lock becomes available or the statement is canceled. statement_timeout, lock_timeout, or a try-lock function can prevent a worker from waiting indefinitely.

Session- and transaction-level requests for the same key still conflict with each other; the difference is how long ownership lasts. PostgreSQL also detects deadlocks involving advisory locks and aborts one participant with a deadlock error.

Monitoring and Failure Modes

Use pg_locks to inspect held and waiting advisory locks:

SELECT pid, mode, granted
FROM pg_locks
WHERE locktype = 'advisory';

Use pg_stat_activity to find sessions waiting for advisory locks and identify blockers:

SELECT
    pid,
    wait_event_type,
    wait_event,
    pg_blocking_pids(pid) AS blocking_pids,
    left(query, 120) AS query
FROM pg_stat_activity
WHERE wait_event = 'advisory';

Important operational consequences include:

  • Connection loss: Ending the database session releases session-level locks. A session-level lock acquired inside a transaction can also remain after that transaction rolls back.

  • Connection pooling: Session-level locks require session pooling or a pinned backend. With PgBouncer transaction pooling, transaction-level locks are generally safer.

  • Failover: Advisory-lock state is not replicated. Locks held on the old primary are not transferred to a promoted standby, so applications must re-establish coordination on the new primary.

  • No lease semantics: A lock has no automatic expiry timestamp. It remains held until explicitly released or its owning session ends.

  • Monitoring: Track advisory-lock waits, contention, retry volume, and backend age alongside normal database wait metrics.

Code Example

The following PostgreSQL 16 SQL acquires and releases a session-level exclusive lock, then demonstrates a transaction-level try-lock:

-- Run in a fresh session when the key is not held by another session.
SELECT pg_advisory_lock(42, 7);
SELECT pg_advisory_unlock(42, 7);

-- A transaction-level lock is released automatically at COMMIT or ROLLBACK.
BEGIN;
SELECT pg_try_advisory_xact_lock(42, 8) AS acquired;
COMMIT;

Expected results are:

Operation Result
SELECT pg_advisory_lock(42, 7) One row containing a void value, normally displayed as blank
SELECT pg_advisory_unlock(42, 7) t because the current session held the lock
SELECT pg_try_advisory_xact_lock(42, 8) t if no conflicting lock exists; otherwise f

In another session, attempting pg_advisory_lock(42, 7) while it is held will block. The corresponding try-lock function returns false instead.

Production Practices

  • Prefer transaction-level locks for short, atomic operations.

  • Use try-lock functions for schedulers and workers that must remain responsive.

  • Adopt a namespaced key scheme, such as application ID, resource type, and resource ID.

  • Acquire multiple advisory locks in a consistent order to reduce deadlocks.

  • Do not treat advisory locks as authorization, protection against ordinary table writes, or a guarantee of exactly-once external side effects.

  • Remember that lock keys are visible through PostgreSQL catalogs; they are identifiers, not secrets.

  • For durable job scheduling, use a transactional job queue and use techniques such as SELECT ... FOR UPDATE SKIP LOCKED to claim queued rows.

42

How do you find and resolve blocking queries in PostgreSQL?

PostgreSQL reports lock blocking through pg_stat_activity and pg_blocking_pids(), which identify backends waiting for locks held by other transactions. The safest resolution is to make the blocker commit or roll back; cancel or terminate its session only when it is safe to interrupt.

Read full answer

Applies toPostgreSQL 16/17

How Lock Blocking Works

PostgreSQL uses MVCC, but concurrent writes can still conflict. If session A updates a row without committing, session B’s update waits to determine whether A’s change will commit or roll back.

  • Row conflict: Usually appears as wait_event_type = 'Lock' and wait_event = 'transactionid'.

  • Relation conflict: A SELECT can wait behind ACCESS EXCLUSIVE locks held by DDL such as ALTER TABLE.

  • Virtual transaction conflict: Commonly reported as virtualxid, often during concurrent DDL.

  • pg_locks shows lock state, but a row-level wait is commonly represented by an ungranted transaction-ID lock rather than a simple tuple lock.

  • A blocker can itself be waiting. Continue joining pg_blocking_pids() until the root blocker is found.

Identify the Actual Blocker

pg_stat_activity provides the important operational context:

  • state: An idle in transaction session can continue holding locks indefinitely.

  • xact_start: Shows how long the transaction has existed, not merely how long its last statement ran.

  • query: For an idle session, this is its most recent query and may not be executing.

  • wait_event: Indicates the lock class being awaited.

  • pid: Identifies the backend for cancellation or termination.

Also check application logs and deployment activity. A blocker may be a migration, batch job, manual session, or abandoned transaction rather than an ordinary API request.

Resolve in the Safest Order

  1. Coordinate with the owning team or workload.

  2. Commit or roll back the blocker if its work is still valid.

  3. Cancel the active statement if the blocker is stuck in a long query. Then roll back the resulting aborted transaction or end the session; cancellation alone does not end the transaction or necessarily release all previously acquired locks.

  4. Terminate the session if it is abandoned or stuck idle in transaction.

  5. Use timeouts to bound future waits.

Terminating a backend rolls back its open transaction. This can discard substantial work, trigger application retries, or cause a failover, so it should not be an automatic response to every blocked query.

Members of the predefined pg_signal_backend role can signal non-superuser backends; a superuser is required to signal a superuser-owned backend. Grant this privilege narrowly rather than making monitoring systems broadly privileged.

Production Monitoring and Failure Modes

Signal Interpretation Operational response
transactionid wait Row or transaction conflict Find the oldest blocker and inspect its transaction
relation wait DDL or heavyweight table lock Coordinate migration and application traffic
virtualxid wait DDL conflicts with other transactions Retry DDL with bounded lock acquisition
Long idle in transaction Session retained locks after an error Roll back and close the session
Rising pg_stat_database.deadlocks PostgreSQL detected and aborted deadlocks Review transaction ordering and indexes

pg_stat_activity and pg_locks provide current snapshots, not history. Enable log_lock_waits and monitor lock-wait duration, blocker age, deadlocks, and long transactions. Long transactions also prevent efficient VACUUM cleanup, increasing bloat and keeping transaction ID cleanup pressure high.

Code Example

Create a small table:

DROP TABLE IF EXISTS accounts;

CREATE TABLE accounts (
    id integer PRIMARY KEY,
    balance integer NOT NULL
);

INSERT INTO accounts (id, balance)
VALUES (1, 1000), (2, 500);

Initial result:

id balance
1 1000
2 500

Run this in session A and leave the transaction open:

BEGIN;
UPDATE accounts
SET balance = balance - 100
WHERE id = 1;
-- Do not commit yet.

Run this in session B; it blocks behind session A:

BEGIN;
UPDATE accounts
SET balance = balance + 50
WHERE id = 1;

While session B is waiting, run this from a third session:

SELECT
    b.pid AS blocked_pid,
    p.blocker_pid,
    b.state AS blocked_state,
    a.state AS blocker_state,
    b.wait_event_type,
    b.wait_event,
    clock_timestamp() - b.xact_start AS blocked_xact_age,
    clock_timestamp() - a.xact_start AS blocker_xact_age,
    left(b.query, 80) AS blocked_query,
    left(a.query, 80) AS blocker_query
FROM pg_stat_activity AS b
JOIN LATERAL
    unnest(pg_blocking_pids(b.pid)) AS p(blocker_pid) ON true
JOIN pg_stat_activity AS a
    ON a.pid = p.blocker_pid
ORDER BY a.xact_start NULLS LAST, b.xact_start NULLS LAST;

An abbreviated result is:

blocked_pid blocker_pid blocked_state blocker_state wait_event blocked_query blocker_query
4242 4199 active idle in transaction transactionid UPDATE accounts ... UPDATE accounts ...

Resolve from session A, when appropriate:

COMMIT;
-- Alternatively:
-- ROLLBACK;

Session B then completes its update; commit or roll back session B as required.

An authorized administrator can instead attempt to cancel a blocker that is actively executing a statement:

SELECT pg_cancel_backend(4199);

Cancellation leaves the transaction open, so follow it with a rollback in that session. For the example blocker, which is idle in transaction, terminate the session instead:

SELECT pg_terminate_backend(4199);

Best Practices and Safeguards

  • Set a finite lock_timeout for transactions that may encounter contention, such as SET LOCAL lock_timeout = '5s'.

  • Always commit or roll back in application cleanup paths, including error handlers.

  • Keep transactions short and avoid network calls while database transactions are open.

  • Access rows in a consistent order to reduce deadlocks.

  • Ensure queries use suitable indexes so updates touch as few rows and pages as possible.

  • Retry deadlock or timeout failures only when operations are idempotent, using bounded exponential backoff with jitter.

  • Treat automatic backend termination as a controlled incident action, not routine troubleshooting.

43

What is PostgreSQL's deadlock detection and resolution process?

PostgreSQL detects a deadlock by examining wait-for relationships between blocked backends for a cycle. When it finds a cycle, it aborts one transaction with SQLSTATE 40P01, releases that transaction’s locks, and allows the other transactions to continue.

Read full answer

Applies toPostgreSQL 16 and 17

How Detection Works

  1. A backend requests a lock managed by PostgreSQL’s lock manager. Heavyweight locks include relation, tuple, transaction-ID, object, and advisory/user locks; row-level locking such as FOR UPDATE is represented by tuple locks.

  2. If another transaction holds a conflicting lock, the requesting backend enters the lock’s wait queue. It waits for the conflicting lock to be released, not necessarily for the holding transaction to finish.

  3. The deadlock_timeout setting controls how long a backend waits before performing a deadlock check. Its default is one second, and the timeout is measured from when the lock request is queued.

  4. PostgreSQL traverses the wait-for relationships between participating backends. A cycle means the involved transactions are waiting for one another and cannot make progress.

  5. A waiting backend can detect the cycle after its deadlock timeout expires. Actual detection can take longer because of scheduling, detector overhead, and contention.

deadlock_timeout is therefore not a maximum lock-wait duration. Reducing it may expose conflicts sooner but increases deadlock-checking work in heavily contended systems; increasing it reduces that overhead but prolongs blocked transactions.

Victim Selection and Resolution

  • PostgreSQL selects one participant as the victim using an internal cost heuristic intended to avoid aborting a transaction that has done substantial work. The choice is not guaranteed to be the newest transaction or the same participant in every deadlock.

  • The server sends the victim ERROR: deadlock detected with SQLSTATE 40P01 and aborts its transaction.

  • Aborting the victim releases its locks, allowing the other participants to continue, although another deadlock can be discovered as the remaining transactions make progress.

  • In an explicit transaction block, the session remains in an aborted transaction state until it issues ROLLBACK; application frameworks and ORMs commonly perform this rollback automatically.

  • A deadlock is a normal concurrency failure, not data corruption. PostgreSQL preserves transactional atomicity, and the application must decide whether retrying the entire transaction is safe.

Timeouts and Isolation

  • lock_timeout can cancel the waiting statement before PostgreSQL detects a deadlock.

  • statement_timeout can also expire first, producing a timeout error rather than 40P01.

  • Isolation levels do not prevent lock deadlocks. SERIALIZABLE transactions can additionally encounter serialization failures, which are distinct from lock-wait deadlocks.

  • Blocking statements continue holding their locks until commit, rollback, statement cancellation, or session termination. Long-running transactions therefore increase both deadlock exposure and contention elsewhere.

Monitoring and Diagnosis

  • pg_stat_activity exposes currently active sessions. pg_blocking_pids(pid) identifies the sessions blocking a particular backend.

  • For lock waits, wait_event_type is Lock, while wait_event provides more detail such as transactionid, tuple, relation, or advisory.

  • pg_stat_database.deadlocks is a cumulative per-database counter. Production monitoring should alert on the rate of new deadlocks rather than treating the lifetime total as the current incident severity.

  • pg_stat_database.conflicts is unrelated to lock_timeout; it counts queries canceled because of recovery conflicts on standby servers.

  • EXPLAIN explains query plans and execution behavior but does not construct or display PostgreSQL’s wait-for graph.

  • The server log records deadlock detected messages and details about the processes and locks involved. Enabling log_lock_waits can reveal waits lasting longer than deadlock_timeout, although it can increase log volume.

  • Grant pg_monitor or equivalent statistics privileges only where broad visibility, including query text, is required. Query text can contain sensitive application data.

Prevention and Recovery

  • Acquire related locks in a consistent order across every application path.

  • Keep transactions short, avoid holding locks while calling external services, and minimize the number of rows and tables touched.

  • Use queue-style processing with FOR UPDATE SKIP LOCKED when skipped work is acceptable; it reduces contention but changes processing semantics.

  • Classify SQLSTATE 40P01 as retryable only when the transaction is idempotent or protected by an appropriate deduplication mechanism. Retry the entire transaction after rollback using bounded exponential backoff and jitter to avoid retry storms.

  • Monitor blocked-session counts, deadlock rates, lock-wait duration, transaction age, and repeated application retries.

  • Backups and PITR are disaster-recovery mechanisms; they do not resolve live lock deadlocks or substitute for concurrency control.

44

How do SELECT FOR UPDATE and SKIP LOCKED work in PostgreSQL?

SELECT FOR UPDATE is PostgreSQL’s row-level pessimistic locking clause: it locks selected rows so conflicting updates or locks wait until the current transaction finishes. SKIP LOCKED changes that waiting behavior by skipping rows whose locks cannot be acquired immediately, allowing multiple workers to process different rows concurrently.

Read full answer

Applies toPostgreSQL 16 and 17

How Row Locking Works

  • FOR UPDATE locks the rows returned by the query as if the transaction were updating them.

  • Conflicting UPDATE or DELETE operations, as well as other FOR UPDATE queries, must wait for the lock.

  • Ordinary SELECT queries continue reading the row under PostgreSQL’s MVCC model; they do not wait for the row lock.

  • Locks are normally released only at COMMIT or ROLLBACK. In autocommit mode, the implicit transaction ends after the statement, which usually makes row locking ineffective for multi-step work.

  • FOR UPDATE OF table_name restricts locking to specific tables when a query joins multiple tables.

  • NOWAIT can be combined with FOR UPDATE to raise an error immediately instead of waiting.

How SKIP LOCKED Changes Behavior

With plain FOR UPDATE, a worker encountering a locked row waits. With FOR UPDATE SKIP LOCKED, it skips that row and evaluates the next candidate.

This is useful for work queues:

  • Each worker claims a different available row.

  • A locked job does not block another worker.

  • Skipping does not consume or modify a row; it may remain eligible later if it still matches the query and the locking transaction does not complete it.

  • If every matching row is locked, the query returns no rows.

  • SKIP LOCKED handles row locks; it does not generally bypass conflicting table-level locks.

At READ COMMITTED, each statement receives a new snapshot. At REPEATABLE READ or SERIALIZABLE, concurrent changes can still produce serialization failures even when SKIP LOCKED is used.

Operational and Failure Modes

  • Deadlocks: Transactions locking multiple rows in different orders can deadlock. Use a consistent row order.

  • Lock contention: Long-running transactions can block normal FOR UPDATE workers or cause SKIP LOCKED workers to leave rows unprocessed temporarily.

  • Stale work: A skipped row might have been updated or completed by another transaction. Workers must verify the row’s state before processing it.

  • Duplicate processing: Row locking does not provide exactly-once semantics across crashes or retries. Use appropriate state transitions, idempotent processing, and unique constraints.

  • Bloat: Frequent updates create dead row versions. Monitor n_dead_tup and ensure autovacuum keeps up, especially if long-running transactions retain old snapshots.

  • Phantom rows: FOR UPDATE does not prevent new rows matching a predicate from being inserted. Additional coordination is needed where that matters.

Monitoring Locks and Deadlocks

Use pg_stat_activity to identify sessions waiting on locks:

-- Sessions currently waiting for a lock.
SELECT pid,
       wait_event_type,
       wait_event,
       clock_timestamp() - xact_start AS transaction_age,
       left(query, 120) AS query
FROM pg_stat_activity
WHERE wait_event_type = 'Lock'
ORDER BY transaction_age DESC;

Additional operational indicators include:

  • pg_locks: transaction, relation, and other lock information.

  • pg_stat_database.deadlocks: cumulative deadlock count for the database.

  • pg_stat_user_tables.n_dead_tup: approximate number of dead row versions.

  • pg_stat_user_tables.last_autovacuum: timing of the last autovacuum run.

Code Example

The following creates a small queue in a fresh demonstration database:

CREATE TABLE demo_jobs (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    payload text NOT NULL,
    status text NOT NULL DEFAULT 'ready'
);

-- Support queue filtering and deterministic ordering.
CREATE INDEX demo_jobs_status_id_idx ON demo_jobs (status, id);

INSERT INTO demo_jobs (payload)
VALUES ('email'), ('billing');

SELECT * FROM demo_jobs ORDER BY id;

Expected result:

id | payload | status
----+---------+--------
 1 | email   | ready
 2 | billing | ready

A worker can atomically transition one unlocked job from ready to done:

BEGIN;

WITH next_job AS (
    -- Lock one ready row without waiting for rows claimed by other workers.
    SELECT id
    FROM demo_jobs
    WHERE status = 'ready'
    ORDER BY id
    LIMIT 1
    FOR UPDATE SKIP LOCKED
)
-- Complete the state transition in the same transaction.
UPDATE demo_jobs
SET status = 'done'
WHERE id = (SELECT id FROM next_job)
RETURNING demo_jobs.id, demo_jobs.payload, demo_jobs.status;

COMMIT;

Expected result without concurrent workers:

id | payload | status
----+---------+-------
 1 | email  | done

The locking behavior is visible in the plan as a LockRows node:

-- Refresh planner statistics before inspecting the execution plan.
ANALYZE demo_jobs;

EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF)
SELECT id
FROM demo_jobs
WHERE status = 'ready'
ORDER BY id
LIMIT 1
FOR UPDATE SKIP LOCKED;

Shortened output:

Limit
  -> LockRows
    -> Index Scan using demo_jobs_status_id_idx on demo_jobs
         Index Cond: (status = 'ready'::text)

Best Practices

  • Use an explicit transaction and keep it short.

  • Claim one or a small batch of rows, complete the work, and commit promptly.

  • Set a bounded wait for other unavoidable locks with SET LOCAL lock_timeout = '2s'; inside the transaction.

  • Index queue filters and ordering columns, commonly with a composite index such as (status, id).

  • Order locked rows consistently to minimize deadlocks.

  • Treat SKIP LOCKED as work distribution, not as proof that a job was processed exactly once.

  • Alert on lock waits, deadlocks, transaction age, replication lag, table bloat, and autovacuum failures.

45

How do you build a PostgreSQL-backed job queue?

A PostgreSQL-backed job queue stores work in a table and uses FOR UPDATE SKIP LOCKED so multiple workers can claim different jobs concurrently. Leases, retries, idempotency, and monitoring provide at-least-once handling with possible duplicate execution; bounded retries can ultimately move a job to failed.

Read full answer

Applies toPostgreSQL 16 and 17

Design and Execution

  • Schema: Store the payload as jsonb, timestamps as timestamptz, and use explicit pending, processing, done, and failed states.

  • Leases and retries: Track locked_by, locked_until, attempts, max_attempts, and run_at. A separate maintenance process returns expired leases to pending.

  • Indexes: Use a partial B-tree index for pending jobs and another for expired processing jobs. Index only rows matching their state to reduce index churn.

  • Claiming: FOR UPDATE locks selected rows. Without SKIP LOCKED, another worker waits unless lock_timeout or a statement timeout is configured; SKIP LOCKED bypasses rows already locked by another transaction.

  • Delivery: Commit the claim before performing the external side effect. A crash afterward can cause redelivery, so handlers must be idempotent using a deduplication key or idempotency table.

  • Storage engine: A backend reads shared-buffer and disk pages, locks the selected tuple, creates an MVCC row version, and WAL-logs the update. The committed claim can then be recovered after a worker failure.

Production Model

Use the primary for claims; a physical standby is read-only, and replication lag makes replicas unsuitable for ownership decisions. Partition only when its lifecycle justifies the complexity—for example, carefully managed created_at partitions for archival—and never drop partitions containing pending work. Core queue features need no extension, although optional tools such as pg_cron can schedule maintenance. PostgreSQL is appropriate for moderate volumes and transactional enqueueing; use a dedicated broker for extreme throughput, routing, or long-term retention.

Code Example

Run the statements in one session because PREPARE is session-scoped.

CREATE TABLE job_queue (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    payload jsonb NOT NULL,
    status text NOT NULL DEFAULT 'pending'
        CHECK (status IN ('pending', 'processing', 'done', 'failed')),
    attempts integer NOT NULL DEFAULT 0 CHECK (attempts >= 0),
    max_attempts integer NOT NULL DEFAULT 5 CHECK (max_attempts > 0),
    run_at timestamptz NOT NULL DEFAULT clock_timestamp(),
    locked_at timestamptz,
    locked_until timestamptz,
    locked_by text,
    last_error text,
    created_at timestamptz NOT NULL DEFAULT clock_timestamp()
);

CREATE INDEX job_queue_pending_idx
    ON job_queue (run_at, id)
    WHERE status = 'pending';

CREATE INDEX job_queue_processing_idx
    ON job_queue (locked_until)
    WHERE status = 'processing';

INSERT INTO job_queue (payload)
VALUES ('{"kind":"email","to":"user@example.com"}');

PREPARE claim_job(text) AS
WITH candidate AS (
    SELECT id
    FROM job_queue
    WHERE status = 'pending'
      AND attempts < max_attempts
      AND run_at <= clock_timestamp()
    ORDER BY run_at, id
    LIMIT 1
    FOR UPDATE SKIP LOCKED
)
UPDATE job_queue AS q
SET status = 'processing',
    attempts = q.attempts + 1,
    locked_at = clock_timestamp(),
    locked_until = clock_timestamp() + interval '60 seconds',
    locked_by = $1::text
FROM candidate AS c
WHERE q.id = c.id
RETURNING q.id, q.payload, q.attempts, q.locked_by;

BEGIN;
EXECUTE claim_job('worker-1');
COMMIT;

The claim returns one row similar to:

id | payload                                  | attempts | locked_by
1  | {"kind": "email", "to": "user@example.com"} | 1        | worker-1

Renew, complete, and fail jobs with owner checks:

PREPARE heartbeat_job(bigint, text) AS
UPDATE job_queue
SET locked_until = clock_timestamp() + interval '60 seconds'
WHERE id = $1::bigint
  AND locked_by = $2::text
  AND status = 'processing'
  AND locked_until > clock_timestamp();

EXECUTE heartbeat_job(1, 'worker-1');

PREPARE finish_job(bigint, text) AS
UPDATE job_queue
SET status = 'done',
    locked_at = NULL,
    locked_until = NULL,
    locked_by = NULL
WHERE id = $1::bigint
  AND locked_by = $2::text
  AND status = 'processing';

EXECUTE finish_job(1, 'worker-1');

PREPARE fail_job(bigint, text, text) AS
UPDATE job_queue
SET status = CASE WHEN attempts >= max_attempts THEN 'failed' ELSE 'pending' END,
    run_at = CASE WHEN attempts >= max_attempts THEN run_at
                  ELSE clock_timestamp() + interval '30 seconds' END,
    locked_at = NULL,
    locked_until = NULL,
    locked_by = NULL,
    last_error = $3::text
WHERE id = $1::bigint
  AND locked_by = $2::text
  AND status = 'processing';

EXECUTE fail_job(1, 'worker-1', 'temporary upstream failure');

Recover expired leases in bounded batches. Exhausted attempts become terminally failed rather than being executed again:

WITH expired AS (
    SELECT id
    FROM job_queue
    WHERE status = 'processing'
      AND locked_until < clock_timestamp()
    ORDER BY locked_until
    LIMIT 1000
    FOR UPDATE SKIP LOCKED
)
UPDATE job_queue AS q
SET status = CASE WHEN q.attempts >= q.max_attempts THEN 'failed' ELSE 'pending' END,
    run_at = CASE WHEN q.attempts >= q.max_attempts THEN q.run_at
                  ELSE clock_timestamp() END,
    locked_at = NULL,
    locked_until = NULL,
    locked_by = NULL,
    last_error = 'worker lease expired'
FROM expired AS e
WHERE q.id = e.id;

Inspect the actual claim plan:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id
FROM job_queue
WHERE status = 'pending'
  AND attempts < max_attempts
  AND run_at <= clock_timestamp()
ORDER BY run_at, id
LIMIT 1
FOR UPDATE SKIP LOCKED;

A sufficiently large queue may produce:

Limit
  -> LockRows
       -> Index Scan using job_queue_pending_idx on job_queue

A tiny table may correctly use a sequential scan.

Operations and Best Practices

Monitor queue depth, oldest pending work, worker waits, dead tuples, and transaction-ID age:

SELECT count(*) FILTER (WHERE status = 'pending') AS pending,
       min(created_at) FILTER (WHERE status = 'pending') AS oldest_pending
FROM job_queue;

SELECT n_live_tup, n_dead_tup, last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
WHERE relname = 'job_queue';

SELECT age(datfrozenxid) AS database_xid_age
FROM pg_database
WHERE datname = current_database();

Use pg_stat_activity and pg_locks for lock contention. Status changes create dead tuples and partial-index churn; batch archival and deletion, and let VACUUM reuse space. VACUUM FULL returns disk space but requires a strong lock.

Index Queue use
B-tree Pending selection and lease recovery
GIN Optional JSONB containment or search
BRIN Large append-only history ordered by time
GiST Usually unnecessary for queue selection

Session-level PREPARE requires one backend. With transaction-pooled PgBouncer, use driver-level unnamed or explicitly supported protocol-level prepared statements. Enforce least-privilege roles, TLS, SCRAM authentication, restricted pg_hba.conf, parameterized SQL, and tested backups with PITR.

46

Are PostgreSQL CTEs optimization fences?

Common table expressions (CTEs) are not inherently optimization fences in PostgreSQL. Whether a CTE forms a planner boundary depends primarily on whether PostgreSQL materializes it: an inlined CTE becomes part of the parent query, while a materialized CTE generally prevents transformations such as predicate pushdown and join reordering across its boundary.

Read full answer

Applies toPostgreSQL 16 and 17

CTE Fundamentals

A CTE is defined with WITH and can be referenced by the main query or by later CTEs in the same statement:

  • Lifetime: It exists only for that statement and is not a persistent database object like a table or view.

  • MVCC behavior: It uses the statement’s snapshot, so all parts of the statement see the same committed data.

  • Recursive queries: WITH RECURSIVE supports hierarchical and graph-style traversal.

  • Data-modifying CTEs: INSERT, UPDATE, and DELETE can appear inside WITH. When the statement succeeds, each data-modifying CTE executes exactly once and independently of whether the main query reads all its output.

  • Security: Using a CTE does not bypass privileges on the referenced tables, views, or functions.

Materialization and Inlining

PostgreSQL 12 introduced explicit control over CTE materialization, and PostgreSQL 16 and 17 retain that behavior.

CTE form PostgreSQL 16/17 behavior Typical use
Default, referenced once Inlined when non-recursive, side-effect-free, and otherwise eligible Avoid an unnecessary materialization boundary
Default, referenced multiple times Normally materialized and evaluated once Reuse an expensive result
AS MATERIALIZED Always materialized Force a boundary or deliberate reuse
AS NOT MATERIALIZED Folded into the parent query when side-effect-free and non-recursive; otherwise materialized Request inlining explicitly
Recursive CTE Materialized, with a recursive working table Support recursive evaluation

A volatile function, such as random() or nextval(), can make a CTE ineligible for safe inlining. Consequently, NOT MATERIALIZED is not a guarantee that every query shape will be inlined.

When a CTE Acts as an Optimization Fence

A materialized CTE is planned separately and normally appears as a CTE Scan in the execution plan. The outer query generally cannot:

  • Push outer predicates into the CTE.

  • Reorder or replace joins across the CTE boundary.

  • Use an outer predicate to select a more selective index access on the CTE’s source tables.

  • Use an index on the CTE output, because the materialized tuplestore has no independently indexed storage.

The planner can still optimize operations above the boundary and use the CTE’s estimated row count. Materialization is therefore a fence for many cross-boundary transformations, not a barrier to every optimization.

By contrast, an inlined CTE disappears as a separate plan boundary. Its filters and joins become part of the parent query, allowing normal predicate pushdown, join selection, and index access.

Production and Operational Effects

Materialization occurs inside the PostgreSQL backend using a tuplestore:

  • Small results remain in memory, while large results can spill to temporary files after exceeding available work_mem.

  • Multiple references to one materialized CTE avoid recomputing the result.

  • Recursive CTEs can run indefinitely if their termination logic is incorrect.

  • Long-running statements retain snapshots that can prevent vacuum from removing old row versions, contributing to bloat and increasing transaction ID wraparound pressure.

  • Data-modifying CTEs acquire row locks and can participate in deadlocks.

Inspect execution plans for CTE Scan nodes and temporary-file activity with EXPLAIN (ANALYZE, BUFFERS). EXPLAIN ANALYZE executes the statement, so it must not be run casually against production data-modifying statements.

Best Practices and Monitoring

  • Do not use MATERIALIZED as a generic performance hint; compare actual plans first.

  • Prefer an ordinary query or an inlined CTE when the planner can optimize the entire statement together.

  • Materialize deliberately when the same expensive result is reused or when a logical boundary is intentional.

  • Monitor long-running statements through pg_stat_activity, including state, wait_event_type, wait_event, and query.

  • Use pg_stat_statements to identify expensive CTE statements and track planning versus execution time.

  • Monitor cumulative temporary-file usage through pg_stat_database.temp_bytes and pg_stat_database.temp_files.

  • Use EXPLAIN without ANALYZE for production statements that might modify data. Run EXPLAIN ANALYZE only for read-only statements or in a safe non-production environment; rolling back does not undo sequence increments or external side effects.

47

What are recursive CTEs in PostgreSQL?

A recursive common table expression (recursive CTE) is a WITH RECURSIVE query that repeatedly evaluates a recursive term, starting from an anchor query, until it produces no additional rows. It is designed for traversing hierarchies, dependency graphs, routes, and other relationships whose depth is not known in advance.

Read full answer

Applies toPostgreSQL 16 and 17

How It Works

The basic form has two parts:

  • Anchor term: A non-recursive query that produces the starting rows.

  • Recursive term: A query that references the CTE itself and expands the result one iteration at a time.

PostgreSQL evaluates the recursive term using the current working table, adds newly produced rows, and repeats until no new rows are produced. The recursive reference must appear exactly once in the recursive term’s FROM clause in PostgreSQL 16 and 17. It cannot appear inside a subquery or on the nullable side of an outer join.

The choice of set operation affects both performance and cycle behavior:

Operation Behaviour
UNION ALL Retains duplicate rows and avoids duplicate-elimination work. It is faster, but a cycle can continue indefinitely.
UNION Suppresses duplicate rows across the accumulated result. It stops only if a repeated row is identical; changing columns such as depth can prevent that from happening.

Capabilities and Restrictions

Recursive CTEs are well suited to organization charts, bill-of-materials structures, dependency resolution, graph traversal, and route discovery where the traversal follows parent-child or adjacency relationships.

In PostgreSQL 16 and 17, the recursive term cannot contain:

  • Aggregate functions such as COUNT() or SUM()

  • GROUP BY or HAVING

  • Window functions

  • DISTINCT

  • More than one reference to the recursive CTE

  • A recursive reference inside a subquery or on the nullable side of an outer join

These are implementation and SQL-standard restrictions that simplify recursive evaluation and state tracking.

Cycles and Termination

A malformed hierarchy can produce an unbounded traversal. Reliable safeguards include:

  • Tracking visited nodes with a path or visited-node collection

  • Using PostgreSQL’s CYCLE clause when the recursive state is designed so repeated cycle rows can be detected

  • Applying an explicit depth or breadth limit

  • Enforcing acyclicity with suitable constraints, triggers, or application validation

A self-referencing foreign key only ensures that a parent exists; by itself, it does not prevent self-references or cycles involving multiple rows.

Production Behaviour and Monitoring

  • Plan and resources: PostgreSQL normally represents the recursive operation with a Recursive Union plan node and an internal working table. Large intermediate results consume memory and can spill to temporary files.

  • Indexes: The recursive join should have supporting indexes on its traversal and join columns. PostgreSQL does not automatically index the referencing side of a foreign key.

  • Estimates: Recursive result sizes depend on later iterations, so estimates can be less precise than for ordinary queries.

  • Execution plans: EXPLAIN (ANALYZE, BUFFERS) shows actual rows, loops, buffer usage, and temporary I/O. Because ANALYZE executes the statement, it must be used cautiously on expensive or large traversals.

  • Locks and vacuum: A long-running recursive SELECT holds an MVCC snapshot and ordinary AccessShareLock locks. These can retain dead row versions, delay vacuum cleanup, and block schema changes requiring stronger locks.

  • Monitoring: Use pg_stat_activity to inspect query_start, elapsed time, wait events, and backend_xmin. The pg_stat_statements extension can identify statements with high total execution time.

Practical Guidance

  • Prefer UNION ALL when duplicate traversal paths cannot occur; use UNION only when duplicate suppression is required and its memory cost is acceptable.

  • Keep recursive state small because every column increases the work performed for generated rows.

  • Index columns used to join each iteration to the previous one.

  • Detect cycles explicitly rather than relying on UNION or referential integrity alone.

  • Test with realistic hierarchy depth and breadth, and monitor temporary-file usage, lock waits, and vacuum pressure.

  • Apply a suitable statement_timeout to unexpectedly expensive traversals.

48

How do PostgreSQL window functions work with an example?

Window functions in PostgreSQL calculate results across a set of rows related to the current row without collapsing those rows into one group. They are useful for rankings, running totals, moving averages, comparisons with neighboring rows, and other row-to-row calculations.

Read full answer

Applies toPostgreSQL 16/17

How Window Functions Work

A window function uses the OVER clause to define its calculation context. The clause can contain PARTITION BY, ORDER BY, and a frame clause.

  • PARTITION BY: Divides rows into independent groups. For example, PARTITION BY region calculates rankings separately for each region.

  • ORDER BY: Defines the row order within each partition.

  • Frame clause: Specifies which rows participate in an aggregate window calculation.

  • Window name: A named window can be reused by multiple functions, reducing repetition.

Unlike GROUP BY, a window function does not remove individual rows from the result. A query can therefore return each source row together with a regional rank or running total.

PostgreSQL evaluates window functions after FROM, WHERE, GROUP BY, and HAVING, but before the final ORDER BY and LIMIT. The executor typically sorts rows by the partition and ordering expressions and then evaluates the window calculations.

Common Window Functions

Function Purpose
ROW_NUMBER() Assigns a unique sequential number, including different numbers to tied rows
RANK() Gives tied rows the same rank and leaves gaps afterward
DENSE_RANK() Gives tied rows the same rank without gaps
LAG() / LEAD() Retrieves values from a previous or following row
SUM() / AVG() Calculates aggregate values over a window
FIRST_VALUE() / LAST_VALUE() Retrieves the first or last value in a frame

The default frame for an aggregate ordered window is effectively RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. For deterministic running totals, explicitly using ROWS is usually clearer because it counts physical rows rather than peer rows.

Code Example

The following creates a small table, inserts four rows, and calculates both a regional rank and a running total:

DROP TABLE IF EXISTS sales;

CREATE TABLE sales (
    order_id integer PRIMARY KEY,
    order_date date NOT NULL,
    region text NOT NULL,
    amount numeric(10,2) NOT NULL
);

INSERT INTO sales (order_id, order_date, region, amount) VALUES
    (1, DATE '2026-01-01', 'east', 100.00),
    (2, DATE '2026-01-02', 'east', 150.00),
    (3, DATE '2026-01-01', 'west', 200.00),
    (4, DATE '2026-01-02', 'west', 250.00);

SELECT
    order_id,
    region,
    amount,
    RANK() OVER (
        PARTITION BY region
        ORDER BY amount DESC
    ) AS region_rank,
    SUM(amount) OVER (
        PARTITION BY region
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM sales
ORDER BY region, region_rank, order_id;

Result:

order_id region amount region_rank running_total
1 east 100.00 1 100.00
2 east 150.00 2 250.00
3 west 200.00 1 200.00
4 west 250.00 2 450.00

RANK() restarts for every region. The SUM() uses an explicit ROWS frame, so each row contributes exactly once to the running total.

Inspect the execution plan on production-shaped data with:

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, SUMMARY OFF)
SELECT
    order_id,
    region,
    amount,
    RANK() OVER (
        PARTITION BY region
        ORDER BY amount DESC
    ) AS region_rank,
    SUM(amount) OVER (
        PARTITION BY region
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM sales
ORDER BY region, region_rank, order_id;

A shortened representative plan is:

Sort
  -> WindowAgg
    -> Sort
      -> WindowAgg
        -> Sort
          -> Seq Scan on sales

The exact scan method, buffers, timings, and sort methods depend on table size, statistics, indexes, and PostgreSQL settings. EXPLAIN ANALYZE executes the statement, so test expensive queries against a representative environment before using it in production.

Production and Operational Considerations

  • Large partitions can be expensive: The executor may sort a large dataset before evaluating the window. Inspect the actual plan with EXPLAIN (ANALYZE, BUFFERS) and monitor temporary-file creation with log_temp_files.

  • work_mem is a per-operation limit: Increasing it may prevent a window sort from spilling to disk, but concurrent sorts can consume substantial memory. Change it cautiously for the affected role or session.

  • Long-running queries affect vacuuming: A window query maintains a consistent MVCC snapshot. If its transaction remains open for a long time, obsolete row versions may remain visible and contribute to table bloat.

  • Indexes are not an automatic solution: An index may help with compatible ordering, but PostgreSQL may still perform an additional sort. Verify the plan rather than assuming an index eliminates the cost.

  • Make ordering deterministic: Add a unique tie-breaker such as order_id when the business requires stable row numbering. Without it, tied rows can have nondeterministic ordering.

  • Filtering window results requires an outer query: PostgreSQL has no QUALIFY clause. Wrap the query and filter on ROW_NUMBER() in the outer SELECT.

  • Security remains unchanged: Window functions do not bypass PostgreSQL privileges or row-level security, but they still operate only on rows visible to the executing role.

49

How does PostgreSQL UPSERT work with INSERT ON CONFLICT?

UPSERT is a single atomic statement that inserts a row or updates an existing row when PostgreSQL detects a conflict with the selected unique index or exclusion constraint. PostgreSQL implements it with INSERT ... ON CONFLICT, avoiding the race conditions caused by separately checking for existence and then inserting or updating.

Read full answer

Applies toPostgreSQL 16 and 17

Conflict Handling Options

The ON CONFLICT clause specifies the conflict target and the action to take:

Action Behavior Typical use
DO NOTHING Leaves the existing row unchanged Ignore duplicate events
DO UPDATE Updates the conflicting row Increment counters or synchronize data

ON CONFLICT (product_id) infers an eligible non-deferrable unique index from a column or expression list, while ON CONFLICT ON CONSTRAINT inventory_pkey names the arbiter constraint directly. Exclusion constraints can also be arbiters; when inferred by columns or expressions, the conflict target’s predicate must match the exclusion constraint’s predicate.

Only a conflict on the selected arbiter triggers the requested action. If the proposed row violates a different unique constraint, the statement still fails.

How It Works

  • PostgreSQL performs a speculative insertion using the selected conflict target.

  • If no conflicting row exists, the row is inserted normally.

  • If a concurrent transaction is inserting the same key, PostgreSQL waits for that transaction before deciding whether to insert, skip, or update.

  • DO NOTHING skips the conflicting row without changing it.

  • DO UPDATE locks and updates the conflicting row.

  • The special EXCLUDED pseudo-relation contains the row that would have been inserted; the target table name or alias refers to the existing row.

For example, inventory.quantity + EXCLUDED.quantity increments the stored quantity by the proposed quantity. The update produces WAL records and follows PostgreSQL’s normal MVCC behavior.

Code Example

Create a table with a primary key and a nonnegative quantity:

CREATE TABLE inventory (
    product_id integer PRIMARY KEY,
    quantity integer NOT NULL CHECK (quantity >= 0),
    updated_at timestamptz NOT NULL DEFAULT now()
);

The following statement inserts a product with quantity 7. If product_id = 42 already exists with quantity 10, it increments the quantity to 17:

INSERT INTO inventory (product_id, quantity)
VALUES (42, 7)
ON CONFLICT (product_id) DO UPDATE
SET quantity = inventory.quantity + EXCLUDED.quantity,
    updated_at = clock_timestamp()
RETURNING product_id, quantity;

A result row looks like:

product_id quantity
42 17

An optional condition can restrict which conflicts cause an update:

INSERT INTO inventory (product_id, quantity)
VALUES (42, 7)
ON CONFLICT (product_id) DO UPDATE
SET quantity = inventory.quantity + EXCLUDED.quantity,
    updated_at = clock_timestamp()
WHERE inventory.quantity < 100
RETURNING product_id, quantity;

Here, an existing quantity of 100 or greater is left unchanged and no row is returned. With DO NOTHING, a conflicting row is likewise not returned by RETURNING.

A shortened EXPLAIN (ANALYZE, BUFFERS) result is:

Insert on public.inventory
  Conflict resolution: update
  Buffers: shared hit=...
Planning Time: ...
Execution Time: ...

EXPLAIN ANALYZE executes the statement, so use it carefully when the statement would modify production data.

Concurrency and Failure Modes

  • Statement atomicity: PostgreSQL does not expose a separate “check, then insert” race. Concurrent upserts targeting the same key are coordinated through speculative insertion and row locking.

  • Deadlocks: Concurrent upserts touching several rows in different orders can deadlock. Keep transactions short and update keys in a consistent order.

  • Lock contention: A hot upsert key can serialize requests. Monitor pg_stat_activity, its wait_event_type = 'Lock', and pg_blocking_pids() to identify blocked sessions.

  • Cardinality violation: ON CONFLICT DO UPDATE cannot affect the same existing row twice in one command; duplicate proposed keys that would update the same row raise an error.

  • Other errors remain errors: ON CONFLICT handles only eligible arbiter conflicts. Foreign-key, check, not-null, type-conversion, and violations of other unique constraints still fail the statement.

Production Practices

  • Ensure the conflict target is backed by a suitable non-deferrable unique index. Without an eligible unique index, conflict inference fails rather than falling back to an expensive table scan.

  • Use DO NOTHING for idempotent ingestion where retaining the original value is correct.

  • Use DO UPDATE for counters, but prevent unbounded increments if clients may retry the same request without idempotency controls.

  • Grant only the required INSERT, UPDATE, and SELECT privileges; DO UPDATE requires SELECT on columns read in its SET expressions and WHERE condition. Row-level security policies also apply.

  • Monitor write latency, deadlocks, lock waits, transaction age, and table size through pg_stat_activity, pg_stat_locks, and pg_stat_user_tables. Aggregate counters such as n_tup_ins and n_tup_upd show total changes but do not identify the responsible statements. Individual tuple locks are not displayed directly in pg_locks.

  • Test conflict behavior against production-like indexes, constraints, isolation levels, and concurrent workloads before deployment.

50

How do materialized views work and refresh in PostgreSQL?

A materialized view stores the result of a query physically in PostgreSQL, so reads can be faster than recomputing the query against large or complex source tables. Unlike a regular view, it is not automatically updated when the underlying data changes; it remains stale until refreshed.

Read full answer

Applies toPostgreSQL 16/17

How It Works

  • The defining query is executed when the materialized view is created.

  • The results are stored as a regular relation, including a heap and any indexes you create on it.

  • Queries can use those indexes just as they can use indexes on ordinary tables.

  • The source tables remain the authoritative data; the materialized view is a derived copy.

  • Materialized views do not automatically track source-table changes. Scheduling refreshes or using an external orchestration system is required.

Materialized views are useful for expensive aggregations, reporting datasets, dashboard queries, and workloads where slightly stale data is acceptable.

Refresh Methods

REFRESH MATERIALIZED VIEW recalculates the entire defining query and replaces the stored results:

REFRESH MATERIALIZED VIEW monthly_sales;

A normal refresh acquires an ACCESS EXCLUSIVE lock on the materialized view, so reads can be blocked while it runs. The operation is transactional: if it fails, the previous contents remain intact.

For a populated materialized view, PostgreSQL supports a concurrent refresh:

REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_sales;

CONCURRENTLY has important requirements:

  • It cannot run inside a transaction block.

  • The materialized view must already contain data.

  • It must have a valid, non-partial unique index containing only ordinary column names, with no expressions.

  • Only one concurrent refresh can run for a given materialized view at a time.

  • It allows ordinary readers to continue reading while the refresh executes.

  • It generally uses more CPU, temporary I/O, and WAL than a normal refresh because it reconciles the new result with the existing one.

A view can be created without populating it:

CREATE MATERIALIZED VIEW monthly_sales AS
SELECT ...
WITH NO DATA;

Populate it later with a normal REFRESH MATERIALIZED VIEW. A WITH NO DATA view cannot use CONCURRENTLY until it has been populated.

Code Example

The following runs on PostgreSQL 16 or 17:

CREATE TABLE sales (
    id integer PRIMARY KEY,
    sold_at timestamp NOT NULL,
    amount numeric(12,2) NOT NULL
);

INSERT INTO sales (id, sold_at, amount) VALUES
    (1, '2025-01-10 09:00:00', 100.00),
    (2, '2025-01-15 11:30:00', 50.00);

CREATE MATERIALIZED VIEW monthly_sales AS
SELECT
    date_trunc('month', sold_at)::date AS sale_month,
    sum(amount)::numeric(12,2) AS total_amount,
    count(*)::bigint AS order_count
FROM sales
GROUP BY 1;

CREATE UNIQUE INDEX monthly_sales_month_uidx
    ON monthly_sales (sale_month);

SELECT * FROM monthly_sales ORDER BY sale_month;

Typical result:

sale_month | total_amount | order_count
------------+--------------+-------------
 2025-01-01 |       150.00 |          2

The materialized view becomes stale after another insert:

INSERT INTO sales (id, sold_at, amount)
VALUES (3, '2025-01-20 14:00:00', 40.00);

SELECT * FROM monthly_sales;
-- Still reports 150.00 and 2 orders.

Refresh it:

REFRESH MATERIALIZED VIEW monthly_sales;

-- Or, after creating the required unique index:
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_sales;

The refreshed result is:

sale_month | total_amount | order_count
------------+--------------+-------------
 2025-01-01 |       190.00 |          3

The defining query can be inspected with EXPLAIN ANALYZE:

EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF)
SELECT
    date_trunc('month', sold_at)::date AS sale_month,
    sum(amount)::numeric(12,2) AS total_amount,
    count(*)::bigint AS order_count
FROM sales
GROUP BY 1;

A shortened plan may look like:

HashAggregate (actual time=... rows=1)
  -> Seq Scan on sales (actual time=... rows=3)

Production and Operational Considerations

  • Refresh cost: Refreshing scans the source data and writes the complete result, so monitor CPU, disk I/O, WAL generation, and temporary-file usage.

  • Lock contention: A normal refresh blocks readers and writers involving the materialized view. Use CONCURRENTLY when read availability is more important than refresh efficiency.

  • Staleness: There is no built-in freshness indicator. Monitor the source-to-view difference, refresh duration, and last successful refresh time.

  • Monitoring: Use pg_stat_activity for active refresh sessions, pg_stat_statements for refresh workload analysis, pg_locks for lock investigation, and pg_total_relation_size() for storage growth.

  • Replication and backups: Physical PostgreSQL replication includes materialized-view data changes. Logical replication generally requires a separate refresh process on subscribers. Physical backups include the data; logical dump tools such as pg_dump include materialized-view definitions and data.

Security and Best Practices

  • In PostgreSQL 16, refreshing generally requires ownership of the materialized view. PostgreSQL 17 supports the MAINTAIN privilege, allowing a controlled refresh role without granting broader ownership.

  • Grant users read access to the materialized view separately from the underlying tables.

  • Schedule refreshes with an external scheduler or pg_cron; do not place untrusted SQL directly into refresh commands.

  • Use CONCURRENTLY only after creating and validating the required unique index.

  • For very large views, test refresh duration and storage growth in production-like conditions. Consider partitioning the source data or maintaining a more incremental design if full refreshes become too expensive.

  • Keep the defining query deterministic and include every source column required by the reporting use case; otherwise, the materialized view may silently omit newly relevant fields.

51

How does declarative table partitioning work in PostgreSQL 16/17?

Declarative table partitioning is PostgreSQL’s native mechanism for dividing one logical table into multiple physical child tables by a partition key. PostgreSQL routes inserts to the appropriate child and can use partition constraints to exclude irrelevant children from query plans.

Read full answer

Applies toPostgreSQL 16/17

How It Works

A partitioned table is primarily a routing and metadata object; it does not store rows itself. Child partitions are registered as partitions of the parent and have their own storage, indexes, statistics, vacuum history, and physical location.

  • Partition key: Determines which partition receives a row. Range and list partition keys can contain multiple columns; hash partition keys can contain only one column or expression.

  • Partition bounds: Range bounds are lower-inclusive and upper-exclusive. List partitions explicitly assign particular key values.

  • Routing: Inserts automatically target the matching child. Updating a partition key can move a row to another partition.

  • Partition pruning: The planner removes child partitions that cannot satisfy the query. Additional pruning can occur at execution time after parameters become known.

  • Partitioned indexes: Creating a supported index on the parent creates corresponding child indexes. PostgreSQL 16/17 support B-tree, hash, and GiST partitioned indexes; GIN and BRIN can exist on individual child tables but not as parent partitioned indexes.

  • Default partition: Receives values not matched by another partition unless its constraints reject them.

Before PostgreSQL 10, partitioning was commonly emulated through inheritance, CHECK constraints, and constraint_exclusion; that arrangement is distinct from native declarative partitioning.

Partitioning Strategies

Strategy Typical use Characteristics
Range Monthly events, logs, time-series data Supports ordered data and efficient retention of old partitions
List Region, tenant category, status Explicitly maps selected key values to partitions
Hash Large tables without a natural range Spreads rows by hash; provides no ordering or direct age-based retention
Default Catch-all partition Convenient, but can conceal missing partition definitions unless carefully constrained

Partitioning helps most when queries contain selective predicates on the partition key and data naturally divides into manageable groups.

Query Planning and Constraints

Use EXPLAIN ANALYZE to verify that only relevant children are scanned. It executes the statement, so it can be expensive in production and should be used with realistic but safe queries.

Unique and primary-key constraints on a partitioned table must include every partition-key column, including columns referenced by an expression key. This allows PostgreSQL to prove that conflicting rows cannot reside in different partitions. Indexes remain local to each child; PostgreSQL has no global partitioned-table index.

Production and Operational Consequences

  • DDL locking: Creating, attaching, or detaching a partition requires parent-table locks and creates catalog contention. Schedule disruptive operations carefully.

  • Retention: Detaching a partition removes it from parent routing but does not delete its data; archive or drop it separately. DETACH PARTITION ... CONCURRENTLY reduces locking but cannot run inside a transaction block and has additional restrictions involving default partitions.

  • Vacuum and bloat: Autovacuum processes each child independently. Monitor pg_stat_user_tables and pg_stat_user_indexes; one neglected partition can still consume substantial space.

  • MVCC and wraparound: Long-running transactions and prepared transactions can hold back xmin, retain dead row versions, and prevent transaction-ID cleanup. An inactive physical replication slot primarily retains WAL; logical slots can also retain catalog data and affect row visibility.

  • Partition churn: Frequent attach or detach operations generate DDL WAL and catalog work, but do not inherently increase row-data volume. Too many small partitions increase planning and operational overhead.

  • Backups and PITR: Physical base backups include child-partition data, and WAL-based PITR covers partition DDL and data changes.

  • Replication: Physical standbys replay partition DDL and data changes through WAL. Invalid or conflicting DDL can therefore fail or delay replicas.

  • Security: Access through the parent checks the parent’s privileges, while direct access to a child checks that child’s privileges separately.

Best Practices

  • Partition by a stable, frequently queried dimension such as event time.

  • Avoid excessive numbers of tiny partitions and monitor child-level vacuum age, dead tuples, index size, lock waits, replication lag, and WAL generation.

  • Pre-create future partitions before data arrives, or use a carefully constrained default partition.

  • When adding a partition while a default partition exists, account for PostgreSQL’s validation of conflicting rows.

  • Test detach, backup, restore, and point-in-time-recovery procedures with production-scale partition counts.

52

How do range, list, and hash partitioning differ in PostgreSQL?

In PostgreSQL declarative partitioning, range, list, and hash partitioning place rows in physical child tables using different routing rules. Range partitioning uses ordered boundaries, list partitioning uses discrete values, and hash partitioning assigns each row to one of a fixed number of partitions using the partition key’s hash.

Read full answer

Applies toPostgreSQL 16 and 17

Core Differences

Method Partition definition Typical use Distribution and pruning
Range Lower-inclusive and upper-exclusive FROM/TO boundaries Time windows, numeric bands Can be uneven; prunes well for predicates on the partition key
List Explicit values using IN Statuses, categories, regions Depends on value frequency; prunes well for equality predicates
Hash MODULUS and REMAINDER Large tables without meaningful boundaries Usually distributes keys evenly; equality predicates can enable pruning
  • Range partitioning: Each partition covers one boundary interval. For example, January 2025 belongs to the range from 2025-01-01 inclusive to 2026-01-01 exclusive.

  • List partitioning: Each partition contains specified values. Adding a category normally requires another partition, unless a default partition is intended to capture it temporarily.

  • Hash partitioning: PostgreSQL calculates an internal hash of the key and uses the remainder to select a partition. Hash partitioning supports exactly one partition-key column, while range and list partitioning support multiple columns.

  • The hash modulus is effectively fixed for that partitioned-table design. Changing it requires redistributing rows into a differently partitioned design.

Routing and Planning

  • The parent table stores partition metadata in PostgreSQL catalogs. Each child is a physical table with its own rows, indexes, statistics, and storage.

  • A unique or primary-key constraint on a partitioned table must include every partition-key column, ensuring uniqueness across all partitions.

  • Range and list pruning use partition constraints. Hash pruning can use equality conditions that let PostgreSQL calculate the target partition.

  • The planner removes irrelevant partitions before execution, while runtime pruning can further exclude rows using constraints that were not fully known at planning time.

  • EXPLAIN (ANALYZE, BUFFERS) shows retained partitions, estimated and actual row counts, buffer usage, and whether child indexes are used. Exact plans depend on table size, statistics, indexes, and data distribution.

  • Partitioning does not provide a global index. Queries that cannot prune partitions must inspect multiple children, and indexes remain local to each child.

Operational Consequences

  • Data skew: A range or list partition can grow much larger than the others. Hash partitioning generally distributes distinct keys evenly, but a frequently used key still concentrates all matching rows in one partition.

  • Default partitions: A default partition captures otherwise unmatched rows. When attaching a new range or list partition, PostgreSQL may scan the default partition to verify that it contains no conflicting rows; a suitable constraint can avoid that scan.

  • Maintenance: VACUUM, ANALYZE, and index maintenance ultimately concern the physical partitions. Large or uneven children can extend maintenance windows and increase cache pressure.

  • Storage and backups: Child tables consume storage independently. Physical tools such as pgBackRest and pg_basebackup include their data as part of the cluster backup, while WAL is required for point-in-time recovery.

  • Transaction ID wraparound: Partitioning does not prevent wraparound. Monitor age(datfrozenxid) in pg_database; pg_stat_database provides workload counters such as transaction counts but does not replace wraparound monitoring.

  • Operational failure modes: Missing future partitions cause rejected inserts, oversized partitions increase recovery and maintenance time, and accidental default-partition growth can make queries scan unexpected data.

Production Best Practices

  • Choose range for time-oriented data, list for stable business categories, and hash when no useful boundaries exist and even key distribution is more important.

  • Use a default partition only when retaining unexpected values is intentional. Monitor it for unexpected growth and reconcile its contents before attaching targeted partitions.

  • Create future range partitions before ingestion deadlines, and archive or detach old partitions according to retention and query requirements.

  • Use pg_stat_user_tables for child-table row estimates, dead-tuple counters, cache statistics, and vacuum or analyze activity. Use pg_relation_size or pg_total_relation_size for storage measurements and an extension-assisted method such as pgstattuple when bloat analysis is necessary.

  • Use pg_stat_statements to track frequently executed and expensive statements, and use EXPLAIN (ANALYZE, BUFFERS) to verify pruning and index behavior.

  • Test partition creation, attachment, detachment, backup, restore, upgrades, and point-in-time recovery procedures before relying on them in production.

53

How does PostgreSQL streaming replication work?

Streaming replication continuously transfers Write-Ahead Log (WAL) records from a primary PostgreSQL server to one or more physical standbys. A standby can maintain a read-only copy for disaster recovery, read scaling, or controlled failover, but replication is not a replacement for backups.

Read full answer

Applies toPostgreSQL 16 and 17

How It Works

  • PostgreSQL uses write-ahead logging: changes are represented in WAL and written before the corresponding data pages are permanently written. WAL is also generated for changes from transactions that may later roll back.

  • For each replication connection, a primary WAL sender transfers WAL to the standby’s WAL receiver. The standby’s startup process reads that WAL and replays the changes into the standby’s own data directory.

  • A standby is commonly initialized with pg_basebackup. It then requires standby.signal, primary_conninfo, and normally a physical replication slot. If connectivity is interrupted, the receiver reconnects and requests missing WAL.

  • Physical replication copies database files and cluster state, not logical row changes. Each server has an independent data directory, storage, WAL, and shared-buffer pool. Logical replication is a separate mechanism that can replicate selected tables or changes.

  • Physical streaming requires wal_level to be at least replica, an available WAL sender, and a replication-authenticated connection.

  • A hot standby accepts read-only SQL and can serve reads through a separate connection pool. Read scaling requires routing queries to the standby and accounting for replication lag.

Commit and Consistency Modes

Mode Commit behavior Trade-off
Asynchronous The primary does not wait for a standby to receive or apply WAL Lowest write latency, but acknowledged transactions may be lost during failover
Synchronous write The primary waits for WAL to be written on a synchronous standby, but not necessarily flushed to its disk Less durable than flush or apply while avoiding a disk-flush wait
Synchronous flush The primary waits for WAL to be flushed on a synchronous standby Protects against an operating-system-level loss on that standby, but replay may still lag
Synchronous apply The primary waits for WAL to be replayed on a synchronous standby Makes committed changes visible to new standby snapshots before the primary commit completes, with additional latency

synchronous_commit selects the local and remote durability behavior, while synchronous_standby_names identifies eligible synchronous standbys. Quorum-style selection can preserve availability when one member fails, but it does not guarantee that every standby has applied each transaction.

Production Behavior and Monitoring

  • Query pg_stat_replication on the primary to inspect each standby’s application name, client address, connection state, synchronization state, sent LSN, and replay LSN. WAL-position differences estimate byte lag, while replay_lag is an interval estimate rather than an exact delay.

  • Query pg_stat_wal_receiver on the standby to check whether WAL is being received, flushed, and made available for replay.

  • pg_stat_database_conflicts on the standby identifies queries canceled because WAL replay needed to cancel conflicting database locks. max_standby_streaming_delay limits such stalls, while hot_standby_feedback can preserve rows needed by standby snapshots but may increase primary bloat.

  • An inactive physical replication slot retains WAL that a disconnected standby still needs. Monitor pg_replication_slots, WAL retention, and free disk on the primary so an abandoned slot cannot exhaust storage.

  • A standby does not automatically become primary. Promotion must be explicit, and the old primary must first be stopped or fenced to prevent split-brain. After promotion, verify application connectivity and either rewind or rebuild the old primary before reusing it.

Backups, Security, and Best Practices

  • Retain independent base backups and archive WAL continuously to support point-in-time recovery. pg_basebackup alone does not provide an ongoing backup strategy, and replication slots do not replace backups.

  • Use TLS for replication connections, require certificate or SCRAM authentication as appropriate, restrict pg_hba.conf to trusted source networks, and protect replication credentials. Replication roles are highly privileged and should not be shared with application accounts.

  • Alert when a standby disappears, leaves the streaming state, or shows sustained WAL or replay lag. Also alert on slot retention growth, low disk space, replication conflicts, and backup or WAL-archive failures.

  • Test restoration, point-in-time recovery, failover, and rejoin procedures regularly. Synchronous replication reduces potential data loss but can reduce write availability and increase latency; asynchronous replication improves availability but permits replication lag.

54

How does synchronous replication differ from asynchronous replication in PostgreSQL?

Synchronous replication makes the primary wait for acknowledgments from standbys selected by synchronous_standby_names before acknowledging a WAL-writing transaction’s commit; asynchronous replication does not wait for remote standby acknowledgment. With PostgreSQL’s default synchronous_commit = on, a transaction covered by the synchronous policy waits for selected standbys to flush WAL to durable storage, not necessarily replay it.

Read full answer

Applies toPostgreSQL 16 and 17

Core Mechanics

  • The primary records changes in its write-ahead log (WAL).

  • A walsender process streams WAL to each standby’s walreceiver process.

  • The standby’s startup process replays WAL and can serve read-only queries when hot_standby = on.

  • With asynchronous replication, the primary can acknowledge commits while WAL is still being transferred or replayed remotely.

  • With synchronous replication, synchronous_standby_names selects eligible standbys using policies such as ANY and FIRST.

  • synchronous_commit controls the wait:

  • on: wait for remote WAL flush.

  • remote_write: wait only for remote WAL write, not a durable flush.

  • remote_apply: wait for remote WAL replay.

  • off and local: do not wait for remote standbys.

Synchronous replication is a commit-acknowledgment policy, not a different WAL format. Logical replication consumers do not provide this synchronous commit acknowledgment mechanism.

Behavior Comparison

Aspect Synchronous replication Asynchronous replication
Commit behavior Waits for acknowledgments satisfying the configured policy Does not wait for a standby
Remote durability guarantee Selected standbys have flushed the WAL No remote durability guarantee
Commit latency Usually higher and network-dependent Generally lower
Acknowledged-transaction loss Protected if a standby that acknowledged the transaction survives with intact storage Recent transactions may be lost during failover
Availability Commits can block when the policy cannot be satisfied Less affected by standby failures
Typical use Durability-critical OLTP Read scaling, distant replicas, and higher-throughput workloads

Production Failure Modes

  • Blocked commits: If fewer eligible standbys are connected than the policy requires, commits can wait indefinitely. Client timeouts, connection saturation, and cascading failures may result.

  • Network latency: Synchronous commits add network round-trip time and remote WAL-processing time.

  • Replication lag: Asynchronous standbys can fall behind because of network degradation, write bursts, index maintenance, or insufficient standby resources.

  • WAL retention: Physical replication slots can retain WAL for inactive or lagging standbys until the primary runs out of disk space.

  • Policy override: synchronous_commit = local or off bypasses the remote wait even when synchronous standbys are configured, removing their remote durability guarantee.

  • Failover scope: ANY 1 requires one standby, but it does not protect against simultaneous loss of all standbys. FIRST n requires n acknowledgments; if higher-priority standbys are unavailable, lower-priority listed standbys can fill the required positions.

Monitoring and Recovery

  • pg_stat_replication exposes application_name, state, sync_state, WAL positions, write_lag, flush_lag, and replay_lag.

  • sync_state values include:

  • quorum: participating in the active synchronous quorum.

  • potential: eligible for synchronous operation but not currently counted.

  • async: configured for asynchronous replication.

  • pg_stat_wal_receiver shows WAL receipt and replay status from the standby’s perspective.

  • pg_stat_activity identifies synchronous-replication waits with wait_event_type = 'WAL' and wait_event = 'Sync'.

  • Lag values are sampled operational indicators rather than exact byte counts and can be NULL when there has been no recent activity.

  • Set max_slot_wal_keep_size to a finite value where appropriate. If required WAL is removed, an affected standby may need restoration or reconfiguration.

  • Replication is not a backup. Maintain base backups and WAL archiving for point-in-time recovery, and regularly test restores.

Best Practices

  • Use physical streaming replication with base backups, standby.signal, primary_conninfo, and physical replication slots where appropriate.

  • Distribute synchronous standbys across independent failure domains, while recognizing that geographic latency directly affects commit time.

  • Alert on sync_state, replication state, replay delay, commit-wait latency, replication-slot WAL usage, and primary WAL-disk usage.

  • Protect replication traffic with TLS, restrict access through pg_hba.conf, and provision credentials securely using mechanisms such as .pgpass.

  • Decide whether the priority is zero acknowledged-transaction loss or maximum write availability before selecting the synchronous policy.

55

What is logical replication in PostgreSQL?

Logical replication is PostgreSQL’s change-data-capture mechanism: it publishes committed row changes from selected tables and applies them to subscriber tables. It enables selective data distribution and supported cross-major-version migrations without copying the publisher’s entire database cluster.

Read full answer

Applies toPostgreSQL 16 and 17

How It Works

  • Publisher configuration: The publisher must use wal_level = logical, which requires a restart. Its capacity must also allow the required replication slots and WAL sender processes.

  • Publication: A publication identifies the tables, columns, and optional row filters to expose. PostgreSQL 16/17 support publication-level row filters, such as publishing only qualifying rows from a table.

  • Subscription: A subscription connects to a publisher, normally creates a replication slot, copies the initial table data, and then applies subsequent changes. The subscriber schema must already exist and be compatible.

  • Apply worker: Each subscription has an ongoing apply worker in PostgreSQL 16/17. It applies transactions in commit order; PostgreSQL 16 and later can also hand large in-progress transactions to parallel apply workers with streaming = parallel. Initial table synchronization may use multiple workers, and independent subscriptions can apply concurrently.

  • Replication slots: A logical slot retains WAL until the subscriber confirms receipt. An inactive or failed subscription can therefore cause unbounded WAL growth on the publisher.

  • Scope: Inserts, updates, deletes, and TRUNCATE can be replicated. Schema-changing DDL, sequences, temporary and unlogged tables, and large objects are not replicated by the built-in mechanism.

When to Use

  • Selective replication: Publish only required tables, columns, or rows rather than replicating an entire cluster.

  • Data distribution: Feed reporting, analytics, search, or audit consumers independently from one publisher.

  • Major-version migration: Move compatible schemas and data between PostgreSQL 16 and 17, or other supported major versions.

  • Multiple consumers: Maintain independent subscriptions with different publication requirements or retention policies.

Logical replication is not a complete active-active solution. Concurrent changes can produce subscriber conflicts, and PostgreSQL 16/17 have no built-in general-purpose conflict-resolution framework. Native transformation is also limited; publication filters can select rows, but custom value transformations require subscriber-side mechanisms such as carefully designed triggers or an ETL workflow.

Logical vs Physical Replication

Aspect Logical replication Physical replication
Replicates Selected logical changes Physical database blocks and WAL
Major versions Often possible when types and schemas are compatible Requires compatible major versions
Schema changes Must be coordinated separately Included through WAL replay
Filtering Publications can filter tables, columns, and rows No selective filtering
Conflict handling Subscriber conflicts may stop apply Generally failover-oriented
Typical use Data distribution and migrations High availability and disaster recovery

Production Operations

  • Monitor publisher connections with pg_stat_replication and slots with pg_replication_slots. Track subscriber lag, slot activity, retained WAL, and publisher free-disk space.

  • Monitor the subscriber with pg_stat_subscription. A stopped apply worker, replication error, or long-running initial synchronization requires investigation rather than simply dropping the slot.

  • Treat slot removal or advancement as a data-loss operation: skipped changes generally require rebuilding and re-synchronizing the affected subscription.

  • Monitor subscriber bloat and autovacuum behavior. Logical apply writes rows but does not replace relation storage, so VACUUM, ANALYZE, and suitable autovacuum settings remain important.

  • Long-running transactions can retain dead tuples and prevent transaction IDs from being frozen, increasing wraparound risk. Monitor transaction age, age(datfrozenxid), and lock contention using PostgreSQL statistics views.

  • Logical replication is not a backup. Use base backups plus WAL archiving for point-in-time recovery, and regularly test restoration.

  • Coordinate schema changes, extension upgrades, partition changes, and major-version upgrades manually or with dedicated schema-management tooling.

Security and Common Pitfalls

  • Encrypt publisher connections with TLS and certificate verification. Restrict replication access with pg_hba.conf, firewalls, and private networking.

  • The remote publisher role needs LOGIN and REPLICATION privileges plus SELECT on published tables for initial synchronization. Grant only required privileges.

  • pg_subscription stores connection information, which may include credentials. Protect access and avoid embedding reusable plaintext passwords.

  • Every published table must have an appropriate replica identity. Prefer a primary key or unique index; use REPLICA IDENTITY FULL only when necessary because it increases WAL volume.

  • A subscription apply conflict normally leaves the worker stopped. Correct the conflicting data or schema, then re-enable it with ALTER SUBSCRIPTION ... ENABLE.

  • DROP SUBSCRIPTION also removes the associated remote replication slot by default. Do not manually drop that slot while the subscription remains active.

56

What are PostgreSQL replication slots and their risks?

Replication slots are server-side records that track the write-ahead log positions required by replication consumers, preventing PostgreSQL from recycling WAL that is still needed. They make physical and logical replication more reliable, but an abandoned or stalled consumer can retain WAL indefinitely, exhaust disk space, impede vacuuming, and complicate failover.

Read full answer

Applies toPostgreSQL 16 and PostgreSQL 17

How Slots Work

PostgreSQL records committed changes in the write-ahead log (WAL) before applying them to tables. A replication slot records how far a consumer has progressed, allowing PostgreSQL to retain the WAL that consumer still requires.

  • Physical slots retain WAL for a physical standby or a pg_basebackup client using a slot.

  • Logical slots retain WAL and decoding state for logical replication clients.

  • A slot does not provide the initial data copy. Physical replication still requires a base backup; logical replication still requires schema and initial-data synchronization.

  • active = true means a replication client is currently connected. An inactive slot remains in pg_replication_slots and can continue retaining WAL.

  • Persistent slots survive server restarts and are copied in some backup workflows. Temporary slots disappear when the creating session ends.

  • WAL files should not be deleted manually to relieve disk pressure; doing so can break replication and recovery.

Physical, Logical, and Cascading Replication

Type Consumer Retained state Primary risk
Physical Standby server or pg_basebackup client Physical WAL Standby failure retains WAL and fills pg_wal
Logical Logical replication subscriber WAL plus logical decoding state Subscriber failure retains WAL and can block vacuum cleanup
Cascading logical Subscriber obtains decoded changes through a physical replica WAL and decoding state on the replica Adds standby lag and slot-management failure modes

PostgreSQL 16 added logical decoding on standby servers, enabling cascading logical replication. PostgreSQL 17 added logical failover slots, which can be synchronized to physical standbys (with sync_replication_slots enabled) and remain available after promotion. Slot synchronization does not remove the need to validate subscriber state, LSN continuity, and ownership after failover.

Main Production Risks

  • Disk exhaustion: A disconnected or slow consumer can cause PostgreSQL to retain WAL indefinitely. The default max_slot_wal_keep_size = -1 imposes no limit on slot-retained WAL.

  • Slot invalidation: With a finite max_slot_wal_keep_size, a slot that requires more WAL than the limit can become invalidated after required WAL is removed. A physical consumer may need a new base backup; a logical subscriber may need resynchronization.

  • MVCC and bloat: Logical decoding state and standby feedback can retain old xmin or catalog_xmin horizons. Vacuum cannot remove all affected dead tuples, increasing table and index bloat and making transaction-ID wraparound prevention harder.

  • Replication lag: A slow subscriber delays advancement of confirmed_flush_lsn, increasing retained WAL even while its connection remains active.

  • Failover surprises: Ordinary slots are local to the server where they were created. PostgreSQL 17 failover slots provide controlled synchronization to standbys, but promotion still requires testing and may require consumer reinitialization.

  • Security exposure: Logical decoding can expose historical row changes. Use dedicated replication roles, TLS with certificate verification, restricted pg_hba.conf rules, and protected client credential files.

  • False recovery confidence: Slots do not replace base backups, WAL archiving, or tested point-in-time recovery procedures.

Monitoring and Detection

  • pg_replication_slots: Monitor active, restart_lsn, confirmed_flush_lsn, wal_status, safe_wal_size, and, on PostgreSQL 17, invalidation_reason. safe_wal_size is an estimate of remaining WAL headroom and should not replace disk-capacity alerts.

  • pg_stat_replication: On a primary, inspect connected physical standbys, their state, synchronous status, sent and replay LSNs, and write or flush lag.

  • pg_stat_subscription: On a logical subscriber, inspect received_lsn, latest_end_lsn, worker state, and timestamps for delayed message receipt.

  • Alert on inactive slots, growing retained WAL, invalidation, subscriber lag, WAL-disk utilization, and tables whose oldest transaction horizons remain high.

Operational Best Practices

  • Configure a finite max_slot_wal_keep_size when disk protection is more important than uninterrupted replication, while understanding that invalidation can become a consumer outage.

  • Keep disk monitoring independent of slot settings because archived WAL, other replicas, and other WAL consumers also consume space.

  • Drop stale slots only after confirming that no standby, subscriber, backup process, or failover plan depends on them.

  • After upgrades or topology changes, validate existing slots, LSN continuity, and subscriber state. Recreate a slot only as part of a planned reinitialization that includes the required base backup or logical resynchronization.

  • Restrict replication access, require encrypted connections, and monitor pg_replication_slots and replication statistics continuously.

  • Regularly test standby promotion, failover-slot behavior, logical resynchronization, slot invalidation, base backups, and point-in-time recovery.

57

How do you fail over a PostgreSQL standby server?

PostgreSQL failover promotes a physical standby to a writable primary, ending recovery on a new timeline. The old primary must be fenced or kept stopped to prevent split-brain, and acknowledged transactions can be lost if the candidate standby had not durably received their WAL.

Read full answer

Applies toPostgreSQL 16 and 17

How Failover Works

The standby’s WAL receiver receives WAL from the primary, while the startup process replays it. Promotion ends recovery and creates a new timeline, after which the server accepts writes.

Failover does not create a backup, reverse replication automatically, or move application traffic. Endpoint routing and old-primary fencing require an HA orchestrator, DNS, a load balancer, or a connection-pooling service.

Scenario Typical action Data-loss risk
Planned switchover Quiesce writes, record the final LSN, stop the primary, wait for replay catch-up, prevent restart, and promote Minimal when catch-up completes and synchronous replication was active
Unplanned failure Fence the failed primary, select the most advanced standby, promote it, and redirect clients Committed transactions whose WAL was not durably received may be lost
Old primary returns Rewind or rebuild it, then attach it as a standby It must never accept writes on the old timeline

Preconditions and Health Checks

Before promotion, confirm that the candidate is still in recovery, its WAL receiver is streaming, and it has sufficient WAL. On the primary, pg_stat_replication exposes sender and flush positions; pg_stat_wal_receiver exposes receiver state on the standby.

For unplanned failover, durable receive or flush lag determines the potential RPO. Replay lag is different: WAL received but not yet replayed can normally be replayed after promotion.

Recovering the Old Primary

Do not start the old primary as a writable server. Prefer pg_rewind; otherwise rebuild it with pg_basebackup.

pg_rewind requires the target to be cleanly shut down and both servers to use the same PostgreSQL major version. The target must have checksums enabled at initialization or wal_log_hints enabled during the relevant writes, and full_page_writes must remain enabled. The source role needs superuser rights or the specific function and file-read permissions required by pg_rewind.

After rewind or rebuild, configure primary_conninfo and primary_slot_name, create the required replication slot on the new primary, create standby.signal, and start the old server as a standby.

Code Example

The following paths are for Debian/Ubuntu packages; use PG_MAJOR=16 on PostgreSQL 16:

export PG_MAJOR=17
export PG_BINDIR="/usr/lib/postgresql/${PG_MAJOR}/bin"
export PGDATA="/var/lib/postgresql/${PG_MAJOR}/main"

Check replication on the primary:

SELECT application_name, state, sync_state,
       pg_wal_lsn_diff(sent_lsn, flush_lsn) AS flush_lag_bytes,
       pg_wal_lsn_diff(sent_lsn, replay_lsn) AS replay_lag_bytes
FROM pg_stat_replication;

Example result:

application_name | state     | sync_state | flush_lag_bytes | replay_lag_bytes
-----------------+-----------+------------+-----------------+-----------------
standby1         | streaming | sync       | 0               | 0

For a planned switchover, first disable application writes and automatic HA restart. On the primary, record the final LSN and stop the server cleanly:

set -eu
FINAL_LSN=$(sudo -u postgres "$PG_BINDIR/psql" -Atqc \
  "SELECT pg_current_wal_lsn();")
sudo -u postgres "$PG_BINDIR/pg_ctl" -D "$PGDATA" stop -m fast

On the standby, replace the value below with the recorded LSN and wait, with a timeout, for replay to reach it:

set -eu
FINAL_LSN=0/5000060
replayed=f
for _ in $(seq 1 60); do
  replayed=$(sudo -u postgres "$PG_BINDIR/psql" -Atqc \
    "SELECT pg_last_wal_replay_lsn() >= '$FINAL_LSN'::pg_lsn;")
  [ "$replayed" = t ] && break
  sleep 1
done
if [ "$replayed" != t ]; then
  echo "Standby did not replay through $FINAL_LSN" >&2
  exit 1
fi

Promote and verify with another bounded wait:

sudo -u postgres "$PG_BINDIR/pg_ctl" -D "$PGDATA" promote

state=r
for _ in $(seq 1 60); do
  state=$(sudo -u postgres "$PG_BINDIR/psql" -Atqc \
    "SELECT pg_is_in_recovery();")
  [ "$state" = f ] && break
  sleep 1
done
if [ "$state" != f ]; then
  echo "Promotion did not complete" >&2
  exit 1
fi

For unplanned failure, externally fence the old primary before running the promotion and verification commands.

To rewind the stopped old primary:

sudo -u postgres "$PG_BINDIR/pg_rewind" \
  --target-pgdata="$PGDATA" \
  --source-server="host=new-primary.example.com port=5432 user=rewind_user dbname=postgres"

Production Best Practices

  • Fence before promotion: A database shutdown alone is insufficient if an HA agent can restart the old primary.

  • Protect replication traffic: Restrict pg_hba.conf to standby addresses and use SCRAM authentication or certificates, never trust.

  • Monitor WAL retention: Persistent slots can retain WAL and fill pg_wal; monitor pg_replication_slots, disk usage, sender state, and receiver lag.

  • Understand RPO: sync protects acknowledged commits only while a synchronous standby retains the WAL. quorum requires a surviving quorum.

  • Maintain independent backups: Use base backups and WAL archiving for PITR; a standby can replicate accidental deletion or corruption.

  • Automate carefully: Patroni and managed PostgreSQL services can coordinate fencing, promotion, routing, and rewind. Avoid manual promotion inside an actively managed HA cluster.

58

How do Patroni and repmgr provide PostgreSQL high availability?

High availability (HA) for PostgreSQL combines physical replication with an external control plane that detects primary failure, promotes a standby, and redirects clients. Patroni uses a distributed configuration store for coordinated failover, whereas repmgr stores cluster metadata in PostgreSQL and uses the repmgrd daemon, optionally assisted by witness nodes.

Read full answer

Applies toPostgreSQL 16 with Patroni 4.0+ or repmgr 5.6.1+; PostgreSQL 17 with Patroni 4.1+ or repmgr 5.6.1+

Tool Comparison

Area Patroni repmgr
Coordination Uses a DCS such as etcd, Consul, ZooKeeper, or the Kubernetes API Uses a dedicated repmgr database, repmgrd, and optional witnesses
Failover Automatic promotion following leader-key expiry in the DCS Manual promotion or deprecated witness-assisted automatic failover
Health API REST API, normally listening on port 8008 Daemon status and repmgr metadata; no equivalent distributed REST API
Configuration Cluster configuration in the DCS with per-node overrides Primarily per-node repmgr.conf with shared cluster metadata
Replication PostgreSQL physical streaming replication PostgreSQL physical streaming replication
Typical environment Kubernetes, containers, and dynamic infrastructure Traditional Linux database clusters
Split-brain handling DCS leader coordination plus infrastructure fencing Witness quorum checks plus mandatory external fencing

Neither tool replaces backups. Point-in-time recovery requires a base backup and continuous WAL archiving, commonly managed by pgBackRest, Barman, or WAL-G.

How It Works

  • Patroni

  • A Patroni process runs alongside each PostgreSQL instance and manages replication-related configuration.

  • The current primary holds a leader key in the DCS with a finite lifetime. If it stops renewing that key, an eligible standby can acquire the key and promote itself.

  • New standbys can be initialized with pg_basebackup. After failover, pg_rewind may be used to prepare the former primary for rejoin as a standby.

  • The REST API exposes role, state, and replication information for load balancers, monitoring, and operational automation.

  • Because DCS coordination is not physical fencing, network or storage isolation should prevent an isolated former primary from remaining writable.

  • repmgr

  • The repmgr extension provides the metadata schema and supporting functions in a dedicated repmgr database.

  • repmgrd monitors the local PostgreSQL service, replication state, and cluster membership.

  • Standbys use physical streaming replication and are registered with the primary; an operator or repmgrd can promote a selected candidate.

  • A witness participates in quorum checks without storing the database's data. It does not fence nodes, so automatic failover still requires strict network or storage fencing.

  • Automatic failover remains available in repmgr 5.6 but is deprecated; new production systems requiring automatic failover should generally use Patroni or another maintained HA solution.

Both tools normally use asynchronous streaming replication. Consequently, a primary failure can lose recently acknowledged transactions if the corresponding WAL has not reached a standby. Synchronous replication can reduce the recovery point objective, but unavailable synchronous standbys can block or delay commits.

Failure Modes and Monitoring

  • Replication lag: Monitor pg_stat_replication on the primary and pg_stat_wal_receiver on standbys, including write, flush, and replay lag.

  • WAL retention: An abandoned replication slot can retain WAL indefinitely and exhaust primary storage. Monitor pg_replication_slots and alert on growth.

  • Split brain: Verify that infrastructure fencing removes an isolated old primary from service and prevents clients from reconnecting to it.

  • Lock contention: Use pg_stat_activity and pg_locks to identify long-running transactions and DDL waiting on conflicting locks.

  • Bloat and wraparound: Track estimated dead tuples in pg_stat_user_tables.n_dead_tup, autovacuum activity, and transaction-ID age using pg_database.datfrozenxid.

  • Archive failures: pg_stat_archiver exposes failed or lagging WAL archiving, which creates a point-in-time recovery gap.

  • Incorrect routing: Application connections must follow the current primary. Use service discovery, HAProxy, PgBouncer, or Patroni-aware routing to avoid writes to a demoted node.

Production Best Practices

  • Deploy at least one standby, preferably across independent failure domains. Patroni requires a highly available DCS; repmgr witness nodes must also be placed according to its quorum requirements.

  • Keep PostgreSQL major versions consistent across replicas and rehearse the supported upgrade procedure before moving from PostgreSQL 16 to 17.

  • Protect PostgreSQL, replication, REST API, DCS, and monitoring traffic with TLS and network controls. Load the injected secret scram-sha-256 from a secret manager rather than committing credentials to patroni.yml or repmgr.conf.

  • Separate application users from replication and orchestration accounts, and do not use PostgreSQL superusers for application traffic.

  • Exercise switchover, failover, fencing, rejoin, restore, and point-in-time recovery regularly.

  • Alert on primary loss, DCS instability, missing standbys, replication lag, growing slots, WAL archive failures, disk exhaustion, and transaction-ID wraparound risk.

59

How does pg_dump differ from physical backups in PostgreSQL?

pg_dump creates a logical backup of database objects and rows, while a physical backup copies the PostgreSQL cluster’s data files. Logical backups are selective and relatively portable; physical backups support fast, whole-cluster recovery but require matching PostgreSQL major versions and compatible platforms.

Read full answer

Applies toPostgreSQL 16 and 17

Core Difference

Area pg_dump logical backup Physical backup
Captured content Objects, schema, data, sequences, constraints and indexes Cluster data files, catalogs and indexes, plus the WAL required for recovery
Scope One database per invocation Entire cluster, including all databases
Representation Plain SQL or a restorable archive Native PostgreSQL data-directory format
Indexes Rebuilt during restoration Copied as stored
Restore target A new or compatible database A compatible PostgreSQL cluster
Version portability Commonly loadable into a later major version; backward loading is not supported Requires the same major version and compatible architecture and OS
Selective restore Supports table- and schema-level selection Normally restores the complete cluster
PITR Not a native point-in-time recovery mechanism Supported with base backups and continuous WAL archiving

Backup and Restore Operations

  • Logical backup: pg_dump --format=custom --file=... creates a compressed custom archive. pg_restore --list displays its contents. The target database must already exist unless it is created separately. For a custom archive, pg_restore --single-transaction --clean --if-exists can restore the selected contents atomically, preventing a failed restore from leaving a partially restored database.

  • Physical backup: pg_basebackup --format=plain --wal-method=stream creates a base backup in an empty, writable directory. The connecting role needs the REPLICATION attribute or appropriate superuser privileges. With streaming, required WAL is transferred during the backup; for long-term PITR, WAL must also be continuously archived outside the backup directory.

  • Transport security: Use TLS with certificate verification and SCRAM authentication rather than sending credentials over an untrusted network. Encrypt sensitive backups at rest.

How They Work

pg_dump reads catalogs and table data through PostgreSQL’s SQL interfaces and COPY, using a consistent snapshot so normal writes can continue. It exports logical objects and data rather than physical storage layout, free space or index files. It does not restore cluster roles or tablespace definitions, and it does not preserve unlogged-table data.

A physical backup copies the cluster files as represented on disk. Recovery therefore needs compatible PostgreSQL binaries, extension libraries, locale and collation support, platform compatibility, and correct data-directory ownership and permissions. pg_basebackup coordinates the required checkpoint and WAL transfer rather than blindly copying every transient file.

Recovery and Production Operations

  • PITR: Restore the base backup, configure restore_command, create recovery.signal, and set the desired recovery target, such as recovery_target_time. PostgreSQL may pause when the target is reached; use recovery_target_action = 'promote' for automatic promotion or promote it deliberately.

  • WAL retention: Retain archived WAL from the backup’s start through the latest required recovery point. If later WAL is unavailable, the backup may still restore to its own consistency point, but PITR beyond that point is impossible.

  • Monitoring: Track running base backups with pg_stat_progress_basebackup, WAL archival with pg_stat_archiver, and standby or replication state with pg_stat_replication.

  • Restore drills: Regularly restore both backup types into isolated environments and run schema, row-count, extension and application-level integrity checks.

  • Operational trade-offs: Logical restores take longer because indexes are rebuilt and statistics are absent until ANALYZE runs. Physical restores generally recover faster but may require downtime, standby promotion or more careful cluster-wide configuration management.

Security and Selection

Both backup types can contain sensitive application data. Physical backups additionally contain cluster-wide configuration and authentication verifiers such as SCRAM secrets, as well as data from every database in the cluster. Restrict filesystem access, monitor retention and deletion, and encrypt archives both at rest and in transit.

Use pg_dump for selective exports, test fixtures and migrations to a later major version, but test restoration because extensions, collations, large objects and unsupported features can cause failures. Use physical backups with archived WAL for production disaster recovery, high availability and minimum recovery time. Logical restores rebuild indexes and avoid copying existing table bloat, whereas physical backups preserve the captured storage layout, including any existing bloat or corruption.

60

How does PostgreSQL point-in-time recovery work?

Point-in-time recovery (PITR) restores a PostgreSQL cluster to a selected point in its transaction history by combining a physical base backup with an unbroken sequence of archived write-ahead log (WAL) segments. The restored copy replays WAL until the configured recovery target is reached and can then be promoted as the new primary.

Read full answer

Applies toPostgreSQL 16 and 17

How It Works

  • Base backup: A tool such as pg_basebackup creates a physical copy of the cluster and records the backup’s start and end WAL positions. WAL from the checkpoint before backup start through the backup end is required to make the copy consistent.

  • WAL archival: The primary’s archive_command copies each completed WAL segment to durable, independent storage. Archives must retain every segment needed from the backup through the intended recovery target.

  • Restore and redo: On the recovery host, recovery.signal enables archive recovery and restore_command retrieves each required WAL segment. PostgreSQL performs physical redo from the last checkpoint.

  • Recovery target: recovery_target_time, recovery_target_xid, recovery_target_name, and recovery_target_lsn select different boundaries. For time, XID, and named targets, recovery_target_inclusive defaults to on, meaning recovery stops just after rather than just before the target.

  • Transaction visibility: Redo is physical, so uncommitted changes may exist in restored pages but remain invisible according to transaction status. A time target effectively selects the appropriate transaction commit boundary.

  • Promotion: With recovery_target_action set to promote, recovery ends at the target and the server becomes writable on a new timeline. Writes made after promotion are not part of the restored history.

Production Architecture

  • Store base backups and WAL archives off-host in durable, access-controlled storage with retention that covers the required recovery window.

  • Archive WAL continuously. Since only completed segments are archived normally, switch WAL when a low-traffic system needs the current segment archived promptly.

  • Pin recovery_target_timeline to the intended branch in multi-timeline archives; its default of latest may otherwise select another archived timeline.

  • Use tools such as pgBackRest, Barman, or WAL-G for managed backup, compression, encryption, retention, and restore workflows.

  • Treat streaming replication as an availability mechanism, not a replacement for backups. A standby can perform PITR if it has the necessary historical WAL.

  • Provision enough disk for the restored data, replayed WAL, indexes, and temporary files. PITR is bounded by both the recovery-point objective and the time required to restore and replay the archive.

Validation and Monitoring

  • Inspect recovery logs for consistent recovery state, the requested target, and the selected new timeline. After promotion, confirm pg_is_in_recovery() returns false, then validate business invariants and expected rows.

  • Monitor the source with pg_stat_archiver, especially last_archived_wal, last_archived_time, last_failed_wal, and last_failed_time. failed_count is cumulative, so compare it over time rather than treating any historical failure as current.

  • Monitor archive-storage capacity, backup age, replica lag, and replay progress with pg_last_wal_replay_lsn().

  • Perform scheduled restore drills. A backup is not proven recoverable until an isolated environment has restored it, started PostgreSQL, reached the required consistency point, and passed application-level validation.

  • Cumulative statistics normally survive a clean restart, though unsaved statistics can be lost after a crash. Preserve trends in external monitoring rather than assuming every restart resets them.

Failure Modes

  • Missing WAL: If an archive segment required before the target is unavailable, recovery ends and the target is not reached; PostgreSQL must not be treated as successfully recovered. Missing WAL after an already reached target does not invalidate that target.

  • Future target: Recovery consumes available WAL and then ends when the next required segment cannot be retrieved. It does not continuously poll for future WAL; an unreached recovery target produces a failure.

  • Archive failure: A nonzero exit from archive_command causes PostgreSQL to retry the archive operation. Alert on sustained failures and verify that archive storage remains available.

  • Corruption: WAL records always carry CRCs, so damaged WAL is detected during replay; data-page checksums (enabled at initdb) detect some heap and index corruption. Neither can compensate for a damaged base backup, so retain redundant copies.

  • Capacity exhaustion: Replay can generate substantial I/O and temporary-file usage. Monitor filesystem capacity and recovery duration.

  • Post-recovery bloat: An old restored database may need aggressive vacuuming and index work. Avoid VACUUM FULL on production systems because of its locking and disk requirements; use normal vacuuming or a controlled online reorganization tool.

  • Transaction ID age: An old target can have a high age(datfrozenxid). Monitor pg_database.datfrozenxid and ensure wraparound-prevention autovacuum remains enabled.

Security and Operational Practices

  • Encrypt base backups, WAL archives, backup metadata, and replication traffic. WAL can contain sensitive data even when application tables use encryption.

  • Restrict file permissions and access through pg_hba.conf; avoid broad trust authentication outside isolated test environments.

  • Put archival and restore logic in tightly controlled scripts rather than embedding complex credentials or shell logic in PostgreSQL configuration.

  • Ensure the restored server retains a working archive_command after promotion so PostgreSQL archives the new timeline and its timeline history file.

  • Choose the target immediately before the incident when possible. A named restore point or exact WAL location is less ambiguous than a wall-clock time, although it may require more WAL replay.

  • Document the target, expected data, validation queries, recovery owner, and promotion procedure, and rehearse those steps before an incident.

61

How do pgBackRest and Barman differ for PostgreSQL recovery?

pgBackRest and Barman both support physical base backups with WAL-based crash recovery and point-in-time recovery, but they use different control planes. pgBackRest is repository-centric and runs backup and restore operations from PostgreSQL hosts, while Barman provides a centralized server for managing backups, WAL archives, standbys, and failover.

Read full answer

Applies toPostgreSQL 16/17 with current pgBackRest 2.x and Barman 3.x releases

Recovery Model

Both tools rely on the same PostgreSQL recovery mechanisms:

  • Base backup: A physical copy of the PostgreSQL data directory can be taken while the server is running. WAL and checkpoint information make the backup recoverable to a consistent state.

  • WAL archiving: Completed WAL segments are retained outside pg_wal for crash recovery, PITR, standby operation, or both.

  • Point-in-time recovery: PostgreSQL restores a base backup, obtains archived WAL through restore_command, and replays it to an explicitly selected target such as recovery_target_time, recovery_target_xid, or recovery_target_lsn.

  • Final consistency: PostgreSQL performs crash recovery and applies full-page writes as needed before reaching the requested target.

The operational difference is where this orchestration occurs. PgBackRest pulls backups and WAL directly from a configured repository. Barman coordinates the transfer from its server to a recovery host and configures that host to retrieve WAL from Barman during replay.

Core Differences

Area pgBackRest Barman
Control plane Commands generally run on the PostgreSQL host or another repository-accessible host A central Barman server manages one or more PostgreSQL clients
Backup model Full, differential, and incremental backups Full and incremental backups
WAL ingestion archive-push writes WAL to the repository; archive-get retrieves it during recovery Uses archive_command, SSH transfer, or WAL streaming through pg_receivewal
Restore workflow pgbackrest restore populates the target data directory from the repository; the target cluster must be stopped barman recover-backup transfers the backup to a recovery host and configures PostgreSQL recovery or standby replay
PITR Supports time, transaction ID, LSN, and immediate-style targets Supports equivalent PostgreSQL recovery targets through recovery configuration and CLI options
Standby management Primarily a backup, archive, and restore engine Provides stronger standby monitoring, promotion, and failover workflows
Storage Repository abstraction supporting local, SFTP, and supported cloud backends Barman-managed local or cloud storage, depending on the installed release and provider

Neither tool replaces streaming replication. Replication provides availability and read capacity; backups provide disaster recovery and protection against logical corruption, accidental deletion, and some forms of ransomware damage.

Production and Failure Modes

  • Archive failure: PostgreSQL retains WAL that has not been archived successfully, but prolonged failure can fill pg_wal and stop writes. Monitor archive failures and the age of the last successfully archived segment.

  • Missing WAL: PITR becomes impossible if a required segment was never archived or was removed by retention. PgBackRest recovery requires the repository to remain reachable for missing WAL; Barman recovery requires continued access to the Barman server until replay is complete.

  • Repository or server failure: A successful backup is not proof that the backup is restorable. Monitor storage capacity, transfer failures, archive continuity, retention, and repository or object-store integrity independently.

  • Version compatibility: Physical backups should be restored to the same PostgreSQL major version. Required extensions, locale settings, and compatible configuration must also be present; cross-major migration normally uses logical tools.

  • Target selection: Record the intended recovery target with an explicit time zone, transaction ID, or LSN. A missing or ambiguous target can result in replay stopping at the wrong point.

Monitoring and Validation

  • Monitor pg_stat_archiver, especially failed_count, last_failed_wal, last_failed_time, and last_archived_wal, and alert on increasing failures rather than only on a cumulative counter.

  • Use pg_stat_replication to monitor standby state and byte or time lag when streaming replication is present.

  • Use pg_stat_all_tables to track dead tuples, analyze activity, and vacuum progress. Query age(datfrozenxid) for high-write tables to detect transaction ID wraparound risk.

  • Track repository capacity, backup age, archive continuity, and failed restore tests outside PostgreSQL’s statistics.

  • Perform scheduled physical restores into an isolated PostgreSQL instance. Validate row counts, extension availability, recovery-target accuracy, and application-level checks rather than relying only on a successful restore command.

Recovery Selection and Practices

Choose pgBackRest when repository-centric performance, direct repository access, and straightforward backup automation are priorities. Choose Barman when centralized management of multiple clusters, standby supervision, and assisted failover are more important. Both remain appropriate in large environments and can coexist if their responsibilities and retention are clearly separated.

Restrict SSH keys, repository credentials, backup directories, and encryption keys independently. Use encrypted repositories where supported, separate keys from the protected data, and apply immutable or write-protected retention where available. Keep base backups and WAL for at least the maximum tolerated recovery window, and rehearse whole-cluster recovery, PITR, extension compatibility, and failover procedures before relying on them during an incident.

62

Why use PgBouncer to pool PostgreSQL connections?

PgBouncer is a lightweight connection-pooling proxy that lets many application connections share a bounded set of PostgreSQL backend connections. It protects PostgreSQL’s connection capacity during bursts and high-fan-out workloads, but it does not make individual queries faster or resolve locking and slow-query problems.

Read full answer

Applies toPgBouncer 1.24.x with PostgreSQL 16/17

Why PostgreSQL Needs Pooling

PostgreSQL uses an operating-system process for each accepted connection. Every backend therefore consumes a connection slot and contributes to memory and file-descriptor pressure, even when it is idle. Connection storms from microservices, serverless functions, or horizontally scaled applications can exhaust max_connections before useful work arrives.

PgBouncer changes the topology:

  • Applications open client connections to PgBouncer, up to its max_client_conn setting.

  • PgBouncer maintains a smaller set of PostgreSQL server connections.

  • In transaction pooling mode, a backend is assigned for the duration of each transaction and then returned to the pool.

  • Active queries still require PostgreSQL backends, so pooling primarily controls connection overhead and bursts; it does not reduce the CPU or memory required by an individual query.

PgBouncer does not alter PostgreSQL’s MVCC, WAL, query planner, indexes, or shared buffers, and it is not a query cache.

How Pooling Modes Work

Pool mode PostgreSQL connection lifetime Typical use
session Entire client session Applications requiring session state
transaction One transaction General-purpose OLTP workloads
statement One statement Restricted workloads without multi-statement transactions

Transaction mode normally provides the best balance between utilization and compatibility. It does not support features whose state belongs to the entire client session, including session-level SET, temporary tables, session advisory locks, LISTEN/NOTIFY, and SQL-level PREPARE.

SET LOCAL is safe when used inside a transaction because its value is reset at transaction end. For protocol-level named prepared statements, PgBouncer 1.24.x can track and rewrite them when max_prepared_statements is enabled. SQL-level prepared statements still require session pooling or another application-level workaround.

Production Operations and Failure Modes

  • Sizing: Pools are normally keyed by database and PostgreSQL user, so default_pool_size applies per database/user pool. The aggregate number of PgBouncer server connections across all pools must remain below PostgreSQL’s max_connections, with capacity reserved for administration, monitoring, replication, and maintenance.

  • Pool explosion: Large numbers of databases or users can create many pools and multiply backend usage. max_db_connections, max_user_connections, and consistent application credentials can bound this risk.

  • Monitoring: PgBouncer’s administrative console provides SHOW POOLS, SHOW STATS, SHOW CLIENTS, and SHOW SERVERS. Persistent cl_waiting or high maxwait indicates pool contention, long transactions, or insufficient capacity.

  • Database visibility: PostgreSQL monitoring should include pg_stat_activity, lock waits, idle transactions, replication lag, and pg_stat_statements where the extension is installed. pg_stat_activity also exposes backend_xmin, which helps identify transactions preventing vacuum cleanup.

  • Long transactions: Use statement_timeout, lock_timeout, and idle_in_transaction_session_timeout as appropriate, plus an application-level limit for total transaction duration. Long transactions retain old row versions, increase bloat, and can delay freezing, contributing to transaction-ID wraparound pressure.

  • Security: Use TLS between clients and PgBouncer and between PgBouncer and PostgreSQL. SCRAM authentication is supported: PgBouncer can check clients against SCRAM verifiers in its auth file or via auth_query, and it can reuse the client’s SCRAM keys to log in to PostgreSQL, so plaintext passwords are not required. Restrict ownership and permissions on the authentication file and the PgBouncer listener.

  • Failover: When PostgreSQL fails, in-flight transactions fail and clients should retry only when the operation is safe to repeat. Clients normally remain connected to PgBouncer, which reconnects to an available PostgreSQL endpoint when possible. A PgBouncer restart or listener failure requires clients to reconnect. PgBouncer does not provide PostgreSQL replication or automatic database failover.

Best Practices

  • Prefer transaction pooling for stateless OLTP services; use session pooling when application correctness depends on session state.

  • Size pools from measured concurrency and transaction duration rather than merely the number of application instances.

  • Keep transactions short, including time spent waiting while holding a pooled backend.

  • Monitor both PgBouncer queueing and PostgreSQL-side wait events so connection shortages are not confused with lock contention or slow queries.

  • When running multiple PgBouncer instances behind a load balancer, remember that each instance maintains independent pools and can collectively exceed the intended PostgreSQL connection budget.

  • Test pool exhaustion, slow transactions, PostgreSQL failover, PgBouncer restarts, upgrades, and client reconnection procedures before production rollout.

63

How does PgBouncer differ between session, transaction, and statement pooling?

Pooling mode determines when PgBouncer assigns a PostgreSQL backend connection to a client. Session pooling keeps one backend for the whole client connection, transaction pooling keeps it for one transaction, and statement pooling keeps it only for one SQL statement.

Read full answer

Applies toPgBouncer 1.24.x with PostgreSQL 16 and 17

How It Works

PgBouncer multiplexes client connections over a smaller set of PostgreSQL backend connections.

Mode Backend assignment Appropriate use Main limitation
session One backend from client connection until disconnect Applications requiring session state Every active client can occupy a backend
transaction One backend for each transaction; an explicit transaction lasts from BEGIN through COMMIT or ROLLBACK, while an autocommit statement is its own transaction Stateless web applications and microservices Session-scoped features are not reliably preserved
statement One backend for each SQL statement Specialized autocommit-only workloads Transactions and session state are unsupported

Statement pooling is a niche mode and should not be selected for normal applications. It does not support multi-statement transactions.

Transaction pooling changes the safety rules for session-scoped features:

  • Session-level SET, LISTEN/NOTIFY, session advisory locks, persistent cursors, and temporary tables cannot be relied upon across transactions. Prefer SET LOCAL or transaction-level advisory locks when the application permits them.

  • Protocol-level named prepared statements work in transaction pooling when max_prepared_statements is nonzero. SQL-level PREPARE and EXECUTE remain tied to a backend and are unsuitable for transaction pooling.

  • A pool is normally keyed by database and user. Consequently, the default default_pool_size = 20 allows up to 20 backends per database/user pool, not 20 globally.

Operational Consequences

  • Session-pool saturation: An idle client still owns a backend, so many idle sessions can consume PostgreSQL’s max_connections allowance.

  • Transaction-pool queues: A long-running transaction holds its backend while other clients wait for a server slot. PgBouncer exposes this through waiting-client and wait-time metrics.

  • Lock contention: More transactions can execute concurrently on a smaller backend set, increasing row-lock waits and the possibility of deadlocks.

  • MVCC and bloat: A long or idle transaction can retain locks and, depending on its isolation level and activity, an old MVCC snapshot. This can prevent vacuum cleanup and contribute to table or index bloat.

  • Failover exposure: An active transaction can fail during PostgreSQL failover or network disruption. Retry only operations that are idempotent or protected by an appropriate application-level retry policy.

Monitoring and Recovery

Use PgBouncer’s administrative SHOW POOLS command to inspect client and server counts, including waiting clients. SHOW STATS exposes aggregate wait-time information, while SHOW CLIENTS and SHOW SERVERS show current connection and state details.

Monitor PostgreSQL with pg_stat_activity for long-running or blocked sessions and pg_stat_database for connection-state totals. When the pg_stat_statements extension is installed, use it to identify expensive queries and workload changes. If a problematic PostgreSQL backend must be terminated to release locks, expect its active transaction and associated PgBouncer client operation to fail.

Best Practices

  • Prefer transaction pooling for stateless services, and test every session-state dependency before changing modes.

  • Use application-level query deadlines together with PostgreSQL settings such as statement_timeout and idle_in_transaction_session_timeout to limit backend pinning.

  • Size pools against PostgreSQL CPU, memory, max_connections, expected concurrency, and administrative or failover headroom. PgBouncer 1.24.x also supports database- and user-level connection limits.

  • In production, use strong authentication, TLS with certificate verification, restricted network access, and protected credential files. Store SCRAM (scram-sha-256) verifiers rather than plaintext passwords, and inject credentials through a secret-management mechanism rather than storing them in configuration.

  • PgBouncer does not replace PostgreSQL backups. Back up PostgreSQL directly with a solution such as pgBackRest or WAL archiving, and regularly test physical restores and point-in-time recovery.

  • For upgrades requiring uninterrupted service, run multiple PgBouncer instances behind a load balancer, drain old instances, and verify health checks and connection draining before replacement.

65

How does pg_stat_statements reveal slow PostgreSQL queries?

pg_stat_statements is a PostgreSQL extension that aggregates cumulative execution statistics by normalized SQL statement. It reveals slow statements by comparing call counts and execution times with block and temporary-I/O counters; EXPLAIN ANALYZE is then used to determine why a selected statement is slow.

Read full answer

Applies toPostgreSQL 16/17 with the matching bundled `pg_stat_statements` contrib package

How It Works

  • The server loads the extension through shared_preload_libraries; CREATE EXTENSION then creates its database objects.

  • It records calls, execution and planning times, returned rows, shared-buffer and temporary-block activity, block I/O times, and WAL statistics.

  • Entries are distinguished by database, user, top-level flag, and queryid, a hash derived from the normalized parse tree. Equivalent statements with different literal values commonly appear with placeholders such as $1.

  • pg_stat_statements.track defaults to top. Setting it to all also tracks nested statements.

  • pg_stat_statements.track_planning = on enables the separate planning-time counters.

  • Statistics occupy shared memory. pg_stat_statements.max limits the number of entries; when full, the least-executed entries are deallocated.

  • Values are cumulative since the extension was loaded or the statistics were reset.

Reading the Results

Metric Meaning Operational interpretation
mean_exec_time Average execution time per call Finds consistently slow statements, not necessarily one rare outlier
max_exec_time Slowest execution observed in the retained statistics Exposes occasional severe latency
total_exec_time Cumulative execution time Finds expensive high-frequency workload
total_plan_time Cumulative planning time Requires track_planning = on
shared_blks_read Shared blocks not found in PostgreSQL’s buffer cache May still be served by the operating-system cache
temp_blks_read / temp_blks_written Temporary-file block activity Writes commonly accompany sorting or hashing that exceeded work_mem

Code Example

For a new Debian or Ubuntu installation using the PGDG repository, install the matching package and check the current preload list first:

sudo apt-get install postgresql-contrib-16
sudo -u postgres psql -d postgres \
  -c "SHOW shared_preload_libraries;"

# Use this value only if the previous command returned no entries.
sudo -u postgres psql -d postgres \
  -c "ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';" \
  -c "ALTER SYSTEM SET pg_stat_statements.track_planning = 'on';"

sudo systemctl restart postgresql
sudo -u postgres psql -d postgres \
  -c "CREATE EXTENSION IF NOT EXISTS pg_stat_statements;"

For PostgreSQL 17, use postgresql-contrib-17. If SHOW returns existing libraries, preserve them by setting shared_preload_libraries to a comma-separated list containing both the existing names and pg_stat_statements.

Rank statements by cumulative execution time in the current database:

SELECT
    queryid,
    calls,
    round(mean_exec_time::numeric, 2) AS mean_ms,
    round(max_exec_time::numeric, 2) AS max_ms,
    round(total_exec_time::numeric, 2) AS total_ms,
    round(total_plan_time::numeric, 2) AS total_plan_ms,
    rows,
    shared_blks_hit,
    shared_blks_read,
    temp_blks_read,
    temp_blks_written,
    left(query, 160) AS query
FROM pg_stat_statements
WHERE dbid = (
    SELECT oid
    FROM pg_database
    WHERE datname = current_database()
)
ORDER BY total_exec_time DESC
LIMIT 20;

Representative result rows might look like:

calls mean_ms max_ms total_ms shared_blks_read rows
12 381.2 412.0 4574.4 18240 1200
840 4.8 19.2 4032.0 920 8400

Use the normalized statement with representative bind values to obtain a plan. This complete example runs in one PostgreSQL 16 session:

CREATE TEMP TABLE orders_demo (
    customer_id integer NOT NULL,
    amount numeric(12, 2) NOT NULL
);

INSERT INTO orders_demo (customer_id, amount)
SELECT id,
       CASE WHEN id % 10 = 0 THEN 50.00 ELSE 150.00 END
FROM generate_series(1, 10) AS s(id);

ANALYZE orders_demo;
SET statement_timeout = '5s';

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT customer_id, sum(amount) AS total
FROM orders_demo
WHERE amount > 100
GROUP BY customer_id
ORDER BY customer_id;

A shortened plan can resemble:

Sort (actual time=0.10..0.11 rows=9 loops=1)
  Sort Key: orders_demo.customer_id
  -> HashAggregate (actual time=0.08..0.09 rows=9 loops=1)
       -> Seq Scan on orders_demo (actual time=0.01..0.04 rows=10 loops=1)
             Filter: (amount > '100'::numeric)
             Rows Removed by Filter: 1

Production Practices

  • EXPLAIN ANALYZE executes the statement. Do not use it casually on production writes; use read-only data, staging, or a controlled transaction, and retain an appropriate statement_timeout.

  • pg_stat_statements does not expose plans or separate lock-wait and wait-event measurements. Correlate it with pg_stat_activity and pg_locks for sessions and blockers, and with pg_stat_io for detailed I/O counters.

  • Use pg_stat_user_tables for scan counts, dead tuples, and vacuum or analyze timestamps. Use relation-size functions or pg_class for size, and age(datfrozenxid) for transaction-ID age. pg_stat_database provides commit, rollback, and deadlock counters.

  • Use log_min_duration_statement to identify duration outliers and auto_explain when production execution plans are required.

  • Snapshot or reset statistics for a controlled investigation window with SELECT pg_stat_statements_reset();. Compare rankings and counters afterward rather than relying on one absolute duration.

  • Monitor pg_stat_statements_info.dealloc and pg_stat_statements.max; raising the limit increases shared-memory consumption.

  • Treat query text as potentially sensitive. Use pg_monitor or another narrowly scoped monitoring role and restrict raw query-text access separately.

  • Normalization can combine parameter values that produce different plans. Inspect bind values and representative plans, then compare execution time, block reads, and temporary usage after tuning.

66

Which pg_stat views are most important in PostgreSQL?

The most important PostgreSQL monitoring views are pg_stat_activity, pg_stat_database, pg_stat_user_tables, pg_stat_io, and the replication and progress views. Together they expose connections, query execution, table activity, I/O, WAL generation, replication health, vacuum progress, and lock contention.

Read full answer

Applies toPostgreSQL 16 and PostgreSQL 17

Core Monitoring Views

View What it monitors Important fields or signals
pg_stat_activity Current sessions and server activity pid, usename, application_name, client_addr, state, wait_event_type, wait_event, query, xact_start, query_start
pg_stat_database Cumulative database activity and session lifecycle Transactions, tuple operations, block I/O, temporary files, conflicts, deadlocks, cumulative session and active/idle time
pg_stat_user_tables User-table access and maintenance seq_scan, idx_scan, row changes, n_live_tup, n_dead_tup, n_mod_since_analyze, vacuum/analyze timestamps
pg_statio_user_tables Block-level I/O for user tables Heap, index, and TOAST block reads and shared-buffer hits
pg_stat_user_indexes Index usage idx_scan, idx_tup_read, idx_tup_fetch; obtain size separately with pg_relation_size()
pg_stat_io Aggregated I/O by backend type, object, and context Reads, writes, extends, writebacks, cache hits, evictions, fsyncs, and timing totals

pg_stat_user_tables.n_live_tup and n_dead_tup are estimates, not exact row counts. Use them to identify vacuum or autovacuum pressure, but confirm bloat with tools such as pgstattuple before taking action.

Connections, Locks, and Progress

pg_stat_activity is a point-in-time snapshot, not a historical query log. It is the primary view for finding:

  • Long-running or idle-in-transaction sessions.

  • Sessions blocked by locks.

  • Waits categorized as Lock, IO, Client, or LWLock.

  • Unexpected clients, databases, roles, or application names.

Use pg_locks with pg_stat_activity to identify blockers and waiters. A waiting session’s pg_locks row generally does not show the blocking PID directly, so inspect granted versus ungranted locks or use pg_blocking_pids().

Progress views show operations that are still running:

  • pg_stat_progress_vacuum: vacuum phase, blocks scanned, blocks vacuumed, dead tuples, and index-vacuum progress.

  • pg_stat_progress_create_index: index-build phase and completed work.

  • pg_stat_progress_cluster: CLUSTER and VACUUM FULL progress.

  • pg_stat_progress_basebackup: progress of a running base backup.

  • pg_stat_progress_copy: progress of a running COPY operation (PostgreSQL 14 and later).

These views are empty when the corresponding operation is not running.

WAL, Replication, and Recovery

View Purpose
pg_stat_wal WAL records and bytes generated, WAL buffer-full events, and write/sync timing totals
pg_stat_replication Connected standbys, their LSN positions, synchronous state, and write, flush, and replay lag
pg_stat_wal_receiver WAL receiver status on a standby, including receive-start, written, and flushed LSNs; use pg_last_wal_replay_lsn() for replay position
pg_stat_subscription Logical replication worker activity and LSN or timestamp progress
pg_stat_subscription_stats Apply and initial-sync error counts per subscription (PostgreSQL 15 and later)
pg_stat_archiver Archived and failed WAL counts and the last archived or failed WAL file when WAL archiving is enabled
pg_stat_recovery_prefetch Recovery-prefetch effectiveness on replicas
pg_stat_database_conflicts Standby recovery conflicts by reason, including snapshot, vacuum, lock, and buffer-pin conflicts

For replication, compare sender and receiver LSNs rather than relying only on lag columns, which may be NULL until enough activity has occurred. Monitor WAL growth alongside replica capacity because WAL generation can exceed standby consumption and cause retention or archiving pressure.

Checkpoints and Background Writing

PostgreSQL 16 exposes checkpoint and background-writer counters through pg_stat_bgwriter. PostgreSQL 17 separates checkpoint statistics into pg_stat_checkpointer, while pg_stat_bgwriter focuses on background writing and buffer allocation.

The relevant fields differ by version:

  • PostgreSQL 16: pg_stat_bgwriter.checkpoints_timed, checkpoints_req, checkpoint_write_time, and checkpoint_sync_time.

  • PostgreSQL 17: pg_stat_checkpointer.num_timed, num_requested, write_time, and sync_time.

Both versions expose cleaned buffers and buffer-allocation counters in pg_stat_bgwriter; PostgreSQL 17 removed the backend-written and backend-fsync counters, which are now in pg_stat_io. Monitor wal_buffers_full in pg_stat_wal. Frequent requested checkpoints can indicate that WAL generation is outpacing checkpoint pacing or that max_wal_size needs tuning.

Interpreting and Maintaining the Statistics

Most cumulative counters increase until statistics are reset. Production monitoring should calculate rates or deltas and detect resets using stats_reset where exposed, including views such as pg_stat_database, pg_stat_io, pg_stat_wal, pg_stat_bgwriter, and pg_stat_statements_info; not every view has this column. Avoid resetting statistics during an incident unless there is a documented reason, because doing so destroys the investigation baseline.

pg_stat_statements is an extension rather than a built-in view. It is one of the best sources for normalized-query execution counts, total_exec_time, and rows processed. It requires the extension to be installed and shared_preload_libraries = 'pg_stat_statements' to be configured before server startup.

Treat query text as sensitive. PostgreSQL restricts visibility of other users’ query text according to privileges; monitoring systems commonly use pg_read_all_stats. Restrict that privilege because query text, application names, client addresses, and pg_stat_statements entries may expose customer data or operational details.

67

How do you tune PostgreSQL 16/17 for write-heavy workloads?

Tuning a write-heavy PostgreSQL workload means reducing unnecessary WAL, index, checkpoint, and per-row work while preserving durability. The highest-impact changes usually come from batching transactions, controlling indexes and autovacuum, sizing memory and WAL correctly, and measuring contention rather than guessing.

Read full answer

Applies toPostgreSQL 16 and PostgreSQL 17

How Writes Flow Through PostgreSQL

  • Each connection has a backend process using shared buffers, work_mem, and OS memory outside shared buffers.

  • Changes are recorded in WAL. With synchronous_commit = on, PostgreSQL waits for the commit record to reach durable storage before acknowledging the transaction.

  • Checkpoints write dirty shared-buffer pages to data files. max_wal_size is a soft target, while checkpoint_timeout limits checkpoint frequency.

  • MVCC updates create new row versions. The old versions remain until VACUUM can reclaim them, which is why long transactions can cause bloat and transaction ID wraparound.

Baseline Configuration

For a dedicated 16 GiB server with SSD storage, a reasonable starting point is:

# postgresql.conf
shared_buffers = '4GB'
effective_cache_size = '12GB'
max_connections = 200
work_mem = '8MB'
maintenance_work_mem = '512MB'
max_wal_size = '8GB'
checkpoint_timeout = '15min'
checkpoint_completion_target = 0.9
synchronous_commit = 'on'
wal_level = 'replica'
  • Treat these as test values, not universal defaults.

  • shared_buffers and max_connections require a restart; most other settings can be reloaded.

  • effective_cache_size is a planner estimate, not allocated memory.

  • work_mem applies per sort or hash operation and can be consumed by several operations per query.

  • Keep synchronous_commit = on unless the application explicitly tolerates losing recent acknowledged transactions after a crash.

On Debian/Ubuntu, after editing the configuration:

sudo systemctl restart postgresql

Indexes, Transactions, and Table Design

  • Every index increases write amplification. Retain only indexes justified by measured query requirements.

  • Use B-tree for equality, range, ordering, and uniqueness; BRIN for large append-only tables whose values correlate with physical order.

  • GIN is useful for arrays, JSON searches, and full-text search, but updates can be expensive. GiST suits ranges and certain spatial or geometric data.

  • Batch many small writes into moderate transactions. This reduces commit records and synchronous flushes, but avoid huge transactions that retain locks and prevent VACUUM from removing dead rows.

  • COPY is faster than individual INSERT statements, but normal COPY still generates WAL.

  • Use UNLOGGED tables only for rebuildable staging data; PostgreSQL truncates them after a crash.

  • Choose the smallest accurate types, such as integer, bigint, timestamptz, and numeric where exact decimals are required.

  • Partitioning mainly helps pruning, retention, and maintenance. It does not automatically improve insertion throughput.

Diagnose Bottlenecks

Use EXPLAIN (ANALYZE, BUFFERS, WAL) on representative write statements. Look for excessive rows, repeated scans, temporary files, WAL volume, and long execution times. EXPLAIN ANALYZE executes the statement, so run it safely.

Monitor vacuum activity and waits:

SELECT relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

SELECT wait_event_type, wait_event, count(*)
FROM pg_stat_activity
WHERE state = 'active'
GROUP BY wait_event_type, wait_event
ORDER BY count(*) DESC;

Common waits include WALWrite, WALSync, DataFileWrite, and Lock waits. Also track commit latency, WAL bytes per second, replication lag, checkpoint duration, disk space, dead tuples, and lock waits.

Code Example

CREATE TABLE write_demo (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    tenant_id integer NOT NULL,
    body text NOT NULL,
    updated_at timestamptz NOT NULL DEFAULT now()
);

INSERT INTO write_demo (tenant_id, body)
VALUES (42, 'first');

SELECT id, tenant_id, body FROM write_demo;

CREATE INDEX write_demo_tenant_idx
    ON write_demo (tenant_id);

ALTER TABLE write_demo SET (
    autovacuum_vacuum_scale_factor = 0.05,
    autovacuum_vacuum_threshold = 1000
);

ANALYZE write_demo;

EXPLAIN (ANALYZE, BUFFERS, WAL)
UPDATE write_demo
SET tenant_id = 43, body = 'second'
WHERE id = 1;

SELECT id, tenant_id, body FROM write_demo;

The initial SELECT returns one row with tenant 42; the update returns UPDATE 1, and the final row contains tenant 43. The shortened plan is:

Update on public.write_demo ...
  -> Index Scan using write_demo_pkey on write_demo
       Index Cond: (id = 1)
Planning Time: ...
Execution Time: ...

Production Practices and Failure Modes

  • Put PgBouncer or an application pool in front of PostgreSQL; oversized connection counts multiply memory use and contention.

  • Tune autovacuum more aggressively for high-churn tables, but monitor worker saturation and I/O.

  • Investigate long transactions early: they block vacuum cleanup and can contribute to transaction ID wraparound.

  • A physical replica offloads reads but still receives and applies WAL. Synchronous replication increases write acknowledgment latency and can reduce write availability if the standby is unavailable.

  • Replication slots and WAL archiving can retain WAL indefinitely; alert on slot size, archive failures, and disk utilization.

  • Automate base backups plus WAL archiving for PITR, and regularly test restores.

  • Enable useful extensions such as pg_stat_statements, but review their overhead and security implications.

  • Require TLS, SCRAM authentication, restricted pg_hba.conf rules, and least-privilege application roles.

  • Change one parameter at a time under production-like load, then validate using latency, WAL rate, bloat, and contention metrics.

68

How do you tune PostgreSQL 16/17 for read-heavy workloads?

Tune PostgreSQL for reads by keeping hot data in memory, using indexes that match actual queries, and distributing reads across replicas through a connection pooler. Read-heavy tuning is an end-to-end latency and I/O exercise, not a matter of applying universal configuration values.

Read full answer

Applies toPostgreSQL 16 and 17

Read Path and Index Strategy

PostgreSQL backends read pages from shared buffers; the operating system’s page cache can serve pages not currently in shared memory. Cache locality, storage latency and the number of concurrently active queries therefore matter more than connection count alone.

Choose indexes from observed query patterns:

  • B-tree: Equality, range, ordering and prefix searches. It is the default for most OLTP and read-heavy workloads.

  • GIN: Arrays, full-text search and JSON containment.

  • GiST: Geometric data, ranges and exclusion constraints.

  • BRIN: Very large, physically correlated tables such as append-only event or time-series data.

  • Partial indexes: Restrict an index to rows matching a stable predicate, such as active records.

  • Covering indexes: Use INCLUDE to store additional columns needed by the query, enabling index-only scans.

Use EXPLAIN (ANALYZE, BUFFERS) to inspect estimated versus actual rows, execution time, heap fetches and buffer hits. ANALYZE executes the statement, so use it carefully on production systems.

Core Configuration

The following values are an illustrative starting point for a dedicated server with 32 GB RAM, not universal defaults.

Setting Starting point Operational consequence
shared_buffers 8GB PostgreSQL’s shared buffer cache; normally start near 25% of RAM on a dedicated server. Changes require restart.
effective_cache_size 24GB Planner estimate of available OS cache; it allocates no memory.
work_mem 16MB Memory per sort or hash node, not per entire query. Multiple nodes or parallel workers can multiply consumption.
maintenance_work_mem 1GB Speeds index creation and vacuum when enough memory is available.
max_connections 200 Must be sized around pooler capacity and backend memory usage. Changes require restart.
random_page_cost 1.1 Reasonable for SSD-oriented storage; validate with real query plans rather than changing it blindly.

Apply restart-required changes through ALTER SYSTEM, then reload and restart appropriately:

ALTER SYSTEM SET shared_buffers = '8GB';
ALTER SYSTEM SET effective_cache_size = '24GB';
ALTER SYSTEM SET work_mem = '16MB';
ALTER SYSTEM SET maintenance_work_mem = '1GB';
ALTER SYSTEM SET max_connections = '200';
ALTER SYSTEM SET random_page_cost = '1.1';
SELECT pg_reload_conf();

On Ubuntu or Debian with a versioned cluster service, restart the affected cluster:

sudo systemctl restart postgresql@16-main

Use postgresql@17-main for a PostgreSQL 17 cluster. Confirm the effective values with SHOW shared_buffers; and related SHOW commands.

Example Query and Plan

CREATE TABLE products (
    id bigint PRIMARY KEY,
    sku text NOT NULL,
    name text NOT NULL,
    stock integer NOT NULL
);

INSERT INTO products (id, sku, name, stock)
SELECT g, 'SKU-' || g, 'Item ' || g, g % 100
FROM generate_series(1, 10000) AS g;

CREATE UNIQUE INDEX products_sku_idx
    ON products (sku) INCLUDE (id, name, stock);

VACUUM (ANALYZE) products;

SELECT id, sku, name, stock
FROM products
WHERE sku = 'SKU-4242';

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, sku, name, stock
FROM products
WHERE sku = 'SKU-4242';

The query returns one result row:

id sku name stock
4242 SKU-4242 Item 4242 42

A typical shortened plan is:

Index Only Scan using products_sku_idx on products
  Index Cond: (sku = 'SKU-4242')
  Heap Fetches: 0

The exact buffer counts and costs vary by PostgreSQL version and statistics. Frequent heap fetches can indicate that the visibility map is not sufficiently maintained or that the query needs columns outside the index.

Pooling, Replicas and Monitoring

  • Use PgBouncer or a cloud-native pooler to reuse backend connections. Transaction pooling is effective for stateless requests, but session-level settings and temporary tables can cause compatibility problems.

  • Route independent reads to replicas to remove read load from the primary. Document consistency requirements because asynchronous replicas can return stale data; long transactions can also delay WAL replay.

  • Enable pg_stat_statements through shared_preload_libraries and rank queries by calls, mean_exec_time, shared_blks_hit and shared_blks_read.

  • Monitor pg_stat_user_tables for seq_scan, idx_scan and estimated n_dead_tup.

  • Monitor pg_stat_user_indexes for unused indexes, remembering that counters are cumulative since the last reset.

  • Track pg_stat_activity and pg_locks for blocked queries, long-running transactions and contention.

  • Watch replica lag with pg_stat_replication and application-level freshness checks; lag estimates can be zero even when replay is delayed.

Failure Modes, Security and Operations

  • Cache cold starts: A restart can temporarily increase storage reads and latency. Warm the cache gradually and monitor readiness rather than sending full production traffic immediately.

  • Memory exhaustion: Large concurrent sorts or hashes can consume multiples of work_mem. Increase it only after measuring sorts, spills and total worker demand.

  • Index bloat: Every index adds write and vacuum overhead. Remove demonstrably unused indexes, but investigate zero idx_scan values before acting.

  • MVCC bloat and wraparound: Long-running transactions prevent vacuum from removing obsolete row versions. Monitor age(datfrozenxid) and prevent transaction-ID wraparound.

  • Read-replica lag: Heavy replica queries can conflict with WAL replay. Use separate replica pools for reporting and latency-sensitive traffic where necessary.

  • Security: Require TLS and SCRAM authentication, restrict pg_hba.conf, use least-privilege roles, and prevent application accounts from creating untrusted functions. Replicas contain the same sensitive data as the primary.

  • Backups and upgrades: Replicas are not backups. Maintain WAL archiving with a tested point-in-time recovery procedure, such as pgBackRest or Barman. For major upgrades, plan pg_upgrade or logical replication and verify extension compatibility before cutover.

69

How do you upgrade PostgreSQL 16 to 17 with minimal downtime?

Use a blue-green logical replication upgrade: replicate from a PostgreSQL 16 publisher to a PostgreSQL 17 subscriber while the old server remains active, then briefly stop application writes, synchronize the remaining changes and sequences, and switch the application connection.

Read full answer

Applies toPostgreSQL 16 publisher to PostgreSQL 17 subscriber

Upgrade Strategy

pg_upgrade is usually simpler, but it requires the old cluster to be stopped during the upgrade and switchover. Logical replication provides a smaller write outage, often limited to final synchronization and connection switching.

Logical replication performs an initial table copy and then streams committed changes. It is not a block-level clone: schemas, sequences, large objects, temporary tables and DDL are not automatically replicated.

Preparation

  • Test the upgrade in staging with production-like data and traffic.

  • Take and restore-test backups before starting. Logical replication is not a backup or PITR system.

  • Ensure PostgreSQL 17 has the same extensions, compatible schemas, indexes and table definitions.

  • Give every replicated table a primary key. Tables without one require REPLICA IDENTITY FULL for updates and deletes.

  • Configure TLS, SCRAM authentication, firewall rules and least-privilege roles.

  • Prepare PostgreSQL 17 for backups and PITR before it becomes authoritative.

Replication

Create a replication role on the publisher and grant it read access:

-- Set the password afterwards with \password app_replicator.
CREATE ROLE app_replicator WITH LOGIN REPLICATION;
GRANT USAGE ON SCHEMA app TO app_replicator;
GRANT SELECT ON ALL TABLES IN SCHEMA app TO app_replicator;

CREATE PUBLICATION app_pub
FOR TABLE app.users, app.orders;

Create the subscription on PostgreSQL 17. The publisher password should be supplied through a secret-managed .pgpass file or equivalent mechanism, rather than committed to configuration:

CREATE SUBSCRIPTION app_sub
  CONNECTION 'host=pg16.example.com port=5432 dbname=app
              user=app_replicator sslmode=verify-full'
  PUBLICATION app_pub
  WITH (copy_data = true, create_slot = true, enabled = true);

On the publisher, pg_hba.conf must allow the role to connect to the published database. Logical replication connections are matched by database name (the replication keyword only covers physical replication), so one rule serves both the initial copy and streaming:

hostssl app app_replicator 10.0.0.0/16 scram-sha-256

Reload PostgreSQL after changing pg_hba.conf:

pg_ctl reload -D /var/lib/postgresql/16/main

The publisher creates a logical replication slot. That slot retains WAL until the subscriber confirms receipt, so a failed or lagging subscriber can consume disk space on the publisher.

Cutover

  1. Stop or quiesce writes to the affected PostgreSQL 16 tables.

  2. Wait until the subscriber has received all transactions committed before the cutover.

  3. Capture sequence values from the publisher because sequences are not replicated:

SELECT schemaname, sequencename, last_value
FROM pg_sequences
WHERE schemaname = 'app';
  1. Apply the captured values on PostgreSQL 17. Use the actual sequence names and values, not MAX(column), because deleted rows may have caused sequence values to advance beyond the maximum visible ID:

SELECT setval('app.users_id_seq', 2, true);
SELECT setval('app.orders_id_seq', 2, true);
  1. Verify row counts, recent writes and application behavior on PostgreSQL 17.

  2. Switch the application connection pool or DNS endpoint to PostgreSQL 17.

  3. Keep the old cluster available briefly for investigation. However, if new writes occur on PostgreSQL 17, rolling back requires reverse logical replication or a separate write-reconciliation plan.

After the rollback window, remove the subscription and its publisher slot. Dropping a subscription may remove the remote slot, so verify that the publisher’s pg_replication_slots no longer contains the old slot.

Monitoring and Failure Modes

Monitor the publisher’s slot and the subscriber’s apply worker:

-- PostgreSQL 16 publisher
SELECT slot_name, active, wal_status, confirmed_flush_lsn, safe_wal_size
FROM pg_replication_slots
WHERE slot_name = 'app_sub';

-- PostgreSQL 17 subscriber
SELECT subname, received_lsn, latest_end_lsn, latest_end_time
FROM pg_stat_subscription
WHERE subname = 'app_sub';

Important failure modes include:

  • Replication lag: the initial copy or apply worker falls behind, causing publisher WAL growth.

  • Schema mismatch: DDL changes on the publisher are not replicated and can cause apply errors.

  • Sequence drift: inserts can fail with duplicate keys after cutover if sequences are not synchronized.

  • Missing privileges: the replication or subscription role cannot read tables or connect over TLS.

  • Slot abandonment: an inactive logical slot can retain WAL indefinitely.

  • DDL and non-table objects: migrations must be applied to PostgreSQL 17 separately.

Check pg_stat_archiver on PostgreSQL 17 to confirm WAL archiving is working, and monitor pg_stat_user_tables and pg_stat_database for bloat, dead tuples, conflicts and deadlocks after the workload moves.

Code Example

A minimal replicated schema can be created on both clusters before enabling the subscription:

CREATE SCHEMA app;

CREATE TABLE app.users (
    id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    email text NOT NULL
);

CREATE TABLE app.orders (
    id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    user_id bigint NOT NULL REFERENCES app.users(id),
    total numeric(12,2) NOT NULL
);

Example source data:

INSERT INTO app.users(email)
VALUES ('ada@example.com'), ('linus@example.com');

INSERT INTO app.orders(user_id, total)
VALUES (1, 25.50), (2, 80.00);

SELECT id, email FROM app.users ORDER BY id;

Example result:

id email
1 ada@example.com
2 linus@example.com

Run the schema on PostgreSQL 17 before creating the subscription; do not manually insert the copied application rows there.

Best Practices

  • Use logical replication for low-downtime upgrades; rehearse pg_upgrade separately when physical in-place upgrades are preferable.

  • Keep the replication slot until the new cluster has been validated and the rollback window has closed.

  • Apply DDL in a coordinated migration process, not only through logical replication.

  • Use sslmode=verify-full, SCRAM authentication, network segmentation and non-superuser replication roles.

  • Record schema versions and sequence values in the deployment runbook.

  • Before cutover, verify replication lag, row counts, sequence state, backup health, WAL archiving and application compatibility.

70

How do you safely add a defaulted column in PostgreSQL?

Fast default is PostgreSQL’s metadata-only path for adding a column with a non-volatile DEFAULT. It avoids rewriting a large table, but the ALTER TABLE still requires an ACCESS EXCLUSIVE lock, so lock contention and deployment timing remain important operational concerns.

Read full answer

Applies toPostgreSQL 16 and 17

How It Works

When PostgreSQL 11 or later adds a column whose default expression is not volatile, it records the default in the table metadata instead of physically updating every existing row.

  • Existing rows appear to contain the default value when queried.

  • New rows normally omit the value physically and use the metadata default.

  • The heap table is not rewritten, minimizing table bloat, I/O, and WAL volume.

  • The operation is transactional: if it fails, it can be rolled back.

  • The ALTER TABLE still takes ACCESS EXCLUSIVE, which conflicts with ordinary reads and writes. The lock is held until the transaction commits.

A volatile default, such as one using clock_timestamp() or random(), requires PostgreSQL to rewrite the table so it can calculate a distinct value for each existing row.

Safe Deployment Procedure

  1. Classify the default expression. Check pg_proc.provolatile; i means immutable, s stable, and v volatile. A non-volatile expression such as now() does not trigger the table rewrite.

  2. Apply the DDL in a short transaction. Set a short lock_timeout so the migration fails quickly instead of waiting behind a long-running query.

  3. Retry deliberately. If the lock timeout occurs, inspect active sessions, resolve the blocking transaction, and retry the migration.

  4. Use staged rollout for application changes. Add the column first, deploy code that reads and writes it, backfill existing data if needed, and only then enforce stricter constraints.

  5. Add NOT NULL separately when necessary. First establish a valid CHECK constraint, then use SET NOT NULL; PostgreSQL can use that constraint to avoid a second full-table scan.

Code Example

The following runs on PostgreSQL 16 and 17. The default is non-volatile, so the ADD COLUMN does not rewrite the table:

CREATE TEMP TABLE events_demo (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    payload text NOT NULL
);

INSERT INTO events_demo (payload)
VALUES
    ('first event'),
    ('second event');

BEGIN;
SET LOCAL lock_timeout = '2s';
SET LOCAL statement_timeout = '30s';

ALTER TABLE events_demo
    ADD COLUMN status text DEFAULT 'active';

COMMIT;

ALTER TABLE events_demo
    ADD CONSTRAINT events_demo_status_nn
    CHECK (status IS NOT NULL) NOT VALID;

ALTER TABLE events_demo
    VALIDATE CONSTRAINT events_demo_status_nn;

ALTER TABLE events_demo
    ALTER COLUMN status SET NOT NULL;

SELECT id, payload, status
FROM events_demo
ORDER BY id;

Expected result:

id payload status
1 first event active
2 second event active

The NOT VALID constraint is added without immediately scanning every row. VALIDATE CONSTRAINT performs the scan while holding a weaker lock than ACCESS EXCLUSIVE; it can still create significant I/O. The subsequent SET NOT NULL can use the valid constraint instead of scanning the table again.

For a large table where a physical backfill is required, use an expand-and-backfill approach: add the column as nullable, update bounded batches in separate transactions, set the default, and then enforce NOT NULL. Avoid one giant UPDATE, because it creates a long transaction, increases WAL and replication lag, and delays vacuum cleanup.

Failure Modes and Monitoring

  • Lock contention: ALTER TABLE may wait behind long-running transactions. Monitor blocked sessions with pg_stat_activity and pg_locks.

  • Unexpected rewrite: Verify that the default function is not volatile before applying the migration.

  • WAL and replica impact: A rewrite generates substantial WAL and can delay physical replicas. Check replication lag after the operation.

  • Disk space and vacuum pressure: A rewrite writes a full new copy of the table, so free disk space must cover roughly the table’s size until the old heap file is unlinked at commit; it does not create dead tuples. It is UPDATE and DELETE that create dead tuples, which VACUUM then reclaims — monitor n_dead_tup, last_autovacuum, and table size in pg_stat_user_tables.

  • Transaction ID retention: The rewrite itself does not cause wraparound, but long-running transactions can prevent vacuum from advancing datfrozenxid. Track age(datfrozenxid) and investigate old transactions.

  • Constraint validation load: VALIDATE CONSTRAINT can consume substantial CPU and I/O, although it generally does not block normal writes.

Useful monitoring queries include:

SELECT pid,
       wait_event_type,
       wait_event,
       now() - query_start AS query_age,
       left(query, 120) AS query
FROM pg_stat_activity
WHERE datname = current_database()
  AND wait_event_type = 'Lock';

SELECT relname,
       n_live_tup,
       n_dead_tup,
       last_autovacuum,
       last_autoanalyze
FROM pg_stat_user_tables
WHERE relname = 'events_demo';

Best Practices

  • Test the migration against a production-sized copy and estimate lock, disk, WAL, and replica-lag impact.

  • Use a non-volatile default whenever the business requirement allows it.

  • Set lock_timeout; do not allow a migration to wait indefinitely and block a queue of application queries.

  • Keep the DDL transaction short and commit promptly.

  • For zero-downtime application changes, prefer nullable-column expansion, batched backfills, application rollout, and deferred constraint enforcement.

  • Ensure backups and point-in-time recovery are available before a large schema change, especially when a rewrite is unavoidable.

71

How do you run PostgreSQL schema migrations without table locks?

Use an online expand-and-contract migration to avoid long-held production table locks: deploy backward-compatible changes, backfill in bounded transactions, switch traffic, and contract later. PostgreSQL has no general “zero table-lock” DDL mode, so the goal is to minimize lock duration, avoid lock queues, and use weaker-lock alternatives where available.

Read full answer

Applies toPostgreSQL 16 and 17

How PostgreSQL Locks During DDL

Most ALTER TABLE operations request an ACCESS EXCLUSIVE lock, which conflicts with ordinary reads and writes. A migration waiting for that lock can also queue subsequent queries behind it.

Operation Typical impact Safer alternative
Add nullable column Brief ACCESS EXCLUSIVE; normally no rewrite Set timeouts, then add it
Add column with volatile default May rewrite the entire table Add nullable column, backfill, then set default
Add NOT NULL ACCESS EXCLUSIVE; may scan unless a valid CHECK proves there are no nulls Add and validate CHECK, then apply NOT NULL
Create index Plain CREATE INDEX blocks writes CREATE INDEX CONCURRENTLY
Rename or drop column Brief ACCESS EXCLUSIVE Expand and contract across releases
Add foreign key Can lock both tables and scan existing rows Add NOT VALID, then validate separately

NOT VALID is supported for CHECK and foreign-key constraints. VALIDATE CONSTRAINT performs the scan using locks that do not block normal writes, although it still consumes I/O and WAL.

Expand-and-Contract Strategy

  1. Expand: Add backward-compatible nullable columns, new tables, or indexes without changing existing application behavior.

  2. Migrate: Backfill in small key ranges, using short transactions. A single large transaction creates dead tuples, holds an old row-version snapshot, increases WAL and replication lag, and can consume many transaction IDs.

  3. Switch: Deploy code that reads or writes the new structure. For large tables, use dual writes or another controlled transition.

  4. Contract: After old code and rollback paths are gone, enforce final constraints or remove obsolete columns in a later release.

Flyway and Liquibase can manage migration ordering and deployment state, but their lock behavior still depends on the SQL executed.

Operational Runbook

Use lock_timeout to bound the wait for a lock and statement_timeout to bound execution. Retry a timeout rather than increasing it blindly while traffic is running.

psql "$DATABASE_URL" -v ON_ERROR_STOP=1 <<'SQL'
BEGIN;
SET LOCAL lock_timeout = '1s';
SET LOCAL statement_timeout = '5min';

ALTER TABLE app_orders ADD COLUMN status text;

COMMIT;
SQL

Inspect lock waits, long transactions, dead tuples, and transaction-ID age:

SELECT pid, usename, state, wait_event_type, xact_start, query
FROM pg_stat_activity
WHERE wait_event_type = 'Lock'
   OR xact_start IS NOT NULL
ORDER BY xact_start;

SELECT schemaname, relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

SELECT datname, age(datfrozenxid) AS xid_age
FROM pg_database
WHERE datname NOT IN ('template0', 'template1');

n_dead_tup is an estimate, not an exact bloat measurement. Combine it with last_autovacuum, long-running transactions, and age(datfrozenxid) to identify vacuum and wraparound pressure.

Code Example

CREATE INDEX CONCURRENTLY must run outside an explicit transaction.

CREATE TABLE app_orders (
    id bigint PRIMARY KEY,
    user_id bigint NOT NULL
);

INSERT INTO app_orders (id, user_id)
SELECT g, g % 100
FROM generate_series(1, 10000) AS g;

BEGIN;
SET LOCAL lock_timeout = '1s';
ALTER TABLE app_orders ADD COLUMN status text;
COMMIT;

UPDATE app_orders
SET status = 'paid'
WHERE id = 42;

CREATE INDEX CONCURRENTLY app_orders_user_id_idx
    ON app_orders (user_id);

ANALYZE app_orders;

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, user_id, status
FROM app_orders
WHERE user_id = 42;

Expected row:

id | user_id | status
----+---------+--------
 42 |      42 | paid

Shortened EXPLAIN output:

Index Scan using app_orders_user_id_idx on public.app_orders
  Index Cond: (user_id = 42)

Production Failure Modes and Safety

  • Failed concurrent index build: PostgreSQL can leave an INVALID index. Check pg_index.indisvalid; remove it with DROP INDEX CONCURRENTLY before rebuilding.

  • Lock pileup: Monitor wait_event_type = 'Lock' and cancel a known-safe statement with pg_cancel_backend(pid), or end the session with pg_terminate_backend(pid) only after assessing impact.

  • Table rewrite: Volatile defaults can rewrite the table and generate substantial WAL. Non-volatile defaults are normally metadata-only in PostgreSQL 16/17.

  • Long transactions: They retain dead tuples, delay VACUUM, and increase bloat and transaction-ID wraparound pressure.

  • Replication and PITR: DDL is recorded in WAL, so physical replicas receive it. Ensure WAL archives retain it for the required PITR window and test recovery.

  • Security and rollback: Use a dedicated migration role with only required privileges, obtain credentials from a secret manager, and prefer forward corrective migrations over destructive rollback scripts.

72

What is PostgreSQL's TOAST storage?

TOAST—Transparent Automatic Storage of Out-of-range Tuple Data—lets PostgreSQL store values that are too large to fit comfortably in a heap tuple. PostgreSQL may compress a value, move it into an internal TOAST table, or leave it inline while preserving normal SQL access to the column.

Read full answer

Applies toPostgreSQL 16/17

How TOAST Works

PostgreSQL pages are normally 8 KB. A heap tuple must fit on one page, so a large text, jsonb, bytea, or other variable-length value cannot always remain directly in the row.

When a value is written:

  1. PostgreSQL checks the column’s storage mode.

  2. If permitted, it attempts to compress the value.

  3. If the compressed value still makes the tuple too large, it externalizes the value.

  4. The main tuple receives a compact TOAST pointer.

  5. The value is divided into chunks and stored as rows in a hidden TOAST table owned by the main table.

A TOAST table contains columns such as chunk_id, chunk_seq, and chunk_data. chunk_id identifies the value, while chunk_seq preserves its original order. The relationship is recorded through pg_class.reltoastrelid.

TOAST is transparent to applications: queries continue using ordinary expressions such as SELECT payload FROM documents, and PostgreSQL retrieves and decompresses the value when required.

Storage Modes

The column’s storage mode is recorded in pg_attribute.attstorage and can be changed with ALTER TABLE ... ALTER COLUMN ... SET STORAGE.

Mode Compression Out-of-line storage Typical use
PLAIN No No Values that must never be compressed or moved out of line; bytea can use it, although its default is EXTENDED
EXTENDED Yes Yes General large values, including typical text and jsonb columns
EXTERNAL No Yes Already-compressed or incompressible data
MAIN Yes No Prefer an inline compressed value but reject values that remain too large

Values of approximately 2 KB or less commonly remain inline, but the effective threshold depends on tuple overhead, alignment, compression, and page size. Compression support in PostgreSQL 16/17 can involve pglz or lz4, depending on server build and the default_toast_compression setting.

Performance and Access Patterns

TOAST changes where data is stored, not the logical behavior of the column.

  • Selective access: Reading one large column from a row usually avoids retrieving unrelated large columns.

  • Detoasting: Comparisons, functions, sorting, or returning the full value may require reading and decompressing the external chunks.

  • I/O latency: Queries returning many large values can consume substantial disk bandwidth even when the main relation appears small.

  • WAL volume: Large inserts and updates generate significant WAL because the externalized data must be recorded.

  • Index limits: TOAST does not guarantee that an arbitrarily large value can be indexed. B-tree index entries have size constraints, so oversized indexed values can still fail.

  • Memory settings: work_mem primarily affects sorting, hashing, and similar operations; it is not the main control for TOAST storage.

Use EXPLAIN (ANALYZE, BUFFERS) to inspect actual execution and buffer activity. Ordinary plans do not necessarily identify TOAST reads separately, so combine buffer observations with database and disk-level I/O monitoring.

Vacuum, Bloat, and Transaction Impact

Updating a large value creates a new version of the TOAST data and leaves the old version eligible for cleanup. PostgreSQL’s normal vacuum process processes TOAST relations, but high update rates can cause:

  • TOAST bloat: Dead chunks remain allocated until vacuumed and can make the TOAST table much larger than the live dataset.

  • Vacuum lag: Long-running transactions prevent cleanup and increase storage pressure.

  • Locking and transaction duration: TOAST chunks are rows in another relation with their own MVCC versions; updates can create, lock, and delete those rows. Slow detoasting can also lengthen transactions, retaining snapshots and relation locks during read-only queries.

  • Disk exhaustion: Main tables may look small while their hidden TOAST relations consume substantial capacity.

Autovacuum should normally manage the owning table and its TOAST relation. Operators should monitor the TOAST relation identified by pg_class.reltoastrelid, its size through pg_total_relation_size, relation statistics through pg_stat_all_tables, vacuum progress through pg_stat_progress_vacuum, and database-wide transaction age through pg_stat_database.datfrozenxid.

Vacuum makes dead space reusable, but it does not always return the space to the operating system. A steadily shrinking live-data footprint alongside a non-shrinking file should not automatically be treated as a leak.

Backups, Replication, and Security

TOAST data is part of the owning PostgreSQL database, not a separate external service.

  • Physical base backups include TOAST relations.

  • WAL required for PITR includes changes to TOAST data.

  • pg_dump and logical replication include the logical column values.

  • Standby replication transmits changes needed to maintain the TOAST relation.

  • Access still follows the owning table’s permissions; moving data to a TOAST table does not bypass PostgreSQL authorization.

Production alerts should track TOAST size growth, dead tuples, vacuum age, WAL generation, disk utilization, and query latency. If values are unusually large or frequently rewritten, review column types, compression settings, update patterns, and whether the application needs to retrieve the complete value on every request.

73

How does PostgreSQL full-text search work?

PostgreSQL full-text search is the built-in facility for indexing and querying words, stems, phrases, and related terms in text. It converts documents into searchable lexemes stored in a tsvector, then matches them against a structured tsquery.

Read full answer

Applies toPostgreSQL 16 and 17

How It Works

  • Text analysis: The parser splits text into tokens; a language dictionary normalizes them, drops stop words when configured, and may stem words. The english dictionary, for example, maps “running” to “run”.

  • tsvector: to_tsvector('english', document_text) returns normalized lexemes with positions. Positions enable phrase and proximity searches; the optional weights A, B, C, D drive ranked results.

  • tsquery: plainto_tsquery, phraseto_tsquery, and websearch_to_tsquery turn user input into a query; the @@ operator tests a tsvector against a tsquery.

A document can be indexed on demand or kept current automatically by a stored generated column, provided the text search configuration is written explicitly in the expression (the explicit configuration name makes the expression immutable). Otherwise an application or trigger must maintain the tsvector.

Search Configuration

Named text search configurations such as english or simple are created with CREATE TEXT SEARCH CONFIGURATION and consist of a parser, dictionaries, and token types. The database default is default_text_search_config; it can be overridden per role with ALTER ROLE ... IN DATABASE ... SET.

Configuration Behaviour Use when
english Stop-word filtering plus stemming Natural-language prose
simple Lowercases only, no stemming or stop words Technical content where exact token matching matters
Custom Own parser, dictionaries, token types Domain-specific or non-English content

Changing dictionaries changes how existing text is tokenized: after a dictionary or configuration migration, rebuild the affected tsvector values and reindex them.

Index Choices

PostgreSQL never creates a full-text index automatically, and the index type depends on the workload.

Index type Full-text use Operational characteristics
GIN Preferred for tsvector @@ tsquery Inverted posting lists; fast reads, but write amplification and heavier vacuum
GiST Supported for tsvector Slower than GIN here; useful when one index must also serve other GiST predicates
B-tree Not for full-text matching Equality, ranges, ordering
BRIN Not for full-text matching Very large, physically correlated tables with summary predicates

GIN keeps pending-list entries before merging them into the main posting structure, so update-heavy workloads grow the index and see temporary search latency. Track index size and vacuum activity; rebuild a badly bloated GIN index in a maintenance window with REINDEX INDEX CONCURRENTLY.

Ranking and Query Features

  • ts_rank: Relevance score from matching lexemes and weights.

  • ts_rank_cd: Cover-density score that favours terms appearing close together.

  • setweight: Assigns weights to per-field vectors, e.g. title versus body.

  • phraseto_tsquery: Requires terms adjacent and in the given order.

  • websearch_to_tsquery: Supports quoted phrases, OR, and - negation, and never raises syntax errors.

Always combine ORDER BY with a ranking function and apply a limit: ranking a broad term without a limit can consume significant CPU and memory.

Production Operations

  • Monitoring: pg_stat_user_indexes shows unused or hot indexes, pg_stat_statements (enable the extension) exposes expensive searches, pg_stat_activity shows active and blocked backends, and pg_stat_progress_create_index reports progress for CREATE INDEX and REINDEX.

  • Bloat and vacuum: Frequent document updates create dead row versions and dead GIN entries; make sure autovacuum keeps pace on large, frequently updated indexes.

  • Long transactions: A long-running transaction pins an old xmin and blocks vacuum from reclaiming dead rows; check pg_stat_activity before blaming autovacuum.

  • Partitioning: Split very large corpora by time or another stable key; pruning only happens when the query predicate matches the partition key.

  • Replication: Hot standbys can serve searches, but results are only as fresh as the standby’s WAL replay position, and read queries on a standby compete with WAL replay and delay it, increasing replication lag.

  • Backups and upgrades: Physical backups include index files; logical restores rebuild indexes from their definitions and data. Ensure required extensions and custom dictionary files exist before a restore or a major-version upgrade.

Security and Limitations

Bind user search input as a query parameter instead of concatenating it into SQL. websearch_to_tsquery avoids interpreting raw tsquery syntax, but it does not make unrestricted searches cheap.

Protect custom dictionaries and configuration files and deploy them consistently across primary servers, replicas, and restored environments. Full-text search matches lexemes, not meaning: synonyms, spelling correction, embedding similarity, or entity recognition need extra extensions, custom dictionaries, or a dedicated search system.

74

How do you harden and secure a PostgreSQL server in production?

Securing a PostgreSQL server means applying defense in depth: lock down the network, authenticate and authorize every connection, encrypt traffic and storage, harden the host operating system, and keep enough audit trail to prove what happened. A database is only as secure as the weakest path to it, and the most common production breach is not an SQL exploit — it is an exposed port, a weak password, or an over-privileged login.

Read full answer

Applies toPostgreSQL 16 and 17 on Linux (Ubuntu 22.04/24.04, RHEL 9)

The Layers of PostgreSQL Security

Layer Control Mechanism
Network Don’t expose 5432 listen_addresses, firewall, security group
Authentication SCRAM, certs, LDAP pg_hba.conf, password_encryption
Authorization Least privilege, RLS Roles, grants, policies
Transport TLS in transit ssl = on, sslmode=verify-full
Storage Disk + backup encryption LUKS/dm-crypt, encrypted archives
OS Service account, file modes postgres user, chmod 0600 keys
Audit Connection and statement logs pgaudit, log_connections

Network and Authentication

Set listen_addresses to only the addresses you need (never * on a public host) and let the firewall do the rest. pg_hba.conf is evaluated top to bottom, first match wins, and any unmatched connection is rejected — so order specific CIDRs before broad ones.

  • Rule order: put the most specific rules first; a host all all 0.0.0.0/0 md5 line near the top silently defeats every rule below it.

  • Encryption: use scram-sha-256, never trust and never md5. MD5 password support is deprecated as of PostgreSQL 18 and planned for removal.

  • superuser: postgres is a superuser by default. Create a named admin role, rename or lock the default, and give applications NOSUPERUSER NOCREATEDB NOCREATEROLE plus only the schema grants they need.

  • pgBouncer: put a connection pooler in front so app credentials never leave the host, and keep it on the same private network with its own TLS.

Encryption and OS Hardening

Enable TLS server-side (ssl = on with ssl_cert_file / ssl_key_file), and require clients to verify the CA with sslmode=verify-full. Keep the private key 0600, owned by postgres. Encrypt the data volume and backups (LUKS, or pgBackRest/WAL-G with encrypted repositories) — WAL segments and base backups contain the same plaintext as the database.

Host hardening: run only the postgres system account, chmod 0700 on PGDATA, restrict ~/.psqlrc (it can execute shell commands), enable auditd/SELinux/AppArmor, and set systemd limits such as LimitNOFILE in the unit override.

Audit and Monitoring

Turn on log_connections, log_disconnections, log_line_prefix with user/database/client address, and install pgaudit to log DDL and DML. Watch pg_stat_ssl to confirm clients are actually using TLS, pg_stat_activity for long-running or idle-in-transaction sessions, and pg_hba_file_rules for the effective ruleset. Ship logs to a central SIEM; on-host logs can be deleted by whoever compromised the host.

Code Example

# /etc/postgresql/16/main/postgresql.conf
listen_addresses = '127.0.0.1,10.0.0.5'   # this host's private IP
ssl = on
ssl_cert_file = '/etc/ssl/certs/postgresql.crt'
ssl_key_file = '/etc/ssl/private/postgresql.key'
ssl_ca_file = '/etc/ssl/certs/ca.crt'
password_encryption = 'scram-sha-256'
log_connections = on
log_disconnections = on
log_line_prefix = '%m [%p] %u@%d %a '
shared_preload_libraries = 'pgaudit'   # required before CREATE EXTENSION pgaudit
# /etc/postgresql/16/main/pg_hba.conf
# TYPE  DATABASE  USER  ADDRESS         METHOD
local   all       all                   scram-sha-256
host    all       all   127.0.0.1/32    scram-sha-256
host    appdb     app   10.0.0.0/16     scram-sha-256
host    replication replicator 10.0.0.0/16 scram-sha-256
-- Least-privilege application role
CREATE ROLE app WITH LOGIN PASSWORD 'use-a-secret-manager' NOSUPERUSER NOCREATEDB NOCREATEROLE;
CREATE DATABASE appdb OWNER app;
REVOKE ALL ON DATABASE appdb FROM PUBLIC;
GRANT CONNECT ON DATABASE appdb TO app;
ALTER SCHEMA public OWNER TO app;

-- Row-level security for multi-tenant data
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY orders_tenant ON orders USING (tenant_id = current_setting('app.tenant_id')::int);

-- Audit extension (superuser required)
CREATE EXTENSION pgaudit;
sudo systemctl reload postgresql      # pg_hba.conf changes
sudo systemctl restart postgresql     # listen_addresses / shared_preload_libraries changes
sudo ufw allow from 10.0.0.0/16 to any port 5432 proto tcp   # private network only
sudo ufw deny 5432/tcp                # everyone else (or firewalld / security group)

Common Pitfalls

  • Reloading vs restarting: pg_hba.conf, password_encryption and logging parameters apply with pg_reload_conf(); listen_addresses and shared_preload_libraries need a restart (ssl can be reloaded).

  • Changing password_encryption does not re-hash existing MD5 passwords — reset each password to upgrade it to SCRAM.

  • trust on a local socket plus host all all 0.0.0.0/0 is effectively open access.

  • Never put the app’s superuser password in a repository or image; use a secret manager and rotate credentials.

  • Restrict pg_read_all_stats, pg_execute_server_program and CREATE EXTENSION to trusted roles only.

  • Keep replicas on a private network, and treat a replica as a full copy of your data for breach-impact purposes.

75

How do you reset a forgotten PostgreSQL superuser password?

PostgreSQL superuser password reset is normally performed through a local administrative connection. Access to the server’s operating system lets you connect as the postgres role using peer authentication, bypassing the forgotten password, and then set a new password securely with \password.

Read full answer

Applies toPostgreSQL 16 and PostgreSQL 17

How It Works

  • The postgres operating-system account connects to PostgreSQL as the database superuser named postgres when the applicable pg_hba.conf rule permits it.

  • Peer authentication verifies the OS identity rather than checking the forgotten database password. This requires a matching local entry in pg_hba.conf.

  • The \password command prompts twice, avoiding plaintext in shell history or process arguments.

  • PostgreSQL stores a password verifier in pg_authid; the plaintext password is not stored.

  • The verifier format depends on password_encryption. PostgreSQL 16/17 defaults to scram-sha-256, but the effective server or session setting should be checked before resetting the password.

  • The new password matters only when the matching pg_hba.conf rule uses password authentication. pg_hba.conf names an authentication method, not a credential: the password-based methods are password, md5, and scram-sha-256. A peer, cert, or trust rule does not test the password.

  • The role must have LOGIN; changing its password does not enable a NOLOGIN role.

Production Considerations

  • Replication: A password change is WAL-logged and reaches a physical standby. Role definitions and passwords are not replicated by logical replication, so logical subscribers must be managed separately. Each server’s pg_hba.conf remains locally configured.

  • Backups and PITR: Role verifiers are included in physical backups, which must be protected as sensitive data. Recovery to a point before the reset may restore the old verifier; replaying WAL containing the reset applies the new one.

  • Monitoring: pg_stat_activity shows current sessions and their state, including background workers, but does not prove successful authentication or expose password verifiers. Use protected PostgreSQL logs where authentication or role-change logging is enabled, and use the organization’s secret manager for rotation auditing.

  • Least privilege: Do not use the superuser for applications. Create separate application, migration, and operational roles with only the required attributes and privileges.

  • Network security: Restrict superuser access to administrative hosts, require TLS for remote connections, and avoid exposing PostgreSQL directly to untrusted networks.

Code Example

Run this on the PostgreSQL host from an account authorized to use sudo:

sudo -u postgres psql

At the psql prompt, ensure that the new verifier uses SCRAM and then replace postgres if the affected superuser has a different name:

postgres=# SET password_encryption = 'scram-sha-256';
SET
postgres=# \password postgres
Enter new password for user postgres:
Enter it again:
postgres=# SELECT rolname, rolcanlogin, rolsuper
postgres-# FROM pg_roles WHERE rolname = 'postgres';
  rolname  | rolcanlogin | rolsuper
----------+-------------+----------
 postgres | t           | t
(1 row)

postgres=# \q

The displayed flags are typical for the built-in postgres role. If rolcanlogin is false but direct login is intended, enable it explicitly:

ALTER ROLE postgres LOGIN;

Test the new password from a remote administrative host. With the server CA installed in the client’s ~/.postgresql/root.crt, run:

psql "host=db.example.com port=5432 dbname=postgres user=postgres sslmode=verify-full"

Let psql prompt for the password rather than placing it in PGPASSWORD, a connection string, or shell history.

If no local peer-authentication path exists, an OS administrator can temporarily add this rule near the top of pg_hba.conf:

local postgres postgres peer

Reload PostgreSQL using the platform’s service-management command, perform the reset, remove the temporary rule, and reload again. For another target role, configure an entry whose OS and database role names match the intended peer mapping. Never replace peer authentication with trust on a production server.

Best Practices and Common Failures

  • Generate a strong, unique password and deliver it through a secret manager rather than email or chat.

  • Prefer \password over ALTER ROLE ... PASSWORD '...' for interactive use because it avoids exposing plaintext through client-side history and process arguments. It does not make the operation invisible to server-side statement logging, so protect or redact logs and avoid broad statement logging around role changes.

  • If sudo -u postgres psql reports peer authentication failed, inspect the first matching local rules in pg_hba.conf and confirm the OS account and target role mapping.

  • If remote login fails after the reset, check rolcanlogin, the applicable host rule, TLS requirements, and the stored verifier format. An MD5 verifier cannot satisfy a host rule that requires SCRAM.

  • Rotate the credential again if the old password may have been exposed. Disabling the original superuser with NOLOGIN may be preferable when it is no longer required.

76

How do I find PostgreSQL's top three revenue products per category?

Use a window function to calculate each product’s total revenue, then apply ROW_NUMBER() within each category to select the top three. This returns up to three products per category—exactly three when at least three products have sales—and produces deterministic ordering when revenues tie.

Read full answer

Applies toPostgreSQL 16/17

Query Approach

  1. Aggregate sales by product and calculate revenue as quantity * unit_price.

  2. Partition products by category_id.

  3. Rank each partition by revenue in descending order.

  4. Retain ranks 1 through 3.

ROW_NUMBER() returns at most three sold products per category. A category with fewer than three matching products contributes fewer rows, while a category with no sales does not appear.

If ties should include every product sharing the same top-three revenue levels, replace it with DENSE_RANK() and remove the product_id tie-breaker; that version may return more than three products.

Code Example

The following is a complete PostgreSQL 16 example with sample data:

CREATE SCHEMA revenue_demo;

CREATE TABLE revenue_demo.categories (
    category_id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    category_name text NOT NULL UNIQUE
);

CREATE TABLE revenue_demo.products (
    product_id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    category_id bigint NOT NULL
        REFERENCES revenue_demo.categories(category_id),
    product_name text NOT NULL UNIQUE
);

CREATE TABLE revenue_demo.sales (
    sale_id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    product_id bigint NOT NULL
        REFERENCES revenue_demo.products(product_id),
    quantity integer NOT NULL CHECK (quantity > 0),
    unit_price numeric(12, 2) NOT NULL CHECK (unit_price >= 0),
    sold_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO revenue_demo.categories (category_name)
VALUES ('Electronics'), ('Books');

INSERT INTO revenue_demo.products (category_id, product_name)
SELECT c.category_id, v.product_name
FROM (
    VALUES
        ('Electronics', 'Laptop'),
        ('Electronics', 'Phone'),
        ('Electronics', 'Headphones'),
        ('Electronics', 'Monitor'),
        ('Books', 'SQL Handbook'),
        ('Books', 'PostgreSQL Guide'),
        ('Books', 'Data Engineering'),
        ('Books', 'Algorithms')
) AS v(category_name, product_name)
JOIN revenue_demo.categories AS c USING (category_name);

INSERT INTO revenue_demo.sales (product_id, quantity, unit_price)
SELECT p.product_id, v.quantity, v.unit_price
FROM (
    VALUES
        ('Laptop', 3, 1000.00),
        ('Phone', 4, 500.00),
        ('Headphones', 2, 150.00),
        ('Monitor', 2, 300.00),
        ('SQL Handbook', 5, 40.00),
        ('PostgreSQL Guide', 2, 60.00),
        ('Data Engineering', 2, 55.00),
        ('Algorithms', 3, 50.00)
) AS v(product_name, quantity, unit_price)
JOIN revenue_demo.products AS p USING (product_name);

The reporting query is:

WITH product_revenue AS (
    SELECT
        p.category_id,
        p.product_id,
        p.product_name,
        SUM(s.quantity * s.unit_price)::numeric(18, 2) AS revenue
    FROM revenue_demo.products AS p
    JOIN revenue_demo.sales AS s
      ON s.product_id = p.product_id
    GROUP BY
        p.category_id,
        p.product_id,
        p.product_name
),
ranked_products AS (
    SELECT
        pr.*,
        ROW_NUMBER() OVER (
            PARTITION BY pr.category_id
            ORDER BY pr.revenue DESC, pr.product_id
        ) AS category_rank
    FROM product_revenue AS pr
)
SELECT
    c.category_name,
    r.category_rank,
    r.product_name,
    r.revenue
FROM ranked_products AS r
JOIN revenue_demo.categories AS c
  ON c.category_id = r.category_id
WHERE r.category_rank <= 3
ORDER BY
    c.category_name,
    r.category_rank;

The result is:

category_name category_rank product_name revenue
Books 1 SQL Handbook 200.00
Books 2 Algorithms 150.00
Books 3 PostgreSQL Guide 120.00
Electronics 1 Laptop 3000.00
Electronics 2 Phone 2000.00
Electronics 3 Monitor 600.00

To inspect execution, prefix the query with EXPLAIN (ANALYZE, BUFFERS). A shortened plan shape is:

WindowAgg
  -> Sort
    -> Hash Join
      -> Aggregate
        -> Hash Join
          -> Seq Scan on products
          -> Seq Scan on sales
      -> Seq Scan on categories

Actual row estimates, buffer counts and execution times depend on table size and PostgreSQL statistics.

Production and Operational Notes

  • Define revenue precisely: The example calculates gross sales revenue. Include discounts, refunds, taxes, cancelled orders and currency conversion according to the reporting requirement.

  • Exclude or include zero-sales products deliberately: The inner join excludes products without sales. Use a LEFT JOIN with COALESCE(SUM(...), 0) if zero-revenue products must appear.

  • Plan for scale: The query aggregates the entire sales history. For large datasets, filter by an appropriate date range, partition sales by sold_at, or maintain a materialized revenue summary.

  • Monitor performance: Use pg_stat_statements to track latency and calls, and pg_stat_user_tables to monitor dead tuples and autovacuum activity. Long-running transactions can increase MVCC bloat and make this aggregation slower.

  • Use EXPLAIN carefully: EXPLAIN ANALYZE executes the statement and can consume substantial I/O and CPU in production. Begin with plain EXPLAIN, then run EXPLAIN (ANALYZE, BUFFERS) during a controlled diagnostic window.

77

How do you diagnose sudden PostgreSQL performance degradation?

Diagnose a PostgreSQL slowdown by correlating wait-event analysis, active queries, lock relationships, execution plans, system resource usage, and MVCC maintenance state. Start with the symptom and blast radius, then isolate whether the bottleneck is CPU, storage, locks, excessive connections, bloat, stale statistics, or a changed query plan.

Read full answer

Applies toPostgreSQL 16/17

Incident Triage

  1. Confirm the regression

  2. Compare API latency, query latency, throughput, and error rates with the previous baseline.

  3. Check whether traffic, deployment, configuration, failover, or storage latency changed.

  4. Determine whether all queries or only specific endpoints are affected.

  5. Understand PostgreSQL’s architecture

  6. Each client connection has a PostgreSQL backend process.

  7. Queries execute through PostgreSQL shared buffers and the operating system’s page cache.

  8. WAL makes committed changes durable and supports replication and PITR.

  9. MVCC creates row versions; old versions eventually require VACUUM cleanup.

  10. Identify the dominant wait

  11. Lock: another transaction is blocking progress.

  12. IO: queries are waiting for buffer reads or storage.

  13. Client (for example ClientRead): PostgreSQL is waiting for the client, not necessarily slow itself.

  14. High CPU often indicates expensive sorting, hashing, or query planning.

  15. High connection counts may mean an application leak or insufficient pooling.

Diagnostic Workflow

  • Inspect pg_stat_activity for long-running, idle-in-transaction, blocked, or unexpectedly active client sessions.

  • Use pg_blocking_pids() to identify blockers instead of guessing from query text.

  • Run EXPLAIN (ANALYZE, BUFFERS) on a known, side-effect-free SELECT and compare it with a previous plan.

  • On the database host, inspect CPU, memory pressure, disk latency, inode usage, checkpoint activity, WAL growth, and replication lag.

  • Review dead tuples, last autovacuum times, stale statistics, and transaction ID age.

  • Check pg_stat_statements when it has been preloaded and enabled to find workload-level regressions.

Code Example

Set connection details without embedding credentials:

export PGHOST=db.example.com
export PGUSER=monitoring_role
export PGDATABASE=appdb

psql -X -v ON_ERROR_STOP=1 \
  -h "$PGHOST" -U "$PGUSER" -d "$PGDATABASE"

Run the following monitoring queries:

-- Active, idle, and blocked client sessions.
-- elapsed_s is query age for active sessions and transaction age
-- for sessions that are idle in a transaction.
SELECT pid, usename, state, wait_event_type, wait_event,
       round(extract(epoch FROM (
           clock_timestamp() -
           CASE
               WHEN state = 'active' THEN query_start
               WHEN state IN ('idle in transaction',
                              'idle in transaction (aborted)') THEN xact_start
           END
       ))::numeric, 3) AS elapsed_s,
       left(query, 120) AS query
FROM pg_stat_activity
WHERE datname = current_database()
  AND backend_type = 'client backend'
  AND pid <> pg_backend_pid()
ORDER BY query_start;

-- Sessions blocked by other backends.
SELECT a.pid, a.state, a.wait_event_type, a.wait_event,
       pg_blocking_pids(a.pid) AS blocking_pids,
       left(a.query, 120) AS query
FROM pg_stat_activity AS a
WHERE a.datname = current_database()
  AND a.backend_type = 'client backend'
  AND cardinality(pg_blocking_pids(a.pid)) > 0;

-- MVCC maintenance state.
SELECT relname, n_live_tup, n_dead_tup,
       last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

-- Transaction ID age; investigate sustained growth.
SELECT datname, age(datfrozenxid) AS xid_age
FROM pg_database
ORDER BY xid_age DESC;

Representative activity results might look like:

pid state wait_event_type wait_event elapsed_s query
1042 active Lock transactionid 31.420 UPDATE orders ...
1038 active IO BufRead 12.840 SELECT ... FROM orders ...
1050 idle in transaction Client ClientRead 240.100 UPDATE inventory ...

A runnable plan example is:

BEGIN;

CREATE TABLE orders_demo (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id bigint NOT NULL,
    status text NOT NULL
);

INSERT INTO orders_demo (customer_id, status)
SELECT g % 100,
       CASE WHEN g % 10 = 0 THEN 'pending' ELSE 'paid' END
FROM generate_series(1, 10000) AS g;

ANALYZE orders_demo;

SELECT id, customer_id, status
FROM orders_demo
WHERE customer_id = 42
ORDER BY id
LIMIT 3;

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders_demo WHERE customer_id = 42;

ROLLBACK;

Representative result and shortened plan:

id | customer_id | status
----+-------------+--------
  42 |          42 | paid
 142 |          42 | paid
 242 |          42 | paid

Seq Scan on orders_demo
  Filter: (customer_id = 42)
  Rows Removed by Filter: 9900
Planning Time: 0.112 ms
Execution Time: 8.431 ms

Exact timings and buffer counts vary. In production, EXPLAIN ANALYZE actually executes the statement, so use it only for safe queries.

Root Causes and Operational Response

Symptom Likely cause Operational response
Lock waits with blocking PIDs Long transaction or inconsistent lock order Identify transaction owner; use pg_cancel_backend() or pg_terminate_backend() only after assessing impact
High dead tuple counts Autovacuum cannot keep up Check autovacuum settings, long-running transactions, and table update rates
Increasing XID age Old transactions prevent cleanup Find long-running transactions and replication-slot issues before wraparound threatens service
Unexpected plan or estimate/actual mismatch Changed selectivity, stale statistics, parameter skew, or a missing supporting index Compare estimated and actual rows, buffers, and cost; do not assume a sequential scan is a regression
High storage latency or buffer reads Saturated disk or cold cache Inspect storage metrics, checkpoint behavior, memory pressure, and query locality
Rising WAL and replica lag High write volume, archiving delay, or lagging standby Inspect replication state, WAL retention, and network throughput

Do not run VACUUM FULL during an incident without considering its strong locks and temporary disk requirements. Prefer REINDEX ... CONCURRENTLY where appropriate, and use pg_repack for online-style table rebuilds when justified.

Protect operational access with least-privilege monitoring roles, redact captured query text, and alert on blocked sessions, dead tuples, XID age, replication lag, WAL growth, checkpoint pressure, connection saturation, and latency percentiles. Maintain tested backups and PITR before performing repairs or major maintenance.

78

How do you scale PostgreSQL beyond one server?

Scaling PostgreSQL beyond one host usually combines horizontal read scaling, connection pooling, partitioning, and, only when necessary, sharding. A PostgreSQL cluster has one writable primary; replicas add read capacity and availability, while genuinely independent write scale requires distributing data across clusters.

Read full answer

Applies toPostgreSQL 16 and 17; PgBouncer 1.25.x

Scaling Strategy

Bottleneck Primary approach Operational trade-off
Excessive reads Physical or logical replicas plus read routing Replication lag can return stale data
Too many connections PgBouncer or application pooling Transaction pooling restricts session features
Large tables Range or list partitioning Non-pruning filters examine every partition
Write throughput Application sharding or Citus Cross-shard transactions and joins become expensive
CPU, memory, or storage limits Scale vertically first Usually simpler and less risky

Partitioning does not distribute one table across servers. Do not shard before exhausting indexing, query tuning, pooling, and vertical scaling.

Replication, Failover, and PITR

Physical streaming replication sends WAL from the primary to a standby. PostgreSQL uses a backend process per connection, shared memory for shared buffers, and WAL for durability and replication. The primary requires wal_level = replica, sufficient max_wal_senders, and a replication-authenticated role.

  • Asynchronous replication: minimizes write latency but can lose recent acknowledged transactions during failover.

  • Synchronous replication: with synchronous_commit = on and an appropriate synchronous_standby_names, acknowledged commits can have zero transaction loss while the required standby acknowledges the WAL flush. Failure of a required standby normally stalls commits.

  • Replication slots: retain WAL that the standby still needs. An inactive slot can fill the primary’s WAL disk; logical slots can also retain old xmin values.

  • Routing and failover: route only read-only traffic to standbys. Patroni or a managed service can coordinate leader election and discovery, but infrastructure must fence a stale primary to prevent split brain.

  • PITR: PostgreSQL combines a base backup with continuously archived WAL to restore to a selected timestamp or transaction. Use pgBackRest, Barman, or an equivalent production backup system with encrypted, separate storage.

Partitioning, Indexes, and Planning

Partitioning improves partition pruning and makes large-table maintenance more manageable, but it remains within one cluster. Use B-tree indexes for equality and range lookups, GIN for jsonb or array containment, GiST for geometric and range data, and BRIN for very large append-ordered tables. Prefer bigint for identifiers, timestamptz for event times, and numeric for exact decimals.

Use EXPLAIN (ANALYZE, BUFFERS) on production-like data to examine plans, row estimates, I/O, and temporary files. Because ANALYZE executes the statement, use it carefully with expensive or mutating statements.

Connection and Resource Scaling

PgBouncer decouples client connections from PostgreSQL backends. Transaction pooling is incompatible with features requiring a stable session, including temporary tables, session advisory locks, LISTEN/NOTIFY, and reliance on session-level configuration state. Benchmark shared_buffers, work_mem, max_connections, and pool sizes; increasing connection limits without sufficient memory can cause swapping or contention.

Code Example

The primary needs a physical replication slot and an pg_hba.conf rule such as host replication replicator 10.0.0.0/16 scram-sha-256. Run pg_basebackup as the PostgreSQL OS user and supply the password through .pgpass or a secret manager rather than the command line.

SELECT pg_create_physical_replication_slot('standby1');
# Run as the PostgreSQL OS user; use PG_MAJOR=16 for PostgreSQL 16.
PG_MAJOR=17

pg_basebackup \
  --host=db-primary.example.com \
  --username=replicator \
  --pgdata="/var/lib/postgresql/${PG_MAJOR}/standby1" \
  --format=plain \
  --wal-method=stream \
  --progress \
  --write-recovery-conf \
  --slot=standby1

Set hot_standby = on on the standby, start it, and verify streaming with pg_stat_replication.

CREATE TABLE events (
    id bigint GENERATED ALWAYS AS IDENTITY,
    tenant_id integer NOT NULL,
    created_at timestamptz NOT NULL,
    payload text NOT NULL
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2025_q1 PARTITION OF events
    FOR VALUES FROM ('2025-01-01') TO ('2025-04-01');

CREATE TABLE events_2025_q2 PARTITION OF events
    FOR VALUES FROM ('2025-04-01') TO ('2025-07-01');

CREATE INDEX events_tenant_created_idx
    ON events (tenant_id, created_at);

SET TIME ZONE 'UTC';

INSERT INTO events (tenant_id, created_at, payload)
VALUES (7, '2025-02-10T12:00:00Z', 'created')
RETURNING id, tenant_id, created_at, payload;
id tenant_id created_at payload
1 7 2025-02-10 12:00:00+00 created
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT payload
FROM events
WHERE tenant_id = 7
  AND created_at >= '2025-02-01'
  AND created_at < '2025-03-01';

Illustrative shortened output:

Append
  -> Seq Scan on events_2025_q1 events
       Filter: (tenant_id = 7 AND created_at >= ... AND created_at < ...)
Planning Time: 0.2 ms
Execution Time: 0.1 ms

A minimal PgBouncer configuration is:

[databases]
app = host=10.0.0.20 port=5432 dbname=app

[pgbouncer]
listen_addr = 10.0.0.10
listen_port = 6432
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

Use TLS with certificate verification for PostgreSQL and replication connections, restrict pg_hba.conf by source network, grant the application only required privileges, and protect userlist.txt and backup credentials with restrictive permissions.

Useful monitoring queries are:

SELECT application_name, state, sync_state,
       write_lag, flush_lag, replay_lag
FROM pg_stat_replication;

SELECT datname, age(datfrozenxid) AS xid_age
FROM pg_database
ORDER BY xid_age DESC;

SELECT pid, wait_event_type, wait_event, xact_start, query
FROM pg_stat_activity
WHERE wait_event_type = 'Lock';

Production Practices and Failure Modes

  • MVCC and VACUUM: MVCC retains old row versions. Long-running transactions delay VACUUM from removing them, causing bloat and preventing freezing; monitor pg_stat_user_tables, pg_stat_activity, and pg_stat_progress_vacuum.

  • Transaction-ID wraparound: old snapshots or logical slots retaining xmin can prevent freezing. Monitor age(datfrozenxid) and alert well before forced anti-wraparound autovacuum.

  • Lock contention: pooling reduces overhead but does not remove row or table locks. Inspect pg_locks, blocked sessions, and transaction age.

  • Replica lag: monitor continuously, but interpret interval-based lag carefully during idle periods.

  • WAL exhaustion: alert on slot inactivity, WAL disk usage, and sustained archive failures.

  • Backups and upgrades: perform timed restores against defined RPO/RTO targets. For PostgreSQL 16-to-17 upgrades, test pg_upgrade with downtime or a logical-replication migration; logical replication does not automatically copy DDL or sequence state, and extensions must be compatible.

79

How does PostgreSQL DELETE differ from TRUNCATE regarding VACUUM?

DELETE removes selected rows through MVCC and leaves dead tuple versions that VACUUM must reclaim. TRUNCATE removes an entire table much faster, reclaims its storage without creating dead tuples, and therefore does not require a dead-tuple vacuum afterward, although it has stricter locking and dependency behavior.

Read full answer

Applies toPostgreSQL 16 and 17

Core Behavior

Behavior DELETE TRUNCATE
Rows removed Rows selected by WHERE, or all rows if WHERE is omitted Every row in the specified table
MVCC dead tuples Created for each deleted row None
Trigger behavior Fires row-level and statement-level DELETE triggers Does not fire DELETE triggers; fires statement-level ON TRUNCATE triggers
Foreign keys Can invoke ON DELETE actions such as CASCADE Cannot truncate a referenced parent table alone unless referencing tables are included in the same command or CASCADE is used
Lock ROW EXCLUSIVE, allowing normal concurrent reads ACCESS EXCLUSIVE, blocking normal reads and writes
WAL for logged tables Generates row-level change records Generates a compact relation-truncation record
Sequence behavior Unaffected Owned sequences remain unchanged unless RESTART IDENTITY is specified
Typical use Selective deletion, retention policies, audited removals Fast reset of a complete table

Both commands are transactional and can be rolled back. TRUNCATE is not filtered by WHERE.

VACUUM and Storage Effects

DELETE marks each removed row with its deleting transaction. Once that transaction commits, the row is invisible to new snapshots, but its old tuple can remain physically present and visible to transactions whose snapshots predate the deletion.

  • Regular VACUUM removes dead tuples and makes their space reusable by the same table.

  • Regular VACUUM generally does not return most disk space to the operating system.

  • VACUUM FULL rewrites the table and rebuilds its indexes, returning unused space to the operating system, but requires an ACCESS EXCLUSIVE lock and can cause significant I/O.

  • Autovacuum uses a threshold calculated from a configured base threshold plus a scale factor applied to the table’s estimated tuple count. Sustained dead-tuple accumulation can therefore require repeated vacuum activity.

  • Long-running transactions can prevent cleanup by retaining an old MVCC snapshot.

TRUNCATE creates no dead tuples and reclaims the associated table and index storage without requiring VACUUM to clean up deleted rows. Statistics may still need refreshing with ANALYZE, and normal database-wide vacuum processes such as anti-wraparound vacuum remain necessary.

Locks, Dependencies, and Security

TRUNCATE is operationally more disruptive because its ACCESS EXCLUSIVE lock conflicts with ordinary SELECT and write locks. A blocked TRUNCATE can consequently queue behind long-running queries and block other work queued behind it.

Important consequences include:

  • A parent table referenced by a foreign key cannot normally be truncated alone. Include the referencing tables in the same command or use TRUNCATE ... CASCADE.

  • ON DELETE CASCADE does not make TRUNCATE valid because no row-level delete operation occurs.

  • Row-level security policies do not apply to TRUNCATE.

  • Granting DELETE does not grant TRUNCATE; manage the separate TRUNCATE privilege explicitly.

  • ON TRUNCATE triggers can provide limited statement-level auditing, but they cannot implement per-row deletion logic.

Production Monitoring and Recovery

Monitor the effects of bulk DELETE operations through PostgreSQL statistics:

  • pg_stat_user_tables.n_dead_tup: estimated dead tuples.

  • pg_stat_user_tables.n_live_tup: estimated live tuples.

  • pg_stat_user_tables.last_autovacuum: timestamp of the last completed autovacuum.

  • pg_stat_activity.backend_xmin: transaction snapshot horizon that may delay tuple cleanup.

  • pg_stat_activity.xact_start: identifies long-running transactions.

  • pg_database.datfrozenxid and age(datfrozenxid): support transaction-ID wraparound monitoring.

n_dead_tup is an estimate, so correlate it with table size, I/O, vacuum duration, and transaction age. For logged tables, both DELETE and TRUNCATE generate WAL that physical standbys replay. Unlogged-table changes are not WAL-logged or replicated to physical standbys. Logical subscribers receive TRUNCATE only when the publication’s publish operation list includes truncate. PITR can recover a truncated logged table if the required WAL and base backup remain available.

Best Practices and Failure Modes

  • Use DELETE when only a subset must be removed or when per-row triggers, foreign-key actions, and row-level security-based filtering are required.

  • Use TRUNCATE for complete table resets only after checking locks, foreign keys, replication behavior, and privilege requirements.

  • Schedule large deletions in controlled batches to limit WAL generation, replication lag, row locks, and dead-tuple bloat.

  • Alert on sustained dead-tuple growth, autovacuum failures, long transactions, and increasing transaction-ID age.

  • Avoid using VACUUM FULL as an automatic cleanup mechanism; it creates a long ACCESS EXCLUSIVE outage.

  • Treat TRUNCATE as a destructive operation: verify backups, standby state, and PITR coverage before executing it in production.

Sign in to keep reading

You've opened 2 full answers. Sign in to read all 79 PostgreSQL answers, mark what you know and pick up where you left off.

Report a mistake

What's wrong?

Test yourself with multiple-choice questions: PostgreSQL MCQ questions and answers

All Cloud, DevOps & Infrastructure subjects