Storing Scraped Data: Postgres vs Parquet vs a Warehouse

Most people store scraped data in whatever was easiest on day one, usually a single Postgres table, and then spend the next year fighting it. The table grows past what it was meant to hold, the analytics queries crawl, and every field change on the source site turns into a migration. The problem is not Postgres. The problem is asking one store to do three different jobs.

Scraped data does not have one right home. It has three, and the skill is knowing which slice of your data belongs in which. I run production scrapers, and pulling the data is only half the work. The other half is storing it so it stays queryable, deduped, and cheap as the volume climbs into the tens of millions of rows.

The three places scraped data lives

Think in three tiers, each solving a different problem.

Object storage holding Parquet files is the cheap, bottomless bucket for raw high volume capture, the stuff you want to keep but rarely query directly. Postgres is the operational layer, the deduped, indexed, current state you serve queries against every day. A warehouse like BigQuery or Snowflake is where analytics at scale lives, the place you run heavy aggregate questions over the whole history.

Almost all the storage pain I see comes from asking one of these to do another’s job.

Land the raw capture first

The first move is to separate raw capture from clean state. When the scraper pulls a page, do not parse it and throw the original away. Write the raw response somewhere cheap and untouched first, then parse from that copy.

I land raw html or raw json responses straight into object storage, one file per fetch or batched, before a single field gets extracted. The parse step reads from there, not from the live site.

The reason this matters is failure. Sites change their layout without warning, and your parser will break. If you kept only the clean parsed rows, a parser bug means you have to rescrape the site to recover, hammering it again and hoping the old data is still there. If you kept the raw capture, you just fix the parser and reparse the files you already have, offline, for free. Raw storage is cheap insurance against your own future mistakes.

Why Parquet for the raw and semi clean layer

You can land raw capture as plain json or csv, and for small jobs that is fine. At volume it hurts.

Parquet is a columnar file format. Instead of storing row by row, it stores all the values of each column together, which compresses far better because similar data sits next to itself. It is typed, so a number stays a number, and it carries its own schema. For large volumes you scan a few columns at a time, it is much smaller on disk and faster to read than csv or json, and every warehouse and analytics tool reads it natively.

The tradeoff: json preserves exactly what the site sent, which is what you want for the truly raw capture. So I keep json for the untouched landing, then convert to Parquet once the shape is known.

Postgres is your operational truth

Postgres is where the clean, current, queryable state lives. Once you have parsed a record, this is the row you actually use, look up, join against, and serve to whatever consumes the data. You get real indexes, real constraints, transactions, and sql everyone already knows.

For operational work, looking up one entity, filtering a few thousand rows, enforcing that a record is unique, nothing beats a boring relational database. Using Postgres is not the mistake. The mistake is trying to keep every raw byte you ever scraped inside it.

Dedup keys and idempotent writes

The most important thing you do in the operational layer is dedup, and it starts with a key. Every record you scrape needs a stable natural id, something the source itself defines: a product code, a listing id, a canonical url. That key tells you whether a row you just parsed is new or something you already have. Put a unique constraint on it.

Then make your writes idempotent, so running the same insert twice changes nothing the second time. Postgres gives you this with an upsert:

insert into products (id, title, price, raw)
values ($1, $2, $3, $4)
on conflict (id) do update
set title = excluded.title,
    price = excluded.price,
    raw   = excluded.raw;

On conflict with the key, it updates the existing row instead of creating a duplicate. This is what lets a pipeline retry safely: a run dies halfway, you start it again, and the records that already landed overwrite themselves cleanly instead of doubling. A stable key plus idempotent writes make a scraper safe to rerun, which you will do constantly.

Handling schema drift

Sites change their fields, and a rigid table will fight you over it. A source adds a column, renames one, or returns a value in a new shape, and suddenly your clean schema does not fit. This is schema drift, and you plan for it rather than getting surprised.

I keep a set of stable core columns I am confident about, the id and the fields I always need, and a jsonb column that holds the rest of the record as is. The structured columns stay clean and indexed for the queries that matter, and the messy, changing parts live in jsonb where a new field does not break anything.

When you have outgrown Postgres

Postgres is excellent right up until it is not. When a single table climbs past tens of millions of rows and your queries turn into big aggregates scanning most of it, a row store starts to struggle.

Counting across a hundred million rows, grouping by month over all of history, joining several large tables for a report: these are analytics questions. A database tuned for operational lookups answers them slowly, and at the cost of the operational work it should be doing. That slowdown is the signal you have outgrown one tier and need the next.

The warehouse is for analytics, not serving

That next tier is a warehouse, something like BigQuery or Snowflake. They are built for exactly the queries that make Postgres sweat, scanning and aggregating enormous columnar datasets. They separate storage from compute, so your data sits cheap and you only pay for the horsepower when a query runs. They read Parquet natively, so loading the raw layer in is straightforward.

The mistake in the other direction is serving live traffic out of a warehouse. Warehouses are built for big scans, not fast single row lookups, and they usually charge by how much data each query scans. Point an app at one for constant small reads and you get slow responses and a surprising bill. Keep operational serving in Postgres and heavy analytics in the warehouse, and let each do what it is good at.

The layered pattern I run

Here is how the three fit together in practice:

• The scraper lands raw responses as files in object storage, cheap and permanent.
• A parse step reads those, extracts clean records, and upserts them into Postgres keyed on a stable id. That becomes the deduped operational truth.
• On a schedule, the clean data loads into the warehouse as Parquet for analytics over the full history.

Raw for recovery, Postgres for serving, warehouse for analysis. Each layer feeds the next, and none is asked to do a job it is bad at.

Choosing by volume and query pattern

If you remember one rule, make it this: choose your store by volume and query pattern, not by habit.

Small data you look up and update row by row belongs in Postgres. Huge volumes of raw capture you rarely query directly belong in flat Parquet on object storage. Heavy aggregate analytics over everything belong in a warehouse. Cost often makes the call: object storage is cents per gigabyte a month, Postgres is a server you pay for whether or not it is busy, and a warehouse charges by how much each query scans. Most real pipelines want all three, and the healthy ones put each slice of data where its size and access pattern fit.

The honest limits

Storage does not change what you were allowed to collect. Public data, a robots file respected, an official api or bulk feed preferred, personal and paywalled data left alone. A clean layered warehouse full of data you should not have collected is still data you should not have collected. Good architecture makes your collection durable and cheap to keep. It does not grant permission.

I run this exact layering in production, raw in object storage, deduped state in Postgres, analytics in a warehouse. If you want the full guides, with real upsert patterns, Parquet partition layouts, and the tools I actually use and test, they are here.

Get new guides and videos first — join the Telegram channel.