How to Download the Stack Overflow Database for Postgres
You wanna learn PostgreSQL, and you wanna use a sample database with big real-world data, the same kinds of issues that pop up with real-life data distribution skew, and fun query challenges.
StackOverflow.com provides a free data dump in XML format with their data as of 2024-04. The data is licensed under cc-by-sa 4.0, which means whenever you use it for public demos, you’ll need to link back to their original data source there, just as I did.
Some quick facts about the data:
- You’ll need Postgres 18 or higher, and about 250GB of free space
- users table: 22.5M rows
- posts (questions & answers): 60M rows
- votes: 239M rows (and there are more tables as well)
To get started, you’re going to need:
- Postgres 18 or higher, and about 250GB of free space
- A BitTorrent client to download it, like the free open source Transmission, available for macOS, Windows, and Linux
Open your BitTorrent client and either copy/paste in this magnet link, or this torrent file. It’ll start downloading the 24GB 7zip archive.
After the download finishes, extract the 7zip file and it’ll produce a ~124GB Postgres dump file (backup). If your OS doesn’t have a 7zip extractor built in, get the free open source 7-zip.
Create the database with the right collation – we’re using the same collation everywhere, Windows/Macs/Linux, to make sure everyone gets the same search behavior & results style:
|
1 2 3 4 5 |
CREATE DATABASE stackoverflow TEMPLATE template0 ENCODING 'UTF8' LOCALE_PROVIDER builtin BUILTIN_LOCALE 'C.UTF-8'; |
If you’re using DBeaver, refresh your database list to see the newly created database. (If you don’t see it, edit your connection and check the box for “show all databases.”) Then right-click on it, Tools, Restore. Point it at the .dump file (you’ll need to change your file mask to . to be able to see/click it.) Check the option do discard object owners, and if you have parallelism options, set the number of parallel jobs to your server’s core count to go faster.
If you’re using the command line in the folder where the dump file is located, use the below, taking care to replace 8 with the number of cores you have, <vm-ip> with the target server’s IP address, and <user> as your login username for the server.
|
1 |
pg_restore -h <vm-ip> -U <user> -j 8 --no-owner -d stackoverflow stackoverflow.dump |
If you have questions, post the exact error message below, and we’ll do our best to help get you straightened out. Happy querying!
Free, 3× a week
Get my new posts by email
Three posts a week, plus a Monday roundup of the best database news from around the web.