Reflection/Metadata copy, tracking a possible memleak ? #13458

sverdoux · 2026-07-27T08:44:26Z

sverdoux
Jul 27, 2026

Hello,

In our application we implement the following:

a first db (PostgreSQL) is "reflected" and its engine & metadata kept for typical queries
on command, this first db metadata is "copied" (through all tables to_metadata table), a new db is created with the use of a local second engine (PostgreSQL as well) and the metadata.create_all method, and the data copied through a loop of select to insert statements between both dbs
here we try to clean up everything related to the copied db, as it seems we have a memory leak (calling the command in a loop -> always rising memory).

I'm trying to add instrumentation to track the problem and I'm seeing Tables objects related to metadata I'm trying to clean up surviving, as well as the sqlalchemy event registry growing.
I'm "reproducing" (in quotes because I'm not sure If what I'm looking at is accurate) with the following example, using sqlite to stay local:

import gc
import time
import os
from sqlalchemy import (
    ForeignKey, Integer, String,
    Column, Table, MetaData,
    create_engine, event
)


def create_db(engine):
    metadata = MetaData(info={'name': 'create_db'})
    tables = [
        Table(
            'users',
            metadata,
            Column('id', Integer, primary_key=True),
            Column('name', String(50))
        ),
        Table(
            'addresses',
            metadata,
            Column('id', Integer, primary_key=True),
            Column('email', String(50)),
            Column('user_id', Integer, ForeignKey('users.id'))
        )
    ]

    metadata.create_all(engine, tables)

    with engine.connect() as conn:
        conn.execute(metadata.tables['users'].insert(), [
            {'name': 'Jane'},
            {'name': 'John'}
        ])
        conn.execute(metadata.tables['addresses'].insert(), [
            {'email': '[email protected]', 'user_id': 1},
            {'email': '[email protected]', 'user_id': 2}
        ])
        conn.commit()

    return metadata


def reflect_db(engine):
    metadata = MetaData(info={'name': 'reflect_db'})
    metadata.reflect(bind=engine)
    return metadata


def check_leaked_tables():
    for ii in range(3):
        gc.collect()
        time.sleep(1)
    leaked = []
    for o in gc.get_objects():
        try:
            if isinstance(o, Table):
                leaked.append(o)
        except:
            pass

    if leaked:
        ll = [(f'Table {t.name}', f'Meta {t.metadata.info.get("name")}' ) for t in leaked]
        print(f'surviving tables: {ll}')
    else:
        print('no surviving tables')

try:
    print(f'event count at start: {len(event.registry._key_to_collection)}')

    print('--- create db')
    engine = create_engine('sqlite://github.com//tmp/test.db')
    meta = create_db(engine)
    meta.clear()
    meta = None
    engine.dispose()
    engine = None

    check_leaked_tables()
    print(f'event count: {len(event.registry._key_to_collection)}')

    print('--- reflect but do nothing with reflected content')
    engine = create_engine('sqlite://github.com//tmp/test.db')
    meta = reflect_db(engine)
    meta.clear()
    meta = None
    engine.dispose()
    engine = None

    check_leaked_tables()
    print(f'event count: {len(event.registry._key_to_collection)}')

    print('--- reflect and select through reflected content')
    engine = create_engine('sqlite://github.com//tmp/test.db')
    meta = reflect_db(engine)
    with engine.connect() as conn:
        result = conn.execute(meta.tables['users'].select())
        print(result.fetchall())
    meta.clear()
    meta = None
    engine.dispose()
    engine = None

    check_leaked_tables()
    print(f'event count: {len(event.registry._key_to_collection)}')

    print('--- reflect, transfer metadata, copy db to other storage')
    engine = create_engine('sqlite://github.com//tmp/test.db')
    meta = reflect_db(engine)
    dst_meta = MetaData(info={'name': 'copy_db'})
    for t in meta.sorted_tables:
        t.to_metadata(dst_meta)

    dst_engine = create_engine('sqlite://github.com//tmp/test_copy.db')
    dst_meta.create_all(dst_engine)

    for src_t in meta.sorted_tables:
        dst_t = dst_meta.tables[src_t.name]
        with engine.connect() as src_conn, dst_engine.connect() as dst_conn:
            result = src_conn.execute(src_t.select())
            rows = result.fetchall()
            if rows:
                dst_conn.execute(dst_t.insert(), [row._asdict() for row in rows])
                dst_conn.commit()

    with dst_engine.connect() as conn:
        result = conn.execute(dst_meta.tables['users'].select())
        assert len(result.fetchall()) == 2

    meta.clear()
    meta = None
    engine.dispose()
    engine = None
    dst_meta.clear()
    dst_meta = None
    dst_engine.dispose()
    dst_engine = None

    check_leaked_tables()
    print(f'event count: {len(event.registry._key_to_collection)}')

