Repository navigation
postgres nodes with "many" on chain transactions are very slow #11289
Description
I'm getting slow performance on postgres nodes with "many" on chain transactions. For example:
frank-7658f6fcd6-fkvlw:/$ echo "utxos: $(lncli listunspent | jq '.utxos | length')"
utxos: 230
frank-7658f6fcd6-fkvlw:/$ echo "txns: $(lncli listchaintxns | jq '.transactions | length')"
txns: 357
frank-7658f6fcd6-fkvlw:/$ time lncli walletbalance
{
"total_balance": "386766785",
"confirmed_balance": "386766785",
"unconfirmed_balance": "0",
"locked_balance": "0",
"reserved_balance_anchor_chan": "50000",
"account_balance": {
"default": {
"confirmed_balance": "386766785",
"unconfirmed_balance": "0"
}
}
}
real 0m13.934s
user 0m0.027s
sys 0m0.023s
frank-7658f6fcd6-fkvlw:/$
This is way too slow and makes metrics scraping with lndmon time out.
Behavior seems the same on both v0.20.5 and v0.21.4
I have db_use_native_sql: "true"
Here is my postgres config:
$ kubectl describe -n smokenet-dev2 cluster
Name: cnpg-cluster
Namespace: smokenet-dev2
Labels: app.kubernetes.io/managed-by=Helm
Annotations: meta.helm.sh/release-name: smokenet-dev
meta.helm.sh/release-namespace: smokenet-dev2
API Version: postgresql.cnpg.io/v1
Kind: Cluster
Metadata:
Creation Timestamp: 2026-09-18T22:11:59Z
Generation: 1
Resource Version: 126832190
UID: 5ab3fef9-6701-427f-9753-55bbaa6250a9
Spec:
Affinity:
Pod Anti Affinity Type: preferred
Backup:
Target: prefer-standby
Volume Snapshot:
Online: true
Online Configuration:
Immediate Checkpoint: false
Wait For Archive: true
Snapshot Owner Reference: none
Bootstrap:
Initdb:
Database: app
Encoding: UTF8
Locale C Type: C
Locale Collate: C
Owner: app
Enable PDB: true
Enable Superuser Access: false
Failover Delay: 0
Image Name: ghcr.io/cloudnative-pg/postgresql:16.4
Instances: 1
Log Level: info
Managed:
Roles:
Connection Limit: -1
Ensure: present
Inherit: true
Login: true
Name: frank-db-postgres-user
Password Secret:
Name: frank-db-postgres-credentials
Connection Limit: -1
Ensure: present
Inherit: true
Login: true
Name: george-db-postgres-user
Password Secret:
Name: george-db-postgres-credentials
Connection Limit: -1
Ensure: present
Inherit: true
Login: true
Name: henry-db-postgres-user
Password Secret:
Name: henry-db-postgres-credentials
Connection Limit: -1
Ensure: present
Inherit: true
Login: true
Name: matt-db-postgres-user
Password Secret:
Name: matt-db-postgres-credentials
Max Sync Replicas: 0
Min Sync Replicas: 0
Monitoring:
Custom Queries Config Map:
Key: queries
Name: cnpg-default-monitoring
Disable Default Queries: false
Enable Pod Monitor: false
Postgres GID: 26
Postgres UID: 26
Postgresql:
Parameters:
archive_mode: on
archive_timeout: 5min
auto_explain.log_analyze: on
auto_explain.log_buffers: on
auto_explain.log_min_duration: 500ms
autovacuum_vacuum_cost_limit: 2000
checkpoint_timeout: 30min
dynamic_shared_memory_type: posix
effective_io_concurrency: 32
full_page_writes: on
Jit: off
jit_above_cost: -1
log_destination: csvlog
log_directory: /controller/log
log_filename: postgres
log_rotation_age: 0
log_rotation_size: 0
log_truncate_on_rotation: false
logging_collector: on
max_connections: 200
max_locks_per_transaction: 512
max_parallel_workers: 32
max_pred_locks_per_transaction: 4096
max_replication_slots: 32
max_wal_size: 16GB
max_worker_processes: 32
random_page_cost: 1.1
shared_buffers: 8000MB
shared_memory_type: mmap
shared_preload_libraries:
ssl_max_protocol_version: TLSv1.3
ssl_min_protocol_version: TLSv1.3
wal_keep_size: 512MB
wal_level: logical
wal_log_hints: on
wal_receiver_timeout: 5s
wal_sender_timeout: 5s
work_mem: 64MB
pg_hba:
host frank frank-db-postgres-user all scram-sha-256
host george george-db-postgres-user all scram-sha-256
host henry henry-db-postgres-user all scram-sha-256
host matt matt-db-postgres-user all scram-sha-256
shared_preload_libraries:
auto_explain
Sync Replica Election Constraint:
Enabled: false
Primary Update Method: restart
Primary Update Strategy: unsupervised
Probes:
Liveness:
Isolation Check:
Connection Timeout: 1000
Enabled: true
Request Timeout: 1000
Replication Slots:
High Availability:
Enabled: true
Slot Prefix: _cnpg_
Synchronize Replicas:
Enabled: true
Update Interval: 30
Resources:
Limits:
Cpu: 4
Memory: 12000Mi
Requests:
Cpu: 400m
Memory: 8000Mi
Smart Shutdown Timeout: 180
Start Delay: 3600
Stop Delay: 1800
Storage:
Resize In Use Volumes: true
Size: 40Gi
Switchover Delay: 3600
Status:
Available Architectures:
Go Arch: amd64
Hash: 1fa32bddc89d8eb9de4a448660db1f1d0227e0269b4ffdcb98e6dbbcffe12cf9
Go Arch: arm64
Hash: e340717194993e45f026a61c78a424a5eba6a3b628e6c2d1d617ae1a94a58ee0
Certificates:
Client CA Secret: cnpg-cluster-ca
Expirations:
Cnpg - Cluster - Ca: 2026-12-17 22:06:59 +0000 UTC
Cnpg - Cluster - Replication: 2026-12-17 22:06:59 +0000 UTC
Cnpg - Cluster - Server: 2026-12-17 22:06:59 +0000 UTC
Replication TLS Secret: cnpg-cluster-replication
Server Alt DNS Names:
cnpg-cluster-rw
cnpg-cluster-rw.smokenet-dev2
cnpg-cluster-rw.smokenet-dev2.svc
cnpg-cluster-rw.smokenet-dev2.svc.cluster.local
cnpg-cluster-r
cnpg-cluster-r.smokenet-dev2
cnpg-cluster-r.smokenet-dev2.svc
cnpg-cluster-r.smokenet-dev2.svc.cluster.local
cnpg-cluster-ro
cnpg-cluster-ro.smokenet-dev2
cnpg-cluster-ro.smokenet-dev2.svc
cnpg-cluster-ro.smokenet-dev2.svc.cluster.local
Server CA Secret: cnpg-cluster-ca
Server TLS Secret: cnpg-cluster-server
Cloud Native PG Commit Hash: 1ecc48340
Cloud Native PG Operator Hash: 1fa32bddc89d8eb9de4a448660db1f1d0227e0269b4ffdcb98e6dbbcffe12cf9
Conditions:
Last Transition Time: 2026-09-18T22:12:30Z
Message: A single, unique system ID was found across reporting instances.
Reason: Unique
Status: True
Type: ConsistentSystemID
Last Transition Time: 2026-09-18T22:12:37Z
Message: Cluster is Ready
Reason: ClusterIsReady
Status: True
Type: Ready
Last Transition Time: 2026-09-18T22:12:30Z
Message: Continuous archiving is working
Reason: ContinuousArchivingSuccess
Status: True
Type: ContinuousArchiving
Config Map Resource Version:
Metrics:
Cnpg - Default - Monitoring: 126830363
Current Primary: cnpg-cluster-1
Current Primary Timestamp: 2026-09-18T22:12:29.739791Z
Healthy PVC:
cnpg-cluster-1
Image: ghcr.io/cloudnative-pg/postgresql:16.4
Instance Names:
cnpg-cluster-1
Instances: 1
Instances Reported State:
cnpg-cluster-1:
Ip: 10.0.0.115
Is Primary: true
Time Line ID: 1
Instances Status:
Healthy:
cnpg-cluster-1
Latest Generated Node: 1
Managed Roles Status:
By Status:
Not - Managed:
app
Reconciled:
frank-db-postgres-user
george-db-postgres-user
henry-db-postgres-user
matt-db-postgres-user
Reserved:
postgres
streaming_replica
Password Status:
Frank - Db - Postgres - User:
Resource Version: 126830164
Transaction ID: 733
George - Db - Postgres - User:
Resource Version: 126830165
Transaction ID: 734
Henry - Db - Postgres - User:
Resource Version: 126830158
Transaction ID: 735
Matt - Db - Postgres - User:
Resource Version: 126830160
Transaction ID: 736
Pg Data Image Info:
Image: ghcr.io/cloudnative-pg/postgresql:16.4
Major Version: 16
Phase: Cluster in healthy state
Pooler Integrations:
Pg Bouncer Integration:
Pvc Count: 1
Read Service: cnpg-cluster-r
Ready Instances: 1
Secrets Resource Version:
Application Secret Version: 126830250
Client Ca Secret Version: 126830247
Managed Role Secret Version:
Frank - Db - Postgres - Credentials: 126830164
George - Db - Postgres - Credentials: 126830165
Henry - Db - Postgres - Credentials: 126830158
Matt - Db - Postgres - Credentials: 126830160
Replication Secret Version: 126830249
Server Ca Secret Version: 126830247
Server Secret Version: 126830248
Switch Replica Cluster Status:
System ID: 7687001634637332503
Target Primary: cnpg-cluster-1
Target Primary Timestamp: 2026-09-18T22:11:59.775043Z
Timeline ID: 1
Topology:
Instances:
cnpg-cluster-1:
Nodes Used: 1
Successfully Extracted: true
Write Service: cnpg-cluster-rw
Events: <none>
$
$ kubectl describe -n smokenet-dev2 database frank henry
Name: frank
Namespace: smokenet-dev2
Labels: app.kubernetes.io/managed-by=Helm
Annotations: meta.helm.sh/release-name: smokenet-dev
meta.helm.sh/release-namespace: smokenet-dev2
API Version: postgresql.cnpg.io/v1
Kind: Database
Metadata:
Creation Timestamp: 2026-09-18T22:11:59Z
Finalizers:
cnpg.io/deleteDatabase
Generation: 1
Resource Version: 126832516
UID: 88b23fc2-7f13-463a-b7db-a30e1b9bb91f
Spec:
Cluster:
Name: cnpg-cluster
Database Reclaim Policy: retain
Ensure: present
Name: frank
Owner: frank-db-postgres-user
Status:
Applied: true
Observed Generation: 1
Events: <none>
Name: henry
Namespace: smokenet-dev2
Labels: app.kubernetes.io/managed-by=Helm
Annotations: meta.helm.sh/release-name: smokenet-dev
meta.helm.sh/release-namespace: smokenet-dev2
API Version: postgresql.cnpg.io/v1
Kind: Database
Metadata:
Creation Timestamp: 2026-09-18T22:11:59Z
Finalizers:
cnpg.io/deleteDatabase
Generation: 1
Resource Version: 126832540
UID: 17ba1b97-5739-49a0-b722-826e8844f57a
Spec:
Cluster:
Name: cnpg-cluster
Database Reclaim Policy: retain
Ensure: present
Name: henry
Owner: henry-db-postgres-user
Status:
Applied: true
Observed Generation: 1
Events: <none>
$
The shared buffers look to be properly allocated
postgres@cnpg-cluster-1:/$ psql
psql (16.4 (Debian 16.4-1.pgdg110+2))
Type "help" for help.
postgres=# SHOW shared_buffers;
SELECT name, setting, unit, pending_restart
FROM pg_settings
WHERE name = 'shared_buffers';
shared_buffers
----------------
8000MB
(1 row)
name | setting | unit | pending_restart
----------------+---------+------+-----------------
shared_buffers | 1024000 | 8kB | f
(1 row)
postgres=# SELECT name, pg_size_pretty(allocated_size) AS allocated
FROM pg_shmem_allocations
WHERE name = 'Buffer Blocks';
-- Total shared memory segment
SELECT pg_size_pretty(sum(allocated_size)) FROM pg_shmem_allocations;
SHOW shared_memory_size; -- PG 15+
name | allocated
---------------+-----------
Buffer Blocks | 8000 MB
(1 row)
pg_size_pretty
----------------
8467 MB
(1 row)
shared_memory_size
--------------------
8468MB
(1 row)
postgres=# CREATE EXTENSION IF NOT EXISTS pg_buffercache;
-- PG 16+
SELECT * FROM pg_buffercache_summary();
-- Older versions
SELECT count(*) AS total,
count(*) FILTER (WHERE relfilenode IS NOT NULL) AS used,
count(*) FILTER (WHERE relfilenode IS NULL) AS unused
FROM pg_buffercache;
CREATE EXTENSION
buffers_used | buffers_unused | buffers_dirty | buffers_pinned | usagecount_avg
--------------+----------------+---------------+----------------+-------------------
432259 | 591741 | 14703 | 0 | 4.962857916203017
(1 row)
total | used | unused
---------+--------+--------
1024000 | 432259 | 591741
(1 row)
postgres=#
Activity
ziggie1984 commentedon Oct 1, 2026
Could you please try a build from current
master(at or after merge commitafcea30d9b) and report thelncli walletbalancetiming?PR #11264 recently added transaction-local caching for SQL KV nested-bucket lookups. It was motivated by slow PostgreSQL
WalletBalancecalls and reduced one reproduction from 6,311 to 2,477 queries (6.8s to 2.4s with simulated query latency). Neither v0.20.5 nor v0.21.4 contains that change, so testing master would tell us whether this is the same issue or whether significant UTXO-dependent latency remains.If possible, please run
walletbalancea few times and include the timings for each run.ZZiigguurraatt commentedon Oct 2, 2026
I deployed https://hub.docker.com/layers/lightninglabs/lndinit/daily-testing-20261002/images/sha256-a584693a0be67f3f38a1c34e79d200ef0926d346434d07ab7680c256ad6407b6
Also, here are a few more runs of walletbalance
So, it looks like walletbalance has improved but is still a bit slow.
Also, some other parts of the node is still running very slow and lndmon times out,
ZZiigguurraatt commentedon Oct 8, 2026
OK, so it seems
PendingChannelsis the bottleneck.walletbalancemay now be slow as a side effect because lndmon hittingPendingChannelsis slowing this node down.also,
seems weird because it has
funding_expiry_blocksnegative.ZZiigguurraatt commentedon Oct 8, 2026
The previous message was from data captured 6 days ago. I tried to reproduce it again yesterday and I could never get
PendingChannelsto respond after 15 minutes.I then ran a profile capture
which clearly confirms
PendingChannelsis the bottleneck.ZZiigguurraatt commentedon Oct 8, 2026
Today I notice there are many postgres processes fighting for CPU:
Note: I am running lightninglabs/lndmon#139 , so lndmon should cancel the gRPC call after lndmon times out.
So, it seems
PendingChannelsis lingering after the gRPC call disconnects, rather than aborting, which makes the node get more and more bogged down over time by lndmon hitting it, on top of whatever performance issuesPendingChannelsalready has.So, I think there are two problems
PendingChannelsis too slow.PendingChannelsdoes not abort work when the gRPC client disconnects, making the problem worse.It's possible we don't care about (1) because this is going to be fixed by the channels native SQL migration. It's possible (2) will be mitigated the channels native SQL migration, but I'm wondering if we should address that before doing the native SQL migration since we can see (2) now due to (1) and won't hit it some time in the future when native SQL eventually hits some scaling slowdowns.
ZZiigguurraatt commentedon Oct 19, 2026