Choosing the Right Database: PostgreSQL, MySQL, MongoDB, SQLite, or Redis?

Choosing a database is one of the few technical decisions in a project that is genuinely hard to reverse. Frameworks can be upgraded and servers replaced, but years of data, queries, and reports built around one database tend to stay there. The good news is that the decision is usually simpler than the online debates suggest. For most business applications, a relational database is the right core, and the real questions are which one, what to add next to it, and when. This guide walks through the options we use in client projects and the questions we ask before picking one.

Start with Questions, Not Products

Database comparisons usually start with features and benchmarks. In practice the choice is decided by a handful of questions about the application and the team behind it:

• How are the data related? Customers, orders, invoices, products, and stock are tightly connected, and the numbers have to add up. That points to a relational database with transactions.
• How much data and traffic is realistic in the next three years? Be honest. A system for a company with 200 employees or 50,000 customers is well within what a single PostgreSQL or MySQL server handles comfortably.
• What does the team already know? A database the team can tune, back up, and restore under pressure is worth more than a theoretically better one nobody has operated.
• Where will it run? Shared hosting, a VPS, Kubernetes, or a managed service such as Amazon RDS or Google Cloud SQL each narrow the options. If data has to stay in Indonesia, check that the provider has a Jakarta region.
• What will the data be used for besides the application? Finance reports, dashboards, exports, and integrations almost always need SQL, joins, and consistent numbers.

PostgreSQL: The Safe Default

If there is no specific reason to pick something else, we start with PostgreSQL. It is a fully open source relational database with strict transactions, a capable query planner, and a feature set that covers needs which used to require a second database. JSONB columns store and index flexible document data next to regular tables. Built-in full-text search handles simple search features. Extensions add geospatial queries through PostGIS and vector search for AI features through pgvector, so a retrieval-augmented chatbot can often run on the same database as the application.

Each major version receives fixes for five years. PostgreSQL 18 is the current release, and version 19 is planned for 19 November 2026. Note the other date this month: PostgreSQL 14 reaches end of life on 12 November 2026, so servers still running it will no longer get security fixes. Upgrades between major versions need a little planning, through pg_upgrade or logical replication, but they are routine.

MySQL and MariaDB: The Widest Ecosystem

MySQL runs a large share of the web, including WordPress and most shared hosting in Indonesia, and it works well with Laravel, Django, and every major framework. For a typical web application, MySQL with InnoDB is entirely adequate, and finding developers and hosting for it is easy. It is a sensible choice when the team knows it well or the hosting environment only offers MySQL.

The thing to check is the version. MySQL 8.0 reached end of life in April 2026. Oracle now offers two long-term support lines, 8.4 and 9.7, and applications still on 8.0 should move to one of them. An upgrade from 8.0 normally goes through 8.4 first. MariaDB, the community fork, is a separate product today, with its own long-term releases such as 12.3. It began as a drop-in replacement, but the two have diverged in areas such as JSON handling and replication, so moving between them should be treated as a migration and tested, not as a package swap.

SQLite: Smaller Than You Think, Stronger Than You Think

SQLite is not a server. It is a library that stores the whole database in a single file inside the application. That makes it the standard choice for mobile apps, desktop software, devices at the edge, and automated tests, and it is reliable enough to be used in aircraft and every smartphone. It also works for small websites and internal tools with modest traffic. Its limit is concurrency: many processes can read at once, but only one can write at a time. For a busy multi-user web application with a separate application server, a client-server database is the better fit.

MongoDB: When the Data Really Are Documents

MongoDB stores JSON-like documents instead of rows. It fits data whose shape varies from record to record and is usually read as a whole: a product catalogue where each category has different attributes, content and form submissions with changing fields, or event data from many sources. It scales out across servers with sharding, and it has supported multi-document transactions since version 4.0.

The trouble starts when MongoDB is chosen to avoid designing a schema. The schema still exists, only now it lives in the application code, and business data rarely stays independent for long. Once reports need to combine orders with customers, payments, and stock, teams end up writing joins by hand in code or copying data between collections and keeping the copies in sync. Its licence is also worth reading: MongoDB is distributed under the SSPL, which is not an open source licence under the OSI definition and restricts offering the database itself as a service.

Redis and Valkey: A Companion, Not a Primary Store

Redis keeps data in memory and answers in well under a millisecond. It is the usual choice for caching, sessions, queues, rate limiting, and leaderboards, and Laravel, Django, and Node.js applications use it heavily for exactly these jobs. It is not where business records should live: persistence to disk exists, but the design assumes that what is in Redis can be rebuilt from the primary database.

Its licence has changed twice in recent years. In 2024 Redis moved away from its open source licence, and a group of contributors backed by AWS, Google, and Oracle started Valkey, a fork under the BSD licence governed by the Linux Foundation. With Redis 8 in 2025, Redis added the AGPLv3 as an option, which some companies do not allow. For most applications both work the same way; managed services increasingly offer Valkey, and the choice usually comes down to licence policy and what your hosting provider supports.

At a Glance

When to Add a Second Database

Growing applications sometimes outgrow what one database does well, but the signal should be a measured problem, not a diagram. Before adding anything, check whether the slow part can be fixed with the right index or a better query. If not, these are the usual additions:

• Search with typo tolerance, relevance ranking, and filters across millions of products: a search engine such as Elasticsearch, OpenSearch, or Meilisearch, fed from the main database.
• Analytics dashboards over hundreds of millions of rows, where reports slow down the application: a column-oriented database such as ClickHouse, or a read replica as a cheaper first step.
• Large volumes of sensor or monitoring data over time: the TimescaleDB extension for PostgreSQL, or a dedicated time-series database.
• Caching and queues as traffic grows: Redis or Valkey next to the primary database.

Every extra database means another system to secure, back up, monitor, upgrade, and keep in sync. Add one when the gain is clear, and keep a single database as the source of truth.

Mistakes That Are Expensive to Undo

• Choosing for a scale the business will not reach. Distributed databases and sharding solve problems few companies have, and they make every day of development harder.
• Giving every microservice its own database before the domain is understood. Data that belongs together ends up split, and simple reports turn into integration projects.
• Running a database without tested backups. A backup that has never been restored is a hope, not a backup. Schedule a restore test at least every quarter.
• Staying on a version past end of life. MySQL 8.0 and PostgreSQL 14 are the ones to check this year, both on your own servers and in managed services, which eventually charge extra or upgrade for you.
• Picking a database because of one benchmark. Benchmarks measure one workload on one machine. Your queries, data, and indexes decide the real result.

A Short Decision Guide

1. 1Building a business application such as an ERP, an online shop, a booking system, or an internal tool: start with PostgreSQL, or MySQL if your team or hosting is built around it.
2. 2Building a mobile or desktop app that stores data on the device: use SQLite.
3. 3Storing large volumes of loosely structured documents that are rarely joined: consider MongoDB, and keep transactional business data in a relational database.
4. 4Needing speed for repeated reads, sessions, or queues: add Redis or Valkey next to the main database.
5. 5Hitting a measured limit in search or analytics: add a specialised engine for that one job and feed it from the main database.

A well-chosen database is one you stop thinking about: it is backed up, monitored, upgraded on schedule, and fast because the queries and indexes were designed with care. If you are starting a new system or wondering whether your current database still fits, our team is happy to review the options with you.