I built a fake 30-year company for a more realistic Postgres sample database

#postgres #sql #database #showdev

Every sample database has the same problem: it's either too small to be interesting or too abstract to stick in your head. pagila and Chinook are lovely, but you'll never stress a query planner with a few thousand rows. The StackOverflow dataset is genuinely great. But posts-and-votes only takes you so far, and I wanted data anyone could reason about on sight.

So I did the sensible thing and invented an entire company.

Meet Nick's Gaming Emporium

NGE is a fictional video-game retailer, simulated across its whole life, from 1986 to 2016. It opens in a strip mall, rides the dot-com wave, balloons through the console boom, and then does what most retailers eventually do: it dies. Founding, growth, decline, liquidation, the lot.

The company is invented. The catalogue isn't. The products are a real game-release list, so every title and release date is genuine. That one detail is what makes the sales data feel believable instead of random: you're not querying product_47, you're querying games you've actually heard of, selling in the years they actually came out.

It's a generator, but you don't have to run it

Under the hood NGE is a deterministic generator: same seed, same rows, every time. There's a matching SQL Server build from the same generator too, so the same data exists on both engines if you ever fancy comparing planners across the two.

Here's the part that matters on a Tuesday afternoon, though: you don't have to build anything. Each size ships as a ready-to-restore dump you just download.

Sizes, so you can match your hardware:

tiny / small: quick to restore on a laptop
medium: a solid single-machine dataset
large: ~3TB restored (a 260GB pg_dump -Fc), for when you actually want to watch things fall over at scale

What's in it

It's not one big sales table. NGE is a whole system:

OLTP: shops, staff, customers, sales, trade-ins, refunds, multi-currency
A data warehouse: a proper star schema with dimensions, facts and rollups, plus a reporting layer
A web layer: accounts, reviews, page views

So one dataset covers everything from "learn a JOIN" to "build a star-schema report" to "why on earth is this query spilling to disk."

Trying it (Postgres)

Grab a dump from the repo's README links, then:

createdb nge
pg_restore -j 4 -d nge nge_small.dump
psql -d nge -c "ANALYZE;"

Two things worth knowing. The dump carries no planner statistics, so run ANALYZE after restoring or your query plans will be nonsense. And there's no hard-coded collation baked into the schema, so it restores cleanly on any locale. I checked, so you don't have to.

It's deliberately imperfect

Worth being upfront about: the schema and indexing are not a textbook-perfect reference design, and they're not trying to be. They're meant to feel like a real system that grew, got patched, and accreted 30 years of decisions, the kind of database you actually inherit rather than the one from the textbook. There's plenty to tune, question, or argue with, which is more or less the point.

And, unavoidably, some nonsense

Because the company needed a reason to exist, it also got a backstory. There's a thoroughly ridiculous companion "documentary" fan-site, linked from the repo. Completely optional. But if your test data is going to have a founder, he may as well have a tragic third act.

DEV Community