I have a PostgreSQL cluster composed of four machines, configured as follows:

Three PostgreSQL nodes managed by Patroni and ETCD, using PgBouncer. One of these nodes is the primary, and the other two are replicas.
One machine using HAProxy to accept connections and perform load balancing.
We have a database with a table containing millions of rows. This table is an hypertable created with TimescaleDB. In our organization, we collect data from meters, which we store in this hypertable, resulting in thousands of rows being inserted daily.

However, the database cannot handle this load. The number of locks keeps increasing, and the RAM usage also continuously grows, eventually causing the primary node to crash.

For context, each node has 12GB of RAM and 16 CPU cores.

What could be causing the continuous increase in locks and RAM usage?
What are the best practices to handle high insert rates in a TimescaleDB hypertable?
How can we optimize our configuration to prevent the primary node from crashing?

I appreciate any insights or recommendations on how to address these performance issues. Thank you!

I am a young developer with little experience, and I’m doing my best to explain the situation.

PostgreSQL configuration with Patroni :

loop_wait: 10
maximum_lag_on_failover: 1048576
postgresql:
  max_parallel_workers: 16
  max_parallel_workers_per_gather: 4
  parameters:
    archive_mode: false
    autovacuum: true
    autovacuum_max_workers: 5
    autovacuum_naptime: 1min
    autovacuum_vacuum_cost_delay: 20ms
    effective_cache_size: 8GB
    effective_io_concurrency: 4
    hot_standby: true
    log_directory: /var/log/postgresql/pg_log
    log_filename: postgresql.log
    log_hostname: true
    log_statement: all
    logging_collector: true
    maintenance_work_mem: 512MB
    max_connections: 3000
    max_locks_per_transaction: 1024
    max_prepared_transactions: 0
    max_wal_senders: 10
    max_worker_processes: 16
    shared_buffers: 4096MB
    shared_preload_libraries: timescaledb
    use_pg_rewind: true
    use_slots: true
    wal_keep_segments: 128
    wal_level: hot_standby
    wal_sender_timeout: 60s
    work_mem: 64MB
retry_timeout: 10
ttl: 30

PgBouncer configuration :

logfile = /var/log/postgresql/pgbouncer.log
pidfile = /var/run/postgresql/pgbouncer.pid

;; IP address or * which means all IPs
listen_addr = 0.0.0.0
listen_port = 6432

unix_socket_dir = /var/run/postgresql

pool_mode = transaction

max_client_conn = 5000

default_pool_size = 20

reserve_pool_size = 20

reserve_pool_timeout = 2

server_idle_timeout = 5