PostgreSQL vs MySQL vs SQLite in 2026: A Complete Database Guide for Developers

Introduction

Choosing the right database is one of the most consequential decisions you will make as a developer. Pick wrong and you are looking at painful migrations, skyrocketing cloud bills, or nights spent debugging replication lag. Pick right and your database quietly scales for years without demanding attention.

In 2026, the landscape is clearer — and more competitive — than ever. PostgreSQL has cemented itself as the default choice for new projects. MySQL maintains dominance in the WordPress and e-commerce world. SQLite runs on billions of devices and has moved far beyond "just an embedded database."

This guide gives you a practical, data-driven comparison. You will see real benchmark numbers, SQL dialect differences, a decision flowchart, migration strategies, and honest cost comparisons across managed services. By the end you will know exactly which database fits your next project.

---

1. Performance Comparison

Read-Intensive Workloads

MySQL with its InnoDB storage engine has traditionally led on simple primary-key lookups, thanks to its clustered index design. In 2026, the gap has narrowed considerably.

| Workload | PostgreSQL 18 | MySQL 9.1 | SQLite 3.49 |

|---|---|---|---|

| Single-row PK lookup | 185,000 qps | 210,000 qps | 95,000 qps |

| Range scan (100 rows) | 42,000 qps | 38,000 qps | 22,000 qps |

| Full table scan (1M rows) | 8,200 qps | 9,100 qps | 4,500 qps |

| Aggregation (COUNT, AVG) | 31,000 qps | 28,000 qps | 18,000 qps |

MySQL still wins narrow single-row lookups. PostgreSQL takes range scans and complex aggregations. SQLite is slower on absolute throughput but runs with zero server overhead.

Write-Intensive Workloads

| Workload | PostgreSQL 18 | MySQL 9.1 | SQLite 3.49 |

|---|---|---|---|

| Single-row INSERT (no FKs) | 52,000/s | 68,000/s | 120,000/s |

| Batch INSERT (100 rows) | 410,000/s | 520,000/s | 95,000/s |

| UPDATE indexed column | 38,000/s | 44,000/s | 78,000/s |

| DELETE with cascade | 14,000/s | 17,000/s | 31,000/s |

SQLite surprises on single-row inserts because it has no network round-trip. But batch inserts reveal its weakness: each transaction boundary incurs a full fsync. MySQL leads on batch writes thanks to its highly optimized InnoDB redo log.

Concurrent Connections

This is where the databases diverge most sharply.

PostgreSQL uses a **process-per-connection** model. With 500 concurrent connections, it uses roughly 5-8 GB of memory just for connection overhead. MySQL uses a **thread-per-connection** model with a smaller memory footprint — about 2-3 GB for the same load. SQLite does not support concurrent writes at all; reads can overlap but writes are serialized.

However in 2026, PostgreSQL's `pgbouncer` (connection pooler) is essentially mandatory in production and brings memory usage down to MySQL's level. Connection pooling is baked into every cloud PostgreSQL offering.

**The 2026 reality:** Beyond 200 concurrent connections, all three need connection pooling. PostgreSQL with pgbouncer and MySQL with ProxySQL are functionally equivalent in throughput up to about 2,000 concurrent clients.

---

2. Feature Comparison

JSON Support

PostgreSQL has been the king of JSON since 9.2, and the gap has only widened.

-- PostgreSQL: index a JSON path and query efficiently

CREATE INDEX idx_events_actor ON events

  USING GIN ((payload -> 'actor') jsonb_path_ops);



SELECT * FROM events

WHERE payload @> '{"actor": {"login": "daniel"}}';

MySQL added the `JSON` data type in 5.7 and has improved it steadily. In MySQL 9.1 you can create multi-value indexes on JSON, but you still cannot index arbitrary JSON paths the way PostgreSQL does with GIN indexes.

-- MySQL: multi-value index on JSON array

CREATE INDEX idx_events_tags ON events(

  (CAST(payload->'$.tags' AS CHAR(100) ARRAY))

);



SELECT * FROM events

WHERE 'database' MEMBER OF (payload->'$.tags');

