13 ms·

PostgreSQL 13

563 points by jkatz05 6y ago

koolba 6y ago
Link to release notes for v13: https://www.postgresql.org/docs/13/release-13.html https://www.postgresql.org/docs/13/release-13.html A lot of performance related improvements. My favorite is B-Tree Deduplication - https://www.postgresql.org/docs/13/btree-implementation.html#BTREE-DEDUPLICATION https://www.postgresql.org/docs/13/btree-implementation.html...
- spennant 6y ago
  Great release! Still waiting for zedstore though.
jfbaro 6y ago
Thanks to the community. Greeat and powerful new features.
diminish 6y ago
PostgreSQL is awesome with a quite a fast release cycle for a project, that complicated.
qatanah 6y ago
Great work postgres team!
theandrewbailey 6y ago
> To make it easier to take advantage of PostgreSQL’s extensibility, PostgreSQL 13 adds the concept of a "trusted extension," which allows database users to install extensions that a superuser marks as "trusted." Certain built-in extensions are marked as trusted by default, including pgcrypto, tablefunc, hstore, and more. Wow, that should make setting up a new installation easier. It looks like trigrams are marked as trusted (my favorite extension). I think this is the full list: https://blog.dbi-services.com/postgresql-13-trusted-extensions/ https://blog.dbi-services.com/postgresql-13-trusted-extensio...
- derefr 6y ago
  Yep. Every time I've tried to run pg_restore as a non-superuser (e.g. against a DBaaS instance, where only the cloud-host's ops staff has true superuser privileges), the inclusion of CREATE EXTENSION commands in the restore stream blocks the whole process. IIRC they added a specific flag in pg_restore to skip CREATE EXTENSION calls, just so people could do this. Now – given that in DBaaS environments, "trusted" extensions would be the only ones exposed to the user anyway — you'll likely be able to pg_restore as an arbitrary user in these envs with ease! But that's not the only gain from this. The need to be a superuser to CREATE EXTENSION was the last thing standing in the way of many SQL client applications initializing their own schema objects (i.e. running DDL migrations) as a limited user (e.g. one with DDL privileges limited to a particular schema.) ----- This is the first time that I feel PG is maybe approaching the point where users can truly "do their own thing" within the DB, without a superuser-privileged account interceding to help them along. As such, PG13 will be a big boon for DB data security... a few versions from now, when it's old enough to be mandated as the minimum requirement for these stacks :) Right now, apps are either deployed to use a PG user with ~full privileges over the DB (even if it's not technically a superuser), where migrations are run automatically on runtime startup, as the same account that accesses data at runtime (exploit vector!); or apps are deployed to use a DML-only user in prod, but migrations get run manually by ops staff under a separate, fully-privileged user/superuser. I bet that, once people start to depend on this change, we'll start to see applications that offer the ability to configure them with one migration-time DB user, and a separate runtime DB user; neither of which are fully-privileged; and where the migration-running happens in a separate context (e.g. as a sidecar container) designed to allow migrations to be run automatically, while isolating the migration-time DB creds from the runtime environment.
- claytonjy 6y ago
  This is great; no more need for pgextwlist then? Would have saved me a day or two quite recently...
