Timescaledb high resource consume

Question

I’m working with a TimescaleDB instance that has 10 CPU cores and 10 GiB of RAM, currently hosting approximately 9 million rows, which is continually growing. I'm encountering resource limitations and need guidance on optimizing performance.

My maximum allowed connections are set to 1000, but I typically have around 100-115 active connections for batch data insertion. Each connection takes a considerable amount of time to complete its operations. I understand that maintaining a high number of long-running connections is not ideal, but I'm unsure how to address this issue effectively in a situation where I have a huge amount of data which must be synced with the database regularly.

Any suggestions for improving performance or managing connections?

Answer 1

I can certainly say the problem is not related to timescaledb itself but how you're loading your data.

Here are a few ideas to think:

1. You can add a connection pooler and it can help to manage the idle connections but,
2. 1k connections will not save your app or make it inserts faster, it will just eat more memory and allow more connections to be waiting. Remember the IO is limited, so it doesn't matter if you have more connections.
3. I'd start by limiting to the amount of connections to the number of cores or double, like 24 connections will be enough to already make your IO 100% busy.
4. Explore and play with the batch size and optimize it. Test with 1k records in a single transaction, later test with 5k, 10k, 20k and so on.
5. If your data is time series, use tsbs to benchmark it and get to know what config works best in your config/workload: https://github.com/timescale/tsbs