Skip to content

feat: Switch to PostgreSQL

Mario Oenning requested to merge postgres into master

Change our DB backend from MariaDB to PostgreSQL.

For easier transition (codebase + data migration) we lowercase column names and use unquoted table names. This allows us to continue to use PascalCase in the codebase for the ORM as well as manually written SQL statements (git hook scripts) while at DB level everything is lowercase.
We might want to still change this in the future and f.e. switch to snake_case...

In terms of performance we should be pretty much on-par with MariaDB, although there are a few scenarios that are either in favour one or the other: So somewhat of a mixed-bag really. Now there is probably still some room for improvement/fine-tuning for postgres. Some benchmarks can be found linked below.

ToDo:

  • Change documentation

ToDo - Migration:

  • Change aurweb ansible playbook at infra (MR infrastructure!769). We can combine these with a new release also.
  • Prepare Migration plan/instructions
  • Performance testing old vs. new (create script with benchmarks which can be run on sandbox environments)
  • Test migration on sandbox
  • Write additional tests: case sensitivity (default collation in postgres is case sensitive as opposed to mariadb)
Edited by Mario Oenning

Merge request reports