First Iteration - Partitioning PostreSQL with FDW
This issue is to track a first iteration (or prototype) using postgresql partitioning with foreign data wrappers (FDW) on our Rails application database.
Notes from our recent session discussing goals and approach in no particular order:
sharding (def) - in the rest of this issue, the term sharding will refer to using the built in postgresql partitioning with foreign data wrappers (FDW).
Use reference architecture
We need to decide on a size, but the existing reference architecture(s) already have performance baselines we can use to compare the impact of sharding.
Simple example
Identify a sharding strategy that requires little to no application level changes for this first iteration. Some of the value in rolling out the first implementation will be in learning how difficult or easy it is to implement.
Goals
- Identify an implementation that:
- models a real world scenario
- requires little to no application changes (sharding is isolated to database)
- Implement a first iteration of sharding
- Document the implementation details
- Document concerns
- Compare the performance against baseline performance metrics
- Identify next iteration
Concerns
- Do we have representative data set
- Future concern for migrations
- Future concern for upgrades of each of the servers required for sharding
- Future concern for infrastructure requirements
- What is a reasonable limit to the number of partitions in PostgreSQL