Re: Intentionally splitting data in a table across files. (fwd)

On Wed, 6 Jun 2001, Tom Lane wrote:

> "Nick Fankhauser" <nickf(at)ontko(dot)com> writes:
> > Does PostgreSQL support (or some day will support) "partitioned tables"?
>
> It's not on anyone's radar screen AFAIK.
>
> > Alternately, does anyone have an idea about how to address this need in a
> > different way using existing tools?
>
> Make a view that's a UNION of the component tables, plus rules that
> cause inserts to go into the appropriate components.

I did a poor mans version of this (sort of) in Perl. I had some data
which had a fairly evenly distributed field (we'll call it foo :). 'foo'
was a varchar(14) field that contained a textual timestamp (don't remember
why, but it looked like '20010606123456'). I created 10 tables
(ie. bar0, bar1, bar2, ... bar9), and then had my insert function select
which table to insert into based on the last character of 'foo'. Since I
was receiving updates about every 7 seconds during business hours, it
worked quite well. All of the resultant tables were of similar size.

After I created the files, I distributed them to 5 drives attached via
SCSI using symlinks (bar0 and bar5 on sd0, etc, to limit the chance of
contention).

Selecting data using a UNION view was probably possible (v7.0.3) but I
never thought to try it that way. I never found any fast way to select
data from the resulting tables, and since the goal of this data
distribution was speed, we later scrapped the idea. They sure looked
pretty though ;).

- brian

>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://www.postgresql.org/search.mpl
>