Exploring a New Postgres Database
At past jobs I’d estimate we had 100 different production apps that in some way were powering key production systems. Sure some were backend/internal apps while others key production apps such as the dashboard itself. At other companies we had a smaller handful of Heroku apps that powered our cloud service, about 5-10 in total. Even just working with those internal apps it’s a number of things to keep context on. But when it comes to interacting with something you don’t know getting a lay of the land quickly is key. In helping a customer optimize and tune, or even just understand what is going on in their app an understanding of the data model is key.
As I just started a few weeks back at Crunchy Data I found myself digging into a lot of new systems and quickly trying to ramp up and get a feel for them.
Over the past 10 years I’ve pretty well codified my steps to getting a feel for a new database. While I’m not a DBA and a small portion of my job is spent inside a database being able to quickly navigate one saves me hours each month and days out of the year. I’m sure my process isn’t perfect, but hopefully it helps other when navigating a new Postgres database for the first time.
First the tooling
For any new database my go to tool is psql. The built-in Postgres CLI is going to be the quickest thing for me to navigate around. If you use a CLI for anything else then this should be your preference here as well. I’m also going to have a
psqlrc file setup that has some good defaults. My go to defaults in my psqlrc are:
1 2 3 4 5 6 7 8 9 10 11 12
Getting a feel for the tables
The first thing I’m going to do is just table a look at which objects exist within the database with
\d. This will spit out a mix of tables, views, sequences all within your database. A cleaner version of this may be
\dt which is only tables:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25
We can also use the describe operation (
\d) on specific relations as well such as tables to get a feel for how they look:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23
Human readable output
Of course you may want to go one step further and actually get a sense for the data. Here a basic
SELECT tends to work with a limit 1. As you don’t quite know the shape of the data this is where having
\x auto setup within your
.psqlrc file is helpful to autoformat the output to your screen. You can also just manually run
\x auto in your SQL session to get cleaner output.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
Most databases I work with now leverage JSONB. It’s a great tool for mixing semi-structured data with more structured data.
Feel more at home when you encounter a new database
It doesn’t take hours of reading an ERD or schema files. In about 5-10 minutes of connecting to a new database I’m able to get a sense of how things are structured and able to actually start digging in. Don’t get me wrong, I’m by no means and expert in that time, but knowing some of these basic commands will really help the next time you encounter a database and are asked to help out or glean insights from it.