From: AJ Weber <aweber@comcast.net>
To: Jeff Janes <jeff.janes@gmail.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Partition table in 9.0.x?
Date: Tue, 08 Jan 2013 11:45:49 -0500
Message-ID: <50EC4D3D.9060209@comcast.net> (raw)
In-Reply-To: <CAMkU=1waDCS4d6M1SeRDZq3JP6V7_Mf2PL2di_V4UwyfJYybiw@mail.gmail.com>
References: <CALd8TVECZDosPDZ7Cwv1gVRy1p2F4Hg+sw+240MsN36KUkt31g@mail.gmail.com>
<50E65B85.9000001@synedra.com>
<50E74A33.5000204@comcast.net>
<CAMkU=1yMkZtme3pB=J8=LrzmqtRDMhOU6mMp1KZAX_VTsdKTbA@mail.gmail.com>
<50E997C5.2000301@comcast.net>
<CAMkU=1waDCS4d6M1SeRDZq3JP6V7_Mf2PL2di_V4UwyfJYybiw@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
>
> It probably does, but from psql command line, you can do \d+ and \di+
\d+ doesn't appear to display any size information.
>
> If you have little control over your storage and are already IO bound,
> and the tables are growing rapidly, you may need to rethink that
> "deletes are rare" bit. So the inserts and updates do target a hot
> part, while the selects are evenly spread?
>
> In that case, it is very important to know if the slow part are the
> selects, or the insert and deletes. If the selects are slow, and the
> hot rows for selects can't be gathered together into a hot partition,
> then after clustering they will still be slow as the disk will still
> have to seek all over the place (massive data-mining type selects
> might be an exception to that, but I wouldn't count on it).
Since order_num is sequential, I could partition on it in broad
(sequential) ranges. That would put all recent/new rows in one
table-partition that would be a fraction of the size of the overall
(unpartitioned) table. I guess that would require manual maintenance
over-time (to switch to another, new partition as each grows).
>
> I think it is a matter of semantics. A small table is poor candidate
> for partitioning even if it has an excellent key to use for
> partitioning. A large table could be a good candidate up until you
> realize it doesn't have a good key to use, at which point it stops
> being a good candidate (in my opinion).
>
My first idea to evenly-partition the table was to use the order_num and
do a "mod" on it with the number of tables I wanted to use. That would
yield a partition-table number of 0-mod, and all rows for the same order
would stay within the same partition-table. However, you're right in
thinking that a search for orders could -- really WOULD -- require
retrieving details from multiple partitions, probably increasing IO. So
maybe the sequential partitioning (if at all) is better, just more
maintenance down-the-road.
>
> Was the order_num (from the parent table) the leading field of the 4
> column PK? If not, you might want to reorder the PK so that it is the
> leading field and cluster again. Or if reordering the PK columns is
> not convenient, make a new index on the order_num and cluster on that
> (perhaps dropping the index after the cluster, if it no longer serves
> a purpose)
>
Yes, the order_num is the first column in the PK, and our main browse
queries use, at a minimum, the first 2-3 columns in that PK in their
where-clause.
Many thanks again for all the input!
-AJ
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
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 using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: aweber@comcast.net, jeff.janes@gmail.com
Subject: Re: Partition table in 9.0.x?
In-Reply-To: <50EC4D3D.9060209@comcast.net>
* 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