Where to put scraped data so you can actually use it

A folder with 118 dated files in it, one per day, all of them clean. The question was how many of the urls I saw in March were gone by June.

I could not answer it without writing a program. So I wrote one, and it opened all 118 files, held everything in memory, and produced a number about forty minutes later. What I had written was a database. A slow one with no indexes, and it could answer exactly that one question and nothing adjacent to it.

That is the moment this piece is about. The storage decision made by default on day one is usually correct on day one, and it has an expiry date almost nobody sees coming.

The folder is the right first move

One file per run, named with the date, appended to and then left alone. No server, no schema, no migration, no credentials. You can read it with anything, copy it by dragging it, and when a value looks wrong you open the file and look at the line that produced it.

For a source pulling a few thousand records a day this is the quickest correct answer available, and I still start every new source this way. Skipping it because it feels unserious costs people a week of setup they did not need yet.

The moment it expires

It is the same moment every time: the first question you want to ask that spans more than one file.

Everything inside a single day stays trivial. Row count for Tuesday. What this listing cost on the 14th. One file, one pass, done.

Then you want a comparison. What changed. What was there in March and gone by June. How often a field actually moves. Now you are reading 118 files at once and the thing you are writing is a query engine.

The trigger is the shape of the question, not the size of the disk. I have seen 400GB folders that never needed a database because every question was “what did we get yesterday”, and I have seen 2GB folders that needed one within a month.

Two layers, and the split is the whole design

There are two things to store and they have opposite requirements.

The raw capture is exactly what came back, stored as it arrived, never edited. Append only, literally: you do not go in and correct a bad row.

The derived table is parsed, typed, deduped, indexed. It is what you query. The rule that makes this work is that it can be deleted entirely and rebuilt from the raw layer whenever you want.

Keeping them apart is what buys you the right to be wrong later, which you will be.

The rebuild test

One question tells you whether you actually have two layers or one layer wearing two hats.

Delete the derived table right now. Can you have it back by tomorrow morning without touching the source site again?

If the honest answer is that you would have to rescrape, your parsed table is the only copy you own. Every parser bug you ship is then permanent damage to data you already paid for, and the site does not keep history on your behalf.

I had a selector go wrong on a price field and write the shipping cost into it for nine days before anyone noticed. Because the raw responses were on disk, the fix was a reparse that ran over lunch. Without them, nine days of history gone for good.

Raw means raw

The temptation, once you have a raw layer, is to fix things in it. You spot a broken row, you know what it should have said, you edit it.

Don’t. The moment raw becomes editable it stops being evidence, and the entire reason you kept it disappears.

Corrections are new rows. The old one stays wrong forever with its timestamp attached, and the derived table takes the newest.

The boring database is right for longer than you think

For the derived layer, use a relational database. Postgres, MySQL, SQLite if it is one machine with one writer.

Tens of millions of rows on a $200 box with an index on the columns you filter by answers in milliseconds. You get constraints, so bad data bounces at the door instead of landing quietly. You get transactions, so a half finished write leaves no debris to find on Monday. And everything speaks SQL.

People skip it because they buy for the volume they imagine rather than the volume they have. Somebody reads that relational databases fall over at scale, decides they will hit that scale eventually, and stands up a cluster before they have 100,000 rows. Now every question costs more to ask and there is an operational surface to keep alive.

Most scraping projects never leave the range where one boring database is the fastest thing in the building. You will know when you leave it, because a query you run daily will get slow and stay slow.

Columnar files, no server

Between a folder of JSON and a database server sits a third option that gets skipped: column oriented files on disk, which in practice means Parquet.

A query engine reads those files directly. You point it at a folder, you write SQL, and there is no server anywhere: no port, no user accounts, nothing to restart after a reboot. For analytics over a few hundred million rows on one machine this is genuinely good, and it costs nothing while nobody is asking anything.

What it will not do is serve. No unique constraints, no cheap single row lookup, and updating one value means rewriting a file. It answers questions about the pile. It does not hold your current state.

When a search engine is actually required

People stand up a search cluster years before they need one. My test is narrow on purpose.

Are you matching words you cannot list in advance, over free text, with results ranked by how well they matched? If yes, that is a search engine and nothing else does the job. Scoring text against a corpus is a different problem from filtering rows.

If your queries are equality and ranges, “this field equals that”, “this date between those two”, what you want is an index on a column. Adding a cluster for that buys you a second copy of your data to keep in sync and one more thing that can be down at 3am.

Either way the search index is derived, rebuilt from the real store on demand. The day you cannot rebuild it, you have made a search engine your source of truth, and search engines drop documents quietly and have no constraints to stop bad data landing.

What raw pages cost, in gigabytes

Store raw responses compressed. HTML squeezes to roughly a fifth of what came over the wire because it is repetitive text.

Now run the arithmetic once, because almost nobody does. Pages averaging 120KB compress to about 25KB. At 100,000 pages a day that is 2.5GB a day, 75GB a month, 900GB a year. One source, one crawler, unremarkable volume, and you have filled a terabyte in fourteen months without a single alert firing.

Retention gets decided on day one or by the disk

So decide how long you keep raw pages on the day you start keeping them.

The alternative is that the disk hits full at 3am, writes start failing, and you delete under pressure with no idea what mattered. Same decision, made worse, by someone tired.

Ninety days of raw covers essentially every parser bug you catch in time to act on. I have never needed raw HTML from eleven months ago. I have needed last Tuesday’s more times than I can count.

The two columns you will never regret

Put a fetch timestamp and a source identifier on every row. Every table, no exceptions, including the ones you are certain will never need them.

The timestamp is when you fetched it, not when the page claims it was published. Those are different values, the second one lies, and only the first lets you ask when something became wrong.

The source identifier is which site, which endpoint, which scraper wrote this row. It costs a few bytes.

The day you need them the reason is always identical: something in the derived table is wrong and you need to know which run wrote it and when. Without those two columns you cannot answer that at all.

Add a run id while you are in there. Then when you ship a bad parser you delete exactly the damage by run, instead of by date range and prayer.

The storage choice I regret

I kept raw HTML inside the database. Same table as the parsed row, in a text column, because it was one less thing to manage. It worked fine for months.

Then the nightly dump went from four minutes to about fifty. The table had become mostly bytes nobody ever queried, dragged through every backup, every restore, every vacuum, and sitting in cache pushing out the rows I read constantly.

Moving it to compressed files on disk took a weekend and the dump went back under five minutes. What I took from it: anything you never query does not belong inside the thing you query.

A backup you have not restored is not a backup

Nightly dumps to encrypted offsite storage is the easy half, and everyone does it. The half almost nobody does is pulling the backup back down, restoring it into an empty database, and counting the rows.

I run that check on a schedule now, because the first time I tried it the dump turned out to be months of a table that had been renamed underneath it. The job reported success the whole time. Exit code zero, file present, file useless.

The line storage does not move

None of this changes what you were allowed to collect. Public data, a robots file honored, a published crawl delay treated as an instruction, an official API or bulk feed used wherever one exists, and personal or paywalled material left alone.

A tidy two layer store full of data you should not have collected is still data you should not have collected.

The full written guides, table layouts, and the tooling I actually run in production are here.

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