Pg_stat_user_tables
Monitor Last VACUUM Runs with pg_stat_user_tables
Jul 12, 2026 / · 8 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 A table's dead tuple count climbing while last_autovacuum stays weeks old isn't noise — it's the clearest sign autovacuum is falling behind, and pg_stat_user_tables is where that gap becomes visible in a single query, without tailing logs or guessing at a schedule. …
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 / · 4 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, which means they can only count up to about 2 billion. When the counter gets close to the limit, PostgreSQL must freeze old transaction IDs to …
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 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 MorePostgreSQL Table Row Count Estimates with SQL
Mar 14, 2026 / · 4 min read · postgresql database administration sql queries performance tuning statistics pg_stat_user_tables postgres dba pg_class pg_namespace information_schema ·PostgreSQL Table Row Count Estimates with SQL This PostgreSQL query returns estimated row counts for all user tables, sorted by the largest tables first. It reads from the statistics collector rather than scanning the tables, making it near-instant even on tables with billions of rows. Purpose and Overview Counting …
Read MorePrioritize PostgreSQL Table Analysis for Performance
Apr 25, 2024 / · 2 min read · PostgreSQL database performance optimization query pg_stat_user_tables ·Prioritize PostgreSQL Table Analysis: Unlocking Performance Insights Maintaining optimal PostgreSQL database performance requires regular table analysis. This process lets the query planner make informed decisions based on accurate table statistics. This article dives into a valuable PostgreSQL script revealing tables …
Read More