Upgrade to PostgreSQL 15

[ymahajan]

Jira Link: DB-1591

Motivation -

1. Feature / Need: This is one of the key motivating factors for application developers and database administrators. Each new version introduces many small and big features which open new ways of solving a problem and introducing new improvements.
2. Support for new Postgres Extensions: An added advantage for some can be added features which you get with the use of Postgres Extensions. With each new version of PostgreSQL the extensions take advantage of the new features and add functionality which can be boon for application development. Tools like Pgpool, Bgbouncer, extensions get better with each version.
3. Community Support and Fixes: The best part of using an open source community database is not being bound by proprietary licensing hence low cost of operations as well as a large group of developers contributing to it. That doesn't translate to being able to keep using a version indefinitely or getting fixes indefinitely. Every version of Postgres is supported by the community for a defined period of time in which the community releases security patches and bug fixes on a regular cycle. Once the version is deemed unsupported, the fixes stop.
4. Compliance: Organizations have strict compliances to ensure and enforce that critical application databases are up to date with the latest supported versions. These policies can also extend to applications with embedded databases. This makes it imperative to keep your deployments up to date.
5. Performance: Throughput of your database can be your key driving factor for upgrades. With each new version Postgres keeps delivering better performances.

Phase 1 - Set up Postgres 15 branch for YSQL

Feature	Status	Comments
Create pg15 YSQL branch	✅	https://github.com/yugabyte/yugabyte-db/tree/pg15
Rebase Postgres 15 changes onto pg15 branch	✅	From PG version 15.2 (commit)
Fix post-rebase compilation issues	✅	Support compiling with ./yb_build.sh --clang15 --sjb --rebuild-postgres --no-initdb
Set up regular rebase of Yugabyte latest on pg15 branch	✅	weekly rebase from yugabyte-db/master branch

Phase 2 - Support YSQL database initialization (initdb)

Feature	Status	Comments
Initdb bootstrap stage	✅	Initialize core template1 system tables
Initialize rest of pinned objects	✅	setup_auth and setup_depend
Initialize rest of template1 objects	✅	collations, dictionary, privileges, schemas, plpgsql
Initialize other primitive databases	✅	template0, postgres, yugabyte, and system_platform

Phase 3 - Feature parity with current YSQL

Many features are already used internally as part of initdb (phase above)

Feature	Status	Comments
Set up CI tests for pg15 branch	✅	Run all the same tests as for the latest branch
Review new PG 15 tests	✅	Add any new tests for pre-existing (PG11) features
Clean up CI test results	✅	Fix any broken tests to get regular clean runs on pg15 branch
Review and merge PG extensions	✅	e.g. pg_stat_statements, pg_hint_plan, orafce, etc.
Support rolling upgrade to YSQL.PG15	⬜️	Functional Spec and Design Doc
Merge pg15 branch into master	✅	PG v15 as default YSQL baseline

Phase 4 - Enable select PostgreSQL v12 to v15 features

Feature	Status	Comments
Allow foreign keys to reference partitioned tables	🕐	Added in PG 12 (release notes)
Stored generated columns	✅	Added in PG 12 (release notes)
Better query planning when using extended statistics	⬜️	Added in PG 13 (release notes)
Improvements to PostgreSQL's query parallelism support	⬜️	Added in PG 14 (release notes)
Improved Sort Performance and Compression	⬜️	Added in PG 15 (release notes)
Support for the SQL MERGE command.	⬜️	Added in PG 15 (release notes)

Phase 5 (future) - Enable all PostgreSQL v12 to v15 features

[adil246]

@ymahajan This will also enable creating foreign keys referencing partitioned tables with other improvements for copy, partition join, etc available from PG12 onwards. A few customers have been requesting this feature.

[kisg]

Do you have a strategy for keeping up with upstream PostgreSQL? I admit that I have not looked into your forked version in detail, so I don't know how feasible it would be. But wouldn't it make sense to refactor the main PostgreSQL codebase in a way so it has APIs / extension points where your code has to diverge from core PostgreSQL? If these changes could be accepted by the upstream PostgreSQL project, then the amount of maintenance work you have to do would drop significantly.

If you start working on upgrading to PostgreSQL 16 (the current development version) now and also do these refactorings and submissions to the upstream project, you could have it done and accepted by the time PostgreSQL 16 is released.

Another option would be to keep up with upstream PostgreSQL on a commit-by-commit basis. This is not a very fun project (I was involved in such a project to port Google V8 commit-by-commit from ARM to MIPS - did it for 6 years), but the result is that you can follow the PostgreSQL feature set very closely.

What do you think?

[bmjhversteeg]

I see upgrade to Postgres 13/15 is still on the github frontpage as being in progress, but is there any news regarding this? The lack of foreign key support on partitioned tables is currently holding our project back from migrating to yugabyte

[mrajcevic01]

Hi @bmjhversteeg,

This effort is actively underway. I will let the engineering team comment on the timeline if one is available at this time. We currently have two main workarounds for this issue - #15424

If you can reach out to me (@MarkoR (YB)) in our Community Slack I can share the function mentioned in #15422 if you would like to test with it. I have also created some documentation that can be shared that further clarifies your options for FKs on non-partitioned and partitioned tables.

