PostgreSQL database administration for web application

PostgreSQL Database Administration for Web Application

PostgreSQL — not "install and forget". By default configured conservatively for wide hardware compatibility. Without regular maintenance: tables bloat from dead tuples, indexes fragment, bloat takes gigabytes, slow queries drag down entire app. System administration — set of regular tasks and continuous monitoring.

Initial Installation Audit

First thing when connecting to new database:

-- Version and configuration
SELECT version();
SHOW config_file;
SHOW data_directory;

-- Database sizes
SELECT datname,
       pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
ORDER BY pg_database_size(datname) DESC;

-- Top-10 tables by size with bloat
SELECT schemaname,
       relname,
       pg_size_pretty(pg_total_relation_size(relid)) AS total,
       pg_size_pretty(pg_relation_size(relid))       AS table,
       pg_size_pretty(pg_indexes_size(relid))        AS indexes
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 10;

-- Tables with dead tuples (VACUUM candidates)
SELECT relname, n_dead_tup, n_live_tup,
       round(n_dead_tup::numeric / nullif(n_live_tup + n_dead_tup, 0) * 100, 1) AS dead_pct,
       last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

VACUUM and ANALYZE

Autovacuum works in background, but sometimes settings insufficient for high-load tables:

-- Manual vacuum for specific table
VACUUM (VERBOSE, ANALYZE) orders;

-- For table with huge bloat — FULL (exclusive lock!)
-- Run in maintenance window
VACUUM FULL orders;

VACUUM FULL locks table entire duration. For production without window — use pg_repack:

# Defragmentation without lock
pg_repack -h localhost -U postgres -d mydb -t orders

Autovacuum tuning for frequently updated tables:

ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor = 0.01,  -- vacuum at 1% dead (vs 20% default)
    autovacuum_analyze_scale_factor = 0.005,
    autovacuum_vacuum_cost_delay = 2        -- ms, more aggressive
);

Index Management

-- Unused indexes (waste space and slow INSERT/UPDATE)
SELECT schemaname, relname, indexrelname,
       idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) AS idx_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelname NOT LIKE '%_pkey'
ORDER BY pg_relation_size(indexrelid) DESC;

-- Duplicate indexes
SELECT a.indexrelid::regclass AS index1,
       b.indexrelid::regclass AS index2,
       a.indrelid::regclass   AS table_name
FROM pg_index a
JOIN pg_index b ON a.indrelid = b.indrelid
               AND a.indexrelid < b.indexrelid
               AND a.indkey = b.indkey;

-- Create index without write lock
CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id);

CONCURRENTLY mandatory on production — standard CREATE INDEX locks writes.

Backups

# pg_dump — logical backup of specific database
pg_dump -h localhost -U postgres -Fc -Z 9 mydb > /backups/mydb_$(date +%Y%m%d_%H%M).dump

# Restore
pg_restore -h localhost -U postgres -d mydb_restored /backups/mydb_20250101_0300.dump

# pg_basebackup — physical backup for PITR
pg_basebackup -h localhost -U replication -D /backups/base -Ft -z -P

Schedule via cron:

# /etc/cron.d/postgres-backup
0 3 * * * postgres pg_dump -Fc mydb | gzip > /backups/mydb_$(date +\%Y\%m\%d).dump.gz
# Rotate: keep 30 days
0 4 * * * find /backups/ -name "*.dump.gz" -mtime +30 -delete

Test restore regularly — at least monthly on isolated environment.

Replication (Streaming Replication)

# postgresql.conf on primary
wal_level = replica
max_wal_senders = 3
wal_keep_size = 1GB

# pg_hba.conf
host replication replication 10.0.0.2/32 scram-sha-256

# Initialize replica
pg_basebackup -h 10.0.0.1 -U replication -D /var/lib/postgresql/14/main \
  -P -Xs -R
# -R creates standby.signal and recovery.conf automatically

Monitor replication lag:

-- On primary
SELECT client_addr,
       state,
       sent_lsn,
       write_lsn,
       flush_lsn,
       replay_lsn,
       (sent_lsn - replay_lsn) AS replication_lag_bytes
FROM pg_stat_replication;

Connection Management: PgBouncer

PostgreSQL scales poorly with thousands concurrent connections — each takes ~5–10 MB memory. PgBouncer solves via pooling:

# /etc/pgbouncer/pgbouncer.ini
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
pool_mode        = transaction  # transaction pooling — most efficient
max_client_conn  = 1000
default_pool_size = 25
reserve_pool_size = 5
server_idle_timeout = 600

App connects to PgBouncer on port 6432, not directly to PostgreSQL.

Regular Tasks

Roles and Permissions

-- Principle of least privilege
CREATE ROLE app_user LOGIN PASSWORD 'strong_password';
GRANT CONNECT ON DATABASE mydb TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_user;

-- For future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;

-- Read-only role for analytics/reports
CREATE ROLE readonly_user LOGIN PASSWORD 'password';
GRANT CONNECT ON DATABASE mydb TO readonly_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;

Major Version Upgrade

PostgreSQL doesn't upgrade between major versions via apt upgrade. Use pg_upgrade:

# Example: 14 -> 16
pg_upgrade \
  -b /usr/lib/postgresql/14/bin \
  -B /usr/lib/postgresql/16/bin \
  -d /var/lib/postgresql/14/main \
  -D /var/lib/postgresql/16/main \
  --link  # hard links instead of copy — faster, but 14 can't run after

For zero downtime — logical replication between old and new, switch when LSNs match.