Scripts

Monitor PostgreSQL ANALYZE Progress with pg_stat_progress_analyze Running ANALYZE on a multi-hundred-gigabyte table and wondering whether it is halfway done or barely started is a common operational question with, until recently, no good answer. pg_stat_progress_analyze answers it: a live view that reports the current …

Query PostgreSQL I/O Statistics with pg_stat_io An I/O spike that nobody can attribute is a frustrating thing to chase. Was it autovacuum, a checkpoint, a bulk load, or ordinary client queries? Before PostgreSQL 16 the answer required stitching together several partial views; pg_stat_io gives a single cluster-wide …

TRUNCATE vs DELETE vs DROP: Remove Rows Fast in PostgreSQL Where DELETE removes rows one logical version at a time and leaves the cleanup to vacuum, TRUNCATE discards an entire table's contents almost instantly and reclaims the space immediately, and DROP removes the table itself. Picking the wrong one for "empty this …

timestamptz and tzdata: Avoid Shifted PostgreSQL Timestamps PostgreSQL stores every timestamptz value as UTC and converts it to the session's time zone only at display time. That design is correct and elegant — until a tzdata update changes the offset rules for a region, at which point timestamps inserted under the old …

Back Up All PostgreSQL Databases with pg_dumpall Before a major-version upgrade or a disaster-recovery rehearsal, a DBA needs a single artifact that captures the whole cluster: every database, plus the global objects that live outside any one database. pg_dumpall produces exactly that — one plain-SQL script that …

PostgreSQL Advisory Locks with pg_advisory_lock Two scheduled jobs fire at the same minute and both try to process the same nightly batch. Nothing in the data stops them — there is no single row to lock, because the work has not produced rows yet. Row and table locks are the wrong tool here. What you want is a named …

List All PostgreSQL Triggers with Their State A disabled trigger is invisible until something it should have done goes missing. The audit row that never got written, the derived column that stopped updating, the constraint check that silently stopped firing — each traces back to a trigger left in the disabled state …

PostgreSQL Column-Level Permissions Audit Query Which roles can read which columns? Table-level grants are easy to list, but they hide a finer layer of access. A role denied SELECT on a table can still hold SELECT on two of its columns, and that is exactly where personal data quietly stays reachable after a wider grant …

List PostgreSQL Wait Events with the pg_wait_events View PostgreSQL 17 added pg_wait_events, a system view that enumerates every wait event the running server can record. Prior to 17 the same information lived only in the documentation — no in-database way to confirm a wait name or attach a description to it. The view …

Identify Running PostgreSQL Autovacuum Worker Sessions Autovacuum lag is a slow accumulation. Every UPDATE rewrites the row and creates a new HOT chain, the launcher falls behind during write spikes, and over weeks a busy OLTP cluster ends up with three workers stuck on a handful of huge tables while smaller ones wait …