Example database datasets (for learning Postgres)

Dev Zone Backend Development
, ,
1

Hello,

I wanted to learn more about postgresql’s dialect and how all of its magical queries like window functions, over keyword and etc. work.

There are good examples in the documentation I found:

However I need the database to try out how stuff works. I’ve already tried looking this stuff in google but didnt find anything.

Anyone has any good resources?
I’m not only looking for the toy example schemas, some that weight a few GB would also be very useful.

1 Like
2

The gold standard for testing databases are made by the Transaction Processing Council (TPC).
They have created a bunch of tests over the years allowing vendors to compete on different typical data loads, such as transaction processing or decision support. Their tests come with a schema and a tool that generates data for any data volume you would like to test. The queries to be used in their benchmarks are also tool generated.

You can go the hard route and download one or more of their tests (TPC-C, TPC-H, TPC-DS) and figure out how to make it work with Postgres.

Here is a page that describes how to use the venerable TPC-H benchmark: TPC-H - PostgreSQL wiki

Or you can take a shortcut and simply use the built-in benchmark that ships with PostgreSQL (pgbench). This one, to not run afoul of the TPC, is only loosely based on TPC-C.

Cheers!

4 Likes
3

Thank you for the answer, it sounds like this is exactly what I was looking for.