Vai al contenuto
Home / Blog /In depth / Three databases instead of one: what changes with MySQL, PostgreSQL and SQLite

Three databases instead of one: what changes with MySQL, PostgreSQL and SQLite

Since 26.1 Maho runs on MySQL, PostgreSQL and SQLite. When PostgreSQL pays off, when SQLite is enough, and what to check before moving.

For twenty years the question did not come up: the database was MySQL, and if performance was a problem you bought a bigger server. Since 26.1 the answer has widened, because the move to Doctrine DBAL 4.4 brought official support for three engines: MySQL, PostgreSQL and SQLite. That is not a footnote: it changes what you can do with a development environment, with a small store and with a store taking peaks.

The command that picks the engine

Installation is where the choice is made, and it takes one line:

./maho install --db_engine mysql   # default
./maho install --db_engine pgsql   # PostgreSQL
./maho install --db_engine sqlite  # SQLite

From there the store speaks one dialect, the chosen one, and the rest of the installation does not change: same modules, same admin, same storefront. The choice is made once, and it is worth making while thinking about three different things: development, small production, large production.

SQLite: the environment you no longer want to configure

SQLite is a file. No service to start, no user to create, no permissions to sort out, and the backup is a copy. For a development environment it is the difference between starting in two minutes and spending half an hour explaining to a colleague where the database password lives.

It is not only a laptop convenience. The Maho demo store runs on SQLite, and reindex times came out at almost half of the MySQL ones. On a small catalog, with few concurrent writers, a store on SQLite is a real option, not an experiment.

The limit is the one you expect: concurrent writes. SQLite serialises them, and a store with many simultaneous orders finds out in the worst way, which is at Christmas.

🗄️ Note from the database: a database file is also why the backup stops being a procedure and becomes a command. The flip side is that two processes writing at once contend for it.

PostgreSQL: the engine for whoever already has the infrastructure

PostgreSQL brings what a large store has wanted for years: serious data types, more expressive indexes, a planner that uses statistics, and concurrency handling that does not rest on table locks. On a large catalog with searches and filters, the difference shows in catalog query response times.

The real reason to pick it, though, is usually not technical: the company already runs PostgreSQL, has a DBA who knows it, monitoring wired to it and backups already happening. A store living inside an existing infrastructure is worth more than a store on the “best” engine in the abstract.

What changes for whoever maintains it

Three things, in order of practical weight.

Hand written queries. The core is portable, a third party module is not necessarily. If an extension writes SQL with MySQL specific functions, it will not work on PostgreSQL, and no configuration fixes that. Before changing engine, the only question for the extension vendor is this: does the module use the abstraction layer, or does it write raw SQL?

The tooling. Backups, monitoring, log collection and restore procedures all have to be rebuilt for the new engine. A backup plan nobody has ever tried is a hypothesis, not a plan.

The rehearsal. A store does not move by changing a parameter and looking at the home page. It needs a full order, a refund, a credit memo, an export, an import and a complete reindex.

🗄️ Note from the database: the full reindex is the test that unmasks everything. It is the part touching the most tables at once, and if a badly written query exists, that is where it stops.

The patches that made the support real

Support for three engines does not end with the release that introduces it. Later patches worked on the details that separate “it runs” from “it runs well”: the price index fixed on PostgreSQL and SQLite in 26.7.2, and the reindex rewrite that takes advantage of the parallel lanes on both engines. That is the kind of work nobody sees in a release note, and that you feel when the store is under load.

How to choose, in three questions

  1. Who writes to the database? If it is only the store and volumes are small, SQLite is a serious answer.
  2. What is already around? If the company runs PostgreSQL or MySQL with backups and monitoring, that is the answer, unless there are strong reasons otherwise.
  3. Which extensions do you use? If they write raw SQL, the current engine is the prudent choice until the vendor declares support.

🗄️ Note from the database: the right question is not which database is better, it is which database you can maintain at three in the morning.

Text prepared with the help of AI and checked against the source before publication. It can still contain mistakes: if you find one, report it.Read the manifesto

Does your store still sell?

Tell us where you are starting from: we reply with a first assessment, no strings attached.

Ask for the store assessment