Partitioning PotgreSQL Database

Introduction In one of the projects I used to work on, we've employed CQRS approach with PostgreSQL as a write storage and NoSQL database as a read storage. As a safety measure, we had a special endpoint that allowed us to regenerate entire content of the read storage based on the write storage that is supposed to be the single source of truth. The story started one day when we discovered that our safety handle started to fail with the write storage database timeout due to several millions of rows in it. Generally speaking, the solution proposed below is applicable to a wide range of data storages but we'll touch upon some Postgres-specific tooling, so let's focus on that. Partitioning overview Partition is the process of splitting a dataset into more manageable, smaller pieces. This, in turn yeilds reduced INSERT and SELECT costs. Partitioning can be accomplished in many ways. For instance, vertical partitioning separates data into columns. Horizontal partition, on the contrary, divid