Insert performance issue of ORM models with multiple Enums and async engine #10356
-
Describe the bugAdding a second Enum column to an ORM model leads to unpredictable performance regression during the insert commit when running with an async engine and null pool size. This issue is not reproduced with a synchronous engine. SQLAlchemy Version in Use2.0.20 DBAPI (i.e. the database driver)asyncpg 0.28.0 Database Vendor and Major VersionPostgreSQL 15 Python Version3.11.4 Operating systemOSX To Reproduceimport asyncio
import enum
import time
from sqlalchemy import JSON, NullPool
from sqlalchemy.ext.asyncio import AsyncSession, create_async_engine
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column
class EnumOne(enum.Enum):
VALUE = "value"
class EnumTwo(enum.Enum):
VALUE = "value"
class EnumThree(enum.Enum):
VALUE = "value"
class Base(DeclarativeBase):
...
class ModelWithOneEnum(Base):
__tablename__ = "model_with_one_enum"
id: Mapped[int] = mapped_column(primary_key=True)
enum_one: Mapped[EnumOne]
# To demonstrate that it's not about additional column, can be removed
third_column: Mapped[dict] = mapped_column(JSON)
class ModelWithTwoEnums(Base):
__tablename__ = "model_with_two_enums"
id: Mapped[int] = mapped_column(primary_key=True)
enum_one: Mapped[EnumOne]
enum_two: Mapped[EnumTwo]
class ModelWithThreeEnums(Base):
__tablename__ = "model_with_three_enums"
id: Mapped[int] = mapped_column(primary_key=True)
enum_one: Mapped[EnumOne]
enum_two: Mapped[EnumTwo]
enum_three: Mapped[EnumThree]
async def main() -> None:
connection_string = "postgresql+asyncpg://postgres:postgres@localhost:5432/postgres"
setup_engine = create_async_engine(connection_string)
null_pool_engine = create_async_engine(connection_string, poolclass=NullPool)
with_pool_size_engine = create_async_engine(connection_string, pool_size=10)
async with setup_engine.begin() as conn:
await conn.run_sync(Base.metadata.drop_all)
await conn.run_sync(Base.metadata.create_all)
async with AsyncSession(null_pool_engine) as session:
session.add(ModelWithOneEnum(enum_one=EnumOne.VALUE, third_column=[1] * 1000))
start_time = time.time()
await session.commit()
print(f"NullPool + ModelWithOneEnum: {time.time() - start_time}")
async with AsyncSession(null_pool_engine) as session:
session.add(ModelWithTwoEnums(enum_one=EnumOne.VALUE, enum_two=EnumTwo.VALUE))
start_time = time.time()
await session.commit()
print(f"NullPool + ModelWithTwoEnums: {time.time() - start_time}")
async with AsyncSession(null_pool_engine) as session:
session.add(
ModelWithThreeEnums(
enum_one=EnumOne.VALUE,
enum_two=EnumTwo.VALUE,
enum_three=EnumThree.VALUE,
)
)
start_time = time.time()
await session.commit()
print(f"NullPool + ModelWithThreeEnums: {time.time() - start_time}")
async with AsyncSession(with_pool_size_engine) as session:
session.add(ModelWithOneEnum(enum_one=EnumOne.VALUE, third_column=[1] * 1000))
start_time = time.time()
await session.commit()
print(f"WithPool + ModelWithOneEnum: {time.time() - start_time}")
async with AsyncSession(with_pool_size_engine) as session:
session.add(ModelWithTwoEnums(enum_one=EnumOne.VALUE, enum_two=EnumTwo.VALUE))
start_time = time.time()
await session.commit()
print(f"WithPool + ModelWithTwoEnums: {time.time() - start_time}")
async with AsyncSession(with_pool_size_engine) as session:
session.add(
ModelWithThreeEnums(
enum_one=EnumOne.VALUE,
enum_two=EnumTwo.VALUE,
enum_three=EnumThree.VALUE,
)
)
start_time = time.time()
await session.commit()
print(f"WithPool + ModelWithThreeEnums: {time.time() - start_time}")
if __name__ == "__main__":
asyncio.run(main())ErrorTimings on my machine: Additional context
Offtop: the reason we have to use the null pool is that we couldn't find another way to make |
Beta Was this translation helpful? Give feedback.
Replies: 3 comments 12 replies
-
|
the asyncpg driver is known to do some unusual additional queries to introspect ENUM datatypes. Might this be an asyncpg issue? SQLAlchemy doesn't do anything differently with the different drivers. |
Beta Was this translation helpful? Give feedback.
-
|
FTR with a localhost connection there is no difference: with a local network connection the nullpool has a slight degradation, so this points to the big introspection query that asyncpg does when it deals with types like ENUM: Here's the query asyncpg runs, note this is not us, this is in their source code: 2023-09-16 22:13:07.815 EDT [1716149] LOG: execute __asyncpg_stmt_32__: WITH RECURSIVE typeinfo_tree(
oid, ns, name, kind, basetype, elemtype, elemdelim,
range_subtype, attrtypoids, attrnames, depth)
AS (
SELECT
ti.oid, ti.ns, ti.name, ti.kind, ti.basetype,
ti.elemtype, ti.elemdelim, ti.range_subtype,
ti.attrtypoids, ti.attrnames, 0
FROM
(
SELECT
t.oid AS oid,
ns.nspname AS ns,
t.typname AS name,
t.typtype AS kind,
(CASE WHEN t.typtype = 'd' THEN
(WITH RECURSIVE typebases(oid, depth) AS (
SELECT
t2.typbasetype AS oid,
0 AS depth
FROM
pg_type t2
WHERE
t2.oid = t.oid
UNION ALL
SELECT
t2.typbasetype AS oid,
tb.depth + 1 AS depth
FROM
pg_type t2,
typebases tb
WHERE
tb.oid = t2.oid
AND t2.typbasetype != 0
) SELECT oid FROM typebases ORDER BY depth DESC LIMIT 1)
ELSE NULL
END) AS basetype,
t.typelem AS elemtype,
elem_t.typdelim AS elemdelim,
COALESCE(
range_t.rngsubtype,
multirange_t.rngsubtype) AS range_subtype,
(CASE WHEN t.typtype = 'c' THEN
(SELECT
array_agg(ia.atttypid ORDER BY ia.attnum)
FROM
pg_attribute ia
INNER JOIN pg_class c
ON (ia.attrelid = c.oid)
WHERE
ia.attnum > 0 AND NOT ia.attisdropped
AND c.reltype = t.oid)
ELSE NULL
END) AS attrtypoids,
(CASE WHEN t.typtype = 'c' THEN
(SELECT
array_agg(ia.attname::text ORDER BY ia.attnum)
FROM
pg_attribute ia
INNER JOIN pg_class c
ON (ia.attrelid = c.oid)
WHERE
ia.attnum > 0 AND NOT ia.attisdropped
AND c.reltype = t.oid)
ELSE NULL
END) AS attrnames
FROM
pg_catalog.pg_type AS t
INNER JOIN pg_catalog.pg_namespace ns ON (
ns.oid = t.typnamespace)
LEFT JOIN pg_type elem_t ON (
t.typlen = -1 AND
t.typelem != 0 AND
t.typelem = elem_t.oid
)
LEFT JOIN pg_range range_t ON (
t.oid = range_t.rngtypid
)
LEFT JOIN pg_range multirange_t ON (
t.oid = multirange_t.rngmultitypid
)
)
AS ti
WHERE
ti.oid = any($1::oid[])
UNION ALL
SELECT
ti.oid, ti.ns, ti.name, ti.kind, ti.basetype,
ti.elemtype, ti.elemdelim, ti.range_subtype,
ti.attrtypoids, ti.attrnames, tt.depth + 1
FROM
(
SELECT
t.oid AS oid,
ns.nspname AS ns,
t.typname AS name,
t.typtype AS kind,
(CASE WHEN t.typtype = 'd' THEN
(WITH RECURSIVE typebases(oid, depth) AS (
SELECT
t2.typbasetype AS oid,
0 AS depth
FROM
pg_type t2
WHERE
t2.oid = t.oid
UNION ALL
SELECT
t2.typbasetype AS oid,
tb.depth + 1 AS depth
FROM
pg_type t2,
typebases tb
WHERE
tb.oid = t2.oid
AND t2.typbasetype != 0
) SELECT oid FROM typebases ORDER BY depth DESC LIMIT 1)
ELSE NULL
END) AS basetype,
t.typelem AS elemtype,
elem_t.typdelim AS elemdelim,
COALESCE(
range_t.rngsubtype,
multirange_t.rngsubtype) AS range_subtype,
(CASE WHEN t.typtype = 'c' THEN
(SELECT
array_agg(ia.atttypid ORDER BY ia.attnum)
FROM
pg_attribute ia
INNER JOIN pg_class c
ON (ia.attrelid = c.oid)
WHERE
ia.attnum > 0 AND NOT ia.attisdropped
AND c.reltype = t.oid)
ELSE NULL
END) AS attrtypoids,
(CASE WHEN t.typtype = 'c' THEN
(SELECT
array_agg(ia.attname::text ORDER BY ia.attnum)
FROM
pg_attribute ia
INNER JOIN pg_class c
ON (ia.attrelid = c.oid)
WHERE
ia.attnum > 0 AND NOT ia.attisdropped
AND c.reltype = t.oid)
ELSE NULL
END) AS attrnames
FROM
pg_catalog.pg_type AS t
INNER JOIN pg_catalog.pg_namespace ns ON (
ns.oid = t.typnamespace)
LEFT JOIN pg_type elem_t ON (
t.typlen = -1 AND
t.typelem != 0 AND
t.typelem = elem_t.oid
)
LEFT JOIN pg_range range_t ON (
t.oid = range_t.rngtypid
)
LEFT JOIN pg_range multirange_t ON (
t.oid = multirange_t.rngmultitypid
)
)
ti,
typeinfo_tree tt
WHERE
(tt.elemtype IS NOT NULL AND ti.oid = tt.elemtype)
OR (tt.attrtypoids IS NOT NULL AND ti.oid = any(tt.attrtypoids))
OR (tt.range_subtype IS NOT NULL AND ti.oid = tt.range_subtype)
OR (tt.basetype IS NOT NULL AND ti.oid = tt.basetype)
)
SELECT DISTINCT
*,
basetype::regtype::text AS basetype_name,
elemtype::regtype::text AS elemtype_name,
range_subtype::regtype::text AS range_subtype_name
FROM
typeinfo_tree
ORDER BY
depth DESC
2023-09-16 22:13:07.815 EDT [1716149] DETAIL: parameters: $1 = '{2732368}'
that's each time you use these ENUM objects on a new connection. |
Beta Was this translation helpful? Give feedback.
-
|
it's here or thereabouts |
Beta Was this translation helpful? Give feedback.
-
|
changed the word "regression", since we use "regression" to mean "this used to work in a previous version of SQLAlchemy" and is obviously like a big red flag to us, so that does not apply here |
Beta Was this translation helpful? Give feedback.
-
Sure, that's what we do
That's a great suggestion! Switching to psycopg3 resolved the issue with Enum overhead and also sped up the tests in general
I am very curious why there is no spike when adding the second enum with null pool and asyncpg on your local machine. I tried in different environments and got the same result. But that's probably a whole different story :) @zzzeek thanks for taking the time to look into this! |
Beta Was this translation helpful? Give feedback.
-
|
IIRC I read that the query used by asycpg may cause issues when pg has jit active. Maybe the difference is that? (If they are indeed two different pg instances that were tested) |
Beta Was this translation helpful? Give feedback.
-
|
Hi, Like muke said, can you reproduce the same behaviour using psycopg3 used with asyncio? |
Beta Was this translation helpful? Give feedback.
-
|
Nope, it seems like an asyncpg issue, and everything runs smoothly with psycopg3. Marked as resolved |
Beta Was this translation helpful? Give feedback.
-
|
Interesting. May be worth reporting it to asyncpg? |
Beta Was this translation helpful? Give feedback.
-
|
I tihnk this is how asyncpg gets us the column descriptions. I doubt they can change it. I would assume psycopg3 gives us "unknown" or "str", not really sure. if asyncpg had an option to say, "hey we dont care about ENUM or custom types, just type those as "unknown" or "char", that woudl be great |
Beta Was this translation helpful? Give feedback.
-
|
don't know, that sql seems very complex. I'm not sure what they need to get but maybe there are simpler versions (at least from the db point of view) |
Beta Was this translation helpful? Give feedback.
-
|
I opened MagicStack/asyncpg#1078 for them. if they can provide an option to skip this type introspection we can integrate that. |
Beta Was this translation helpful? Give feedback.
-
|
they responded and they're going to keep the query but get the PostgreSQL JIT to not run on that query. |
Beta Was this translation helpful? Give feedback.
the asyncpg driver is known to do some unusual additional queries to introspect ENUM datatypes. Might this be an asyncpg issue? SQLAlchemy doesn't do anything differently with the different drivers.