MIME-Version: 1.0
From: =?UTF-8?B?RXJ0YW4gS8O8w6fDvGtvZ2x1?= <ertan.kucukoglu@gmail.com>
Date: Tue, 26 Aug 2025 11:21:52 +0300
Message-ID: 
 <CAH2i4yeOU11Co792H0F2EbosQW3bsUuGRs=kQgxuFLCfNkHcYQ@mail.gmail.com>
Subject: Keeping some tables in cache
To: pgsql-performance@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000e8d707063d405d5a"
Archived-At: 
 <https://www.postgresql.org/message-id/CAH2i4yeOU11Co792H0F2EbosQW3bsUuGRs%3DkQgxuFLCfNkHcYQ%40mail.gmail.com>
Precedence: bulk

--000000000000e8d707063d405d5a
Content-Type: text/plain; charset="UTF-8"

Hello,

I read a lot of different suggestions on the web and finally confused and
decided to ask in here.
Same/similar questions are asked before like 14 years ago 7 years ago, etc.
and I also wanted to learn the latest news.

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.
There are about 6-7 tables where some of them are empty, some of them have
single row in it, some of them have a few rows only.
These tables are 99% of the time select queried and rarely updated. That
may change and there maybe updates more frequent than now in the future.

My problem is, I am running a time bound web service on the same server
which needs to reply incoming requests under 2.0 seconds.
These 6-7 tables are only queried if certain request reaches to the web
service.
There are other web services on that same system doing other things, using
other tables, running different queries depending on user selections on the
same database.
There are other scheduled jobs on that same system doing some other web
service communication including select/insert/update interaction with the
same database.
Finally, there are hourly backups using pg_dump and 7zip.

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).
This would support this tme bound service a lot on this server.

I don't know how PostgreSQL cache system works in detail. If there is a
setting for per database then maybe I can move time bound service tables in
another database.

Any help is appreciated.

Thanks & Regards,
Ertan

--000000000000e8d707063d405d5a
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hello,<div><div><br></div><div>I read a lot of different s=
uggestions on the web and finally confused and decided to ask in here.</div=
><div>Same/similar questions are asked before like 14 years ago 7 years ago=
, etc. and I also wanted to learn the latest news.</div><div><br></div></di=
v><div>I am using PostgreSQL 17.6 on Win64 platform running on VPS with 4 c=
ores (2.59Ghz Xeon SapphireRapids) and 4GB RAM.</div><div>On average 1.7 to=
 2.0GB of RAM is actively used on that server.</div><div>Disk performance i=
s not great at all.</div><div>I have no other choice to use that server as =
it is provided.</div><div><br></div><div>I used pgtune web site optimized c=
onfiguration tool and relevant parameters in my postgresql.conf are as foll=
owing:</div><div>max_connections =3D 200<br>shared_buffers =3D 1GB<br>effec=
tive_cache_size =3D 3GB<br>maintenance_work_mem =3D 256MB<br>checkpoint_com=
pletion_target =3D 0.9<br>wal_buffers =3D 16MB<br>default_statistics_target=
 =3D 100<br>random_page_cost =3D 1.1<br>work_mem =3D 5041kB<br>huge_pages =
=3D off<br>min_wal_size =3D 1GB<br>max_wal_size =3D 4GB</div><div><br></div=
><div>My database size is way too small and not even 200 MB.</div><div>Ther=
e are about 6-7 tables where some of them are empty, some of them have sing=
le row in it, some of them have a few rows only.</div><div>These tables are=
 99% of the time select queried and rarely updated. That may change and the=
re maybe updates more frequent than now in the future.</div><div><br></div>=
<div>My problem is, I am running a time bound web service on the same serve=
r which needs to reply incoming requests under 2.0 seconds.</div><div>These=
 6-7 tables are only queried if certain request reaches to the web service.=
</div><div>There are other web services on that same system doing other thi=
ngs, using other tables, running different queries depending on user select=
ions on the same database.</div><div>There are other scheduled jobs on that=
 same system doing some other web service communication including select/in=
sert/update interaction with the same database.</div><div>Finally, there ar=
e hourly backups using pg_dump and 7zip.</div><div><br></div><div>What I wo=
uld 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 d=
on&#39;t know maybe this is already how PostgreSQL works).</div><div>This w=
ould support this tme bound service a lot on this server.</div><div><br></d=
iv><div><div>I don&#39;t know how PostgreSQL cache system works in detail. =
If there is a setting for per database then maybe I can move time bound ser=
vice tables in another database.</div></div><div><br></div><div>Any help is=
 appreciated.</div><div><br></div><div>Thanks &amp; Regards,</div><div>Erta=
n</div></div>

--000000000000e8d707063d405d5a--