"Because it's policy" is rarely a good design decision :-) Lose the FK constraints, and make up for them with integrity checking queries.
I just did a major refactor and shard on our PG schema and the performance improvement was dramatic ... a big plus for PG, if it is e.g. time-series data is to shard by time and make the tables write-once. The same applies to any record id that doesn't get re-used. PG doesn't do in-place record updates, so tables with lots of row changes can get order-fragmented. If not, also check out the "cluster table on index" command. Cheers Dave On Wed, May 5, 2010 at 3:25 PM, Richard Yen <d...@richyen.com> wrote: > Hello, > > I'm about to embark on a partitioning project to improve read performance > on some of our tables: > > db=# select relname,n_live_tup,pg_size_pretty(pg_relation_size(relid)) from > pg_stat_all_tables where schemaname = 'public' order by n_live_tup desc > limit 10; > relname | n_live_tup | pg_size_pretty > -------------------------------------+------------+---------------- > objects | 125255895 | 11 GB > papers | 124213085 | 14 GB > stats | 124202261 | 9106 MB > exclusions | 53090902 | 3050 MB > marks | 42467477 | 4829 MB > student_class | 31491181 | 1814 MB > users | 19906017 | 3722 MB > view_stats | 12031074 | 599 MB > highlights | 10884380 | 629 MB > > Problem is, I have foreign keys that link almost all of our tables together > (as a business requirement/IT policy). However, I know (er, I have a gut > feeling) that many people out there have successfully deployed table > partitioning, so I'm hoping to solicit some advice with respect to this. > I've looked at documentation, tried creating a prototype, etc...looks like > foreign keys have to go. But do they? What have other people out there > done to get their tables partitioned? > > Any input would be much appreciated. > > Thanks! > --Richard > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance >
