During the lectures in this class, I give you queries that you can follow along with on your own machine if you want. I also give you lab exercises that require you to run queries and modify tables.

To do this, you're going to need Postgres, a client tool, the Stack Overflow database, and the check_indexes script.

If you're not comfortable installing things like this on your machine or a server you own, that's fine, you can just watch the class without doing the hands-on work. That's totally okay too. But if you are okay installing things, I'm going to point you to the resources, but I don't have detailed walkthroughs on how to accomplish each step. (This is an index tuning class, not a how-to-install-Postgres class - hope that's fair.)

Install Postgres

There are no minimum hardware requirements for our database - even just a Docker VM is fine as long as it has ~10GB of space. If you've already got a Postgres server or access to one where you can restore databases and run queries, you're set, and you can move on to the next section.

If you're on a Mac, I recommend the free Postgres.app, which I find to be the easiest way to run Postgres on a Mac, especially multiple versions.

For everyone else, here are the official Postgres installers.

If you'd like to use a Docker container or a cloud service, that's totally fine too, as long as you can restore a ~10GB database into it.

Install a Client Tool to Run Queries

In the class, I use the free DBeaver Community Edition for running my queries. It's a fairly friendly database GUI that works on Linux, Mac, and Windows.

However, you can use any client tool you're comfortable with, like pgAdmin, psql, DataGrip, Azure Data Studio, etc.

If you don't have any client tools installed yet, stick with DBeaver because it's the easiest to install, uninstall, and get started with.

Download and Restore the Stack Overflow Database

This training class (like all of mine) uses the free Stack Overflow Postgres database. It's built from StackOverflow.com's free public data dump with the contents of their database. I love using it for demos because it's real-world data with real-world problems.

The 1.5GB download file is below for the small version of the Stack Overflow database. It's the same one I distribute publicly at SmartPostgres.com. If you already have that one, or the large one, you don't need to re-download this:

To restore the database with DBeaver, right-click on any existing database and click Tools, Restore:

Check the box for Create Database, and click the folder next to Backup File to locate the Stack Overflow backup you just downloaded. The file picker window may default to .backup - change that to .* or .sql so you can click on the file. Click Start, and DBeaver may throw a warning that it might corrupt a database - click OK.

The restore will take a couple minutes because it's restoring about 1.5GB of data, then creating indexes.

You may get errors - as long as they're only about permissions, you're fine. You can close the wizard.

After the restore finishes, in DBeaver's database list, right-click on the word Databases and click Refresh, and the Stack Overflow database will appear.

If you're using any client tool other than DBeaver, I'll leave the restore up to you since you know your tool better than I do. ;-)

Download and Install check_indexes

In this class, we'll be analyzing indexes with the free check_indexes script from our Bag of Tricks Github repo. Run that script in the Stack Overflow database that you just restored, and the check_indexes function will get created in there.

Congratulations! You're ready to get your learn on.