public inbox for [email protected]  

help / color / mirror / Atom feed

From: Laurenz Albe <[email protected]>
To: Ertan Küçükoglu <[email protected]>
To: [email protected]
Subject: Re: Keeping some tables in cache
Date: Tue, 26 Aug 2025 14:21:59 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAH2i4yeOU11Co792H0F2EbosQW3bsUuGRs=kQgxuFLCfNkHcYQ@mail.gmail.com>
References: <CAH2i4yeOU11Co792H0F2EbosQW3bsUuGRs=kQgxuFLCfNkHcYQ@mail.gmail.com>

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

view thread (3+ messages)

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Keeping some tables in cache
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox