Database Administration

Database Administration is the practice of ensuring databases are available, performant, consistent, and recoverable across the lifecycle of a system. It matters when data is the core of the business—when a query latency of 200ms or a 30-minute downtime has tangible impact on users, revenue, or operations.

Schema Evolution and Migration

Database schema changes are the most common touchpoints between developers and DBAs. The process begins with a migration script written in SQL, executed in a consistent order across environments.

A typical migration is a file named 2024-03-15-add-user-roles.sql with contents:

-- Add roles column to users table
ALTER TABLE users ADD COLUMN roles TEXT[] NOT NULL DEFAULT '{}';

-- Add index for role-based queries
CREATE INDEX idx_users_roles ON users USING GIN (roles);

-- Populate existing users with default role
UPDATE users SET roles = ARRAY['user'] WHERE roles IS NULL;

Migration tools like Flyway or Liquibase use a schema_version table to track which migrations have been applied. A migration fails if the schema_version table does not exist or if the version column is not indexed and the current migration is not atomic.

The most common failure is a migration that runs but leaves the database in a state where the schema is updated, but the version is not recorded. This occurs when the migration script executes BEGIN; but the COMMIT; is missing or fails due to a network timeout.

To prevent this, migrations should be wrapped in a transaction and the version update should be part of the same transaction:

BEGIN;

-- Apply schema changes
ALTER TABLE users ADD COLUMN roles TEXT[] NOT NULL DEFAULT '{}';

-- Update schema version
INSERT INTO schema_version (version, description, applied_at)
VALUES (20240315, 'Add user roles', NOW());

-- Ensure atomicity: version update is part of the migration
COMMIT;

If the schema_version table is in a different database, or the connection drops after INSERT, the migration appears to succeed but the version is not updated. The solution is to use a schema_version table in the same database, and to set transaction_isolation = 'SERIALIZABLE' at the start of the migration.

A sharp edge is the difference between ALTER TABLE ... ADD COLUMN and ALTER TABLE ... ADD COLUMN IF NOT EXISTS. The latter is idempotent but does not update the schema_version table, so a repeated deployment may apply the same migration twice. Use IF NOT EXISTS only when the migration is meant to be idempotent; otherwise, prefer ADD COLUMN with a CREATE TABLE IF NOT EXISTS step.

Query Performance Tuning

Query performance is measured at the database level, not the application level. The slowest query in a system is often not the one that appears in the logs, because the database does not log every query by default.

Enable query logging via log_statement = 'all' and log_min_duration_statement = 100 in postgresql.conf. This captures every query that takes 100 milliseconds or more.

A query that returns a large result set slowly is often due to a missing index or a poorly chosen execution plan. To analyze a query, use EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON):

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT u.name, p.title, p.published_at
FROM users u
JOIN posts p ON u.id = p.author_id
WHERE p.published_at > '2024-01-01'
  AND u.active = true
ORDER BY p.published_at DESC
LIMIT 50;

The output includes actual_time, rows, loops, and shared_read_time—key metrics for diagnosing bottlenecks.

The most common failure is a query that performs well in development but poorly in production, due to a mismatch in data distribution. A WHERE p.published_at > '2024-01-01' may be fast on 1000 rows but slow on 1 million. The solution is to gather statistics with ANALYZE posts; and to update the pg_statistic table with VACUUM ANALYZE posts;.

A sharp edge is the difference between VACUUM and VACUUM ANALYZE. VACUUM reclaims space from deleted rows but does not update statistics. VACUUM ANALYZE does both. Use VACUUM ANALYZE after bulk inserts and VACUUM during regular maintenance.

Another sharp edge is the work_mem setting. If work_mem is too low, queries perform sort and hash operations in temporary files instead of memory. A query with GROUP BY may double in execution time when work_mem is set to 1MB instead of 8MB.

To tune work_mem, set it in postgresql.conf and monitor pg_stat_bgwriter for buffers_written and checkpoints_timed. A high number of buffers_written indicates that background writer is flushing memory to disk frequently, often due to insufficient work_mem.

Backup and Recovery

