[bug]: LND crashed on postgres "out of shared memory" error

[IanPasteur]

Background

I run LND with PostgreSQL backend on a virtual server on server hardware. There is 4 GB RAM dedicated to LND and PostgreSQL only (bitcoind runs on another virtual server). I have about 8 channels opened and there is not much routing nor load at all.

Since update to 18.5-beta day before yesterday (not sure if it is related), LND crashed twice with this error:

[CRT] SQLB: Caught unhandled error: ERROR: out of shared memory (SQLSTATE 53200)
[INF] SQLB: Sending request for shutdown
[ERR] DISC: Unable to determine if node **** is advertised: unknown postgres error: ERROR: out of shared memory (SQLSTATE 53200)

This "out of memory" error is strange as there should be enough memory available (there is only 25 % of 4 GB memory used in average). Postgres memory configuration follows:

shared_buffers = 512MB
max_connections = 64  # RTL does not work well if set to lower value
work_mem = 8MB

maintenance_work_mem = 64MB  # should not be relevant for this issue
effective_cache_size = 2GB  # only an estimation for query planner

The whole database size is currently 270 MB.

There could be some issue with the db locks, as I found these errors in the postgres log:

ERROR:  out of shared memory
HINT:  You might need to increase "max_pred_locks_per_transaction".
STATEMENT:  SELECT id FROM channeldb_kv WHERE parent_id IS NULL AND key=$1 AND value IS NULL

I also found another error in postgres log and I am not sure if it is related:

ERROR:  could not serialize access due to read/write dependencies among transactions
DETAIL:  Reason code: Canceled on identification as a pivot, during write.
HINT:  The transaction might succeed if retried.
STATEMENT:  INSERT INTO channeldb_kv (key, value, parent_id) VALUES($1, $2, $3) ON CONFLICT (key, parent_id) WHERE parent_id IS NOT NULL DO UPDATE SET value=$2 WHERE channeldb_kv.value IS NOT NULL

Similar errors were mentioned in #8049 and it should be fixed already, so I am confused.

Your environment

• lnd Version: 0.18.5-beta
• Linux 6.1.0-31-amd64 #1 SMP PREEMPT_DYNAMIC Debian 6.1.128-1 (2025-02-07) x86_64 GNU/Linux
• Bitcoin Core version v28.1.0
• Dedicated virtual server for LND and PostgreSQL with 4 GB RAM and 4 vCPU. Server hardware (Xeon CPU, ECC RAM, SSD drives with RAID, ...)

Steps to reproduce

I will provide more details if I would be able to discover some steps to reproduce.

Expected behaviour

LND with postgres does not consume nor demand so much RAM with such low load.

Actual behaviour

Postgres returns "out of shared memory" and LND crashes.

[Roasbeef]

Is it able to run consistently with this extra section in the config?

max_locks_per_transaction = 128
max_pred_locks_per_transaction = 1024

The shared memory error here is actually complaining about there not being enough locks in a given transaction.

[DerEwige]

Is it able to run consistently with this extra section in the config?

max_locks_per_transaction = 128
max_pred_locks_per_transaction = 1024

The shared memory error here is actually complaining about there not being enough locks in a given transaction.

afaik it is the other way around

it is out of shared memory, because lnd uses 50 connections to the DB per default

lnd/sample-lnd.conf

Line 1573 in 27440e8

; db.postgres.maxconnections=50

so especially during bootstraping of the graph there are a lot of parallel transactions running and

max_locks_per_transaction = 128
max_pred_locks_per_transaction = 1024

will only increase the memory usage

postgress's best practice is for one client not to use more than 2x[number of cores] connections
so if your postgress sql has 4 cores, lnd should not use more than 8 connections

futhermore lnd uses
Serializable isolation

Which is the savest, but causes the most conflicting transactions, so a minimum amount of parallel transactions should be used

[IanPasteur]

Thank you both for the reply 🙌

I will try to reduce db.postgres.maxconnections to 8, then?

Is there some documentation about recommended lnd and postgresql parameters? I could't find any on github or in the docs

[djkazic]

