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

On Thu, Dec 31, 2020 at 11:18 AM Thorsten Schöning <tschoening(at)am-soft(dot)de>
wrote:

> Guten Tag Michael Lewis,
> am Donnerstag, 31. Dezember 2020 um 18:20 schrieben Sie:
>
> > Why is your fillfactor so low?[...]
>
> I've just copied what my GUI-tool pgModeler generated as SQL right
> now, that fill factor might have never been applied at all.
>

You should definitely check. Only 10% fill on each block is a bit crazy and
has a ton of wasted space built into your table. If it is set low like
that, I'd check the fillfactor on the indexes for that table as well.

select
t.reloptions
from pg_class t
join pg_namespace n on n.oid = t.relnamespace
where t.relname = 'clt_rec'
and n.nspname = 'public';

> > [...]That is, you could not have a primary key on ID and
> > partition on captured_on for instance.
>
> That's what I understood as well and is the reason why I asked: That
> means IDs could be duplicated manually within individual partition
> tables, while I need them to be unique across all of those.
> Additionally I wonder when IDs are generated by which SEQUENCE etc.
>

Sequences are incremented and return the new value whenever they are
called. If all partitions inherit the same sequence (behavior determined by
which options you use when you create partitions LIKE parent), then they
will all use the same series of values that cannot provide duplicates
(until wrap around if allowed). If you want to ensure you don't get
duplicates, then either do a proper IDENTITY which is generated as always,
or ensure ID is a primary key/unique index. Since the partition key must
include the ID column and partitions cannot overlap in the partition key
space, each index on each partition would be part of a set.