merqurio 6y ago
I heard someone say the other day that they would skip this version and wait for the next one. Just because of the version number. I found it somewhat sad and very poor judgment, but after thinking about it on my way to home, I think a lot of people will do the same. It would be interesting to keep an eye on the number of downloads from containers and packaging systems to see if this is the case.
- throwaway8941 6y ago
  Hopefully Chinese (and other non-Western) users will help to counterbalance this lunacy, if it does happen. Also, I see a lot of container build files not being pinned to a specific version, which automatically implies "latest".
  - mrmonkeyman 6y ago
    They have a problem with 4 though
  - seized 6y ago
    Many of those users have equal or greater lunancy just with different numbers... Including buying or not buying specific houses due to the address or physical layout etc etc.
  - Yetanfou 6y ago
    I don't know why mrmonkeyman's reply on Chinese having something against the number 4 was downvoted into oblivion although I suspect it was because of the all-to-common knee-jerk reaction against 'racism'. What he said is true, 4 is the most unlucky number in Chinese because it sounds like the word for 'death'. Buildings in China often don’t have a 4th floor, some of them skip all floor numbers which contain '4' so they don't have a 14th or 24th etc. floor. The number 13 has also gained an ominous reputation in China so it is unlikely that help will arrive from China to combat any 13-related superstitions. PostgreSQL v8, v8.8 or (in the far far future) v88 will be popular though since 8 is the most lucky number - it sounds like the word for 'wealth'.
eatonphil 6y ago
One cool feature is LIMIT WITH TIES so that if there are more rows with the same value sorted on by what would have been the last row, it shows all these rows of the same value.
strikelaserclaw 6y ago
The postgresql team is a sight to behold, people from all over the world making amazing contributions to the project.
aargh_aargh 6y ago
Some articles about new PostgreSQL 13 features: [1] https://info.crunchydata.com/blog/why-postgresql-13-is-a-lucky-release https://info.crunchydata.com/blog/why-postgresql-13-is-a-luc... [2] https://info.crunchydata.com/blog/postgresql-13-hidden-gems https://info.crunchydata.com/blog/postgresql-13-hidden-gems [3] https://www.2ndquadrant.com/en/blog/postgresql-13-limit-with-ties/ https://www.2ndquadrant.com/en/blog/postgresql-13-limit-with... [4] https://www.2ndquadrant.com/en/blog/pg13-slot-size-limit/ https://www.2ndquadrant.com/en/blog/pg13-slot-size-limit/ [5] https://www.2ndquadrant.com/en/blog/unicode-normalization-in-postgresql-13/ https://www.2ndquadrant.com/en/blog/unicode-normalization-in... [6] https://www.2ndquadrant.com/en/blog/backup-manifests-and-pg_verifybackup-in-postgresql-13/ https://www.2ndquadrant.com/en/blog/backup-manifests-and-pg_... [7] https://amitkapila16.blogspot.com/2020/05/improved-autovacuum-in-postgresql-13.html https://amitkapila16.blogspot.com/2020/05/improved-autovacuu...
- ainar-g 6y ago
  Non-extension get_random_uuid() and the Unicode normalisation stuff are great, love it!
  - Dominisi 6y ago
    Stop, I can only get so erect.
  - adelarsq 6y ago
    Just for note, its `gen_random_uuid ()` Thanks for the tip.
jeltz 6y ago
My favorite feature in this release is probably incremental sorts. It allows for narrower indexes in certain applications.
arconis987 6y ago
This is fantastic! Love the parallel vacuum feature, which should help my team’s sanity with a write-heavy workload. Does anyone know when the zheap storage system will be released? This system will apparently obviate the need for vacuums all together since old row version information will be kept in an undo log instead of in the heap. This will be a game changer for us.
- petereisentraut 6y ago
  It's a bit more complicated than that. You will also notice another release note item in PG13 that says "Allow inserts, not only updates and deletes, to trigger vacuuming activity in autovacuum", which is because vacuum is not only about cleaning up deleted or rolled-back rows, but also transaction ID wraparound. Making that go away is also a dream of many, but it's a different (or additional) project than a new storage system.
kriro 6y ago
For me, this project is the biggest FLOSS success story after Linux, xBSD and the GNU stuff. Great quality, amazing team. The best RDBMS that exists today and that includes commercial ones (imo). Thank you.
- VWWHFSfQ 6y ago
  I think MySQL is still significantly more widely deployed. And Postresql wouldn't be ahead of things like Firefox, apache, etc in terms of successful FLOSS. The world wouldn't really be that much different if postgres didn't exist. But it would be significantly worse if Firefox or apache never existed
  - spacemanmatt 6y ago
    I think that is a rather naive take on the scope of PostgreSQL in "invisible" services you depend on regularly
    - VWWHFSfQ 6y ago
      can be swapped out for another floss database pretty easy without any real impact. internet in general would be significantly worse right now if FF or apache never took off
      - jimktrains2 6y ago
        Spoken by someone who has never used any db specific functionality to facilitate simplifying logic and constraints.
      - pronik 6y ago
        I wish you the best of luck replacing PostGIS with just about anything else.
      - spacemanmatt 6y ago
        [laugh react]
Thaxll 6y ago
How do people keep track on all the features that are added in RDMS? When you know the basic stuff like SELECT / JOIN / indexes ect ... how do you stay relevent with new things added?
- bhahn 6y ago
  Not a direct answer to your question, but there are two ways that I usually learn about new Postgres features. 1. Through blog posts like this 2. Needing something in the moment and googling. To elaborate on #2, I learned about partial indices when I was at a situation at work when I needed to optimize indices for a heavily used table. Google eventually brought me to their excellent docs and SO posts that mentioned them.
- VPenkov 6y ago
  I work for a fintech company and I'm not much of a SQL guy but I often hear how we've reached some limit of what PostgreSQL can do, and every update is a huge deal, even the patch versions. So I guess it's tech like any other. The more you know and use, the more you're aware of the recent developments. Like pretend you know enough javascript to build small promotional websites with some animations as you scroll down the page. You probably don't care, and are not aware, of the recent developments around optional chaining operators.
- jkatz05 6y ago
  The PostgreSQL community tries to keep a list of new features that are added in its feature matrix: https://www.postgresql.org/about/featurematrix/ https://www.postgresql.org/about/featurematrix/ This is not an exhaustive list, but it contains a lot. You would still have to cull information from release notes: https://www.postgresql.org/docs/release/ https://www.postgresql.org/docs/release/ Or looking at the documentation page for a command (e.g. SELECT), or as per other suggestions, reading blogs/articles.
- castorp 6y ago
  > How do people keep track on all the features that are added in RDMS? By reading the release notes
- 6y ago
ruairispain 6y ago
I used to be a big Oracle fan in the late 1990s, but now can't see the point in paying big bucks for Oracle DB. Are there any legitimate reasons to pick Oracle over Postgres for a new project? Apart from "support"
- mritchie712 6y ago
  Pretty easy to find "support" for Postgres too and more convenient. e.g. submit a request on CodeMentor and you'll have you several solid devs willing to help you, live, in a matter of minutes.
- eloff 6y ago
  * cricket sounds
