psycopg3[binary] consumes a lot more memory than psycopg2-binary for large select in requests

[nmoreaud]

Hello

I have the following sqlalchemy code.

while True:
    for i in range(50):
        offset = random.randrange(20000, 50000)
        load(offset)
    gc.collect()
    print(str(memory()))

def load(offset):
    # select col1, col2, rel_name from table1; 
    q = cnx.session.query(Table1.col1, Table1.col2, Table1.rel_name)
    q = q.limit(50000)
    q = q.offset(offset)
    rel_names = {t.rel_name for t in q.all()}

    # select col1, col2 from table2 where name in ('val1', 'val3', 'val4000')
    q = (
        session.query(Table2.col1, Table2.col2, ...)
        .filter(Table2.name.in_(rel_names))
    )
    q.all()

I have not translated it to psycopg, but here is the idea: it uses a lot more RAM with psycopg v3 than v2, and memory usage tends to increase over time (yet it hits a fixed limit).
I suspect that there is a cache on select queries, that fills memory.

Memory evolution over time:

Here is a tracemalloc data:

"<string>:1: size=9000 KiB (+8284 KiB), count=127393 (+117810), average=72 B
/project/.venv/lib/python3.8/site-packages/psycopg/_queries.py:355: size=7353 KiB (+6854 KiB), count=126318 (+117757), average=60 B
/project/.venv/lib/python3.8/site-packages/psycopg/_queries.py:306: size=5459 KiB (+5073 KiB), count=126318 (+117757), average=44 B
/project/.venv/lib/python3.8/site-packages/psycopg/_queries.py:63: size=3200 KiB (+2960 KiB), count=69 (+42), average=46.4 KiB
/project/.venv/lib/python3.8/site-packages/psycopg/_queries.py:195: size=1964 KiB (+1813 KiB), count=133 (+84), average=14.8 KiB
/project/.venv/lib/python3.8/site-packages/psycopg/_queries.py:184: size=1042 KiB (+971 KiB), count=63 (+42), average=16.5 KiB
/project/.venv/lib/python3.8/site-packages/psycopg/_queries.py:182: size=1042 KiB (+971 KiB), count=63 (+42), average=16.5 KiB
/project/.venv/lib/python3.8/site-packages/psycopg/_queries.py:365: size=1042 KiB (+971 KiB), count=61 (+42), average=17.1 KiB
/usr/lib/python3.8/tracemalloc.py:532: size=122 KiB (+122 KiB), count=2212 (+2211), average=56 B
/project/.venv/lib/python3.8/site-packages/psycopg/_queries.py:75: size=10.2 KiB (+5800 B), count=140 (+84), average=75 B
/project/.venv/lib/python3.8/site-packages/psycopg/cursor.py:864: size=535 KiB (+5269 B), count=7935 (+75), average=69 B
/project/.venv/lib/python3.8/site-packages/psycopg/_queries.py:314: size=3920 B (+2352 B), count=70 (+42), average=56 B
/project/.venv/lib/python3.8/site-packages/psycopg/_queries.py:164: size=3920 B (+2352 B), count=70 (+42), average=56 B
/project/.venv/lib/python3.8/site-packages/psycopg/_queries.py:175: size=3528 B (+2352 B), count=63 (+42), average=56 B
/project/.venv/lib/python3.8/site-packages/psycopg/_queries.py:310: size=3465 B (+1806 B), count=63 (+42), average=55 B
/project/.venv/lib/python3.8/site-packages/sqlalchemy/sql/cache_key.py:280: size=4808 B (+1712 B), count=9 (+3), average=534 B
/project/.venv/lib/python3.8/site-packages/psycopg/_adapters_map.py:76: size=9976 B (+1512 B), count=60 (+9), average=166 B
/project/.venv/lib/python3.8/site-packages/sqlalchemy/sql/default_comparator.py:51: size=1461 B (+1461 B), count=2 (+2), average=730 B
/usr/lib/python3.8/tracemalloc.py:397: size=728 B (+728 B), count=4 (+4), average=182 B
/project/.venv/lib/python3.8/site-packages/psycopg/_adapters_map.py:75: size=9992 B (+672 B), count=66 (+4), average=151 B"

Do you think something could be done to avoid such an overhead?

[dvarrazzo]

In _queries.py there is an @lru_cache function but the cache size is limited to 128. The content of the cache is a result of the processing of the query (not of the result). It seems that the overhead is around 100Mb: it seems unlikely that each query takes 1Mb of footprint.

You use SQLAlchemy. Can you make a stand-alone script showing the memory footprint instead?

[nmoreaud]

Here is a standalone script: test.py
Simpler script: simple_test.txt

My conclusion is that psycopg3 doesn't like variable select in sizes.

[nmoreaud]

And you are right, ANY works much faster than IN without memory increase

[dvarrazzo]

Psycopg doesn't parse the query, so it cannot figure out it contains an IN () construct.

Yes, IN is used often as an operator, but usually with values taken from a list, not with a list of placeholders. Because psycopg 3 uses server-side parameters, the construct IN %s (with values in a tuple) cannot be used. So we advise explicitly to use = any(%s) (with values in a list).

The cache on query parsing has a positive effect; yours is a pathological case which has a clear and documented workaround. I don't wish to remove an useful optimisation because MSSQL may not support the any() operator: this is way outside our domain of interest.

[CaselIT]

@dvarrazzo maybe a compromise could be to disable caching for queries that have a large number of parameteres? say 64+ (I'm sure you have a better number in mind here)

btw sqlalchemy absolutely uses parametes in the in, it does not embed them inside the query string, so such a dropoff would "solve" this case.

Of course then there is the opposite problem: the user uses an in with a large number of params, say 500, but it's batching the queries, to it will executes 100 queries with 500 values inside the in, plus a final one with the remaining values.

Overall I'm not sure what's best here.

Regarding any(), in most case pg will convert the in to any for you (at least that's what it reports when explain ...)

[dvarrazzo]

@dvarrazzo maybe a compromise could be to disable caching for queries that have a large number of parameteres? say 64+ (I'm sure you have a better number in mind here)

Actually no, I don't think I have a better number. For me, if a query has so many parameters, it deserves to have its parsing cached 😄

btw sqlalchemy absolutely uses parametes in the in, it does not embed them inside the query string, so such a dropoff would "solve" this case.

Yes, that's confirmed. The heavier use of the cache happens when there is a variable number of parameters, not when the parameters change, which is consistent with generating a query IN (%s, %s, ...) with varying placeholders number.

Regarding any(), in most case pg will convert the in to any for you (at least that's what it reports when explain ...)

This is totally within the realm of your project, so I don't want to suggest you what to do. However, you are right: IN is converted by postgres into = any(). Performing this conversion in SA could be useful because you can then pass less parameters, less parsing in the server, prepared statements kick in, etc. But I don't know if this type of manipulation fits well in the philosophy of your project.

[CaselIT]

Actually no, I don't think I have a better number. For me, if a query has so many parameters, it deserves to have its parsing cached 😄

yes, the right answer here is "it depends" so considering also my other consideration regarding batched queries I don't think this is something psycopg should do automatically. If anything is to be done, probably the best option is to allow an user to disable caching on a per-statement basis

[dvarrazzo]

Note: discussion in SQLAlchemy at sqlalchemy/sqlalchemy#10270

[dvarrazzo]

FYI, a fix for this issue was released in psycopg 3.1.11, where too large queries or queries with a large number of parameters are not cached.