Bret,
>>> And then, even if the support is there, you'd need to outline exactly
>>> how you're planning on pushing this button.
>>>
>>> Specifically, what's your usage pattern that would make this a
>>> win for you?
>>>
>> Let me explain. I have a very busy application generating thousands
>> of SQLs per second.
>> There is an application level cache built into the application already.
>>
>> The important part is that once per hour the application writes
>> collected data to huge historical
>> tables (100M up-to billions of records, partitioned). Since it
>> happens every hour database buffer
>> cache is already overwritten by data and indexes of other tables, so
>> the write operation is very
>> slow and requires huge amount of disk seeks causing 50-100x drop of
>> performance.
>>
>
> The disk seeks will happen regardless of what Postgres does, as the
> OS pulls in new disk blocks to perform the write. If your OS' buffer
> cache is large enough to hold all the data you need, then your
> best bet is likely partitioning data across multiple disks, so that
> queuing the archive reads doesn't get in the way of production reads.
>
> As I'm a unix admin mostly, I'm not qualified to give advice on whether
> or not that's possible, or how to do it if it is ;)
>
I was talking about read seeks obviously caused by index-related
searches. Write operations
do not cause latency issues as they are handled quite well by OS,
controller, HDD, whatever
write cache.
>> So, my idea is to assign a separate buffer cache for the historical
>> tables. It would guarantee that
>> index data is always cached, so the write operation will be very fast.
>>
>> Is it possible? Is there any other techniques available?
>>
>
> If it were at all possible, I'd actually set up a secondary archiving
> server (unless you need the historical data on tap for the production
> system as well), either on another port on the same machine, or on
> another machine which won't impact your production system if it has to
> suddenly do a bunch of disk I/O, and log the history to that.
>
I agree. Two separate servers would be a nice solution as usage patterns
are absolutely different,
so the servers can be tuned differently. Lack of transactional integrity
is an obvious drawback of
such approach.
Kind regards,
Alexei