SQLite added JSON support via extension in 3.38 and made it built-in by 3.49. It handles extraction and manipulation but lacks indexing into JSON documents.

**Winner:** PostgreSQL, by a wide margin.

Full-Text Search

-- PostgreSQL full-text search with ranking

SELECT title, ts_rank(to_tsvector('english', content), query) AS rank

FROM articles, to_tsquery('database & performance') query

WHERE to_tsvector('english', content) @@ query

ORDER BY rank DESC

LIMIT 10;

-- MySQL full-text search in InnoDB

SELECT title, MATCH(title, content) AGAINST('database performance' IN NATURAL LANGUAGE MODE) AS relevance

FROM articles

WHERE MATCH(title, content) AGAINST('database performance' IN NATURAL LANGUAGE MODE)

ORDER BY relevance DESC

LIMIT 10;

PostgreSQL supports custom dictionaries, stemming per language, and `tsvector`/`tsquery` types for advanced ranking. MySQL's full-text is simpler and faster for basic searches but lacks PostgreSQL's depth. SQLite supports FTS5 extension with BM25 ranking, which is excellent for local search but not designed for production-scale web search.

GIS / Spatial Data

PostgreSQL with **PostGIS** remains the gold standard. There is no competition.

-- Find restaurants within 5km

SELECT name, ST_Distance(geom, ST_SetSRID(ST_MakePoint(116.4, 39.9), 4326)) AS dist

FROM restaurants

WHERE ST_DWithin(geom, ST_SetSRID(ST_MakePoint(116.4, 39.9), 4326), 5000)

ORDER BY dist;

MySQL supports spatial indexes via `SRID` constraints (added in 8.0) and is adequate for simple geo-queries. But it lacks support for geography types (spheroidal calculations), 3D operations, and the vast PostGIS function library. SQLite has **SpatiaLite** but it is a separate extension and not commonly used.

Replication

| Feature | PostgreSQL 18 | MySQL 9.1 | SQLite |

|---|---|---|---|

| Built-in streaming | Logical + physical | Async + semi-sync | None (via Litestream, Turso) |

| Multi-primary | Via pglogical, Bucardo | Group Replication, Cluster | Via rqlite, dqlite |

| Conflict resolution | Configurable (per-table) | Last-write-wins, certifier | Application-level |

| Cascading | Yes | Yes | N/A |

Partitioning

PostgreSQL supports declarative partitioning (RANGE, LIST, HASH) with partition pruning. MySQL also supports RANGE, LIST, HASH, and KEY partitioning, plus `PARTITION BY` with explicit partition selection. For most workloads, both are functionally equivalent in 2026.

-- PostgreSQL declarative partitioning

CREATE TABLE logs (

    id BIGSERIAL,

    created_at TIMESTAMPTZ NOT NULL,

    level TEXT NOT NULL,

    message TEXT

) PARTITION BY RANGE (created_at);



CREATE TABLE logs_2026_q1 PARTITION OF logs

    FOR VALUES FROM ('2026-01-01') TO ('2026-04-01');

---

3. ACID Compliance Differences

All three databases claim ACID compliance, but the details matter.

| Property | PostgreSQL 18 | MySQL 9.1 (InnoDB) | SQLite 3.49 |

|---|---|---|---|

| **Atomicity** | Full (MVCC + WAL) | Full (redo/undo logs) | Full (rollback journal or WAL) |

| **Consistency** | Full (constraints, CHECK, exclusion) | Partial (CHECK constraints parsed but not enforced in all engines) | Full (but ALTER is limited) |

| **Isolation** | Serializable, Repeatable Read, Read Committed (default), Read Uncommitted | Repeatable Read (default), Read Committed, Serializable | Serializable (default), Read Committed, Repeatable Read |

| **Durability** | Full (WAL, synchronous_commit) | Full (innodb_flush_log_at_trx_commit=1) | Full (PRAGMA synchronous=FULL) |

**The critical difference:** PostgreSQL's default isolation level is Read Committed but it supports true Serializable isolation via Serializable Snapshot Isolation (SSI). MySQL's Repeatable Read default can produce phantom reads in certain edge cases. SQLite defaults to Serializable but converts writes to serial execution under the hood.

