Re: PG as in-memory db? How to warm up and re-populate buffers? How to read in all tuples into memory?

On 02/26/2012 01:11 PM, Stefan Keller wrote:
> 2012/2/26 Andy Colson<andy@squeakycode.net>  wrote:
>> On 02/25/2012 06:16 PM, Stefan Keller wrote:
>>> 1. How can I warm up or re-populate shared buffers of Postgres?
>>> 2. Are there any hints on how to tell Postgres to read in all table
>>> contents into memory?
>>>
>>> Yours, Stefan
>>
>> How about after you load the data, vacuum freeze it, then do something like:
>>
>> SELECT count(*) FROM osm_point WHERE tags @>  'tourism=>junk'
>>
>> -Andy
>
> That good idea is what I proposed elsewhere on one of the PG lists and
> got told that this does'nt help.
>
> I can accept this approach that users should'nt directly interfere
> with the optimizer. But I think it's still worth to discuss a
> configuration option (per table) or so which tells PG that this table
> contents should fit into memory so that it tries to load a table into
> memory and keeps it there. This option probably only makes sense in
> combination with unlogged tables.
>
> Yours, Stefan
>

I don't buy that.  Did you test it?  Who/where did you hear this?  And... how long does it take after you replace the
entiretable until things are good and cached?  One or two queries? 

After a complete reload of the data, do you vacuum freeze it?

After a complete reload of the data, how long until its fast?

-Andy