List PostgreSQL Partitioned Tables with SQL

calendar Apr 9, 2026 / · 8 min read · partitioning administration pg_class pg_namespace pg_inherits  ·
Share on: linkedin copy

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 pg_inherits shows how many partitions sit under each parent.

Purpose and Overview

Declarative partitioning splits one logical table into many physical pieces. The parent — the partitioned table — holds no rows itself; the PostgreSQL documentation calls it a "virtual" table with no storage of its own. The rows live in its partitions, which are ordinary tables tied to the parent by their partition bounds. On a mature database it is easy to lose track of which large tables were partitioned, which were not, and which partitioning work was never finished.

The system catalogs record all of it. pg_class has a row for every relation, and its relkind column says what kind each one is: p for a partitioned table, r for an ordinary table. Its relispartition flag is true when a table is itself a partition. pg_namespace provides schema names, and pg_inherits records each parent–child link.

The query below returns one row per top-level user table: the schema, the table name, whether it is partitioned or ordinary, the owning role, and how many direct children it has. Partitions themselves are left out, so the list shows each partition tree once, by its root, alongside every ordinary table.

Sample Code

 1SELECT
 2    n.nspname                         AS schema_name,
 3    c.relname                         AS table_name,
 4    CASE c.relkind
 5        WHEN 'p' THEN 'partitioned table'
 6        WHEN 'r' THEN 'ordinary table'
 7    END                               AS table_type,
 8    pg_get_userbyid(c.relowner)       AS owner,
 9    (SELECT count(*)
10       FROM pg_inherits i
11      WHERE i.inhparent = c.oid)      AS direct_children
12FROM pg_class c
13JOIN pg_namespace n ON n.oid = c.relnamespace
14WHERE c.relkind IN ('p', 'r')
15  AND NOT c.relispartition
16  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
17  AND n.nspname !~ '^pg_'
18ORDER BY schema_name, table_name;

To see the whole tree under one partitioned table — every partition, its parent, whether it is a leaf, its depth, and its size — PostgreSQL 12 and later provide pg_partition_tree():

1SELECT
2    relid,
3    parentrelid,
4    isleaf,
5    level,
6    pg_size_pretty(pg_relation_size(relid)) AS size
7FROM pg_partition_tree('your_table_name')
8ORDER BY level, relid::text;

Notes: The first query works on PostgreSQL 10 and later, the release that introduced declarative partitioning and the relispartition column. It returns ordinary tables and the root of each partition tree — not only partitioned tables. Partitions are hidden, and so is a partitioned table that is itself a partition of another table (sub-partitioning), because relispartition is true for it. The second query needs PostgreSQL 12 or later; replace your_table_name with a partitioned table.

Code Breakdown

The first query reads three catalogs and applies three filters.

pg_class and pg_namespace

pg_class holds one row for every relation in the database — tables, indexes, sequences, views, and more. It stores the schema as an OID in relnamespace, so the join to pg_namespace on n.oid = c.relnamespace turns that number into a readable schema name.

Labelling relkind

relkind is a single character. The query keeps only p (partitioned table) and r (ordinary table), and the CASE expression turns those codes into readable labels. Because the WHERE clause already limits relkind to those two values, the CASE never needs a fallback branch.

Excluding partitions with relispartition

The documentation defines relispartition as "true if table or index is a partition". Filtering on NOT c.relispartition removes every partition from the list, so a table with 365 daily partitions shows up as one row instead of 366. The same flag also hides a sub-partitioned table that sits beneath another parent; only the top of each tree appears.

Schema filter

The nspname conditions drop pg_catalog, information_schema, and every other schema whose name begins with pg_ — names the documentation reserves for system use — so only application tables remain.

Owner with pg_get_userbyid

relowner is the OID of the owning role. pg_get_userbyid() returns the role's name for that OID, which is handy when auditing who can alter or drop each table.

Counting children with pg_inherits

pg_inherits stores one row per child, with inhrelid pointing at the child and inhparent at the parent. The scalar subquery counts the rows whose inhparent is the current table. For a partitioned table this is its number of direct partitions. For an ordinary table it is usually zero — and when it is not, that table is the parent in an older inheritance-based design, which is worth knowing (see Version Compatibility).

Key Partitioning Concepts

A few facts about how PostgreSQL partitioning works explain what the query shows.

Partition methods

PostgreSQL partitions by range, by list, or by hash. Range and list partitioning arrived with declarative partitioning in PostgreSQL 10. Hash partitioning — rows placed by the hash of the key, using a modulus and remainder for each partition — was added in PostgreSQL 11.

Default partitions

From PostgreSQL 11, a partitioned table can have a default partition that stores rows matching no other partition. It shows up as one more child in direct_children and in pg_partition_tree().

Sub-partitioning

A partition can itself be declared as a partitioned table, creating another level. The first query shows only the top-level root; pg_partition_tree() shows every level, with level 0 for the root and isleaf true for the tables that actually hold rows.

Where the storage is

Because the parent has no storage of its own, the size of a partitioned table is the sum of its leaf partitions. In the pg_partition_tree() output, the non-leaf rows carry no data; the sizes that matter are on the rows where isleaf is true.

Practical Applications

The list is a starting point for routine partition housekeeping.

Partition inventory before maintenance

Before writing a job that creates next month's partition or drops old ones, run the first query to confirm which tables are partitioned and how many children each has today. A table with far more children than its retention policy allows is a sign that the drop job is not running.

Finding tables that may need partitioning

Ordinary tables in the list are candidates when they grow very large and are always queried by date or another natural key. Pair the list with a size query to pick the biggest ones first.

Spotting a stalled migration

Moving a large table to a partitioned design often happens in stages. If the new parent shows direct_children of zero, or a table you expected to be partitioned still reads ordinary table, the migration has not finished.

Auditing ownership

The owner column shows which role owns each table. A mix of owners across a schema is worth checking before a maintenance job that creates or drops partitions runs under one account.

Version Compatibility

Declarative partitioning, the p relation kind, and the relispartition column all arrived in PostgreSQL 10, so the first query needs version 10 or later. PostgreSQL 11 added hash partitioning, default partitions, and support for primary keys, foreign keys, indexes, and triggers on partitioned tables. PostgreSQL 12 added the partition introspection functions pg_partition_tree(), pg_partition_ancestors(), and pg_partition_root().

Before version 10, partitioning was built with table inheritance plus triggers or rules to route rows. PostgreSQL still documents that method. Those parents are ordinary tables (relkind = 'r'), and their children are not partitions in the declarative sense, so relispartition is false for them. In this query an inheritance parent appears as an ordinary table with a non-zero direct_children count, and its children appear as ordinary tables too.

Best Practices

  • Check the count against the plan — compare direct_children with your retention rules after every scheduled partition job.
  • Use pg_partition_tree for depth — for sub-partitioned tables, the tree function is simpler than a recursive query over pg_inherits.
  • Size the leaves, not the parent — the parent has no storage, so sum leaf partitions when reporting table size.
  • Keep an eye on the default partition — rows landing there usually mean a partition for a new range was never created.
  • Watch for legacy inheritance — an ordinary table with children is an older design that needs different maintenance from declarative partitions.
  • Filter to one schema when needed — add AND n.nspname = 'your_schema' to the first query on large multi-tenant databases.

References

Posts in this series