I am facing a issue that the sql first time query was slow. I read the execution plan and found out the index are not in shared buffers the first time query. then I prewarm the index like this: SELECT pg_prewarm('public.pk_w_03002_kt_h','buffer'); and check the index that exists in shared buffer: SELECT c.relname, pg_size_pretty(count(*) * 8192) as buffered, round(100.0 * count(*) / (SELECT setting FROM pg_settings WHERE name='shared_buffers')::integer, 1) AS buffer_percent, round(100.0 * count(*) * 8192 / pg_table_size(c.oid), 1) AS percent_of_relation FROM pg_class c INNER JOIN pg_buffercache b ON b.relfilenode = c.relfilenode INNER JOIN pg_database d ON (b.reldatabase = d.oid AND d.datname = current_database()) GROUP BY c.oid, c.relname ORDER BY 3 DESC LIMIT 10; Now I facing a issue that the cached index will be swap out sometimes. Is it possible to keep the index always in the shared buffer? the index size was 60MB and the shared buffer config is 8GB. it still not solved the first time query slow issue.

I have an SQL query that executes slowly on PostgreSQL 13.15 with 128GB of memory. The query mainly performs a Bitmap Heap Scan, and I’ve noticed that many reads are coming from the heap instead of the cache. Here’s a link to the query and execution plan. An index that frequently appears in the plan looks like this: create index ix_contacts__addresses_value__normalized on contacts__addresses using gin (company_id, field_name, field_id, value__normalized); My memory settings: • shared_buffers = 32GB • work_mem = 64MB • effective_cache_size = 96GB • maintenance_work_mem = 1320MB Questions: 1. Why is the query reading so much from the heap? 2. How can I configure PostgreSQL to better use memory (shared_buffers, work_mem, and other settings) to avoid unnecessary heap reads? 3. Should I consider modifying the indexes to reduce execution time? I would greatly appreciate any advice on optimizing caching and overall performance.

When using EXPLAIN with buffers, the blocks read sometimes are actually from system cache, this is understandable sometimes based on the read time. But is there any proper way to understand a system cache-miss as of version 13?

We have migrated our PROD DB to new server with 24GB RAM. Based on that value it is possible to tune some PG configuration values? I think we can increase some values. Is it possible to suggest which value can be increased like shared_buffers to 50% of RAM, etc?

I'm having 'out of shared memory' issue in PostgreSQL 13.3 on x86_64-pc-linux-gnu (The database has 41G data), first, it suggested me to increase max_locks_per_transaction. After increasing max_locks_per_transaction to 1024, I still get 'out of shared memory', but now it suggests:

ERROR:  out of shared memory
HINT:  You might need to increase max_pred_locks_per_transaction.

It seems very confusing to me. So my question: - Why does max_pred_locks_per_transaction affect on shared_memory ? - The shared_buffers is already 4GB, should I increase it ? - The 'out of shared memory' relates to which: max_locks_per_transaction * (max_connections + max_prepared_transactions) or shared_buffers ? Thank you all.

What is the difference between effective_cache_size and shared_buffers ? I got confused .While we trying to query on a table (like select) what happens to query? what role do the effective_cache_size and shared_buffers playing ?

I am debugging a production issue where a regular index scan used in a join reports very high buffers usage (sometimes gigabytes) when inspecting using explain (analyse, buffers). Because it's reading so much from buffers it often performs I/O which is very slow. I measured the actual returned data size and it's always ~100 times smaller no matter how big the joined sets are. I am wondering why the difference between read buffers and actual data size is so huge when querying using index. I reproduced the issue using trivial example (db-fiddle ). Let's create a simple user table and insert 500 rows:

CREATE TABLE users(id int primary key, user_data text);
INSERT INTO users(id, user_data) select generate_series(1,500), random()::text;
select pg_size_pretty(pg_relation_size('users'));

After inserting 500 rows actual table size is 32 kB. Lets query all rows and check buffers used:

explain (analyze, buffers) select * from users;

Seq Scan on users  (cost=0.00..9.00 rows=500 width=24) (actual time=0.032..5.150 rows=500 loops=1)
  Buffers: shared hit=4
Planning time: 1.546 ms
Execution time: 10.484 ms

Selecting all rows yields Buffers: shared hit=4. 4*8kB = 32kB which is exactly table size. Great. If I however query all rows using id condition:

