Re: select count() out of memory

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

regards

thomas
thomas