Pg_class
Check Replica Identity Settings in PostgreSQL Logical replication in PostgreSQL requires each replicated table to have a replica identity. The replica identity tells PostgreSQL which columns to include in the WAL record for UPDATE and DELETE operations so the subscriber can identify the row being changed. If replica …
Read MoreAudit Single vs Multi-Column Indexes in PostgreSQL
Apr 16, 2026 / · 4 min read · postgresql administration indexing database pg_index pg_class pg_namespace ·Audit Single vs Multi-Column Indexes in PostgreSQL Not all indexes are created equal. A single-column index covers one column and is simple to reason about. A multi-column (composite) index covers two or more columns and can serve a wider range of queries — but only when the leading columns match the query filter. As a …
Read MoreList PostgreSQL Partitioned Tables with SQL
Apr 9, 2026 / · 4 min read · postgresql administration partitioning database pg_class pg_namespace information_schema pg_inherits ·How to List PostgreSQL Partitioned Tables PostgreSQL table partitioning splits a large table into smaller physical pieces called child partitions. This improves query performance and simplifies data lifecycle management. But as a DBA, you need a quick way to see which tables in your database use partitioning — and …
Read MoreList PostgreSQL Object Comments with SQL
Apr 4, 2026 / · 4 min read · postgresql administration schema database pg_description pg_statio_all_tables information_schema pg_class pg_index pg_namespace pg_proc ·List PostgreSQL Object Comments with SQL PostgreSQL allows you to attach plain-text comments to tables, columns, indexes, functions, and other database objects using the COMMENT ON command. These comments are stored in the system catalog and are visible in psql, pgAdmin, and any tool that reads pg_description. They are …
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 MoreFind PostgreSQL Tables That Need VACUUM FREEZE
Apr 2, 2026 / · 3 min read · postgresql administration vacuum maintenance database pg_class pg_settings pg_stat_activity ·Find PostgreSQL Tables That Need VACUUM FREEZE PostgreSQL uses transaction IDs (XIDs) to track which rows are visible to which transactions. Over time, these IDs age. When a table's oldest unfrozen XID gets too old, PostgreSQL triggers an aggressive autovacuum to freeze it. This is controlled by the …
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 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