Re: splitting data into multiple tables

On Mon, 25 Jan 2010, Viji V Nair wrote:
> I think this wont help that much if you have a single machine. Partition the
> table and keep the data in different nodes. Have a look at the tools like
> pgpool.II

So partitioning. You have three choices:

1. Use a single table
2. Partition the table on the same server
3. Partition the data across multiple servers.

This is in increasing order of complexity.

There will probably be no problem at all with option 1. The only problem
arises if you run a query that performs a full sequential scan of the
entire table, which would obviously take a while. If your queries are
indexable, then option 1 is almost certainly the best option.

Option 2 adds complexity in the Postgres server. You will need to
partition your tables in a logical manner - that is, there needs to be
some difference between rows in table a compared to rows in table b. This
means that the partitioning will in effect be a little like indexing. You
do not want to have too many partitions. The advantage is that if a query
requires a full sequential scan, then there is the possibility of skipping
some of the partitions, although there is some complexity involved in
getting this to work correctly. In a lot of cases, partitioning will make
queries slower by confusing the planner.

Option 3 is only useful when you have a real performance problem with
long-running queries (partitioning the data across servers) or with very
large numbers of queries (duplicating the data across servers). It also
adds much complexity. It is fairly simple to run a "filter these results
from the table" queries across multiple servers, but if that was all you
were doing, you may as well use an index instead. It becomes impossible to
perform proper cross-referencing queries without some very clever software
(because not all the data is available on the server), which will probably
be hard to manage and slow down the execution anyway.

My recommendation would be to stick with a single table unless you have a
real need to partition.

Matthew

--
Note: some countries impose serious penalties for a conspiracy to overthrow
the political system. THIS DOES NOT FIX THE VULNERABILITY.
-- http://seclists.org/vulnwatch/2003/q2/0002.html