PostgreSQL, MySQL or SQLite?
Every relational database speaks SQL, so why are there so many? Because they are built for different situations. The question that matters most is not which is "best", but where the database runs and who connects to it.
The rule
Many app servers share a database server; one app can carry a file.
There are two shapes of relational database.
Client–server databases run as their own program, often on their own machine. Your application connects to them over the network, and many application servers can connect at the same time.
- PostgreSQL: open source, strict about data, with rich types (JSON, arrays, ranges) and extensions. A common default for new projects.
- MySQL: open source, very widely deployed, especially in web hosting. MariaDB began as a fork of MySQL and stays largely compatible with it.
- Microsoft SQL Server and Oracle Database: commercial products, common in large companies that already use those vendors' tools.
Embedded databases run inside your program as a library. SQLite is the famous one: the whole database is a single file, there is no server to install, and no network connection. It is used in phones, browsers, desktop apps, tests and small sites. Many processes can read it at once, but only one can write at any moment, and it cannot be shared over the network by several servers the way a database server can.
import sqlite3
conn = sqlite3.connect("bookshop.db") # a file on disk; created if missing
rows = conn.execute("SELECT title FROM books").fetchall()Most SQL carries across engines unchanged, but the details differ, which is why dialects matter. An auto-numbering id, for example:
-- PostgreSQL
id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY
-- MySQL / MariaDB
id INT AUTO_INCREMENT PRIMARY KEY
-- SQLite
id INTEGER PRIMARY KEY -- automatically fills in new idsThe shortcut
Ask two questions, in order:
- Will more than one machine need to write to this data? Yes → a client–server database.
- If not, is it one app keeping its own data? Yes → SQLite is often enough, and much simpler.
Then prefer what your team already runs well. Operating a database you know beats a "better" one you do not.
The public API
Three app servers must write orders to the same data at once. Question 1 is yes: PostgreSQL.
Drills
Answer fast — trust the rule.
How is SQLite different from PostgreSQL?
Four app servers behind a load balancer must all write orders. Which fits best?
How is MariaDB related to MySQL?
A table definition using AUTO_INCREMENT fails in PostgreSQL. Why?
Remember
- Many app servers share a database server; one app can carry a file.
- Ask: will more than one machine write? Then prefer what your team operates well.
- SQL is shared, but dialects differ; test on the engine you run in production.