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.

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.

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

murdered by words

Nice deepfake. It's so surprising how far the field has gone. I re never seeing the fake Nixon moon speech and being blown away

Naive question. Wouldn't sampling a power law dataset be straightforward? The idea is there's only a few outlier values, and the rest are uncommon. This distribution seems extremely common. Ie column with mostly NULL values and the rest somewhat unique non null strings.

I'm curious what data you saw and why the sampling didn't work?

I'm curious, will he answered given his vested interest in "exabyte-scale analytical database" ? :-)

And laurenz_albe has a vested interest in PG as he's a contributor.

So I, too, would love more concrete details.

I think the problem is PG does not know underlying data distribution and use one sampling strategy which works for normally distributed data.

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.

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

AFAIK the PostgreSQL's approach is based upon http://citeseerx.ist.psu.edu/viewdoc/download?doi=10.1.1.53....

You can change the statistics target, see 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

But if your statistics are bad, it will certainly mess up some of your query plans.

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.

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.

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"

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

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.

> 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?

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.

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

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

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.

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

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

You would get predictably bad behavior and performance without the statistics collected.

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.

Then use something like pg_hint_plan: 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.

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