CODEWITHSUNDEEP
sqlpostgresqlinheritancedatabase-partitioning
Shay
Shay

Reputation: 693

Get number of partitions in PostgreSQL database

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

I am using PostgreSQL API for C++.

Upvotes: 7

Views: 23466

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656331

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:

Upvotes: 9

Frank Heikens
Frank Heikens

Reputation: 126991

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;

Upvotes: 14

Related Questions