From: Gregory Stark <stark@enterprisedb.com>
To: Scott Marlowe <smarlowe@g2switchworks.com>
Cc: david@lang.hm
Cc: 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: Tue, 05 Jun 2007 19:55:44 +0100
Message-ID: <87sl96z0wf.fsf@oxford.xeocode.com> (raw)
In-Reply-To: <466593E5.1010904@g2switchworks.com>
References: <C289C6B1.A63%tandrews@soliantconsulting.com>
<1180980943.31471.322.camel@archimedes>
<46645DD9.7030805@soliantconsulting.com>
<877iqjmro5.fsf@oxford.xeocode.com>
<46647427.4030103@g2switchworks.com>
<Pine.LNX.4.64.0706041631490.11737@asgard.lang.hm>
<466593E5.1010904@g2switchworks.com>
"Scott Marlowe" <smarlowe@g2switchworks.com> writes:
> Sorry, I think I initially read your response as "Postgres doesn't really get
> any faster by breaking the tables up" without the "like that" part.
Well breaking up the tables like that or partitioning, either way should be
about equivalent really. Breaking up the tables and doing it in the
application should perform even better but it does make the schema less
flexible and harder to do non-partition based queries and so on.
I guess I should explain what I originally meant: A lot of people come from a
flat-file world and assume that things get slower when you deal with large
tables. In fact due to the magic of log(n) accessing records from a large
index is faster than first looking up the table and index info in a small
index and then doing a second lookup in up in an index for a table half the
size.
Where the win in partitioning comes in is in being able to disappear some of
the data entirely. By making part of the index key implicit in the choice of
partition you get away with a key that's half as large. And in some cases you
can get away with using a different key entirely which wouldn't otherwise have
been feasible to index. In some cases you can even do sequential scans whereas
in an unpartitioned table you would have to use an index (or scan the entire
table).
But the real reason people partition data is really for the management ease.
Being able to drop, and load entire partitions in O(1) is makes it feasible to
manage data on a scale that would simply be impossible without partitioned
tables.
--
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, smarlowe@g2switchworks.com, david@lang.hm, tandrews@soliantconsulting.com, mark.lewis@mir3.com
Subject: Re: Thousands of tables versus on table?
In-Reply-To: <87sl96z0wf.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