pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Dave Crooke <dcrooke@gmail.com>
To: Richard Yen <dba@richyen.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: partioning tips?
Date: Wed, 5 May 2010 16:31:40 -0500
Message-ID: <j2rca24673e1005051431m6f20291dxec24d4d92508f93@mail.gmail.com> (raw)
In-Reply-To: <0E1248CD-551D-4F40-95AD-17336996ADAF@richyen.com>
References: <0E1248CD-551D-4F40-95AD-17336996ADAF@richyen.com>

"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
>

view thread (3+ messages)  latest in thread

Message-ID: <j2rca24673e1005051431m6f20291dxec24d4d92508f93@mail.gmail.com>
Permalink:  ../j2rca24673e1005051431m6f20291dxec24d4d92508f93@mail.gmail.com/
Also on:    postgresql.org/message-id/j2rca24673e1005051431m6f20291dxec24d4d92508f93@mail.gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: dcrooke@gmail.com, dba@richyen.com
  Subject: Re: partioning tips?
  In-Reply-To: <j2rca24673e1005051431m6f20291dxec24d4d92508f93@mail.gmail.com>
  References: <0E1248CD-551D-4F40-95AD-17336996ADAF@richyen.com> <j2rca24673e1005051431m6f20291dxec24d4d92508f93@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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