Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 6DBF7633072 for ; Thu, 24 Jun 2010 17:30:09 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 16308-01 for ; Thu, 24 Jun 2010 20:30:01 +0000 (UTC) X-Greylist: delayed 00:06:36.575542 by SQLgrey-1.7.6 Received: from mail.akn.me (mail.akn.me [188.40.171.129]) by mail.postgresql.org (Postfix) with ESMTP id 0EABD632223 for ; Thu, 24 Jun 2010 17:30:02 -0300 (ADT) Received: from localhost (localhost.localdomain [127.0.0.1]) by mail.akn.me (Postfix) with ESMTP id 457C44882803; Thu, 24 Jun 2010 21:08:50 +0200 (CEST) X-Virus-Scanned: amavisd-new at akn.me Received: from mail.akn.me ([127.0.0.1]) by localhost (mail.akn.me [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 8ObFEkYskvan; Thu, 24 Jun 2010 21:08:45 +0200 (CEST) Received: from [127.0.0.1] (213.37.98.137.static.user.ono.com [213.37.98.137]) by mail.akn.me (Postfix) with ESMTP id 29BAD48827F1; Thu, 24 Jun 2010 21:08:45 +0200 (CEST) Message-ID: <4C23BD71.40706@venaver.info> Date: Thu, 24 Jun 2010 22:17:53 +0200 From: Petru Ghita User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.1.10) Gecko/20100512 Thunderbird/3.0.5 MIME-Version: 1.0 To: Joshua Gooding , pgsql-sql mailing list Subject: Re: question about partitioning References: <4C235807.5070509@ttitech.net> In-Reply-To: <4C235807.5070509@ttitech.net> X-Enigmail-Version: 1.0.1 OpenPGP: id=7CF29D04 Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="------------enig5DA2A68DADBF664BC1E41CFE" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.724 tagged_above=-5 required=5 tests=BAYES_05=-0.5, RCVD_IN_NJABL_PROXY=2.224 X-Spam-Level: * X-Archive-Number: 201006/82 X-Sequence-Number: 34273 This is an OpenPGP/MIME signed message (RFC 2440 and 3156) --------------enig5DA2A68DADBF664BC1E41CFE Content-Type: multipart/mixed; boundary="------------060009030800070503000004" This is a multi-part message in MIME format. --------------060009030800070503000004 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable There is no partitioning by size that I know of but at: http://www.postgresql.org/docs/8.4/interactive/ddl-partitioning.html there is very good documentation on the topic. As of this last weekend I had myself to do some testing with partitioning in Postgres 8.4. I had 7000 items. For each of them I stored 6 variables (smallint) for each hour quarter. Test data was generated for 5 years. That gave me 210336 records per id per 5 years period and a total of 1,472,352,000 records. This is taking about 33MB per partition table and a total of 231GB. Doing something like a grouping by the id and summing up the values of 10 ids takes about 2.5 seconds, which looks to me like a quite a nice performance. Petru Ghita On 24/06/2010 15:05, Joshua Gooding wrote: > rules for --------------060009030800070503000004 Content-Type: application/pgp-keys; name="0x7CF29D04.asc" Content-Transfer-Encoding: quoted-printable Content-Disposition: attachment; filename="0x7CF29D04.asc" -----BEGIN PGP PUBLIC KEY BLOCK----- Version: GnuPG v1.4.9 (MingW32) mQGiBEm+OJIRBACpGuu5Zb31cYiz9dPn0aeqH3tzEdVurPXoWkxOCkdiDlY0mfUm TnQP54K1bM+7NljbMSq8uVGy4h242FOyJjfrUC3ACaqeWBKYaksGUuMruf8QPInX cC1s47UXVx/7K8Q1qxub/FClRyqmowHo/EzKONhhM1bXlGW5sIM0eUo8ywCg4186 9W9fITbbmqPadU15aFQ3JYkD/0zCk6jRreNT4Jc/KLhSZipceEASsv0Dei1Imwyx 5yknGuItYUNeSCNvo5WTSn5tukVoBMzV2V1Nbhk60DgfkaCGTKULhh5q9J70flbt uOnW7fxVw5/u4eCWj81PJ9qOxgg8DiFIX0YJTm/rYUTID1+/4y2hbkZPkKYcgHIE n7FHA/oDqBeD+KEjR/Agzw9wHuBXLZbYbo0bT1+51vHHG/DuA13WTbhP86ay8A6s 3FAWJ4s4mfe853QCDegIsO/lfIryj5QPY646roZsKsqUbbOUvoXE0KZdU7FQCkPX EUmR8bIY7G6YmaI9dHdAECkea+vdt8uAYzQp+MJO8G8Rqy4oA7Q4UGV0cnUgR2hp dGEgKFBldHJ1IEdoaXRhIFNoZXJhcikgPHBldHJ1LmdoaXRhQGdtYWlsLmNvbT6I ZgQTEQIAJgUCSb45qQIbIwUJCWYBgAYLCQgHAwIEFQIIAwQWAgMBAh4BAheAAAoJ ELeiC+l88p0Eta0AoMD36wmM7lXfeaUMCaty+SkSx+OgAJ9fRd321yQD6PQmsuLb fVpyC8J2XrQ0UGV0cnUgR2hpdGEgKFBldHJ1IEdoaXRhIFNoZXJhcikgPHBldHJ1 dHpAeWFob28uY29tPohmBBMRAgAmBQJJvjl2AhsjBQkJZgGABgsJCAcDAgQVAggD BBYCAwECHgECF4AACgkQt6IL6XzynQTXaACbBULN47fo5k1W3B6OAWzU6OMPKhQA mwe3aBHKvm3fKcga6/z2ydnxVFZQtDdQZXRydSBHaGl0YSAoUGV0cnUgR2hpdGEg U2hlcmFyKSA8cGV0cnV0ekB2ZW5hdmVyLmluZm8+iGkEExECACkCGyMFCQlmAYAG CwkIBwMCBBUCCAMEFgIDAQIeAQIXgAUCSb45rgIZAQAKCRC3ogvpfPKdBAogAJ9F 7KDn26vVISxbTUtIk5OqkGvlmACeI6c4Nd8BEi+uLAhaGv1THSqhcOa5BA0ESb44 khAQAKFdlm1fYtQ1B3IG7r0xoZ38Tb0SPVf1GKfBxJ46Q0fLd9zwLRkhnv1i8e88 Lmcjd8jYvAYuTeReGENdZr5ssIkbf+VcthvZadRmITY5uXe1+Bld8RL+z1rjmFeO ijD+WgFHYl9RSM7m42SlxEqLev7EcuBbNBpxi8jSL1Jk1iqVwERBIJ5YG5UK5HQB RNmrj5R0ZVlvR+6PNIkK79Qlt84/vVvXfCzLBMoLdk14RcQRfNZLmmpFcYnVSFsR cXqR2189VYOxRRAM+lfPMwFWx+sRem50FXSuhX32jKk6MVFq4J/lEcvbfo0Ir0G8 1/RYnr3/tWLJkUL/JK69AywDzM9s8dA7Jx8RHGSB/DAyUb9AdPuiROaeLPIKxgid a08prH70H9vpW0rqwSK0i76v7NMM3tmMKqNLWTte+KySwwgci1r5S/xFPU8dYId7 svq8aSt8thv/I3jubrZky5z1+C6JOkzkXrLDdsRQYI5s5nYh48+YdCD24ZbfXmH3 U6FG+BWsTYODdtK3s9nhwJ8Y8uRyng7LzKKEcOKzZV6t0AKsju4IuxgWsDemRdnS 46EmKb2k8P19DnNoue7XjgqBNEEiacrBqZuJWGtRnkJdy0SgED3L8iTR6PEv/t3r ghnS3bhrfKdpUXMot8NrWzxRz6zjdp8qWcT7BPeymYquOUT3AAMFD/kBJsE3ngPt 0KyQ4IWl42G5S5D9oZMD3L0hPTVFUkavnMugAAxYOWR9u6HazsYlYFOlc6ZkW4+l wdCjdvrYVsi34Nr+4KkogDylYNt1+Fg48poLR5crq9uMRQKwhdRAxDF5clwiEwnx hV1JShfHmvwKfvSzejOP9v+abp8UMLIn/R2uBbgz4WnWfw4C6HzaD0+JCmJEDhEX emXhC7ZVITZBMsKU6/54oCByJw2aduvYfzn8ZemVqWcuX515jsMARllPzlWZabxo qraFX6hKeak0rpMtONAFsq9KTcQazGpTdw42fAehaeWS8kWBtglQAn2uQuXOHsuG wOlI7oQauwU6YfVyU5eRCQMwRU4iWuH+UEhu6xVTEd7CkbUkSpf7cO9bzaOK1VZi QJCqOSmYMW3ZAB9YVZRjcsutb8P+J45iHIu2C6byc96beS6pwFmGnt8Oj4aZ2Q67 wgIcAs7OO1OsrvPfNwNl2vSE+Yp1yF8vJSl21YsA9S0AP62SGgq5bRmZbFRmv/T4 O4vsYquC5PKMLOb4UXHoEMdYCbywLIshjzqQXPCzLJ/weuBBjKUSZZV3lALprz5+ OIvuKQW2OVckFBrV6LuODPX3PFId6SfYNcn1x8nyE/2VXROVGw2rlAsXr46OI9Qe yH+wGUJq/3tfYz4c6WZyW2GyjYggTT27hYhPBBgRAgAPBQJJvjiSAhsMBQkJZgGA AAoJELeiC+l88p0EvxYAoLzxDKu8JjZydBV/N3OLC2oxh1veAJ0X8EeudV8guzV1 8E/KEI1yVJcShQ=3D=3D =3DQWm0 -----END PGP PUBLIC KEY BLOCK----- --------------060009030800070503000004-- --------------enig5DA2A68DADBF664BC1E41CFE Content-Type: application/pgp-signature; name="signature.asc" Content-Description: OpenPGP digital signature Content-Disposition: attachment; filename="signature.asc" -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.9 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkwjvXQACgkQt6IL6XzynQR1zgCgsS95DbVr4/1pylJxtmuFptlR 2LEAn3i/hNjzGBeJLtkIavczkFq0vYtn =4Yey -----END PGP SIGNATURE----- --------------enig5DA2A68DADBF664BC1E41CFE--