Automatic partition creation failure for native ID-based table

[AurusHuang]

Environment: Windows 10, PostgreSQL 12(with PostGIS installed).

Preparation: I compiled the C code in pg_partman myself and it reports no errors. make is unavailable on my machine so I just copied the compiled DLL to \lib, SQL and CONTROL file to \share\extension\ on PG installation directory manually. The same was done to pg_jobmon.

Problem: Here is the creation of tables:

CREATE TABLE public.sample_points
(
    gid bigserial,
    grid_name character varying,
    i integer NOT NULL,
    j integer NOT NULL,
    depth double precision
) PARTITION BY RANGE(gid);

ALTER TABLE public.sample_points
    OWNER to seismic;

select create_parent('public.sample_points','gid','native','100');

Then I insert 1000 rows:

insert into sample_points
select generate_series(1,1000) as gid,
'grids' as grid_name,
random()*1000::integer as i,
random()*2000::integer as j,
random()*-50 as depth

But pg_partman didn't automatically create new partitions for gid=500 to 1000. Instead, they fall into the default partition. Is it a problem of pg_partman itself, or if it lies on Windows compilation, or if I missed something when creating the table?

[dtseiler]

I don't believe pg_partman automatically creates partitions on-demand like that. The background worker (or a manual run_maintenance() call) would pre-create partitions based on the part_config information for that table. What does \d tell you after running the create_parent() function? Otherwise you may need to specify a p_start_partition when you call create_parent().

[keithf4]

Don is correct, pg_partman does not create partitions on demand. With how PostgreSQL works, that can cause heavy contention issues when multiple transactions are all trying to create the same child table at the same time. pg_partman works by pre-creating the partitions ahead of time. With integer based partitioning that can be a bit trickier than time. My recommendation is to figure out the insert/update rate for new id values as they increase and base your partition interval and maintenance schedule on that. If you didn't realize, you also have to schedule pg_partman to run maintenance either using the built-in scheduler or calling the maintenance functions yourself somehow (usually cron on linux).

If your situation is that your id values are truely random and you'll never know what id value will be coming in, I'd honestly question the need for partitioning at all, especially with a brand new table. The primary reason for partitioning in PG is for retention reasons to make old data easier to remove. The performance benefits for reads/writes themselves aren't apparent until you are dealing with tables of a fairly significant size (100s of millions or billlions of rows).

[AurusHuang]

But we ARE dealing with hundred of millions of rows, and that's why we need our tables partitioned.

[keithf4]

So then the question is are all of the numerical values coming in going to be completely unpredictable, or are you going to be using some numerical value that is increasing over time?

If it's completely unpredictable, then you will need to create all possible child tables up front and there will be no need for ongoing maintenance. In this case, pg_partman likely isn't going to help much since its primary purpose with native partitioning is ongoing maintenance needs. If all possible child tables are immediately created up front, and will never be dropped, there is no ongoing maintenance.

If it will be a value that is incrementing over time, you'll first have to figure out the range of data that will be coming in at any point. Set the premake configuration to account for that range so that many child tables are always ready and available for new data. Next you will have to figure out the ingestion rate so see how often maintenance will need to be run to keep the pre-made child tables ahead of your new data range.

Again, it's not recommended to have partitions created as needed as you will likely run into contention issues when multiple transactions all attempt to make the same child table simultaneously. pg_partman's premake system is designed to avoid that contention.

[AurusHuang]

Thank you. I'll consider your advice. But since it's "randomly increase over time", it might end with dropping my whole project due to impossibility to implement a high performance system with software limitations.

[keithf4]

If there is another timestamp column that increases over time as well, could possibly consider partitioning on that vs the integer column. That could then lead you to being able to archive data out over time as well if it's not needed forever within the database.