How do I tell Postgres to load small tables into memory?

Question

Let's say I have a small postgres database (< 500mb) , and I have app which is very read intensive 99% of requests are reads. Is there a way to tell Postgres to load all tables into RAM so it can do selects faster.? I think Oracle and SQL server have that kind of functionality.

I have done some test on my local machine, I have table with 500 records, Java HashMap Took 2ms, sql select took 12000 ms, Obviously java HashMap is faster because it is within same process but is there a way to speedup sql queries for small tables in postgres ? Thanks

 for (int i = 0; i < 100_000; i++) {
   //1) select * from someTable where id = 10
   // 2) get from Java HashMap by key
 }

Does this answer your question? [Optimise PostgreSQL for fast testing](https://stackoverflow.com/questions/9407442/optimise-postgresql-for-fast-testing) — Saaru Lindestøkke, Nov 23 '22 at 17:15
Maybe: https://www.postgresql.org/docs/current/pgprewarm.html — , Nov 23 '22 at 17:30
Thank @a_horse_with_no_name , I just tested it and does bring much improvement. — Vahe Harutyunyan, Nov 23 '22 at 18:59

score 1 · Accepted Answer · answered Nov 24 '22 at 07:26

1

PostgreSQL caches data in RAM automatically. All you have to do is set shared_buffers to be at least as big as your database is, then all data have to be read only once and will stay cached in RAM.

If you want to reduce the initial slowness caused by reading the data, set shared_preload_libraries = 'pg_prewarm,pg_stat_statements'.

I suspect that the excessive slowness you report is not slowness of the database, but network lag or inefficient queries. pg_stat_statements will help profile your database workload.

answered Nov 24 '22 at 07:26

Laurenz Albe

209,280
17
206
263

Thanks @laurenz-albe I think this is just network lag, query is just select by id. – Vahe Harutyunyan Nov 24 '22 at 09:09
I will try to compare this with redis, I think it will give similar numbers. Basically I want to have redis like performance for small tables in postgres – Vahe Harutyunyan Nov 24 '22 at 09:17
Then run queries that return more than a single row at a time. – Laurenz Albe Nov 24 '22 at 10:36

How do I tell Postgres to load small tables into memory?

1 Answers1