partitioning - implement partitioning on PostgreSQL tables

Question

I'm implementing partition to PostgreSQL (version 12). I'll use the transaction_date (year) to create the partitions, one partition per year, trigger and function to save data on the right partition.

I'm applying the implementation Using Inheritance and how can I load the partitions for 2009, 2010, 2011, ...?

All the examples I found starts with an empty table.

Thanks

标签： partitioningpostgresql-12

Answer 1

我正在应用使用继承的实现

不要那样做。

使用 Postgres 12，您应该使用声明式分区，它也不需要使用触发器。

create table your_table
(
  ..., 
  transaction_date timestamp not null
)
partition by range (transaction_date);

create table your_table_p2018
  partition of your_table
  for values from ('2018-01-01') to ('2019-01-01');

create table your_table_p2019
  partition of your_table
  for values from ('2019-01-01') to ('2020-01-01');

create table your_table_p2020
  partition of your_table
  for values from ('2020-01-01') to ('2021-01-01');

插入your_table将自动路由到适当的分区。