explain (analyze, buffers) select * from users where id in (1, 2, 3, .... , 499, 500);

Index Scan using users_pkey on users  (cost=0.27..165.16 rows=316 width=24) (actual time=0.044..5.704 rows=500 loops=1)
  Index Cond: (id = ANY ('{1,2,3,...,500}'::integer[]))
  Buffers: shared hit=1006
Planning time: 0.521 ms
Execution time: 10.424 ms

We get Buffers: shared hit=1006. 1006 * 8kB = 8048kB is way higher compared to previous 32kB. Where is this difference coming from?

I am taking this EDB PostgreSQL essential course and Instructor explained about PostgreSQL architecture referring to the diagram that, whenever a client make an update request and suppose data is present in shared buffer (that means no need to fetch it from file storage) then it'll make an entry in WAL buffers and upon *committing* the WAL writer will write the transaction to transaction logs and make it permanent but not in the file systems (as far as I've understood, that's the task of *checkpointer*, below.) So far so good. **image courtesy traning.enterprisedb.com** Now comes checkpointer, *it is a process which runs after every certain interval of time "usually 5 mins is an ideal time" and, write anything in the shared buffer into the file storage.* My question is, suppose checkpointer just ran and after that I initiated an atomic transaction and transferred 100 bucks to my friend, how is it that my friend can see it immediately, is Postgres making query to transaction logs? Or, how's this happening? But upon little pondering, I realize that when the request is made to update the data and in order to update it, Postgres has bring it into the main memory and a viable way to do that is to keep track of dirty data in shared buffer and update the data in shared buffer itself and in the transaction logs we can have 0/1 with every DML transaction entry to identify whether data is present in shared buffer or not. This can also come handy while doing analysis. Can someone help me understand? Thanks in advance!

I am doing testing on a replica, trying to diagnose why servers take several minutes to shutdown when fast mode is used. It seems to correlate with high shmem usage, can anyone recommend a good query / command to run which would cause postgresql to allocate / use a lot of shared buffers (shmem)? I am a Linux engineer but somewhat new to postgres, and only know the basics from the DBA standpoint, but am knowledgable about systems and Linux as well as memory/OS concepts. I've tried running \dt+ and looking for tables that are reasonable large (1G), turning off the pager and doing SELECT * on the table in psql, but it didn't seem to cause much usage increase in the shared memory. I am looking to increase the footprint by ~ <= 200G, ideally. Preferably something simple which doesn't actually write to disk. I don't want to modify the actual state much, just cause high use of shmem (e.g: perhaps a large select, or something ephemeral). This is on a Linux system 3.x kernel without hugepages (THP is enabled at the moment) and mmap is being used for shmem, mainly. postgres 13.6. I am trying to get ~ 500G of shared memory usage

I'm using Postgresql-11, with a traditional HDD as the physical storing device. I need to optimize the performance for writing. I have changed the wal_buffers from -1 to 512MB, that means that wal_buffers no long use shared_buffers. Am I right? I want to know whether or not it can improve the writing performance to enlarge shared_buffers. In my mind, it is used only for querying? The current value of shared_buffers is 2GB, my RAM is 8GB. I want to change it to 4GB. thanks!

I know in Postgres, we can **explicitly** load a table into the cache using pg_prewarm extension, is there a similar mechanism/UDF/etc in MySQL? I know MySQL would cache data in shared buffer **implicitly**, but is there a way to **explicitly** do so? Thanks!

Is there a way to exclude tables from being added to the shared_buffers? I've looked at the space used by the tables and those which use the most are not really relevant for us at the moment. There are some tables which we only write to but don't need to have fast read-access at the moment. For a few tables we would like to keep the latest rows in the memory. If we could only mark these tables to be added to the shared_buffer that would allow us to use the memory on the machine much more efficiently.

