Postgres On-Premises Operational Playbook

This playbook is an operational reference for teams running the Bindplane-managed PostgreSQL store on their own infrastructure. It covers routine maintenance tasks, common problem patterns, and recommended configuration values for a Bindplane workload.

Before following this guide, make sure Postgres is installed and Bindplane is configured to connect to it. See Postgres Store for initial setup.

Supported Versions

Bindplane is tested and supported against the following PostgreSQL major versions:

Version	Support Status
18	Supported
17	Supported
16	Supported
15	Supported
14	Supported

Default Settings Relevant to Bindplane

The following postgresql.conf parameters have the most direct impact on Bindplane's behavior. Review and adjust these before putting a Bindplane instance into production.

max_connections

Controls how many concurrent client connections Postgres accepts. Bindplane holds a connection pool against Postgres; the pool size is set by maxConnections in the Bindplane configuration (default 100).

max_connections = 110

Recommendation: Since this Postgres instance is dedicated to Bindplane, set max_connections to cover all Bindplane nodes plus a small number of connections for admin work (psql sessions, monitoring queries, etc.). For a single-node Bindplane installation with maxConnections = 100, a value around 110 leaves room for admin tasks. For a High Availability deployment, add the maxConnections values across all nodes — for example, 3 nodes × 100 connections = 300, so set max_connections to 310 or similar.

Each Postgres connection consumes approximately 5–10 MB of RAM. Set max_connections only as high as needed.

Applying the Settings

After editing postgresql.conf, changes to max_connections require a full restart:

sudo systemctl restart postgresql

Dead Tuples and VACUUM

What Are Dead Tuples?

PostgreSQL uses multi-version concurrency control (MVCC). When a row is updated or deleted, the old version of the row is not immediately removed from disk — it becomes a "dead tuple." Over time, dead tuples accumulate and consume disk space. If they are not cleaned up, they also degrade query performance because the query planner must scan over them.

Bindplane performs frequent updates and deletes against the Postgres store — particularly for agent state, rollout tracking, and event records. These workloads generate dead tuples at a higher rate than typical OLTP applications, making routine VACUUM maintenance important.

Checking Dead Tuple Accumulation

Query pg_stat_user_tables to see how many dead tuples exist per table in the bindplane database:

SELECT
    relname        AS table_name,
    n_live_tup     AS live_tuples,
    n_dead_tup     AS dead_tuples,
    last_vacuum,
    last_autovacuum,
    last_analyze,
    last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

Tables with a high n_dead_tup count relative to n_live_tup — for example, a dead-to-live ratio above 10–20% — are candidates for manual VACUUM ANALYZE.

Running VACUUM ANALYZE Manually

Connect to the bindplane database and run:

VACUUM ANALYZE;

To target a specific table — for example the agents table — run:

VACUUM ANALYZE agents;

VACUUM ANALYZE is safe to run on a live database. It does not lock tables in a way that blocks normal operations, but it does consume I/O. Schedule it during a low-traffic window if the Postgres host is resource-constrained.

Configuring Autovacuum

PostgreSQL's autovacuum daemon runs VACUUM automatically in the background. For a Bindplane workload, the default autovacuum thresholds may be too conservative, allowing dead tuples to build up between runs.

The relevant settings are in postgresql.conf. The defaults and recommended values for a Bindplane workload are shown below:

Parameter	Default	Recommended for Bindplane
autovacuum	on	on
autovacuum_vacuum_scale_factor	0.2 (20% of the table)	0.05 (5%)
autovacuum_analyze_scale_factor	0.1 (10% of the table)	0.02 (2%)
autovacuum_vacuum_cost_delay	2ms	2ms
autovacuum_max_workers	3	3–5 depending on CPU count

Edit postgresql.conf (location varies by distribution — commonly /etc/postgresql/<version>/main/postgresql.conf) and adjust:

autovacuum = on
autovacuum_vacuum_scale_factor = 0.05
autovacuum_analyze_scale_factor = 0.02

Reload Postgres to apply without a full restart:

sudo systemctl reload postgresql

Verify the settings took effect:

SHOW autovacuum_vacuum_scale_factor;
SHOW autovacuum_analyze_scale_factor;

