PostgreSQL

PostgreSQL is an advanced, open-source object-relational database management system with nearly 40 years of active development (origins dating to 1986 at UC Berkeley). It has earned a reputation for reliability, data integrity, and correctness, making it the default choice for most greenfield projects that need a relational database. As of early 2026, PostgreSQL 18 is the current major release (September 2025), with PostgreSQL 17 widely deployed in production.

What sets PostgreSQL apart from other relational databases is its extensibility. You can define your own data types, build custom functions in multiple languages (PL/pgSQL, PL/Python, PL/Perl, C), create custom index types, and even write extensions that fundamentally change how the database behaves. This extensibility is why PostgreSQL has become the foundation for specialized databases like TimescaleDB (time-series), Citus (distributed), and pgvector (AI/ML embeddings).

Core Strengths

When to Choose PostgreSQL

PostgreSQL is the right choice for most workloads where you need a relational database. It excels at OLTP, handles analytical queries well (especially with partitioning and parallel query), supports JSON/JSONB for semi-structured data, and has native full-text search. The only cases where you might look elsewhere: extremely high write throughput with relaxed consistency (consider Cassandra/ScyllaDB), pure key-value at massive scale (consider Redis/DynamoDB), or when your team is deeply invested in another ecosystem (SQL Server/.NET, Oracle/Java EE).

Understanding PostgreSQL's process model and memory architecture is essential for proper tuning and troubleshooting. Unlike MySQL's thread-per-connection model, PostgreSQL uses a process-per-connection model — each client connection gets its own operating system process. (Note: as of PG 15, the stats collector process was removed and statistics are now stored in shared memory.)

Process Model

The postmaster is the parent process. It listens for incoming connections and forks a new backend process for each one. This model is simple and robust — a crash in one backend doesn't bring down others — but it means each connection consumes real OS memory (typically 5-10 MB per idle connection, more under load). This is why connection pooling is critical in production.

Shared Memory

All backend processes share a region of memory managed by PostgreSQL:

• Shared Buffers — The main data cache. Pages read from disk are cached here. Default is 128 MB, but production systems typically set this to 25% of total RAM (up to about 8-16 GB, beyond which returns diminish).
• WAL Buffers — Buffer for Write-Ahead Log entries before they're flushed to disk. Default auto-sized to 1/32 of shared_buffers, usually 16 MB is sufficient.
• Lock Tables — Tracking all active locks across transactions.
• Proc Array — Information about all active backends, used for MVCC visibility checks.

Write-Ahead Logging (WAL)

Every change to data is first written to the WAL before being applied to the actual data files. This ensures durability — if the server crashes, PostgreSQL replays the WAL to recover to a consistent state. WAL is also the foundation for replication (streaming replication sends WAL records to replicas) and Point-in-Time Recovery (PITR).

# WAL files live in pg_wal/ (formerly pg_xlog/)
# Each segment is 16 MB by default
ls -la /var/lib/postgresql/18/main/pg_wal/
# Watch WAL generation rate
pg_stat_wal  # PostgreSQL 14+

MVCC Internals

PostgreSQL's MVCC implementation stores old row versions directly in the table (heap). When a row is updated, the old version is marked with the transaction ID that invalidated it, and a new version is inserted. This means:

• Updates are essentially delete + insert (table bloat is a real concern)
• VACUUM is required to reclaim space from dead tuples
• Long-running transactions prevent VACUUM from cleaning up, leading to table bloat
• The visibility map tracks which pages contain only all-visible tuples (enables index-only scans)

Tablespaces

Tablespaces allow you to place specific tables or indexes on different physical storage. Useful for putting hot indexes on fast NVMe while keeping large, infrequently accessed tables on cheaper storage. In practice, most cloud deployments use a single tablespace since the storage layer handles tiering.

-- Create a tablespace on fast storage
CREATE TABLESPACE fast_ssd LOCATION '/mnt/nvme/pg_data';

-- Move a table to the fast tablespace
ALTER TABLE orders SET TABLESPACE fast_ssd;

-- Move an index
ALTER INDEX orders_created_idx SET TABLESPACE fast_ssd;

How you deploy PostgreSQL has a massive impact on operational complexity, performance, and cost. There's no single right answer — it depends on your team's capabilities, compliance requirements, and budget.