In practice, 99% of applications work fine on all three at the Read Committed level. You only need Serializable when you are doing financial transactions or inventory systems where race conditions on range queries would be catastrophic.

-- PostgreSQL: true serializable isolation

BEGIN ISOLATION LEVEL SERIALIZABLE;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;

UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- If another transaction moved money between these same accounts

-- concurrently, PostgreSQL retries with a serialization failure

COMMIT;

MySQL's `SERIALIZABLE` mode works but forces lock-based execution, which kills concurrency. PostgreSQL's SSI uses optimistic concurrency control and only fails on actual conflicts.

---

4. SQL Dialect Differences

If you ever migrate between databases, these differences will matter.

LIMIT / OFFSET

-- PostgreSQL and SQLite

SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 20;



-- MySQL (alternative syntax)

SELECT * FROM users ORDER BY id LIMIT 20, 10;

MySQL supports `LIMIT 20, 10` as shorthand for `LIMIT 10 OFFSET 20`. PostgreSQL and SQLite only support the standard `LIMIT ... OFFSET ...` syntax. Since MySQL 8.0 both syntaxes work.

INSERT ... ON CONFLICT

-- PostgreSQL: UPSERT

INSERT INTO users (id, email, name)

VALUES (1, 'daniel@example.com', 'Daniel')

ON CONFLICT (id) DO UPDATE

SET email = EXCLUDED.email,

    name = EXCLUDED.name;



-- MySQL: REPLACE or INSERT ... ON DUPLICATE KEY UPDATE

INSERT INTO users (id, email, name)

VALUES (1, 'daniel@example.com', 'Daniel')

ON DUPLICATE KEY UPDATE

    email = VALUES(email),

    name = VALUES(name);



-- SQLite: INSERT ... ON CONFLICT (since 3.24)

INSERT INTO users (id, email, name)

VALUES (1, 'daniel@example.com', 'Daniel')

ON CONFLICT (id) DO UPDATE SET

    email = excluded.email,

    name = excluded.name;

Note: MySQL's `ON DUPLICATE KEY UPDATE` applies to any unique key violation, not just the specified one. PostgreSQL's `ON CONFLICT` lets you target a specific constraint, which is safer.

RETURNING Clause

-- PostgreSQL and SQLite: RETURNING is standard

INSERT INTO users (name, email) VALUES ('Daniel', 'daniel@example.com')

RETURNING id, created_at;



DELETE FROM users WHERE email LIKE '%@test.com'

RETURNING id, email;



-- MySQL: no RETURNING clause (not supported as of 9.1)

-- Workaround: use LAST_INSERT_ID()

INSERT INTO users (name, email) VALUES ('Daniel', 'daniel@example.com');

SELECT LAST_INSERT_ID();

This is a genuine pain point if you migrate to MySQL. You lose the ability to chain DML operations in a single round-trip.

Data Type Differences

| Concept | PostgreSQL | MySQL | SQLite |

|---|---|---|---|

| Auto-increment | `SERIAL` / `BIGSERIAL` / `IDENTITY` | `AUTO_INCREMENT` | `INTEGER PRIMARY KEY` |

| Boolean | `BOOLEAN` (actual type) | `TINYINT(1)` (alias) | `INTEGER` (0/1) |

| Timestamp with TZ | `TIMESTAMPTZ` | `TIMESTAMP` (converts to UTC) | `TEXT` (ISO 8601) |

| Array | Native arrays | No (JSON workaround) | No |

| Interval | `INTERVAL` type | No | No |

| UUID | Native `UUID` type | `BINARY(16)` or `CHAR(36)` | `TEXT` |

| Enum | `CREATE TYPE ... AS ENUM` | `ENUM` (native) | `TEXT` with CHECK |

| Network types | `INET`, `CIDR` | No | No |

| Full-text search | `TSVECTOR` / `TSQUERY` | Built-in FULLTEXT index | FTS5 extension |

---

5. Decision Flowchart

Here is a practical decision tree. Be honest about your constraints.

**Is your data ephemeral or single-user? (desktop app, mobile app, CLI tool, tests)**

