How to find which partition contains a specific row in my PostgreSQL database?

To find which partition contains a specific row in your PostgreSQL database, you can use the pg_partman extension which provides a set of functions to manage partitioned tables.

Assuming you have already partitioned your table using the pg_partman extension, you can use the following query to find which partition contains a specific row:

SELECT partition_name FROM pg_partman_list_partitions(‘my_partitioned_table’) 
WHERE p_start <= ‘specific_row_id’ AND p_end > ‘specific_row_id’;

In this query, my_partitioned_table is the name of your partitioned table, and specific_row_id is the ID of the row you are looking for.

The pg_partman_list_partitions function returns a list of all partitions of the specified partitioned table. The WHERE clause filters the list to only include partitions that cover the specified row ID. The partition name is then returned by the SELECT statement.

Note that if your partitioning is based on a range of values rather than a single value like the row ID, you would need to modify the query accordingly. Also, if you have partitioned your table manually without using pg_partman, you may need to use a different query to determine which partition contains a specific row.

About Shiv Iyer 157 Articles
Open Source Database Systems Engineer with a deep understanding of Optimizer Internals, Performance Engineering, Scalability and Data SRE. Shiv currently is the Founder, Investor, Board Member and CEO of multiple Database Systems Infrastructure Operations companies in the Transaction Processing Computing and ColumnStores ecosystem. He is also a frequent speaker in open source software conferences globally.