Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Index bloat reduced in PostgreSQL v14

cybertec-postgresql.com

Post reply on HN
Print view View original

41–50 of 93 posts

Re: Index bloat reduced in PostgreSQL v14

#41

Post by da_chicken »

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…

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 ended up using a trace flag to use the legacy estimator. They have steadily improved the new estimator and it's no longer a problem, but it goes to show how much is going on under the surface.

Re: Index bloat reduced in PostgreSQL v14

#42

Post by potamic »

srgpqt wrote: post #16

It’s unfortunate pg is unable to maintain large numbers of connections open, necessitating pgbouncer in those setups.

When does a database need to maintain large number of open connections? You really don't need a lot of connections to serve high throughput. The database system typically becomes a bottleneck before the connections do. A good client-side pooling implementation will manage and limit connection usage.

Re: Index bloat reduced in PostgreSQL v14

#43

Post by pas »

jandrewrogers wrote: post #37
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 is robust across a wide range of applications but it does have some architectural sharp edges that can cause serious operational problems in practice if you run into them. Most of these only show up at scale. Only a few do not have any viable workaround in practice. The worst one, in my experience, is that the statistics collector is architecturally broken for some large tables, which can cause the query p…

Even if a full rework or overriding would be hard to implement, saving/loading statistics, or disabling the collector (after it already managed to do a decent run) couldn't be done? Or the nature/representation of the stats don't allow for this? (So they need constant updating, even if badly? But if stats only influence query plans then after good plans are found for the most common queries they could be persisted, right? Am I missing something?)

Re: Index bloat reduced in PostgreSQL v14

#44

Post by rrrrrrrrrrrryan »

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…

> 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 completely unrelated changed someplace else in the database.

One fair criticism of Postgres (and many other open source projects) is that they can be a little too religious about how the thing should work in an ideal world (in this case, SQL being as declarative as possible), sometimes to the detriment of practicality and of making things easier for the business.

Re: Index bloat reduced in PostgreSQL v14

#45

Post by citrons »

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…

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

I'm kinda stuck on it, any ideas what can I do to resolve it?

Re: Index bloat reduced in PostgreSQL v14

#46

Post by da_chicken »

smt88 wrote: post #10
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 don't remember specifics, but that Uber article made it clear that they didn't understand how Postgres worked and made some very basic mistakes. The whole thing made them seem surprisingly incompetent, although the general crappiness of Uber apps maybe should have tipped me off sooner.

That's what I remember, too.

That said, Postgres have had a somewhat common problem with poorly selected defaults.

I remember that the performance tests back over 10 years ago between Postgres (v7-v9) and MySQL (~v5.0) always showed MySQL way ahead. For a long time people assumed the reason was because MyISAM (the then-default) isn't transactional. Except InnoDB was still faster. Okay, but by default MySQL didn't `fsync()` after writes. But it was still faster when you enabled that, too.

Turns out that Postgres's default memory configurations were either largely unchanged from v6 initial releases a decade earlier, or perhaps more accurately they're simply set to make the DB not a resource hog out of the box. Well, outside of the development environment, most people set up a dedicated server, and they want the server to be a resource hog! Most RDBMSs do that automatically. It's an odd choice.

It's been 6-7 years since I used the platform daily, but I would not be surprised if it was still that way. And given the complexity of the memory configuration [0] it's hard to NOT make "basic" mistakes.

[0]: https://www.postgresql.org/docs/current/runtime-config-resou...

Re: Index bloat reduced in PostgreSQL v14

#47

Post by janto »

jandrewrogers wrote: post #37
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 is robust across a wide range of applications but it does have some architectural sharp edges that can cause serious operational problems in practice if you run into them. Most of these only show up at scale. Only a few do not have any viable workaround in practice. The worst one, in my experience, is that the statistics collector is architecturally broken for some large tables, which can cause the query p…

I wonder if the planner can look at stats of full query execution in addition to the table stats. That way it can fall back to a previous plan (or previous table stats) if things get crazy.

Re: Index bloat reduced in PostgreSQL v14

#48

Post by mnahkies »

srgpqt wrote: post #16

It’s unfortunate pg is unable to maintain large numbers of connections open, necessitating pgbouncer in those setups.

Sometimes this is a symptom of an oversized connection pool at the application level. It might be worth monitoring for how many of your connections are actively used Vs idle - you might be surprised

Otherwise I find pgbouncer to be a good solution when it's needed

Re: Index bloat reduced in PostgreSQL v14

#49

Post by adeel_siddiqui »

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…

set `enable_seqscan` = 'off' or set local `enable_seqscan` = 'off'. This will force the pg query planner to use indexes. Experiment with it until you figure out why your query performance deteriorates. Maybe you are doing a lot of updates/deletes? Increase the statistics sampling size? Autovacuum more frequently?

Re: Index bloat reduced in PostgreSQL v14

#50

Post by nyanpasu64 »

da_chicken wrote: post #46
smt88 wrote: post #10

Earlier quoted context omitted.

I don't remember specifics, but that Uber article made it clear that they didn't understand how Postgres worked and made some very basic mistakes. The whole thing made them seem surprisingly incompetent, although the general crappiness of Uber apps maybe should have tipped me off sooner.

That's what I remember, too. That said, Postgres have had a somewhat common problem with poorly selected defaults. I remember that the performance tests back over 10 years ago between Postgres (v7-v9) and MySQL (~v5.0) always showed MySQL way ahead. For a long time people assumed the reason was because MyISAM (the then-default) isn't transactional. Except InnoDB was still faster. Okay, but by default MySQL didn't `fs…

In production workloads, it makes sense for a database to use up all available system resources. If I self-host Nitter on localhost and it runs Redis, or if I install KDE PIM apps which insist on hosting emails and such on a localhost MySQL database, I don't want them to eat all available resources. In my experience, Redis for Nitter, and a barely-used MySQL WordPress database, are quite lightweight in practice.
Post reply on HN