- briffle 6y ago
  At my last job, I ran about 50 servers running Oracle Standard Edition. (Enterprise Edition would have taken each $25k license to something like $600k for our dual core servers). I was Sysadmin, not a developer, so I have different pain points. I will never look back. There were so many things that we wanted to do, but were 'enterprise only' features. Like rebuild/create indexes online. We had a DB with a 1TB table. rebuilding that, while locking the table, would mean something like a 14 hour outage. With postgresql, online index creation is right there. Oracle didn't support a proper incremental backup, except in enterprise edition. (and RMAN was a pain in the ass of arcane commands and black magic). pgBarman makes backups so simple for us. Standby's on Oracle are good for disaster recovery. But you have to manually ship the logs, apply them, monitor it, and delete old logs. There were multiple thousand line korn shell scripts we utilized, and lots of cronjobs. With Postgresql, its stream based, and my standby follows production by less than 100ms most of the time. Plus, I can connect to the standby, and run read-only queries on it (we run large reports on standby, so we don't affect production) That is also possible in Oracle, with 'enterprise' edition (data-gaurd). We filed a few ticket with Oracle Support. We never got any answers. I don't think we ever got an support person at Oracle that had been there more than a year. Even when we escalated.
- deleted 6y ago
  [deleted]
shay_ker 6y ago
Are there any PG providers that allow you to do a seamless upgrade, from, say, 12 -> 13? I know logical replication allows you to do this, but with some caveats. Curious what other people's experience is.
- contrahax 6y ago
  Aiven does this and usually has the updates available within two weeks of release. We've gone 10 -> 11 -> 12 seamlessly (and fully online). GCP also has this, but it usually takes them a year or two to have newer versions of PG (and by that time they are already outdated).
  - dwwoelfel 6y ago
    > GCP also has this Are you saying that GCP has seamless upgrades? Any link to a doc? The only one I can find has you dump the database and restore it: https://cloud.google.com/sql/docs/postgres/upgrade-db https://cloud.google.com/sql/docs/postgres/upgrade-db
- dragonwriter 6y ago
  AWS supports relatively seamless (but not automatic) major version upgrades, though because the database engine may have backward compatibility issues they recommend (but don't require) you do a lot of steps which make it less-than-seamless but safer. They don't try to automatically handle breaking changes for you. https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/USER_UpgradeDBInstance.PostgreSQL.html https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide...
- Graphguy 6y ago
  IBM Cloud lets you upgrade major versions through Read Replicas and also do dry runs, https://cloud.ibm.com/docs/databases-for-postgresql?topic=databases-for-postgresql-upgrading#upgrading-from-a-read-only-replica https://cloud.ibm.com/docs/databases-for-postgresql?topic=da...
gonzus 6y ago
There are a few projects I absolutely could not live a professional life without: PostgreSQL is one, SQLite is another. All my appreciation and congratulations on a great release!
aplummer 6y ago
Could someone please recommend the best up to date 0 to confident Postgres book?
- samgranieri 6y ago
  In all honesty, you don't need a book on postgres. They have excellent documentation on their site
- tuatoru 6y ago
  You don't go from zero to mastery in one book. Three years minimum, double or triple that if you're doing other stuff as well. To start, Stepháne Faroult's SQL Success will give you a solid grounding in using RDBMSes as an application programmer. It's not Postgres specific, but the learning you need to do isn't tied to one vendor. But as sibling said, you'll be using the documentation as well. PostgreSQL has documentation that is nearly up to the standards that hardware engineers expect and rely on.
  - tuatoru 6y ago
    Adding to the above, Schönig's Mastering PostgreSQL 11 will help you advance from intermediate to high-intermediate PostgreSQL programmer, as will Faroult's The Art of SQL. Karwin's SQL Antipatterns is useful supplementary reading also. I've looked through my kindle, and I wouldn't bother with much else.
- furstenheim 6y ago
  This one was super useful for me https://www.amazon.es/PostgreSQL-High-Performance-Gregory-Smith/dp/184951030X https://www.amazon.es/PostgreSQL-High-Performance-Gregory-Sm... I think that they have a newer edition for postgres 10
- Spiritus 6y ago
  Not sure about “0” but https://theartofpostgresql.com https://theartofpostgresql.com is a good book.
adamch 6y ago
Postgres has great docs. It's one of the big reasons I love it so much. Very comprehensive, although I usually skip straight to the examples.
blahyawnblah 6y ago
I don't have really any experience with PostgreSQL. Everything I've heard says it's awesome. What makes it so good?
ngrilly 6y ago
Awesome release, as always. Kudos to the team. Anyone knows if the new B-Tree deduplication will solve the lack of "index skip scan" in PostgreSQL? https://wiki.postgresql.org/wiki/Loose_indexscan https://wiki.postgresql.org/wiki/Loose_indexscan
didip 6y ago
If I can hijack this thread to make a plea of my, very short, wishlist: * Multi master reads and writes. * Much better connection handlers, it's silly to always install pgbouncer every time I need to use PostgreSQL. With these 2 features, PostgreSQL would grow to the next level. It already has a lot of SQL features. Now it needs operational features.
mekster 6y ago
How do people overcome the limitation that you cannot reorder the columns in PostgreSQL?

563 points by jkatz05 6y ago

Link to release notes for v13: https://www.postgresql.org/docs/13/release-13.html https://www.postgresql.org/docs/13/release-13.html A lot of performance related improvements. My favorite is B-Tree Deduplication - https://www.postgresql.org/docs/13/btree-implementation.html#BTREE-DEDUPLICATION https://www.postgresql.org/docs/13/btree-implementation.html...

spennant 6y ago
Great release! Still waiting for zedstore though.

jfbaro 6y ago

Thanks to the community. Greeat and powerful new features.

diminish 6y ago

PostgreSQL is awesome with a quite a fast release cycle for a project, that complicated.

qatanah 6y ago

Great work postgres team!

theandrewbailey 6y ago

> To make it easier to take advantage of PostgreSQL’s extensibility, PostgreSQL 13 adds the concept of a "trusted extension," which allows database users to install extensions that a superuser marks as "trusted." Certain built-in extensions are marked as trusted by default, including pgcrypto, tablefunc, hstore, and more. Wow, that should make setting up a new installation easier. It looks like trigrams are marked as trusted (my favorite extension). I think this is the full list: https://blog.dbi-services.com/postgresql-13-trusted-extensions/ https://blog.dbi-services.com/postgresql-13-trusted-extensio...

derefr 6y ago
Yep. Every time I've tried to run pg_restore as a non-superuser (e.g. against a DBaaS instance, where only the cloud-host's ops staff has true superuser privileges), the inclusion of CREATE EXTENSION commands in the restore stream blocks the whole process. IIRC they added a specific flag in pg_restore to skip CREATE EXTENSION calls, just so people could do this. Now – given that in DBaaS environments, "trusted" extensions would be the only ones exposed to the user anyway — you'll likely be able to pg_restore as an arbitrary user in these envs with ease! But that's not the only gain from this. The need to be a superuser to CREATE EXTENSION was the last thing standing in the way of many SQL client applications initializing their own schema objects (i.e. running DDL migrations) as a limited user (e.g. one with DDL privileges limited to a particular schema.) ----- This is the first time that I feel PG is maybe approaching the point where users can truly "do their own thing" within the DB, without a superuser-privileged account interceding to help them along. As such, PG13 will be a big boon for DB data security... a few versions from now, when it's old enough to be mandated as the minimum requirement for these stacks :) Right now, apps are either deployed to use a PG user with ~full privileges over the DB (even if it's not technically a superuser), where migrations are run automatically on runtime startup, as the same account that accesses data at runtime (exploit vector!); or apps are deployed to use a DML-only user in prod, but migrations get run manually by ops staff under a separate, fully-privileged user/superuser. I bet that, once people start to depend on this change, we'll start to see applications that offer the ability to configure them with one migration-time DB user, and a separate runtime DB user; neither of which are fully-privileged; and where the migration-running happens in a separate context (e.g. as a sidecar container) designed to allow migrations to be run automatically, while isolating the migration-time DB creds from the runtime environment.
claytonjy 6y ago
This is great; no more need for pgextwlist then? Would have saved me a day or two quite recently...

merqurio 6y ago

I heard someone say the other day that they would skip this version and wait for the next one. Just because of the version number. I found it somewhat sad and very poor judgment, but after thinking about it on my way to home, I think a lot of people will do the same. It would be interesting to keep an eye on the number of downloads from containers and packaging systems to see if this is the case.

throwaway8941 6y ago
Hopefully Chinese (and other non-Western) users will help to counterbalance this lunacy, if it does happen. Also, I see a lot of container build files not being pinned to a specific version, which automatically implies "latest".
- mrmonkeyman 6y ago
  They have a problem with 4 though
- seized 6y ago
  Many of those users have equal or greater lunancy just with different numbers... Including buying or not buying specific houses due to the address or physical layout etc etc.
- Yetanfou 6y ago
  I don't know why mrmonkeyman's reply on Chinese having something against the number 4 was downvoted into oblivion although I suspect it was because of the all-to-common knee-jerk reaction against 'racism'. What he said is true, 4 is the most unlucky number in Chinese because it sounds like the word for 'death'. Buildings in China often don’t have a 4th floor, some of them skip all floor numbers which contain '4' so they don't have a 14th or 24th etc. floor. The number 13 has also gained an ominous reputation in China so it is unlikely that help will arrive from China to combat any 13-related superstitions. PostgreSQL v8, v8.8 or (in the far far future) v88 will be popular though since 8 is the most lucky number - it sounds like the word for 'wealth'.

