Sql-Queries
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 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 MorePostgreSQL Preset Options Settings Query Guide
Mar 21, 2026 / · 4 min read · postgresql database administration sql queries configuration system views pg_settings postgres dba ·PostgreSQL Preset Options Settings Query Guide This PostgreSQL query retrieves preset option settings from the pg_settings system view. Preset options are read-only, compiled-in values that describe fundamental characteristics of the PostgreSQL installation — they cannot be changed at runtime. Purpose and Overview …
Read MorePostgreSQL Query Planning Settings Query Guide
Mar 20, 2026 / · 5 min read · postgresql database administration sql queries configuration query planning performance tuning pg_settings postgres dba pg_stat_activity pg_stat_statements pg_stat_user_tables ·PostgreSQL Query Planning Settings Query Guide This PostgreSQL query retrieves all query tuning and statistics collection settings from the pg_settings system view. These parameters control how the query planner selects execution strategies, estimates costs, and collects runtime statistics. Purpose and Overview The …
Read More