Jian Shi <jshi(at)unitrends(dot)com> wrote:
[moving the last sentence to the top]
> The system is 32-bit Linux, dual core, 4G memory. Postgres version
> is 8.1.21.
Version 8.1 is out of support and doesn't perform nearly as well as
modern versions.
http://wiki.postgresql.org/wiki/PostgreSQL_Release_Support_Policy
The system you're talking about is the same as what I bought as a
home computer four years ago. You don't mention your disk system,
but that doesn't sound like server-class hardware to me.
> Is there a suggested number of child tables for table
> partitioning,
Generally, don't go over about 100 partitions per table.
> I ran a stress test on a master table (with 800 thousand rows),
> trying to create 500,000 child tables for it, each child table has
> 2 indexes and 3 constraints (Primary key and foreign key).
That probably at least 5 disk files per table, to say nothing of the
system table entries and catalog caching. Some file systems really
bog down with millions of disk files in a single subdirectory.
That is never going to work on the hardware you cite, and is a very,
very, very bad design on any hardware.
> This stress test is for the partition plan I'm going to make,
> since we don't want to add another Field just for partitioning.
Why not?
-Kevin