PostgreSQL: Auto-partition a table

Question

Hi,

I have a huge database which holds pairs of numbers (A,B), each ranging from 0 to 10,000 and stored as floats.

e.g.,

(1, 9984.4), (2143.44, 124.243), (0.55, 0), ...

Since the PostgreSQL table which stores these pairs grew quite large, I have decided to partition it into inheriting sub-tables. I intend to create 100 such tables, each storing a range of 1000x1000.

The problem is that these numbers tend to come in large chunks of nearby numbers. It means that in the future, some tables will be nearly empty and some will hold a very large portion of the database. Unfortunately, the distribution of future pairs is yet unknown.

I am looking for a way to automatically repartition my table. That means that if a certain subtable holds more than a specific number of pairs, it will be automatically partitioned into four sub-sub tables, and so on.

My questions are:

• Is recursive partitioning and inheritance possible in PostgreSQL 8.3? Will indexes and query plans understand it?
• What's the best way to split a subtable once it grew too large? I should point out that this isn't a live database, so a downtime of few hours every week is totally acceptable.
• UPDATE: I might make split the inheriting tables into four tables that would replace the original one (that is, inherit directly from the master table). I will avoid having more than one level of inheritance, but have thousands if tables inheriting directly from one table. What are the pros and cons of this approach?

Thanks in advance,

Adam

Answer 1

First of all, if the tables already are large, are you sure the distribution is not reliable for future estimates? Would a histogram done today be useless?

I think that even if recursive inheritance is possible, it adds unnecessary complexity to the model, both for maintenance and planner.

When you partition it to 100 tables, I expect you will generate the partitions and insert/update rules/triggers automatically.

The simplest approach may be to copy data from one partition to a temporary table, drop it, create 4 partitions in its place and copy the data back. I don't think this operation would be more difficult than recursive partitioning.

You could also ask folks over at PostgreSQL mailing lists. They're the best experts you can possibly get, including original developers.