Partman behaves unexpectedly when partition size is changed, leaving gaps

[smcgivern]

We have a table using ID-based partitioning where we want to dynamically change the partition size such that it's approximately a week's worth of data.

When we increase the partition size and run maintenance, new partitions get created based on the start of the latest existing partition, leaving gaps. An example is probably easiest:

CREATE SCHEMA partman;
CREATE EXTENSION IF NOT EXISTS pg_partman WITH SCHEMA partman;
CREATE TABLE test (id BIGSERIAL PRIMARY KEY, text text) PARTITION BY RANGE (id);
CREATE INDEX test_created_at ON test (id);
CREATE TABLE test_template (LIKE test);

SELECT partman.create_parent(
  p_parent_table => 'public.test',
  p_template_table => 'public.test',
  p_default_table => FALSE,
  p_control => 'id',
  p_interval => '10',
  p_premake => 1
);

foo=# \d+ test
                                                 Partitioned table "public.test"
 Column |  Type  | Collation | Nullable |             Default              | Storage  | Compression | Stats target | Description
--------+--------+-----------+----------+----------------------------------+----------+-------------+--------------+-------------
 id     | bigint |           | not null | nextval('test_id_seq'::regclass) | plain    |             |              |
 text   | text   |           |          |                                  | extended |             |              |
Partition key: RANGE (id)
Indexes:
    "test_pkey" PRIMARY KEY, btree (id)
    "test_created_at" btree (id)
Partitions: test_p0 FOR VALUES FROM ('0') TO ('10'),
            test_p10 FOR VALUES FROM ('10') TO ('20')

UPDATE partman.part_config SET partition_interval = 100 WHERE parent_table = 'public.test';
INSERT INTO test (text) VALUES ('a'), ('b'), ('c'), ('d'), ('e'), ('f'), ('g'), ('h'), ('i'), ('j');
CALL partman.run_maintenance_proc();

foo=# \d+ test
                                                 Partitioned table "public.test"
 Column |  Type  | Collation | Nullable |             Default              | Storage  | Compression | Stats target | Description
--------+--------+-----------+----------+----------------------------------+----------+-------------+--------------+-------------
 id     | bigint |           | not null | nextval('test_id_seq'::regclass) | plain    |             |              |
 text   | text   |           |          |                                  | extended |             |              |
Partition key: RANGE (id)
Indexes:
    "test_pkey" PRIMARY KEY, btree (id)
    "test_created_at" btree (id)
Partitions: test_p0 FOR VALUES FROM ('0') TO ('10'),
            test_p10 FOR VALUES FROM ('10') TO ('20'),
            test_p110 FOR VALUES FROM ('110') TO ('210'),
            test_p210 FOR VALUES FROM ('210') TO ('310')

No partition covers the range 20-110! Is this expected? I appreciate that changing partition size dynamically may not be typical usage, but this was quite surprising to us. We had to manually create a partition to fill in the gap like so:

CREATE TABLE test_p20 (LIKE test INCLUDING COMMENTS INCLUDING COMPRESSION INCLUDING CONSTRAINTS INCLUDING DEFAULTS INCLUDING GENERATED INCLUDING STATISTICS INCLUDING STORAGE);
SELECT partman.inherit_template_properties('public.test', 'public', 'test_p20');
ALTER TABLE test ATTACH PARTITION test_p20 FOR VALUES FROM ('20') TO ('110');

[keithf4]

There is no inherent support for changing the partition size within pg_partman without creating a new partition set. It is something I would like to look at supporting in the future, though.

There is a partiton_gap_fill() function in pg_partman, but I'm not quite sure how it would work with different interval sizes in a single partition set.

[smcgivern]

Thanks - I will try that. It seems like this might work:

1. Increase partition size.
2. Run maintenance. (Creating gaps.)
3. Revert increase.
4. Run gap fill.
5. Increase partition size again.

This could theoretically work as long as the new partition size is a multiple of the old one, but I haven't checked how gap filling works, so I will test it.

[keithf4]

Would like to support changing partition size in some fashion so tagging this as a feature request and looking for anyone to assist.

However, I doubt I'd be able to do something as dynamic as you're looking for here where it changes the partition interval on the fly.

[smcgivern]

Coming back late here to confirm that running gap fill (with the smaller partition size) as in my previous comment does end up with what I'd expected:

UPDATE partman.part_config SET partition_interval = 10 WHERE parent_table = 'public.test';
SELECT partman.partition_gap_fill('public.test');

foo=# \d+ test
                                                 Partitioned table "public.test"
 Column |  Type  | Collation | Nullable |             Default              | Storage  | Compression | Stats target | Description
--------+--------+-----------+----------+----------------------------------+----------+-------------+--------------+-------------
 id     | bigint |           | not null | nextval('test_id_seq'::regclass) | plain    |             |              |
 text   | text   |           |          |                                  | extended |             |              |
Partition key: RANGE (id)
Indexes:
    "test_pkey" PRIMARY KEY, btree (id)
    "test_created_at" btree (id)
Partitions: test_p0 FOR VALUES FROM ('0') TO ('10'),
            test_p10 FOR VALUES FROM ('10') TO ('20'),
            test_p100 FOR VALUES FROM ('100') TO ('110'),
            test_p110 FOR VALUES FROM ('110') TO ('210'),
            test_p20 FOR VALUES FROM ('20') TO ('30'),
            test_p30 FOR VALUES FROM ('30') TO ('40'),
            test_p40 FOR VALUES FROM ('40') TO ('50'),
            test_p50 FOR VALUES FROM ('50') TO ('60'),
            test_p60 FOR VALUES FROM ('60') TO ('70'),
            test_p70 FOR VALUES FROM ('70') TO ('80'),
            test_p80 FOR VALUES FROM ('80') TO ('90'),
            test_p90 FOR VALUES FROM ('90') TO ('100')

Thanks for the help, we'll consider how to proceed from here.