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++.

Frank Heikens · Accepted Answer · 2011-12-26 09:19:46Z

14

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;

answered Dec 26, 2011 at 9:19

Frank Heikens

129k26 gold badges157 silver badges153 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Shay Over a year ago

And how do I delete the oldest partition? (The partition which was created first?)

Frank Heikens Over a year ago

If the lowest OID is the oldest partition, you could use MIN(child.oid) to find this partition. You can use this to find the name and schema for this table and use it to DROP this table. A stored procedure makes it a bit easier to maintain.

Erwin Brandstetter · Accepted Answer · 2020-06-27 23:32:29Z

9

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;

Collectives™ on Stack Overflow

Get number of partitions in PostgreSQL database

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related