pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedpartioning tips?
3+ messages / 3 participants
[nested] [flat]
* partioning tips?
@ 2010-05-05 20:25 Richard Yen <dba@richyen.com>
0 siblings, 2 replies; 3+ messages in thread
From: Richard Yen @ 2010-05-05 20:25 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: partioning tips?
@ 2010-05-05 21:31 Dave Crooke <dcrooke@gmail.com>
parent: Richard Yen <dba@richyen.com>
1 sibling, 0 replies; 3+ messages in thread
From: Dave Crooke @ 2010-05-05 21:31 UTC (permalink / raw)
To: Richard Yen <dba@richyen.com>; +Cc: pgsql-performance
"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@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
>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: partioning tips?
@ 2010-05-07 17:48 Josh Berkus <josh@agliodbs.com>
parent: Richard Yen <dba@richyen.com>
1 sibling, 0 replies; 3+ messages in thread
From: Josh Berkus @ 2010-05-07 17:48 UTC (permalink / raw)
To: pgsql-performance
On 05/05/2010 01:25 PM, Richard Yen wrote:
> 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?
Well, it's possible to work around the limitation on FKs, but probably
not worth it. In general, the reasons you want to partition (being able
to cheaply drop segments, no scans against the whole table, ever) are
reasons why you wouldn't want an FK to a partition table in any case.
The specific cases where it works to have FKs anyway are:
1) if you're making FKs between two partitioned tables whose partition
ranges match exactly. In this case, you can just FK the individual
partitions (there is a TODO, and some draft code from Aster, to make
this happen automatically).
2) If the partitioned table has very wide rows, and it's large for that
reason rather than because of having many rows. In this case, you can
create an FK join table containing only the SKs for creating FKs to,
just like a many-to-many join table.
--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2010-05-07 17:48 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-05-05 20:25 partioning tips? Richard Yen <dba@richyen.com>
2010-05-05 21:31 ` Dave Crooke <dcrooke@gmail.com>
2010-05-07 17:48 ` Josh Berkus <josh@agliodbs.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox