Administration Status
List 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 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 MoreGRANT SELECT on All Tables in PostgreSQL — with Examples
Mar 27, 2026 / · 12 min read · postgresql database administration sql queries security access control permissions etl postgres dba pg_default_acl pg_class ·Grant SELECT on All Tables in PostgreSQL Third-party ETL connectors, BI platforms, and audit users all need read access to PostgreSQL tables — without write permissions that could corrupt production data. Granting SELECT table by table works until someone adds a new table and the connector silently skips it; ALTER …
Read MoreKill Idle PostgreSQL Sessions with SQL
Mar 24, 2026 / · 9 min read · postgresql database administration sql queries connection management performance tuning pg_stat_activity postgres dba ·Kill Idle PostgreSQL Sessions with SQL The connection count sits at max_connections, new clients are being refused, and pg_stat_activity is full of sessions that have run nothing for hours. Freeing the slots takes one statement, pg_terminate_backend(), but the choices around it decide whether the cleanup is safe: which …
Read More