Re: Declarative partitioning and automatically generated row-IDs using BIGSERIAL

Why is your fillfactor so low? That seems pretty crazy, especially for a
table with only 4 columns that are fixed width. 100 million rows with so
little data in each row is not very much at all. You should be looking to
other solutions before partitioning I expect.

Perhaps a silly question, but do you have an index on captured_on? You
included the full DDL for the table it seems so I wanted to ask. Can you
provide some example queries that you want to speed up? What are you
configuration settings (select * from pg_settings;)?

Have you pursued adding a BRIN index, or partial index on captured_on
perhaps? If using a partial index that covers the last 15+ days, you would
both need to drop & recreate that occasionally, and also you would need to
ensure your where clause on captured_on is a static value and not relative
like now() - interval '15 days'. It should be simple enough to compute that
date in whatever application or process is generating your query strings
though.

As you have gathered already from the docs, implementing partitioning is
not without hurdles or feature concessions. The planning time to consider
all those partitions is also not free. Not to mention the manual
maintenance to create new or combine old partitions.

One thing of note to correct from your email- Primary keys are NOT unique
to individual partitions, and if you have a primary key or other unique
index on a partitioned table, at least one of those columns must be in the
partition key. That is, you could not have a primary key on ID and
partition on captured_on for instance.