CODEWITHSUNDEEP
postgresqlperformancestatistics
Phillip Fleischer
Phillip Fleischer

Reputation: 1103

Is it possible to determine Postgres write rate in writes per second by table?

In PostgreSQL is it possible to get an approximation of the writes to a table as a measure of time?

Specifically, I'm looking for an approximate of "writes/sec" to a given table. I know I can get total writes (insert, update, deletes) from the stat collector, but there is no clear definition of when the stats "began" so that I can determine a rate.

Is there another table that I can apply to make this calculation?

SELECT relname, idx_tup_fetch + seq_tup_read as TotalReads,
    n_tup_ins + n_tup_upd + n_tup_del as TotalWrites, 
-- (n_tup_ins + n_tup_upd + n_tup_del) / magical_seconds_column_that_doesnt_exist as WritesPerSecond
    *
from pg_stat_all_tables
order by totalwrites desc

Upvotes: 2

Views: 1222

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247270

There is no such functionality in PostgreSQL itself. You'll have take a snapshot of the statistics tables regularly and compute the values yourself.

Note that with track_io_timing = on, you'll also get I/O statistics.

Upvotes: 2

Related Questions