Partitioning is one of the most desired features of PostgreSQL, widely adopted by developers. This is not only true for in PostgreSQL 15, but also for older versions which did not provide as many features as the latest version of the database. That's why you should know not only how to properly partition tables, but how to find out which partition a piece of data belongs to.
Table of Contents
So, there's a small question which often arises: How DO I figure out which partition I just inserted my data into? Some small “old consultant’s trickery” can help to find out.
To get started, you first have to create a partitioned table:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
CREATE TABLE t_turnover ( id serial, country text, t timestamptz, task text, turnover numeric ) PARTITION BY LIST (country); CREATE TABLE t_austria PARTITION OF t_turnover FOR VALUES IN ('Austria'); CREATE TABLE t_usa PARTITION OF t_turnover FOR VALUES IN ('USA'); CREATE TABLE t_ger_swiss PARTITION OF t_turnover FOR VALUES IN ('Germany', 'Switzerland'); |
In this case, you've created a simple table to store turnover. Currently, 4 languages are supported. However, you will often want to tell PostgreSQL that all other data should go to a default partition. Here's how to create such a default partition:
|
1 2 |
CREATE TABLE t_rest PARTITION OF t_turnover DEFAULT ; CREATE TABLE |
This table will contain all data that is not supposed to go to any other partition.
Now, let's insert into this table and see what happens:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
sample=# x Expanded display is on. sample=# INSERT INTO t_turnover (country, t, task, turnover) VALUES ('Uganda', now(), 'Some task', 200) RETURNING tableoid::regclass, *; -[ RECORD 1 ]--------------------------- tableoid | t_rest id | 1 country | Uganda t | 2022-11-01 09:35:38.991547+01 task | Some task turnover | 200 |
We've applied a little trick here: In the case of INSERT, you can use RETURNING *, which is a commonly used and simple way to see what you've just inserted. However, there is more: There is a hidden column called “tableoid”: It represents the object ID of the table you've just inserted. Note that this is the object ID of the partition - not of the parent table.
So far, you've seen that you can use the tableoid to determine the partition on INSERT. However, you can also do the same thing when you query the table:
|
1 2 3 4 5 6 |
sample=# SELECT tableoid, tableoid::regclass, * FROM t_turnover ORDER BY id; tableoid | tableoid | id | country | t | task | turnover ----------+-----------+----+---------+-------------------------------+--------------------+---------- 27616 | t_rest | 1 | Uganda | 2022-11-01 09:35:30.193676+01 | Some task | 200 27597 | t_austria | 3 | Austria | 2022-11-01 11:11:40.583728+01 | PostgreSQL support | 900 (3 rows) |
The regclass data type is a convenient way to turn an object ID into a human readable string. If you use this trick to determine the partition the data is in, you can apply even more trickery: Nobody stops you from using aggregate functions on this string to generate some statistics about the content of your partitions:
|
1 2 3 4 5 6 7 8 9 |
sample=# SELECT tableoid::regclass, count(*) FROM t_turnover GROUP BY 1 ORDER BY 1; tableoid | count -----------+------- t_austria | 1 t_rest | 1 (2 rows) |
The advantage over a query which focuses on the system catalog is that you can get a real count and not just estimates. It's also a lot easier than unioning to the result of those individual partitions.
If you want to learn more about PostgreSQL and if you happen to be interested in how to modify data structures efficiently, I want to recommend my post about "ALTER TABLE Done Right" on our website.
Also, if you want to learn more about PostgreSQL and if you are interested in other topics, feel free to leave a comment. We're always eager to post more useful content that's relevant to as many people out there as possible.
+43 (0) 2622 93022-0
office@cybertec.at
Thank you for sharing this interesting blog. Just few questions about the first command of the "Selecting from a partitioned table", I didn't catch why it is written 3 rows? However it lists only 2? And, why the id was 1 and 3? (Or maybe, you didn't show some insert and delete row). Thank you again.