On Thu, Dec 31, 2020 at 11:18 AM Thorsten Schöning <tschoening@xxxxxxxxxx> 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.