Deployment Comparison

Self-Hosted vs Managed

The decision to self-host vs use a managed service comes down to team expertise and control requirements. If your team has strong PostgreSQL operational knowledge and you need full control over extensions, configuration, and upgrade timing, self-host. If you're a small team without a DBA, managed services like AWS RDS, Google CloudSQL, or Azure Database for PostgreSQL handle the undifferentiated heavy lifting.

Kubernetes Considerations

Running PostgreSQL on Kubernetes is increasingly viable but requires careful planning. The key challenges are storage performance, data durability, and operational tooling. Use a purpose-built operator:

Installation on Ubuntu/Debian

Always use the official PGDG (PostgreSQL Global Development Group) repository for the latest stable packages:

# Add the official PostgreSQL repository
sudo apt install -y postgresql-common
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh

# Install PostgreSQL 18
sudo apt install -y postgresql-18

# Verify
pg_lsclusters
sudo -u postgres psql -c "SELECT version();"

PostgreSQL does not have built-in automatic failover. It provides the replication primitives — streaming replication and logical replication — but you need external tooling to detect failures and promote a standby. This is by design: the database doesn't try to be a distributed consensus system.

Streaming Replication

Physical (streaming) replication ships WAL records from the primary to one or more standby servers. The standby continuously applies these records, maintaining an exact byte-for-byte copy of the primary. Standby servers can serve read-only queries (hot standby).

# postgresql.conf on primary
wal_level = replica                # minimal, replica, or logical
max_wal_senders = 10               # max number of standby connections
wal_keep_size = 1GB                # retain WAL for slow standbys (PG 13+)
synchronous_standby_names = ''     # empty = async, set for sync replication

# pg_hba.conf on primary
host replication replicator 10.0.0.0/24 scram-sha-256

# On the standby server
sudo -u postgres pg_basebackup \
  -h primary.example.com \
  -D /var/lib/postgresql/18/main \
  -U replicator -Fp -Xs -P -R

# -R creates standby.signal and sets primary_conninfo in postgresql.auto.conf

Synchronous vs Asynchronous Replication

HA Tooling Comparison

Patroni Architecture

Split-Brain Prevention

Split-brain occurs when two nodes both believe they are the primary. Patroni prevents this through its DCS (distributed consensus store) — the leader key has a TTL, and the primary must continuously renew its lease. If it can't reach the DCS, it demotes itself. Additionally, configure watchdog support in Patroni to ensure the old primary's PostgreSQL is actually stopped before the new primary starts accepting writes.

PostgreSQL's process-per-connection model means each connection consumes real memory and OS resources. A typical backend process uses 5-10 MB at idle and can grow to hundreds of MB under load. Without connection pooling, applications that open hundreds or thousands of connections will overwhelm the database — not through query load, but through sheer resource consumption.

Why You Need a Pooler

Most applications don't need to hold a dedicated database connection for every request. A web application serving 1,000 concurrent requests might only need 50-100 actual database connections if queries are fast. A connection pooler multiplexes application connections onto a smaller pool of real PostgreSQL connections.

# Check current connection count
sudo -u postgres psql -c "SELECT count(*) FROM pg_stat_activity;"

# See connection limits
sudo -u postgres psql -c "SHOW max_connections;"
# Default: 100. Many production systems set 200-500.
# Beyond that, you almost certainly need a pooler.

Pooler Comparison

PgBouncer Configuration

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

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

# Pool mode: transaction is recommended for most apps
pool_mode = transaction

# Pool sizing
default_pool_size = 25        # connections per user/database pair
min_pool_size = 5             # pre-warm this many connections
reserve_pool_size = 5         # extra connections for burst traffic
reserve_pool_timeout = 3      # seconds before using reserve pool

max_client_conn = 1000        # total client connections PgBouncer accepts
max_db_connections = 100      # max real connections to PostgreSQL

# Timeouts
server_idle_timeout = 600     # close idle server connections after 10 min
client_idle_timeout = 0       # 0 = no timeout for idle clients
query_timeout = 0             # 0 = no query timeout (handle in app)

# Logging
log_connections = 0
log_disconnections = 0
stats_period = 60

Pooling Modes Explained

