PostgreSQL - ERROR out of shared memory HINT

Hello colleagues.

In today's article, we will talk about such an error that you might have in a PostgreSQL database, and this error is called -  ERROR: out of shared memory HINT: You might need to increase max_locks_per_transaction . Some of you may have already seen this error message in PostgreSQL, but have not been able to solve this problem.

This problem is actually not as incomprehensible as it might seem at first glance. This error means that you do not have enough shared memory. As we all already know, most of the shared memory used by PostgreSQL has a fixed size and the shared_buffers parameter is responsible for this.

Shared memory is used by many database components such as cache or I/O and many other components. One of these database components is related to locking. If you do anything to a table within a transaction, PostgreSQL will always track your actions to ensure that a parallel transaction cannot drop the table you are about to access. Tracking such activity is very important for the database to maintain its integrity. The problem is that you need to store this information about the tracked activity somewhere, and just all this activity will be collected in shared memory.

In fact, to solve this problem, you need to change the values ​​of the  max_locks_per_transaction parameter . max_locks_per_transaction  is a critical database configuration setting that must be used to avoid this issue. You need to find this setting in the main postgresql.conf configuration file and increase its values.

By default, the max_locks_per_transaction parameter is set to 64.

Increase the parameter from 64 to 512.

conf> max_locks_per_transaction = 64

Save the file and be sure to restart the database.

Thank you all, I hope my article was of some help to you.