Scripts
List Foreign Key Constraints in PostgreSQL
Apr 5, 2026 / · 4 min read · postgresql administration schema database pg_constraint information_schema ·List Foreign Key Constraints in PostgreSQL Foreign key constraints enforce referential integrity between tables. They guarantee that a value in one table's column always has a matching row in another table. Knowing which foreign keys exist — and how they are defined — is essential before dropping tables, renaming …
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 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 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 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 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 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 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 More