public inbox for [email protected]help / color / mirror / Atom feed
Keeping some tables in cache 3+ messages / 3 participants [nested] [flat]
* Keeping some tables in cache @ 2025-08-26 08:21 Ertan Küçükoglu <[email protected]> 0 siblings, 2 replies; 3+ messages in thread From: Ertan Küçükoglu @ 2025-08-26 08:21 UTC (permalink / raw) To: [email protected] 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Keeping some tables in cache @ 2025-08-26 09:04 Achilleas Mantzios <[email protected]> parent: Ertan Küçükoglu <[email protected]> 1 sibling, 0 replies; 3+ messages in thread From: Achilleas Mantzios @ 2025-08-26 09:04 UTC (permalink / raw) To: [email protected] On 8/26/25 09:21, Ertan Küçükoglu wrote: > 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. You will get more advice, and certainly more options moving away from Windows to a UNIX platform (Linux, FreeBSD, etc) > 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. pg_dump when used unwisely can kill your performance in may ways, you better find a good alternative. (pg_backrest, barman, pg_probackup, etc, file sys backup / snapshots + PITR , ...) > > 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. Besides the shared buffers, PostgreSQL relies on the OS, e.g. on Linux with ext4, it relies on the OS cache for better read performance. No setting per database, besides : effective_cache_size for the whole cluster. > > Any help is appreciated. > > Thanks & Regards, > Ertan ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Keeping some tables in cache @ 2025-08-26 12:21 Laurenz Albe <[email protected]> parent: Ertan Küçükoglu <[email protected]> 1 sibling, 0 replies; 3+ messages in thread From: Laurenz Albe @ 2025-08-26 12:21 UTC (permalink / raw) To: Ertan Küçükoglu <[email protected]>; [email protected] 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2025-08-26 12:21 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2025-08-26 08:21 Keeping some tables in cache Ertan Küçükoglu <[email protected]> 2025-08-26 09:04 ` Achilleas Mantzios <[email protected]> 2025-08-26 12:21 ` Laurenz Albe <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox