• psql with pspg for viewing results
• pdot (my tool) for exploring structure
• pgtap for testing

My go-to CLI client is pgcli: https://www.pgcli.com/. For examining the output of logical replication slots using pgoutput encoding, I’ve built a CLI tool myself: https://github.com/gunnarmorling/pgoutput-cli.

JetBrains DataGrip - https://www.jetbrains.com/datagrip/

I use DBeaver (Community Edition) every day to interact with multiple PgSQL DBs: running queries, inspecting schemas, saving queries. The transactional mode is handy when I have to operate on a production DB. It is such a handy tool. (Not specific to PgSQL, you can use other supported databases as well)

95% of the time: psql and tmux with a personalized .psqlrc file and EDITOR set to either vim or emacs.

rest of the time: datagrip, QGIS/ogr2ogr, programmatically through libpq or ruby pg, or python psycopg or whatever pg library for a given language i’m using.

I mostly rely on psql for querying and such. It really pays off learning its ins and outs! For performance analyses there’s three nice tools I’ve used:

• Depesz’ explain visualizer
• PEV (postgres explain visualizer), you can self host but it’s also available hosted at http://tatiyants.com/pev/#/plans/new
• PgMustard - commercial and closed but well worth the money - the explained plan deep-links to various knowledge base articles they wrote

99% of the time I’ve been raw-dogging psql. Sometimes I hop unto pgcli

I don’t use it for pg, but TablePlus is a pretty nice multi-db client.

I use pgcli as a CLI improvement for psql. It has a cousin litecli that will give you the same experience for SQLite, which is nice.

When using Postgres via an ORM (Django), I’m often in IPython. Browsing the results of queries from IPython is not that nice, but I found I can integrate visidata quite nicely into IPython with some glue code I wrote.

You can also use visidata with postgresql directly.

Great question!

1- What are your go-to tools or software for managing, querying, or interacting with PostgreSQL databases?

pgcli + pspg are indispensable to me

2- Any particular GUI clients, command-line tools, or libraries you find indispensable for your PostgreSQL workflows?

I’ve given up on GUI clients, but sometimes good old pgAdmin can still be handy.

A good way to find interesting and valuable postgres extensions is to check out the ones selected by Supabase (which is a great project in intself): https://supabase.com/docs/guides/database/extensions and to follow this wonderful, never-ending issue discussion on GitHub: Vote for Postgres extensions

Migrations is a big and somewhat underappreciated topic (most people use an ORM or something similar integrated into their app platform to manage migrations). The most comprehensive choice is probably Sqitch but it’s also worth looking at “declarative migrations” tools where you just write the SQL you want and the tool creates the migration you need. Two interesting choices are Atlas and Tusker. (I can’t vouch for either though.)

3- Are there any lesser-known or niche tools you’ve discovered that have made your PostgreSQL experience smoother or more efficient?

I am a big fan of tools that infer an API (REST or GraphQL) directly from the database schema. I have the most experience with PostGraphile. PostgREST is the grandmommy of them all and still going strong. Hasura is by all appearances also great, but I haven’t used it.

I bought a TablePlus license. It’s not perfect but does what I need: visualize my tables, inline edits in the table (with manual commit), and raw queries. The UI is sometimes a bit confusing but all in all it’s a good client.

I use Postico for quick queries and searches. It’s nice enough; there’s a persistent SQL “scratchpad” that I use a lot. I also find it more convenient than the CLI for checking table definitions. (I’m using v1, which is slightly cheaper.)

Technically, it is not a tool for interacting with PostgreSQL but for monitoring it; in any case, it has saved my life a couple of times during production releases. Right now, what happens in the environment is entirely transparent to me, and it’s crazy how much happens without you even noticing.

Inspired as well by the design of htop (or top): https://github.com/dalibo/pg_activity

Edit: After reading every comment, I realized nobody mentioned the best open-source IDE for SQL out there: https://dbeaver.io/

I like datagrip, but really only because I have a standing subscription to JetBrains’ all products pack. (Back before they moved to subscriptions, my company was reimbursing me for PyCharm and Clion. When they moved to subscriptions, it was cheaper to get the full line than to buy those two. I’ve since moved on, but have continued to pick up the renewals myself because I get quite a lot of usage out of more than two of their tools.)

As you’re just diving in, though, I’d recommend sticking to the built in psql command line tool for a while. And I’d strongly recommend picking up and working through the book A Curious Moon. It was a fun, engaging way to learn a lot about postgres in just a few days. I haven’t enjoyed any other tech book this much, and postgres might be the topic about which I’d have least expected to enjoy a book.

I have a funny one for you. org-mode in emacs! Whyever would I do that you ask?

Because I prototype queries and schema changes in source code blocks, and the results are saved in my notes. I can press C-c C-c in the query and that immediately updates the RESULTS block below the query.

#+begin_src sql :engine postgresql
  \d import.master_plan
#+end_src

#+RESULTS:
| Table "import.master_plan" |      |           |          |         |
|----------------------------+------+-----------+----------+---------|
| Column                     | Type | Collation | Nullable | Default |
|   start_time_utc           | text |           |          |         |
|   duration                 | text |           |          |         |
|   date                     | text |           |          |         |
|   team                     | text |           |          |         |
|   spass_type               | text |           |          |         |
|   target                   | text |           |          |         |
|   request_name             | text |           |          |         |
| library_definition         | text |           |          |         |
|   title                    | text |           |          |         |
|   description              | text |           |          |         |

I write my org-mode notes such that I start with a question like: Am I able to use the ltree operators to update large jsonb fields? I incrementally build my own answers by starting with snippets that teach me the operators, and then accomplish the task, and I can go back and refresh my understanding from my notes.

This sort of literate programming has been unexpectedly useful when a coworker accidentally broke our schema and no one else noticed!

Bonus points if you recognize the schema above from a postgresql book!

I always liked psql and commands like \dt,explain analyze, etc. dbcli is also a fun tool with good autocompletion.

psql I guess. It kinda sucks for editing long queries, but you can run it on the server and don’t have to tunnel anything. https://explain.depesz.com/ is of course great for visualizing explains.

Postico is quite nice - https://eggerapps.at/postico2/

• psql for quick things
• PgAdmin when I want a GUI, such as graph output from EXPLAIN ANALYZE. PgAdmin is hugely featureful. The UI is a bit weird but it’s fine.
• Python wrappers around psql/pg_dump/pg_restore for automating database cloning, minimizing, sanitizing, etc. for development purposes

pgadmin is pretty nice.

I also have the postgres docs in Zeal and look at them all the time when trying to recall certain syntax/behavior. Docs are alright.

If you’re already kinda versed in SQL, then this site is a full explanation of loads of Postgres stuff implementation. Should help to understand what is a good or bad idea to do in pg. Goes deep in how data is stored on disk.

M-x sql-postgres in Emacs, or psql if that’s not available for some reason.

I don’t have direct SQL access to our production RDS DBs, but we have an PgAnalyse instance that I can use to answer some questions. I like it, though I haven’t used any of the other tools mentioned here so don’t know how it compares.