5 ms·

As someone still on Postgres 9.6, does anyone have advice on how to make the business case for upgrading to 10, 11, or 12? I want to upgrade but I have a hard t

As someone still on Postgres 9.6, does anyone have advice on how to make the business case for upgrading to 10, 11, or 12? I want to upgrade but I have a hard time articulating the benefits against the costs of downtime and risk

skeletal88 6y ago
There are no other risks than the chance that you somehow mess up the upgrade, delete the wrong files, etc. Planning and practicing the upgrade process helps against these risks. What I most waited for was improved replication and table partitioning support. These were must haves for us.
- Tostino 6y ago
  The other thing that helps with these risks is having a properly managed backup system. If you can always just practice the upgrade on a new machine, or easily roll back if the upgrade does fail.
ksec 6y ago
> make the business case On the assumption right now there may be less people using the services and may be the least disruptive if anything goes down. Upgrading now means you have less hassle in the future.
briffle 6y ago
I am in the same boat. It appears 9.6 is still supported until Nov 2021 [0] but I plan to look at moving up to 12 here either this summer, or next spring. I know partitioning is drastically improved, but we currently don't use it in our data (but need to start looking at it) [0]https://www.postgresql.org/support/versioning/ https://www.postgresql.org/support/versioning/
nix23 6y ago
Well i am too on 9.6 its still supported and i have absolutely no problems with it, but maybe you find here some "must have's" : https://www.postgresql.org/about/featurematrix/ https://www.postgresql.org/about/featurematrix/
- nine_k 6y ago
  Indeed, GIS improvements, index operations improvements, statistics improvements, stored generated columns, parallelized hash joins all could be compelling reasons to upgrade. They improve operations efficiency and DB performance (nearly) without any changes to the working code.
fabian2k 6y ago
At some point 9.6 won't get security patches and bugfixes anymore as far as I understand. That alone is a reason not to fall too far behind with upgrades. There is lots of new stuff regarding declarative partioning and parallel queries. If any of that seems useful to you those are big new features. There have been lots of small performance improvements that add up, and also the long requested change to CTEs as omptomization fences. I'd probably just scan the release notes for 10-13 and see if anything particular hits a pain point you're having.
tomswartz07 6y ago
depesz has a great tool that highlights the changes. Even going from latest 9.6 to latest 12 gives a huge number of fixes and changes and features. Can't recommend this tool enough :) https://why-upgrade.depesz.com/show?from=9.6.17&to=12.3 https://why-upgrade.depesz.com/show?from=9.6.17&to=12.3
unixhero 6y ago
No real risks to be honest. There are many new stable features on every major version. V10 was rock solid to me and my team. However I needed to go v11 in order to get stored procedures, a feature our data scientist required and based on his Oracle SQL background this was a necessity. 9.6 is old now. I'd upgrade. Get pgadmin4 while you're at it.
crad 6y ago
1. There is a significant amount of improvement with regard to performance. 2. The partitioning improvements are tremendous and well worth the change if you're using trigger+constraint based partitioning. 3. Parallelization and the addition of concurrent reindexing 4. Built-In Logical Replication 5. JIT query optimization
aeyes 6y ago
With the upgrade from 9.6 to 10 we improved performance by 20% without doing any other changes. From 10 to 11 it was pretty similar, saw a 15-20% performance increase. We haven't made the jump to 12 yet, we expect overall performance to be lower but disk I/O to be improved. If performance doesn't matter and you don't need any of the new features you'll have a hard time finding reasons to upgrade. I consider 9.6 to be very stable.
- Tostino 6y ago
  While it may be very stable, the business case for upgrading sooner rather than later is not being behind the 8-ball when 9.6 EOL comes in a year.
tams 6y ago
If you're on AWS you can use Performance Insights [1] from PostgreSQL 10 and up. [1] https://aws.amazon.com/rds/performance-insights/ https://aws.amazon.com/rds/performance-insights/
garyclarke27 6y ago
Version 12, Inlining of - With CTEs - has been a big win for us, we have seen some dramatic improvements in performance. Also Parallel query to take advantage of multi core processors has improved performance in general since 9.6. I also like Lateral joins especially with Set Returning functions, they are literally like magic.
zejn 6y ago
Test before upgrade. There is one risk for this migration - versioning has changed a bit between 9.6 and 10 and some libraries have an issue with that. (They need to be updated.)

