Pg_stat_user_tables
Monitor Last VACUUM Runs with pg_stat_user_tables
Jul 12, 2026 / · 9 min read · postgresql database administration sql queries vacuum autovacuum pg_stat_user_tables dead tuples postgres dba ·Monitor Last VACUUM Runs with pg_stat_user_tables When a table's dead tuple count keeps climbing and last_autovacuum stays weeks old, autovacuum is falling behind on that table. pg_stat_user_tables shows that gap in a single query, without tailing logs or guessing at a schedule. Purpose and Overview pg_stat_user_tables …
Read MoreIdentify Running PostgreSQL Autovacuum Worker Sessions
May 24, 2026 / · 7 min read · pg_stat_activity pg_stat_progress_vacuum autovacuum backend_type vacuum administration pg_stat_user_tables ·Identify Running PostgreSQL Autovacuum Worker Sessions Autovacuum lag is a slow accumulation. Every UPDATE rewrites the row and creates a new HOT chain, the launcher falls behind during write spikes, and over weeks a busy OLTP cluster ends up with three workers stuck on a handful of huge tables while smaller ones wait …
Read MoreMonitor PostgreSQL Vacuum Progress with pg_stat_progress_vacuum
May 16, 2026 / · 4 min read · pg_stat_progress_vacuum vacuum autovacuum performance monitoring administration pg_stat_activity pg_stat_user_tables ·Tracking Active Vacuum and Autovacuum Workers in PostgreSQL PostgreSQL vacuum is the maintenance process that removes dead tuples left behind by updates and deletes, reclaims storage, and prevents transaction ID wraparound. On active databases, autovacuum runs continuously in the background, but knowing whether it is …
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 PostgreSQL Transaction ID Wraparound Risk
Apr 3, 2026 / · 5 min read · postgresql administration vacuum monitoring database pg_class pg_database pg_settings pg_stat_activity pg_stat_user_tables ·Detect PostgreSQL Transaction ID Wraparound Before It Causes Downtime PostgreSQL assigns a transaction ID (XID) to every write transaction. These IDs are 32-bit integers, so the counter wraps around after about 4 billion transactions. Because IDs are compared modulo 2^32, each ID has two billion IDs that count as older …
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 / · 9 min read · postgresql database administration performance autovacuum monitoring pg_stat_user_tables ·Identify Insert-Only Tables in PostgreSQL Using pg_stat_user_tables pg_stat_user_tables keeps three cumulative counters for every user table: rows inserted, rows updated and rows deleted. A table whose update and delete counters sit at zero while the insert counter keeps climbing is an insert-only table. Those tables …
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 MorePostgreSQL Statistics Settings Query Guide This PostgreSQL query retrieves all statistics collection configuration settings from the pg_settings system view. These parameters control what runtime data PostgreSQL collects about query execution, table activity, and performance, feeding into the pg_stat_* monitoring …
Read MoreList PostgreSQL Tables by Size with SQL
Mar 15, 2026 / · 5 min read · postgresql database administration sql queries storage performance tuning pg_class postgres dba information_schema pg_indexes pg_namespace pg_stat_user_indexes pg_stat_user_tables ·List PostgreSQL Tables by Size with SQL This PostgreSQL query lists all tables in the current database sorted by their total size, largest first. It uses pg_total_relation_size() to include the table data, indexes, TOAST storage, and free space map in the size calculation. Purpose and Overview Disk space pressure is a …
Read More