[2026-04-08] "Proof" that `session` in SQLAlchemy does not consume DB resources until used

README.md

"Proof" that session in SQLAlchemy does not consume DB resources until used

Before session1 exists

acme-db> SELECT
     pid,
     usename,
     application_name,
     state,
     wait_event,
     backend_type,
     backend_start
 FROM
     pg_catalog.pg_stat_activity
 ORDER BY pid ASC;
+-------+---------+------------------+--------+---------------------+------------------------------+-------------------------------+
| pid   | usename | application_name | state  | wait_event          | backend_type                 | backend_start                 |
|-------+---------+------------------+--------+---------------------+------------------------------+-------------------------------|
| 855   | <null>  |                  | <null> | CheckpointerMain    | checkpointer                 | 2026-03-31 13:14:27.263825-05 |
| 856   | <null>  |                  | <null> | BgwriterHibernate   | background writer            | 2026-03-31 13:14:27.265154-05 |
| 860   | <null>  |                  | <null> | WalWriterMain       | walwriter                    | 2026-03-31 13:14:27.358589-05 |
| 861   | <null>  |                  | <null> | AutovacuumMain      | autovacuum launcher          | 2026-03-31 13:14:27.36027-05  |
| 862   | dhermes |                  | <null> | LogicalLauncherMain | logical replication launcher | 2026-03-31 13:14:27.362074-05 |
| 87402 | dhermes | pgcli            | active | <null>              | client backend               | 2026-04-08 10:14:56.655456-05 |
+-------+---------+------------------+--------+---------------------+------------------------------+-------------------------------+
SELECT 6
Time: 0.008s

acme-db> SELECT
     pid,
     usename,
     application_name,
     state,
     wait_event,
     backend_type,
     backend_start
 FROM
     pg_catalog.pg_stat_activity
 WHERE
     pid NOT IN (855, 856, 860, 861, 862, 87402)
 ORDER BY pid ASC;
+-----+---------+------------------+-------+------------+--------------+---------------+
| pid | usename | application_name | state | wait_event | backend_type | backend_start |
|-----+---------+------------------+-------+------------+--------------+---------------|
+-----+---------+------------------+-------+------------+--------------+---------------+
SELECT 0
Time: 0.006s

Create session1

In [1]: import sqlalchemy

In [2]: import sqlalchemy.engine

In [3]: import sqlalchemy.orm.session

In [4]: engine = sqlalchemy.engine.create_engine("postgresql:///acme-db")

In [5]: session_type = sqlalchemy.orm.session.sessionmaker(bind=engine)

In [6]: session1 = session_type()

In [7]: result1 = session1.execute("SELECT PG_BACKEND_PID()")

In [8]: result1.fetchall()
Out[8]: [(89721,)]

After session1 made a query

acme-db> SELECT
     pid,
     usename,
     application_name,
     state,
     wait_event,
     backend_type,
     backend_start
 FROM
     pg_catalog.pg_stat_activity
 WHERE
     pid NOT IN (855, 856, 860, 861, 862, 87402)
 ORDER BY pid ASC;
+-------+---------+------------------+---------------------+------------+----------------+-------------------------------+
| pid   | usename | application_name | state               | wait_event | backend_type   | backend_start                 |
|-------+---------+------------------+---------------------+------------+----------------+-------------------------------|
| 89721 | dhermes |                  | idle in transaction | ClientRead | client backend | 2026-04-08 10:16:18.773638-05 |
+-------+---------+------------------+---------------------+------------+----------------+-------------------------------+
SELECT 1
Time: 0.006s

Make session2, no query

In [9]: session2 = session_type()

acme-db> SELECT
     pid,
     usename,
     application_name,
     state,
     wait_event,
     backend_type,
     backend_start
 FROM
     pg_catalog.pg_stat_activity
 WHERE
     pid NOT IN (855, 856, 860, 861, 862, 87402)
 ORDER BY pid ASC;
+-------+---------+------------------+---------------------+------------+----------------+-------------------------------+
| pid   | usename | application_name | state               | wait_event | backend_type   | backend_start                 |
|-------+---------+------------------+---------------------+------------+----------------+-------------------------------|
| 89721 | dhermes |                  | idle in transaction | ClientRead | client backend | 2026-04-08 10:16:18.773638-05 |
+-------+---------+------------------+---------------------+------------+----------------+-------------------------------+
SELECT 1
Time: 0.007s

Use session2

In [10]: result2 = session2.execute("SELECT PG_BACKEND_PID()")

In [11]: result2.fetchall()
Out[11]: [(90879,)]

acme-db> SELECT
     pid,
     usename,
     application_name,
     state,
     wait_event,
     backend_type,
     backend_start
 FROM
     pg_catalog.pg_stat_activity
 WHERE
     pid NOT IN (855, 856, 860, 861, 862, 87402)
 ORDER BY pid ASC;
+-------+---------+------------------+---------------------+------------+----------------+-------------------------------+
| pid   | usename | application_name | state               | wait_event | backend_type   | backend_start                 |
|-------+---------+------------------+---------------------+------------+----------------+-------------------------------|
| 89721 | dhermes |                  | idle in transaction | ClientRead | client backend | 2026-04-08 10:16:18.773638-05 |
| 90879 | dhermes |                  | idle in transaction | ClientRead | client backend | 2026-04-08 10:17:03.404721-05 |
+-------+---------+------------------+---------------------+------------+----------------+-------------------------------+
SELECT 2
Time: 0.008s

Make many sessions

In [12]: many_sessions = [session_type() for _ in range(40)]

acme-db> SELECT
     pid,
     usename,
     application_name,
     state,
     wait_event,
     backend_type,
     backend_start
 FROM
     pg_catalog.pg_stat_activity
 WHERE
     pid NOT IN (855, 856, 860, 861, 862, 87402)
 ORDER BY pid ASC;
+-------+---------+------------------+---------------------+------------+----------------+-------------------------------+
| pid   | usename | application_name | state               | wait_event | backend_type   | backend_start                 |
|-------+---------+------------------+---------------------+------------+----------------+-------------------------------|
| 89721 | dhermes |                  | idle in transaction | ClientRead | client backend | 2026-04-08 10:16:18.773638-05 |
| 90879 | dhermes |                  | idle in transaction | ClientRead | client backend | 2026-04-08 10:17:03.404721-05 |
+-------+---------+------------------+---------------------+------------+----------------+-------------------------------+
SELECT 2
Time: 0.008s

Runtime cost

In [13]: %timeit session_type()
3.49 μs ± 48.4 ns per loop (mean ± std. dev. of 7 runs, 100,000 loops each)

700,000 loops but only session1 and session2 PIDs are in here

acme-db> SELECT
     pid,
     usename,
     application_name,
     state,
     wait_event,
     backend_type,
     backend_start
 FROM
     pg_catalog.pg_stat_activity
 WHERE
     pid NOT IN (855, 856, 860, 861, 862, 87402)
 ORDER BY pid ASC;
+-------+---------+------------------+---------------------+------------+----------------+-------------------------------+
| pid   | usename | application_name | state               | wait_event | backend_type   | backend_start                 |
|-------+---------+------------------+---------------------+------------+----------------+-------------------------------|
| 89721 | dhermes |                  | idle in transaction | ClientRead | client backend | 2026-04-08 10:16:18.773638-05 |
| 90879 | dhermes |                  | idle in transaction | ClientRead | client backend | 2026-04-08 10:17:03.404721-05 |
+-------+---------+------------------+---------------------+------------+----------------+-------------------------------+
SELECT 2
Time: 0.005s