Sfrgttk

If a VARCHAR(MAX) column is included in an index, is the entire value always stored in the index page(s)?

Why are both D and D# fitting into my E minor key?

Denied boarding although I have proper visa and documentation. To whom should I make a complaint?

Wu formula for manifolds with boundary

また usage in a dictionary

Do wooden building fires get hotter than 600°C?

What would be the ideal power source for a cybernetic eye?

For a new assistant professor in CS, how to build/manage a publication pipeline

Did MS DOS itself ever use blinking text?

Irreducible of finite Krull dimension implies quasi-compact?

Has negative voting ever been officially implemented in elections, or seriously proposed, or even studied?

What do you call the main part of a joke?

Extracting terms with certain heads in a function

Can a party unilaterally change candidates in preparation for a General election?

Can an alien society believe that their star system is the universe?

Closed form of recurrent arithmetic series summation

Withdrew £2800, but only £2000 shows as withdrawn on online banking; what are my obligations?

Can anything be seen from the center of the Boötes void? How dark would it be?

Does classifying an integer as a discrete log require it be part of a multiplicative group?

Should I use a zero-interest credit card for a large one-time purchase?

How do pianists reach extremely loud dynamics?

When a candle burns, why does the top of wick glow if bottom of flame is hottest?

Around usage results

How to react to hostile behavior from a senior developer?

PostgreSQL: how to partition 1000 tenants

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}

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:

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

postgresql

share

asked 2 mins ago

Alex PlugaruAlex Plugaru

1162

add a comment | 

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:

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

postgresql

share

asked 2 mins ago

Alex PlugaruAlex Plugaru

1162

add a comment | 

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:

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

postgresql

share

asked 2 mins ago

Alex PlugaruAlex Plugaru

1162

share

asked 2 mins ago

Alex PlugaruAlex Plugaru

1162

share

asked 2 mins ago

Alex PlugaruAlex Plugaru

1162

asked 2 mins ago

Alex PlugaruAlex Plugaru

1162

asked 2 mins ago

Alex PlugaruAlex Plugaru

1162