PostgreSQL: measuring query activity (WAL size generated, shared buffer reads, filesystem reads,…)

select *,pg_current_wal_lsn() from pg_stat_database where datname=current_database() \gset
select blks_hit-:blks_hit"blk hit",blks_read-:blks_read"blk read",tup_inserted-:tup_inserted"ins",tup_updated-:tup_updated"upd",tup_deleted-:tup_deleted"del",tup_returned-:tup_returned"tup ret",tup_fetched-:tup_fetched"tup fch",xact_commit-:xact_commit"commit",xact_rollback-:xact_rollback"rbk",pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(),:'pg_current_wal_lsn')) "WAL",pg_size_pretty(temp_bytes-:temp_bytes)"temp" from pg_stat_database where datname=current_database();
demo=# create table DEMO(n int primary key,flag char,text varchar(1000));CREATE TABLE

Insert 1000 rows:

I’ve run the following insert:

insert into DEMO
select generate_series, 'N',lpad('x',1000,'x') from generate_series(1,10000);
INSERT 0 10000
select relkind,relname,relpages,pg_size_pretty(relpages::bigint*8*1024) from pg_class natural join (select oid relnamespace,nspname from pg_namespace) nsp where nspname='public';

Count the rows

Now, I’ll check how many blocks have to be read when counting all rows:

select count(*) from DEMO ;


Let’s vacuum to update the visibility map:

vacuum DEMO;

Count from vacuumed table

Now, counting the rows again:

select count(*) from DEMO ;


I update the ‘flag’ column to set half of the rows to ‘Y’:

update DEMO set flag='Y' where n%2=0;


Now deleting the non-flagged rows

delete from DEMO where flag='N';


This post is there mainly to show the simple query I use to get SQL execution statistics, including WAL writes which are probably the most useful, but unfortunately missing from pg_stat_database. I’m also advocating here for small test cases fully understood rather than general benchmarks difficult to analyze. It is, in my opinion, the best way to understand how it works, both for educational purpose and to guarantee scalable applications.



Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Franck Pachot

Franck Pachot


Developer Advocate at Yugabyte, Open Source distributed SQL database 🚀 Also Oracle ACE Director, Oracle Certified Master, AWS Data Hero, OakTable member