Received: from maia.hub.org (maia-1.hub.org [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id E4F55633790 for ; Wed, 5 May 2010 18:31:50 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 65566-07 for ; Wed, 5 May 2010 21:31:37 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-bw0-f216.google.com (mail-bw0-f216.google.com [209.85.218.216]) by mail.postgresql.org (Postfix) with ESMTP id C163463317A for ; Wed, 5 May 2010 18:31:42 -0300 (ADT) Received: by bwz8 with SMTP id 8so3120424bwz.3 for ; Wed, 05 May 2010 14:31:41 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:received:in-reply-to :references:date:message-id:subject:from:to:cc:content-type; bh=6kniOSSEO2UJ+rA2GwOuY1KkVZ/oRIFon0x+ryADWYE=; b=ebVzcs6S1A0qw/ss7w6yUhFoWM0e2ZZ3oU5X2KyLt1HB5XQUW1Ck5gWz9MJ8tvXzRw z6VCgqZvqRS4X4/of04TB8It0PemK+mmlGCOve3IgtE9Gi+1V8oRMXjvauwjrdQTeGwo ygGFBIkWgGsqVTUoKAzbZkQ3eQN97F5cSrQHM= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type; b=rpgj7pHbYb+xW+KoZLYx+45fstlJsXr41gbIqeNtrZcpzHlQWlvKtjtDJWIGW+tmED PImX641ErDtI8XCqsMVxQe41unCoatoeZ8HQWT/Y/xf+DpvgUsFF/cXXYs9yK/vj+DHU j8wYL8hFsLGx4X18KqXtmC1b4GE1stBEHhLg8= MIME-Version: 1.0 Received: by 10.204.9.21 with SMTP id j21mr1639742bkj.180.1273095100353; Wed, 05 May 2010 14:31:40 -0700 (PDT) Received: by 10.204.100.196 with HTTP; Wed, 5 May 2010 14:31:40 -0700 (PDT) In-Reply-To: <0E1248CD-551D-4F40-95AD-17336996ADAF@richyen.com> References: <0E1248CD-551D-4F40-95AD-17336996ADAF@richyen.com> Date: Wed, 5 May 2010 16:31:40 -0500 Message-ID: Subject: Re: partioning tips? From: Dave Crooke To: Richard Yen Cc: pgsql-performance@postgresql.org Content-Type: multipart/alternative; boundary=00151758855838d1870485df8dec X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.899 tagged_above=-10 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_NONE=-0.0001 X-Spam-Level: X-Archive-Number: 201005/14 X-Sequence-Number: 38598 --00151758855838d1870485df8dec Content-Type: text/plain; charset=UTF-8 "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 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 > --00151758855838d1870485df8dec Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable "Because it's policy" is rarely a good design decision :-) Lo= se 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 per= formance 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 a= pplies 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-frag= mented.

If not, also check out the "cluster table on index" command.<= br>
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 performan= ce on some of our tables:

db=3D# select relname,n_live_tup,pg_size_pretty(pg_relation_size(relid)) fr= om pg_stat_all_tables where schemaname =3D 'public' order by n_live= _tup desc limit 10;
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 relname =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | n_live_tup | pg_size_pretty
-------------------------------------+------------+----------------
=C2=A0objects =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0125255895 | 11 GB
=C2=A0papers =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0| =C2=A0124213085 | 14 GB
=C2=A0stats =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0| =C2=A0124202261 | 9106 MB
=C2=A0exclusions =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0| =C2=A0 53090902 | 3050 MB
=C2=A0marks =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0 42467477 | 4829 MB
=C2=A0student_class =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 | =C2=A0 31491181 | 1814 MB
=C2=A0users =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0 19906017 | 3722 MB
=C2=A0view_stats =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 | =C2=A0 12031074 | 599 MB
=C2=A0highlights =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 | =C2=A0 10884380 | 629 MB

Problem is, I have foreign keys that link almost all of our tables together= (as a business requirement/IT policy). =C2=A0However, I know (er, I have a= gut feeling) that many people out there have successfully deployed table p= artitioning, so I'm hoping to solicit some advice with respect to this.= =C2=A0I've looked at documentation, tried creating a prototype, etc...= looks like foreign keys have to go. =C2=A0But do they? =C2=A0What have othe= r 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

--00151758855838d1870485df8dec--