PostgreSQL Administration¶

Intermediate40 minPrereqs: postgresql-fundamentalsdatabases postgresql

Learning outcomes

Manage roles, privileges, and row-level security policies
Configure VACUUM, ANALYZE, and autovacuum for dead tuple management
Monitor performance with pg_stat views and manage WAL for crash recovery

PostgreSQL gives you more control over your database than almost any other RDBMS - but that control comes with responsibilities. You need to understand how roles and privileges work, how to manage the storage layer with tablespaces, why dead tuples accumulate and how VACUUM reclaims them, what the pg_stat_* views are telling you, which extensions are worth installing, and how WAL keeps your data safe through crashes. This guide covers the daily administration tasks that keep a PostgreSQL instance running well in production.

Roles and Privileges¶

PostgreSQL has a single concept for both users and groups: the role. A role can own database objects, hold privileges, and contain other roles as members. There is no separate CREATE USER and CREATE GROUP - those commands exist for convenience, but they all create roles under the hood.

Creating Roles¶

Use CREATE ROLE to define a new role:

-- A role that can log in (equivalent to CREATE USER)
CREATE ROLE app_user LOGIN PASSWORD 'strong_password_here';

-- A role that cannot log in (used as a group)
CREATE ROLE readonly NOLOGIN;

-- A role with specific attributes
CREATE ROLE admin_user LOGIN PASSWORD 'another_password'
    CREATEDB CREATEROLE VALID UNTIL '2027-01-01';

The key attributes you control at creation time:

Attribute	Description
`LOGIN` / `NOLOGIN`	Whether the role can connect to the database
`SUPERUSER`	Bypasses all permission checks (use sparingly)
`CREATEDB`	Can create new databases
`CREATEROLE`	Can create, alter, and drop other roles
`REPLICATION`	Can initiate streaming replication
`PASSWORD`	Sets the authentication password
`VALID UNTIL`	Password expiration timestamp
`CONNECTION LIMIT`	Maximum concurrent connections for this role

CREATE USER is an alias for CREATE ROLE ... LOGIN. CREATE GROUP is an alias for CREATE ROLE ... NOLOGIN. Use whichever reads more clearly in context.

GRANT and REVOKE¶

Privileges in PostgreSQL are granted at the database, schema, and object level. The GRANT and REVOKE statements control access:

-- Database-level: allow connecting
GRANT CONNECT ON DATABASE myapp TO app_user;

-- Schema-level: allow using objects in the schema
GRANT USAGE ON SCHEMA public TO app_user;

-- Table-level: specific operations
GRANT SELECT, INSERT, UPDATE ON TABLE orders TO app_user;
GRANT ALL PRIVILEGES ON TABLE orders TO admin_user;

-- All tables in a schema at once
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

-- Set default privileges for future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT ON TABLES TO readonly;

-- Revoke access
REVOKE INSERT, UPDATE ON TABLE orders FROM app_user;

Default privileges only affect future objects

ALTER DEFAULT PRIVILEGES applies to objects created after the statement runs. Existing tables are unaffected. When setting up a read-only role, you need both GRANT SELECT ON ALL TABLES for current tables and ALTER DEFAULT PRIVILEGES for tables created later.

Role Membership and Inheritance¶

Roles can be members of other roles, forming a group structure. When a role inherits from another, it automatically gains that role's privileges:

-- Create a group role
CREATE ROLE analysts NOLOGIN;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO analysts;

-- Add members
GRANT analysts TO alice;
GRANT analysts TO bob;

-- alice and bob now inherit SELECT on all public tables

By default, member roles inherit privileges from their parent role. You can control this with INHERIT and NOINHERIT:

-- This role must explicitly SET ROLE to gain privileges
CREATE ROLE operator NOLOGIN NOINHERIT;
GRANT admin_role TO operator;

-- The user must switch role context to use admin_role privileges
SET ROLE admin_role;
-- ... do admin work ...
RESET ROLE;

NOINHERIT is useful when you want a user to consciously activate elevated privileges rather than having them always active - similar to sudo on Linux.

Row-Level Security¶

Row-level security (RLS) lets you control which rows a user can see or modify at the table level, not just which tables they can access. This is powerful for multi-tenant applications where all tenants share a table but should only see their own data.

-- Step 1: Enable RLS on the table
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

-- Step 2: Create policies
CREATE POLICY tenant_isolation ON orders
    USING (tenant_id = current_setting('app.current_tenant')::int);

-- Step 3: Users now only see rows matching the policy
-- The application sets the tenant context per connection:
SET app.current_tenant = '42';
SELECT * FROM orders;  -- Only returns rows where tenant_id = 42

You can create separate policies for different operations:

-- Users can see their own data
CREATE POLICY select_own ON orders
    FOR SELECT
    USING (user_id = current_user);

-- Users can only insert rows attributed to themselves
CREATE POLICY insert_own ON orders
    FOR INSERT
    WITH CHECK (user_id = current_user);

-- Users can update their own rows, but cannot change the user_id
CREATE POLICY update_own ON orders
    FOR UPDATE
    USING (user_id = current_user)
    WITH CHECK (user_id = current_user);

Table owners bypass RLS by default

The table owner and superusers are not subject to row-level security policies. If you want the owner to also be restricted, run ALTER TABLE orders FORCE ROW LEVEL SECURITY. This is important for testing - you may not notice broken policies if you're testing as the table owner.

Tablespace Management¶

A tablespace defines a physical location on the filesystem where PostgreSQL stores data files. By default, PostgreSQL uses two tablespaces:

pg_default - stores user data (under base/ in the data directory)
pg_global - stores cluster-wide system catalogs (under global/ in the data directory)

Creating Tablespaces¶

You can create additional tablespaces to place data on different storage devices - for example, putting frequently accessed indexes on fast SSDs while keeping large archival tables on slower, cheaper disks:

-- The directory must exist and be owned by the postgres OS user
-- On the OS: mkdir -p /mnt/ssd/pgdata && chown postgres:postgres /mnt/ssd/pgdata

CREATE TABLESPACE fast_storage
    OWNER postgres
    LOCATION '/mnt/ssd/pgdata';

CREATE TABLESPACE archive_storage
    LOCATION '/mnt/hdd/pgdata';

Using Tablespaces¶

Assign tablespaces when creating objects or move existing objects:

-- Create a table in a specific tablespace
CREATE TABLE recent_events (
    id bigserial PRIMARY KEY,
    event_type text,
    created_at timestamptz DEFAULT now()
) TABLESPACE fast_storage;

-- Create an index in a specific tablespace
CREATE INDEX idx_events_created
    ON recent_events (created_at)
    TABLESPACE fast_storage;

-- Move an existing table to a different tablespace
ALTER TABLE old_logs SET TABLESPACE archive_storage;

-- Set the default tablespace for a database
ALTER DATABASE myapp SET default_tablespace = 'fast_storage';

Moving tables locks them

ALTER TABLE ... SET TABLESPACE acquires an ACCESS EXCLUSIVE lock. The table is completely unavailable during the move. For large tables, this can take a long time. Plan tablespace moves during maintenance windows, or use logical replication to migrate data with minimal downtime.

Monitoring Tablespace Usage¶

-- List all tablespaces with size
SELECT spcname, pg_size_pretty(pg_tablespace_size(spcname))
FROM pg_tablespace;

-- See which tablespace each table uses
SELECT tablename, tablespace
FROM pg_tables
WHERE schemaname = 'public';
-- NULL tablespace means pg_default

VACUUM and ANALYZE¶

If there is one PostgreSQL administration concept you must understand, it is VACUUM. PostgreSQL's multi-version concurrency control (MVCC) system never overwrites data in place. When you update a row, PostgreSQL creates a new version and marks the old one as "dead." When you delete a row, PostgreSQL marks it as dead but does not remove it. These dead tuples accumulate over time and waste disk space, slow down sequential scans, and cause table bloat.

Why VACUUM Exists¶

Consider what happens without VACUUM:

You update 1 million rows in a table
PostgreSQL creates 1 million new row versions
The old 1 million versions are still on disk, marked as dead
Sequential scans now read 2 million row versions even though only 1 million are live
Indexes still point to both live and dead versions
The table has doubled in size on disk

VACUUM reclaims the space occupied by dead tuples, making it available for reuse by future inserts and updates within the same table.

VACUUM vs VACUUM FULL¶

PostgreSQL offers two flavors:

	`VACUUM`	`VACUUM FULL`
Locking	Runs alongside reads and writes	Locks the table exclusively
Space reclamation	Marks dead tuple space as reusable within the table	Rewrites the entire table, releasing space to the OS
Speed	Fast, designed to run frequently	Slow, especially on large tables
When to use	Regular maintenance (autovacuum does this)	Only when a table has extreme bloat and you need to shrink the file on disk
Downtime	None	Table is unavailable for the duration

In practice, you should almost never need VACUUM FULL. Regular VACUUM keeps dead tuple space recyclable. VACUUM FULL is a last resort for tables that grew extremely large due to a bulk operation and you need the disk space back.

ANALYZE¶

ANALYZE collects statistics about the distribution of data in a table's columns. The query planner uses these statistics to choose efficient execution plans - deciding whether to use an index scan, sequential scan, hash join, or merge join.

-- Analyze a specific table
ANALYZE orders;

-- Analyze specific columns
ANALYZE orders (customer_id, created_at);

-- VACUUM and ANALYZE together
VACUUM ANALYZE orders;

Stale statistics lead to bad query plans. If you load a large batch of data or significantly change the data distribution, running ANALYZE immediately afterward helps the planner make good decisions.

Autovacuum Configuration¶

PostgreSQL runs autovacuum as a background process that automatically triggers VACUUM and ANALYZE when tables accumulate enough changes. The key configuration parameters in postgresql.conf:

# Master switch (leave this on)
autovacuum = on

# How often the autovacuum launcher checks for work (seconds)
autovacuum_naptime = 60

# VACUUM triggers when dead tuples exceed:
#   autovacuum_vacuum_threshold + (autovacuum_vacuum_scale_factor * table rows)
autovacuum_vacuum_threshold = 50        # base number of dead tuples
autovacuum_vacuum_scale_factor = 0.2    # fraction of table size

# ANALYZE triggers when changed tuples exceed:
#   autovacuum_analyze_threshold + (autovacuum_analyze_scale_factor * table rows)
autovacuum_analyze_threshold = 50
autovacuum_analyze_scale_factor = 0.1

# Maximum number of concurrent autovacuum workers
autovacuum_max_workers = 3

With the defaults, a table with 10,000 rows triggers autovacuum when it accumulates more than 50 + (0.2 * 10,000) = 2,050 dead tuples. For a table with 10 million rows, the threshold is 50 + (0.2 * 10,000,000) = 2,000,050 dead tuples - which means a lot of bloat accumulates before autovacuum kicks in.

For large, heavily-updated tables, you often need per-table overrides:

-- More aggressive autovacuum for a high-traffic table
ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor = 0.01,
    autovacuum_analyze_scale_factor = 0.005,
    autovacuum_vacuum_threshold = 100
);

Monitoring Dead Tuples¶

The pg_stat_user_tables view tells you how well autovacuum is keeping up:

SELECT
    schemaname,
    relname,
    n_live_tup,
    n_dead_tup,
    round(n_dead_tup::numeric / NULLIF(n_live_tup, 0) * 100, 2) AS dead_pct,
    last_vacuum,
    last_autovacuum,
    last_analyze,
    last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

If n_dead_tup is consistently high and last_autovacuum is far in the past, autovacuum may be falling behind. Common causes: too few autovacuum_max_workers, thresholds set too high, or long-running transactions holding back the oldest transaction horizon (preventing VACUUM from reclaiming tuples that might still be visible to them).

A table has 5 million rows and the autovacuum_vacuum_scale_factor is set to the default 0.2. Approximately how many dead tuples must accumulate before autovacuum triggers a VACUUM? (requires JavaScript)

Invalid interactive component configuration (terminal)

pg_stat_* Monitoring Views¶

PostgreSQL provides a rich set of statistics views prefixed with pg_stat_ that give you visibility into what the database is doing. These views are your primary tool for understanding performance, diagnosing problems, and capacity planning.

pg_stat_activity¶

The pg_stat_activity view shows one row per active server process - every connection, what it is doing, and how long it has been doing it:

-- Active queries right now
SELECT
    pid,
    usename,
    datname,
    state,
    wait_event_type,
    wait_event,
    now() - query_start AS query_duration,
    left(query, 80) AS query_preview
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY query_start;

Key columns:

Column	What it tells you
`state`	`active`, `idle`, `idle in transaction`, `fastpath function call`
`wait_event_type`	Why the process is waiting: `Lock`, `IO`, `LWLock`, `BufferPin`, etc.
`wait_event`	Specific wait: `relation`, `transactionid`, `DataFileRead`, etc.
`query_start`	When the current query began executing
`xact_start`	When the current transaction started (important for long transactions)
`backend_type`	`client backend`, `autovacuum worker`, `walwriter`, etc.

Watch for idle in transaction connections - they hold locks and prevent VACUUM from reclaiming dead tuples. If xact_start is far in the past for an idle in transaction session, that connection is a problem.

-- Find long-running transactions blocking VACUUM
SELECT
    pid,
    usename,
    state,
    now() - xact_start AS transaction_age,
    left(query, 60) AS last_query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
    AND now() - xact_start > interval '5 minutes'
ORDER BY xact_start;

pg_stat_user_tables¶

This view provides per-table I/O and maintenance statistics:

SELECT
    relname,
    seq_scan,
    seq_tup_read,
    idx_scan,
    idx_tup_fetch,
    n_tup_ins,
    n_tup_upd,
    n_tup_del,
    n_dead_tup
FROM pg_stat_user_tables
ORDER BY seq_scan DESC
LIMIT 10;

A table with high seq_scan counts and many rows probably needs an index. Compare seq_scan to idx_scan - if sequential scans dominate on a large table, the planner is not finding useful indexes for common queries.

pg_stat_user_indexes¶

Shows how much each index is actually used:

-- Find unused indexes (candidates for removal)
SELECT
    schemaname,
    relname AS table_name,
    indexrelname AS index_name,
    idx_scan,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

Unused indexes waste disk space and slow down writes (every INSERT, UPDATE, and DELETE must update all indexes). Periodically auditing for zero-scan indexes and dropping them is a valuable maintenance task.

pg_stat_bgwriter¶

The background writer and checkpointer flush dirty buffers to disk. This view shows how that work is distributed:

SELECT
    checkpoints_timed,
    checkpoints_req,
    buffers_checkpoint,
    buffers_clean,
    buffers_backend,
    buffers_alloc
FROM pg_stat_bgwriter;

If buffers_backend is high relative to buffers_checkpoint and buffers_clean, client processes are being forced to write dirty pages themselves instead of the background writer handling it. This indicates the background writer cannot keep up and you may need to tune bgwriter_lru_maxpages or bgwriter_lru_multiplier.

If checkpoints_req is high relative to checkpoints_timed, checkpoints are being forced by WAL volume rather than happening on schedule. Increase max_wal_size to reduce forced checkpoints.

pg_stat_replication¶

When running streaming replication, this view shows the state of each standby:

SELECT
    client_addr,
    state,
    sent_lsn,
    write_lsn,
    flush_lsn,
    replay_lsn,
    sent_lsn - replay_lsn AS replay_lag_bytes,
    write_lag,
    flush_lag,
    replay_lag
FROM pg_stat_replication;

The lag columns (write_lag, flush_lag, replay_lag) show time-based lag at each stage. If replay_lag is growing, the standby is falling behind - often due to heavy write load, slow standby disk, or a long-running query on the standby blocking replay.

Extensions¶

One of PostgreSQL's greatest strengths is its extension system. Extensions add data types, functions, operators, index methods, and more - all without modifying the core server. PostgreSQL ships with a set of contrib extensions, and hundreds more are available from the community.

Installing Extensions¶

Extensions must first be available on the filesystem (installed as shared libraries), then loaded into a specific database:

-- See what's available
SELECT name, default_version, comment
FROM pg_available_extensions
ORDER BY name;