Backups are your last line of defense. A PostgreSQL deployment without tested, automated backups is a disaster waiting to happen. The strategy you choose depends on your RPO (Recovery Point Objective), RTO (Recovery Time Objective), and database size.

Backup Methods Comparison

pgBackRest — The Production Standard

pgBackRest is the go-to backup tool for production PostgreSQL. It supports full, differential, and incremental backups, parallel backup and restore, backup verification, and S3/GCS/Azure storage. If you're running PostgreSQL in production and not using pgBackRest, you should switch.

# /etc/pgbackrest/pgbackrest.conf
[global]
repo1-type=s3
repo1-s3-bucket=mycompany-pg-backups
repo1-s3-region=us-east-1
repo1-s3-endpoint=s3.amazonaws.com
repo1-path=/pgbackrest
repo1-retention-full=4
repo1-retention-diff=14
repo1-cipher-type=aes-256-cbc
repo1-cipher-pass=your-encryption-passphrase

process-max=4
compress-type=zst
compress-level=3

[mydb]
pg1-path=/var/lib/postgresql/18/main
pg1-port=5432

# Create stanza (one-time setup)
sudo -u postgres pgbackrest --stanza=mydb stanza-create

# Full backup
sudo -u postgres pgbackrest --stanza=mydb --type=full backup

# Incremental backup (daily)
sudo -u postgres pgbackrest --stanza=mydb --type=incr backup

# Verify backups (checks backup integrity without full restore)
sudo -u postgres pgbackrest --stanza=mydb verify

# Restore to a point in time
sudo -u postgres pgbackrest --stanza=mydb \
  --type=time "--target=2026-03-19 14:30:00" \
  --target-action=promote restore

WAL Archiving for PITR

# postgresql.conf
archive_mode = on
archive_command = 'pgbackrest --stanza=mydb archive-push %p'
# Or without pgbackrest:
# archive_command = 'test ! -f /archive/%f && cp %p /archive/%f'

Backup Automation

# Example cron schedule
# Full backup every Sunday at 2 AM
0 2 * * 0 postgres pgbackrest --stanza=mydb --type=full backup
# Incremental backup every other day at 2 AM
0 2 * * 1-6 postgres pgbackrest --stanza=mydb --type=incr backup
# Verify backup integrity daily at 6 AM
0 6 * * * postgres pgbackrest --stanza=mydb verify

PostgreSQL's default configuration is intentionally conservative — it's designed to run on a system with 512 MB of RAM. For production, you need to tune the key memory parameters, understand query execution plans, choose the right indexes, and keep autovacuum healthy.

Essential Memory Parameters

# postgresql.conf - Example for a 64 GB RAM server
shared_buffers = 16GB
effective_cache_size = 48GB
work_mem = 128MB
maintenance_work_mem = 2GB
wal_buffers = 64MB
max_connections = 300
max_worker_processes = 16
max_parallel_workers_per_gather = 4
max_parallel_workers = 16

EXPLAIN ANALYZE

The single most important skill for PostgreSQL performance troubleshooting. EXPLAIN ANALYZE executes the query and shows the actual execution plan with real timing and row counts.

-- Always use ANALYZE to see actual vs estimated rows
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.total, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at > '2026-01-01'
  AND o.status = 'completed'
ORDER BY o.total DESC
LIMIT 50;

-- Key things to look for:
-- 1. Seq Scan on large tables (missing index?)
-- 2. actual rows vs estimated rows mismatch (stale statistics?)
-- 3. Sort with external merge (work_mem too low?)
-- 4. Nested Loop with high loop count (missing index on join key?)

Index Strategies

-- Covering index for index-only scans
CREATE INDEX idx_orders_status_created
ON orders (status, created_at DESC)
INCLUDE (total, customer_id);

-- Partial index (only index what you query)
CREATE INDEX idx_orders_active
ON orders (created_at DESC)
WHERE status = 'active';

-- BRIN for time-series data
CREATE INDEX idx_events_ts_brin
ON events USING brin (created_at)
WITH (pages_per_range = 32);

-- GIN for JSONB queries
CREATE INDEX idx_metadata_gin
ON documents USING gin (metadata jsonb_path_ops);

Vacuum & Autovacuum Tuning

