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
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?