Backups are the primary defense against data loss. The most reliable backup strategy uses a combination of full, differential, and incremental backups.

A full backup is created with pg_basebackup:

pg_basebackup -D /backups/base-2024-03-15 \
              -Ft \
              -z \
              -P \
              -h db.example.com \
              -U backup_user \
              -X stream \
              -v

The -X stream flag ensures that the WAL (Write-Ahead Log) is streamed during the backup, so the backup is consistent and can be restored to the exact point in time.

After the full backup, incremental backups are created with pg_start_backup() and pg_stop_backup():

-- Start incremental backup
SELECT pg_start_backup('incremental-2024-03-15-14:30');

-- Copy data files (e.g., via rsync)
rsync -av /var/lib/postgresql/15/main/ /backups/incremental-2024-03-15-14:30/

-- Stop backup
SELECT pg_stop_backup();

The pg_stop_backup() function returns the WAL location and checkpoint LSN, which are used during recovery to identify the full backup and the set of WAL files needed to restore to a specific time.

Recovery is performed by restoring the full backup and replaying the WAL files. The recovery process is configured via recovery.conf:

restore_command = 'cp /archived_wal/%f %p'
archive_command = 'cp %p /archived_wal/%f'
recovery_target_time = '2024-03-15 15:00:00'
recovery_target_action = 'promote'

The most common failure is a missing archive_command or restore_command. If the archive_command fails, WAL files are not archived, and the recovery process may not be able to restore to the desired time.

A sharp edge is the difference between recovery_target_time and recovery_target_lsn. recovery_target_time restores the database to the nearest available checkpoint after the given time. recovery_target_lsn restores to an exact WAL location, which is useful for point-in-time recovery.

Another sharp edge is the standby_mode setting. When standby_mode = on, the database starts in recovery mode and applies WAL from a primary server. The recovery process waits for WAL files to arrive, and a standby.signal file must be present in the data directory.

A critical failure mode occurs when the recovery.conf file is missing or misnamed. The database starts but does not enter recovery mode, and the system remains in a stale state, unable to apply WAL changes.

High Availability and Failover

High availability requires a primary database and one or more replicas. The primary handles all writes; replicas handle reads and serve as failover targets.

Replication is configured via postgresql.conf on both primary and replica:

wal_level = replica
synchronous_commit = on
synchronous_standby_names = 'replica1, replica2'

# Replica
primary_conninfo = 'host=primary.example.com port=5432 user=replica_user'
recovery_target_timeline = 'latest'

The synchronous_standby_names setting defines a list of replicas that must acknowledge a write before the transaction is committed. A write on the primary is not complete until at least one synchronous standby acknowledges the WAL record.

A sharp edge is the difference between synchronous_commit = on and synchronous_commit = remote_apply. With on, the write is committed when the WAL is flushed to disk on the primary. With remote_apply, the write is committed only when the WAL is applied on the replica.

Another sharp edge is the recovery_target_timeline setting. When a replica is promoted to primary, it creates a new timeline. The recovery_target_timeline setting can be latest, specific, or current. latest restores to the newest timeline, specific to a named timeline, and current to the timeline of the current recovery process.

Failover is triggered by promoting a replica to primary. The process is initiated by creating a promote.signal file in the data directory:

touch /var/lib/postgresql/15/main/promote.signal

The replica then reads the recovery.conf, applies all WAL, and starts serving as primary. The promote.signal file is automatically deleted during the promotion.

A common failure is a replica that fails to promote due to a missing recovery.conf file or an incorrect primary_conninfo. The replica starts but does not connect to the primary, and the pg_stat_replication view shows state = 'startup' and sync_state = 'async'.

Another failure mode is a split-brain scenario, where multiple replicas believe they are the primary. This occurs when the primary fails and multiple replicas promote, but the network splits so that all replicas see a different primary. The solution is to use a quorum-based voting system, such as a shared file lock or a distributed lock via pg_advisory_lock.

Monitoring and Alerting

Monitoring is the practice of observing database metrics and reacting to deviations. The most essential metrics are available via pg_stat_database, pg_stat_bgwriter, and pg_stat_user_tables.

A basic monitoring query collects key metrics:

SELECT
  datname,
  numbackends,
  tup_inserted,
  tup_updated,
  tup_deleted,
  blks_read,
  blks_hit,
  xact_commit,
  xact_rollback,
  stats_reset
FROM pg_stat_database
WHERE datname = 'app_db';

This query is executed every 5 minutes by a monitoring tool like cron, systemd, or Prometheus.

The most common alert is a rising blks_read to blks_hit ratio. A ratio greater than 0.8 indicates that the database is reading data from disk frequently, suggesting that the buffer cache is insufficient.

Another alert is a rising checkpoint_write_time in pg_stat_bgwriter. If checkpoint_write_time exceeds 500ms, it indicates that the background writer is taking too long to flush dirty pages to disk, which can block the main database operations.

A sharp edge is the difference between checkpoint_timeout and checkpoint_completion_target. checkpoint_timeout sets the maximum time between checkpoints. checkpoint_completion_target sets the fraction of the checkpoint interval that should be spent writing checkpoint data.

When checkpoint_completion_target is too low, the background writer starts writing too early, but the final checkpoint does not complete until the end of the interval. When it is too high, the background writer writes slowly, but the final checkpoint arrives late.

A critical failure mode is a missing checkpoint_timeout setting. The database defaults to 5 minutes, but after a large bulk load, checkpoint_timeout is increased to 15 minutes. The background writer starts writing, but the final checkpoint does not complete before the next one begins. This leads to overlapping checkpoints and increased I/O pressure.

Another sharp edge is the pg_stat_statements extension. Enable it with CREATE EXTENSION pg_stat_statements; and configure it via shared_preload_libraries and pg_settings:

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = 'all'
pg_stat_statements.save = true

The pg_stat_statements view tracks execution time, calls, and I/O for every SQL statement. A query that runs slowly in production but is not in the logs is often visible in pg_stat_statements but not in pg_stat_database.

Schema and Data Consistency

Schema and data consistency are maintained through a combination of constraints, triggers, and periodic validation.

A foreign key constraint ensures referential integrity:

ALTER TABLE posts
ADD CONSTRAINT fk_posts_author_id
FOREIGN KEY (author_id) REFERENCES users(id)
ON DELETE CASCADE;

The constraint is enforced at the database level, but the most common failure is a FOREIGN KEY violation that is not logged. The error message is ERROR: insert or update on table "posts" violates foreign key constraint "fk_posts_author_id", but the detail field contains the missing author_id.

To ensure that constraints are not only defined but also enforced, use CHECK CONSTRAINT and VALIDATE CONSTRAINT:

ALTER TABLE users
VALIDATE CONSTRAINT uk_users_email;

-- Check all constraints on posts
ALTER TABLE posts VALIDATE CONSTRAINT;

The VALIDATE CONSTRAINT command scans the entire table and returns the number of constraint violations. It can be run as a maintenance job.

A sharp edge is the difference between CHECK constraints and TRIGGER functions. CHECK constraints are evaluated during INSERT and UPDATE and are part of the transaction. TRIGGER functions are executed after the row is inserted or updated and can perform complex logic.

A common failure is a TRIGGER that fails to update a related table but does not raise an error. The trigger function runs, but an exception is silently swallowed. The solution is to use RAISE EXCEPTION and to set session_replication_role = 'replica' to ensure that triggers are not executed during replication.

Another sharp edge is the pg_prewarm extension. After a database restart or a major schema change, the most frequently accessed tables are not in memory. pg_prewarm loads tables into the buffer cache:

SELECT pg_prewarm('users');
SELECT pg_prewarm('posts', '100000');

The pg_prewarm function returns the number of pages loaded and the time taken. It is used in pg_cron jobs to warm up the database after a restart.

A critical failure mode is a pg_prewarm job that runs but does not complete. The pg_prewarm function returns quickly, but the background worker does not load all pages. The solution is to use pg_prewarm with pg_stat_activity to monitor the progress of the prewarm job.

This page was rewritten on 18 October 2026. It replaced a templated version that was not specific to its own title. How these pages are written and checked. If anything here is wrong, tell us at [email protected] and we will correct it.