SQLite, PostgreSQL, DuckDB, Snowflake or BigQuery? Choosing the right database

"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

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

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. The skill is in moving data cleanly between them, which is exactly what analytics engineering is about.

Running PostgreSQL? Check whether your version is reaching the end of support in PostgreSQL 14 end of life, and what is next in PostgreSQL 19. For DuckDB's next version, read DuckDB 2.0.

Alessandro Ecclesie AgazziWritten by Alessandro Ecclesie Agazzi, analytics engineer in London. Updated 30 September 2026. About me