Brian Likes Postgres

submited by

Style Pass

2022-01-12 19:30:13

Now, while the slow query is running we can open another psql session and run our 'monitor' in a \watch and see how quickly Postgres is chewing through rows. Sequnces aren't transactional, which is why this works. When we see the values from nextval in our watch start shooting up, that's Postgres working its way through the query.

Unfortunately this won't work for every query. Postgres needs to evaluate the call to nextval in the 'meat' of the execution for it to work, and Postgres won't always choose to do that. It won't work on this query, for example

But you could get the math wrong doing it that way and end up changing the logic of the UPDATE. Better to use the RETURNING clause

There's another way you can monitor INSERT statements that doesn't involve tacking on a RETURNING clause or modifying the INSERT statement in any way. This use case might be pretty rare, but I once wanted to monitor an INSERT statement and I couldn't easily modify the query. The trick was to add a dummy column to the table and make it use the sequence by default.