CODEWITHSUNDEEP
algorithmpostgresqldatabase-partitioning
Adam Matan
Adam Matan

Reputation: 136191

PostgreSQL: Auto-partition a table

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:

Thanks in advance,

Adam

Upvotes: 3

Views: 4047

Answers (1)

Konrad Garus
Konrad Garus

Reputation: 54015

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.

Upvotes: 2

Related Questions