I'm building a DB that will have several tables partitioned into 64 partitions. Although I'm using PG 10, I'm using the inheritance method as I need greater control over each table than what is currently possible with declarative partitioning.

I have a hash function in C that determines which partition to target for all ops. It's based on the first 3 columns.

The routing uses dynamic SQL since I figured that is faster than a 64-entry CASE statement.

Then it dawned on me that perhaps there is another way to do this. What if I had 64 instances of the DB running on n servers, and the web services tier directed the query to the applicable node?

This way I'd need no partitioning at all inside any DB, hence none of the complexity, overhead, or dynamic SQL.

However, the single DB would become 64, which would require more total resources and would introduce other complexities. Nonetheless, the 64 could operate on several physical servers, which provides scaling benefits.

Replication gets more complicated.

Could anyone give me any advice on this, please?

  • 1
    Maybe the number of partitions should not be hardcoded in the long term? Did you check Citus cluster? You should be able to scale the number of hosts & shards as your business grows. – filiprem Dec 4 '17 at 8:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.