Re: Keeping some tables in cache

On Tue, 2025-08-26 at 11:21 +0300, Ertan Küçükoglu wrote:
> I am using PostgreSQL 17.6 on Win64 platform running on VPS with 4 cores
> (2.59Ghz Xeon SapphireRapids) and 4GB RAM.
> On average 1.7 to 2.0GB of RAM is actively used on that server.
> Disk performance is not great at all.
> I have no other choice to use that server as it is provided.
> 
> I used pgtune web site optimized configuration tool and relevant parameters in
> my postgresql.conf are as following:
> max_connections = 200
> shared_buffers = 1GB
> effective_cache_size = 3GB
> maintenance_work_mem = 256MB
> checkpoint_completion_target = 0.9
> wal_buffers = 16MB
> default_statistics_target = 100
> random_page_cost = 1.1
> work_mem = 5041kB
> huge_pages = off
> min_wal_size = 1GB
> max_wal_size = 4GB
> 
> My database size is way too small and not even 200 MB.
> 
> My problem is, I am running a time bound web service on the same server which needs
> 
> What I would like is to keep these small tables in RAM all the time and if there is
> a change to their data I also want to have it reflected into the cache (I don't know
> maybe this is already how PostgreSQL works).

That will happen automatically: all data you read or modify get loaded into
shared buffers and stay there.

The cache will be empty after a restart of the database. You can use the pg_prewarm
extension (in shared_preload_libraries) for PostgreSQL to load the data into the
cache again automatically after a restart.

Yours,
Laurenz Albe