-- Install an extension
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Check installed extensions
SELECT extname, extversion FROM pg_extension;

-- Update an extension to a new version
ALTER EXTENSION pg_stat_statements UPDATE;

-- Remove an extension
DROP EXTENSION pg_stat_statements;

pg_stat_statements¶

pg_stat_statements tracks execution statistics for every SQL statement executed by the server. It is the single most valuable extension for performance analysis.

To enable it, add to postgresql.conf:

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all

Then restart PostgreSQL and create the extension:

CREATE EXTENSION pg_stat_statements;

-- Top 10 queries by total execution time
SELECT
    left(query, 80) AS query,
    calls,
    round(total_exec_time::numeric, 2) AS total_ms,
    round(mean_exec_time::numeric, 2) AS avg_ms,
    rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- Reset statistics
SELECT pg_stat_statements_reset();

This tells you which queries consume the most time in aggregate - not just the slowest individual query, but the ones that are called thousands of times and add up. A query that takes 2 ms but runs 500,000 times per day (1,000 seconds total) matters more than one that takes 5 seconds but runs once.

pgcrypto¶

pgcrypto provides cryptographic functions directly in SQL - hashing, encryption, and random data generation:

CREATE EXTENSION pgcrypto;

-- Hash a password with bcrypt (blowfish)
SELECT crypt('user_password', gen_salt('bf'));

-- Verify a password
SELECT (stored_hash = crypt('user_input', stored_hash)) AS password_valid;

-- Generate random bytes
SELECT gen_random_bytes(16);

-- Generate a random UUID (also available via gen_random_uuid() in PG 13+)
SELECT gen_random_uuid();

-- Symmetric encryption
SELECT pgp_sym_encrypt('sensitive data', 'encryption_key');
SELECT pgp_sym_decrypt(encrypted_column, 'encryption_key');

PostGIS¶

PostGIS adds geospatial data types, indexes, and functions - turning PostgreSQL into a full geographic information system. It is one of the most capable open-source geospatial databases available:

CREATE EXTENSION postgis;

-- Store a point (longitude, latitude in WGS 84)
CREATE TABLE locations (
    id serial PRIMARY KEY,
    name text,
    geom geometry(Point, 4326)
);

INSERT INTO locations (name, geom)
VALUES ('Office', ST_SetSRID(ST_MakePoint(-73.9857, 40.7484), 4326));

-- Find locations within 5 km of a point
SELECT name, ST_Distance(
    geom::geography,
    ST_SetSRID(ST_MakePoint(-73.9851, 40.7580), 4326)::geography
) AS distance_meters
FROM locations
WHERE ST_DWithin(
    geom::geography,
    ST_SetSRID(ST_MakePoint(-73.9851, 40.7580), 4326)::geography,
    5000
);

pg_trgm¶

pg_trgm provides trigram-based text similarity functions and index support. It is excellent for fuzzy text matching, "did you mean?" features, and typo-tolerant searches:

CREATE EXTENSION pg_trgm;

-- Similarity score (0 to 1)
SELECT similarity('PostgreSQL', 'PostgerSQL');  -- ~0.6

-- Find similar product names
SELECT name, similarity(name, 'PostgreSQ') AS sim
FROM products
WHERE similarity(name, 'PostgreSQ') > 0.3
ORDER BY sim DESC;

-- Create a GIN index for fast trigram searches
CREATE INDEX idx_products_name_trgm
    ON products USING gin (name gin_trgm_ops);

-- Use LIKE/ILIKE with trigram index acceleration
SELECT * FROM products WHERE name ILIKE '%postgres%';

Combine pg_trgm with full-text search

Use pg_trgm for fuzzy matching on short strings (names, titles) and PostgreSQL's built-in full-text search (tsvector/tsquery) for document-length content. They complement each other well - trigrams catch typos and abbreviations that full-text search misses.

WAL Management¶

The Write-Ahead Log (WAL) is PostgreSQL's mechanism for ensuring data durability and crash recovery. The core principle: before any change is written to the actual data files, it is first written to the WAL. If PostgreSQL crashes, it replays the WAL on startup to recover any changes that were committed but not yet flushed to the data files.

