How to set DB pool size?

[gllona]

Hi! I am running alembic on a PostgreSQL DB provided by the Supabase free tier, which has a limit of 15 connections and sometimes they lower it. Today I got this error:

alembic: failed to run migrations
...
sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) connection to server at "aws-0-eu-west-1.pooler.supabase.com" (108.128.216.176), port 5432 failed: FATAL: MaxClientsInSessionMode: max clients reached - in Session mode max clients are limited to pool_size`
...
File "/home/tools/migrations/env.py", line 98, in <module>
run_migrations_online()

Is there a way to set the DB pool size in the alembic configuration? I didn't found anything related in the alembic documentation.

Thanks!

[zzzeek]

the pool size is set using pool_size on create_engine , which has to be somewhere in your app in order for you to connect

[gllona]

Thank you, that solved my issue.

In run_migrations_online, instead of:

    # Create the connectable
    connectable = engine_from_config(
        config_section,
        prefix="sqlalchemy.",
        poolclass=pool.QueuePool,  # Use QueuePool for connection pooling
    )

I switched to:

    connectable = create_engine(
        build_pg_url(),
        # echo=True,
        pool_recycle=300,  # Recycle connections every 300 seconds
        pool_timeout=30,  # Timeout for acquiring a connection
        pool_pre_ping=True,  # Check connection health before using it
        pool_size=1,  # Set a lower number of connections in the pool
        max_overflow=0,  # Allow a limited number of additional connections
    )

That solved my issue with the PostgreSQL Supabase DB free tier