Postgres Dba

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 …

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 …

Grant SELECT on All Tables in PostgreSQL Third-party ETL connectors, BI platforms, and audit users all need read access to PostgreSQL tables — without write permissions that could corrupt production data. Granting SELECT table by table works until someone adds a new table and the connector silently skips it; ALTER …

Kill Idle PostgreSQL Sessions with SQL These PostgreSQL scripts terminate idle sessions that have been inactive for more than 15 minutes. Two variants are provided: one that targets idle sessions across all databases on the server, and one scoped to only the currently connected database. Purpose and Overview Idle and …