5 useful things in Postgres

I've had these snippets in Tot for a while now, but then reminded myself that I've set up this blog to quickly share things for future-me too. Maybe they are useful for someone else stumbling upon them.

1. Quickly get an approximate count of a large table by using the index:

SELECT reltuples::bigint AS estimate FROM pg_class where relname = 'bigtable';

2. Explanation why splitting off a frequently updated column into a new table is useful: https://dba.stackexchange.com/a/318548

3. Reviewing frequently used triggers and functions

alter system set track_functions to 'all';
select pg_reload_conf();
select * from pg_stat_user_functions order by calls desc;

The results of that can be exported as a CSV and then analyised in a spreadsheet.

4. Inspecting the differences between two versions of Postgres on depesz.com.

5. Generate curl commands to run batch requests.

This one sounds a bit silly but it has saved me countless hours over the years, sometimes it's very quick to just generate a long list of curl commands, put them in run.sh, chmod +x run.sh and ./run.sh it instead of writing scripts, or doing things directly in the database which might be riskier.

select 'curl -H "Authorization: Bearer test-token-123" http://localhost:8090/movie/' || m.id || '/update'
from movie m
where m.release_year = 2026
;