Partitions implementation with views

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I've seen a lot about partitions recently, and I had a "bright idea". I am
by no means a database expert, so perhaps this is utter nonsense.

A partition, as I understand it, contains only a select subset of a table.
Usually, it is data that is related to each other somehow. I guess an
example would be for an internet host who wants to provide a common
shopping cart functionality for all of its customers, but put their
specific data on a specific partition for ease of management and query
speed. They can't put the data into seperate databases because they also
need to examine the complete data set occasionally.

The common response is "Use partial indexes". But I imagine that they want
the ability to move partitions onto seperate OS partitions (hence the name,
"partition").

So here is a possible implementation.

Say we want to divide up the data in a table into N partitions.

Step 1: We create N identical tables. These tables are the "partitions".
They have the exact same columns in the exact same sequence as the
partitioned table.

Step 2: We will create a function that will tell us which partition a
specific row should belong in based on the data in that row.

Step 3: We create a view:

SELECT * FROM partition_1 UNION ALL SELECT * FROM partition_2 UNION ALL ...
SELECT * FROM partition_N;

Step 4: On that view, we create a rule for insert, update, and delete so
that the operation is applied to the appropriate partition, using the
function mentioned in Step 2.

Now that view is the partitioned table for all intents and purposes. The
partition tables are the partitions themselves.

Is this what they are looking for, or is it something completely different?

- --
Jonathan Gardner
jgardner(at)jonathangardner(dot)net
Live Free, Use Linux!
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)

iD8DBQE/yuSXWgwF3QvpWNwRAmblAJwKS0Lgk/wSC+AmH5fgX7yoicvfOACfYXXx
Hfk/9R84NCKJyAkuXCuG8Ak=
=Ifsm
-----END PGP SIGNATURE-----