@IanPasteur the error is not because of the connection count. It is because of the locks configuration, please raise both and restart postgres to see if the issue is resolved. The maxconnections value of 50 is just an upper limit and is typically not hit with KVDB access patterns.

Even if you reduce the connections count you will still encounter that shared memory error eventually with a burst of DB activity.

The serialize errors are expected and benign -- they are an artifact of the KVDB schema we use to store data in postgres and will be phased out once SQL schema migration paths are available.

[IanPasteur]

Thanks for clearing this out.

I encountered higher connection count while I am browsing in RTL web interface. So I downscale db.postgres.maxconnections to 16 for lnd.

PostgreSQL is configured currently this way:

max_connections = 24
shared_buffers = 512MB
work_mem = 8MB
max_locks_per_transaction = 128
max_pred_locks_per_transaction = 1024

I will let you know how it works in few days.

PS: Are these tuning parameters recommended somewhere? Should I open a PR to LND docs?

[saubyk]

Are these tuning parameters recommended somewhere?

here's a gist authored by @djkazic
https://gist.github.com/djkazic/526fa3e032aea9578997f88b45b91fb9

[saubyk]

moving this thread to a discussion instead. Can open an issue, if there is something to fix

[DerEwige]

here's a gist authored by @djkazic https://gist.github.com/djkazic/526fa3e032aea9578997f88b45b91fb9

It is important to note that those tests were conducted on a PostgreSQL setup with 6 cores, 12 threads, and 32GB of RAM, with 6GB of shared memory allocated in PostgreSQL settings.
These settings are not applicable for a machine with 4 cores and 4GB of RAM.

One cannot just throw around these numbers for someone using a Raspberry Pi as their PostgreSQL server.

Breaking It Down

Each connection an application makes to PostgreSQL can hold one active transaction at a time.
Some of these transactions can use multiple parallel workers, depending on:

• Number of active connections
• max_parallel_workers_per_gather (workers per query)
• max_parallel_workers (total parallel workers available)
• Other cost-based planner decisions

However, having more parallel workers than the system can actually utilize is pointless. It won’t improve performance but can increase lock contention, which may lead to longer transaction durations and higher conflict rates. This results in more transaction rollbacks, requiring application retries, ultimately decreasing overall performance.

Optimal Connection Limits

There is no single "best" number of connections for every workload, but for a high-conflict workload, running significantly more connections than CPU cores can handle is counterproductive.

A widely accepted guideline (not a strict rule) is to keep active connections around 2× the number of cores/threads.
Beyond that point, context switching overhead and transaction conflicts typically outweigh any performance benefits.

Additionally, most performance tests and online benchmarks assume the default isolation level Read Committed, which is optimized for parallel workloads.

The Impact of Serializable Isolation

LND uses Serializable isolation, which is stricter than the default Read Committed level.
Serializable ensures transactions execute as if they happened in a serial order, preventing anomalies but increasing rollback rates when conflicts occur.

In high-contention workloads, Serializable transactions frequently abort and require retries, leading to wasted work. This makes Serializable a poor choice for workloads with heavy parallel execution and frequent write conflicts.

For a workload like this, tuning max_locks_per_transaction and max_pred_locks_per_transaction can help reduce lock exhaustion, but this does not prevent transaction conflicts—it only prevents PostgreSQL from running out of locks.

Conclusion

In my opinion, increasing max_locks_per_transaction and max_pred_locks_per_transaction only addresses the symptoms of the issue and is only effective if you have sufficient memory to support it. The more sustainable solution is to decrease the number of active connections to a reasonable amount. By doing so, you reduce the number of parallel transactions, which directly addresses the root cause of the problem—excessive lock contention—without negatively impacting performance. This approach helps maintain a balance between system resources and workload demands, especially in environments with limited hardware.

[djkazic]

This is because LND waits for connections to become available rather than just blindly creating new connections per query:

lnd/kvdb/sqlbase/db_conn_set.go

Lines 51 to 57 in 5fe900d

	// Limit maximum number of open connections. This is useful to prevent
	// the server from running out of connections and returning an error.
	// With this client-side limit in place, lnd will wait for a connection
	// to become available.
	if d.maxConnections != 0 {
	db.SetMaxOpenConns(d.maxConnections)
	}