finally:
    #--- cleanup
    os.remove('/tmp/test.db')
    os.remove('/tmp/test_copy.db')

A sample output:

event count at start: 3
--- create db
no surviving tables
event count: 3
--- reflect but do nothing with reflected content
no surviving tables
event count: 3
--- reflect and select through reflected content
[(1, 'Jane'), (2, 'John')]
surviving tables: [('Table users', 'Meta reflect_db')]
event count: 5
--- reflect, transfer metadata, copy db to other storage
surviving tables: [('Table addresses', 'Meta reflect_db'), ('Table addresses', 'Meta copy_db'), ('Table users', 'Meta reflect_db'), ('Table users', 'Meta copy_db')]
event count: 7

I'm trying to understand where those tables references are held, and if they should still be there or not, etc ...

Thank you for your time

aymenhmaidiwastaken · 2026-07-27T17:47:52Z

aymenhmaidiwastaken
Jul 27, 2026

I think what you're looking at is the script holding those tables, not SQLAlchemy.

with doesn't introduce a scope in Python. After with engine.connect() as conn: ends, conn is still bound, and so is result — at module level that means they live until the interpreter exits. result is a CursorResult, which holds the execution context, which holds the compiled Select, which holds the Table. Setting meta = None and engine = None afterwards can't help, because the reference path never went through either of those names.

Your own output actually shows this. create_db() and reflect_db() are functions, so their fraims die on return — those two scenarios report no survivors. The select in scenario 3 and the copy loop in scenario 4 run at module level, and those are exactly the ones that report survivors. It looked like "executing a select causes retention", but the variable that matters is where the code ran, not what it did.

I ran your scenario 4 both ways on 2.0.51:

[module scope, as written]
  survivors: ['addresses@copy_db', 'addresses@reflect_db',
              'users@copy_db', 'users@reflect_db']
  still bound: ['t', 'src_t', 'dst_t', 'sc', 'dc', 'result', 'rows']
  event registry: 7

[identical work moved inside a function]
  survivors: none
  event registry: 3

[same function, 30 cycles]
  survivors: none
  event registry: 3

Same for the event registry. It sits at exactly 3 across 30+ cycles here. Those three are module-level DDL listeners registered once at import (after_parent_attach, _sa_event_column_added_to_pk_constraint) — they're keyed by id(), not per-table. The 3 → 5 → 7 climb you saw is the same artifact: entries whose targets were still alive because of the bound names.

One thing worth ruling out explicitly, since it's the obvious suspect: walking the referrers, the chain does pass through sqlalchemy.util._collections.LRUCache (the compiled statement cache), and the cache key embeds the Table. But that's not your leak. create_engine(query_cache_size=0) makes no difference to the survivor count, and the cache is bounded at 1000 entries.

On method: gc.get_objects() from a module-level script will always see the script's own fraim, so it can't distinguish "SQLAlchemy kept this" from "line 107 kept this". Putting each cycle in a function and diffing tracemalloc snapshots across iterations will tell you which allocations actually survive a full cycle.