I'm using AWS Aurora PostgreSQL Serverless with autoscaling. It appears as though scaling clears the shared buffer, so right when we want to crank out the performance, we are forced to face-plant with an I/O bottleneck. After we get warmed up, we see a great performance improvement. However, if we run back-to-back once scaled, the second run goes much faster. While I haven't seen anything specific on whether the shared buffer gets cleared on scaling, I'm almost positive that it is. Aurora Serverless is currently using PostgreSQL 10.14 and it supports the pg_prewarm extension. It looks like the newest documentation suggests that prewarm supports auto pre-warm after a server restart, but this is serverless and a version that doesn't appear to mention auto pre-warming in the documentation . I found this post that works great for PostgreSQL when restarting the server or recovering from a crash. 1. If we could at least retain the contents of the shared buffer of the lower ACU node after scaling, that'd be fine. 2. If we could pre-warm exactly what needs to be in memory ahead of time, that would be awesome! 3. There are certain tables that are quite large and we would want to selectively pre-warm the pieces that we want. pg_prewarm supports first_block and last_block block numbers for a table/index, but how would one know what values to put in there? We know ahead of time when our peak is and tell RDS to scale right before, so we have a window of time where we could prepare. What are my options?

I have a query (attached with query plan) that is run every 5-7 seconds from every device that is running our dashboard (typically, 500+ devices at peak time). This query, at the outset looks like it spends time in wait state IO:BufFileWrite. From AWS Aurora Performance Insights dashboard, one can see that the query in question spends more time in IO: BUfFileWrite wait state (sky blue color in the graph) **Postgres Configuration / Details:** 1. AWS Aurora PostgreSQL 10.6 1. R5.4X Large instance (128 GB RAM) 1. work_mem = 80MB 1. I use a hikari connection pool to manage the DB connections. A week earlier, I started seeing many errors on my app server: > Failed to get database connection errors and/or > java.sql.SQLTimeoutException: Timeout after 30000ms of waiting for a connection A little debugging and with help of PGAdmin, saw that most of the connections were waiting on IO: BufFileWrite and hence realised that the default **4MB** work_mem was not enough for the query. With the help of PgBadger, saw that the average temp file size was 70MB and hence updated the work_mem to 80 MB. This definitely helped, I started seeing lesser DB connections issue, but it did not go away completely. I used Postgres Visualize Analyzer and Explain Depesz to understand the query plan and saw that an index only scan has a **Total Cost of 4984972.45 and Plan Rows 111195272.** This table (students_points) actually has 100M+ rows and is ~15GB in size and is not partitioned. I tried adding a partial index ( create index students_latest_point_idx ON students_points (student_id, evaluation_date desc) ), in the hope that the cost of the above scan would improve, but in vain. I have run VACUUM ( FULL, ANALYZE, VERBOSE); and REINDEX on the tables involved, but no visible performance improvement. I need help with the following 1. What does the never executed part of the query plan mean? - I have checked the literature on the web, but no satisfactory explanation apart from Postgres engine thinks that it's not relevant / returned 0 rows. 1. Should I look at/be worried at **Total Cost of 4984972.45 and Plan Rows 111195272.** from the query plan, even though it says **never executed**? 1. What would lead to excessive time spent in the wait state BufFileWrite? From what I understand, when a sort/filter is being applied, temp files are used and this shows up as the BufFileWrite wait state. Monitoring_Postgres 1. Where would you advise me to start with, to reduce the time spent by the query in the IO wait state of BufFileWrite? - I have tried, Vacuum, Reindex, adding new partial index - but didn't help. 1. One thing on my mind is, instead of using the students_points table (which has 1 row, for every student, for every test, he takes every week, over 4 years) so it builds up fast, create a new table that will hold only the latest points for every student (hence only as many rows as there are students) and use that in the query. Any help is appreciated. Thanks in advance. If you need any more information, I would be happy to provide. ### The query and the plan

EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, TIMING, SUMMARY) 
SELECT 
    SUM(CASE WHEN just_pass = 1 THEN 1 ELSE 0 END) point,
    SUM(CASE WHEN week_of_birthday = TRUE THEN 1 ELSE 0 END) birthday, 
    SUM(CASE WHEN fresh_grad = 1 THEN 1 ELSE 0 end) fresher, 
    SUM(CASE WHEN intervention_type::INT = 2 OR intervention_type::INT = 3 THEN 1 ELSE 0 END) attention 
FROM 
    (
        SELECT 
            checkins.student_id, intercepts.intervention_type ,max(evaluation_date), just_pass, 
            compute_week_of_birthday(student_birthdate, 4 , 'US/Central') as week_of_birthday, 
            CASE 
                WHEN student_enrolment_date NOTNULL AND student_enrolment_date >= '2017-01-29' AND student_enrolment_date  '2019-06-11 01:00:40' AND timestamp=18 
        AND 
            lower(registration_type_description) !~* '.*temporary.*' 
        GROUP BY 
            checkins.student_id, students.student_enrolment_date, student_birthdate, just_pass, intercepts.intervention_type
    ) AS result 
