"Which database should we use?" is one of the most common questions I am asked, and the honest answer is: it depends on what the data is for. The first distinction to make is between transactional and analytical workloads.
Transactional versus analytical
| Transactional (OLTP) | Analytical (OLAP) | |
|---|---|---|
| Typical work | Many small reads and writes: place an order, update a stock level | Few large reads: total revenue by month for three years |
| Storage layout | Row by row | Column by column |
| Examples | PostgreSQL, MySQL, SQLite | Snowflake, BigQuery, DuckDB |
| Serves | Applications | Reports, dashboards, data science |
Columnar analytical databases are dramatically faster for aggregations because they only read the columns a query needs, and compress similar values together.
The contenders
SQLite
A complete database in a single file, with no server to run. It powers countless mobile and desktop apps, including most iPhone apps under the hood. Ideal for local tools, prototypes and embedded use; not designed for many simultaneous writers.
PostgreSQL
The dependable, open-source default for applications. Rich SQL, strong consistency, JSON support and an enormous extension ecosystem. It can handle moderate analytics too, and it is increasingly offered by the major data platforms as a companion to their warehouses.
DuckDB
Often described as "SQLite for analytics": an in-process, columnar database that runs inside Python, R or the command line and queries CSV and Parquet files directly. Excellent for data science, local development and datasets up to hundreds of gigabytes on a laptop.
import duckdb
# Query a folder of Parquet files directly, with no loading step
duckdb.sql("""
SELECT product, SUM(amount) AS revenue
FROM 'sales/*.parquet'
GROUP BY product
ORDER BY revenue DESC
LIMIT 10
""").show()Snowflake
A managed cloud data platform with storage and compute separated, strong governance, data sharing and near-zero administration. A strong fit when several teams need a single, secure source of truth. Read the Snowflake guide for the essentials.
BigQuery
Google's serverless warehouse: no clusters to size, and you can pay per amount of data scanned. It integrates tightly with Google Analytics exports and the rest of Google Cloud.
How to choose
| If you need to... | Start with |
|---|---|
| Store data for an app or website | PostgreSQL (or SQLite for a small, local app) |
| Analyse files on your own machine quickly | DuckDB |
| Build a shared source of truth for a company | Snowflake or BigQuery |
| Report on Google Analytics or Google Ads data | BigQuery |
| Keep costs near zero for a small business | DuckDB, PostgreSQL, or well-structured spreadsheets feeding Power BI |
Questions to ask before deciding
- How much data is there now, and in three years?
- Who will query it, and with which tools?
- Does it need to be updated in real time, or is daily enough?
- Who will maintain it? A managed service costs more per unit but less in people's time.
- What are the security and data residency requirements?
Many teams use more than one: PostgreSQL behind the application, a warehouse for reporting, and DuckDB on analysts' laptops. The skill is in moving data cleanly between them, which is exactly what analytics engineering is about.
Written by Alessandro Ecclesie Agazzi, freelance analytics engineer in London. Updated 30 September 2026.