18 ms·

Index bloat reduced in PostgreSQL v14

179 points by petergeoghegan 5y ago

edoceo 5y ago
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)
- xkcd-sucks 5y ago
  Shit the bed worse than Oracle? Not in my experience, unless it's from abuse (storing 1000x 128mb blob per single row, making 100000 simultaneous connections+transactions on a 1-cpu instance, etc). Most of this seems to originate from people treating PG as "magic", which it is....... until it isn't
- jimktrains2 5y ago
  Replication is often talked about as being more difficult than in MySQL.
  - throw0101a 5y ago
    Especially since Galera entered the picture: * https://packages.debian.org/search?keywords=galera https://packages.debian.org/search?keywords=galera * https://galeracluster.com/ https://galeracluster.com/ There's BDR, but it's a commercial product from 2ndQuadrant/EDB: * https://www.2ndquadrant.com/en/resources/postgres-bdr-2ndquadrant/ https://www.2ndquadrant.com/en/resources/postgres-bdr-2ndqua...
- dspillett 5y ago
  Until recent versions CTEs could be very inefficient where they would not be in other databases, though that has now been addressed unless you are running an older release. I'm can't think of any major data loss stories, though there have been one or two "why we switched from pg to <whatever>" ones.
- mchusma 5y ago
  I love postgres, but foreign data wrappers are not really good for much production use in my experience. The query planners need more work, as sometimes they do inconsistent things. The biggest issues is not taking advantage of indices sometimes. Hoping it's solid by version 15 or so.
- jfbaro 5y ago
  It's not a limitation that affects most of users, but lack of official multi master support makes it less suitable for "always on" databases. But to be honest multi master is something hard to get right in ANY database. PostgreSQL is great for many use cases and good enough for many more!
  - gurjeet 5y ago
    Lately I've been getting a lot of notifications for BDR (short for Bi-Directional Replication) from EDB; the term 'Always On' also appears in those notifications, apparently to woo the Oracle audiences. You might want to take a look into that. Disclosures/info: I used to work for EDB. BDR was developed by 2nd Quadrant, which was recently acquired by EDB.
  - eloff 5y ago
    Why would you need multi master to be "always on"?
    - sudhirj 5y ago
      If for always on or High Availability setups you want two DBs running that are in constant communication, with every write being committed on both machines synchronously - so if one of them goes down the other can take its place immediately with no downtime. Postgres has no built in support yet, but tools like STOLON exist.
      - deleted 5y ago
        [deleted]
      - eloff 5y ago
        Postgres doesn't support synchronous replication yet? It's had it since 9.1. I think you're referring to something else.
        pezezin 5y ago
        PostgreSQL has master-slave replication (one read/write master, and multiple read-only standbys), the other users are talking about multi-master replication (all the databases are read/write).
- smt88 5y ago
  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.
  - eloff 5y ago
    In what way did they make basic mistakes?
    - smt88 5y ago
      My DB knowledge is too patchy to give a good summary, but it was extensively discussed on HN several times[1]. In general it seems like they could have solved all of their problems within Postgres, but someone at Uber seemed to just know MySQL better and want to switch. They also made other major changes (like removing schemas from certain data stores) that likely gave them more of an advantage than switching could have. 1. https://news.ycombinator.com/item?id=12166585 https://news.ycombinator.com/item?id=12166585
      - eloff 5y ago
        I remember the article and the discussion, but the fundamental issue seemed to be the difference between MySQL and Postgres indices and the different trade-offs made. Postgres indices are better for read heavy workloads, and MySQL for write heavy. I don't see how this is representative of them not knowing what they are doing. Frankly it's condescending to think you understand their engineering problems better than they do.
  - rattray 5y ago
    Fwiw I personally find their android apps to be among the highest quality of any android app I encounter.
  - Zababa 5y ago
    Speaking about mistakes, do you have any good resources on Postgres to ensure that you don't make the same kind of mistakes?
