Postgres Ops Monitoring And Migrations

PostgreSQL Operations: Monitoring, Indexing, Safe Migrations, Thin Clones

I operate production PostgreSQL: I instrument it, read its statistics, resolve lock chains, choose and build indexes, apply schema changes without stopping traffic, and stand up thin-clone environments for realistic testing.

Before I start

Before executing anything I collect the full reference set:

• references/monitoring.md — pgSCV exporter setup and the complete catalogue of health/size/cache/bloat/connection/lock SQL queries.
• references/indexing.md — index type selection matrix, syntax for every type, concrete indexing patterns.
• references/migrations.md — the full zero-downtime DDL playbook (lock_timeout, columns, indexes, REINDEX, partitions, NOT NULL, FK, UNIQUE, PK) with version-specific rules.
• references/thin-clones-dle.md — Database Lab Engine in physical mode with WAL-G, ZFS, S3/MinIO, snapshot preprocessing, clone lifecycle, OIDC roles.
• references/pitfalls.md — every known failure mode and the corresponding fix, plus acceptance criteria.

I always establish the environment facts first:

1. Exact server version — SELECT version();. Techniques differ for 9.1+, 9.2+, 10, 11, 12+ and choosing wrong locks the table.
2. Whether I hold a superuser/privileged role (DDL, statistics resets, extensions).
3. Whether I can edit postgresql.conf and restart the server.
4. pg_stat_statements readiness: present in shared_preload_libraries, server restarted, CREATE EXTENSION pg_stat_statements;, and track_io_timing = on so blk_read_time/blk_write_time are populated.
5. Topology, because it dictates migration strategy: single app+DB (downtime allowed — optimize for speed), multiple app servers running mixed versions (backward compatibility is mandatory), or sharded databases whose schemas temporarily diverge.

Instructions

Step 1: Classify the request and pick the track

• "Collect/expose metrics" → Step 2 (pgSCV) and Step 3 (statistics baseline).
• "Database is slow / sizes / cache / bloat / unused indexes" → Step 3.
• "Query is stuck / something is blocking" → Step 4.
• "Which index / create an index" → Step 5.
• "Change the schema on a live database" → Step 6 (the most dangerous track — never skip Rule Zero).
• "Test against production-like data" → Step 7 (thin clones) and Step 8 (plan analysis).

Step 2: Stand up metric collection with pgSCV

1. Pre-create a monitoring role in PostgreSQL (e.g. monitoring) with a password and read access to statistics.
2. Download and unpack the exporter: curl -O -L https://github.com/weaponry/pgscv/releases/download/v0.5.0/pgscv_0.5.0_linux_amd64.tar.gz tar xvzf pgscv_0.5.0_linux_amd64.tar.gz
3. Write a minimal pgscv.yaml:

   defaults:
     postgres_username: "monitoring"
     postgres_password: "supersecretpassword"
   

4. Run ./pgscv --config-file pgscv.yaml. It auto-discovers the local system, PostgreSQL and Pgbouncer and exposes a very large built-in metric set with no further configuration.
5. Verify from a second console: curl -s 127.0.0.1:9890/metrics must return a populated metric list. Port 9890 must be free.
6. Trim or extend: disable unneeded collectors, restrict the databases metrics are collected from, or define user-defined metrics from my own SQL queries.
7. For services on other hosts, configure collection from the exact remote services with their credentials instead of relying on autodiscovery.
8. Point Prometheus at the endpoint and build dashboards and alerts.

Step 3: Take a statistics baseline and read database health

I reset counters when I need a clean measurement window (SELECT pg_stat_reset();, SELECT pg_stat_statements_reset();), then run the full query set from references/monitoring.md and interpret it:

