Database Basics

Sooner or later a folder of CSV files stops working. Two people need the data at once, or a question comes up that pandas can only answer by reading 12 GB from disk. That is when you want a database. The first fork in the road is relational versus NoSQL, and for most coursework and most research projects the answer is relational.

Relational databases

A relational database stores tables. You decide the columns before you load any data, you say what type each column holds, and from then on every row in that table has exactly those columns. A students table has a student_id and a name for all one thousand rows, or the database refuses the write.

The word “relational” trips people up. It isn’t about relationships in the loose sense of things being connected. Tables are linked by matching values: your enrollments table stores the number 1001 in its student_id column, your students table stores 1001 in its own student_id column, and a JOIN lines them up when you ask. Nothing is stored twice and no pointers are involved.

Two you’ll meet constantly. MySQL sits under an enormous number of web applications and is the default on most cheap hosting. PostgreSQL is what most data teams pick now, because it keeps absorbing features you eventually want: window functions for ranking and running totals, JSONB columns for when part of your data really is shapeless, PostGIS for maps, and pgvector for embeddings.

Two tables, students and enrollments, linked by matching student_id values students student_id name 1001 Ada 1002 Ken enrollments student_id course 1001 DS 5100 1002 DS 5001 1001 DS 6013 student_id 1 student : many rows SELECT name, course FROM students JOIN enrollments USING (student_id) Every row has the same columns. Tables connect by matching values.

Two ideas do most of the work here. A transaction is a group of changes that either all happen or none of them do; if the server loses power halfway through, you never find half the work applied. A constraint is a rule the database enforces on every write, such as “student_id must be unique” or “gpa cannot be negative.” Constraints apply to everyone, including the teammate who didn’t read your README and the script you wrote at midnight.

What it’s good at

  • SQL transfers everywhere. The same SELECT you learn on Postgres works on MySQL, DuckDB, Snowflake, BigQuery, and Spark, with small dialect differences. Every BI tool speaks it, and pandas.read_sql() turns a query straight into a DataFrame. Few skills in this field pay off across as many tools.
  • Joins mean you store each fact once. An advisor’s name lives in one row of one table. Correct a typo there and every query that touches it is correct from that moment on.
  • The database refuses bad data. Types and constraints catch the duplicate student, the negative age, and the string that wandered into a numeric column, at write time rather than three weeks later in your analysis.
  • Indexes. A query that would scan fifty million rows can come back in milliseconds once you index the column you filter on. You add the index later, without changing the data.
  • Transactions make multi-step updates safe, which matters as soon as more than one process writes.

Where it hurts

  • You define the schema before you load anything, and changing it later is a migration you have to plan, test, and run in step with your code. On a large live table that is genuine work.
  • Data that genuinely varies in shape fits badly. A survey where each respondent answers a different subset of 200 questions turns into a table of mostly empty columns.
  • Writes scale up rather than out. You get one primary server, and the usual answers are a bigger machine or read replicas. Splitting writes across machines is real engineering.
  • Deeply nested data has to be flattened across several tables and rebuilt with joins on the way out. Postgres JSONB softens this, though at that point you are doing document storage inside a relational database.
  • You have to learn SQL. It’s worth it, but it is a real thing to learn on top of the Python you already know.

NoSQL databases

NoSQL is not one kind of database. It’s a label that stuck to a group of systems built in the late 2000s whose only shared trait was not being relational. The two you’re most likely to touch, MongoDB and Redis, have almost nothing in common with each other, so it’s worth taking them separately.

MongoDB, a document store

MongoDB stores documents, which for practical purposes are JSON objects. A collection is a pile of documents, and two documents in the same collection do not have to carry the same fields. If you’re pulling from an API that hands back JSON, you can store what it gave you without designing anything first.

One MongoDB collection holding two documents with different fields collection: students { "_id": 1001, "name": "Ada", "major": "DS" } { "_id": 1002, "name": "Ken", "major": "DS", } "courses": ["DS 5100"], "advisor": "Lee" Same collection, different fields. Nothing had to be declared first.

What it’s good at. Records whose shape varies are no trouble, and adding a field to new documents takes no migration. What you store looks like what your Python code already holds, so a dict goes in and a dict comes out with no translation layer in between. Fetching one whole record by its id is fast, since the entire thing sits in one place. It also shards across machines more readily than a relational database does.

Where it hurts. Joins are an afterthought; $lookup exists and it is not where Mongo is comfortable. Nothing stops your data from drifting, so one document ends up with "price": 10 and the next with "price": "10", and the only thing standing between you and that mess is your own application code. Duplication bites: if you copy an advisor’s name into 4,000 student documents and the advisor changes their name, you now own an update script. The aggregation pipeline is a genuinely capable query language, but it’s a second one to learn and it doesn’t transfer anywhere else the way SQL does. The pattern to watch for is storing data quickly now and finding it hard to query later.

Redis, a key-value store in memory

Redis keeps its data in RAM. You hand it a key, it hands back a value. The lookup itself takes microseconds, and even across the network you’re usually under a millisecond. It also understands a few useful shapes beyond plain strings: counters, lists, sets, and sorted sets, which is why leaderboards and rate limiters get built on it constantly.

Redis is rarely the place your data lives. It sits in front of something else as a cache, holds login sessions, counts requests per user, or passes jobs between processes as a queue. Set an expiry on a key and it cleans itself up.

