0

Setup

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.

Problem

1000 tenants (customers) are a lot of partitions - is it reasonable to do this on PostgreSQL?

More details

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_id is very uneven, 5% accounts have 50% of the data.
  • There is a timestamp field and a few others (JSONB, author_id, etc..)
  • 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 account_id + id) or all events in a given period of time. Period of time is not always set. account_id is always present in the query.

Possible solutions

Partionining by account_id:

Pros:

  • Deletes will be fast because of DROP TABLE.
  • Queries should also be pretty decent since all queries contains WHERE account_id = 123

Cons:

  • 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:

Pros:

  • Recent data is usually mostly accessed and will make queries with a timestamp faster.
  • More predictable/even distribution of events.

Cons:

  • 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.
  • Did you think about using PostgreSQL schemas? They should exactly fit the purpose you need: postgresql.org/docs/11/ddl-schemas.html. "Schemas are analogous to directories at the operating system level, except that schemas cannot be nested." – Patrick Mevzek Apr 18 at 2:24
  • It’s too late now. We explored it at the beginning but we dropped the idea because it adds extra work for migrations and maintaince. I’m also not sure how it affects performance. We also don’t need to split everything, an tenant id is much cheaper than having schémas for every tenant. – Alex Plugaru Apr 18 at 2:32
  • Maybe, but they allow clear logical separation of data inside the same physical database. I do not think they change anything much for migrations and maintenance and they can not badly impact performance. As a bonus you are able to better protect data and make sure stuff from customers X does not pollute stuff from customers Y, but obviously it depends how your application is written. As for "much cheaper" i do not know what that means, your own question is about if too many partitions due to account_id create a problem or not... Anyway that was just a suggestion in passing. – Patrick Mevzek Apr 18 at 3:00
  • The partition you can do and their relevant performances or constraints depend on the PostgreSQL version you are using, but you are not saying which one. You may want to add that to your post. – Patrick Mevzek Apr 18 at 3:01
  • Sorry, it's Postgres 11. – Alex Plugaru Apr 18 at 22:44
2

Partitioning is mostly about speeding up deletes and sequential scans.

  • If your biggest pain is the big deletes by account_id, list partitioning on that column would be the best solution.

  • If your biggest pain is getting rid of all data older than a certain date, range partitioning by time would be the solution.

  • If you have may queries that have to perform sequential scans of large tables, but have a WHERE condition on account_id or a time constraint, partitioning on that WHERE condition would allow PostgreSQL to perform the sequential scan only on some of the partitions.

You have to decide if any of these things is painful enough for you to consider partitioning. Partitioning doesn't come for free: it will increase query planning time and sometimes execution time as well.

Partitioning won't make index scans faster, often the opposite is the case. Only partition if you expect a real benefit from it.

1000 partitions is almost too many to be efficient. You might consider having separate partitions for the bigger accounts and bundling the rest, perhaps using a default partition.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.