eatonphil 6y ago

One cool feature is LIMIT WITH TIES so that if there are more rows with the same value sorted on by what would have been the last row, it shows all these rows of the same value.

strikelaserclaw 6y ago

The postgresql team is a sight to behold, people from all over the world making amazing contributions to the project.

aargh_aargh 6y ago

Some articles about new PostgreSQL 13 features: [1] https://info.crunchydata.com/blog/why-postgresql-13-is-a-lucky-release https://info.crunchydata.com/blog/why-postgresql-13-is-a-luc... [2] https://info.crunchydata.com/blog/postgresql-13-hidden-gems https://info.crunchydata.com/blog/postgresql-13-hidden-gems [3] https://www.2ndquadrant.com/en/blog/postgresql-13-limit-with-ties/ https://www.2ndquadrant.com/en/blog/postgresql-13-limit-with... [4] https://www.2ndquadrant.com/en/blog/pg13-slot-size-limit/ https://www.2ndquadrant.com/en/blog/pg13-slot-size-limit/ [5] https://www.2ndquadrant.com/en/blog/unicode-normalization-in-postgresql-13/ https://www.2ndquadrant.com/en/blog/unicode-normalization-in... [6] https://www.2ndquadrant.com/en/blog/backup-manifests-and-pg_verifybackup-in-postgresql-13/ https://www.2ndquadrant.com/en/blog/backup-manifests-and-pg_... [7] https://amitkapila16.blogspot.com/2020/05/improved-autovacuum-in-postgresql-13.html https://amitkapila16.blogspot.com/2020/05/improved-autovacuu...

ainar-g 6y ago
Non-extension get_random_uuid() and the Unicode normalisation stuff are great, love it!
- Dominisi 6y ago
  Stop, I can only get so erect.
- adelarsq 6y ago
  Just for note, its `gen_random_uuid ()` Thanks for the tip.

jeltz 6y ago

My favorite feature in this release is probably incremental sorts. It allows for narrower indexes in certain applications.

arconis987 6y ago

This is fantastic! Love the parallel vacuum feature, which should help my team’s sanity with a write-heavy workload. Does anyone know when the zheap storage system will be released? This system will apparently obviate the need for vacuums all together since old row version information will be kept in an undo log instead of in the heap. This will be a game changer for us.

petereisentraut 6y ago
It's a bit more complicated than that. You will also notice another release note item in PG13 that says "Allow inserts, not only updates and deletes, to trigger vacuuming activity in autovacuum", which is because vacuum is not only about cleaning up deleted or rolled-back rows, but also transaction ID wraparound. Making that go away is also a dream of many, but it's a different (or additional) project than a new storage system.

kriro 6y ago

For me, this project is the biggest FLOSS success story after Linux, xBSD and the GNU stuff. Great quality, amazing team. The best RDBMS that exists today and that includes commercial ones (imo). Thank you.

