Re: high transaction rate - Mailing list pgsql-general

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