From: Gregory Stark <stark@enterprisedb.com>
To: Thomas Andrews <tandrews@soliantconsulting.com>
Cc: Mark Lewis <mark.lewis@mir3.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Thousands of tables versus on table?
Date: Mon, 04 Jun 2007 21:18:43 +0100
Message-ID: <87y7izlbh8.fsf@oxford.xeocode.com> (raw)
In-Reply-To: <C289E6F5.A74%tandrews@soliantconsulting.com>
References: <C289E6F5.A74%tandrews@soliantconsulting.com>
"Thomas Andrews" <tandrews@soliantconsulting.com> writes:
> Clustering sounds like it might be a really good solution. How long does a
> cluster command usually take on a table with 50,000,000 records? Is it
> something that can be run daily/weekly?
ouch, ok, with 50M records cluster isn't going to be quick either, especially
if you have a lot of indexes.
With those kinds of numbers and with the kind of workload you're describing
where you have different areas that are really complete separate you might
consider partitioning the table. That's essentially what you're proposing
anyways.
Honestly table partitioning in Postgres is pretty young and primitive and if
you have the flexibility in your application to refer to different tables
without embedding them throughout your application then you might consider
that. But there are also advantages to being able to select from all the
tables together using the partitioned table.
> I'd rather not post the schema because it's not mine - I'm a consultant. I
> can tell you our vacuum every night is taking 2 hours and that disk IO is
> the real killer - the CPU rarely gets higher than 20% or so.
Do you ever update or delete these records? If you never update or delete
records then the vacuum is mostly a waste of effort anyways. (You still have
to vacuum occasionally to prevent xid wraparound but that's much much less
often).
If you do delete records in large batches or have lots of updates then
vacuuming daily with default fsm settings probably isn't enough.
How many indexes do you have?
And if they don't all have client_id in their prefix then I wonder about the
plans you're getting. It's unfortunate you can't post your schema and query
plans. It's possible you have some plans that are processing many more records
than they need to to do their work because they're using indexes or
combinations of indexes that aren't ideal.
specific enough
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
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: stark@enterprisedb.com, tandrews@soliantconsulting.com, mark.lewis@mir3.com
Subject: Re: Thousands of tables versus on table?
In-Reply-To: <87y7izlbh8.fsf@oxford.xeocode.com>
* 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