How to set postgreSQL to use as buffers more RAM than my computer has?

Question

I have a problem. I am learning PostgreSQL and I work with pgAdmin 4v4. At this point, I am trying to set PostgreSQL to use as buffers more RAM than my computer has. I am thinking of using something like SET shared_buffers TO '256MB' but I am not sure if it is correct. Do you have any ideas?

Answer 1

I don't think there is a way to use more RAM than your machine can physically provide. To answer part of your question as of 2024, in Windows 10/11, PostgreSQL 16.1, pgAdmin4 v.8.2 you'll have to change the shared_buffers size, as jjanes already mentioned correctly, and also restart the PostgreSQL server.

By default, shared_buffers are set to 128MB:

To restart the server, in Windows 10/11, press Ctrl+Alt+Del > Task Manager > Services and then search for postgresql in the search bar at the top. Select the postgresql-x64-16 service by right clicking and selecting Restart . That, did the job for me:

Finally, running again the SHOW shared_buffers; command, pgAdmin will prompt you with a Connection Warning, and by clicking 'Continue' you will be able to see the value change to 256MB.

Answer 2

SET shared_buffers TO '256MB'

This will not work because shared_buffers must be set at server start and cannot be changed later, and this is a command you would run after the server is already running. You would have to put the setting in postgresql.conf, or specify it with the -B option to the "postgres" command.

You could also set it through 'alter system' command, and it would take effect at the next restart. However, you could easily make it a setting that will cause your system to fail to start again (indeed, that appears to be your goal...), at which point you can't use 'alter system' to fix it, and will have to dig into the .conf files.