[GENERAL] Hardware question
Hoping to tap into the hive mind here. I'm looking to upgrade drives on some of our db servers and am hoping someone has run/tested a similar setup in the past and can share their wisdom. I have a pair of Dell R910s with H700 controllers and am looking to replace the existing drives with SSDs. Right now I'm looking at populating both systems with x16 Intel 400GB S3700 drives in RAID-10 array which seem to fall right in the middle price/performance wise. Does anyone have any experience using the Intel S3700s (not Dell branded version) with the H700 controller? Anything I should be aware of? Are there any known issues? Thanks, Leonard -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Composite Foreign Key performance
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] composite foreign key performance
Apologies for the previous partial post, fat-fingered today. 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] composite foreign key performance
Hoping I can get some advice here. We have an existing schema as follows; lookup (id bytea PK, status text, .) data -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