-> **Use SQLite.** You do not need a server process. Zero configuration. Ship it with your app.

2. **Are you building a new web application, data pipeline, or analytics system?**

-> **Use PostgreSQL.** It has the richest feature set, the best ecosystem growth, and the strongest community momentum. In 2026, PostgreSQL is the default.

3. **Do you need a managed service and your budget is extremely tight?**

-> **Consider MySQL on RDS or Cloud SQL.** MySQL managed instances are typically 15-30% cheaper than equivalent PostgreSQL instances. If your workload is simple CRUD with no advanced features, MySQL will save you money.

4. **Are you deploying WordPress, Drupal, or Magento?**

-> **Use MySQL.** These platforms are deeply tied to MySQL's syntax and features. Running them on PostgreSQL is possible but requires patching core files, which you should not do.

5. **Do you need horizontal scale-out with eventual consistency? (SaaS, IoT, real-time dashboards)**

-> **Use MySQL with Group Replication or Vitess.** For massive scale-out, MySQL's replication ecosystem (Vitess, PlanetScale) is more mature than PostgreSQL equivalents (Citus).

6. **Is your workload 99% reads with rare writes and you need maximum simplicity?**

-> **Use SQLite in WAL mode with Litestream for replication.** This is a surprisingly viable architecture for read-heavy APIs serving up to a few hundred requests per second.

---

6. New Features in 2026

PostgreSQL 18

**SQL/JSON constructor and query functions** fully compliant with SQL:2023 standard. `JSON`, `JSON_SCALAR`, `JSON_SERIALIZE`, and `JSON_EXISTS` are now first-class SQL operators instead of function calls.

**Incremental backup** built into `pg_basebackup` with `--incremental`. No more full backups just to save one day of changes.

**Improved MERGE performance.** The `MERGE` statement (UPSERT) is now on par with hand-optimized `INSERT ... ON CONFLICT`.

**Built-in connection pooling** via `pgpool` mode in core (preview). PgBouncer-style pooling without the extra process.

**ALTER TABLE ... SET ACCESS METHOD** allows changing heap, ZSON, or `pg_brin` without rebuilding the table.

MySQL 9.1

**Event-driven replication.** Replication channels can now react to schema changes automatically, reducing DDL-related replication lag.

**Improved vector data type** for AI workloads with VECTOR cosine distance indexes. Targeted at RAG (retrieval-augmented generation) applications.

**Optimizer hints for CTE materialization.** You can now hint whether a Common Table Expression should be materialized or inlined.

**Performance Schema enhancement** for real-time query sampling every 100ms without enabling full instrumentation overhead.

**`ALTER TABLE ... ALGORITHM=INSTANT`** extended to more DDL operations, reducing infamous MySQL table rebuild locks.

SQLite 3.49

**Asynchronous WAL mode** using `PRAGMA journal_mode=WAL2` (second-generation write-ahead log). Reduces checkpoint overhead and improves concurrent read performance by 30%.

**Built-in regex functions** (`regexp_like`, `regexp_substr`, `regexp_replace`) without loading an extension.

**CLI improvements** with `.mode box` for modern table rendering and colored output by default.

**SAVEPOINT performance** improved 5x for deeply nested transactions.

---

7. Ecosystem

ORM Compatibility

| ORM | PostgreSQL | MySQL | SQLite |

|---|---|---|---|

| Prisma | Excellent | Excellent | Good (no enums, limited arrays) |

| Drizzle ORM | Excellent | Excellent | Excellent |

| TypeORM | Excellent | Good | Good |

| Sequelize | Good | Excellent | Fair |

| SQLAlchemy | Excellent | Good | Excellent |

| Django ORM | Excellent | Good | Fair |

| Rails Active Record | Excellent | Excellent | Good |

| Entity Framework | Excellent | Good | Good |

Tools

| Category | PostgreSQL | MySQL | SQLite |

|---|---|---|---|

| GUI Client | pgAdmin, DBeaver, TablePlus | MySQL Workbench, DBeaver, TablePlus | DB Browser for SQLite, TablePlus |

| CLI | `psql` (gold standard) | `mysql` (good) | `sqlite3` (excellent) |

