[Policy] How to select the minimum required database versions

Thanks @daffie, avoiding bottle necks is important to allow progress, as also discussed in 📌 Add a composer plugin that supports 'composer require-lenient' to support major version transitions Active .

@daffie by latest version do you mean 'MySQL 8' as opposed to 5.7, or 'MySQL 8.0.34' as opposed to 'MySQL 8.0.25'?

The complication I think is that sometimes latest means 'released two years ago' and sometimes it means 'released a month ago'. Also the release cycles of the three core-supported databases are very different. With MySQL there's also the MySQL vs. MariaDB schedules and versioning too.

It looks like PostgreSQL does major releases approximately every year

https://www.postgresql.org/about/news/postgresql-15-released-2526/
https://www.postgresql.org/about/news/postgresql-14-released-2318/
https://www.postgresql.org/about/news/postgresql-13-released-2077/

I dread to say it, but we might need different approaches for all three databases, for example we generally assume that people running PostgreSQL or sqlite have specific use cases or requirements, so requiring the very latest versions of those should be easy to do without causing too many problems. For MySQL we want people to be able to run on mass hosting where possible, so might need to be a bit more careful. The main thing for core is being able to adopt features that all three databases have in common, or things that bring them into line with one another that we've previously implemented workarounds for or have outstanding bug reports about.

I am curious if there are any relevant statistics for this?

Most Drupal sites use containers and can run with any database version that is required.

Re #4 I agree that MySQL is surely our primary database engine and we probably want to be a bit more conservative there, but it is likely that Postgres and SQLite can be closer to the bleeding edge.

Since we moved database drivers to modules, it would be good to get some update.module stats from the DA so we can see exactly how popular (or not) Postgres and SQLite are.

Things have moved on a bit since I last posted here:

I found PostgreSQL's versioning page which I'm not sure I'd seen previously, and that confirms there's an official (but not exact) yearly release cycle and the actual releases always seem to be in September or October.

https://www.postgresql.org/support/versioning/

Given PostgreSQL 16 was released in September 2023, then I think it would make complete sense for us to require that, and we can expect there won't be a PostgreSQL 17 until Autumn 2024. So for a policy wording, something like 'the most recent PostgreSQL major version that is available prior to the first beta window for a Drupal major version'. This is very similar to what we're doing with PHP too.

However, there is no corollary for sqlite https://www.sqlite.org/changes.html they seem to do a minor release every few months on no particular schedule. So our policy will probably have to be 'pick an sqlite minor version based on feature set and availability' which is what it is now by default.

For MySQL, they seem to have announced a formal release schedule too: https://blogs.oracle.com/mysql/post/introducing-mysql-innovation-and-lon... This suggests an LTS every two years, with 8.0 as the current one and 8.4 as the next one. It does not look like 8.4 will be available before Drupal 11.0.0-beta1 though.

I don't think we can not support any MySQL LTS release (like requiring MySQL 8.1 now before 8.4 is available), but maybe once MySQL 8.4 is out, we could require MySQL 8.1 or something if there were specific features we want, on the basis that mass hosting is likely to enable MySQL 8.4 soon as the new LTS. But probably it's simplest to say 'the MySQL LTS available before the first major beta window' which will be MySQL 8.0.

MariaDB also has formalized their schedule a bit - they have LTS and one-year support minors, i.e. 10.6 and 10.11 are supported for five years, 10.7/8/9/10 supported for just one year. 11.x releases are happening but there is no LTS minor on that branch yet. https://mariadb.org/download/?t=mariadb&p=mariadb&r=10.11.6&os=Linux&cpu...

I mostly agree with #7, but with a few tweaks...

For MySQL, I think we should be the most conservative, which means I would prefer instead of:

'the MySQL LTS available before the first major beta window'

something more like 'the MySQL LTS that's been in an Ubuntu LTS for at least a year before the expected Drupal major release date'.
Given the alignment of Drupal's cycle with Ubuntu's cycle, in practice I think that'll often be the same thing, but in cases where MySQL releases an LTS after Ubuntu's feature freeze, or if Drupal changes its cycle to be less aligned with Ubuntu's, then my version is a bit more conservative.

For PostgreSQL:

the most recent PostgreSQL major version that is available prior to the first beta window for a Drupal major version

Except we want to set our requirements 3 months before beta. But given PostgreSQL's schedule of Sep/Oct, I think we could change this to the most recent PostgreSQL major version that is available 6 months before the expected Drupal major release date without that making a difference to the version that that corresponds to.

For MariaDB, I think we should get Pantheon's input into what their constraints are around adopting MariaDB versions.

For SQLite, I think we could potentially be as aggressive as "the version that's in the latest Ubuntu LTS before Drupal's beta", or as conservative as "the version that's in the latest Ubuntu LTS, Debian, and MacOS before Drupal's beta", or in between the two (e.g., Ubuntu and MacOS but not Debian). I think for any given Drupal major version we should base what we choose within that range on how important are the features in the newer SQLite release. For Drupal 11, SQLite 3.45 has jsonb which I think is important, so if that makes it into Ubuntu 24.04, I'd support that minimum even if it means early testers/users on Debian and Mac need to manually upgrade their sqlite or install a contrib driver. However, if Ubuntu's latest ends up being 3.44, then I don't think there's anything between 3.40 and 3.44 that's useful (I don't think json5 in db insert/update queries is useful to Drupal), so I'd go down to 3.40, or even to 3.39 in order to pick up the 2nd latest MacOS. Basically, where it costs us nothing to make things a bit easier for more people, I think we should do that where we can, but where core development/maintenance gets a tangible benefit from a higher minimum, we should choose that higher minimum within reason.