- mamcx 5y ago
  Well I wish I could embed it as sqlite, but that is like me dreaming too big!
  - ezst 5y ago
    Can't you, though? I think I've seen several apps doing it, making the set-up and administration of the cluster abstracted away and transparent to the user (I can think of KDE's Akonadi suite for instance). That doesn't make the whole DB be contained in a single file, sure, but PG can then serve as the data backend of a versatile lot of applications
    - mamcx 5y ago
      You can pack binaries yourself, but can't use PG as a library like with Sqlite or Firebird.
- gjvc 5y ago
  TXID wraparound protection is still a hazard AFAIK.
- i_have_to_speak 5y ago
  A bunch of Postgres pain points: https://pgdash.io/blog/postgresql-six-not-so-easy-pieces.html https://pgdash.io/blog/postgresql-six-not-so-easy-pieces.htm...
- scott113341 5y ago
  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, the planner would sometimes wildly mis-estimate costs due to under-sampling, and would choose a bad plan. We fixed it in two different ways: 1) lower the auto-ANALYZE threshold; 2) increase the number of rows sampled when collecting statistics for the relevant column. Again, this was “user error”. That said, it will probably happen again on the same or another query, because it’s hard to know if/when a query plan is about to change, and pg_hint_plan and similar are very heavy-handed solutions.
  - da_chicken 5y ago
    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.
    - rrrrrrrrrrrryan 5y ago
      > 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.
- gjs278 5y ago
  master - master replication
- jandrewrogers 5y ago
  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 planner to go haywire. As a consequence, query performance can be erratic, varying by a couple orders of magnitude each time the table is analyzed. There are no workarounds in many cases, and the architectural defect that causes it is decidedly non-trivial to address (otherwise it would have been fixed years ago). This is the only defect that consistently rears its head in operational environments in my experience. As data volumes grow, more Postgres instances are experiencing this problem. At a minimum there needs to be a way to easily override the query planner when it goes insane.
  - pas 5y ago
    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?)
    - pgaddict 5y ago
      I think import/export of stats is pretty doable. Not a tiny amount of work, because of how many stats there may be (regular, extended), but I don't see any obvious major challenges ... Similarly for disabling autoanalyze. We kinda already allow that for autovacuum (ALTER TABLE ... SET ...), so adding autoanalyze_enabled seems possible too. Or you could just set the threshold/scale_factor very high ...
  - janto 5y ago
    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.
- thih9 5y ago
  > (Besides the Uber one) (which is its own long thread) I’m out of the loop; does anyone have a link or could anyone summarize?
  - desdiv 5y ago
    I believe they are referring to this: https://eng.uber.com/postgres-to-mysql-migration/ https://eng.uber.com/postgres-to-mysql-migration/ Though keep in mind that this was back in 2016 and Postgres has changed a lot since then. Previous HN discussions: https://news.ycombinator.com/item?id=17280239 https://news.ycombinator.com/item?id=17280239 https://news.ycombinator.com/item?id=12166585 https://news.ycombinator.com/item?id=12166585 https://news.ycombinator.com/item?id=26283348 https://news.ycombinator.com/item?id=26283348
- ruuda 5y ago
  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.
- naranha 5y ago
  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...)
jfbaro 5y ago
Congratulations to the PostgreSQL community! You guys do an amazing work! Thanks
srgpqt 5y ago
It’s unfortunate pg is unable to maintain large numbers of connections open, necessitating pgbouncer in those setups.
- sojournerc 5y ago
  I find pooling is effective for applying back-pressure on clients. If it can't handle the connections it is likely under-provisioned. Pooling can be managed in client server processes instead of using pg_bouncer
- osigurdson 5y ago
  This appears to be improving as well - especially idle connections. https://pganalyze.com/blog/postgres-14-performance-monitoring https://pganalyze.com/blog/postgres-14-performance-monitorin...
  - jasfi 5y ago
    Postgres is improving rapidly. I started developing my own DB a few years ago, but decided to do something else. One of the main reasons was because of the progress this DB is making on all fronts.
  - fulafel 5y ago
    Interesting that the graph goes up to 10k connections without any cliff in throughput, I wonder what use cases people have beyond that. Or maybe the issue with idling connections is memory usage?
    - pgaddict 5y ago
      Yeah, the absence of a sudden cliff is very nice. I think we fixed the main causes back in ~9.5. But the gradient at the end is pretty clear, and even at 10k the throughput is already less than 50% of the max. And it's dropping faster and faster. It's also important to keep in mind this is an extremely simple workload, essentially just read-only point queries, on about 3GB of data.
