Advanced4 lessons92 minutes
Administering PostgreSQL: access, connections and growth
Who may connect, how many at once, what the database is doing right now, and why it takes more space than the data inside it.
The backups course asks for a read-only account without showing how to create one, and mentions `~/.pgpass` without saying which authentication method makes a password relevant. This course picks up there: roles and `pg_hba.conf`, the connection arithmetic a Node application exceeds without warning, the view that says what the server is doing right now, and the mechanism that explains a table growing after a `DELETE`.
What you will be able to do
- Create an application role and a read-only backup role without going through the superuser
- Read `pg_hba.conf` and explain why `psql` works where the application is refused
- Work out how many connections your application really opens, and what happens beyond that
- Query `pg_stat_activity` to find an open transaction and a query scanning a whole table
- Explain why a `DELETE` returns no disk space, and what autovacuum does
Outline
- 01
Who connects, and how many
Two limits that refuse a perfectly configured application.
- 02
What it is doing, and why it grows
Two views and one mechanism explain most of the surprises a production database produces.
Technical content last reviewed on 3 August 2026