Technology

Ruby on Rails vs PostgreSQL 14 partitioning primary keys

Starting from PostgreSQL 10 we have available new type of paritioning, which is declarative one. Before we had inheritance which is also good but has some limitations. Now, with declarative partitioning user need not to specify partition name while inserting data. To create partitioned table:

CREATE TABLE tablename 
( 
  id BIGSERIAL NOT NULL, 
  created_at TIMESTAMP WITHOUT TIME ZONE NOT NULL,
  CONSTRAINT tablename_pkey PRIMARY KEY (id, created_at)
) PARTITION BY RANGE(created_at);

There are few things worth explaining. First one is BIGSERIAL data type. It is a bigger form of integer type with automatic sequence creation, which is very convenient. Second thing is PARTITION BY RANGE(columnname). It defined that the data will be spread across paritions based on created_at date range. It positively affects query performance.

To create a partition:

CREATE TABLE tabelename_2022_09 PARTITION OF tablename
    FOR VALUES FROM ('2022-09-01 00:00:00.00') TO ('2022-09-30 23:59:59.99');

With table inheritance I’ve used partitioning with over 1000 tables. Generally speaking, PostgreSQL, even older versions, could easily handle 100 000 tables on single server and data volume around 10 TB. However with declarative partitioning you need to remember, that when executing a query o master table there will be shared lock on each and every parition and it costs memory. Althought it migth be a little simplification you need to increase max_locks_per_transaction parameter to fit required number of partitions created, attached. If you do not need online access to some old data or created future paritions upfront then either do not attach them or just detach if unsed.

Last and the most important thing related to multi-column primary key. Ruby on Rails and ActiveRecord library does not support it by default. You could use some gem to overcome this limitation, but what you only need is to point the specific colum in model definition:

self.primary_key = :id

It works just fine.