- 5y ago

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)

xkcd-sucks 5y ago
Shit the bed worse than Oracle? Not in my experience, unless it's from abuse (storing 1000x 128mb blob per single row, making 100000 simultaneous connections+transactions on a 1-cpu instance, etc). Most of this seems to originate from people treating PG as "magic", which it is....... until it isn't
jimktrains2 5y ago
Replication is often talked about as being more difficult than in MySQL.
- throw0101a 5y ago
  Especially since Galera entered the picture: * https://packages.debian.org/search?keywords=galera https://packages.debian.org/search?keywords=galera * https://galeracluster.com/ https://galeracluster.com/ There's BDR, but it's a commercial product from 2ndQuadrant/EDB: * https://www.2ndquadrant.com/en/resources/postgres-bdr-2ndquadrant/ https://www.2ndquadrant.com/en/resources/postgres-bdr-2ndqua...
dspillett 5y ago
Until recent versions CTEs could be very inefficient where they would not be in other databases, though that has now been addressed unless you are running an older release. I'm can't think of any major data loss stories, though there have been one or two "why we switched from pg to <whatever>" ones.
mchusma 5y ago
I love postgres, but foreign data wrappers are not really good for much production use in my experience. The query planners need more work, as sometimes they do inconsistent things. The biggest issues is not taking advantage of indices sometimes. Hoping it's solid by version 15 or so.
jfbaro 5y ago
It's not a limitation that affects most of users, but lack of official multi master support makes it less suitable for "always on" databases. But to be honest multi master is something hard to get right in ANY database. PostgreSQL is great for many use cases and good enough for many more!
- gurjeet 5y ago
  Lately I've been getting a lot of notifications for BDR (short for Bi-Directional Replication) from EDB; the term 'Always On' also appears in those notifications, apparently to woo the Oracle audiences. You might want to take a look into that. Disclosures/info: I used to work for EDB. BDR was developed by 2nd Quadrant, which was recently acquired by EDB.
- eloff 5y ago
  Why would you need multi master to be "always on"?
  - sudhirj 5y ago
    If for always on or High Availability setups you want two DBs running that are in constant communication, with every write being committed on both machines synchronously - so if one of them goes down the other can take its place immediately with no downtime. Postgres has no built in support yet, but tools like STOLON exist.
    - deleted 5y ago
      [deleted]
    - eloff 5y ago
      Postgres doesn't support synchronous replication yet? It's had it since 9.1. I think you're referring to something else.
      - pezezin 5y ago
        PostgreSQL has master-slave replication (one read/write master, and multiple read-only standbys), the other users are talking about multi-master replication (all the databases are read/write).
smt88 5y ago
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.
- eloff 5y ago
  In what way did they make basic mistakes?
  - smt88 5y ago
    My DB knowledge is too patchy to give a good summary, but it was extensively discussed on HN several times[1]. In general it seems like they could have solved all of their problems within Postgres, but someone at Uber seemed to just know MySQL better and want to switch. They also made other major changes (like removing schemas from certain data stores) that likely gave them more of an advantage than switching could have. 1. https://news.ycombinator.com/item?id=12166585 https://news.ycombinator.com/item?id=12166585
    - eloff 5y ago
      I remember the article and the discussion, but the fundamental issue seemed to be the difference between MySQL and Postgres indices and the different trade-offs made. Postgres indices are better for read heavy workloads, and MySQL for write heavy. I don't see how this is representative of them not knowing what they are doing. Frankly it's condescending to think you understand their engineering problems better than they do.
- rattray 5y ago
  Fwiw I personally find their android apps to be among the highest quality of any android app I encounter.
- Zababa 5y ago
  Speaking about mistakes, do you have any good resources on Postgres to ensure that you don't make the same kind of mistakes?
