We have an RDS Postgres database which keeps individual store data in a common schema with a field called store_id to distinguish between stores. Let's say we have 1MB per store and 10k stores so we're talking about 10GB of data.
We're making some changes to how the system works in general and rethinking some of the pieces. One possibility that came up is to create partitions for each store so they will be completely isolated. It seems ideal for our use case, but we don't have experience with this many partitions so I don't know if there are gotchas.
A few notes, the queries are always done on a per-store level - for this data set we don't need querying across stores. Most of the stores will be active most of the time - so this isn't like historical data where old partitions won't be written to. There isn't another dimension like time that would make sense to partition on.
Is there anything we should be concern about with a partition/schema per store or any areas that might be a problem?
UPDATE:
I found some more references to this:
- https://stackoverflow.com/questions/44524364/postgresqls-schemas-for-multi-tenant-applications
- https://www.citusdata.com/blog/2016/10/03/designing-your-saas-database-for-high-scalability/
- https://www.quora.com/What-are-the-pros-and-cons-of-using-schemas-to-support-multi-tenancy-in-PostgreSQL-How-scalable-is-it
Another (perhaps important) detail I forgot to add is that the individual terminals at each store have their own copy of the database (and sometimes multiple terminals/databases per location) so that can continue to run completely offline for extended periods of time. It's an asynchronous master-master data-level replication - writes can happen via an app to the cloud or on the terminals and we handle the syncing and conflict resolution.
The thought to migrate to a schema per store relates more to simplicity in the replication and backup logic to treat each store as an independent entity. It also could make it easier to scale because we can more easily move stores to different cloud databases. It also might help us with migrations if we could make schema changes per store as the upgrade to new versions (not sure if/how this would work with PG partitions.) It's not related to security concerns.