[policy] Decide on PostgreSQL 9.x/10.x support status

Looks 10 is great number!

The newest versions of Debian 10 and Red Hat Enterprice Linux (RHEL) 8 are gong to be almost a full year old when Drupal 9 will be released. For me that is long enough to be used as a base distribution for Drupal 9. Ubuntu 18.04 LTS will be by then 2 years old. All 3 of those distribions support at least PostgreSQL 10. PostgreSQL 10 is by the time of the release of Drupal 9 over 2.5 years out. Which implies that it should be very stable.

According to @Dries: "Drupal is for ambitious digital experiences". Supporting only modern databases helps with that goal.

For me the minimum supported version for PostgreSQL should be 10.

As long as Drupal supports MySQL, rather old versions of Postgres will generally work since newer Postgres features are not in use. Using 9.5 as the lowest version means that INSERT ... ON CONFLICT (upsert) is available. That is useful for implementing db_merge(). Are there any other new Postgres features which would actually get used?

I think that for Drupal 9.0, we should set the PostgreSQL minimum to either 9.6 or 10, and keep it there for all of Drupal 9's lifetime (until November 2023). Which includes having DrupalCI test on it, even after they're technically EOL.

Do we foresee difficulty in being able to have DrupalCI test on 10 during 2023? Is the concern that we won't have access to a secure build of it? Do we know if Ubuntu will provide secure builds of it past April 2023? Note that Ubuntu now provides 10 years of support to 18.04 instead of 5, but I don't know if that includes backporting security fixes to all of their packages beyond the first 5 years. Also, RHEL8 will be supported until 2028, but again, now that they have app streams, I don't know what that means in terms of how long they'll backport security fixes to PostgreSQL 10.

I think deciding on 9.6 is the tricky one. That will go EOL in Nov 2021, and without Ubuntu backporting security fixes to it, I don't know if anyone will. So we truly might not be able to have DrupalCI test on it during 2022 or 2023. I think it would be ideal to avoid changing platform requirements during D9's lifetime, so therefore, if we're going to have to drop PostgreSQL 9.6 by 2022 anyway, I think there's a strong argument for dropping it in Drupal 9.0. However, the counterargument to that is that Drupal 7 and 8 sites will have to upgrade to Drupal 9 by November 2021, and since PostgreSQL 9.6 will still be a version supported by PostgreSQL itself all the way up until then, I'm concerned about how many D7 and D8 sites will still legitimately be on PostgreSQL 9.6 by then and not yet have access to a higher version on their server.

Using 9.5 as the lowest version means that INSERT ... ON CONFLICT (upsert) is available. That is useful for implementing db_merge(). Are there any other new Postgres features which would actually get used?

Even if we don't plan to use new features beyond 9.5, I still think we should enforce a minimum (whether that's 9.6 or 10) during installation, and also mirror that minimum for what we test on with DrupalCI. For people who want to use unsupported versions, they can do so by creating their own driver, and then they're responsible for securing it and testing it.

Do we foresee difficulty in being able to have DrupalCI test on 10 during 2023? Is the concern that we won't have access to a secure build of it? Do we know if Ubuntu will provide secure builds of it past April 2023? Note that Ubuntu now provides 10 years of support to 18.04 instead of 5, but I don't know if that includes backporting security fixes to all of their packages beyond the first 5 years.

I found the info on https://ubuntu.com/esm. Extended security maintenance (ESM) is the window that Ubuntu 14.04 is currently in (between April 2019 and April 2022) and the window that Ubuntu 18.04 will be in between April 2023 and April 2028. In the "What’s covered?" section of that page, it says "ESM continues security updates for high and critical CVEs (Common Vulnerabilities and Exposures), in the Ubuntu base OS and scale-out infrastructure". I don't know how the definition of "scale-out infrastructure" will evolve for when 16.04 and 18.04 enter the ESM window, but presently, for Ubuntu 14.04, it does include postgresql. If that continues to be true for 18.04, then that should mean that we'd have access to secure backports of PostgreSQL 10 available for DrupalCI to use for all of 2023. However, that access might require a paid ESM subscription, because PostgreSQL uses an MIT-like license rather than a GPL license, so Canonical would be free to decide what license to release their security backports under.