VWWHFSfQ 6y ago
I think MySQL is still significantly more widely deployed. And Postresql wouldn't be ahead of things like Firefox, apache, etc in terms of successful FLOSS. The world wouldn't really be that much different if postgres didn't exist. But it would be significantly worse if Firefox or apache never existed
- spacemanmatt 6y ago
  I think that is a rather naive take on the scope of PostgreSQL in "invisible" services you depend on regularly
  - VWWHFSfQ 6y ago
    can be swapped out for another floss database pretty easy without any real impact. internet in general would be significantly worse right now if FF or apache never took off
    - jimktrains2 6y ago
      Spoken by someone who has never used any db specific functionality to facilitate simplifying logic and constraints.
    - pronik 6y ago
      I wish you the best of luck replacing PostGIS with just about anything else.
    - spacemanmatt 6y ago
      [laugh react]

Thaxll 6y ago

How do people keep track on all the features that are added in RDMS? When you know the basic stuff like SELECT / JOIN / indexes ect ... how do you stay relevent with new things added?

bhahn 6y ago
Not a direct answer to your question, but there are two ways that I usually learn about new Postgres features. 1. Through blog posts like this 2. Needing something in the moment and googling. To elaborate on #2, I learned about partial indices when I was at a situation at work when I needed to optimize indices for a heavily used table. Google eventually brought me to their excellent docs and SO posts that mentioned them.
VPenkov 6y ago
I work for a fintech company and I'm not much of a SQL guy but I often hear how we've reached some limit of what PostgreSQL can do, and every update is a huge deal, even the patch versions. So I guess it's tech like any other. The more you know and use, the more you're aware of the recent developments. Like pretend you know enough javascript to build small promotional websites with some animations as you scroll down the page. You probably don't care, and are not aware, of the recent developments around optional chaining operators.
jkatz05 6y ago
The PostgreSQL community tries to keep a list of new features that are added in its feature matrix: https://www.postgresql.org/about/featurematrix/ https://www.postgresql.org/about/featurematrix/ This is not an exhaustive list, but it contains a lot. You would still have to cull information from release notes: https://www.postgresql.org/docs/release/ https://www.postgresql.org/docs/release/ Or looking at the documentation page for a command (e.g. SELECT), or as per other suggestions, reading blogs/articles.
castorp 6y ago
> How do people keep track on all the features that are added in RDMS? By reading the release notes
6y ago

ruairispain 6y ago

I used to be a big Oracle fan in the late 1990s, but now can't see the point in paying big bucks for Oracle DB. Are there any legitimate reasons to pick Oracle over Postgres for a new project? Apart from "support"

