Advanced PostgreSQL Partitioning by Date with YugabyteDB Auto Sharding
Recently, I wrote about how YugabyteDB can scale without having to use PostgreSQL declarative partitioning and working around its limitations. I referenced a table from Kyle Hailey’s blog detailing a PostgreSQL production issue involving a “payments” table, where “payment_id” is the primary key. Kyle’s post pointed out that including a partition key in the primary key is mandatory in PostgreSQL.
In my blog post, I discussed the scaling process and explained how to hash shard the “payments” table on the “payment_id” without changing its primary key. This method ensures fast inserts by distributing the table rows across the cluster’s nodes. This approach works seamlessly with YugabyteDB’s automatic sharding and supports the use of any secondary index for additional use cases.
I received some feedback on Reddit and other sites that partitioning on a different column may be better. This is true for PostgreSQL, where the partition key limits the partitioning of the table and all the indexes. However, with YugabyteDB sharding, I don’t need to change the table partitioning to query a range of dates. YugabyteDB’s secondary indexes are global, meaning they don’t need to share a sharding key with their table. In addition, distributing the rows in a distributed PostgreSQL database like YugabyteDB offers extra benefits since it spreads data across multiple active servers.