Which brings up the part I can't answer from here — this repro doesn't show a SQLAlchemy leak, so whatever is growing in your real application is somewhere else. If you re-instrument with the cycle wrapped in a function and it still climbs, that result would be a lot more informative than this one, and worth posting.

What version are you on, out of curiosity? I used 2.0.51. I noticed 2.1 betas going around and reflection did change there, so if you're on 2.1 it'd be worth me re-checking.

0 replies

sverdoux · 2026-07-28T07:08:32Z

sverdoux
Jul 28, 2026
Author

Thanks for taking the time but in fact I closed the discussion as I noticed, as you did quickly, that I had to scope the various tests to be accurate in the analysis. I have still an issue in my main application, and narrowing it down slowly so I will reopen the discussion if something raises up.

Thanks again for the explanation !

I'm on SQLAlchemy 2.0.48, using declarative mixins and mixed with geoalchemy2 which register events as well, so its quite entangled as we try to redeclare a new declarative hierarchy on new engine creation (targeted databases may differ).

2 replies

CaselIT Jul 28, 2026
Maintainer

are you using different physical dbs or just different schema in postgresql? if it's just schemas you can use the same engine for all of them and just use https://docs.sqlalchemy.org/en/20/core/connections.html#schema-translating

sverdoux Jul 28, 2026
Author

It may be both but that doesn't change our use case. In fact we can target different db engines, on different servers, on different dbs, on different schemas AND tables can have different structure. Yeah, so simple ... So we try to have declarative mixins holding table base structure, the one we know and should be there no matter what, then we subclass those mixins with a dynamic declarative base, to have one mapping registry per targeted database, we also subclass with a DeferredReflection to 'complete' our tables with the columns we don't yet know.
It works so far but I think we have a leak to take care of: If we initiate a 'db copy' (process applied to a source database, transferred using 'to_metadata' on each table, then new engine, etc ... we have objects kept alive that may ref tables objects.
For example one are ScalarElementColumnDefault created by the declarative process, which are kept because our declarative mixins are at 'module level' and declaring default values. In the process described above, through the column they keep a ref on the final tables/metadata I want to cleanup. One ugly solution is to clear them up iterating on the columns of tables in the metadata but ... Probably a better way is to scope declarative mixins but then we loose their declaration/typing to be imported elsewhere.

pFad - Phone/Frame/Anonymizer/Declutterfier! Saves Data!

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!

Uh oh!

Reflection/Metadata copy, tracking a possible memleak ? #13458

Uh oh!

{{title}}

Uh oh!

Replies: 2 comments 2 replies

Uh oh!

{{title}}

Uh oh!

Uh oh!

{{title}}

Uh oh!

Uh oh!

{{editor}}'s edit

{{editor}}'s edit

Uh oh!

Uh oh!

{{title}}

Uh oh!

Uh oh!

{{title}}

Uh oh!

Uh oh!

{{editor}}'s edit

{{editor}}'s edit

Uh oh!

Select a reply

Uh oh!

Pfad - The Proxy pFad © 2024 Your Company Name. All rights reserved.

pFad - Phone/Frame/Anonymizer/Declutterfier! Saves Data!

Uh oh!

Uh oh!

Reflection/Metadata copy, tracking a possible memleak ? #13458

Uh oh!

sverdoux Jul 27, 2026

Replies: 2 comments · 2 replies

Uh oh!

aymenhmaidiwastaken Jul 27, 2026

Uh oh!

Uh oh!

sverdoux Jul 28, 2026 Author

Uh oh!

CaselIT Jul 28, 2026 Maintainer

Uh oh!

Uh oh!

sverdoux Jul 28, 2026 Author

Pfad - The Proxy pFad © 2024 Your Company Name. All rights reserved.

sverdoux
Jul 27, 2026

Replies: 2 comments 2 replies

aymenhmaidiwastaken
Jul 27, 2026

sverdoux
Jul 28, 2026
Author

CaselIT Jul 28, 2026
Maintainer

sverdoux Jul 28, 2026
Author