Managing large-scale databases often requires table partitioning, a technique where one large logical table is split into several smaller physical pieces. In PostgreSQL, the partitioned table itself is a “virtual” table that holds no data; instead, the data is stored in child partitions that are associated with the parent. Identifying these parent tables is a vital step for database administrators who need to monitor storage distribution and optimize query performance.
Whether you are performing a routine audit or troubleshooting a complex schema, PostgreSQL provides several built-in methods to view your partitioned structures. You can use simple terminal meta-commands or query the system catalogs directly to retrieve detailed metadata about your partitioning strategies. This guide explains how to effectively list and inspect all partitioned tables across your database instance.
Table of Contents
Key Takeaways for Listing Partitions
- Virtual Nature → Partitioned tables are logical anchors and do not store data themselves; storage is handled by the physical child partitions.
- System Catalogs → PostgreSQL stores information about all relations, including partitioned ones, in the
pg_classsystem catalog. - relkind Attribute → Partitioned tables are distinguished in the system by a specific relation kind (identified as ‘p’ in the
relkindcolumn). - Search Path → When listing tables by name alone, PostgreSQL follows a
search_path, so ensure your schema is visible or use qualified names. - Partition Tree → The
pg_partition_tree()function is a powerful tool for visualizing the entire hierarchy of a partitioned table.
Method 1: Using psql Meta-Commands
The quickest way to list partitioned tables within the psql interactive terminal is by using specific backslash commands.
Command Syntax: To list all partitioned relations (tables and indexes): \dP
To list only partitioned tables with human-readable details: \dP+
Example Output:
List of partitioned relations
Schema | Name | Owner | Type | Table | Description
--------+--------+----------+--------+----------+-------------
public | orders | postgres | table | |
(1 row)
The \dP command is superior to the standard \dt because it specifically filters for partitioned objects, making it easier to manage complex environments. For a broader look at object management, see Finding your tables in PostgreSQL.
Method 2: Querying pg_class via SQL
If you are writing an automation script or need to filter by specific attributes, querying the pg_class system catalog is the most flexible approach.
Command Syntax:
SELECT relname AS table_name,
schemaname AS schema_name
FROM pg_stat_user_tables
WHERE relid IN (SELECT oid FROM pg_class WHERE relkind = 'p');
Understanding the Output: This query returns the names of all user-defined partitioned tables. The relkind = 'p' filter ensures that standard tables, views, and sequences are excluded from your results. To understand how these tables fit into your overall structure, refer to A Comprehensive Guide to PostgreSQL Database Structure.
See also: Mastering the Linux Command Line — Your Complete Free Training Guide
Method 3: Inspecting Partition Trees with Functions
To see not just the parent table but also its associated child partitions, you can use the pg_partition_tree function.
Command Syntax:
SELECT * FROM pg_partition_tree('your_parent_table_name');
Output expectation: The output provides the OID (relid), the parent OID, and a boolean isleaf indicating if the row represents a physical data-holding partition or another sub-partition level. This is essential for verifying if your horizontal partitioning has been implemented correctly across all ranges or lists.
Step-by-Step Process to Audit Partitioned Tables
- Launch psql: Connect to your database using the terminal or your preferred client application.
- Run Global List: Execute
\dPto identify which tables in your database are partitioned. - Check Partition Key: To see how the data is being routed, query the partition key:
SELECT pg_get_partkeydef('table_name'::regclass);. - Verify Child Status: Use
pg_partition_tree('table_name')to confirm all child partitions are attached and active. - Check Storage: Combine your list with size functions like
pg_relation_size()to see how much data each partition consumes.
Partition Listing Summary
| Task | Recommended Command | Output Type |
|---|---|---|
| Quick Overview | \dP | List of parent objects |
| Detailed Metadata | \dP+ | Includes owner and description |
| List Child Partitions | pg_partition_tree('name') | Hierarchy of the table |
| View Routing Logic | pg_get_partkeydef(oid) | Partition key definition |
FAQs
Does \dt show partitioned tables? Yes, but it lists them alongside regular tables. Use \dP if you want to isolate only the partitioned structures.
How can I find partitioned tables in a specific schema? You can qualify your psql command: \dP myschema.*. Alternatively, add a schema filter to your SQL query against pg_class.
Can I see the ranges or values for each partition? Yes, but you must inspect the individual child tables. psql’s \d+ parent_table_name will show a list of child partitions and their specific bounds (e.g., FOR VALUES FROM...).
