What are the causes of the "Out of shared memory: You might need to increase max_locks_per_transaction" error?

Question

PostgreSQL

Good day.
Today I received such an error in the logs. Prior to this, the server was spinning without a restart for about three months, the database is not that very large, but in some tables there are hundreds of thousands of rows. Increased this same "max_locks_per_transaction", raised "shared_buffers" and "work_mem", restarted postgres, the problem disappeared. But forever?
It was:

shared_buffers = 128MB			# min 128kB
work_mem = 4MB				# min 64kB
temp_buffers = 8MB			# min 800kB
max_locks_per_transaction = 64		# min 10

It became:

shared_buffers = 256MB			# min 128kB
work_mem = 16MB				# min 64kB
temp_buffers = 32MB			# min 800kB
max_locks_per_transaction = 1024		# min 10

Executing a query to pg_locks did not give any sane information (or I just can't read between the lines).
Actually, the question is the following: where to dig, perhaps set up additional. logging? I want to understand exactly why it appeared in order to prevent further relapses. Maybe I have crooked queries that produce deadlocks? Or just increasing the size of the database requires an increase in the amount of allocated memory? Something else?
Thanks in advance.

Answer 1

https://www.postgresql.org/docs/current/runtime-co...
Has nothing to do with table sizes. Has to do with the size of the internal lock table and the number of affected objects (eg tables) in the transaction. Strictly speaking: the maximum number of object locks simultaneously by all active transactions.
Has nothing to do with shared_buffers (not subtracted from it, but added from above. shared_buffers is strictly a page buffer, even without taking into account the data structures for managing this buffer. The shared memory segment is always greater than shared_buffers) or work_mem or something else (except for max_connections + max_prepared_transactions). Moreover, it has nothing to do with deadlocks.
The default value is usually more than enough. You may be abusing partitioning.