7 ms·

Things we learned running Postgres 13

377 points by lfittl 6y ago

minxomat 6y ago
As with any Postgres update, I recommend Noriyoshi Shinoda‘s super detailed change log [PDF] with examples as a reference for DBAs looking to upgrade. This version it’s 81 pages (so far): https://www.hpe.com/content/dam/hpe/download/pdf/japan/linux/postgresql13beta1-new-features-en-20200527-1.pdf https://www.hpe.com/content/dam/hpe/download/pdf/japan/linux...
- blu_ 6y ago
  Wow, thanks for sharing this!
- artimaeis 6y ago
  Is there a list of these somewhere? I can find a few editions on search engines, just curious if there's a more canonical list. And thanks for sharing, this is a great resource!
  - petergeoghegan 6y ago
    https://www.postgresql.org/docs/13/release-13.html https://www.postgresql.org/docs/13/release-13.html
  - minxomat 6y ago
    I've only started reading them back when PG11 was in beta and found them mostly by googling. Here's what I can find: - PG 9.6: https://www.slideshare.net/noriyoshishinoda/postgresql-96-new-features-with-examples https://www.slideshare.net/noriyoshishinoda/postgresql-96-ne... - PG 10: https://www.hpe.com/content/dam/hpe/download/pdf/japan/linux/support/lcc/PostgreSQL_10_New_Features_en_20170522-1.pdf https://www.hpe.com/content/dam/hpe/download/pdf/japan/linux... - PG 11: https://h50146.www5.hpe.com/products/software/oe/linux/mainstream////support/lcc/pdf/PostgreSQL_11_New_Features_beta1_en_20180525-1.pdf https://h50146.www5.hpe.com/products/software/oe/linux/mains... - PG 12: https://h50146.www5.hpe.com/products/software/oe/linux/mainstream/support/lcc/pdf/PostgreSQL_12_GA_New_Features_en_20191011-1.pdf https://h50146.www5.hpe.com/products/software/oe/linux/mains... There are more, but those are in Japanese.
- gigatexal 6y ago
  This is a treasure. Thank you for sharing.
systemvoltage 6y ago
It would be great if we stop adding features now. Are there any pieces of software that has started doing this? Why are we perpetually in this loop of feature creep? Like a painter, you must know when to stop painting. This is a real dilemma all painters face, especially when dealing with watercolor. If anyone has tried painting here, you know exactly the problem - how do you know you're done? It applies to writing and music as well, but it is more evident in visual arts than any other field. I wish there was a Postgres branch that took previous version and then just applied optimizations and bugfixes. No more.
- blu_ 6y ago
  [Insert default "its OSS, just create a fork" here]
- citruspi 6y ago
  > It would be great if we stop adding features now... I wish there was a Postgres branch that took previous version and then just applied optimizations and bugfixes. No more. To be fair, quoting from the article: > There are no big new features in Postgres 13, but there are a lot of small but important incremental improvements. Let's take a look. But also, in general, yes there are pieces of software that do this - most recently Moment.js[1]. There was some discussion earlier this week[2]. [1] https://momentjs.com/docs/#/-project-status/ https://momentjs.com/docs/#/-project-status/ [2] https://news.ycombinator.com/item?id=24477941 https://news.ycombinator.com/item?id=24477941
  - chrisweekly 6y ago
    It's unfortunate that performance is so rarely considered to be a feature.
    - marcosdumay 6y ago
      Postgres usually adds plenty of performance from one version to another. Not much on .0 versions, those are more feature packed, but all the other time.
mastazi 6y ago
There is one thing I'm curious about and never found a good answer: why there is no VACUUM equivalent in MySQL or MS SQL? I guess VACUUM is necessary because of the way Postgres stores data? How do those other RDBMS clean up indexes?
- luhn 6y ago
  When you update a row in a SQL database, you need to keep the old row around until the transaction is committed. In Postgres, a new row is written, and both the new and old coexist in the table. Even after the transaction is committed, the old row stays in the table. VACUUM scans the table, finds rows that are no longer needed, and marks them as okay to overwrite. In MySQL, the row is overwritten in the table immediately, and then the old row is temporarily written to a separate area (the UNDO Segment), so the old record is available if needed. Since old rows don't accumulate in the main table, there's no need for VACUUM.
  - mastazi 6y ago
    Thank you for your answer, this makes sense! I guess that there must be some tradeoff, right? What I mean is that while in MySQL you don't need to vacuum, perhaps updates would be slightly slower (due to the need to write into the UNDO segment)?
    - gshulegaard 6y ago
      I think this might provide more context which you are looking for (and your suspicion seems spot on!): > This means more writes to do on updates and makes access to old row versions quite a lot slower, but gets rid of the need for asynchronous vacuum and means you don't have table bloat issues. Instead you can have huge rollback segments or run out of space for rollback. https://stackoverflow.com/questions/25153532/why-is-it-a-vacuum-not-needed-with-mysql-compared-to-the-postgresql https://stackoverflow.com/questions/25153532/why-is-it-a-vac... Edit: Just wanted to explicitly call out that the Robert Haas article linked in the answer is excellent (I am still reading my way through it).
    - 6y ago
gigatexal 6y ago
The best change imo is the efforts to verify backups now with the pg_verifybackup command. https://www.postgresql.org/docs/13/app-pgverifybackup.html https://www.postgresql.org/docs/13/app-pgverifybackup.html
jakeogh 6y ago
line 24 takes 5m on 155M records, it's not a pg problem, how can it be made faster? https://github.com/jakeogh/pubchemmer/blame/master/README.md https://github.com/jakeogh/pubchemmer/blame/master/README.md
nnain 6y ago
[Off Topic] Postgres 13 doesn't work with the PSequel client; PSequel is no longer developed and that kinda sucks.

377 points by lfittl 6y ago

As with any Postgres update, I recommend Noriyoshi Shinoda‘s super detailed change log [PDF] with examples as a reference for DBAs looking to upgrade. This version it’s 81 pages (so far): https://www.hpe.com/content/dam/hpe/download/pdf/japan/linux/postgresql13beta1-new-features-en-20200527-1.pdf https://www.hpe.com/content/dam/hpe/download/pdf/japan/linux...

blu_ 6y ago
Wow, thanks for sharing this!
artimaeis 6y ago
Is there a list of these somewhere? I can find a few editions on search engines, just curious if there's a more canonical list. And thanks for sharing, this is a great resource!
- petergeoghegan 6y ago
  https://www.postgresql.org/docs/13/release-13.html https://www.postgresql.org/docs/13/release-13.html
- minxomat 6y ago
  I've only started reading them back when PG11 was in beta and found them mostly by googling. Here's what I can find: - PG 9.6: https://www.slideshare.net/noriyoshishinoda/postgresql-96-new-features-with-examples https://www.slideshare.net/noriyoshishinoda/postgresql-96-ne... - PG 10: https://www.hpe.com/content/dam/hpe/download/pdf/japan/linux/support/lcc/PostgreSQL_10_New_Features_en_20170522-1.pdf https://www.hpe.com/content/dam/hpe/download/pdf/japan/linux... - PG 11: https://h50146.www5.hpe.com/products/software/oe/linux/mainstream////support/lcc/pdf/PostgreSQL_11_New_Features_beta1_en_20180525-1.pdf https://h50146.www5.hpe.com/products/software/oe/linux/mains... - PG 12: https://h50146.www5.hpe.com/products/software/oe/linux/mainstream/support/lcc/pdf/PostgreSQL_12_GA_New_Features_en_20191011-1.pdf https://h50146.www5.hpe.com/products/software/oe/linux/mains... There are more, but those are in Japanese.
gigatexal 6y ago
This is a treasure. Thank you for sharing.

systemvoltage 6y ago

It would be great if we stop adding features now. Are there any pieces of software that has started doing this? Why are we perpetually in this loop of feature creep? Like a painter, you must know when to stop painting. This is a real dilemma all painters face, especially when dealing with watercolor. If anyone has tried painting here, you know exactly the problem - how do you know you're done? It applies to writing and music as well, but it is more evident in visual arts than any other field. I wish there was a Postgres branch that took previous version and then just applied optimizations and bugfixes. No more.

blu_ 6y ago
[Insert default "its OSS, just create a fork" here]
citruspi 6y ago
> It would be great if we stop adding features now... I wish there was a Postgres branch that took previous version and then just applied optimizations and bugfixes. No more. To be fair, quoting from the article: > There are no big new features in Postgres 13, but there are a lot of small but important incremental improvements. Let's take a look. But also, in general, yes there are pieces of software that do this - most recently Moment.js[1]. There was some discussion earlier this week[2]. [1] https://momentjs.com/docs/#/-project-status/ https://momentjs.com/docs/#/-project-status/ [2] https://news.ycombinator.com/item?id=24477941 https://news.ycombinator.com/item?id=24477941
- chrisweekly 6y ago
  It's unfortunate that performance is so rarely considered to be a feature.
  - marcosdumay 6y ago
    Postgres usually adds plenty of performance from one version to another. Not much on .0 versions, those are more feature packed, but all the other time.

mastazi 6y ago

There is one thing I'm curious about and never found a good answer: why there is no VACUUM equivalent in MySQL or MS SQL? I guess VACUUM is necessary because of the way Postgres stores data? How do those other RDBMS clean up indexes?

luhn 6y ago
When you update a row in a SQL database, you need to keep the old row around until the transaction is committed. In Postgres, a new row is written, and both the new and old coexist in the table. Even after the transaction is committed, the old row stays in the table. VACUUM scans the table, finds rows that are no longer needed, and marks them as okay to overwrite. In MySQL, the row is overwritten in the table immediately, and then the old row is temporarily written to a separate area (the UNDO Segment), so the old record is available if needed. Since old rows don't accumulate in the main table, there's no need for VACUUM.
- mastazi 6y ago
  Thank you for your answer, this makes sense! I guess that there must be some tradeoff, right? What I mean is that while in MySQL you don't need to vacuum, perhaps updates would be slightly slower (due to the need to write into the UNDO segment)?
  - gshulegaard 6y ago
    I think this might provide more context which you are looking for (and your suspicion seems spot on!): > This means more writes to do on updates and makes access to old row versions quite a lot slower, but gets rid of the need for asynchronous vacuum and means you don't have table bloat issues. Instead you can have huge rollback segments or run out of space for rollback. https://stackoverflow.com/questions/25153532/why-is-it-a-vacuum-not-needed-with-mysql-compared-to-the-postgresql https://stackoverflow.com/questions/25153532/why-is-it-a-vac... Edit: Just wanted to explicitly call out that the Robert Haas article linked in the answer is excellent (I am still reading my way through it).
  - 6y ago

gigatexal 6y ago

The best change imo is the efforts to verify backups now with the pg_verifybackup command. https://www.postgresql.org/docs/13/app-pgverifybackup.html https://www.postgresql.org/docs/13/app-pgverifybackup.html

jakeogh 6y ago

line 24 takes 5m on 155M records, it's not a pg problem, how can it be made faster? https://github.com/jakeogh/pubchemmer/blame/master/README.md https://github.com/jakeogh/pubchemmer/blame/master/README.md

nnain 6y ago

[Off Topic] Postgres 13 doesn't work with the PSequel client; PSequel is no longer developed and that kinda sucks.