connection pooling

14 notes, read out of this brain and free to use. Each one was extracted from a source and is re-checked against its exam.

Three ways to monitor replication lag in Supabase

Replication lag can be monitored in three ways: (1) Dashboard - view replication lag via Reports, (2) Database - using pg_stat_subscription, pg_stat_subscription_stats, and pg_replication_slots tables, (3) Metrics - using the Prometheus endpoint with metrics like replication_slots_max_lag_bytes, pg_stat_replication_replay_lag, and pg_stat_replication_send_lag.

pg_stat_subscription_stats error_count monitoring

The pg_stat_subscription_stats table contains an error_count column to check for issues applying or syncing replication data. If error_count is greater than zero, check the logs to determine why replication failed.

pg_stat_subscription PID null means inactive

In the pg_stat_subscription table, if the PID column is null, the subscription is not active.

replication_slots_max_lag_bytes is primary metric

Among Prometheus replication metrics, replication_slots_max_lag_bytes is the more important metric to monitor.

pg_stat_replication_replay_lag throttling

pg_stat_replication_replay_lag measures the lag in replaying WAL files from the source database on the target database. This lag is throttled by disk performance or high activity levels.

pg_stat_replication_send_lag causes and meaning

pg_stat_replication_send_lag measures the lag in sending WAL files from the source database. High send lag indicates either that the publisher is not being asked to send new WAL files OR there are network issues.

Query primary replication status

To check the status of replicas connected to the primary database, use: SELECT pid, application_name, state, sent_lsn, write_lsn, flush_lsn, replay_lsn, sync_state FROM pg_stat_replication;

Three replication slot states

Replication slots have three possible states: (1) active - the slot is active and receiving data, (2) inactive - the slot is not active and not receiving data, (3) lost - the slot is lost and not receiving data.

Query replication slot status

To check replication slot status, use: SELECT slot_name, active, state FROM pg_replication_slots;

Query WAL size

To check the WAL directory size, use: SELECT * FROM pg_ls_waldir();

Query current LSN on primary

To check the current log sequence number on the primary database, use: SELECT pg_current_wal_lsn();

Four subscription replication states

The srsubstate column in pg_subscription_rel shows the replication state of subscriptions with four possible values: (1) 'i' - Initializing - the subscription is being initialized, (2) 'd' - Data Synchronizing - doing the initial copy, (3) 's' - Synchronized - subscription is synchronized, (4) 'r' - Replicating - replicating data.

Query subscriber subscription status

To check the status of subscriptions on a replica and their table-level status, use: SELECT sub.subname AS subscription_name, relid::regclass AS table_name, srel.srsubstate AS replication_state, CASE srel.srsubstate WHEN 'i' THEN 'Initializing' WHEN 'd' THEN 'Data Synchronizing' WHEN 's' THEN 'Synchronized' WHEN 'r' THEN 'Replicating' ELSE 'Unknown' END AS state_description, srel.srsyncedlsn AS last_synced_lsn FROM pg_subscription sub JOIN pg_subscription_rel srel ON sub.oid = srel.srsubid ORDER BY table_name;

Query last WAL replay LSN on subscriber

To check the last replayed log sequence number on the subscriber database, use: SELECT pg_last_wal_replay_lsn();

Give your agent this brain