A Redis instance holding three keys in memory, with an optional snapshot to disk Redis, everything held in RAM key value expires session:8f2a {"user": 1001} 30 min rate:1001 47 60 sec leaderboard sorted set, 10 items never optional snapshot to disk One key, one lookup. Restart without saving and it's empty.

What it’s good at. Speed, mostly, and the fact that there is very little to learn: SET, GET, INCR, EXPIRE and you’re productive. Putting it in front of Postgres can take a heavily repeated query off the database entirely. Expiry is built in, so caches and sessions clean up after themselves.

Where it hurts. Your data has to fit in RAM, and RAM is the expensive part of any server. Durability is optional and off by default in some setups, so treat anything in Redis as losable unless you have deliberately configured snapshots or the append-only log. There is no query language: you can fetch by key, and that’s the deal. You cannot ask it for every user in Virginia, because the values are opaque to it. And a cache is another moving piece that can serve you stale answers, which is its own category of confusing bug.

DuckDB, the one that reads your files where they sit

DuckDB belongs in neither box above, and it’s worth meeting early because it deletes a step you have probably been doing by hand for years.

It is a real relational database: SQL, tables, joins, window functions, all of it. It is also embedded, meaning it runs inside your own process, so there’s no server, no port, no user accounts, and no daemon to remember to start. pip install duckdb and you’re done. What sets it apart is what it’s tuned for. The databases above store a row at a time, which suits looking up and updating individual records; DuckDB stores data by column and processes it in batches, which is what makes it tear through aggregate queries over millions of rows on an ordinary laptop.

The part that changes how you work is that it queries data files directly. Nothing gets loaded or imported first.

SELECT species, avg(body_mass_g)
FROM 'penguins.csv'
GROUP BY species;

That is the entire program. No CREATE TABLE, no import step, no waiting. Replace the filename with 'data/*.parquet' and it reads a whole directory as one table. Point it at 's3://my-bucket/year=2026/*.parquet' and it reads straight from object storage, fetching only the columns and row groups your query actually touches rather than the whole file. On a 40 GB Parquet dataset where you asked for two columns and one date range, that is the difference between minutes and hours, and between a small egress bill and a memorable one.

DuckDB driven from a CLI, Python, R or a notebook, reading CSV, Parquet and JSON files locally, in S3, and in other databases drive it from duckdb CLI Python R notebook DuckDB one binary, no server SQL in, DataFrame out reads only what it needs it reads, in place data.csv, data.json data.parquet s3://bucket/*.parquet Postgres, MySQL The data never moves. One query can span a local file, a bucket, and a database.

Poking around by hand

Run duckdb with no arguments and you get a shell that prints proper boxed tables, so answering “what is actually in this file” takes one line instead of a notebook:

duckdb -c "DESCRIBE SELECT * FROM 'readings.parquet'"
duckdb -c "SELECT count(*) FROM 's3://my-bucket/logs/*.json'"
duckdb -c "FROM 'readings.parquet' LIMIT 20"

That last one is not a typo. DuckDB lets you start a query with FROM, which makes quick looks quicker. Recent versions also ship a local browser interface with duckdb -ui if you’d rather click through a dataset than type at it.

The same thing from code 

import duckdb

df = duckdb.sql("""
    SELECT station, date_trunc('day', ts) AS day, max(temp_c)
    FROM 's3://my-bucket/readings/*.parquet'
    WHERE station = 'CHO'
    GROUP BY 1, 2
""").df()

.df() hands back a pandas DataFrame, and .arrow() or .pl() give you Arrow or Polars instead. It works in the other direction too: a DataFrame sitting in memory is queryable by its variable name, so you can drop into SQL for the one join that’s awkward in pandas and come straight back out, without writing anything to disk.

import pandas as pd, duckdb

readings = pd.read_csv("readings.csv")
duckdb.sql("SELECT station, max(temp_c) FROM readings GROUP BY station").df()

The connector part goes further than files. DuckDB can ATTACH a running Postgres or MySQL database and query it as though its tables were local, which means one statement can join a Parquet file in a bucket against a table in your department’s Postgres. That is usually the moment people stop thinking of it as a database and start thinking of it as the thing that talks to everything else.

Where it hurts. It’s built for one process at a time. Several readers are fine, but it is not an application backend and it will not serve concurrent web traffic, so Postgres keeps that job. It runs in memory unless you hand it a file to persist to, and while it spills to disk for many operations that exceed RAM, not every operation does. It also moves fast as a project, having only reached 1.0 in 2024, so pin your version if you care about reproducibility.

Picking one

Situation Use
Questions about files you already have, local or in a bucket DuckDB
Analytics over millions of rows on one machine DuckDB
Records that all share the same fields Relational
You need to combine data from several places in one query Relational
Correctness matters more than raw write speed Relational
Anyone downstream will use SQL or a BI tool Relational
JSON from an API, shape varies per record MongoDB
Each record is read and written whole, and rarely joined MongoDB
A cache in front of a slower database Redis
Counters, sessions, rate limits, queues, leaderboards Redis

For a single-user project on your laptop, reach for DuckDB before you set up any server at all. There is nothing to install beyond a pip install, nothing to run, and it speaks ordinary SQL, so the queries carry over if you outgrow it and move to Postgres later. Plenty of analysis work never needs more than this.

Real systems commonly run two of these at once, and the split is usually the same: Postgres holds the authoritative data, and Redis holds a fast copy of whatever gets read over and over. Start with the relational database. Add something else when you can name the specific problem it solves.

Going further