Introspection TYPE_BY_OID running for long time

asyncpg version:.29.0
PostgreSQL version: 2.0.26
Do you use a PostgreSQL SaaS? If so, which? Can you reproduce
the issue with a local PostgreSQL install?: no
Python version: 3.8.12
Platform: macos | arm | m1
Do you use pgbouncer?: no
Did you install asyncpg with pip?: yes
If you built asyncpg locally, which version of Cython did you use?: na
Can the issue be reproduced under both asyncio and
uvloop?: haven't tried yet

Recently integrated Postgres db in of my services, it is a fastapi application hence I am leveraging asyncpg and sqlalchemy, however ever since I've done that, the db is executing the query
SELECT t.oid, t.typelem AS elemtype, t.typtype AS kindFROM pg_catalog.pg_type AS tWHERE t.oid = $1

upon inspection I found that it is executed through asyncpg while introspection, using the TYPE_BY_OID introspection type.

The RDS metrics looks like this

Need some help in understanding why it is happening, and how can we stop it.

In the service there is a global pg_session.
ENGINE
pg_engine = create_async_engine( os.environ.get('PG_CONN_STRING'), pool_size=5, max_overflow=7, connect_args={"server_settings": {"jit": "off"}} )
SESSION
pg_async_session = async_sessionmaker(pg_engine, expire_on_commit=False)
for each query we start the connection , execute the query using ORMs and commit.
async with pg_async_session() as session:

@elprans

@elprans please share your perspective on this problem.

@slice-ArpitSharma

@slice-ArpitSharma Any updates on this? We're pretty much having the same problem, although we're using SQLAlchemy in addition.

@zagortenej024

@zagortenej024 no updates, i am also using sqlalchemy

@slice-ArpitSharma

@slice-ArpitSharma Any updates on this? Do you use NullPool in SQLAlchemy?

I'm happy to open a separate issue, but I'm seeing inconsistent performance from the same type introspection query. My setup requires pool recycling because passwords roll every 15 minutes:

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: Any, conn_rec: Any, cargs: list, cparams: dict) -> None:
    cparams["password"] = boto3.client("rds").generate_db_auth_token(config.POSTGRES_HOST, config.POSTGRES_PORT, config.POSTGRES_USER)

I'm curious if it's possible to save off the results of the first type introspection query in future connections. We only expect the schema to change during startup when alembic migrations run, so by the time the service is ready to serve connections from the pool the schema is locked in for the duration of the service's runtime.

I didn't quite grok what was suggested by #530 (comment), but something where I could pre-register my database types would be awesome so I didn't have to run this query repeatedly.

Here's a sample production timing when creating a new connection. It's not slow but it's definitely not fast, and in this case is nearly half the total runtime for the request.

In my case, the types it's looking at are JSON and JSONB, which I'm honestly flabbergasted required any special casing.

bumping this -- am experiencing introspection queries upwards of 1.5-2min sometimes. typically, its only 2-3s. which is still very bad.

Found the solution: use psycopg3

Avoid performing type introspection on known types

Avoid performing type introspection on known types (MagicStack#1243)

@slice-ArpitSharma

@slice-ArpitSharma Hello, I bumped into the same problem where that TYPE_BY_OID query is running constantly at my db and using resource. I found out that asyncpg or sqlalchemy that's using asyncpg only runs that query at the first time per connection. The reason for the constant use of TYPE_BY_OID query is because of the sqlalchemy max_overflow setting. In the sqlalchemy document it says

max_overflow – The maximum overflow size of the pool. When the number of checked-out connections reaches the size set in pool_size, additional connections will be returned up to this limit. When those additional connections are returned to the pool, they are disconnected and discarded. It follows then that the total number of simultaneous connections the pool will allow is pool_size + max_overflow, and the total number of “sleeping” connections the pool will allow is pool_size. max_overflow can be set to -1 to indicate no overflow limit; no limit will be placed on the total number of concurrent connections. Defaults to 10.

The overflow connections are always discarded after use. That's why in heavy load situations overflow connections are created -> runs the TYPE_BY_OID query upon running the first query -> the session completes -> the connection is discarded -> newly created overflow connection runs the TYPE_BY_OID query and on and on.

I tested out that setting max_overflow: 0 fixes this problem. I hope this helps you out.

sorry if I shouldn't comment to closed issues.

Seeing that this has been fixed in main, could you pls cut a new release?

Navigation Menu

Repository navigation

Introspection TYPE_BY_OID running for long time #1138

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Introspection TYPE_BY_OID running for long time #1138

Description

Activity

slice-ArpitSharma commented on Apr 4, 2024

zagortenej024 commented on Apr 24, 2024

slice-ArpitSharma commented on Apr 26, 2024

amaksymov commented on Sep 25, 2024

swanysimon commented on Nov 1, 2024

nathan-gage commented on Nov 12, 2024

suhjohn commented on Mar 14, 2025

lhj-paytalab commented on Jun 22, 2025

gregtesseract commented on Oct 2, 2025

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions