8 ms·

The assertion that PostgreSQL can handle dozens of TB of data needs to be qualified, as this is definitely not the case in some surprising and unexpected cases

The assertion that PostgreSQL can handle dozens of TB of data needs to be qualified, as this is definitely not the case in some surprising and unexpected cases that are rarely talked about. PostgreSQL's statistics collection, which is used by the query planner, doesn't scale with storage size. For some ordinary data distributions at scale, the statistical model won't reflect any kind of reality and therefore can produce pathological query plans. It is quite difficult to get around this scaling behavior. Consequently, I've moved away from using PostgreSQL for data models with distributions such that these query planner limitations will occur. These pathologies occur well before the "dozens of TB" range. The statistics collector is not a scalable design generally, but that is another matter. In its current state it does not degrade gracefully with scale.

andy_ppp 6y ago
This sounds really in depth and useful, are there more in depth articles about how to avoid these types of query.
claytonjy 6y ago
This is intriguing; could you give an example of a distribution Postgres struggles with at scale, and an application that produces such a distribution?
- mkilling 6y ago
  We’ve recently been surprised by wrong estimates made by the planner resulting in inefficient query plans. It seems that power law distributions over multiple tables aren’t handled very well: https://stackoverflow.com/questions/65861780/is-there-a-way-to-improve-postgresql-estimates-when-joining-skewed-relations/65862472#65862472 https://stackoverflow.com/questions/65861780/is-there-a-way-...
silon42 6y ago
Is it easy to disable it? Personally I would not want any unpredictable behavior from query planner anyway, especially at scale.
- Tostino 6y ago
  You would get predictably bad behavior and performance without the statistics collected.
  - ben509 6y ago
    Predictably bad won't result in a sudden spike in IO usage that brings down a production system. This is why Oracle has SQL profiles.
    - Tostino 6y ago
      Then use something like pg_hint_plan: https://pghintplan.osdn.jp/pg_hint_plan.html https://pghintplan.osdn.jp/pg_hint_plan.html (never used it myself) I've been bitten by bad optimizer choices which took way too long to figure out how to debug / fix, so I know the pain. I do hope the PG optimizer continues improving. It's one of the weakest areas in comparison to commercial DB's IMO.
eatonphil 6y ago
Neither of you have given a reproducible example one way or the other! I could share that I know of TB of core analytics data running postgresql and it does alright. But it would be nicest if there were a more tangible study to talk about.
laurenz_albe 6y ago
This seems to be unfounded criticism. When statistics are gathered, PostgreSQL samples a certain percentage of the table, so that obviously scales. The number of "most common values" and histogram buckets scales up to 10000, which should be good even for large tables. While I'll readily admit that not all aspects of cross-column dependencies are dealt with, and cross-table distributions are not considered, that has nothing to do with size. I guess you hit a problem somewhere, couldn't solve it and jumped to unwarranted conclusions.
- jandrewrogers 6y ago
  It is ironic that you accuse me of "unwarranted conclusions". I've been customizing and modifying PostgreSQL internals for almost two decades, I know how to read the source. You aren't as familiar with PostgreSQL as you think you are. This wasn't my problem, I was asked by a well-known company with many large PG installations and enterprise support contracts to look at the issue because no one else could figure it out. The limitations of the statistics collector are not only evident in the source but they are documented there. There are also deep architectural reasons why you can't trivially modify the statistics collector -- I looked into this option -- to work for larger tables without introducing other serious issues. If you have a uniform distribution of values, the statistics collector will work fine. In the above case, the values followed a power law distribution which created extreme biases in the statistical model due to necessary restrictions on sampling. Other distributions can have similar effects once you exceed the ability of the statistics collector to acquire a representative sample.
  - brasetvik 6y ago
    > I know how to read the source. You aren't as familiar with PostgreSQL as you think you are. Oh, maybe you have read some of Laurenz Albe's many contributions to Postgres, then. https://git.postgresql.org/gitweb/?p=postgresql.git&a=search&h=HEAD&st=commit&s=laurenz https://git.postgresql.org/gitweb/?p=postgresql.git&a=search...
    - bobthebuilders 6y ago
