Repository navigation
Why is asyncpg doing type introspection on json types? #1206
Description
- asyncpg version:
0.30.0 - PostgreSQL version:
15.3 - Do you use a PostgreSQL SaaS? If so, which? Can you reproduce
the issue with a local PostgreSQL install?: RDS, and yes - Python version:
3.12.6 - Platform: MacOS and Linux
- Do you use pgbouncer?: No
- Did you install asyncpg with pip?: No,
poetry - If you built asyncpg locally, which version of Cython did you use?: n/a
- Can the issue be reproduced under both asyncio and uvloop?: Have not tried. Happy to if you think it would be beneficial
Spinning out of #1138 (comment) because it feels like a different discussion.
I'm running a FastAPI service that connects to AWS RDS, and needs to refresh credentials every 15 minutes. Normally, the type introspection queries don't take up much time because they run once per connection, but I have a lot of churn in my connection pool so run them a decent number of times. Recently I'm seen more traffic and thus more connections being created, and with more connections, the more often we're likely to see slow queries on things that are normally fast.
At a very high level, my service is set to connect to the database with:
engine = create_async_engine(
postgres_url(use_asyncpg=True),
pool_size=10,
max_overflow=25,
pool_recycle=600, # IAM credentials expire after 15 mins
pool_pre_ping=True,
)
@event.listens_for(engine.sync_engine, "do_connect")
def provide_token(dialect, conn_rec, cargs, cparams) -> None:
cparams["password"] = boto3.client("rds").generate_db_auth_token(
config.POSTGRES_HOST, config.POSTGRES_PORT, config.POSTGRES_USER,
)Even abnormally slow type introspection queries aren't horrible but they are noticeable, as in the example below these 2 queries took more than 50% of the service's total response time.
Debugging locally a little with command: ["postgres", "-c", "log_statement=all"] in my docker-compose.yml, I can see what type asyncpg needs to examine:
2024-11-01 20:52:52.239 UTC [491] LOG: execute __asyncpg_stmt_1__: SELECT
t.oid,
t.typelem AS elemtype,
t.typtype AS kind
FROM
pg_catalog.pg_type AS t
WHERE
t.oid = $1
2024-11-01 20:52:52.239 UTC [491] DETAIL: parameters: $1 = '114'
2024-11-01 20:52:52.240 UTC [491] LOG: execute __asyncpg_stmt_2__: SELECT
t.oid,
t.typelem AS elemtype,
t.typtype AS kind
FROM
pg_catalog.pg_type AS t
WHERE
t.oid = $1
2024-11-01 20:52:52.240 UTC [491] DETAIL: parameters: $1 = '3802'
These correspond to the JSON and JSONB types, respectively, not even custom types.
The actual question: how can I pre-register the JSON and JSONB types in each connection so I don't have to keep running the introspection query? I've tried the json_{de,}serializer argument to the SQLAlchemy engine, as well as trying to hook into SQLAlchemy events to intercept connection creation and set the codecs.
Activity
nathan-gage commentedon Nov 12, 2024
bumping this -- we are experiencing this issue as well. the strange part is the latency is ok for sometime, before completely shitting the bed:
swanysimon commentedon Jan 3, 2025
Hey team, would appreciate any thoughts on how this could be accomplished as we're seeing this problem scale with the number of connections. I took a little bit of a stab in the codebase here to see if I could get something working but believe I'm firmly out of my depth.
swanysimon commentedon Feb 25, 2025
Some more looking: it seems that JSON and JSONB should already be in the type map. So the question remains: why is there type introspection happening? Is it because my Python type for the column is
Mapped[dict[str, Any]]instead ofstr?suhjohn commentedon Mar 14, 2025
#1138
#1242
#1082
Despite this, it's not fixed
elprans commentedon Mar 14, 2025
This seems to me like an RDS/Aurora bug where access to the system catalog is sometimes this slow. I mean this:
is a trivial query looking thing up by an indexed key. It should run in well under a millisecond.
That said, it should be possible to avoid running it when setting codecs on builtin types.
Avoid performing type introspection on known types
Avoid performing type introspection on known types
Avoid performing type introspection on known types
Avoid performing type introspection on known types (MagicStack#1243)
antonmyronyuk commentedon Mar 17, 2025
@elprans Thank you! Looking forward to the next release!
swanysimon commentedon Mar 19, 2025
My understanding is that Aurora is a server less architecture, and so can be victim to cold starts. This is likely what we're seeing, but exactly as you say:
I can't say I fully understand the change but I'm excited to see these queries disappear from my dashboard. Thank you!
For the future, if I did have a custom type in the database, is there a way I could I preregister it with asyncpg on connection creation and get the same functionality I get from SQLAlchemy's pools?
elprans commentedon Mar 19, 2025
Yes, this is what the
initcallback argument oncreate_poolis for (the documentation even states explicitly that it's where you should register custom codecs in).