Locking Issues

Detecting Blocking Queries

Long-running queries or transactions that hold locks can block other Postgres operations, which surfaces in Bindplane as slow API responses, stalled rollouts, or agent check-in failures.

The following query joins pg_locks with pg_stat_activity to identify any process that is blocking another:

SELECT
    blocking.pid            AS blocking_pid,
    blocking.query          AS blocking_query,
    blocking.state          AS blocking_state,
    blocking.wait_event     AS blocking_wait_event,
    now() - blocking.query_start AS blocking_duration,
    blocked.pid             AS blocked_pid,
    blocked.query           AS blocked_query
FROM pg_locks AS lock_blocked
JOIN pg_stat_activity AS blocked
    ON blocked.pid = lock_blocked.pid
JOIN pg_locks AS lock_blocking
    ON lock_blocking.transactionid = lock_blocked.transactionid
    AND lock_blocking.pid != lock_blocked.pid
JOIN pg_stat_activity AS blocking
    ON blocking.pid = lock_blocking.pid
WHERE NOT lock_blocked.granted
ORDER BY blocking_duration DESC;

If the result set is non-empty, a lock contention situation exists. The blocking_query column shows what the blocking session is running, and blocking_duration shows how long it has been running.

Terminating a Blocking Process

Once you have identified the blocking PID, you can ask it to stop gracefully:

SELECT pg_cancel_backend(<blocking_pid>);

pg_cancel_backend sends an interrupt to the query, allowing the transaction to roll back cleanly. If the query does not respond to the cancel signal within a reasonable time, terminate the session:

SELECT pg_terminate_backend(<blocking_pid>);

pg_terminate_backend immediately kills the connection. Any open transaction on that session will be rolled back. Use this only when pg_cancel_backend is insufficient.

Health Check Queries

Run these queries against the bindplane database to verify Postgres is healthy.

1. Confirm Bindplane Can Connect

From the Bindplane server host, verify connectivity with psql:

psql \
  --host=bindplane-postgres \
  --port=5432 \
  --username=bindplane \
  --dbname=bindplane \
  --command="SELECT 1;"

A result of 1 confirms a successful connection.

2. Check Database Size

SELECT
    pg_database.datname                                         AS database,
    pg_size_pretty(pg_database_size(pg_database.datname))       AS size
FROM pg_database
WHERE datname = 'bindplane';

3. Check Active Connection Count

SELECT
    count(*)                AS active_connections,
    max_conn.setting::int   AS max_connections
FROM pg_stat_activity
CROSS JOIN (
    SELECT setting FROM pg_settings WHERE name = 'max_connections'
) AS max_conn
WHERE state = 'active'
GROUP BY max_conn.setting;

If active_connections is close to max_connections, increase max_connections in postgresql.conf or reduce Bindplane's maxConnections setting.

4. Check for Long-Running Queries

SELECT
    pid,
    now() - query_start     AS duration,
    state,
    left(query, 100)        AS query_snippet
FROM pg_stat_activity
WHERE state != 'idle'
  AND query_start IS NOT NULL
  AND now() - query_start > interval '30 seconds'
ORDER BY duration DESC;

Any query running for more than 30 seconds on a Bindplane database is abnormal and warrants investigation.

5. Check Table Bloat

SELECT
    relname                                                 AS table_name,
    n_dead_tup                                             AS dead_tuples,
    n_live_tup                                             AS live_tuples,
    round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_pct
FROM pg_stat_user_tables
WHERE n_live_tup > 0
ORDER BY dead_pct DESC;

A dead_pct above 20% indicates that VACUUM has not run recently enough. Review the autovacuum configuration described in the Dead Tuples and VACUUM section above.

6. Check Replication Lag (HA deployments only)

If running Bindplane in High Availability mode with a Postgres replica, check replication lag from the primary:

SELECT
    client_addr,
    state,
    sent_lsn,
    write_lsn,
    flush_lsn,
    replay_lsn,
    (sent_lsn - replay_lsn) AS replication_lag_bytes
FROM pg_stat_replication;

A replication_lag_bytes value consistently above a few megabytes indicates the replica is falling behind and should be investigated.