Postgres max_locks_per_transaction Increase

einaraglenPRO

a year ago

I have a Postgres database with TimescaleDB plugin

It has massive inserts of timeseries data, but it keeps choking:

`
2024-05-06 12:40:36.283 UTC [40419] ERROR: out of shared memory

2024-05-06 12:40:36.283 UTC [40419] HINT: You might need to increase maxlocksper_transaction.
`

But if i view the memory "Metrics" graph i see no indication that i am overstepping memory usage, so i went to check max_locks_per_transaction

`
railway=# SHOW maxlocksper_transaction;

maxlocksper_transaction

64
(1 row)
`

Now i am wondering how i can change this, since i dont have access to :
`
railway=# SHOW config_file;

config_file

/var/lib/postgresql/data/pgdata/postgresql.conf
(1 row)
`

i have tried ALTER SYSTEM SET max_locks_per_transaction = 1024;

but this does not change anything, any tips?

0 Replies

einaraglenPRO

a year ago

d14883c4-9060-44a4-b3a1-76b91ed13c00


einaraglenPRO

a year ago

for ref: this is the Timescale + PostGIS template database

if i do the same steps for the pure Timescale template it actually works!


a year ago

Why not just increase the shared memory? you're Pro so you can set RAILWAY_SHM_SIZE_BYTES to 268435456 that will increase the shared memory from 64MB to 256MB


Postgres max_locks_per_transaction Increase - Railway Help Station