[djkazic]

Therefore, even on a RPI the defaults are totally okay to use.

[DerEwige]

I just realized I must share some more info.
Because there was some discussion going on in some telegram channels before I came to this issue/discussion.

Also please edit your response before posting, I was reading while you were still editing and lost may location multiple times.

There have been instances where the max number of retries on the database have been reached.

My Golang is not really good, so I’m not sure which line actually applies.

lnd/kvdb/sqlbase/db.go

Line 27 in 5fe900d

DefaultNumTxRetries = 50

lnd/sqldb/interfaces.go

Line 25 in 5fe900d

DefaultNumTxRetries = 20

[ERR] DISC: unable to add node 03fd1da48a1e6320cd110e6b0190a83c902c2b47a23f1018b77cb3c1d24c0be6bd to the graph: db tx retries exceeded

some of the PG logs at the time of lnd error

2025-02-20 11:04:49.154 CET [36197] lnduser@lndb ERROR:  could not serialize access due to read/write dependencies among transactions
2025-02-20 11:04:49.154 CET [36197] lnduser@lndb DETAIL:  Reason code: Canceled on identification as a pivot, during conflict out checking.
2025-02-20 11:04:49.154 CET [36197] lnduser@lndb HINT:  The transaction might succeed if retried.
2025-02-20 11:04:49.154 CET [36197] lnduser@lndb STATEMENT:  SELECT key, value FROM channeldb_kv WHERE parent_id=18 AND key>$1 ORDER BY key LIMIT 1
2025-02-20 11:04:49.689 CET [36208] lnduser@lndb ERROR:  could not serialize access due to read/write dependencies among transactions
2025-02-20 11:04:49.689 CET [36208] lnduser@lndb DETAIL:  Reason code: Canceled on identification as a pivot, during conflict in checking.
2025-02-20 11:04:49.689 CET [36208] lnduser@lndb HINT:  The transaction might succeed if retried.
2025-02-20 11:04:49.689 CET [36208] lnduser@lndb STATEMENT:  INSERT INTO channeldb_kv (key, value, parent_id) VALUES($1, $2, $3) ON CONFLICT (key, parent_id) WHERE parent_id IS NOT NULL DO UPDATE SET value=$2 WHERE channeldb_kv.value IS NOT NULL

If I understand it correctly this error only shows up it the same transaction fail at least 20 times and reach this line in the code:

lnd/sqldb/interfaces.go

Line 310 in 5fe900d

return ErrRetriesExceeded

So there are at least some scenarios where a transaction conflicts may arise.

This particular case was also very interesting, because it is an empty node that is only used to get the graph info. The node was offline far a while, came online connected to its pinned syncing peers and run into this issue during initial graph sync. While the issue went away after the sync was done.

Would you agree that LND probably overloaded the DB int his particular scenario?

[DerEwige]

This is because LND waits for connections to become available rather than just blindly creating new connections per query:

lnd/kvdb/sqlbase/db_conn_set.go

Lines 51 to 57 in 5fe900d

	// Limit maximum number of open connections. This is useful to prevent
	// the server from running out of connections and returning an error.
	// With this client-side limit in place, lnd will wait for a connection
	// to become available.
	if d.maxConnections != 0 {
	db.SetMaxOpenConns(d.maxConnections)
	}

I am aware of that.
that number used here is read from this variable here right:

lnd/sample-lnd.conf

Line 1573 in 27440e8

; db.postgres.maxconnections=50

With these default settings that means that LND will queue up to 50 transactions in parallel if for whatever reason the DB cannot process them faster than LND sends them right?

[DerEwige]

here is another example of a big node having problems because LND fires to many queries during startup and they have to kill some by hand on the DB server to prevent LND from failing during startup:

#9532

Database Query Overload at Startup:
Each startup triggers an excessive number of PostgreSQL queries, leading to a bottleneck. Many queries get stuck, and if not manually terminated, they eventually cause the node to crash.