TutorialDBA - Support | Training | Consultant

------This Is For 16GB RAM BSNL CLOUD---------- shared_buffers = 1GB               (5-25 %)     effective_cache_size =4GB  default (50-80 %)   ---->>(shared_buffers+page cache) for dedicated server only work_mem = 128MB                   (0.3-1 %) maintenance_work_mem = 1GB         (0.5-4 % ) temp_Buffer =  32MB                    ---->>default is better( setting can be changed within individual sessions) max_wal_senders = 10 wal_keep_segments = 64 wal_buffers = 32MB                     ----->>leave this default 3% of shared buffer is better random_page_cost = 3.0                 ----->>For fast disks you could reduce that to 3.0 max_connections = 1000

A sequential scan i.e when there is no index and postgres has to fetch all the data from disk are a problem area for a cache like this. Since a single seq scan can wipe all of the data from a cache, it is handled differently. Instead of using a normal LRU/clock sweep algorithm, it uses a series of buffers of total 256 K.B in size. The below plan shows how it is handled. test=# explain (analyze,buffers) select count(*) from emp; Aggregate (cost=48214.95..48214.96 rows=1 width=0) (actual time=3874.445..3874.445 rows=1 loops=1) Buffers: shared read=35715 -> Seq Scan on emp (cost=0.00..45714.96 rows=999996 width=0) (actual time=6.024..3526.606 rows=1000000 loops=1) Buffers: shared read=35715 Planning time: 0.114 ms Execution time: 3874.509 ms Executing the above query again. test=# explain (analyze,buffers) select count(*) from emp; Aggregate (cost=48214.95..48214.96 rows=1 width=0) (actual time=426.385..426.385 rows=1 loops=1) Buff...