Hi,
I need to tune my database for a high update rate of a single small table. A little simplified it looks like this:
CREATE TABLE temp_agg(
topic TEXT PRIMARY KEY,
tstmp TIMESTAMP,
cnt BIGINT,
sum NUMERIC
)
The table has 500 rows.
A transaction looks simplified like this:
1) select * from temp_agg where topic=$1 for update
2) if date_trunc('second', tstmp)=date_trunc('second', $3) then:
2a) update temp_agg set cnt=cnt+1, sum=sum+$2 where topic=$1
2b) set local synchronous_commit to off
3) if a new second has started:
3a) insert into other_table select * from temp_agg where topic=$1
3b) update temp_agg set tstmp=date_trunc('second', $3) cnt=1, sum=$2 where topic=$1
3c) emit a notification (pg_notify(...)) with the new data
As a first test my program starts 10 writers each of which serves 50 topics. The timestamps are generated in a way that on average 10 timestamps per second per topic a