Retention Policy: move to another partitioned table

[AndrewJackson2020]

Scenario: would like to have one "live" partitioned table that contains only current data and another "archive" partitioned table that contains older data. The "archive" partitioned table would in turn have a it's own retention policy.

Is this possible via retention policy or subpartition? I think the answer is currently no. If it is not how difficult would it be to implement?

[keithf4]

There's no real need for something like this in PostgreSQL from my experience if I'm understanding you and all the live and archive data should be accessible from the same parent table. The "live" partition is just the current partition the data is going into and the older ones should automatically be excluded in read queries via partition pruning as long as you use a condition on the partition key to limit it.

If you're talking about the live and archive tables being completely separate where the "live" table doesn't have access to the "archive" data, that is not currently supported. You would need to design something that shifts table names and attaches old tables to the archive.

In any sub-partition scenario in PG the data always lives at the lowest level. The other levels are just for defining constraints to reach those lower levels. Most practical subpartition scenarios in PG are when the different levels are on completely different columns to allow different partition pruning scenarios or to meet business data separation requirements.

[AndrewJackson2020]

There's no real need for something like this in PostgreSQL from my experience if I'm understanding you and all the live and archive data should be accessible from the same parent table. The "live" partition is just the current partition the data is going into and the older ones should automatically be excluded in read queries via partition pruning as long as you use a condition on the partition key to limit it.

In my experience partition pruning is not at the point where the pattern that I am describing above is completely obsolete. Copy pasted an example below of one issue that I've seen that leaves room for improvement from native partition pruning. Have seen other less easily reproducible stuff as well that motivates me to consider a more defensive posture.

In any sub-partition scenario in PG the data always lives at the lowest level. The other levels are just for defining constraints to reach those lower levels. Most practical subpartition scenarios in PG are when the different levels are on completely different columns to allow different partition pruning scenarios or to meet business data separation requirements.

So it sounds like sub partitioning is definitely not what I'm looking for which I think makes sense.

If you're talking about the live and archive tables being completely separate where the "live" table doesn't have access to the "archive" data, that is not currently supported. You would need to design something that shifts table names and attaches old tables to the archive.

Would it be possible to:

1. disable retention policy on the "live" partition, continue to use partman to prebuild partitions here
2. write user code to implement a custom retention policy from "live" to "archive", maybe use some of the logic from drop_partition_time.sql for this WRT flagging partitions in scope of retention and processing them.
3. Disable partman prebuilding partitions on "archive", instead just use partman for retention on the "archive" partition and depend on the custom retention policy in step 2 for populating.

I'm very new to pg_partman and postgres partitioning in general so I appreciate your above response and patience.

Partition issues

1. Create database

data_dir=$(mktemp -d)
initdb --username=postgres $data_dir
pg_ctl -D $data_dir -l logfile start

2. Setup test

psql \
    postgresql://postgres@localhost/postgres \
    --command="
        CREATE TABLE measurement (
            city_id         INT NOT NULL,
            logdate         DATE NOT NULL,
            peaktemp        INT,
            unitsales       INT
        ) PARTITION BY RANGE (logdate);

        CREATE TABLE measurement_y2006m02 PARTITION OF measurement
            FOR VALUES FROM ('2006-02-01') TO ('2006-03-01');
        CREATE TABLE measurement_y2006m03 PARTITION OF measurement
            FOR VALUES FROM ('2006-03-01') TO ('2006-04-01');

        INSERT INTO measurement (city_id, logdate) VALUES (0, '2006-03-01');
        INSERT INTO measurement (city_id, logdate) VALUES (0, '2006-02-02');

        CREATE TABLE measurement_filter (
            logdate         DATE NOT NULL
        ) ;
        INSERT INTO measurement_filter VALUES ('2006-02-02');
    "

3. Partitions pruned correctly

> SELECT logdate FROM measurement_filter;
  logdate
------------
 2006-02-02
(1 row)

> EXPLAIN SELECT * FROM measurement WHERE logdate IN ('2006-02-02');
                                    QUERY PLAN
----------------------------------------------------------------------------------
 Seq Scan on measurement_y2006m02 measurement  (cost=0.00..33.12 rows=9 width=16)
   Filter: (logdate = '2006-02-02'::date)
(2 rows)

4. Partitions not pruned correctly

> EXPLAIN SELECT * FROM measurement WHERE logdate IN (SELECT logdate from measurement_filter);
                                            QUERY PLAN
---------------------------------------------------------------------------------------------------
 Hash Join  (cost=46.38..152.17 rows=1850 width=16)
   Hash Cond: (measurement.logdate = measurement_filter.logdate)
   ->  Append  (cost=0.00..75.50 rows=3700 width=16)
         ->  Seq Scan on measurement_y2006m02 measurement_1  (cost=0.00..28.50 rows=1850 width=16)
         ->  Seq Scan on measurement_y2006m03 measurement_2  (cost=0.00..28.50 rows=1850 width=16)
   ->  Hash  (cost=43.88..43.88 rows=200 width=4)
         ->  HashAggregate  (cost=41.88..43.88 rows=200 width=4)
               Group Key: measurement_filter.logdate
               ->  Seq Scan on measurement_filter  (cost=0.00..35.50 rows=2550 width=4)
(9 rows)