Change already implemented PSQL 11 Native Partitioning from Monthly to Daily?

[Doctorbal]

I am currently using PostgreSQL Native Range partitioning together with pg_partman on a Zabbix Database.

I recently implemented 'monthly' partitioning for history* tables but later realized that 'daily' partitions will work much better as I'd like to drop the partitions after 7 days.

Is there a way to initiate this change without the need of using sub-partitions?

[Doctorbal]

I have tested this by updating the table partman.part_config and ran the maintenance but can't seem to fully understand how to change the partitions from monthly to daily.

UPDATE partman.part_config SET partition_interval = '1 day', premake = 14, retention_keep_table = false, retention = '7 day', datetime_string = 'YYYY_MM_DD', retention_keep_index = true WHERE parent_table = 'public.history';
UPDATE partman.part_config SET partition_interval = '1 day', premake = 14, retention_keep_table = false, retention = '7 day', datetime_string = 'YYYY_MM_DD', retention_keep_index = true WHERE parent_table = 'public.history_uint';
UPDATE partman.part_config SET partition_interval = '1 day', premake = 14, retention_keep_table = false, retention = '7 day', datetime_string = 'YYYY_MM_DD', retention_keep_index = true WHERE parent_table = 'public.history_str';
UPDATE partman.part_config SET partition_interval = '1 day', premake = 14, retention_keep_table = false, retention = '7 day', datetime_string = 'YYYY_MM_DD', retention_keep_index = true WHERE parent_table = 'public.history_log';
UPDATE partman.part_config SET partition_interval = '1 day', premake = 14, retention_keep_table = false, retention = '7 day', datetime_string = 'YYYY_MM_DD', retention_keep_index = true WHERE parent_table = 'public.history_text';

SELECT partman.run_maintenance('public.history');
SELECT partman.run_maintenance('public.history_uint');
SELECT partman.run_maintenance('public.history_str');
SELECT partman.run_maintenance('public.history_log');
SELECT partman.run_maintenance('public.history_text');

[keithf4]

Dynamically changing the interval isn't really something that is built into the extension as it is right now. I'd really have to review things to see what else needs to change, but I also know the other thing that needs to be updated would be the datetime_string field in the config. Problem with that, though, is that it expects to find an existing partition with that format for its suffix. So you're likely going to have to create a new child table with a daily suffix as well for things to work. Another problem with that again is with native partitioning you can't create two child tables that have overlapping constraints. So you'd have to do some juggling of data around.

So having said all that and probably just confusing you more, at this time, you're probably better unpartitioning your data and repartitioning it. There are procedures (if you're on PG11+) or scripts (for older versions) to help you do this. Since you're only keeping 7 days of data, hopefully it's not too much data and you could manually delete any data older than 7 days now before doing it to help it go quicker.

[Doctorbal]

Thank you Keith for your quick response. I am currently running PostgreSQL version 11 thus I will look at the procedures you mentioned. 👍

[keithf4]

If you have any problems with the procedures, please let me know. Or also even let me know if they work well for you. I haven't gotten much chance to put them to use in real-world work (no clients on PG11 yet), only really in my testing framework. So any feedback on how good/bad they are would be appreciated.

[Doctorbal]

@keithf4 ,

I have conceptually outlined the steps below after having tested this on my DEV Zabbix instance (~1/100 the size).

Would you be able to clarify whether this is appropriate or if there is a faster, more efficient way?

Objective: changing the following table (plus 4 other tables ~10GGB each) from monthly to daily partitions while minimizing downtime and data loss.

zabbix=# \d+ history
                                     Table "public.history"
 Column |     Type      | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------------+-----------+----------+---------+---------+--------------+-------------
 itemid | bigint        |           | not null |         | plain   |              |
 clock  | integer       |           | not null | 0       | plain   |              |
 value  | numeric(16,4) |           | not null | 0.0000  | main    |              |
 ns     | integer       |           | not null | 0       | plain   |              |
Partition key: RANGE (clock)
Indexes:
    "history_1" btree (itemid, clock)
Partitions: history_p2019_01 FOR VALUES FROM (1546300800) TO (1548979200),
            history_p2019_02 FOR VALUES FROM (1548979200) TO (1551398400),
            history_p2019_03 FOR VALUES FROM (1551398400) TO (1554076800),
            history_p2019_04 FOR VALUES FROM (1554076800) TO (1556668800),
            history_p2019_05 FOR VALUES FROM (1556668800) TO (1559347200),
            history_p2019_06 FOR VALUES FROM (1559347200) TO (1561939200),
            history_p2019_07 FOR VALUES FROM (1561939200) TO (1564617600),
            history_default DEFAULT

Procedure:
0. Delete data older than 7 days. This will simplify and speed up the moving process as you mentioned.

