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.

Thanks in advance,

Adam