How WAL Works¶

Every transaction that modifies data generates WAL records. These records are written sequentially to files in the pg_wal/ directory (called pg_xlog/ before PostgreSQL 10). The sequence of events:

Transaction modifies data in shared buffers (memory)
WAL records describing the changes are written to WAL buffers
At commit, WAL buffers are flushed to WAL files on disk (fsync)
The commit is acknowledged to the client
Later, the background writer or checkpointer writes the actual data pages to disk

Because WAL writes are sequential (append-only) and data file writes are random, this design converts expensive random I/O into cheap sequential I/O for the critical commit path. The actual data pages are written asynchronously in the background.

wal_level Settings¶

The wal_level parameter controls how much information is written to WAL:

Level	What it includes	Use case
`minimal`	Only enough for crash recovery	Standalone server with no replication or archiving
`replica`	Adds data needed for WAL archiving and replication	Default since PostgreSQL 10; required for standby servers
`logical`	Adds data for logical decoding	Required for logical replication, change data capture

# postgresql.conf
wal_level = replica    # The safe default for most deployments

Changing wal_level requires a server restart.

WAL Archiving¶

WAL archiving copies completed WAL files to a safe location before they are recycled. Combined with a base backup, archived WAL files enable point-in-time recovery (PITR) - restoring a database to any moment in time.

# postgresql.conf
archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/wal_archive/%f'

The %p placeholder is the full path to the WAL file being archived. %f is just the filename. The archive_command must return exit code 0 on success - PostgreSQL will retry on failure and will not recycle a WAL file until it has been successfully archived.

For production systems, use a more robust archive command that copies to remote storage:

# Archive to S3
archive_command = 'aws s3 cp %p s3://my-wal-archive/%f'

# Archive with pgBackRest
archive_command = 'pgbackrest --stanza=mydb archive-push %p'

# Archive with Barman
archive_command = 'barman-wal-archive backup-server mydb %p'

WAL Directory Management¶

WAL files are 16 MB each by default. Under heavy write load, the pg_wal/ directory can grow significantly. Key parameters:

# Soft target for total WAL size (checkpoints try to stay under this)
max_wal_size = 1GB

# Minimum WAL to retain (even if not needed for recovery)
min_wal_size = 80MB

Monitor WAL directory size:

-- Current WAL LSN (Log Sequence Number) position
SELECT pg_current_wal_lsn();

-- Size of pg_wal directory
SELECT pg_size_pretty(sum(size))
FROM pg_ls_waldir();

-- Number of WAL files
SELECT count(*) FROM pg_ls_waldir();

Never manually delete files from pg_wal

If pg_wal/ is filling your disk, do not delete WAL files by hand. This can cause data loss or prevent crash recovery. Instead, investigate why WAL is accumulating: a failed archive_command, a replication slot preventing WAL recycling, or max_wal_size set too low. Fix the root cause, and PostgreSQL will recycle old WAL files automatically.

WAL and Replication Slots¶

Replication slots prevent PostgreSQL from recycling WAL files that a standby server or logical consumer still needs. Without slots, a slow standby might fall behind and find that the WAL it needs has been recycled - breaking replication.

-- Create a physical replication slot
SELECT pg_create_physical_replication_slot('standby1');

-- View replication slots and their WAL retention
SELECT
    slot_name,
    slot_type,
    active,
    pg_size_pretty(
        pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)
    ) AS retained_wal
FROM pg_replication_slots;

-- Drop a slot that's no longer needed (releases retained WAL)
SELECT pg_drop_replication_slot('standby1');

An inactive replication slot is one of the most common causes of pg_wal/ growing unbounded. If a standby is decommissioned without dropping its slot, WAL accumulates indefinitely.

Putting It Together: Diagnosing Bloat¶

The topics in this guide are interconnected. Dead tuples accumulate because of MVCC. VACUUM cleans them up. pg_stat_user_tables tells you whether VACUUM is keeping up. Long-running transactions (visible in pg_stat_activity) prevent VACUUM from reclaiming tuples. pg_stat_statements reveals the queries causing the most churn. Understanding these relationships is the core of PostgreSQL administration.