Message-ID: <835091f124435baf195c331d79641cea95ddf451.camel@cybertec.at>
Subject: Re: Keeping some tables in cache
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Ertan =?ISO-8859-1?Q?K=FC=E7=FCkoglu?= <ertan.kucukoglu@gmail.com>,
	pgsql-performance@lists.postgresql.org
Date: Tue, 26 Aug 2025 14:21:59 +0200
In-Reply-To: 
 <CAH2i4yeOU11Co792H0F2EbosQW3bsUuGRs=kQgxuFLCfNkHcYQ@mail.gmail.com>
References: 
	<CAH2i4yeOU11Co792H0F2EbosQW3bsUuGRs=kQgxuFLCfNkHcYQ@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.56.2 (3.56.2-1.fc42) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/835091f124435baf195c331d79641cea95ddf451.camel%40cybertec.at>
Precedence: bulk

On Tue, 2025-08-26 at 11:21 +0300, Ertan K=C3=BC=C3=A7=C3=BCkoglu 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.
>=20
> I used pgtune web site optimized configuration tool and relevant paramete=
rs in
> my postgresql.conf are as following:
> max_connections =3D 200
> shared_buffers =3D 1GB
> effective_cache_size =3D 3GB
> maintenance_work_mem =3D 256MB
> checkpoint_completion_target =3D 0.9
> wal_buffers =3D 16MB
> default_statistics_target =3D 100
> random_page_cost =3D 1.1
> work_mem =3D 5041kB
> huge_pages =3D off
> min_wal_size =3D 1GB
> max_wal_size =3D 4GB
>=20
> My database size is way too small and not even 200 MB.
>=20
> My problem is, I am running a time bound web service on the same server w=
hich needs
>=20
> What I would like is to keep these small tables in RAM all the time and i=
f 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 int=
o the
cache again automatically after a restart.

Yours,
Laurenz Albe