VACUUM is not optional — it's a critical maintenance operation that reclaims space from dead tuples and updates visibility maps. Autovacuum runs this automatically, but the defaults are often too conservative for busy tables.

# postgresql.conf - Aggressive autovacuum for busy systems
autovacuum_max_workers = 6                  # default 3
autovacuum_naptime = 15s                    # default 1min
autovacuum_vacuum_threshold = 25            # default 50
autovacuum_vacuum_scale_factor = 0.01       # default 0.2 (triggers at 1% dead tuples)
autovacuum_analyze_threshold = 25
autovacuum_analyze_scale_factor = 0.005
autovacuum_vacuum_cost_delay = 2ms          # default 2ms (PG 12+)
autovacuum_vacuum_cost_limit = 1000         # default -1 (uses vacuum_cost_limit = 200)

Table Partitioning

Declarative partitioning (PostgreSQL 10+) splits a large table into smaller physical tables based on a partition key. This improves query performance through partition pruning, makes maintenance operations faster (VACUUM on a smaller table), and enables efficient data lifecycle management (drop old partitions instead of deleting rows).

-- Range partitioning by date
CREATE TABLE events (
    id bigint GENERATED ALWAYS AS IDENTITY,
    event_type text NOT NULL,
    payload jsonb,
    created_at timestamptz NOT NULL
) PARTITION BY RANGE (created_at);

-- Create monthly partitions
CREATE TABLE events_2026_01 PARTITION OF events
    FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
CREATE TABLE events_2026_02 PARTITION OF events
    FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
CREATE TABLE events_2026_03 PARTITION OF events
    FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');

-- Automate with pg_partman
CREATE EXTENSION pg_partman;
SELECT partman.create_parent(
    p_parent_table := 'public.events',
    p_control := 'created_at',
    p_interval := '1 month',
    p_premake := 3
);

PostgreSQL security operates at multiple layers: network access control (pg_hba.conf), authentication, encryption in transit (SSL/TLS), authorization (roles and privileges), row-level security, and audit logging. A production deployment should address all of these.

Authentication — pg_hba.conf

The pg_hba.conf file controls who can connect, from where, and how they authenticate. Rules are evaluated top-to-bottom; the first match wins.

# pg_hba.conf - Production example
# TYPE  DATABASE  USER       ADDRESS          METHOD

# Local socket connections
local   all       postgres                    peer
local   all       all                         scram-sha-256

# SSL-only remote connections
hostssl myapp     app_user   10.0.0.0/24      scram-sha-256
hostssl all       admin      10.0.1.0/24      scram-sha-256

# Replication (SSL required)
hostssl replication replicator 10.0.0.0/24    scram-sha-256

# Reject everything else
host    all       all        0.0.0.0/0        reject

SSL/TLS Configuration

# postgresql.conf
ssl = on
ssl_cert_file = '/etc/postgresql/server.crt'
ssl_key_file = '/etc/postgresql/server.key'
ssl_ca_file = '/etc/postgresql/ca.crt'       # for client certificate verification
ssl_min_protocol_version = 'TLSv1.3'         # PG 12+
ssl_ciphers = 'HIGH:!aNULL:!MD5'

Roles & Privileges

Follow the principle of least privilege. Create separate roles for applications, read-only users, and administrators. Never let applications connect as the postgres superuser.

-- Create application role with minimal privileges
CREATE ROLE app_user WITH LOGIN PASSWORD 'strong-password-here';
GRANT CONNECT ON DATABASE myapp 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;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;

-- Read-only role for reporting
CREATE ROLE readonly WITH LOGIN PASSWORD 'another-strong-password';
GRANT CONNECT ON DATABASE myapp TO readonly;
GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT ON TABLES TO readonly;

Row-Level Security (RLS)

RLS allows you to restrict which rows a user can see or modify based on their role or session properties. Essential for multi-tenant applications.

-- Enable RLS on a multi-tenant table
ALTER TABLE customer_data ENABLE ROW LEVEL SECURITY;

-- Policy: users can only see their own tenant's data
CREATE POLICY tenant_isolation ON customer_data
    USING (tenant_id = current_setting('app.tenant_id')::int);

-- Force RLS even for table owners
ALTER TABLE customer_data FORCE ROW LEVEL SECURITY;

-- Set tenant context in application
SET app.tenant_id = '42';

