PostgreSQL Scripts for Database Administration

Claim Queue Rows with FOR UPDATE SKIP LOCKED in PostgreSQL Several workers poll the same job table with SELECT ... LIMIT 10, and a plain LIMIT does nothing to stop two of them from getting the same list of items to process. Add FOR UPDATE and the second worker hangs until the first one commits. Put FOR UPDATE SKIP …

Monitor Logical Replication Slot Stats with pg_stat_replication_slots How full does a logical decoding worker's memory buffer get before PostgreSQL gives up holding a transaction in RAM and starts writing it to disk? pg_stat_replication_slots answers that question per slot, in concrete numbers — spilled-transaction …

Summarize the Buffer Cache with pg_buffercache_summary PostgreSQL 16 added a shortcut to a question DBAs have always had to answer the expensive way. Before that release, checking how full the shared buffer cache was — how many buffers were dirty, how many pinned, how hot the clock-sweep usage counts were running — …

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 how …

Monitor Last VACUUM Runs with pg_stat_user_tables When a table's dead tuple count keeps climbing and last_autovacuum stays weeks old, autovacuum is falling behind on that table. pg_stat_user_tables shows that gap in a single query, without tailing logs or guessing at a schedule. Purpose and Overview pg_stat_user_tables …

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 …