fabian2k 6y ago
You can change the statistics target, see https://www.postgresql.org/docs/current/runtime-config-query.html#GUC-DEFAULT-STATISTICS-TARGET https://www.postgresql.org/docs/current/runtime-config-query... You can also create more advanced statistics over multiple columns: https://www.postgresql.org/docs/current/planner-stats.html https://www.postgresql.org/docs/current/planner-stats.html But if your statistics are bad, it will certainly mess up some of your query plans.
- jandrewrogers 6y ago
  Yes, everyone knows these things, it does not address the issue. Due to its architecture, there are difficult to change internal limits on how PostgreSQL samples the data. Under some conditions, it is not possible for the sampling mechanics to build a representative model -- the actual statistics used for query planning will be quasi-random. One of the ways this manifests is that every time you rebuild the statistics you get a completely different statistical model even if the data has barely changed.
boomer918 6y ago
Theoretically your argument would make sense, but practically your data distribution should be uniform enough for the statistics collector to work for terabytes and terabytes. I've seen query plans get messed up with partial indexes, but if you have a regular index, you needn't worry.
natmaka 6y ago
Even when using as much stat samples as possible (3000000) for each of those tables: ALTER TABLE table_name ALTER column_name SET STATISTICS 10000; See https://www.postgresql.org/docs/current/sql-altertable.html https://www.postgresql.org/docs/current/sql-altertable.html AFAIK the PostgreSQL's approach is based upon http://citeseerx.ist.psu.edu/viewdoc/download?doi=10.1.1.53.1734&rep=rep1&type=pdf http://citeseerx.ist.psu.edu/viewdoc/download?doi=10.1.1.53....
jajool 6y ago
I have seen many PostgreSQL benchmarks having solid performance with TB data but my real world experience is the complete opposite. Here are some of the main issues that I have encountered so far: 1. Queries on large tables (around 10 GB) are slow even when "index only scan" is used because of MVCC and the way postgreSQL manages concurrency. 2. Hot-standby instances can't be used for anything serious since all queries are dropped regularly (I believe it's not safe to use "hot_standby_feedback" config to overcome this issue). 3. It is not possible to have tables with heavy "update" workflows. (because of simultaneous autovaccum execution) I would be very happy if anyone could show me that I am wrong.
- singron 6y ago
  1 mostly applies to update heavy tables since index only scans use the visibility map, which would be frequently invalidated. 3 is definitely true, especially the larger the table. I've had success splitting frequently updated columns out into their own much smaller table, or any other trick to concentrate updates into a small table. Also MVCC bookkeeping requires updating pages, so an UPDATE that doesn't change any field and SELECT FOR UPDATE will cause the same problem.
- tommyzli 6y ago
  1. You'll have to define "slow" - I have a 3TB table where an index only scan takes under 1ms 2. hot_standby_feedback is absolutely safe. I've got 5 hot standbys in prod with that flag enabled 3. Again, it depends on how "heavy" your update throughput is. It is definitely tough to find the right balance to configure autovacuum between "so slow that it can't keep up" and "so fast that it eats up all your I/O"
- YorickPeterse 6y ago
  > 2. Hot-standby instances can't be used for anything serious since all queries are dropped regularly (I believe it's not safe to use "hot_standby_feedback" config to overcome this issue). Hot-standby instances are fine. We've been using these for GitLab.com for a number of years now, and never have they caused any issues. We do have some code in place that retries queries when they are cancelled by the hot-standby, but IIRC this doesn't happen often.
