I have a PostgreSQL database that I use to store time-series (finance) data. each table contains the same table schema but has a different name based on the market pair and timeframe.
Ex. I have tables called candles_btc_usdt_one_minute, candles_eth_usdt_one_hour, candles_eth_usdt_one_week, etc.
These tables sum up to around 9000 tables in total.
Note that I know about TimescaleDB and InfluxDB, I already tested both and will post a reason I'm not using them at the end of this post.
So, since this is time-series data, it means that I'm only doing INSERT write operations and very rarely some SELECT to retrieve some data.
My issue is that the database memory usage seems to grow infinitely until I get an OOM crash. I configured my postgresql.conf using solutions as PGTune to a system with 1GB of RAM, 6 cores, and 120 connections and I limited my docker container to 4GB and still got an OOM after around one day with the system on.
I also tried other configs as 4GB of ram and 8GB in the container but PostgreSQL never respects the limit stipulated by the config and keeps using more and more RAM.
Is this the expected behavior? Maybe PostgreSQL has some other obscure config I can use to limit the memory usage in cases where there is a huge number of tables.. I'm not sure..
The reason I'm guessing this issue has something to do with my high number of tables is because the opened connections from my connection pool keep growing in memory usage faster at the start of my system (the first hours) and then the growth gets slower (but never stops).
That behavior reflects my INSERT intervals when hitting the tables.
For example, a table with a timeframe five_minutes means that every five minutes I will insert a new row to it, which means that I'm accessing these tables for the first time faster when the system starts than tables with higher timeframes as one_hour, etc.
And monitoring the memory growth, it seems that the connection process grows a little bit when it accesses a new table for the first time.
So, assuming this is right, it would mean that after some months, all the connections would have accessed all the tables at least one time and memory growth would stop. The problem with that is that I don't know how much memory this would use at the end and it's not ideal since trying to limit the memory via postgresql.conf becomes meaningless.
Here is the schema for one of the tables (as I said before, all tables has the same columns, index, etc):
data_db_prod=# \d+ candles_one_minute_btc_usdt
Table "public.candles_one_minute_btc_usdt"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
-----------+-----------------------------+-----------+----------+---------+---------+--------------+-------------
timestamp | timestamp without time zone | | not null | | plain | |
open | numeric | | not null | | main | |
close | numeric | | not null | | main | |
high | numeric | | not null | | main | |
low | numeric | | not null | | main | |
volume | numeric | | not null | | main | |
Indexes:
"candles_one_minute_btc_usdt_timestamp_desc_index" btree ("timestamp" DESC)
"candles_one_minute_btc_usdt_timestamp_index" btree ("timestamp")
Access method: heap
About other solutions
As I said before, I already tried TimescaleDB and InfluxDB.
For TimescaleDB I would be able to use a single candles table and create 2 partitions to store the market pair and the timeframe, fixing the high number of tables and probably the RAM issue I'm having, but I cannot use this because TimescaleDB uses too much storage, so I would need to use their compression feature, but a compressed hypertable doesn't allow write operations, meaning that to be able to do a backfill (which I do often) I would need to basically decompress the whole database each time.
For InfluxDB the issue is simply because they don't support any numeric/decimal type, and I cannot lose precision using double.
Feel free to suggest some other alternative I'm not aware of that would fit nicely into my use case if there is one.
Best Answer
Each backend (the process a connection is connected to) keeps a cache of data on every object it has touched. There is no provision for expiring this cache, so it does indeed grow for every object it has ever used.
Why does your connection pooler maintain 120 connections, or 20 for every CPU? This seems like a ridiculous number, especially considering you already know you have memory problems.
I find that it uses about 6.7kB for every table (with two indexes) which it touches. I used the 'max resident' line from the stats collected by
log_executor_stats=on. That isn't really suitable for use in production, but I don't know what to use in that case.