Posts
Estimate PostgreSQL Table Bloat with SQL
Apr 1, 2026 / · 5 min read · postgresql performance vacuum optimization database pg_class pg_namespace pg_stats pg_attribute ·How to Estimate Table Bloat in PostgreSQL When you update or delete rows in PostgreSQL, the old row versions are not removed immediately. They stay in the table as dead tuples until VACUUM cleans them up. Over time, if VACUUM does not keep up, these dead rows pile up and the table grows larger than it needs to be. This …
Read MoreFind PostgreSQL Index Bloat and Wasted Space
Mar 31, 2026 / · 5 min read · postgresql performance indexing optimization database pg_class pg_index pg_namespace pg_stats pg_am pg_attribute pg_stat_user_indexes ·How to Find Index Bloat and Wasted Space in PostgreSQL Just like tables, PostgreSQL indexes can become bloated over time. When rows are updated or deleted, the old index entries are not removed immediately. They stay in the index as dead entries, wasting space and slowing down index scans. B-tree indexes are the most …
Read MoreMonitor PostgreSQL Index Build Progress with SQL
Mar 30, 2026 / · 4 min read · postgresql administration indexing monitoring database pg_stat_activity pg_stat_progress_create_index ·How to Monitor PostgreSQL Index Build Progress Building an index on a large table can take minutes or even hours. Without progress tracking, it is hard to know if the build is almost done or barely started. PostgreSQL 12 introduced the pg_stat_progress_create_index view to fix this. It shows real-time progress for any …
Read MoreMonitor PostgreSQL HOT Updates and Fillfactor
Mar 29, 2026 / · 4 min read · postgresql performance optimization administration database pg_class pg_namespace pg_stat_all_tables ·How to Monitor PostgreSQL HOT Updates and Fillfactor When PostgreSQL updates a row, it normally writes a new row version and creates a new index entry pointing to it. This is safe, but it adds overhead — especially on tables with many indexes. HOT updates (Heap Only Tuple) are a smarter path. When a HOT update happens, …
Read MoreFind Idle in Transaction Sessions in PostgreSQL
Mar 28, 2026 / · 4 min read · postgresql administration monitoring troubleshooting database pg_locks pg_stat_activity ·How to Find Idle in Transaction Sessions in PostgreSQL An idle-in-transaction session is a database connection that has started a transaction but is not currently running a query. It is just sitting there, holding the transaction open. This is different from a regular idle connection, which has no open transaction. …
Read MoreGrant SELECT on All Tables in PostgreSQL
Mar 27, 2026 / · 4 min read · postgresql database administration sql queries security access control permissions etl postgres dba pg_default_acl ·Grant SELECT on All Tables in PostgreSQL This PostgreSQL script creates a read-only user and grants SELECT privileges on all existing tables in a schema, plus ensures the same access is automatically applied to any future tables created in that schema. Purpose and Overview Third-party ETL tools, reporting tools, and …
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 More