aeyes 6y ago
Vacuum is a way bigger problem, if you have a few million updates per day on a 5TB table you are going to have a hard time keeping index and page bloat down. Sure, if your tables are insert only you might be fine but doing any kind of DDL or maintenance (analyze after version upgrades) is going to ruin your day.
gher-shyu3i 6y ago
> PostgreSQL's statistics collection, which is used by the query planner, doesn't scale with storage size. Out of curiosity, do statistics collectors for other offerings (e.g. MySQL, SQL Server, Oracle) scale with storage size?
fanf2 6y ago
Does statistics collection scale better with partitioned tables? TFA mentions that partitioning helps with vacuum, and AIUI vacuum workers also collect statistics.

andy_ppp 6y ago

This sounds really in depth and useful, are there more in depth articles about how to avoid these types of query.

claytonjy 6y ago

This is intriguing; could you give an example of a distribution Postgres struggles with at scale, and an application that produces such a distribution?

mkilling 6y ago
We’ve recently been surprised by wrong estimates made by the planner resulting in inefficient query plans. It seems that power law distributions over multiple tables aren’t handled very well: https://stackoverflow.com/questions/65861780/is-there-a-way-to-improve-postgresql-estimates-when-joining-skewed-relations/65862472#65862472 https://stackoverflow.com/questions/65861780/is-there-a-way-...

silon42 6y ago

Is it easy to disable it? Personally I would not want any unpredictable behavior from query planner anyway, especially at scale.

Tostino 6y ago
You would get predictably bad behavior and performance without the statistics collected.
- ben509 6y ago
  Predictably bad won't result in a sudden spike in IO usage that brings down a production system. This is why Oracle has SQL profiles.
  - Tostino 6y ago
    Then use something like pg_hint_plan: https://pghintplan.osdn.jp/pg_hint_plan.html https://pghintplan.osdn.jp/pg_hint_plan.html (never used it myself) I've been bitten by bad optimizer choices which took way too long to figure out how to debug / fix, so I know the pain. I do hope the PG optimizer continues improving. It's one of the weakest areas in comparison to commercial DB's IMO.

eatonphil 6y ago

Neither of you have given a reproducible example one way or the other! I could share that I know of TB of core analytics data running postgresql and it does alright. But it would be nicest if there were a more tangible study to talk about.

laurenz_albe 6y ago

This seems to be unfounded criticism. When statistics are gathered, PostgreSQL samples a certain percentage of the table, so that obviously scales. The number of "most common values" and histogram buckets scales up to 10000, which should be good even for large tables. While I'll readily admit that not all aspects of cross-column dependencies are dealt with, and cross-table distributions are not considered, that has nothing to do with size. I guess you hit a problem somewhere, couldn't solve it and jumped to unwarranted conclusions.

jandrewrogers 6y ago
It is ironic that you accuse me of "unwarranted conclusions". I've been customizing and modifying PostgreSQL internals for almost two decades, I know how to read the source. You aren't as familiar with PostgreSQL as you think you are. This wasn't my problem, I was asked by a well-known company with many large PG installations and enterprise support contracts to look at the issue because no one else could figure it out. The limitations of the statistics collector are not only evident in the source but they are documented there. There are also deep architectural reasons why you can't trivially modify the statistics collector -- I looked into this option -- to work for larger tables without introducing other serious issues. If you have a uniform distribution of values, the statistics collector will work fine. In the above case, the values followed a power law distribution which created extreme biases in the statistical model due to necessary restrictions on sampling. Other distributions can have similar effects once you exceed the ability of the statistics collector to acquire a representative sample.
- brasetvik 6y ago
  > I know how to read the source. You aren't as familiar with PostgreSQL as you think you are. Oh, maybe you have read some of Laurenz Albe's many contributions to Postgres, then. https://git.postgresql.org/gitweb/?p=postgresql.git&a=search&h=HEAD&st=commit&s=laurenz https://git.postgresql.org/gitweb/?p=postgresql.git&a=search...
  - bobthebuilders 6y ago

fabian2k 6y ago

