Website database selection guide: MySQL, PostgreSQL, SQLite, NoSQL comparison

Website database selection guide: MySQL, PostgreSQL, SQLite, NoSQL comparison

Database is a core component of website architecture. Choosing right saves significant migration and optimization costs later. Many sites discover a year or two after launch that "the database I picked can no longer support the business," and database migration is among the most painful kinds of refactoring. This guide uses scenarios and numbers to settle the selection question up front.

Comparison

MySQL

Best for: WordPress sites, e-commerce, CMS systems.

Most widely used database for websites. Deep integration with WordPress, Drupal, Magento.

Key: Mature ecosystem, strong replication and HA options (InnoDB Cluster, Replication), MariaDB as a drop-in alternative, and affordable managed services everywhere (RDS, Cloud SQL).

Config optimization:

# /etc/mysql/my.cnf
[mysqld]
innodb_buffer_pool_size = 2G  # set to 60-70% of available RAM
innodb_log_file_size = 512M
innodb_flush_log_at_trx_commit = 2  # balance performance and durability
query_cache_type = 0  # query cache removed in MySQL 8.0
max_connections = 500

MySQL tuning is essentially about keeping hot data in memory. Once innodb_buffer_pool_size is raised, most reads hit memory and disk I/O pressure drops noticeably; innodb_flush_log_at_trx_commit = 2 is a performance-versus-durability trade-off — most sites can accept flushing the log every second in exchange for a big write-throughput gain. After changing the config, verify the hit rate with SHOW STATUS LIKE 'Innodb_buffer_pool_read%'.

PostgreSQL

Best for: complex analytics, geospatial applications, projects needing advanced SQL.

Key: Most advanced SQL features (window functions, CTEs, recursive queries). Strong extension system with PostGIS (geospatial) and pgvector (AI vectors). Excellent concurrency via MVCC, plus JSON/JSONB support so it can act as a document store.

Examples:

-- Window function example
SELECT name, department, salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) as rank
FROM employees;

-- Recursive query (org tree)
WITH RECURSIVE org_tree AS (
    SELECT id, name, parent_id, 1 as depth
    FROM org WHERE parent_id IS NULL
    UNION ALL
    SELECT o.id, o.name, o.parent_id, ot.depth + 1
    FROM org o
    JOIN org_tree ot ON o.parent_id = ot.id
)
SELECT * FROM org_tree;

PostgreSQL's other big advantage is "one database, many data models": create tables for structured transactional data, store JSONB for flexible fields, and add pgvector for similarity search. For small and mid-sized teams with limited budgets, this avoids the cost of maintaining several databases at once and reduces the pain of cross-database joins.

SQLite

Best for: small sites, development environments, mobile apps, embedded devices.

World's most deployed database engine. Serverless, zero configuration.

Limits: not suitable for high-concurrency writes (writers block each other), datasets larger than 1TB, or network access (local connections only).

A Real Migration Story

A content site launched quickly on SQLite, then grew from 200 to 20,000 daily actives within six months and started hitting "database is locked" errors — the classic symptom of SQLite's single-writer model under concurrency. The team migrated to PostgreSQL with pgloader; the SQL barely changed, and window functions even helped refactor a few slow queries. The whole migration, including data validation and a rollback plan, took under two days. The lesson: starting simple is fine, but keep a clear path for migration once scale arrives.

Another common mistake in database selection is over-engineering for a future that has not arrived — adopting distributed databases or sharding while the project still has only a few hundred users. For the vast majority of websites, a single 4-core/8GB PostgreSQL can handle traffic up to hundreds of thousands of daily actives. A more pragmatic approach: use a single database well first, build good indexes and caches, and evolve only when a real bottleneck appears.

Selection guide

• CMS (WordPress/Drupal) → MySQL/MariaDB
• Analytics/GIS needs → PostgreSQL
• Small site/prototype → SQLite (migrate later)
• Flexible data model → MongoDB
• High-speed cache → Redis + relational DB
• AI vectors → PostgreSQL + pgvector

FAQ

Q: MySQL or PostgreSQL?
A: If your project is dominated by the CMS ecosystem (WordPress, Magento), MySQL is the smoothest fit; for complex queries, analytics, GIS, or AI vectors, PostgreSQL is a better match. For the vast majority of new projects, PostgreSQL is a safe default.

Q: Can I use multiple databases at once?
A: Yes, but only when there is a clear benefit. A common combination is "relational store for primary data + Redis for cache"; mixing databases without a reason adds operations and consistency costs.

16IDC Takeaway

For most web projects, the recommended default is PostgreSQL. It offers the best balance of features, performance, and scalability. For the vast majority of website workloads, query optimization and indexing matter far more than database choice.

Related: Docker deployment guide | Security hardening guide

Reference: PostgreSQL official docs https://www.postgresql.org/docs/
Reference: MySQL Reference Manual https://dev.mysql.com/doc/
Reference: SQLite official site https://www.sqlite.org/docs.html