/* The following is just an example of an epoch timestamp...
DELETE FROM history where clock < 1549168074;

1. Create a table similar to the one being unpartitioned. For e.g.:

CREATE TABLE public.history_moved
(
    itemid bigint NOT NULL,
    clock integer NOT NULL DEFAULT 0,
    value numeric(16,4) NOT NULL DEFAULT 0.0000,
    ns integer NOT NULL DEFAULT 0
) PARTITION BY RANGE (clock);

Then run

select partman.create_parent('public.history_moved', 'clock', 'native', 'monthly', null, 1, 'on', null, true, 'seconds');

to set up partitions for the data to be inserted. Since I only keep data less than 7 days using a 1 month +/2 partition is perfectly reasonable or I can break it up into days too if you suggest differently.
2. Call the partman.undo_partition_proc() function on the table wanting to be unpartitioned:

CALL partman.undo_partition_proc('public.history', null, null, 1, 'public.history_moved', false, 0, 10, false);

What would be the ideal p_interval or p_batch parameter from your experience for large tables?
3. Create the partitioned tables on the original history table wanting daily partitions:

select partman.create_parent('public.history', 'clock', 'native', 'daily', null, 7, 'on', null, true, 'seconds');

This outputs the following:

zabbix=# \d+ history
                                     Table "public.history"
 Column |     Type      | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------------+-----------+----------+---------+---------+--------------+-------------
 itemid | bigint        |           | not null |         | plain   |              |
 clock  | integer       |           | not null | 0       | plain   |              |
 value  | numeric(16,4) |           | not null | 0.0000  | main    |              |
 ns     | integer       |           | not null | 0       | plain   |              |
Partition key: RANGE (clock)
Indexes:
    "history_1" btree (itemid, clock)
Partitions: history_p2019_02_06 FOR VALUES FROM (1549411200) TO (1549497600),
            history_p2019_02_07 FOR VALUES FROM (1549497600) TO (1549584000),
            history_p2019_02_08 FOR VALUES FROM (1549584000) TO (1549670400),
            history_p2019_02_09 FOR VALUES FROM (1549670400) TO (1549756800),
            history_p2019_02_10 FOR VALUES FROM (1549756800) TO (1549843200),
            history_p2019_02_11 FOR VALUES FROM (1549843200) TO (1549929600),
            history_p2019_02_12 FOR VALUES FROM (1549929600) TO (1550016000),
            history_p2019_02_13 FOR VALUES FROM (1550016000) TO (1550102400),
            history_p2019_02_14 FOR VALUES FROM (1550102400) TO (1550188800),
            history_p2019_02_15 FOR VALUES FROM (1550188800) TO (1550275200),
            history_p2019_02_16 FOR VALUES FROM (1550275200) TO (1550361600),
            history_p2019_02_17 FOR VALUES FROM (1550361600) TO (1550448000),
            history_p2019_02_18 FOR VALUES FROM (1550448000) TO (1550534400),
            history_p2019_02_19 FOR VALUES FROM (1550534400) TO (1550620800),
            history_p2019_02_20 FOR VALUES FROM (1550620800) TO (1550707200),
            history_default DEFAULT

4. INSERT data > EPOCH(7 Days) into the newly partitioned tables:

INSERT INTO public.history SELECT * FROM public.history_moved WHERE clock > 1549168074

5. Drop the old table and remove the partman.part_config column

DROP TABLE history_moved;
DELETE FROM partman.part_config WHERE parent_table = 'public.history_moved';

6. UPDATE the partman.part_config for public.history:

UPDATE partman.part_config SET retention_keep_table = false, retention = '7 day'
WHERE parent_table = 'public.history';

7. Run the maintenance:

SELECT partman.run_maintenance('public.history');

8. Perform the same procedure on history_str, history_text, history_uint and history_log.

This appeared to work with very little data loss in my DEV instance; but again it was 1/100 the size.

Is there any way of performing this with zero data loss and transparent to the applications connected to the Zabbix database? What would you suggest is done to simplify the procedure?

Thank you for your help thus far; I truly appreciate your time and effort. pg_partman has been an amazing addition to simplifying database partitioning efforts.

[keithf4]

Those steps look good and data loss should be zero as far as I know. And if it's working properly in your testing it should work fine on any sized table. The p_interval is really up to you and controls how much data will be unavailable only while it's being moved. So it's really dependent on how much time you want each batch to take. The smaller the interval, the quicker the batch. You likely don't need to set the p_batch variable unless you want it to stop mid-processing for some reason. If unset, it will unpartition the entire set. Same is true when repartitioning again using the stored_procedure.

However, doing this with transparency to the application with native partitioning is really tricky and would involve a lot more steps than this. The old trigger-based methods you could just move all the data to the parent and back out again, but that's no longer possible. Could possibly use the default partition feature in PG11, but even that's tricky as well (see #230). I actually need to sit down at some point and really plan it out myself to get the feature added to pg_partman, so don't want to get into too much speculation on the steps involved right now since even I'm not 100% sure the ideas I have in mind will actually work.

[keithf4]

One thing I will caution you on with partitioning/unpartitioning the tables is that, if there is a lot of data, it can cause a HUGE spike in WAL traffic which in turn causes a huge spike in disk usage wherever your pg_wal directory is located (default in data directory). So if that's a concern, especially on production, you may want to take advantage of the p_wait flag to introduce a pause and let IO calm down a bit. With not being transparent to the app, tho, that will make the outage longer. But if you previously partitioned all this data on the system without an disk space issue, you'll probably be fine. Just wanted to throw that bit in there.

[Doctorbal]

@keithf4 , thank you for the details. Setting p_wait and p_interval to reasonable values to minimize high system IO did the trick. The default partition feature will be a cool fix for the future!

The transparency to the applications is definitely a challenge, especially on a high read/write DB such as the Zabbix DB. What I will need to do is temporarily set a maintenance window and shut down the Server and Web instance. I could use a Proxy as well (pgbouncer for e.g.) or branch out to HA solutions with a replicated, delayed DB in the meantime so there are ways to work around this.

Either way this works well for my current needs and I look forward to more improvements in the future. Feel free to close this as you see fit 💪 .