Get number of partitions in PostgreSQL database

Question

What is the most efficient way to get the number of partitions created in the database?

I am using PostgreSQL API for C++.

Answer 1

This is how you can select all the names of the table partitions:

SELECT
    nmsp_parent.nspname AS parent_schema,
    parent.relname      AS parent,
    nmsp_child.nspname  AS child,
    child.relname       AS child_schema
FROM pg_inherits
    JOIN pg_class parent        ON pg_inherits.inhparent = parent.oid
    JOIN pg_class child         ON pg_inherits.inhrelid   = child.oid
    JOIN pg_namespace nmsp_parent   ON nmsp_parent.oid  = parent.relnamespace
    JOIN pg_namespace nmsp_child    ON nmsp_child.oid   = child.relnamespace

It can be used to count as well:

SELECT
    nmsp_parent.nspname     AS parent_schema,
    parent.relname          AS parent,
    COUNT(*)
FROM pg_inherits
    JOIN pg_class parent        ON pg_inherits.inhparent = parent.oid
    JOIN pg_class child     ON pg_inherits.inhrelid   = child.oid
    JOIN pg_namespace nmsp_parent   ON nmsp_parent.oid  = parent.relnamespace
    JOIN pg_namespace nmsp_child    ON nmsp_child.oid   = child.relnamespace
GROUP BY
    parent_schema,
    parent;

Answer 2

Traditional partitioning is implemented with inheritance. Every inheriting table has an entry in the system catalog pg_inherits. The number of partitions for a given parent table can simply be counted with:

SELECT count(*) AS partitions
FROM   pg_catalog.pg_inherits
WHERE  inhparent = 'schema.parent_tbl'::regclass;

The solution also works for declarative partitioning in Postgres 10 or later because, quoting the manual:

Individual partitions are linked to the partitioned table with inheritance behind-the-scenes;

Related question on dba.SE:

• Get all partition names for a table