[mooreds]

Amazing that the process went so smoothly and that there were so many resources for them to draw from. Jumping from 9 to 12 is quite a few major versions!

Also liked the couple of gotchas which go to show no matter how smooth a data migration is, there'll be some bumps.

[hans_castorp]

> Jumping from 9 to 12 is quite a few major versions!

Just a little side note.

They were jumping from 9.6 to 12, not from 9.0 to 12.

Before Postgres 10 was released, the first two digits defined a "major" version). So from 9.6 to 12 it's three major releases (9.6 -> 10, 10 -> 11, 11 -> 12)

[foxhill]

still, changes between 9.6 and 12 are _numerous_, both in features and performance: llvm based query compilation, CTE de-materialisation, proper procedures, and that's just off the top of my head.

i wish the process for upgrading postgres were easier/more dynamic. i'm sure plenty of people are still using versions 9.6 or earlier.

[m_st]

The changes you mention like llvm, CTE de-mat and such: Were these for "free" or did you have to adapt your code?

And if you didn't adapt: Would your code still running after upgrading (backwards compatible)?

I'm asking as a dev mostly working with MS-SQL and seriously considering moving to PostgreSQL one day.

[wiredfool]

I've done PG upgrades on a code base from ~7.x now through 11. For the most part it's pretty seamless, though there are occasionally backwards incompatible changes that pop up, but they're called out in the release notes.

The biggest bites have been changing the database driver from pypgsql to psycopg2 and there was a binary column default wire format change at the 9/9.1 upgrade. In another job, I've got essentially the same code running against 9.5 and 12, and I don't notice the difference.

Performance improvements for a feature are almost always 'free', but obviously your code isn't just going to start using CTEs if you haven't been using them.

[dijit]

> The changes you mention like llvm, CTE de-mat and such: Were these for "free" or did you have to adapt your code?

The changes were made "for-free" as in no code needed to be changed, but when changing query planners caution should be heeded..

I remember a time where a mysql 5.6->5.7 upgrade crippled our entire monitoring stack:

https://bugs.mysql.com/bug.php?id=87164

https://dba.stackexchange.com/questions/121841/mysql-5-7-inn...

[foxhill]

whilst "free" in principle, there is obvious cost/risk in adopting features like those (CTE demat and llvm), if nothing else, in even trying them out.

testing the llvm-based optimiser (in postgres 11) showed little benefit, and in a few circumstances slowed things down. had i had more time to poke around, i'd have probably got it to a place where it represented an improvement, but, up until i left that role, it was a feature that remained switched off - postgres doesn't generally introduce features that represents "breaks" in code.

having not had _that_ much experience with MS-SQL, i'd still recommend the switch. looking through the things in postgres that i take for granted vs. what's in MS-SQL, i don't think i could cope with not having postgres :)