Database Administration
Monitor PostgreSQL ANALYZE Progress with pg_stat_progress_analyze
Jun 18, 2026 / · 6 min read · postgresql database administration sql queries statistics progress reporting pg_stat_progress_analyze postgres dba ·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 …
Read MoreQuery PostgreSQL I/O Statistics with pg_stat_io
Jun 17, 2026 / · 6 min read · postgresql database administration sql queries statistics io pg_stat_io postgres dba ·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 …
Read MoreTRUNCATE vs DELETE vs DROP: Remove Rows Fast in PostgreSQL
Jun 16, 2026 / · 7 min read · postgresql database administration sql queries truncate delete maintenance postgres dba ·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 …
Read Moretimestamptz and tzdata: Avoid Shifted PostgreSQL Timestamps
Jun 15, 2026 / · 7 min read · postgresql database administration sql queries data types time zones timestamptz postgres dba ·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 …
Read MoreDetect Soft Delete Patterns in PostgreSQL
Apr 3, 2026 / · 5 min read · postgresql database administration performance autovacuum bloat information_schema pg_stat_user_tables ·Detecting Soft Delete Patterns in PostgreSQL Soft deletes are a common application pattern. Instead of removing a row with a DELETE statement, the application marks it as deleted by setting a column — typically deleted_at — to a non-null timestamp. The row stays in the table forever. The application simply filters it …
Read Morepg_stat_user_tables — Find Insert-Only Tables in PostgreSQL
Apr 2, 2026 / · 5 min read · postgresql database administration performance autovacuum monitoring pg_stat_user_tables pg_stat_bgwriter ·Identify Insert-Only Tables in PostgreSQL Using pg_stat_user_tables Not all PostgreSQL tables behave the same way. Most application tables have a mix of inserts, updates, and deletes. A minority of tables receive only inserts. These append-only tables — event logs, audit trails, sensor readings, message queues — have …
Read MoreGRANT SELECT on All Tables in PostgreSQL — with Examples
Mar 27, 2026 / · 11 min read · postgresql database administration sql queries security access control permissions etl postgres dba pg_default_acl ·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 …
Read MoreKill Idle PostgreSQL Sessions with SQL
Mar 24, 2026 / · 5 min read · postgresql database administration sql queries connection management performance tuning pg_stat_activity postgres dba ·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 …
Read MoreCount PostgreSQL Sessions by State with SQL
Mar 23, 2026 / · 4 min read · postgresql database administration sql queries connection management monitoring pg_stat_activity postgres dba ·Count PostgreSQL Sessions by State This PostgreSQL query returns a count of all server sessions grouped by their current state. It gives a fast at-a-glance view of how connections are distributed across active, idle, and problematic states. Purpose and Overview PostgreSQL tracks the state of every backend connection in …
Read MorePostgreSQL Miscellaneous Settings Query Guide
Mar 22, 2026 / · 5 min read · postgresql database administration sql queries configuration system views pg_settings postgres dba ·PostgreSQL Miscellaneous Settings Query Guide This PostgreSQL query retrieves configuration settings that fall outside all standard category groups — including customized options, process title settings, and any settings added by extensions or custom builds. It works as a catch-all complement to the other …
Read More