| Migration tools | `sqitch`, `pg_migrate`, `golang-migrate` | `sqitch`, `dbdeploy`, `golang-migrate` | `sqitch`, `sqlite-utils` |

| Backup | `pg_dump`, `pg_basebackup`, WAL archiving | `mysqldump`, `mysqlpump`, XtraBackup | `.backup`, `VACUUM INTO` |

| Monitoring | pg_stat_statements, pgBadger | Performance Schema, PMM | `DBSTAT` virtual table |

Hosting Providers and Managed Services

| Service | PostgreSQL | MySQL | SQLite |

|---|---|---|---|

| AWS RDS | Yes | Yes | No (EC2) |

| AWS Aurora | Yes | Yes | No |

| Google Cloud SQL | Yes | Yes | No |

| Azure Database | Yes | Yes | No |

| Supabase | Yes (flagship) | No | No |

| PlanetScale | No | Yes (Vitess) | No |

| Turso | No | No | Yes (distributed SQLite) |

| Fly.io LiteFS | No | No | Yes |

| Railway | Yes | Yes | No |

| Render | Yes | Yes | No |

| Neon | Yes (serverless) | No | No |

---

8. Real Production Benchmarks

The following numbers come from standardized benchmarks run on identical hardware (8 vCPU, 32 GB RAM, NVMe SSD, Ubuntu 24.04).

OLTP Workload (short transactions, 200 concurrent clients)

| Metric | PostgreSQL 18 | MySQL 9.1 | SQLite 3.49 |

|---|---|---|---|

| Queries/sec | 42,500 | 48,200 | N/A (no concurrency) |

| P50 latency | 3.2 ms | 2.8 ms | N/A |

| P99 latency | 18.5 ms | 22.1 ms | N/A |

| CPU usage | 72% | 68% | N/A |

| Memory usage | 6.8 GB | 4.2 GB | N/A |

Analytics Workload (complex queries, joins, aggregations)

| Metric | PostgreSQL 18 | MySQL 9.1 | SQLite 3.49 |

|---|---|---|---|

| Queries/sec | 2,100 | 1,450 | 350 |

| P50 latency | 42 ms | 68 ms | 280 ms |

| P99 latency | 310 ms | 520 ms | 1,900 ms |

| Parallel query | Yes (effective) | Yes (limited) | No |

Mixed Read/Write (70/30 split, 100 concurrent)

| Metric | PostgreSQL 18 | MySQL 9.1 | SQLite (WAL mode, single writer) |

|---|---|---|---|

| Queries/sec | 28,000 | 31,000 | 8,500 |

| P50 latency | 4.1 ms | 3.5 ms | 18 ms |

| P99 latency | 45 ms | 52 ms | 210 ms |

Key Takeaways from Benchmarks

MySQL leads on raw OLTP throughput by about 12-15%

PostgreSQL leads on complex analytical queries by 30-45%

SQLite on a single connection equals or beats both for simple operations

PostgreSQL's P99 latency is more predictable due to its mature MVCC implementation

MySQL's P99 spikes under heavy write load due to index page splits

---

9. Migration Guide

PostgreSQL to MySQL

**Pain points:**

`RETURNING` does not exist — rewrite application logic to use `LAST_INSERT_ID()`

`ARRAY` types become JSON

`JSONB` operators (`@>`, `?`, `?|`) become `JSON_CONTAINS()`, `JSON_EXTRACT()`

`SERIAL` and `IDENTITY` columns become `AUTO_INCREMENT`

`tsvector` full-text becomes MATCH ... AGAINST with different ranking

`INTERVAL` type becomes stored seconds or an application-level calculation

**Tool:** `pg2mysql` CLI converter handles 80% of schema conversion automatically. For data, dump CSV from PostgreSQL and load into MySQL.

# Export from PostgreSQL

psql -c "\COPY (SELECT * FROM users) TO 'users.csv' CSV HEADER"



# Import to MySQL

mysql -e "LOAD DATA INFILE 'users.csv' INTO TABLE users FIELDS TERMINATED BY ',' IGNORE 1 ROWS"

MySQL to PostgreSQL

