Index bloat reduced in PostgreSQL v14 • Hacker News Board

Re: Index bloat reduced in PostgreSQL v14

#51

Post by arp242 » Sat, Sep 11, 2021, 9:37 AM UTC

citrons wrote: post #45

scott113341 wrote: post #27

Earlier quoted context omitted.

I’m a huge fan of Postgres. This one is “user error”, but we still got bit pretty hard. A query plan changed, on a frequently-run query (~1k/sec) on a large table (~2B rows) without warning. Went from sub-millisecond to multi-second. The PG query planner is generally very good, but also very opaque. The statistics collected during an ANALYZE and used by the planner are subject to some significant caveats. Essentially…

I'm currently having a similar issue where the query planner refuses to use the indexes on a search query (was fine for w hile, but one day it just started de-optimizing itself). Instead just does a seq-scan. Instead of the execution taking ~40ms with indexes the query planner thinks that the seq scan of ~1.5s is better... Re-indexes the db and run analyze the table. It gets better for max 30min then PG de-optimizes…

Try lowering the random_page_cost value; this is the performance cost query planner uses for random reads, which is usually too high if you're using an SSD where random reads are cheap (on disks it's expensive). Just setting it to 1 works well in my case.

This solves many "it does a slow seq scan even though there's an index"-cases.

https://postgresqlco.nf/doc/en/param/random_page_cost/

There are some other query planner knobs you can tune as well; the https://postgresqlco.nf site is pretty good.

Re: Index bloat reduced in PostgreSQL v14

#52

Post by ruuda » Sat, Sep 11, 2021, 9:39 AM UTC

edoceo wrote: post #2

I'm sure we'll get a bunch of (well deserved) praise for PG here but, does anyone have a case where PG really shit the bed? (Besides the Uber one) (which is its own long thread)

Transaction id wraparound. Transaction ids are 32 bit and they are used in a ring like manner, so a transaction id can only be reused if there are no open transactions before it. If you (accidentally) keep a transaction open for a long time, then after a while you run out of transaction ids, and it is tough to recover from, because management operations themselves involve creating transactions.

Re: Index bloat reduced in PostgreSQL v14

#53

Post by castorp » Sat, Sep 11, 2021, 9:50 AM UTC

tomnipotent wrote: post #39

saurik wrote: post #32

Earlier quoted context omitted.

Is this really such a problem? I feel like you'd want pgbouncer anyway in order to mitigate connection establishment latency. Like, in my infrastructure I generally have not one but two layers of pgbouncer, one on either side of the network, so the topology of underlying network connections could be reasonably static.

You'd be surprised how websites are out there running Wordpress/phpBB and managing 100+ concurrent connections with per-process pooling.

"hundreds" of concurrent connections isn't really a problem for Postgres.

Several thousands are - at least up until now. V14 will improve this substantially.

Re: Index bloat reduced in PostgreSQL v14

#54

Post by naranha » Sat, Sep 11, 2021, 10:01 AM UTC

edoceo wrote: post #2

I'm sure we'll get a bunch of (well deserved) praise for PG here but, does anyone have a case where PG really shit the bed? (Besides the Uber one) (which is its own long thread)

PostgreSQL has a lot of really great features, but some things are seriously underdeveloped as well, mostly because missing man power. You cannot reorder columns of a table easily, it does not support compression of connections (yet), XID-wraparound as others have already said.

I would also personally love map-reduce indexes (for example for efficiently calculating sums or other aggregate functions...)

Re: Index bloat reduced in PostgreSQL v14

#55

Post by whoknowswhat11 » Sat, Sep 11, 2021, 10:21 AM UTC

scott113341 wrote: post #27

edoceo wrote: post #2

I'm sure we'll get a bunch of (well deserved) praise for PG here but, does anyone have a case where PG really shit the bed? (Besides the Uber one) (which is its own long thread)

I’m a huge fan of Postgres. This one is “user error”, but we still got bit pretty hard. A query plan changed, on a frequently-run query (~1k/sec) on a large table (~2B rows) without warning. Went from sub-millisecond to multi-second. The PG query planner is generally very good, but also very opaque. The statistics collected during an ANALYZE and used by the planner are subject to some significant caveats. Essentially…

Aurora PostgreSQL has something called Query Plan Management - which I like - is meant to address this type of issue especially for large tables that have key queries that you could blow up DB basically if they go haywire in planning.

Would def be a feature that would be nice to see in PostgreSQL itself.

Re: Index bloat reduced in PostgreSQL v14

#56

Post by jashmatthews » Sat, Sep 11, 2021, 10:26 AM UTC

citrons wrote: post #45

scott113341 wrote: post #27

Earlier quoted context omitted.

I’m a huge fan of Postgres. This one is “user error”, but we still got bit pretty hard. A query plan changed, on a frequently-run query (~1k/sec) on a large table (~2B rows) without warning. Went from sub-millisecond to multi-second. The PG query planner is generally very good, but also very opaque. The statistics collected during an ANALYZE and used by the planner are subject to some significant caveats. Essentially…

