Apologies for the previous partial post earlier, fat-fingered today.
If this is a repost I apologize in advance as I could not see my reply
in the archive after a couple of hours.
I currently have;
- lookup table (lookup_id BYTEA PK, status TEXT, last_update
TSTAMPTZ, ...) (PK index is > 20GB)
- data table (data_id BIGINT PK, customer_id BIGINT, lookup_id BYTEA,
...) which is partitioned by customer_id (there is currently no FK set
up for lookup_id)
> A bytea as the PK? That sounds horrible.
Reason for the bytea: benching it gave far better results as far as
size went (index and on-disk size). Every query is keyed on this value
(decoded from external data)
The lookup table currently contains ~ 350mm records.
We receive ~ 10mm new customer 'data' records per day, and there is ~
8% likelyhood of overlap with other customer records.
For every set of data imported, we export those records with extra
data appended, including the 'status' and other fields.
Right now we perform join btw data and lookup when extracting the
records, which is quite an expensive query.
What I'm thinking of doing is extending the data table to include
those fields from lookup which are always exported and creating a
composite foreign key so that any updates to the lookup record are
propagated to any matching records in the various data_X partitions.
This would involve converting PK on lookup to (lookup_id, status, last_update)
adding fields (status, last_update) to the data parent table
syncing/copying the relevant data from the lookup table into matching
data_X tables
creating new index (lookup_id, status, last_update) on each data_X child table;
creating new FK (lookup_id, status, last_update) on each data_X child
table with ON UPDATE CASCADE ON DELETE RESTRICT;
I'm aware of the extra space this would require, and am really wondering if:
a) there is a potential improvement in the select from data_X
operations when not using the join
b) there is a significant overhead in having the foreign keys
Looking for any initial advice from experience before I set up huge
test to measure.
Thanks,
Leonard