Performance
PostgreSQL Database Statistics with pg_stat_database
Apr 15, 2026 / · 4 min read · postgresql administration monitoring database performance pg_stat_database ·Monitor PostgreSQL Databases with pg_stat_database PostgreSQL tracks activity at the database level in the pg_stat_database view. Every database on the server has a row, and each row contains counters for commits, rollbacks, cache hits, disk reads, deadlocks, temp file usage, and more. This is one of the first places …
Read MoreCancel vs Terminate PostgreSQL Backends Explained
Cancel vs Terminate PostgreSQL Backends: What Every DBA Should Know PostgreSQL gives you two tools for dealing with problem backends: pg_cancel_backend() and pg_terminate_backend(). They sound similar but behave very differently. Using the wrong one can disrupt users unnecessarily, or leave a problematic connection in …
Read MoreFind Missing Indexes in PostgreSQL with SQL
Apr 4, 2026 / · 3 min read · postgresql performance optimization indexing database pg_stat_user_tables pg_stat_all_tables ·How to Find Missing Indexes in PostgreSQL A missing index means PostgreSQL reads every row in a table to find what it needs. This is called a sequential scan. For small tables, that is fine. For large tables, it is slow and wastes CPU and I/O. PostgreSQL tracks sequential scans in the pg_stat_all_tables view. You can …
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 MoreIdentify 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 MoreEstimate 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 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 MoreUnderstanding PostgreSQL pg_settings and pg_file_settings
Aug 26, 2025 / · 3 min read · postgresql database sql pg_file_settings pg_settings administration configuration tuning performance ·Understanding PostgreSQL pg_settings and pg_file_settings with Practical Examples PostgreSQL offers powerful catalog views for examining configuration values that shape the performance and behavior of your database. Among these, pg_settings and pg_file_settings are essential tools for administrators who want to ensure …
Read MoreMonitor PostgreSQL Active Sessions with pg_stat_activity
Aug 25, 2025 / · 3 min read · postgresql sql database monitoring performance queries pg_stat_activity ·Monitoring PostgreSQL Active Sessions with pg_stat_activity Explained As databases scale, monitoring active connections and queries in PostgreSQL becomes essential for performance tuning, troubleshooting, and ensuring stability. PostgreSQL provides a powerful system view called pg_stat_activity that contains detailed …
Read More