Postgres Tools: psql and the Web Console

There is no shortage of Postgres clients and I have settled on two: psql on the command line, and whatever web console the hosted provider gives me. Most of what I do is looking at data while debugging something, and changing the schema.

The console is the one I reach for first, and it is the one that let me down. On tables of around 100,000 rows it got slow and then crashed on me, so the tool I was using to understand a problem became a second problem. psql on the same database did not notice the size at all. Separately, a migration I ran failed halfway and left the schema in a state that neither the old version nor the new one expected, which is a worse outcome than it simply refusing to run.

The console is a reading tool

For a quick look at a small table, a browser console is the fastest thing available. No connection string, no terminal, no remembering anything. That convenience is real and I use it every week.

It stops being the right tool at a size that arrives sooner than you expect. 100,000 rows is not a big table by any serious measure, and it was enough to make the page crawl and then fall over. The database was fine the whole time; the browser was the bottleneck. Once you learn that boundary, the console stays useful and you stop asking it to do the heavy part.

psql is worth the half hour it takes to get used to

psql looks unwelcoming because it tells you nothing. The commands you need are backslash ones, and no part of the screen hints at that. Learn the handful that list tables, describe a table and show indexes, and you have a client that is already installed on every machine with Postgres and does not care how large anything is.

It is also the better place to be when something goes wrong, because it tells you which statement failed rather than turning the problem into a browser error.

The migration lesson

Mine failed partway and left the schema half changed. That is the state worth designing against, because it is not a failure you can simply retry: the next run meets a database that is neither where it started nor where it was going.

What I would tell anyone running a schema change on something that matters: run it in a transaction so a failure rolls all of it back, take the backup before rather than after, and run it from psql where you can see which statement died. A browser tab is a bad place to be standing when a migration stops halfway.

What to actually install

Nothing, to start with. You have psql already, and the hosted console came free with the database. Use the console for looking and psql for changing, and add a heavier graphical client only when you can name the thing those two cannot do for you.

That is the same test I apply to any tool that wants to sit between me and my data, which is how I ended up moving a file into a real database in the first place when a spreadsheet started rewriting my numbers.