I'm currently having a similar issue where the query planner refuses to use the indexes on a search query (was fine for w hile, but one day it just started de-optimizing itself). Instead just does a seq-scan. Instead of the execution taking ~40ms with indexes the query planner thinks that the seq scan of ~1.5s is better... Re-indexes the db and run analyze the table. It gets better for max 30min then PG de-optimizes…

Tune autovacuum analyze to run every 30mins. Seriously. The query planner needs up to date statistics.

Re: Index bloat reduced in PostgreSQL v14

#57

Post by Dylan16807 » Sat, Sep 11, 2021, 10:49 AM UTC

jashmatthews wrote: post #56

citrons wrote: post #45

Earlier quoted context omitted.

I'm currently having a similar issue where the query planner refuses to use the indexes on a search query (was fine for w hile, but one day it just started de-optimizing itself). Instead just does a seq-scan. Instead of the execution taking ~40ms with indexes the query planner thinks that the seq scan of ~1.5s is better... Re-indexes the db and run analyze the table. It gets better for max 30min then PG de-optimizes…

Tune autovacuum analyze to run every 30mins. Seriously. The query planner needs up to date statistics.

Why does it need up to date statistics to decide not to change anything?

I mean, if you could freeze statistics entirely wouldn't that fix this problem?

Re: Index bloat reduced in PostgreSQL v14

#58

Post by zz865 » Sat, Sep 11, 2021, 10:51 AM UTC

rrrrrrrrrrrryan wrote: post #44

da_chicken wrote: post #41

Earlier quoted context omitted.

Unfortunately, this is just the reality of using an RDBMS. I've seen similar behavior on Informix and SQL Server (with a smaller load than yours). They all occasionally generate suboptimal query plans. That's what your DBA is for. SQL Server was somewhat notorious for it when migrating to 2014 because they rewrote the cardinality estimator. It generally worked better, but in some systems it really didn't. Some people…

> this is just the reality of using an RDBMS It's the reality of Postgres, yes, but not all relational database. You mentioned SQL Server, which lets you lock in a query plan, specifically to cover the use case the parent described. When you have a Very Important frequently-run query that pulls from a monstrous table, it's nice to be able to sleep peacefully knowing the DB won't shit the bed because something complet…

We update statistics weekly on SQL server. One week did did a particularly aggressive data cleanup and then ran stats which created a bad plan when the "tiny" table quickly grew.

Re: Index bloat reduced in PostgreSQL v14

#59

Post by masklinn » Sat, Sep 11, 2021, 10:53 AM UTC

da_chicken wrote: post #41

scott113341 wrote: post #27

Earlier quoted context omitted.

I’m a huge fan of Postgres. This one is “user error”, but we still got bit pretty hard. A query plan changed, on a frequently-run query (~1k/sec) on a large table (~2B rows) without warning. Went from sub-millisecond to multi-second. The PG query planner is generally very good, but also very opaque. The statistics collected during an ANALYZE and used by the planner are subject to some significant caveats. Essentially…

Unfortunately, this is just the reality of using an RDBMS. I've seen similar behavior on Informix and SQL Server (with a smaller load than yours). They all occasionally generate suboptimal query plans. That's what your DBA is for. SQL Server was somewhat notorious for it when migrating to 2014 because they rewrote the cardinality estimator. It generally worked better, but in some systems it really didn't. Some people…

> Unfortunately, this is just the reality of using an RDBMS. I've seen similar behavior on Informix and SQL Server (with a smaller load than yours). They all occasionally generate suboptimal query plans. That's what your DBA is for.

Other DBs let you lock in query plans or provide query hints, but postgres' developers are against either, which is not necessarily complete nonsense as it avoids users shooting themselves in the foot… but it also prevents users from digging themselves out of query planner stupidity.

Re: Index bloat reduced in PostgreSQL v14

#60

Post by williamdclt » Sat, Sep 11, 2021, 10:55 AM UTC

scott113341 wrote: post #27

edoceo wrote: post #2

I'm sure we'll get a bunch of (well deserved) praise for PG here but, does anyone have a case where PG really shit the bed? (Besides the Uber one) (which is its own long thread)

I’m a huge fan of Postgres. This one is “user error”, but we still got bit pretty hard. A query plan changed, on a frequently-run query (~1k/sec) on a large table (~2B rows) without warning. Went from sub-millisecond to multi-second. The PG query planner is generally very good, but also very opaque. The statistics collected during an ANALYZE and used by the planner are subject to some significant caveats. Essentially…

That's really my number 1 gripe with PG.

I'm not even too bothered by the opaqueness of the query planner (although I'd love better visibility into it). But the fact that the query plan can change any second is insane: you can't lock it, and you can't force another one as a short-term fix.

There's no option that I know of. If you reach an impossible-to-anticipate threshold and the query plan changes, your whole system can be down and you can only fix forward, which might take a _long_ time to figure out and is super dangerous as you'll pretty much have to experiment on your prod database.

It's insane, I've not yet been bit too bad by it but I know it's coming for me.