PostgreSQL Replication: Monitoring

Tip: Check Replication Lag

SELECT client_addr, state, sent_lag, replay_lag
FROM pg_stat_replication;

Gotcha: pg_last_wal_replay_lsn()

SELECT now() - pg_last_xact_replay_timestamp() AS replication_lag;

Shows how far behind the replica is.

Tip: Replica Status

SELECT pg_is_in_recovery();

Returns true on replica, false on primary.

Gotcha: Streaming vs File-Based

Streaming replication is near real-time. File-based (log shipping) has higher lag.

Tip: Monitor WAL Files

SELECT pg_walfile_name(pg_current_wal_lsn());

Shows the current WAL file being written.

Gotcha: Replica Can't Write

Replicas are read-only. Use primary for writes.

Tip: EXPLAIN (ANALYZE, BUFFERS) Is Your Best Friend

For query debugging, always use EXPLAIN (ANALYZE, BUFFERS) instead of plain EXPLAIN. The BUFFERS option shows hit/miss rates for every node, revealing whether your indexes are actually in memory.

Tip: Partial Indexes Are Underutilized

CREATE INDEX ON orders (status) WHERE status = 'pending' creates a tiny index that covers only the rows your query needs. It's faster to scan and cheaper to maintain than a full-column index.

Gotcha: NULL Sorting Is Non-Obvious

By default, NULLs sort AFTER non-null values in ascending order. ORDER BY col DESC puts NULLs FIRST. Use NULLS LAST or NULLS FIRST to be explicit.

Senior Insight

The most common PostgreSQL issue I encounter in Laravel applications is connection exhaustion. Every request opens a database connection, and under load, the pool fills up. I configure PgBouncer for production deployments and set conservative connection limits. The math is simple: if your application server runs 10 PHP-FPM workers and each holds a database connection, 10 concurrent requests can exhaust a 100-connection pool if each request opens sub-connections for queue processing or long-running transactions.

Source: pganalyze Blog (https://pganalyze.com/blog), PostgreSQL Docs (https://www.postgresql.org/docs/current/), Crunchy Data Blog (https://www.crunchydata.com/blog)