PSQL, TOOLS

Some psql tools

Three psql related tools which I have tested this year.

pgenv

pgenv is to postgres what pyenv to python. From their github repository:

pgenv is a simple utility to build and run different releases of PostgreSQL. This makes it easy to switch between versions when testing applications for compatibility.

It is actually pretty simple to install different psql versions and get started. One of its contributors wrote a nice article to read together with the official documentation.

Since I am a docker user, this tools does not bring much benefit to my toolbox. If there were a server without docker which run a selfhosted psql database it could be of use. But so far there is no room on my toolbox for this one.

pg_activity

pg_activity is a monitoring tool for postgres databases. It looks and feels like top. The tool can be connected to local and remote databases. It can be installed with pipx.

So far I have only used it with local databases. It has been very useful to monitor how different user interaction had an effect on:

  • amount of queries
  • queries memory usage
  • blocking queries

I would love to hook it to our production database for debugging and monitoring purposes, but unfortunately our organization has very strict security policies and I have not been allowed.

I will be comming back to this tool every now and then, for debugging and monitoring tasks.

Squawk

Squawk is a linter for psql and sql migrations. It supports django and alembic migrations. Can be installed with npm or pipx. It can be run on the terminal with:

python manage.py db upgrade <from-version>:<to-version> --sql | squawk --pg-version 17

It also has a github action integration.

It provides good feedback on the migration scripts, which sometimes has been very useful. For example, in our team we usually forget that creating indexes concurrently is a thing. Now Squawk reminds us everytime a new index is created.

I will keep this tool. Ideally should always be integrated in the CI of any application which handles migrations. If not possible, I will keep using it locally.