Audit Logging with pgAudit

# postgresql.conf
shared_preload_libraries = 'pgaudit'
pgaudit.log = 'ddl, role, write'       # log DDL, role changes, and write operations
pgaudit.log_catalog = off               # don't log system catalog queries
pgaudit.log_relation = on               # log table names, not just commands
pgaudit.log_parameter = on              # log query parameters

PostgreSQL releases a new major version annually (typically September) and minor versions quarterly (February, May, August, November). Major upgrades (e.g., 17 to 18) require a migration — the on-disk data format changes. Minor upgrades (e.g., 18.1 to 18.3) are always in-place and binary-compatible.

Upgrade Methods

pg_upgrade Workflow

# 1. Install new PostgreSQL version alongside old
sudo apt install postgresql-18

# 2. Stop both clusters
sudo systemctl stop postgresql@17-main
sudo systemctl stop postgresql@18-main

# 3. Run upgrade check (dry run)
sudo -u postgres /usr/lib/postgresql/18/bin/pg_upgrade \
  --old-datadir=/var/lib/postgresql/18/main \
  --new-datadir=/var/lib/postgresql/18/main \
  --old-bindir=/usr/lib/postgresql/17/bin \
  --new-bindir=/usr/lib/postgresql/18/bin \
  --check

# 4. Run actual upgrade (--link for speed)
sudo -u postgres /usr/lib/postgresql/18/bin/pg_upgrade \
  --old-datadir=/var/lib/postgresql/18/main \
  --new-datadir=/var/lib/postgresql/18/main \
  --old-bindir=/usr/lib/postgresql/17/bin \
  --new-bindir=/usr/lib/postgresql/18/bin \
  --link

# 5. Start new cluster
sudo systemctl start postgresql@18-main

# 6. Rebuild optimizer statistics (critical!)
sudo -u postgres /usr/lib/postgresql/18/bin/vacuumdb \
  --all --analyze-in-stages

# 7. After verifying, remove old cluster
sudo pg_dropcluster 17 main

Minor Version Upgrades

Minor upgrades are straightforward and should be applied promptly — they contain security fixes and bug fixes. No data migration is needed.

# Minor upgrade (e.g., 18.1 → 18.3)
sudo apt update
sudo apt upgrade postgresql-18
sudo systemctl restart postgresql@18-main

# Verify
sudo -u postgres psql -c "SELECT version();"

PostgreSQL exposes a wealth of internal statistics through its catalog views. The key is knowing which metrics matter, setting up proper alerting, and correlating database metrics with application-level observability.

Essential Statistics Views

Key Metrics to Monitor

-- Cache hit ratio (should be > 99%)
SELECT
  sum(heap_blks_hit) / nullif(sum(heap_blks_hit) + sum(heap_blks_read), 0) AS ratio
FROM pg_statio_user_tables;

-- Active connections by state
SELECT state, count(*)
FROM pg_stat_activity
GROUP BY state;

-- Replication lag (on primary)
SELECT client_addr, state, replay_lag
FROM pg_stat_replication;

-- Top 10 slowest queries (requires pg_stat_statements)
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

-- Tables with most dead tuples (vacuum candidates)
SELECT schemaname, relname, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

-- Transaction ID age (wraparound risk)
SELECT datname, age(datfrozenxid) AS xid_age,
  current_setting('autovacuum_freeze_max_age') AS freeze_max
FROM pg_database
ORDER BY age(datfrozenxid) DESC;

Prometheus + Grafana Stack

The postgres_exporter by Prometheus Community is the standard way to expose PostgreSQL metrics to Prometheus. Combine with Grafana dashboards for visualization and alerting.

# docker-compose.yml snippet for postgres_exporter
postgres-exporter:
  image: prometheuscommunity/postgres-exporter:latest
  environment:
    DATA_SOURCE_NAME: "postgresql://monitor:password@postgres:5432/myapp?sslmode=require"
  ports:
    - "9187:9187"

-- Create a monitoring role with minimal privileges
CREATE ROLE monitor WITH LOGIN PASSWORD 'monitor-password';
GRANT pg_monitor TO monitor;  -- PG 10+ built-in monitoring role
GRANT CONNECT ON DATABASE myapp TO monitor;

