Re: BUG #16363: Memory increases for each table accessed until connection is closed

Sending the reply again since it seems that it didn't go through the
mailing list

Hello Tom, first of all, thanks for the response!

Sorry to not have added a lot of the needed information, I will try to
rectify that in this message.

So, first is what I'm trying to do, in my backend, I store financial data
(candlesticks and trades) in Postgres + TimescaleDB, the way I do it is
separating each exchange in one database and each market and timeframe in
one table, for example, giving that I have `ExchangeA` with 1 market
EUR/USD and 4 timeframes, I would get these 5 tables:
candles_eur_usd_one_minute
candles_eur_usd_five_minutes
candles_eur_usd_third_minutes
candles_eur_usd_one_hour
trades_eur_usd

Normally in my backend, I have, per database (exchange) ~750 markets, and
~16 timeframes, so 750 * 16 = 12000 tables.

I noticed that Postgres would continuously increase my memory usage per
connection until I got an OOM error, which, in my backend terms, means
inserting candles and trades, ~2000 rows per minute. For a system with
16GB, it would take like 1 to 3 hours and then I would get the OOM. For my
other system which has 64GB, it would take like 2 days but it would come a
time when Postgres would use everything and crash too.

For versions, I was using Postgres 11 with latest TimescaleDB, since I was
not sure if this was a TimescaleDB issue, I first created a minimal example
that triggered the issue faster (the one I talked in the first e-mail that
uses 700 tables) and then tested without TimescaleDB in Postgres 11 and 12.
I got the same result in both, so removing TimescaleDB out of the equation.

For the operational system, I'm using ubuntu, but I do test it in my Gentoo
machine with the same results, and I also tested it using Postgres in
docker.

For postgresql.conf, I was using first one tunned by the TimescaleDB tuner
software, but then changed to a default one to see if it would make a
change.

My backend is written in Elixir, so I'm using Ecto library to connect with
Postgres, Ecto will create a pool of connections that will be alive as long
as my backend is alive too. To make it easier for me to test the issue, I
limited the number of connections to 1, so I can keep an eye on that
connection process memory usage, etc.

> Yes, Postgres caches some information about every table your session
> has accessed, and no you don't have a lot of control over that, and
> no it isn't a bug.

> Having said that, the amount of memory consumed this way is in the
> vicinity of a few tens of KB per table, according to some simple
> experiments I just did. I couldn't get a process touching 700 simple
> tables to eat more than ~70MB of space. So either you are talking about
> tables with enormously complex schemas, or there's something else going
> on. (Please note that the SO post you are referencing describes an
> attempt to use circa 100000 tables/views in one session, not 700.)

Here are the details of one of the tables I'm using to do my test (all of
then are equal, just the name is different):
test_dev=# \d+ trades_eur_usd
Table "public.trades_eur_usd"
Column | Type | Collation | Nullable | Default |
Storage | Stats target | Description
-----------+-----------------------------+-----------+----------+---------+---------+--------------+-------------
id | integer | | not null | |
plain | |
timestamp | timestamp without time zone | | not null | |
plain | |
amount | numeric | | not null | |
main | |
price | numeric | | not null | |
main | |
Indexes:
"trades_eur_usd_id_desc_index" btree (id DESC)
"trades_eur_usd_timestamp_desc_index" btree ("timestamp" DESC)
Access method: heap

One thing that I forgot to explain better is what I'm doing to touch those
tables. Basically I created a little Elixir backend with Ecto which will
keep 1 connection alive for the pool and start a bulk insertion of rows to
one of the 700 tables, so, basically, the steps are:
1) Choose one of the tables randomly;
2) Insert in one query 13000 rows to that table;
3) Go back to step 1 and repeat.

This will trigger the issue instantly and will make the memory usage of
that connection grow very fast as shown in the last e-mail.

One very important thing to note is that if I change the above algorithm to
do the same thing, but always choose the same table in step 1 (so I will do
the bulk insertion always in 1 table instead of one of the 700), I will not
trigger the issue, memory usage will not go up at all. it only triggers it
if I'm doing the insertion in different tables each time.

> The memory consumption curve you showed looks suspiciously like you have
> circa 4GB shared buffers allocated and the reported number is mostly about
> how many of those buffers the process has touched so far. This is a
> common misunderstanding; "top" and similar tools tend to be really bad
> about accounting for shared memory usage in any useful way. In any case,
> the process you've shown us stopped growing its space consumption
> some time ago, so I wonder where the OOM complaint actually came from.

> The short answer is that you're probably barking up the wrong tree,
> but there's not enough information here to provide any useful guesses
> about which is the right tree.

I guess you are right, maybe the issue is with some shared buffers cache or
something, I tried lowering to the minimum value each possible option in
postgresql.conf that I found but still got the same OOM result.

Thanks a lot for the help, and feel free to ask for more information!