Introspection TYPE_BY_OID running for long time

[slice-ArpitSharma]

• 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:

[slice-ArpitSharma]

@elprans please share your perspective on this problem.

[zagortenej024]

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

[slice-ArpitSharma]

@zagortenej024 no updates, i am also using sqlalchemy

[amaksymov]

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

[swanysimon]

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.

[nathan-gage]

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

[suhjohn]

Found the solution: use psycopg3