Choosing Between PostgreSQL, MySQL, SQLite, and MongoDB (2026)

# Choosing Between PostgreSQL, MySQL, SQLite, and MongoDB (2026) ## The four in one line each - **PostgreSQL** — relational, feature-rich, strong consistency. Default pick in 2026. - **MySQL / MariaDB** — relational, massive ecosystem, WordPress & legacy friendly. - **SQLite** — embedded relational, single file, no server. Great for small apps. - **MongoDB** — document database, flexible schema, JSON-native. ## Decision tree **1. Is your app ≤10K users + doesn't need multi-writer?** → SQLite. Don't overthink it. **2. Do you need ACID + complex queries + evolving schema?** → PostgreSQL. The safe default for apps with a future. **3. Is this WordPress / WooCommerce / Magento / Drupal / Joomla?** → MySQL. The CMS community assumes it. **4. Is data deeply nested, schemaless, frequently-changing?** → MongoDB (or Postgres with JSONB — usually wins). **5. Analytics / OLAP / time-series?** → ClickHouse, Postgres + TimescaleDB, or DuckDB — beyond this article's scope. ## PostgreSQL — the default Why most modern apps pick Postgres in 2026: - Rich data types: JSONB (with indexes), arrays, ranges, geometric - Advanced indexes: GIN, GIST, BRIN, HNSW (pgvector for embeddings) - Strong SQL compliance (window functions, CTEs, lateral joins) - Logical replication for zero-downtime migrations - Extensions: PostGIS, pgvector, TimescaleDB, pg_cron - Excellent concurrency (MVCC) Weaknesses: - More complex to tune than MySQL - Smaller community for "just works" tutorials (easier to find MySQL help in forums) Good for: most new projects, SaaS, B2B, analytics-heavy apps. See our [Postgres Performance Tuning guide](https://domainindia.com/support/kb/postgresql-performance-tuning-indexing-vacuum-partitioning). ## MySQL / MariaDB — the ubiquitous MySQL has 60%+ market share due to WordPress alone. MariaDB is MySQL's community fork — technically better but less universally supported. Why pick MySQL: - WordPress, WooCommerce, phpBB, PrestaShop — ecosystem assumes it - cPanel/DirectAdmin shared hosting ships it by default - Massive tooling (phpMyAdmin, Workbench) - Slightly simpler replication story Weaknesses: - JSON support weaker than Postgres JSONB - Stored procedures less flexible - Some SQL features missing (LATERAL, FILTER) Good for: CMS-based sites, legacy code, shared hosting customers, LAMP stack. ## SQLite — criminally underrated Conventional wisdom: "SQLite is for mobile/embedded, not production web." Modern reality (2026): SQLite serves production for many apps up to 10M users with WAL mode + smart patterns. **Why it works:** - Zero server, zero network, zero config - Single file = trivial backup, copy to staging, diff for debugging - 10× faster reads than networked Postgres for local data - Handles 10K writes/sec in WAL mode **When SQLite shines:** - Personal apps, side projects - Edge deployments (each edge has its own replica) - Read-heavy apps (95%+ reads) - Backup is cp /path/to/db.sqlite **When SQLite struggles:** - Many concurrent writers (WAL helps but not unlimited) - Distributed systems (horizontal scale needs tools like Litestream, rqlite, LiteFS) - Full-text search at huge scale (FTS5 is good but not Postgres GIN) **Tools to know:** - **Litestream** — streams SQLite to S3 / R2 for continuous backup - **LiteFS** — distributed SQLite via FUSE - **Turso** — hosted SQLite at edge Good for: internal tools, CMS for low-traffic sites, prototypes, "throw-away" apps. ## MongoDB — the document DB When your data is naturally hierarchical (product with variants, user with nested prefs) without strict schema, MongoDB feels natural. Strengths: - Flexible schema (but now Postgres JSONB matches this) - Native JSON-like documents - Horizontal scaling via sharding - Aggregation pipeline powerful for analytics Weaknesses: - Multi-document ACID only since 4.0 (2018) — still edge cases - Joins clunky (`$lookup` aggregation) vs SQL - Default security terrible (unauthenticated MongoDBs still exposed to internet) - Licensing (SSPL) complications for some use cases Good for: event logging, product catalogs, content with varied fields, quick-iteration startups. **2026 recommendation:** try Postgres with JSONB first — it covers 80% of MongoDB's use cases with relational safety. MongoDB only if you've hit real limits. ## Head-to-head on common tasks ### Full-text search For serious search: add Meilisearch or Elasticsearch regardless of primary DB. ### JSON columns Postgres JSONB matches MongoDB capabilities for most cases, inside a relational DB. ### Geospatial For GIS-heavy work, Postgres + PostGIS is unmatched. ### Transactions + ACID - **Postgres/MySQL:** Full ACID, multi-row transactions - **SQLite:** Full ACID, single-file single-writer - **MongoDB:** Single-document ACID; multi-document requires careful use of sessions ### Replication - **Postgres:** streaming + logical replication - **MySQL:** classic binary log replication - **SQLite:** Litestream for stream-to-S3 (one-way) - **MongoDB:** replica sets + sharding ### Backup - **Postgres:** pg_dump, PITR with WAL archiving - **MySQL:** mysqldump, MyDumper, Percona XtraBackup - **SQLite:** `cp` (or `.backup` for hot backup) - **MongoDB:** mongodump, snapshot ## Hosting on DomainIndia For most customers: shared cPanel / DirectAdmin with MySQL works. Upgrade to VPS when you need Postgres, MongoDB, or dedicated resources. ## Migration paths ### MySQL → PostgreSQL `pgloader` handles most migrations: ```bash pgloader mysql://user:pass@localhost/mydb postgresql://user:pass@localhost/mydb ``` Most tables transfer cleanly. Review: data types (TINYINT → BOOLEAN), `AUTO_INCREMENT` → `SERIAL`, utf8mb4 → UTF8. ### MongoDB → PostgreSQL Script your own migration: - Each collection → table (flatten top-level fields) - Nested docs → JSONB column - Arrays → PostgreSQL array or separate table Many have done this successfully. See "Why We Moved From MongoDB to PostgreSQL" blog posts — common pattern. ### SQLite → PostgreSQL When SQLite starts slowing for your app: ```bash # Dump to SQL sqlite3 old.db .dump > dump.sql # Edit: SQLite SQL is mostly Postgres-compatible # Load into Postgres psql mydb < dump.sql ``` Often a 10-minute operation for small apps. ## Common pitfalls ## FAQ