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

Hi,

2011/10/24 Stephen Frost <sfrost(at)snowman(dot)net> wrote
> Now, we've also been discussing ways to have PG automatically
> re-populate shared buffers and possibly OS cache based on what was in
> memory at the time of the last shut-down, but I'm not sure that would
> help your case either since you're rebuilding everything every night and
> that's what's trashing your buffers (because everything ends up getting
> moved around).  You might actually want to consider if that's doing more
> harm than good for you.  If you weren't doing that, then the cache
> wouldn't be getting destroyed every night..

I'd like to come back on the issue of aka of in-memory key-value database.

To remember, it contains table definition and queries as indicated in
the appendix [0]. There exist 4 other tables of similar structure.
There are indexes on each column. The tables contain around 10 million
tuples. The database is "read-only"; it's completely updated every
day. I don't expect more than 5 concurrent users at any time. A
typical query looks like [1] and varies in an unforeseable way (that's
why hstore is used). EXPLAIN tells me that the indexes are used [2].

The problem is that the initial queries are too slow - and there is no
second chance. I do have to trash the buffer every night. There is
enough main memory to hold all table contents.

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

APPENDIX

[0]
CREATE TABLE osm_point (
osm_id integer,
name text,
tags hstore
geom geometry(Point,4326)
);

[1]
SELECT osm_id, name FROM osm_point
WHERE tags @> 'tourism=>viewpoint'
AND ST_Contains(
GeomFromText('BOX(8.42 47.072, 9.088 47.431)'::box2d, 4326),
geom)

[2]
EXPLAIN ANALYZE returns:
Bitmap Heap Scan on osm_point (cost=402.15..40465.85 rows=430
width=218) (actual time=121.888..137.
Recheck Cond: (tags @> '"tourism"=>"viewpoint"'::hstore)
Filter: (('01030...'::geometry && geom) AND
_st_contains('01030'::geometry, geom))
-> Bitmap Index Scan on osm_point_tags_idx (cost=0.00..402.04
rows=11557 width=0) (actual time=1 6710 loops=1)
Index Cond: (tags @> '"tourism"=>"viewpoint"'::hstore)
Total runtime: 137.881 ms
(6 rows)