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