Re: partioning tips?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



"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 <dba@xxxxxxxxxxx> 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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux