PostgreSQL Scripts for Database Administration

Audit Row-Level Security Policies with pg_policies pg_policies turns a table's row-level security setup — however many CREATE POLICY statements are scattered across migration files — into one queryable inventory: one row per policy, with the roles it binds to, the command it governs, and the boolean expressions doing …

List PostgreSQL Materialized Views with pg_matviews A materialized view carries no built-in warning label for staleness. Nothing about the row itself changes when the underlying tables mutate underneath it — a sales dashboard or a cached report join keeps returning last week's numbers with the same confidence as this …

Audit PostgreSQL Authentication with pg_hba_file_rules Validating a pg_hba.conf edit before trusting it in production usually means reading the raw file line by line and hoping the parser interprets every rule the way it looks on screen. pg_hba_file_rules skips that guesswork — it is a system view that shows exactly …

Monitor Last VACUUM Runs with pg_stat_user_tables A table's dead tuple count climbing while last_autovacuum stays weeks old isn't noise — it's the clearest sign autovacuum is falling behind, and pg_stat_user_tables is where that gap becomes visible in a single query, without tailing logs or guessing at a schedule. …

Query PostgreSQL Tablespace Info with pg_tablespace What tablespaces exist in this cluster, who owns each one, and where do their files actually live on disk? pg_tablespace answers all three: it holds one row per tablespace with the owner OID, access control list, storage options, and — via pg_tablespace_location() — …

List PostgreSQL Functions with pg_proc Where information_schema.routines delivers a SQL-standard view of functions and procedures — portable across databases but deliberately incomplete — pg_proc gives the full PostgreSQL catalog row: language, source code, argument modes, cost estimates, and the prokind flag that …

Inspect PostgreSQL Sequences with the pg_sequences View pg_sequences is the catalog-backed view that exposes every sequence in the current database in one readable row each — start_value, min_value, max_value, increment_by, cache_size, cycle, and the all-important last_value. It turns a scattered set of per-sequence …

Track Two-Phase Commit State with pg_prepared_xacts A prepared transaction that never gets committed or rolled back is one of the quieter ways a PostgreSQL database degrades. It keeps holding locks, it pins the transaction-ID horizon so VACUUM cannot reclaim dead rows, and nothing in normal monitoring screams about it. …

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 …