WHERE 
    max IN 
        (
            SELECT 
                evaluation_date 
            FROM 
                students_points 
            ORDER BY 
                evaluation_date DESC LIMIT 1
        ) 
        OR 
    max ISNULL;
                                                                                                                                                                                                                                                                                                                                                                                                                                                                      QUERY PLAN                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=74433.83..74433.84 rows=1 width=32) (actual time=0.081..0.081 rows=1 loops=1)
   Output: sum(CASE WHEN (result.just_pass = 1) THEN 1 ELSE 0 END), sum(CASE WHEN result.week_of_birthday THEN 1 ELSE 0 END), sum(CASE WHEN (result.fresh_grad = 1) THEN 1 ELSE 0 END), sum(CASE WHEN (((result.intervention_type)::integer = 2) OR ((result.intervention_type)::integer = 3)) THEN 1 ELSE 0 END)
   Buffers: shared hit=20
   ->  Subquery Scan on result  (cost=74412.92..74432.98 rows=34 width=15) (actual time=0.079..0.079 rows=0 loops=1)
         Output: result.student_id, result.intervention_type, result.max, result.just_pass, result.week_of_birthday, result.fresh_grad, students.student_enrolment_date, students.student_birthdate
         Filter: ((hashed SubPlan 1) OR (result.max IS NULL))
         Buffers: shared hit=20
         ->  GroupAggregate  (cost=74412.31..74431.52 rows=68 width=35) (actual time=0.079..0.079 rows=0 loops=1)
               Output: checkin_table.student_id, intervention_table.intervention_type, max(points.evaluation_date), points.just_pass, compute_week_of_birthday(students.student_birthdate, 4, 'US/Central'::text), CASE WHEN ((students.student_enrolment_date IS NOT NULL) AND (students.student_enrolment_date >= '2017-01-29'::date) AND (students.student_enrolment_date   Sort  (cost=74412.31..74412.48 rows=68 width=30) (actual time=0.078..0.078 rows=0 loops=1)
                     Output: checkin_table.student_id, intervention_table.intervention_type, points.just_pass, students.student_enrolment_date, students.student_birthdate, points.evaluation_date
                     Sort Key: checkin_table.student_id, students.student_enrolment_date, students.student_birthdate, points.just_pass, intervention_table.intervention_type
                     Sort Method: quicksort  Memory: 25kB
                     Buffers: shared hit=20
                     ->  Nested Loop Left Join  (cost=70384.64..74410.24 rows=68 width=30) (actual time=0.035..0.035 rows=0 loops=1)
                           Output: checkin_table.student_id, intervention_table.intervention_type, points.just_pass, students.student_enrolment_date, students.student_birthdate, points.evaluation_date
                           Buffers: shared hit=6
                           ->  Nested Loop Left Join  (cost=70384.08..74151.91 rows=1 width=22) (actual time=0.035..0.035 rows=0 loops=1)
                                 Output: checkin_table.student_id, intervention_table.intervention_type, students.student_birthdate, students.student_enrolment_date
                                 Buffers: shared hit=6
                                 ->  Nested Loop  (cost=8.90..25.46 rows=1 width=16) (actual time=0.034..0.034 rows=0 loops=1)
                                       Output: checkin_table.student_id, students.student_birthdate, students.student_enrolment_date
                                       Buffers: shared hit=6
                                       ->  Group  (cost=8.46..8.47 rows=2 width=8) (actual time=0.034..0.034 rows=0 loops=1)
                                             Output: checkin_table.student_id
                                             Group Key: checkin_table.student_id
                                             Buffers: shared hit=6
                                             ->  Sort  (cost=8.46..8.47 rows=2 width=8) (actual time=0.033..0.033 rows=0 loops=1)
                                                   Output: checkin_table.student_id
                                                   Sort Key: checkin_table.student_id
                                                   Sort Method: quicksort  Memory: 25kB
                                                   Buffers: shared hit=6
                                                   ->  Append  (cost=0.00..8.45 rows=2 width=8) (actual time=0.027..0.027 rows=0 loops=1)
                                                         Buffers: shared hit=6
                                                         ->  Seq Scan on public.checkin_table  (cost=0.00..0.00 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=1)
                                                               Output: checkin_table.student_id
                                                               Filter: ((checkin_table.checkin_time > '2019-06-11 01:00:40+00'::timestamp with time zone) AND (checkin_table.checkin_time   Index Scan using checkins_y2019_m6_house_id_timestamp_idx on public.checkins_y2019_m6  (cost=0.43..8.45 rows=1 width=8) (actual time=0.024..0.024 rows=0 loops=1)
                                                               Output: checkins_y2019_m6.student_id
                                                               Index Cond: ((checkins_y2019_m6.house_id = 9001) AND (checkins_y2019_m6.checkin_time > '2019-06-11 01:00:40+00'::timestamp with time zone) AND (checkins_y2019_m6.checkin_time   Index Scan using students_student_id_idx on public.students  (cost=0.43..8.47 rows=1 width=16) (never executed)
                                             Output: students.student_type, students.house_id, students.registration_id, students.registration_status, students.registration_type_status, students.total_non_core_subjects, students.registration_source, students.total_core_subjects, students.registration_type_description, students.non_access_flag, students.address_1, students.address_2, students.city, students.state, students.zipcode, students.registration_created_date, students.registration_activation_date, students.registration_cancellation_request_date, students.registration_termination_date, students.cancellation_reason, students.monthly_dues, students.student_id, students.student_type, students.student_status, students.student_first_name, students.student_last_name, students.email_address, students.student_enrolment_date, students.student_birthdate, students.student_gender, students.insert_time, students.update_time
                                             Index Cond: (students.student_id = checkin_table.student_id)
                                             Filter: ((lower((students.registration_type_description)::text) !~* '.*temporary.*'::text) AND (date_part('year'::text, age((CURRENT_DATE)::timestamp with time zone, (students.student_birthdate)::timestamp with time zone)) >= '18'::double precision))
                                 ->  Nested Loop  (cost=70375.18..74126.43 rows=2 width=14) (never executed)
                                       Output: intervention_table.intervention_type, intervention_table.student_id
                                       Join Filter: (intervention_table.intervention_date = (max(intervention_table_1.intervention_date)))
                                       ->  HashAggregate  (cost=70374.75..70376.75 rows=200 width=8) (never executed)
                                             Output: (max(intervention_table_1.intervention_date))
                                             Group Key: max(intervention_table_1.intervention_date)
                                             ->  HashAggregate  (cost=57759.88..63366.49 rows=560661 width=16) (never executed)
                                                   Output: max(intervention_table_1.intervention_date), intervention_table_1.student_id
                                                   Group Key: intervention_table_1.student_id
                                                   ->  Seq Scan on public.intervention_table intervention_table_1  (cost=0.00..46349.25 rows=2282125 width=16) (never executed)
                                                         Output: intervention_table_1.record_id, intervention_table_1.student_id, intervention_table_1.intervention_type, intervention_table_1.intervention_date, intervention_table_1.house_id, intervention_table_1.teacher_id, intervention_table_1.expiration_date, intervention_table_1.point_at_intervention
                                       ->  Index Scan using intervention_table_student_id_idx on public.intervention_table  (cost=0.43..18.70 rows=4 width=22) (never executed)
                                             Output: intervention_table.record_id, intervention_table.student_id, intervention_table.intervention_type, intervention_table.intervention_date, intervention_table.house_id, intervention_table.teacher_id, intervention_table.expiration_date, intervention_table.point_at_intervention
                                             Index Cond: (checkin_table.student_id = intervention_table.student_id)
                           ->  Index Scan using students_latest_points_idx on public.students_points points  (cost=0.57..257.65 rows=68 width=16) (never executed)
                                 Output: points.record_id, points.student_id, points.registration_id, points.house_id, points.evaluation_date, points.just_pass, points.five_star, points.star1, points.star2, points.star3, points.star4, points.updatedate
                                 Index Cond: (checkin_table.student_id = points.student_id)
         SubPlan 1
           ->  Limit  (cost=0.57..0.61 rows=1 width=4) (never executed)
                 Output: students_points.evaluation_date
                 ->  Index Only Scan Backward using students_points_evaluation_date_idx on public.students_points  (cost=0.57..4984972.45 rows=111195272 width=4) (never executed)
                       Output: students_points.evaluation_date
                       Heap Fetches: 0
 Planning time: 23.993 ms
 Execution time: 17.648 ms
(72 rows)

PS: The names of the table and the attributes are replaced for privacy concerns. At the outset, it seems like I can partition the students_points table by year, but that is not an option that the team is open to for reasons I can't specify and it does not make sense to partition it based on the year, since most of our joins are on student_id and partitioning on student_id would lead to 1M+ partitions. Edited to address Jjanes comment . 1. **checkin_table seems to be empty** - checkin_table is a partitioned table. The query actually hits the partition - checkins_y2019_m6, which actually has data. 1. **What led you to think this query was the culprit?** - When using PGBadger at the peak time, see that 30 out of the 40 DB connections are in the wait state. Looking at the queries of these connections, it's the same query described above - but with different combinations of house_id and checkin_time. Also, from the RDS insights, (image 1 above), if you look at the bottom portion of the screenshot, it has bar graphic, under the **Load By Waits (AAS)** and you can see that 2/3rds of the bar graph is light blue color (IOWait) and 1/3 is Green (CPU) and the corresponding query. Look at the attached pgbadger view (redacted the query details). This query is the most time consuming query. 1. **pg_stat_statements** - Yes I have had a look at it And this is the top query on total_time desc, which concurs with the PG Badger one. 1. **auto_explain** looks doable. Just one question - would it hamper the performance in any way? 1. **Regarding IO Churn and slowest queries** - I agree, but I am hitting dead ends and run out of ideas. I may be misinterpreting things, like you pointed out. I am not looking at all queries writing to temporary files, and that might be hogging the buffers, resulting in an IOWait here. **Edited to add solution:** The backend team re-wrote the sql query and the performance improved. This brought down the query execution time to milliseconds.

I have a standalone server which is running Jira and PostgreSQL 9.6. I have noticed that PostgreSQL has shared_buffers parameter is equal 128MB where RAM is 32GB. Because server is shared with application Jira then there is 16GB left for other usage (including postgres, httpd). According to PostgreSQL documentation size of the shared_buffers should be 25% of RAM. It could be 16GB * 0,25 = 6,5GB. Unfortunately I have doubts because application jira is using 20 connection sessions to PostgreSQL server. So I afraid that when I increase shared_buffers it will multiply it by 20 usage of entire memory. Is it true: approx. all memory consumed by postgres = shared_buffers*sessions What will you suggest to do with shared_buffers parameter?

On a 7.5 GB host (Ubuntu 16.04.5 LTS), I have allocated 3 GB to shared_buffers: postgres=# SHOW shared_buffers; shared_buffers ---------------- 3GB (1 row) with: postgres@my-host:~$ cat /etc/postgresql/10/main/postgresql.conf | grep shared_buffers shared_buffers = 3072MB # min 128kB, valid units are kB, MB, GB, TB I have also configured my host with HP as: postgres@my-host:~$ cat /proc/meminfo | grep ^Huge HugePages_Total: 1600 HugePages_Free: 1600 HugePages_Rsvd: 0 HugePages_Surp: 0 Hugepagesize: 2048 kB With a Hugepagesize at 2048 kB, that works out as 1600*2048/1024 = 3200 MB of HP. When I then try to turn: postgres@raeflightcachemaster0-nonprod:~$ cat /etc/postgresql/10/main/postgresql.conf | grep huge_pages huge_pages = on PostgreSQL (10.5) is reporting: 2018-09-27 09:31:54.397 BST FATAL: could not map anonymous shared memory: Cannot allocate memory 2018-09-27 09:31:54.397 BST HINT: This error usually means that PostgreSQL's request for a shared memory segment exceeded available memory, swap space, or huge pages. To reduce the request size (currently 3523592192 bytes), reduce PostgreSQL's shared memory usage, perhaps by reducing shared_buffers or max_connections. 2018-09-27 09:31:54.397 BST LOG: database system is shut down pg_ctl: could not start server Examine the log output. and the DB server fails to start. My question is, where is it getting the number 3523592192 bytes from? This value does not match the shared_buffers = 3072MB setting in postgresql.conf as it works out as 3523592192/1024/1024 = 3360.359375 MB. I get around the problem by increasing HugePages_Total slightly to 1700 so that we have 3400 MB of HP but I don't understand the discrepancy between the reported value of shared_bufferes and the configured value. Is this a units issue?