Critical Alerts

PostgreSQL's extension system is one of its greatest strengths. Extensions can add new data types, operators, index access methods, functions, and even background workers — all without forking the core project. Some extensions effectively turn PostgreSQL into a specialized database.

Essential Extensions

Foreign Data Wrappers (FDW)

FDWs let you query external data sources as if they were PostgreSQL tables. The postgres_fdw extension (included in core) enables cross-server queries. Third-party FDWs exist for MySQL, MongoDB, Redis, S3/Parquet files, Elasticsearch, and more.

-- Query a remote PostgreSQL server
CREATE EXTENSION postgres_fdw;

CREATE SERVER remote_server
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host 'remote.example.com', port '5432', dbname 'analytics');

CREATE USER MAPPING FOR app_user
    SERVER remote_server
    OPTIONS (user 'remote_reader', password 'secret');

IMPORT FOREIGN SCHEMA public
    FROM SERVER remote_server
    INTO remote_schema;

-- Now query remote tables as if they were local
SELECT * FROM remote_schema.sales WHERE region = 'EMEA';

Logical Decoding & CDC

# postgresql.conf for logical decoding
wal_level = logical                    # required (not just 'replica')
max_replication_slots = 10             # one per consumer
max_wal_senders = 10

-- Create a logical replication slot for Debezium
SELECT pg_create_logical_replication_slot('debezium_slot', 'pgoutput');

-- Create a publication for CDC
CREATE PUBLICATION my_publication FOR TABLE orders, customers, products;

PostgreSQL is released under the PostgreSQL License, a liberal open-source license similar to the BSD and MIT licenses. It permits use, modification, and distribution for any purpose — commercial or otherwise — with no copyleft requirements. There is no "enterprise edition," no dual licensing, and no feature gating. Every feature is available to everyone.

License Comparison

Commercial Support Options

While PostgreSQL itself is free, commercial support is available from several companies. This is relevant for enterprises that need SLA-backed support, consulting, or managed services.

Before deploying PostgreSQL for a customer, work through these questions. They'll surface requirements that drive architecture decisions and prevent post-deployment surprises.

1. Data volume & growth rate? — Current database size, expected growth per month/year. This drives storage, partitioning, and backup strategy decisions. Terabyte databases need different tooling than 50 GB ones.
2. HA requirements? — What's the acceptable downtime? RPO (data loss tolerance) and RTO (recovery time). Zero RPO requires synchronous replication. Sub-minute RTO requires Patroni with automated failover.
3. Connection patterns? — Peak concurrent connections, connection duration (short-lived HTTP handlers vs long-lived batch jobs). This determines whether you need PgBouncer and how to size pools.
4. Read/write ratio? — Read-heavy workloads benefit from read replicas. Write-heavy workloads need careful WAL tuning, connection pooling, and potentially Citus for horizontal scaling.
5. Compliance requirements? — SOC 2, HIPAA, PCI-DSS, GDPR. These drive encryption at rest, SSL enforcement, pgAudit, row-level security, and data residency decisions.
6. Backup & recovery targets? — RPO/RTO numbers, retention requirements, offsite storage mandates. pgBackRest to S3 with PITR is the standard, but some customers need backup to specific providers.
7. Deployment platform? — Bare metal, VMs, Kubernetes, or managed service? Team expertise matters more than technical merits. If they don't have K8s skills, don't deploy on K8s.
8. Upgrade strategy? — How will you handle major version upgrades? pg_upgrade with downtime window, or logical replication for near-zero downtime? How often will you apply minor patches?
9. Extensions needed? — PostGIS, pgvector, TimescaleDB, pg_cron? This affects deployment model — managed services may not support all extensions. Verify compatibility before committing.
10. Monitoring stack? — What's the existing observability platform? Prometheus/Grafana, Datadog, New Relic? Ensure postgres_exporter or the provider's integration is in place from day one.
11. Team expertise? — Does the team have PostgreSQL operational experience? If not, budget for training, runbooks, and potentially commercial support (EDB, Crunchy Data, Percona).
12. Migration source? — Coming from MySQL, Oracle, SQL Server? Plan for schema differences, stored procedure rewrites, and data type mapping. EDB has Oracle compatibility tools. pgLoader handles MySQL migrations.