To choose between PostgreSQL, MySQL and SQLite, you compare four operator criteria: the license, the LTS series you will install, what replication can do without an external tool, and the tooling available to back up and monitor. The three engines speak SQL, but they do not share the same nature (SQLite is a library, the other two are servers) or the same license and version policy. This lesson gives you the criteria and a decision table. It is aimed at an administrator who is about to install an engine and must be able to justify the choice to their team.
What you will learn
- Distinguish an embedded database from a database server
- Compare the licenses of PostgreSQL, MySQL and SQLite and what they allow
- Choose an LTS series and know what support guarantees you
- Assess the built-in replication of each engine
- Place MariaDB in relation to MySQL
- Justify an engine choice with operator criteria
What is the scope of this lesson?
This lesson does not go back over the relational model, query writing or transactions: the SQL training (in French) teaches them, and it is a prerequisite of this training. Isolation levels are in its lesson on transactions and concurrency (in French), and the ACID guarantee (all or nothing) is placed in the Relational databases page (in French). Here, we only look at what changes for the person who runs the engine: where it runs, who can use it, how it is backed up and repaired.
An engine choice is rarely made with the syntax in mind. It is made when you have to answer concrete questions: who fixes the security flaws, until when, under which license, and what happens when the server goes down?
Which criteria separate the three engines?
The table below summarises the operator criteria. The first row explains almost all the others: SQLite is not a server but a library linked into the program, so there is no process to start, no account to create, no port to open and no replication to configure. PostgreSQL and MySQL are network services, which bring multi-user access and high availability at the price of an installation and ongoing administration. The acronyms in the table (WAL, GTID, binlog) are defined in the sections that follow.
| Criterion | SQLite | PostgreSQL | MySQL |
|---|---|---|---|
| Nature | Embedded library, one local file | Server, systemd service | Server, systemd service |
| License | Public domain | PostgreSQL License (free, permissive) | GPL v2 (Community), commercial license (Enterprise) |
| Series followed | A single version, built into the application | One major version per year | LTS series (8.4) or Innovation series |
| Built-in replication | None | Streaming (physical) and logical replication | Source/replica with GTID, Group Replication, InnoDB Cluster |
| Automatic failover | Not applicable | External tool required (Patroni) | InnoDB Cluster, with MySQL Shell |
| Backup | File copy, .backup command | pg_dump, pg_basebackup, WAL archiving | mysqldump, MySQL Shell utilities, binlog |
| Access control | File permissions | Roles, pg_hba.conf | user@host accounts |
What does the license change for the operator?
A license decides what you can do with the engine in your product. SQLite is in the public domain, with no constraint. PostgreSQL is published under the PostgreSQL License, permissive and close to the BSD and MIT licenses: you can embed it, modify it and redistribute it, including in a closed commercial product. MySQL Community is under GPL v2, whose obligations (providing the source code) are triggered when you distribute the engine or a product that includes it, not when you run it. A commercial license (Enterprise edition) lifts this constraint for the vendor. For an internal service, none of the three gets in the way; for a vendor who ships the engine with their software, this is the deciding criterion, and a lawyer must then read the license, because the line between "integrating" and "talking to the server" cannot be summed up in a single sentence.
Which LTS series should you install?
An LTS (Long Term Support) series receives security fixes for several years, which saves you from upgrading with every release. MySQL 8.4 is an LTS series. According to the MySQL manual, an LTS series follows the Oracle Lifetime Support Policy: 5 years of Premier Support then 3 years of Extended Support, eight years in all. Oracle also publishes Innovation series, "supported until the next Innovation release" and intended for teams that want new features as soon as possible; the manual describes them as "production-grade quality", but their short cycle forces you to upgrade often, which is the criterion to weigh. PostgreSQL has no LTS label: it publishes one major version about once a year, each supported for five years after its release. The choice therefore comes down to a specific major version, here PostgreSQL 18 (released on 25 September 2025, supported until 14 November 2030), and to an upgrade plan before that date. Snapshot of 2026-10-20: the latest published minor release of the 18 series is 18.6 (13 August 2026), and the latest published 8.4 is 8.4.12 (18 August 2026).
Is built-in replication enough for high availability?
Replication copies writes from the primary (the server that accepts them) to one or more replicas. What varies is what the engine does on its own when the primary goes down. PostgreSQL includes streaming replication, which continuously ships the WAL (the log where the engine records every change before applying it), and logical replication, which replays changes table by table. But automatically promoting a standby requires an external tool such as Patroni, a third-party project under the MIT license (it is not a component of PostgreSQL) that relies on a distributed configuration store (etcd, Consul or ZooKeeper). MySQL natively offers source/replica replication with GTID (a global identifier given to each transaction, which tells a replica where to resume), as well as Group Replication and InnoDB Cluster, which you drive with MySQL Shell: an InnoDB Cluster groups at least three instances, automatically promotes a replica if the primary goes down, and MySQL Router redirects clients to the new primary. SQLite has no replication: one file, one writer at a time. The lessons on PostgreSQL replication (in French) and MySQL replication (in French) detail each mechanism.
Which backup and monitoring tooling can you expect?
The criterion is not that a tool exists, but that it exists in the edition you
are allowed to install. PostgreSQL provides pg_dump for logical backup,
pg_basebackup for physical backup and WAL archiving for point-in-time
recovery. MySQL Community provides mysqldump, the MySQL Shell utilities
and the binary logs (binlog, the log of changes, which can be replayed to
replicate or restore), whereas Oracle's physical backup tool, MySQL
Enterprise Backup, is reserved for customers of the Enterprise edition. In
Community, hot physical backup of InnoDB goes through a third-party tool such as
Percona XtraBackup (open source, GPL v2). Monitoring is comparable:
statistics views (pg_stat_activity) on one side, Performance Schema on the
other. The lessons on
PostgreSQL backup
(in French) and
MySQL backup
(in French) put them into practice.
How do access rights differ from one engine to another?
Access rights do not separate the engines, but they surprise you most when you
switch engines, because they do not show in the schema: tables transfer, rights
do not. PostgreSQL reasons in roles (a user is a role with LOGIN) and
filters network connections in pg_hba.conf. MySQL ties each account to a
host ('alice'@'10.0.0.%'). SQLite has no users: access is set with the
file permissions on the host. The lessons on
PostgreSQL hardening
(in French) and
MySQL hardening
(in French) cover the subject.
When should you choose SQLite, PostgreSQL or MySQL?
Each engine has a context where it is the right choice, and that context is described by the most rigid constraint of the project, not by the team's preference.
SQLite fits when the database belongs to a single program: an inventory script, a command-line tool, a mobile or embedded application, a prototype. There is nothing to administer on the server side, and only one process writes at a time. As soon as several services must write at once, or the database must survive the loss of the machine, you need a server. The SQLite guide (in French) covers installation, WAL mode and the limits.
PostgreSQL is the default choice of this training. It fits business applications with complex data (JSON, geometries, advanced constraints), infrastructure tools that can store their state in it (Keycloak and Forgejo, for example) and teams that want a permissive license. MySQL fits classic web stacks (WordPress, Drupal, Magento), shared hosting that offers only MySQL and teams that already operate a MySQL fleet.
What about MariaDB?
MariaDB is a fork of MySQL created in 2009 by Michael "Monty" Widenius,
one of MySQL's original developers, after Sun Microsystems bought MySQL AB
(Oracle later absorbed Sun), to keep a MySQL that stays open and independent.
It shares the network protocol and much of the syntax, but the two engines have
diverged on replication (Galera for MariaDB, Group Replication for
MySQL), on the default authentication plugin (caching_sha2_password for MySQL
8.4, mysql_native_password for MariaDB according to their respective
documentation) and on Performance Schema. Many distributions ship it instead of
MySQL in their default repositories, which surprises people at installation. As
of 2026-10-20, the MariaDB LTS series are 12.3, the most recent, then 11.8,
11.4 and 10.11. This training does not teach MariaDB: the
MySQL installation page
(in French) compares the two engines.
How do you decide in a real case?
You start from the most rigid constraint, then look at the freedom that remains. An application shipped with its engine, or a vendor that supports only one, makes the choice for you. Shared hosting makes it too. When several engines fit, the criterion that most often breaks the tie is team skill: a badly operated PostgreSQL gives worse service than a well-mastered MySQL.
| Your constraint | Engine | Why |
|---|---|---|
| A single program writes, no administration wanted | SQLite | No server, no account, no port |
| Business application, state of an infrastructure tool | PostgreSQL | Permissive license, rich data types, extensions |
| Shared hosting or existing web stack on MySQL | MySQL | The ecosystem is already there |
| Vendor embedding the engine in a closed product | PostgreSQL or SQLite | No redistribution obligation |
| Built-in automatic failover without an external tool | MySQL (InnoDB Cluster) | Driven by MySQL Shell |
| No constraint | PostgreSQL | Default choice of this training |
A correct answer to the question "why this engine?" cites two criteria among the license, the LTS series, replication and tooling, not a slogan like "it is faster".
Key takeaways
- SQLite is an embedded library, PostgreSQL and MySQL are servers.
- The license decides whether you can redistribute the engine: public domain, permissive PostgreSQL License, GPL v2 or commercial for MySQL.
- MySQL 8.4 is an LTS series; PostgreSQL publishes one major version per year, supported for five years.
- Replication is built into both servers, but PostgreSQL's automatic failover goes through an external tool.
- Oracle's physical backup tool (MySQL Enterprise Backup) is reserved for the Enterprise edition; in Community, you use a third-party tool such as Percona XtraBackup.
- Access rights do not transfer with the tables: roles,
user@hostaccounts or file permissions. - MariaDB is not MySQL: it has diverged on replication and authentication.
- You justify a choice with operator criteria, never with a preference.
Knowledge check
Check what you have learned with this interactive quiz
How it works
- The timer starts when you click Start
- Multiple choice, true/false and short answer questions
- You can move between questions
- Detailed results are shown at the end
Starts the quiz and the timer
Review
(0/0)Score by domain
A domain groups several guides and gets several questions, so its score is more reliable than a per-topic score, which often rests on a single question.
Summary
Strengths
No topic passed at 80% on at least two questions yet.
Weak areas
No topic below the pass mark.
Recommended next
Hints before you try again?
Continue the course: PostgreSQL administration: install, configure and secure
A new full quiz with random questions
Work again only on the questions you missed