On Oct 25, 2007, at 10:36 AM, tfinneid@xxxxxxxxxxxxxxxxxxxxx wrote:
The db worked fine until it reached perhaps 30-40 thousand
partitions.
It depends on how you have the partitions set up and how you're
accessing them. Are all of these partitions under the same parent
table? If so, then trying run a SELECT COUNT(*) against the parent
table is simply insane. Think about it, you're asking one query to
scan 55000 tables. What you need to do is partition based on your
access patterns, not what you *think* will help with performance down
the road. Look into constraint exclusion, whether or not you can
just access child tables directly, and whether you really need all of
these under one logical table. Also, no matter how you do the
partitioning, once you get up to that many and more relations in your
system, dumps and restores take a lot longer.
The design is based on access patterns, i.e. one partition
represents a
group of data along a discrete axis, so the partitions are the
perfect for
modeling that. Only the last partition will be used on normal
cases. The
previous partitions only need to exists until the operator deletes
them,
which will be sometime between 1-6 weeks.
Regarding dumps and restore; the system will always be offline during
those operations and it will be so for several days, because a new
project
might start at another location in the world, so the travelling there
takes time. In the mean time, all admin tasks can be performed without
problems, even backup operations that take 3 days.
Excellent, it sounds like you should be fine then. One thing to
note: if you want to get an "idea" of how many rows you have in your
partitions, you can run a SUM aggregate on reltuples in pg_class for
all of your partitions. The more recent the last ANALYZE for each
table, the more accurate those values will be.
Erik Jones
Software Developer | Emma®
erik@xxxxxxxxxx
800.595.4401 or 615.292.5888
615.292.0777 (fax)
Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