skeletal88 6y ago

There are no other risks than the chance that you somehow mess up the upgrade, delete the wrong files, etc. Planning and practicing the upgrade process helps against these risks. What I most waited for was improved replication and table partitioning support. These were must haves for us.

Tostino 6y ago
The other thing that helps with these risks is having a properly managed backup system. If you can always just practice the upgrade on a new machine, or easily roll back if the upgrade does fail.

ksec 6y ago

> make the business case On the assumption right now there may be less people using the services and may be the least disruptive if anything goes down. Upgrading now means you have less hassle in the future.

briffle 6y ago

I am in the same boat. It appears 9.6 is still supported until Nov 2021 [0] but I plan to look at moving up to 12 here either this summer, or next spring. I know partitioning is drastically improved, but we currently don't use it in our data (but need to start looking at it) [0]https://www.postgresql.org/support/versioning/ https://www.postgresql.org/support/versioning/

nix23 6y ago

Well i am too on 9.6 its still supported and i have absolutely no problems with it, but maybe you find here some "must have's" : https://www.postgresql.org/about/featurematrix/ https://www.postgresql.org/about/featurematrix/

nine_k 6y ago
Indeed, GIS improvements, index operations improvements, statistics improvements, stored generated columns, parallelized hash joins all could be compelling reasons to upgrade. They improve operations efficiency and DB performance (nearly) without any changes to the working code.

fabian2k 6y ago

At some point 9.6 won't get security patches and bugfixes anymore as far as I understand. That alone is a reason not to fall too far behind with upgrades. There is lots of new stuff regarding declarative partioning and parallel queries. If any of that seems useful to you those are big new features. There have been lots of small performance improvements that add up, and also the long requested change to CTEs as omptomization fences. I'd probably just scan the release notes for 10-13 and see if anything particular hits a pain point you're having.

tomswartz07 6y ago

depesz has a great tool that highlights the changes. Even going from latest 9.6 to latest 12 gives a huge number of fixes and changes and features. Can't recommend this tool enough :) https://why-upgrade.depesz.com/show?from=9.6.17&to=12.3 https://why-upgrade.depesz.com/show?from=9.6.17&to=12.3

unixhero 6y ago

No real risks to be honest. There are many new stable features on every major version. V10 was rock solid to me and my team. However I needed to go v11 in order to get stored procedures, a feature our data scientist required and based on his Oracle SQL background this was a necessity. 9.6 is old now. I'd upgrade. Get pgadmin4 while you're at it.

crad 6y ago

1. There is a significant amount of improvement with regard to performance. 2. The partitioning improvements are tremendous and well worth the change if you're using trigger+constraint based partitioning. 3. Parallelization and the addition of concurrent reindexing 4. Built-In Logical Replication 5. JIT query optimization

aeyes 6y ago

With the upgrade from 9.6 to 10 we improved performance by 20% without doing any other changes. From 10 to 11 it was pretty similar, saw a 15-20% performance increase. We haven't made the jump to 12 yet, we expect overall performance to be lower but disk I/O to be improved. If performance doesn't matter and you don't need any of the new features you'll have a hard time finding reasons to upgrade. I consider 9.6 to be very stable.

Tostino 6y ago
While it may be very stable, the business case for upgrading sooner rather than later is not being behind the 8-ball when 9.6 EOL comes in a year.

tams 6y ago

If you're on AWS you can use Performance Insights [1] from PostgreSQL 10 and up. [1] https://aws.amazon.com/rds/performance-insights/ https://aws.amazon.com/rds/performance-insights/

garyclarke27 6y ago

Version 12, Inlining of - With CTEs - has been a big win for us, we have seen some dramatic improvements in performance. Also Parallel query to take advantage of multi core processors has improved performance in general since 9.6. I also like Lateral joins especially with Set Returning functions, they are literally like magic.

zejn 6y ago

Test before upgrade. There is one risk for this migration - versioning has changed a bit between 9.6 and 10 and some libraries have an issue with that. (They need to be updated.)