Table partitioning in PostgreSQL 11 with automatic partition creation?

Question

I need to maintain audit table and since the number of changes are going to be huge, I need an efficient way of dealing with the problem. The solution which I have thought is to record only the changed column in the audit table and partition it on the createdon column quarterly or half-yearly.

I wanted to know if there is anything like 'interval partition' of oracle? If not then how can I achieve it?

I want that every 6 months a new partition is created automatically as the row is inserted.

I am using postgres 11 as my db.

You can't - there is no "automatic partition creation" in Postgres. Not in 11 nor in the upcoming 12. You need something like github.com/pgpartman/pg_partman for that. But why not simply have a cron job that runs every 6 months and creates the partitions for the next 6 months? — user330315
– user330315, Commented Jul 5, 2019 at 6:28
yeah I will do that only if there's no other way. Thanks anyway. — Karan Gupta
– Karan Gupta, Commented Jul 5, 2019 at 7:11
@a_horse_with_no_name Is it impossible with a trigger? (genuine question, I need to achieve the same thing than OP). My table is partitioned per week based on a column timestamp. — GG.
– GG., Commented Oct 16, 2019 at 21:39
@GG. - Partitioned tables cannot have BEFORE / FOR EACH ROW triggers. — klin
– klin, Commented Oct 16, 2019 at 22:03

Jaisus · Accepted Answer · 2019-10-17 12:07:42Z

2

I do not think there is any magic configuration that make your life easier on this point : https://www.postgresql.org/docs/11/ddl-partitioning.html

If you want the table auto-created, I think you have two major possibilities :

Verify each data at the in of the 'mother' table to see if it fits in an already present partition (trigger, if huge amount of inserts it could be a problem)
Check once in a while that you already have the partitions that are going to be needed in the future. For this one pg_partman is going to be your best ally.

As an example, few years ago, I had done a partition mechanism when there was only the declarative one and not any possibility to add pg_partman. With the trigger mechanism for 15 million rows per month it still works like a charm.

If you do not want to harm your performances EVER (and especially if you do not know how large your system is going to grow) I recommand to you the same response than in a_horse_with_no_name comment : use pg_partman.

If you cannot use it, like it was the case for me, adopt one of the two philosophies (trigger or advance table creation by crontask (for example)).

answered Oct 17, 2019 at 12:07

Jaisus

1,1176 silver badges15 bronze badges

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

2 Comments

Jaisus Over a year ago

Why using a windows licence for a postgresql server? Can't you just roll over with a Linux distribution?

Winfred Huang Over a year ago

because sometimes they just can't.

Collectives™ on Stack Overflow

Table partitioning in PostgreSQL 11 with automatic partition creation?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related