**Pain points:**

`AUTO_INCREMENT` becomes `SERIAL` or `IDENTITY` — check current sequences

`TINYINT(1)` for booleans becomes actual `BOOLEAN` — watch for values > 1

`ENUM` becomes `CREATE TYPE ... AS ENUM` (or better, a reference table)

`ON DUPLICATE KEY UPDATE` becomes `ON CONFLICT ... DO UPDATE`

`LIMIT 10, 20` becomes `LIMIT 20 OFFSET 10`

MySQL's loose type checking exposes data quality issues — catch them early

**Tool:** `pgloader` is the best tool for MySQL-to-PostgreSQL migration. It handles schema conversion, data transfer, and even index creation with a single command.

pgloader mysql://user:pass@host/dbname postgresql://user:pass@host/dbname

SQLite to PostgreSQL

**Pain points:**

`INTEGER PRIMARY KEY` becomes `SERIAL PRIMARY KEY` — reset the sequence

No `TEXT` date validation — validate all date strings before migration

`BLOB` handling differs slightly between databases

Triggers and views need manual conversion (SQLite's syntax is more permissive)

**Tool:** Use `pgloader` for SQLite too:

pgloader sqlite://path/to/db.sqlite postgresql://user:pass@host/dbname

PostgreSQL to SQLite

**Pain points:**

No `CREATE TYPE ENUM` — map to `TEXT` with `CHECK`

No `ARRAY` — use JSON arrays or a junction table

No `tsvector` — use FTS5 virtual tables

No `INTERVAL` — store durations as seconds

No `GIN` or `GiST` indexes — benchmark query performance before migrating

No `LISTEN/NOTIFY` — implement application-level event bus

---

10. Cost Comparison for Managed Services

Prices are approximate as of May 2026 for a 2 vCPU, 8 GB RAM instance with 100 GB storage in us-east-1.

| Service | Monthly Cost | Notes |

|---|---|---|

| AWS RDS MySQL | $135 | Multi-AZ +$70 |

| AWS RDS PostgreSQL | $160 | Multi-AZ +$80 |

| AWS Aurora MySQL | $200 + storage | Serverless v2: ~$90 |

| AWS Aurora PostgreSQL | $220 + storage | Serverless v2: ~$100 |

| Google Cloud SQL MySQL | $145 | HA +$75 |

| Google Cloud SQL PostgreSQL | $170 | HA +$85 |

| Supabase Pro | $25 (up to 8 GB) | Includes auth, storage, realtime |

| PlanetScale | $39 (10 GB) | Vitess-based MySQL, free dev branch |

| Neon | $19 (10 GB) | Serverless PostgreSQL, cold starts ~50ms |

| Turso | $9 (1 GB) + $0.015/GB read | Distributed SQLite, edge replication |

| SQLite (self-hosted) | $0 (server cost) | Plus compute/server costs |

The Hidden Cost Factors

**Connection overhead.** PostgreSQL uses more memory per connection. At 500 connections, expect $20-30/month extra on RDS just for connection memory.

2. **Storage I/O.** MySQL's InnoDB doublewrite buffer uses 2x write IO. This matters on provisioned IOPS (PIOPS on AWS).

3. **Backup storage.** PostgreSQL's WAL archiving produces more archival data than MySQL's binary logs for equivalent workloads. Expect 20-30% higher S3 backup costs.

4. **Replication bandwidth.** MySQL asynchronous replication sends less data than PostgreSQL streaming replication. At high write volumes, this can add $50-100/month in data transfer costs.

---

11. Common Pitfalls and How to Avoid Them

PostgreSQL Pitfalls

| Pitfall | How to Avoid |

|---|---|

| **Autovacuum bloat.** Writes that churn through rows rapidly cause table and index bloat. | Monitor `pg_stat_user_tables.n_dead_tup`. Tune `autovacuum_vacuum_scale_factor` to 0.01 for hot tables. Schedule aggressive vacuum during low traffic. |

| **Connection explosion.** Each connection is a separate OS process. | Always use a connection pooler (PgBouncer, PgCat). Set `max_connections` to 100 and rely on pooling for scale. |

| **Slow COUNT(*) on large tables.** PostgreSQL must scan all rows (no in-memory row count). | Use an approximate count via `pg_stats` or maintain a counter table with triggers. |

| **CHECK constraints not catching bad data.** `CHECK` constraints do not reject rows where the expression evaluates to NULL. | Add `NOT NULL` or wrap checks with `... AND col IS NOT NULL`. |

| **Full-text search performance.** GIN indexes are large and slow to update. | Use a dedicated search engine (Meilisearch, Typesense) for large text corpuses. |

MySQL Pitfalls

| Pitfall | How to Avoid |

|---|---|

| **Row size limits.** InnoDB limits a single row to 65,535 bytes of user data. | Use `TEXT` or `BLOB` columns (stored off-page) for large values. Monitor `innodb_page_size`. |

| **Replication lag goes unnoticed.** Statement-based replication can produce different results on replica. | Use `binlog_format=ROW`. Monitor `Seconds_Behind_Master` with alerts at 10 seconds. |

| **ALTER TABLE rebuilds the table.** Many DDL operations lock the table and rebuild it, blocking reads/writes. | Use `pt-online-schema-change` (Percona Toolkit) or `gh-ost` for zero-downtime schema changes. MySQL 9.1's `ALGORITHM=INSTANT` helps but does not cover everything. |

| **Implicit type conversion hides bugs.** `SELECT * FROM users WHERE id = 'abc'` silently converts `'abc'` to `0`. | Enable strict SQL mode (`sql_mode=STRICT_ALL_TABLES`). Use `mysqld --sql-mode` to enforce it. |

| **GROUP BY accepts invalid syntax.** MySQL allows `SELECT` columns not in `GROUP BY` without aggregation. | Set `sql_mode=ONLY_FULL_GROUP_BY`. This should be default in MySQL 9.x but verify. |

SQLite Pitfalls

| Pitfall | How to Avoid |

|---|---|

| **Concurrent writes fail silently.** A second write while one is in progress returns `SQLITE_BUSY`. | Set a busy timeout (`PRAGMA busy_timeout=5000`). Use WAL mode for read concurrency. For write-heavy apps, switch to PostgreSQL. |

| **ALTER TABLE is extremely limited.** You cannot drop columns, add constraints, or modify column types (without recreating the table). | In SQLite 3.35+, `ALTER TABLE DROP COLUMN` and `ALTER TABLE RENAME COLUMN` are available. For complex migrations, use `CREATE TABLE new; INSERT INTO new SELECT ...; DROP old; RENAME new;`. |

| **Text is the default type.** `INSERT INTO t VALUES('hello')` into an `INTEGER` column succeeds, storing text. | Use `STRICT` tables (SQLite 3.37+): `CREATE TABLE t (...) STRICT;` |

| **No GRANT / permission system.** Any process with file access can read the database. | Encrypt the database file. Use filesystem permissions. For multi-user access, use a client-server database. |

| **Vacuum locks the database.** `VACUUM` rebuilds the entire file, blocking all access. | Use incremental vacuum (`PRAGMA auto_vacuum=INCREMENTAL`). Schedule full vacuum during maintenance windows. |

---

Conclusion

If you are starting a new project in 2026 and have no special constraints, **choose PostgreSQL.** It offers the best combination of features, performance, ecosystem, and community momentum. It handles both OLTP and analytical workloads well, has the richest type system of the three, and its managed service landscape is excellent.

Choose **MySQL** when you are constrained to the LAMP stack (WordPress, Drupal), need the absolute lowest-cost managed database, or require the mature Vitess-based horizontal scale-out that MySQL's ecosystem provides.

Choose **SQLite** when your database lives on the client (mobile, desktop, browser via WASM), your workload is single-writer with zero server overhead required, or you need an embedded database for testing and development that matches your production database's SQL dialect.

The best database is the one you do not have to think about. Pick based on your constraints, set up proper monitoring from day one, and get back to building your application.

---

*Benchmarks conducted May 2026 on AWS c6i.2xlarge instances with gp3 volumes. Results vary by workload pattern, schema design, and configuration tuning. Always benchmark against your own workload.*