[bmjhversteeg]

@mrajcevic01 Thanks a lot for your reply! I'll meet you on Slack.

[FranckPachot]

@bmjhversteeg I'm interested to know more about your use case needing foreign keys to partitioned tables. I think that in the context of YugabyteDB the need to reference a partitioned table from a non-partitioned one should be rare. I've detailed that in: https://dev.to/yugabyte/foreign-keys-referencing-partitioned-tables-in-yugabytedb-26pn but I'll be happy to see if I missed something. I'll ping you on slack

[NOBLES5E]

This issue seems to have been open for a while now, and it's clear from the discussion thread that it's of substantial importance to many users. Could someone from the engineering team kindly provide an estimated timeline for the completion of the PostgreSQL 15 upgrade? I understand that it's a complex endeavor, and these things can be difficult to predict accurately, but a rough estimation would be helpful to understand when we might expect this upgrade. Thanks for all the hard work on this!

[alexted]

Now, it seems to make sense to upgrade to version 17, not 15

[FranckPachot]

@alexted which PG16 feature are you using? It is important to think in term of features to also remain compatible with previous versions of PostgreSQL

[thinkwelltwd]

which PG16 feature are you using? It is important to think in term of features to also remain compatible with previous versions of PostgreSQL

As a longtime lurker on this 3.5 year old thread, with well-nigh zero (0) meaningful input from YB, that response seems rather tactless and impertinent.

Shouldn't YB support a modern version of Postgres and let application writers and sysadmins worry about compatibility with older versions?

[alexted]

@alexted which PG16 feature are you using? It is important to think in term of features to also remain compatible with previous versions of PostgreSQL

1. JSON path expressions
2. generated columns
3. nondeterministic collations
4. "REINDEX CONCURRENTLY" command
5. pg_checksums command
6. extended statistics based on "CREATE STATISTICS" command
7. gen_random_uuid() function
8. JSON data access using expressions, reminiscent of working with arrays
9. multirange-types
10. "SEARCH" and "CYCLE" commands
11. "MERGE" command
12. regexp_count(), regexp_instr(), regexp_like() and regexp_substr() functions
13. security_invoker regime
14. ability to set "ICU Collation" as default collation instead of libc
15. paralleling the operations of joining tables "FULL JOIN" and "RIGHT JOIN"
16. "IS JSON", "JSON_ARRAY()" и "JSON_ARRAYAGG()" functions
17. ability to using the underscore character in numbers
18. ability to specify hexadecimal, binary and octal literals

I can go on and on with this list...

[FranckPachot]

@alexted YugabyteDB uses PostgreSQL only for the SQL layer so the features that are storage specific do not depend on the PostgreSQL compatibility. For example:

• "REINDEX CONCURRENTLY" command: there's no blot in indexes so re-indexing is not useful. And you can drop+create indexes and they are by default created concurrently
• pg_checksums command: YugabyteDB has checksums to detect corruption and automatically repairs from the other replicas
• gen_random_uuid(): we have this in pgcrypto extension
• ability to set "ICU Collation" as default collation instead of libc: we do not depend on OS GLIBC
• paralleling the operations of joining tables "FULL JOIN" and "RIGHT JOIN": parallel query is being implemented in YugabyteDB
• generated columns: the PG12 identity columns were added to YugabyteDB on top of PG11 compatibility

Just wanted to note that features on layers below the SQL API don't apply to YugabyteDB as-is. I agree that there are also great SQL features that we will benefit from.

[alexted]

Thanks for your comment. besides the few things I mentioned that you mentioned, there is a lot more functionality from the current SQL layer of Postgres missing. Well, in this case, could you tell me if we should wait for the necessary functionality of the latest versions of PostgreSQL in YugabyteDB?
Is work on this task being done at all?

[sushantrmishra]

@alexted
Currently we are aggressively working on upgrading YugabyteDB to PostgreSQL15.

Current state:
Code from PostgreSQL15 has been merged with YugabyteDB code and is available in a separate pg15 branch in Yugabyte repository.
Refer to https://github.com/yugabyte/yugabyte-db/tree/pg15 .

Currently we are working on

• Stabilizing the tests and making sure that all the test suites are passing with the PostgreSQL15 compatible YugbayteDB clusters.
• Seamless upgrade from existing YugabyteDB Clusters to PostgreSQL15 compatible YugabyteDB clusters.

Considering this is a very large effort, so currently do not have any fixed timeline yet. Though as we are getting closer to the completion of ongoing task, we will update the firm timeline for it.
Tentatively we expect to have the ability to create the PostgreSQL15 compatible cluster this year (2024) .

Please ping us for any additional question you may have.

[alexted]

@sushantrmishra Look, April 8 is going to be a feature freeze in PostgreSQL. Perhaps, in that case, it makes sense for you to work with PostgreSQL version 17 at once?

[ddorian]

@alexted one of the reasons that this project is hard is because we're looking to support online-upgrade of the version, which isn't supported in normal PostgreSQL. Once we upgrade to PG15, and support online upgrade, it will be much easier to upgrade to newer versions of PostgreSQL.