Python asyncpg first query huge 7 seconds delay!!

[tamis-laan]

Describe the problem

The first query in a connection to CRDB using asyncpg results in a 7 seconds delay. This is especially annoying when using connection pools.

To Reproduce

import os
import asyncpg
import logging

# Singleton
_db = None

async def get():
    # Get singleton
    global _db

    # Return database singleton if already connected
    if _db:
        return _db

    # Log to console
    logging.info("Connecting to database")
    _db = await asyncpg.connect(
        database = os.getenv("DB_NAME","defaultdb"),
        user     = os.getenv("DB_USER", "root"),
        password = os.getenv("DB_PASS", "root"),
        host     = os.getenv("DB_HOST", "localhost"),
        port     = os.getenv("DB_PORT", "26257")
    )

    # Return database singleton
    return _db

async def main():
    from datetime import datetime

    db = await get()

    start = datetime.now()
    await db.fetch("SELECT * FROM EVENTS")
    stop = datetime.now()
    print(stop-start)
    start = datetime.now()
    await db.fetch("SELECT * FROM EVENTS")
    stop = datetime.now()
    print(stop-start)

# Entry point
if __name__ == "__main__":
    import asyncio
    asyncio.run(main())

(.venv) > python src/database2.py
0:00:06.947757
0:00:00.000738

Environment:

• asyncpg version: 0.29.0
• CockroachDB version: v23.1.23
• Python version: 3.12.4
• Platform: Linux

Jira issue: CRDB-41199

[blathers-crl[bot]]

Hi @tamis-laan, please add branch-* labels to identify which branch(es) this C-bug affects.

_{:owl: Hoot! I am a Blathers, a bot for CockroachDB. My owner is dev-inf.}

[blathers-crl[bot]]

Hello, I am Blathers. I am here to help you get the issue triaged.

Hoot - a bug! Though bugs are the bane of my existence, rest assured the wretched thing will get the best of care here.

I have CC'd a few people who may be able to assist you:

• @cockroachdb/sql-queries (found keywords: import)

If we have not gotten back to your issue within a few business days, you can try the following:

• Join our community slack channel and ask on #cockroachdb.
• Try find someone from here if you know they worked closely on the area and CC them.

_{:owl: Hoot! I am a Blathers, a bot for CockroachDB. My owner is dev-inf.}

[rafiss]

Thanks for the report. This might be related to the introspection queries that asyncpg does on connection init. They seem to be pretty expensive: https://github.com/MagicStack/asyncpg/blob/85d7eed40637e7cad73a44ed2439ffeb2a8dc1c2/asyncpg/introspection.py#L14-L88

[tamis-laan]

@rafiss Yes and on the frontend which uses a connection pool it works out to multiple 7 second delays for loading pages. Can I revert to a version of cockroachdb without this delay, or is this purely a asyncpg issue?