mritchie712 6y ago
Pretty easy to find "support" for Postgres too and more convenient. e.g. submit a request on CodeMentor and you'll have you several solid devs willing to help you, live, in a matter of minutes.
eloff 6y ago
* cricket sounds
briffle 6y ago
At my last job, I ran about 50 servers running Oracle Standard Edition. (Enterprise Edition would have taken each $25k license to something like $600k for our dual core servers). I was Sysadmin, not a developer, so I have different pain points. I will never look back. There were so many things that we wanted to do, but were 'enterprise only' features. Like rebuild/create indexes online. We had a DB with a 1TB table. rebuilding that, while locking the table, would mean something like a 14 hour outage. With postgresql, online index creation is right there. Oracle didn't support a proper incremental backup, except in enterprise edition. (and RMAN was a pain in the ass of arcane commands and black magic). pgBarman makes backups so simple for us. Standby's on Oracle are good for disaster recovery. But you have to manually ship the logs, apply them, monitor it, and delete old logs. There were multiple thousand line korn shell scripts we utilized, and lots of cronjobs. With Postgresql, its stream based, and my standby follows production by less than 100ms most of the time. Plus, I can connect to the standby, and run read-only queries on it (we run large reports on standby, so we don't affect production) That is also possible in Oracle, with 'enterprise' edition (data-gaurd). We filed a few ticket with Oracle Support. We never got any answers. I don't think we ever got an support person at Oracle that had been there more than a year. Even when we escalated.
deleted 6y ago
[deleted]

shay_ker 6y ago

Are there any PG providers that allow you to do a seamless upgrade, from, say, 12 -> 13? I know logical replication allows you to do this, but with some caveats. Curious what other people's experience is.

contrahax 6y ago
Aiven does this and usually has the updates available within two weeks of release. We've gone 10 -> 11 -> 12 seamlessly (and fully online). GCP also has this, but it usually takes them a year or two to have newer versions of PG (and by that time they are already outdated).
- dwwoelfel 6y ago
  > GCP also has this Are you saying that GCP has seamless upgrades? Any link to a doc? The only one I can find has you dump the database and restore it: https://cloud.google.com/sql/docs/postgres/upgrade-db https://cloud.google.com/sql/docs/postgres/upgrade-db
dragonwriter 6y ago
AWS supports relatively seamless (but not automatic) major version upgrades, though because the database engine may have backward compatibility issues they recommend (but don't require) you do a lot of steps which make it less-than-seamless but safer. They don't try to automatically handle breaking changes for you. https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/USER_UpgradeDBInstance.PostgreSQL.html https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide...
Graphguy 6y ago
IBM Cloud lets you upgrade major versions through Read Replicas and also do dry runs, https://cloud.ibm.com/docs/databases-for-postgresql?topic=databases-for-postgresql-upgrading#upgrading-from-a-read-only-replica https://cloud.ibm.com/docs/databases-for-postgresql?topic=da...

gonzus 6y ago

There are a few projects I absolutely could not live a professional life without: PostgreSQL is one, SQLite is another. All my appreciation and congratulations on a great release!

aplummer 6y ago

Could someone please recommend the best up to date 0 to confident Postgres book?

samgranieri 6y ago
In all honesty, you don't need a book on postgres. They have excellent documentation on their site
tuatoru 6y ago
You don't go from zero to mastery in one book. Three years minimum, double or triple that if you're doing other stuff as well. To start, Stepháne Faroult's SQL Success will give you a solid grounding in using RDBMSes as an application programmer. It's not Postgres specific, but the learning you need to do isn't tied to one vendor. But as sibling said, you'll be using the documentation as well. PostgreSQL has documentation that is nearly up to the standards that hardware engineers expect and rely on.
- tuatoru 6y ago
  Adding to the above, Schönig's Mastering PostgreSQL 11 will help you advance from intermediate to high-intermediate PostgreSQL programmer, as will Faroult's The Art of SQL. Karwin's SQL Antipatterns is useful supplementary reading also. I've looked through my kindle, and I wouldn't bother with much else.
furstenheim 6y ago
This one was super useful for me https://www.amazon.es/PostgreSQL-High-Performance-Gregory-Smith/dp/184951030X https://www.amazon.es/PostgreSQL-High-Performance-Gregory-Sm... I think that they have a newer edition for postgres 10
Spiritus 6y ago
Not sure about “0” but https://theartofpostgresql.com https://theartofpostgresql.com is a good book.

adamch 6y ago

Postgres has great docs. It's one of the big reasons I love it so much. Very comprehensive, although I usually skip straight to the examples.

blahyawnblah 6y ago

I don't have really any experience with PostgreSQL. Everything I've heard says it's awesome. What makes it so good?

ngrilly 6y ago

Awesome release, as always. Kudos to the team. Anyone knows if the new B-Tree deduplication will solve the lack of "index skip scan" in PostgreSQL? https://wiki.postgresql.org/wiki/Loose_indexscan https://wiki.postgresql.org/wiki/Loose_indexscan

didip 6y ago

If I can hijack this thread to make a plea of my, very short, wishlist: * Multi master reads and writes. * Much better connection handlers, it's silly to always install pgbouncer every time I need to use PostgreSQL. With these 2 features, PostgreSQL would grow to the next level. It already has a lot of SQL features. Now it needs operational features.

mekster 6y ago

How do people overcome the limitation that you cannot reorder the columns in PostgreSQL?