2
CREATE SCHEMA plugin_work_queue;

CREATE TABLE IF NOT EXISTS plugin_work_queue.plugin_executions
(
    execution_key        bigserial   NOT NULL,
    tenant_id            uuid        NOT NULL,
    creation_time        timestamptz NOT NULL,
    message              bytea       NOT NULL
)
PARTITION BY RANGE (creation_time);


CREATE SCHEMA partman;
CREATE EXTENSION pg_partman WITH SCHEMA partman;


SELECT partman.create_parent(p_parent_table => 'plugin_work_queue.plugin_executions',
                             p_control => 'creation_time',
                             p_type => 'native',
                             p_interval=> 'daily',
                             p_premake => 30
           );

So I've got a database much like the one described above. This works and I can partition my data by creation_time.

But ideally I'd actually like to have two levels of partitioning. I'd like to first partition by tenant_id, and then sub-partition by creation_time.

Unfortunately I haven't found this to be straightforward.

  1. pg_partman doesn't support partitioning by uuid. Fine, I can extract the lower 4 bytes of the uuid and use that as a partition key.

  2. pg_partman doesn't seem to support partitioning by hash, which I believe would be the ideal way to manage this (but I'm unsure).

  3. The documentation for sub-partitions is a bit less than ideal in pg_partman.

I'd like to make operations like "drop this tenants partitions" or "drop partitions older than date X" easy, hence the desire for a tiered partitioning.

IBit
  • 380
  • 2
  • 9
  • There are no such limitations with native partitioning. Why don't you use that instead? –  Dec 21 '21 at 21:24
  • pg_partman along with pg_cron are being used to automatically manage the time based partitioning and drop old tables. I'm not aware of a way to do the partitioning natively while still having that functionality. – IBit Dec 21 '21 at 21:32
  • you can use pg_cron without pg_partman - just run "normal" SQL statements to create the partitions in advance. Or create a stored procedure that is run through pg_cron. –  Dec 21 '21 at 21:35
  • Could you provide an example of how that would work? Or links to examples? I haven't found a way to do this looking around. – IBit Dec 21 '21 at 21:41
  • 3
    [This](https://dbfiddle.uk/?rdbms=postgres_14&fiddle=eb0c46b9222f8011e28a80c26c65c6e5) is more or less copied [from the manual](https://www.postgresql.org/docs/current/sql-createtable.html) –  Dec 21 '21 at 22:08

0 Answers0