PostgreSQL Database Setup for Web Application

PostgreSQL Database Setup for Web Applications

PostgreSQL is the standard for most web applications needing reliable relational DB with JSON support, full-text search, extensions, and complex queries. Proper setup from the start saves significant time as load grows.

Installation and basic configuration

On Ubuntu 24.04 / Debian 12:

apt install -y postgresql-16 postgresql-client-16
systemctl enable postgresql
systemctl start postgresql

Creating database and user:

CREATE USER myapp WITH PASSWORD 'strong_password_here';
CREATE DATABASE myapp_production OWNER myapp;
GRANT ALL PRIVILEGES ON DATABASE myapp_production TO myapp;

-- Connect to DB and grant schema rights
\c myapp_production
GRANT ALL ON SCHEMA public TO myapp;

Key postgresql.conf parameters

Defaults assume 256 MB RAM. For production minimum:

# /etc/postgresql/16/main/postgresql.conf

# Memory (for 8 GB RAM server)
shared_buffers = 2GB              # 25% RAM
effective_cache_size = 6GB        # 75% RAM
work_mem = 64MB                   # for sorting, hash join
maintenance_work_mem = 512MB      # for VACUUM, CREATE INDEX

# Checkpoint
checkpoint_completion_target = 0.9
wal_buffers = 64MB
max_wal_size = 2GB
min_wal_size = 512MB

# Parallelism
max_worker_processes = 8
max_parallel_workers_per_gather = 4
max_parallel_workers = 8

# Connections
max_connections = 200             # with pgBouncer — can be less
shared_preload_libraries = 'pg_stat_statements'

# Slow query logging
log_min_duration_statement = 1000  # 1 second
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '

Connection pooling with pgBouncer

Direct PostgreSQL connections are expensive: each is a separate process. pgBouncer multiplexes them:

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

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction          # best mode for most apps
max_client_conn = 1000
default_pool_size = 25
reserve_pool_size = 5
server_idle_timeout = 600
log_connections = 0
log_disconnections = 0

Application connects to pgBouncer on port 6432, not PostgreSQL on 5432.

Indexes

-- Simple indexes on frequently used fields
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);
CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id);
CREATE INDEX CONCURRENTLY idx_orders_created_at ON orders(created_at DESC);

-- Partial index — only active records
CREATE INDEX CONCURRENTLY idx_sessions_active ON sessions(user_id, expires_at)
WHERE revoked_at IS NULL;

-- Composite index for typical WHERE + ORDER BY
CREATE INDEX CONCURRENTLY idx_products_category_price
ON products(category_id, price) WHERE deleted_at IS NULL;

-- GIN index for JSONB
CREATE INDEX CONCURRENTLY idx_orders_metadata ON orders USING gin(metadata);

-- Full-text search
CREATE INDEX CONCURRENTLY idx_articles_search
ON articles USING gin(to_tsvector('russian', title || ' ' || body));

Partitioning for large tables

-- Events table with monthly partitioning
CREATE TABLE events (
    id          bigint GENERATED ALWAYS AS IDENTITY,
    user_id     int NOT NULL,
    event_type  text NOT NULL,
    payload     jsonb,
    created_at  timestamptz NOT NULL DEFAULT now()
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2024_01 PARTITION OF events
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE events_2024_02 PARTITION OF events
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

-- Automatic partition creation via pg_partman
CREATE EXTENSION IF NOT EXISTS pg_partman;
SELECT partman.create_parent('public.events', 'created_at', 'native', 'monthly');

Monitoring slow queries

-- Enable pg_stat_statements (in shared_preload_libraries)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Top-10 slow queries
SELECT
    round((total_exec_time / 1000)::numeric, 2) AS total_sec,
    round((mean_exec_time)::numeric, 2) AS mean_ms,
    calls,
    round(rows::numeric / calls, 1) AS rows_per_call,
    left(query, 120) AS query
FROM pg_stat_statements
WHERE calls > 10
ORDER BY total_exec_time DESC
LIMIT 10;

-- Unused indexes
SELECT
    schemaname,
    tablename,
    indexname,
    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;

Replication

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

# Create replication user
CREATE USER replicator REPLICATION LOGIN PASSWORD 'repl_password';

# On replica — pg_basebackup
pg_basebackup -h primary_host -U replicator -D /var/lib/postgresql/16/main -P -R -X stream

Backup

# pg_dump for logical backups
pg_dump -Fc -Z 9 myapp_production > backup_$(date +%Y%m%d_%H%M%S).dump

# WAL-G for continuous archiving to S3
export WALG_S3_PREFIX=s3://my-bucket/postgres-wal
export AWS_REGION=eu-central-1
wal-g backup-push /var/lib/postgresql/16/main

Timeline

Setup and basic tuning for specific load: 1–2 days. pgBouncer, replication, monitoring setup: 2–3 days. Existing DB migration with hot standby and automatic backups: 3–5 days depending on data volume.