Administration
List All Schemas in Your PostgreSQL Database
Apr 13, 2026 / · 8 min read · postgresql administration database information_schema pg_namespace pg_class pg_tables ·How to List All Schemas in a PostgreSQL Database information_schema.schemata is the SQL-standard view for listing schemas, and it returns one row per schema in the current database that the connected role owns or holds a privilege on. Behind it sits pg_namespace, the system catalog that stores every schema with its …
Read MoreList PostgreSQL Partitioned Tables with SQL
List Partitioned and Ordinary Tables in PostgreSQL Before you change a retention job, plan a migration, or add a partition for next month, you need a plain list of which tables are partitioned and which are not. pg_class joined to pg_namespace gives that list for the whole database in one query, and a count from …
Read MoreFind PostgreSQL Tables Without a Primary Key
How to Find PostgreSQL Tables Without a Primary Key A missing primary key is one of the most common and damaging database design oversights. Without a primary key, PostgreSQL has no reliable way to uniquely identify a row. This causes problems with logical replication, ORM frameworks, and application-level updates or …
Read MoreList All Views in a PostgreSQL Database with SQL
Apr 7, 2026 / · 4 min read · postgresql administration schema database information_schema pg_views pg_matviews pg_depend ·How to List All Views in a PostgreSQL Database Views are saved SQL queries stored in the database. A production database can accumulate dozens or hundreds of views over time — many created by developers, some by tools, and some that are no longer used. Knowing what views exist, which schema they belong to, and what …
Read MoreList PostgreSQL Enum Types and Their Values with SQL
Apr 6, 2026 / · 4 min read · postgresql administration schema database data types pg_type pg_enum information_schema pg_namespace ·How to List All Enum Types in a PostgreSQL Database PostgreSQL supports user-defined enum types — a fixed ordered set of string values, each occupying four bytes on disk. Enums are common in application schemas for columns like status, role, or priority. Once created, their allowed values are managed in the database …
Read MoreList 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 MoreList PostgreSQL Object Comments with SQL
Apr 4, 2026 / · 6 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 pg_description is where PostgreSQL keeps every comment attached with COMMENT ON, whether the target is a table, a column, an index, or a function. Psql shows these comments, and so does any tool that reads the catalog, which makes them a cheap way to keep a schema …
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 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 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