partioning tips?

From: Richard Yen <dba(at)richyen(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: partioning tips?
Date: 2010-05-05 20:25:46
Message-ID: 0E1248CD-551D-4F40-95AD-17336996ADAF@richyen.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Dave Crooke 2010-05-05 21:31:40 Re: partioning tips?
Previous Message Alexander Korotkov 2010-05-03 13:57:06 Re: Planner issue on sorting joining of two tables with limit