# Postgres Ops Monitoring And Migrations

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

- Skill: `mikerrr/postgres-ops-monitoring-and-migrations` (Agent Skill)
- Install (CLI): `npx skillmds@latest add mikerrr/postgres-ops-monitoring-and-migrations`
- Raw SKILL.md: https://api.skillmd.com/api/skills/mikerrr/postgres-ops-monitoring-and-migrations/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Coding & Dev Tools
- Author: mikerrr (https://skillmd.com/u/mikerrr)
- Updated: 2026-09-22
- Page: https://skillmd.com/skills/mikerrr/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`:
   ```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:

```sql
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`.