There are a lot of (little) changes in PostgreSQL (See https://www.postgresql.org/docs/10/release-10.html). There is one I would like to highlight: "Add full text search support for JSON and JSONB".
For me the whole PostgreSQL 9.6 is something we should not do. @effulgentsia has a whole lot of good points why we should not support 9.6 in Drupal 9. I know I have the more developer viewpoint on this and I do not like to work with old software. But I also realise that the operational side of the software business is important.

For me the whole PostgreSQL 9.6 is something we should not do.

I'd love to set the minimum to 10 if we can get away with it. But we shouldn't leave D7/D8 users on Postgresql without an upgrade path to D9 come late 2021.

I tried to get a sample of where PostgreSQL support is for some of the hosting companies in https://www.drupal.org/project/drupal/issues/2938725. Here's some of them:

• A2 Hosting: 9.6
• AlwaysData: 12
• Amazee: 11
• Bluehost: 8.1
• Digital Ocean: 10 and 11
• Gandi: 9.6
• SiteGround: "Latest"

I think that's pretty encouraging so far. Two in the above list are on 9.6, but that's now, which makes me think there's a reasonable chance they'll update to 10 by next year. I don't know what's up with Bluehost and if that help page saying they're on 8.1 is accurate.

However, when I got to https://www.liquidweb.com/products/cloud-sites/#faqs, this raised a red flag for me:

On the Linux side, Cloud Sites employs Debian 8

If they're on Debian 8 now, there's a chance they'll only get to Debian 9 a year or two from now. And I wonder how many other managed hosting providers will be similarly conservative.

So one thought I have is should we make the minimum for the core driver be 10, but then have a contrib project that overrides the core driver, sets the minimum lower (9.6? 9.5? 9.4?), and is then responsible for fixing in that overridden driver whatever issues are discovered on the lower versions? An advantage of punting older PostgreSQL support to contrib would be that as a contrib project, it could sunset support for older versions on whatever schedule it wants, rather than if it's in core there being the expectation that support is maintained until end of 2023.

In any case, I think it makes sense to do #2846994: Increase minimum version requirement for Postgres to 10 and require the pg_trgm extension (for Drupal 9) in the meantime, while we continue to deliberate on whether to further raise it to 9.6 or 10.

So one thought I have is should we make the minimum for the core driver be 10, but then have a contrib project that overrides the core driver, sets the minimum lower (9.6? 9.5? 9.4?), and is then responsible for fixing in that overridden driver whatever issues are discovered on the lower versions?

I think that would be a great solution!

With PostgreSQL we are already getting to a two track solution. To fix #2988018: [PP-1] Performance issues with path alias generated queries on PostgreSQL we are adding (optionaly) the extension "pg_trgm". Without the extension we are doing table scan queries over the url_alias table. We should see if we can use that fix with other tables. We have the option of not doing that in Drupal 8 and wait for Drupal 9.1 to implement it. Maybe adding the extension "pg_trgm" as a requirement for the by core supported driver in Drupal 9 and leave it for the contrib driver?

I think the best version to support as a contrib driver would be 9.1.2, which is the minimum required version for Drupal 8. If you have a Drupal 8 site on PostgreSQL, you can use this driver for Drupal 9. Maybe only for making a migration easier or stay on it for a longer time. There will be no new functionality, only keeping working on Drupal 9.
Newer versions like 9.5 and 9.6 will and up getting the same treatment as a contrib driver with a minimum version of 9.1.2.

There shall be some minor work needed on the testbot. The testbot now runs for contrib modules only the tests that are added by that module and not the full testset from core. Contrib database drivers need to have the full testset of core being run. See: #3106299: Create testbots for the contrib database drivers for OracleDB, Microsoft SQL Server and MongoDB.

@effulgentsia: I have made a plan how to improve the support for contrib database drivers in #2846366-17: Improve Drupal's Database Abstraction Layer Extensibility and Capabilities.

A question for the product manager. When using Drupal on PostgreSQL, we have the problem with queries that have conditions with the LIKE-operator. Using them will result in full table scans. The solution is to add an index on the tabe column. For that we need the extension "pg_trgm" (See: #2988018: [PP-1] Performance issues with path alias generated queries on PostgreSQL ). To install extensions on a PostgreSQL database one needs to have superuser or database owner privileges (See: https://www.postgresql.org/docs/9.1/sql-createextension.html). We can add support for the extension "pg_trgm" in different ways:

1. We can add the extension "pg_trgm" as optional for Drupal 9. This result in having to support sites that have and do not have the extension installed. Also we must support sites that install the extension in that change and the other way around. This option is the easiest for the users of Drupal on PostgreSQL. It is however not the easiest to support.
2. We can add the extension "pg_trgm" as required for Drupal 9. Drupal on PostgreSQL is for all users on the by core supported driver a better experience (faster). As for support: we only have to support with the extension installed. Also a single upgrade path for adding the indexes must be created. Users of Drupal on PostgreSQL who for some reason cannot get the extension installed can no longer use Drupal on PostgreSQL.
3. The same as the previous option only we create a contrib database driver for PostgreSQL without support for the extension. Users of Drupal on PostgreSQL have now a fallback option. This driver will also not support the new JSON functionality. The minimum version would be 9.1.2 and that is the same as the minimum version that is required for Drupal 8. Users can use this driver for upgrading to Drupal 9 and then at a later moment upgrade to the by core supported driver. Users will be encouraged to moving to the by core supported driver. I am willing to become the maintainer of that contrib database driver.

Add the tag "Needs product maintainers review", because they can make the best judgment call about what the users of Drupal on PostgreSQL want/need. In short support on more users can use Drupal on PostgreSQL vs. a better experience with working/using Drupal on PostgreSQL. As somebody who works on the driver software, I would go for option 2 or 3, but it is not my call to make.

@daffie: As a core product manager I believe your questions belong more in the framework manager responsibilities. @effulgentsia is already a framework manager, so you are getting feedback :) See https://www.drupal.org/contribute/core/maintainers for the different roles:

Significant developer features, changes to public APIs, external libraries, and overall architecture must be approved by the framework managers.

The same as the previous option only we create a contrib database driver for PostgreSQL without support for the extension. Users of Drupal on PostgreSQL have now a fallback option.

Having a contrib driver to support lower versions sounds pretty good, but do we have a way for database drivers to do database updates? - i.e. when you switch from contrib to core, how do you get the new indexes?

As a core product manager I believe your questions belong more in the framework manager responsibilities.

For me it was more a decision for a product manager, but if you (@gabor_hojtsy) say that it more a decision for a framework manager, then that is fine for me too.

@catch: AFAIK Drupal 9.0 will start with the "pg_trgm" extension as optional or required. There will be no indexes added in Drupal 9.0. The first index will be added in Drupal 9.1 or later. For that we shall need update function. To migrate from the contrib driver to the by core supported driver, those update functions need to executed. My idea is to let the contrib driver have a special menu option to execute all those update functions. After that the user can switch to the by core supported driver.

to migrate from the contrib driver to the by core supported driver, those update functions need to executed. My idea is to let the contrib driver have a special menu option to execute all those update functions.

Putting the updates in the contrib project somewhere makes sense to solve that issue.

To me it seems fine to make the pg_trm extension a requirement for core.

To me it seems fine to make the pg_trm extension a requirement for core.

Can we confirm that Debian 10, Ubuntu 18.04, and Postgres 10 on RHEL 8, all come with pg_trgm enabled by default? If so, then I agree it's fine to require it for core. If not, I think we should discuss a bit more.

Debian 10

$ apt install postgresql-contrib
...
$ find /usr -name pg_trgm.so
/usr/lib/postgresql/11/lib/pg_trgm.so

Ubuntu 18.04

$ apt install postgresql-contrib
...
$ find /usr -name pg_trgm.so
/usr/lib/postgresql/10/lib/pg_trgm.so

RH8 needs to find

Centos 8

# dnf module list|grep postgresql
postgresql           9.6        client, server [d]                       PostgreSQL server and client module                                
postgresql           10 [d][e]  client, server [d]                       PostgreSQL server and client module                                
postgresql           12         client, server                           PostgreSQL server and client module    

# dnf install postgresql-contrib
....
# find /usr -name pg_trgm.so
/usr/lib64/pgsql/pg_trgm.so

Dropping support for everything below 9.6 definitely seems the way to go (unless that Bluehost bit is for real or indicative of a larger trend).

I'm less confident that we should raise the requirement to v10 based on the sample in #14. Based on the EOL dates 10+ would be good, but if half of hosting providers that support PostgreSQL don't support 10+, that raises some doubts for me. Should we look at more hosting providers, and/or add a table to the IS like we have for PHP version hosting and solicit feedback from the community like we did there?

FWIW the contrib driver approach also seems worthwhile, but if we do rely on that, we'd need the contrib driver to exist first (and would want it to still be the minority usecase for PostgreSQL, I think).

Re #28, if we're willing to entertain the possibility of a contrib driver, then my recommendation would be for us to release Drupal 9.0-beta1 with a requirement on PostgreSQL 10 and the pg_trgm extension. Then, if between beta and RC, if either we don't get a contrib driver up, or if we learn during the beta testing period that 9.6 and/or lack of pg_trgm extension are more commonly out in the wild than we're willing to punt to contrib, then we can loosen core's requirements. In other words, I think it's better to have stricter requirements for beta and then loosen them before release if necessary than to have looser requirements for beta and then regret the extra maintenance burden in core for 3+ years.

#29 seems like a good way around. We'd need DrupalCI support for that whether the support is contrib or core.

I understand why you @effulgentsia and @xjm want to wait as long as possible before making a decision about which version of PostgreSQL will be the minimum version supported by Drupal 9 and whether the pg_trgm extension will be required or optional. Also you would like to see the contrib driver first before making a decision about it. From the position as a core committer that is all very understandable.
For me as the person who offered to create and maintain the contrib driver, I would like to wait with creating the contrib driver untill the decisions have been made. Creating a contrib driver with the pg_trgm extension being optional in the by core supported driver is not very usefull. Only now we have a situation where 2 sides are waiting on each other and that is not very usefull. Therefor I would like to know when is the best time to start working on the contrib driver with smallest chance of the pg_trgm extension being optional in the by core supported driver.

So that leaves this bit from effulgentsia's plan:

or if we learn during the beta testing period that 9.6 and/or lack of pg_trgm extension are more commonly out in the wild than we're willing to punt to contrib

I agree with @daffie we shouldn't leave this up in the air - either we move that support to contrib (as long as the contrib driver happens), or we should commit to supporting in core now.

But having a contrib driver written, and then rolling back (or not going ahead in the first place) in core, is wasting a lot of work.

I'm less confident that we should raise the requirement to v10 based on the sample in #14.

I checked with Gandi, and learned that they actually offer 10 and 11 as options, even on their simple hosting. See https://shop.gandi.net/en/simplehosting/create?size=trial and pick "custom language and db" and "postgresql", and notice the version dropdown. I also found out from the Gandi rep that PostgreSQL extensions, including pg_trgm, are available.

Using the wayback machine, I found out that A2 hosting was on PostgreSQL 9.1 on July 2014, then upgraded to 9.4 some time before April 2016 (at least 6 months before 9.1's EOL), and to 9.6 some time before Feb 2019 (at least 1 year before 9.4's EOL). If this can be extrapolated, then it seems likely they'll upgrade from 9.6 six months or a year or so before its EOL of Nov. 2021.

So one question is... is it ok for the D9 beta and/or 9.0 release to require a PostgreSQL version that some shared hosting companies don't yet have, but that will likely be available on those hosting companies before D7's and D8's EOL? In my opinion, that is ok.

My bigger concern is what about the hosting companies that wait until the last possible moment (Nov. 2021) to upgrade from 9.6, thus leaving those Drupal sites basically no time to upgrade from 7 or 8 to 9? However, I think this will be the minority case (it's pretty irresponsible for a hosting company to cut it that close), for which I think a contrib driver is an acceptable solution.

To be clear, my recommendation is...

• For the beta, set core's minimum to 10. And also require pg_trgm.
• Create a contrib driver that works on 9.6 (and maybe without pg_trgm?).
• In the best case scenario (the contrib driver exists and is running tests on DrupalCI, and we don't get a ton of issues from users who are still stuck on 9.6 or without access to pg_trgm), release 9.0-rc1 the same way.
• In the worst case scenario (the contrib driver isn't viable before RC for whatever reason), relax core's requirements as needed for 9.0-rc1.

Adding back the "needs release manager review" tag to see if this sounds good to them. #30 says yes, but @xjm might still disagree.

#34 sounds good to me. Raise the requirements as high as is reasonable + contrib support, fallback to lowering requirements in core if there turns out to be a problem with contrib.

Wrong issue was linked in the summary.

Actually increasing to 10 as a minimum required version has a beta deadline, but creating a contrib driver to backport 9.6 support or such could be a beta target.

Create an issue for the contrib database driver: #3118455: [META] Create contrib fallback database driver for PostgreSQL 9.6.

I think there's now consensus on #34, which I think means we've decided everything that needs to be decided here, so setting to RTBC.

IS could probably use an update for posterity before we mark this fixed.

I think we might want to have a contrib driver for Postgres 9 before we mark it fixed.

The contrib driver is here: https://www.drupal.org/project/pgsql_fallback

It does not having DrupalCI testing enabled, because DrupalCI does not support contributed database drivers at all yet.

For me, the contrib driver existing is sufficient to mark this issue fixed once there's a tagged release for it. With the caveat that we should not commit patches relying on postgres 10 or pgtrm features until DrupalCI is up and running with the contrib driver.

Released version 2.0.0 of https://www.drupal.org/project/pgsql_fallback.

Now that we have the contrib driver I think we can mark this fixed.

There is #3118448: PostgreSQL 9.6 needed for contrib fallback database driver and I think at least another issue open to improve test support for contrib db drivers, but at the moment the pgsql/sqlite drivers are very light - just allowing sites to run older version vs. actually providing support via different implementations.

I opened #3128699: Testing issue for pgsql_fallback for testing the contrib project in a hacky way until we get a better way.