citusdata
Support sharding by schema name
I've got a rubygem call Apartment that does database multi-tenancy for ActiveRecord using postgresql schemas. Effectively, our whole table structure is mirrored on a per-schema basis for each customer that we add to the system.
It'd be nice if we could shard based on the postgresql schema, such that the app itself wouldn't need to know about physical shards under the hood and queries containing a particular schema in the search path would automatically be routed to the correct physical machine.
The basic setup is that public schema stores excluded models which are non tenanted models. Then, for each tenant, we create a new schema and create all the tables etc in that schema. I guess this would involve triggers on things like CREATE SCHEMA x to actually create it on the correct shard, and then of course on queries themselves to route to that schema.
As discussed at PgConf, I'm not well versed on the internals of pg_shard itself but I'm quite happy to get the discussion going around the Apartment design and using schemas from a sharding concern.
:grin: ok, ok, we definitely see the +1 comments here. I'll chat with the team about where this is on our roadmap, since it seems to be a popular request.
Any further updates about the status of this? I would like to see it as a feature as well.
@bradrobertson -- happy to chat more about Apartment and sharding for multi-tenant applications. Citus' architecture lends itself better to sharding at the table level; and we're curious how this could interact with Apartment.
@Viatrak @Velora @Frank004 @Infrag @trevvvy -- I have a clarification question. Are you interested in using the Apartment gem, sharding by schema, or sharding for your multi-tenant application?
We recently released Citus v5.2, which enables full SQL coverage for multi-tenant applications. We're curious to hear your thoughts on the following: https://www.citusdata.com/blog/2016/08/10/sharding-for-a-multi-tenant-app-with-postgres/
@ozgune -- My interest is in sharding by schema for a multi-tenant application where each tenant's data lives in its own schema. It's very similar (maybe identical) to @bradrobertsons scenario except it's in Python/Django (django-tenant-schemas).
@Viatrak -- got it. I had a quick follow-up question.
Is the reason you're interested in sharding by schema because of django-tenant-schemas, or are you interested in isolation guarantees that come with using schemas?
If it's the latter, does PostgreSQL 9.5's row security policies help?
+1, I need an hibrid solution were the base schema is multitenant per table basis and for each client we also have a different schema complete custom acordly their needed.
+1, hmm, https://github.com/citusdata/django-multitenant looks great, but I think tenants separated by schema will be more helpful on data statistics group by tenant. I used https://github.com/django-tenants/django-tenants, just wish schema sharding works well with this package.
Glad to see this improvement. I commented on this back in 2016.
We are using the Apartment gem currently on postgres with Heroku. Database version upgrades are painful because of close to a million tables.
Where can I find more about how we might be able to use Citus with the Apartment gem with sharding by schema?
Thank you!
Citus 12 is available now: https://www.citusdata.com/blog/2023/07/18/citus-12-schema-based-sharding-for-postgres/
We haven't specifically tested Apartment, but there's a good chance it works out of the box. (if not, let us know!)