• Sizes: tablespaces (always excluding pg_global), databases, schemas and tables with heap vs index split and n_live_tup (always excluding pg_catalog, information_schema, pg_toast* and relkind = 'i').
• Overall cache hit ratio — target 99% or higher; only data-warehouse workloads are excused.
• Index cache hit rate.
• Index-usage percentage per table — prime candidates for new indexes are tables with more than 10000 rows and zero or low index usage.
• Unused indexes (idx_scan = 0) — candidates for dropping.
• Per-relation I/O hot spots, then the responsible queries from pg_stat_statements ordered by blk_read_time + blk_write_time.
• Bloat: tbloat/wastedbytes for tables, ibloat/wastedibytes for indexes; fight it with VACUUM and by tuning autovacuum after checking last_vacuum/last_autovacuum.
• Connection counts by backend_type and the list of currently running queries with their age.

Step 4: Diagnose and clear lock chains

1. Join pg_locks with pg_stat_activity twice (blocked and blocking sides) matching on the same transactionid OR the same relation + locktype with different pids, filtering NOT blockedl.granted and blockinga.datname = current_database(); return locked_item, waiting_duration, blocked pid/query/mode and blocking pid/query/mode (exact query in references/monitoring.md).
2. Act on the blocking pid, gently first: SELECT pg_cancel_backend(PID_ID); cancels the query. Only if that is insufficient use SELECT pg_terminate_backend(PID_ID);, which kills the session and closes its connection.
3. Cancelling one blocking query normally drains the entire accumulated queue, because the lock queue is served in strict order.

Step 5: Choose and create the right index

I select by data type and query pattern (full matrix and syntax in references/indexing.md): B-tree for comparisons, sorting, ranges and uniqueness on text/numeric/date-time; Hash only for equality (no sorting, no ranges, rarely worth it); GiST for geometry, text, arrays and array intersection, PostGIS types; SP-GiST for non-overlapping, non-uniformly distributed data and IP addresses; GIN for full-text, arrays, JSON/JSONB and trigrams; BRIN for very large naturally ordered data such as time series.

I always weigh the trade-off: every INSERT/UPDATE/DELETE must maintain every index, so I create only indexes that measurably improve real queries, drop those with idx_scan = 0, and REINDEX when needed. On live tables I create indexes only as described in Step 6.

Step 6: Execute schema changes without downtime

Rule Zero — non-negotiable. Before any command that takes a strong lock (practically every ALTER TABLE), inside the transaction I set:

SET LOCAL lock_timeout TO '100ms'

LOCAL confines the setting to the current transaction; without it the setting applies to the whole session. If the lock cannot be acquired in 100 ms the command fails — I then retry it or investigate the long transaction holding the table. The mechanism I am protecting against: a long SELECT holds the weak AccessShare lock; ALTER TABLE requests AccessExclusive, which is incompatible with every other mode and queues; because the queue is drained in strict order, every subsequent harmless SELECT queues behind the ALTER TABLE and the application freezes.

Then I follow the exact recipe from references/migrations.md for the operation at hand:

• ADD COLUMN (metadata only, cheap) and ADD COLUMN WITH DEFAULT (fast on 11+; on pre-11 split into ADD COLUMN + ALTER COLUMN ... SET DEFAULT + batched backfill — never one plain UPDATE over a big table).
• DROP COLUMN (drop its constraints, hide it from the ORM via Hibernate @Transient / JOOQ <excludes>, audit SELECT * mappings, deploy the code to all app servers, only then drop; space is not freed without VACUUM FULL).
• CREATE INDEX CONCURRENTLY always on live tables, followed by an pg_index.indisvalid check; drop, fix data, recreate if invalid.
• REINDEX only on PG 12+ with CONCURRENTLY; clean up _ccnew (retry) and _ccold (just drop) leftovers.
• Partitioned tables: per-partition CONCURRENTLY for inheritance and PG 10 declarative; on PG 11+ use CREATE INDEX ... ON ONLY parent → per-partition CONCURRENTLY → ALTER INDEX ... ATTACH PARTITION until the parent index becomes valid.
• NOT NULL via CHECK (col IS NOT NULL) NOT VALID + data fix + VALIDATE CONSTRAINT (ShareUpdateExclusive, does not block DML).
• FOREIGN KEY via NOT VALID + data fix + VALIDATE CONSTRAINT.
• UNIQUE via CREATE INDEX CONCURRENTLY + ADD CONSTRAINT ... UNIQUE USING INDEX.
• PRIMARY KEY via unique index + ADD CONSTRAINT ... PRIMARY KEY USING INDEX when a genuine NOT NULL exists; otherwise on PG 11+ the new-column + trigger + batched backfill + triple-rename swap procedure, followed by full cleanup.

Step 7: Build a thin-clone test environment (Database Lab Engine, physical mode)

I follow references/thin-clones-dle.md end to end: WAL-G backup-push + WAL archiving to S3 with separate read-write and read-only accounts, OpenZFS 2.x on the host, a ZFS pool created at exactly the mount point referenced in the DLE config, an extended-postgres image with the same PostgreSQL major version as the source plus WAL-G, extensions and locales, then server.yml (retrieval jobs physicalRestore → physicalSnapshot, poolManager.mountDir mapped identically inside and outside the container, global.debug: true, AWS_ENDPOINT set in both places, LANG/LC_ALL = en_US.UTF-8, portPool 6001–6100, maxIdleMinutes, snapshot and retention timetables).

I copy planner-relevant settings from production into databaseConfigs so clone plans match production, put masking/cleanup .sql files into the preprocessing directory (ordering encoded in file names), and verify the expected container sequence: dblab_phr_* → dblab_promote_* → only dblab_sync_* plus the DLE container remain. Then I create clones (seconds each, own port, near-zero initial space) and manage their lifecycle with delete-protection and idle cleanup.

Step 8: Analyze query plans safely

I never paste production plans or data into public EXPLAIN web services. I deploy a self-hosted plan-visualization service internally (packages exist for RHEL/Debian, Docker on Linux/Windows/macOS, Kubernetes and AWS) and use it for tests, debugging and personal analysis.

Step 9: Verify against acceptance criteria

I close the task only after checking the criteria in references/pitfalls.md: metrics endpoint populated and scraped, every health question answerable with one query, cache hit ratio ≥ 99%, all live-table indexes valid and built CONCURRENTLY with no _ccnew/_ccold leftovers, every strong-lock statement preceded by lock_timeout, all constraints validated without blocking DML, users noticing nothing, and — for DLE — snapshots rotating on schedule, clones thin and isolated, fresh source data visible in clones from newer snapshots.

Troubleshooting

A migration hangs and the application freezes. The DDL is queued on AccessExclusive behind a long transaction and every later query is queued behind it. I cancel the migration, identify the blocker with the lock-chain query (Step 4), pg_cancel_backend the blocking pid, and re-run the DDL with SET LOCAL lock_timeout TO '100ms' so it fails fast instead of taking traffic down.

CREATE INDEX CONCURRENTLY or REINDEX CONCURRENTLY leaves an index that queries never use. The build failed and the index is INVALID (classic cause: a unique index on data containing duplicates). I check SELECT pg_index.indisvalid FROM pg_class, pg_index WHERE pg_index.indexrelid = pg_class.oid AND pg_class.relname = '<index>';, then DROP INDEX CONCURRENTLY, fix the data with an UPDATE, and rebuild. For REINDEX leftovers: _ccnew → drop it and retry REINDEX; _ccold → the new index is already built, just drop the _ccold one.

DLE stops applying new WALs or fails to detect checkpoints. Either the WAL-G binary in the image is older than the WAL-G that produced the backup (an old WAL-G can restore a full backup from a newer one but breaks on differential backups and on timeline changes after a Patroni failover) — I align WAL-G versions; or the container locale is non-English, so DLE cannot parse English log text and pg_controldata output — the container must contain the source database's locale but keep C or en_US.UTF-8 as its default with English PostgreSQL messages. I also confirm the host runs OpenZFS 2.x (never 0.8.x) and global.debug: true so the log shows the real cause.

Cache hit ratio is below 99% and I/O is heavy. I locate the hot relations via pg_statio_user_tables ordered by heap_blks_read DESC, then find the responsible statements in pg_stat_statements ordered by blk_read_time + blk_write_time (requires track_io_timing = on), and fix them by adding the missing index (tables >10000 rows with low idx_scan), reclaiming bloat with VACUUM, or resizing shared_buffers.