Our application has a number of inserters posting rows of network statistics into a database. This is occuring continously. The following is an example of a stats table (simplified but maintains key concepts).
CREATE TABLE stats
(
logtime timestamptz,
key int,
stat1 bigint,
stat2 bigint,
stat3 bigint,
PRIMARY KEY (key,logtime)
);
CREATE INDEX x ON stats(logtime);
There are on the order of 1M unique values for "key" and a new row for each key value will be inserted say every 15 minutes. These rows are divided up between a number of different inserting elements, but that isn't relevant.
The problem is, the insert pattern has low correlation with the (key,logtime) index. In this case, would need >1M blocks in my shared_buffer space to prevent a read-modify-write type of pattern happening during the inserts (given a large enough database).
Wondering about lowering the BLKSZ value so that the total working set of blocks required can be maintained in my shared buffers. Our database only has 8G of memory and likely need to reduce BLKSZ to 512....
Any comment on other affects or gotchas with lowering the size of BLKSZ? Currently, our database is thrashing its cache of blocks we we're getting only ~100 inserts/second, every insert results in a evict-read-modify operation.
Ideally, like to keep the entire working set of blocks in memory across insert periods so that the i/o looks more like write full blocks....
Thanks
Marc