CODEWITHSUNDEEP
postgresqltriggerspartitioning
Karan Gupta
Karan Gupta

Reputation: 529

Table partitioning in PostgreSQL 11 with automatic partition creation?

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.

Upvotes: 3

Views: 5832

Answers (1)

Jaisus
Jaisus

Reputation: 1109

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

Upvotes: 2

Related Questions