You can change the statistics target, see https://www.postgresql.org/docs/current/runtime-config-query.html#GUC-DEFAULT-STATISTICS-TARGET https://www.postgresql.org/docs/current/runtime-config-query... You can also create more advanced statistics over multiple columns: https://www.postgresql.org/docs/current/planner-stats.html https://www.postgresql.org/docs/current/planner-stats.html But if your statistics are bad, it will certainly mess up some of your query plans.

jandrewrogers 6y ago
Yes, everyone knows these things, it does not address the issue. Due to its architecture, there are difficult to change internal limits on how PostgreSQL samples the data. Under some conditions, it is not possible for the sampling mechanics to build a representative model -- the actual statistics used for query planning will be quasi-random. One of the ways this manifests is that every time you rebuild the statistics you get a completely different statistical model even if the data has barely changed.

boomer918 6y ago

Theoretically your argument would make sense, but practically your data distribution should be uniform enough for the statistics collector to work for terabytes and terabytes. I've seen query plans get messed up with partial indexes, but if you have a regular index, you needn't worry.

natmaka 6y ago

Even when using as much stat samples as possible (3000000) for each of those tables: ALTER TABLE table_name ALTER column_name SET STATISTICS 10000; See https://www.postgresql.org/docs/current/sql-altertable.html https://www.postgresql.org/docs/current/sql-altertable.html AFAIK the PostgreSQL's approach is based upon http://citeseerx.ist.psu.edu/viewdoc/download?doi=10.1.1.53.1734&rep=rep1&type=pdf http://citeseerx.ist.psu.edu/viewdoc/download?doi=10.1.1.53....

jajool 6y ago

I have seen many PostgreSQL benchmarks having solid performance with TB data but my real world experience is the complete opposite. Here are some of the main issues that I have encountered so far: 1. Queries on large tables (around 10 GB) are slow even when "index only scan" is used because of MVCC and the way postgreSQL manages concurrency. 2. Hot-standby instances can't be used for anything serious since all queries are dropped regularly (I believe it's not safe to use "hot_standby_feedback" config to overcome this issue). 3. It is not possible to have tables with heavy "update" workflows. (because of simultaneous autovaccum execution) I would be very happy if anyone could show me that I am wrong.

singron 6y ago
1 mostly applies to update heavy tables since index only scans use the visibility map, which would be frequently invalidated. 3 is definitely true, especially the larger the table. I've had success splitting frequently updated columns out into their own much smaller table, or any other trick to concentrate updates into a small table. Also MVCC bookkeeping requires updating pages, so an UPDATE that doesn't change any field and SELECT FOR UPDATE will cause the same problem.
tommyzli 6y ago
1. You'll have to define "slow" - I have a 3TB table where an index only scan takes under 1ms 2. hot_standby_feedback is absolutely safe. I've got 5 hot standbys in prod with that flag enabled 3. Again, it depends on how "heavy" your update throughput is. It is definitely tough to find the right balance to configure autovacuum between "so slow that it can't keep up" and "so fast that it eats up all your I/O"
YorickPeterse 6y ago
> 2. Hot-standby instances can't be used for anything serious since all queries are dropped regularly (I believe it's not safe to use "hot_standby_feedback" config to overcome this issue). Hot-standby instances are fine. We've been using these for GitLab.com for a number of years now, and never have they caused any issues. We do have some code in place that retries queries when they are cancelled by the hot-standby, but IIRC this doesn't happen often.

aeyes 6y ago

Vacuum is a way bigger problem, if you have a few million updates per day on a 5TB table you are going to have a hard time keeping index and page bloat down. Sure, if your tables are insert only you might be fine but doing any kind of DDL or maintenance (analyze after version upgrades) is going to ruin your day.

gher-shyu3i 6y ago

> PostgreSQL's statistics collection, which is used by the query planner, doesn't scale with storage size. Out of curiosity, do statistics collectors for other offerings (e.g. MySQL, SQL Server, Oracle) scale with storage size?

fanf2 6y ago

Does statistics collection scale better with partitioned tables? TFA mentions that partitioning helps with vacuum, and AIUI vacuum workers also collect statistics.