0

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.

7
  • What version of PostgreSQL are you using?
    – jjanes
    Nov 21, 2020 at 17:25
  • Sorry, I forgot to add that information, I'm running PostgreSQL 13.1
    – Sassa
    Nov 21, 2020 at 18:57
  • Just curious: "but I cannot use this because TimescaleDB uses too much storage" --> (uncompressed) TimescaleDB shouldn't consume any more storage than Postgres, so I'm confused what you are seeing? Is it just the default index overhead, which you can turn off? (Timescale person here) Nov 22, 2020 at 3:41
  • Hmm, last time I checked the same database consumed around 300GB in TimescaleDB and 70GB in PostgreSQL, I also remember that somewhere in the TimescaleDB documentation there was something about this that recommended enabling compression to mitigate it.
    – Sassa
    Nov 22, 2020 at 19:12
  • Not sure why you don't use one table with list partitioning. The per table, per connection overhead is killing you.
    – Brian
    Nov 23, 2020 at 19:28

1 Answer 1

0

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.

4
  • The reason is because since I insert new data at specific times (every 5 minutes for 5 minute timeframe, every 15 minutes for 15 minutes timeframe and so on), normally all my connections are idle and at these times I get a lot of queries at the same time to do new data insertion. I think I can lower that number a bit or even try using PGBouncer to see if helps, but I didn't try it yet because I was not sure if this was the real issue.
    – Sassa
    Nov 21, 2020 at 19:00
  • So, considering this is the case, is it possible to calculate how much this cache can reach so I can better preview how much my database will consume in RAM?
    – Sassa
    Nov 21, 2020 at 19:18
  • Also jjanes, do you know if it is possible to get some telemetry or some other way to get the usage of this (and others) cache? I would like to be able to see it better so I can be certain where is the issue.
    – Sassa
    Nov 23, 2020 at 16:07
  • In the end, the number of connections was indeed the reason for the increase in my ram, the solution for me was to use PGBouncer so my app can still has more than100 connections but PostgreSQL itself only has 20.
    – Sassa
    Nov 27, 2020 at 20:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.