mamcx 5y ago
Well I wish I could embed it as sqlite, but that is like me dreaming too big!
- ezst 5y ago
  Can't you, though? I think I've seen several apps doing it, making the set-up and administration of the cluster abstracted away and transparent to the user (I can think of KDE's Akonadi suite for instance). That doesn't make the whole DB be contained in a single file, sure, but PG can then serve as the data backend of a versatile lot of applications
  - mamcx 5y ago
    You can pack binaries yourself, but can't use PG as a library like with Sqlite or Firebird.
gjvc 5y ago
TXID wraparound protection is still a hazard AFAIK.
i_have_to_speak 5y ago
A bunch of Postgres pain points: https://pgdash.io/blog/postgresql-six-not-so-easy-pieces.html https://pgdash.io/blog/postgresql-six-not-so-easy-pieces.htm...
scott113341 5y ago
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, the planner would sometimes wildly mis-estimate costs due to under-sampling, and would choose a bad plan. We fixed it in two different ways: 1) lower the auto-ANALYZE threshold; 2) increase the number of rows sampled when collecting statistics for the relevant column. Again, this was “user error”. That said, it will probably happen again on the same or another query, because it’s hard to know if/when a query plan is about to change, and pg_hint_plan and similar are very heavy-handed solutions.
- da_chicken 5y ago
  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.
  - rrrrrrrrrrrryan 5y ago
    > 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.
gjs278 5y ago
master - master replication
jandrewrogers 5y ago
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 planner to go haywire. As a consequence, query performance can be erratic, varying by a couple orders of magnitude each time the table is analyzed. There are no workarounds in many cases, and the architectural defect that causes it is decidedly non-trivial to address (otherwise it would have been fixed years ago). This is the only defect that consistently rears its head in operational environments in my experience. As data volumes grow, more Postgres instances are experiencing this problem. At a minimum there needs to be a way to easily override the query planner when it goes insane.
- pas 5y ago
  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?)
  - pgaddict 5y ago
    I think import/export of stats is pretty doable. Not a tiny amount of work, because of how many stats there may be (regular, extended), but I don't see any obvious major challenges ... Similarly for disabling autoanalyze. We kinda already allow that for autovacuum (ALTER TABLE ... SET ...), so adding autoanalyze_enabled seems possible too. Or you could just set the threshold/scale_factor very high ...
- janto 5y ago
  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.
thih9 5y ago
> (Besides the Uber one) (which is its own long thread) I’m out of the loop; does anyone have a link or could anyone summarize?
- desdiv 5y ago
  I believe they are referring to this: https://eng.uber.com/postgres-to-mysql-migration/ https://eng.uber.com/postgres-to-mysql-migration/ Though keep in mind that this was back in 2016 and Postgres has changed a lot since then. Previous HN discussions: https://news.ycombinator.com/item?id=17280239 https://news.ycombinator.com/item?id=17280239 https://news.ycombinator.com/item?id=12166585 https://news.ycombinator.com/item?id=12166585 https://news.ycombinator.com/item?id=26283348 https://news.ycombinator.com/item?id=26283348
ruuda 5y ago
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.
naranha 5y ago
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...)

jfbaro 5y ago

Congratulations to the PostgreSQL community! You guys do an amazing work! Thanks

srgpqt 5y ago

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

sojournerc 5y ago
I find pooling is effective for applying back-pressure on clients. If it can't handle the connections it is likely under-provisioned. Pooling can be managed in client server processes instead of using pg_bouncer
osigurdson 5y ago
This appears to be improving as well - especially idle connections. https://pganalyze.com/blog/postgres-14-performance-monitoring https://pganalyze.com/blog/postgres-14-performance-monitorin...
- jasfi 5y ago
  Postgres is improving rapidly. I started developing my own DB a few years ago, but decided to do something else. One of the main reasons was because of the progress this DB is making on all fronts.
- fulafel 5y ago
  Interesting that the graph goes up to 10k connections without any cliff in throughput, I wonder what use cases people have beyond that. Or maybe the issue with idling connections is memory usage?
  - pgaddict 5y ago
    Yeah, the absence of a sudden cliff is very nice. I think we fixed the main causes back in ~9.5. But the gradient at the end is pretty clear, and even at 10k the throughput is already less than 50% of the max. And it's dropping faster and faster. It's also important to keep in mind this is an extremely simple workload, essentially just read-only point queries, on about 3GB of data.
5y ago