Upgrade PostgreSQL 9.4 to 11 or 12

[rhymes]

DEV uses/requires PostgreSQL 9.4. I'm opening this ticket to invite a discussion about upgrading PostgreSQL to 11 or 12.

Between PostgreSQL 9.4 and 12 there are five major versions: 9.5, 9.6, 10, 11 and 12.

EOL dates:

• PostgreSQL 9.4: February 13, 2020
• PostgreSQL 9.5: February 11, 2021
• PostgreSQL 9.6: November 11, 2021
• PostgreSQL 10: November 10, 2022
• PostgreSQL 11: November 9, 2023
• PostgreSQL 12: November 14, 2024 (released on October 3rd, 2019)

I've chosen to highlight features that might benefit DEV in the short term. I omitted main news like logical replication (replication of a subset of tables) and hash partitioning (partitioning data with roughly evenly distributed partitions) because I believe they are not relevant right now, though they might matter in the future.

I've used PostgreSQL for many years and I've always found it extremely stable, I've also been using DEV locally with PostgreSQL 11 since its release in October 2018 without any issue.

What's new in PostgreSQL 9.5

Aside for many bug, performance and security fixes these are some relevant news from PostgreSQL 9.5 that might help DEV:

INSERTs that ON CONFLICT can be transformed into UPDATEs

The guarantee that PostgreSQL 9.5 makes is that an INSERT ... ON CONFLICT DO UPDATE is done atomically, so both the insert and the update are a single operation

What's new in PostgreSQL 9.6

Aside for many bug, performance and security fixes these are some relevant news from PostgreSQL 9.6 that might help DEV:

Parallel execution of sequential scans, joins and aggregates

This is self explanatory, PostgreSQL 9.6 is the first major version to introduce paralellism in queries and aggregations. Such paralellism has been greatly expanded in versions 10, 11 and 12.

What's new in PostgreSQL 10

Aside for many bug, performance and security fixes these are some relevant news from PostgreSQL 10 that might help DEV:

Full text search on json columns

FTS in PostgreSQL is already a cool feature, but now it's possible to search json/jsonb columns as well (example taken from postgresql.org):

SELECT bookdata -> 'title'
  FROM bookdata
  WHERE to_tsvector('english',bookdata) @@ to_tsquery('duke');
------------------------------------------
"The Tattooed Duke"
"She Tempts the Duke"
"The Duke Is Mine"
"What I Did For a Duke"

Improved parallel queries

They have done a lot of work improving parallelism for queries. Some types of joins and index scans are executed in parallel:

• parallel merge joins
• the support for parallel index scans has been improved
• it's also possible to customize the number of parallel workers in a single query (defaults to 8)

Improved monitoring on DB activity

pg_stat_activity shows the background processes operating on the DB and more information about what's going on.

Add IDENTITY columns

They added the type IDENTITY which is similar to the data type SERIAL but is compliant with the SQL standard.

What's new in PostgreSQL 11

Aside from many bug, performance and security fixes these are some relevant news from PostgreSQL 11 that might help DEV:

Improved support for parallel operations

• parallel hash joins (helps with inner joins)
• parallel append (helps with unions)
• Parallel creations of b-tree indexes (CREATE INDEX)

ALTER TABLE ADD COLUMN NOT NULL with DEFAULT runs in constant time

This is huge! PostgreSQL addition of a column is affected by the size of the table itself because it essentially has to rewrite the table.

PostgreSQL 11 guarantees constant time if asked to add a non null column with a default (a constant default) which means it's unaffected by the table size and runs fast.

Covering indexes

Basically you can attach additional columns to an index, to avoid going back to the heap for the often required columns that are not part of the index itself. Useful in case of related data that's queried all the time with the indexed column(s)

What's new in PostgreSQL 12

Aside for many bug, performance and security fixes these are some relevant news from PostgreSQL 12 that might help DEV:

• Add a partial support for the SQL standard "JSON path", which allows querying jsonb columns in a standard and easier way than existing JSON operators
• REINDEX CONCURRENTLY to do just that
• Generated columns allow the user to create computed columns as a result of operations on other columns, like views but on a column
• Progress monitoring on CREATE INDEX and CREATE INDEX CONCURRENTLY: when a index is being created on a big column, you can ask PostgreSQL to tell you how far along the creation is.

Some of these are truly awesome!

How to upgrade on Heroku

Heroku has a detailed (yet simple) guide on how to do it. It does require some downtime (around 10 minutes for the first method, around 3 minutesp per GB for the second): Upgrading the Version of a Heroku Postgres Database

Since it requires downtime it should be carefully planned and notified.

Resources

• PostgreSQL.org: Feature Matrix
• PostgreSQL.org: Release 9.5
• PostgreSQL.org: Release 9.6
• PostgreSQL.org: New in postgres 10
• PostgreSQL.org: Release 10
• PostgreSQL.org: Release 11
• PostgreSQL.org: Release 12
• Waiting for PostgreSQL 12
• Just Upgrade: How PostgreSQL 12 Can Improve Your Performance
• Upgrading the Version of a Heroku Postgres Database
• Why upgrade PostgreSQL?: Upgrading from 9.4.24 to 11.5 gives you 1493 fixes

[ibrahimmohelsayed]

can i take this issue ?

[rhymes]

@ibrahimelbanna this is more a ticket to start a discussion than an actual issue (my bad for not labelling this correctly from the start).

If the discussion has a positive outcome and the upgrade is given a go, then DEV Heroku's DB will have to be manually upgraded using Heroku's upgrade guide. After that I'm sure you can work on a PR to upgrade the minimum required version.

I've been running DEV locally on PostgreSQL 11 for months and I know it it works (there are no breaking changes between all of these releases), what version do you have locally?

[rhymes]

Closing this because we're officially on PostgreSQL 11 🎉