We have a multi-tenant app that has about 1000 customers more or less. When a customer churns we delete all their data after a period. We have a few tables that are pretty big and we're considering using partitioning to split them per customer.
1000 tenants (customers) are a lot of partitions - is it reasonable to do this on PostgreSQL?
Currently, the separation between our tenants is via an
account_id column on all tables in the DB. There are a few tables that are pretty big. For example, there is an
event table (the one I'm interested in partitioning) that contains audit logs and other events for everything that happens in our app.
Here are a few facts about the event table:
- It contains about 300M rows + a few of composite/partial indexes.
- The count of events by
account_idis very uneven, 5% accounts have 50% of the data.
- There is a timestamp field and a few others (JSONB,
- Write operations: inserts mostly and deletes (per
account_id). Deletes can potentially be millions of rows. No updates. Deletes of big accounts are rare and not a big performance issue at the moment.
- Selects are either for a specific event (by
id) or all events in a given period of time. Period of time is not always set.
account_idis always present in the query.
- Deletes will be fast because of
- Queries should also be pretty decent since all queries contains
WHERE account_id = 123
- Are 1000 partitions too much for Postgres?
- Uneven distribution of events per account creating a few super big partitions and a few small ones.
Partitioning by timestamp:
- Recent data is usually mostly accessed and will make queries with a timestamp faster.
- More predictable/even distribution of events.
- Deletion of a single account might touch a lot of partitions - not a big concern.
- Always needs to contain a filter by timestamp - which is not always possible.