Google Colab VM Lifecycle and SQLAlchemy Connection Pool Interaction

Answer to the Core Questions

1. Does a Colab factory reset terminate open TCP sockets immediately?

Yes. When Colab performs a factory reset, the Python kernel process and its underlying OS process are killed. The kernel closes all file descriptors, including TCP sockets, sending an RST to the remote peer. The sockets do not linger until a TCP keep‑alive timeout expires.

2. Should the pool’s idle_timeout be tuned to the VM lifetime?

Because sockets are closed instantly on reset, any connection that is still checked out at that moment becomes unusable. If the pool’s idle_timeout is set longer than the expected VM inactivity period (≈90 min), those stale connections will stay in the pool until the timeout fires, potentially exhausting the pool. A safer approach is to set idle_timeout to a value comfortably below the inactivity threshold (e.g., 60 min) and, more importantly, to return connections to the pool before the next cell runs.

3. Is there a reliable pre‑shutdown signal in Colab?

No documented hook or event signals an imminent VM recycle. The only detectable event is the runtime disconnect that occurs after the reset, which is too late for graceful cleanup.

Confirmed Facts (From Observed Behavior)

• Factory reset kills the kernel process → immediate socket RST.
• SQLAlchemy’s QueuePool does not auto‑detect external socket closure; the connection raises on next use but remains in the pool until closed or timed out.
• There is no pre‑shutdown hook in Colab notebooks.

Likely Explanation

Colab’s VM lifecycle is managed by Google’s infrastructure. When a reset is triggered, the entire runtime environment is torn down. The kernel process is terminated with a SIGKILL, which forces the OS to close all open file descriptors. The remote database server therefore receives a TCP RST, not a graceful FIN. SQLAlchemy’s pool, unaware of this external event, keeps the connection object in its internal list until the user explicitly returns it or the connection times out.

Practical Steps for a Notebook

1. Configure the pool with a conservative idle_timeout:

   engine = create_engine(
           "postgresql://user:pass@host/db",
           poolclass=QueuePool,
           pool_size=5,
           max_overflow=10,
           pool_timeout=30,
           pool_recycle=60 * 60  # 60 minutes
       )
       

2. Always close the session in a try/finally or with block so it returns to the pool before the next cell:

   from contextlib import contextmanager
   
   @contextmanager
   def get_session():
       session = Session(bind=engine)
       try:
           yield session
       finally:
           session.close()
   
   # Usage in a cell
   with get_session() as s:
       result = s.execute("SELECT 1")
       print(result.scalar())
       

3. Optionally, dispose the engine at the very end of the notebook:

   engine.dispose()
       

   This clears the pool immediately, useful after a manual restart.
4. Monitor pool status (optional):

   print(engine.pool.status())
       

Why Per‑Cell Cleanup Is Preferable

Because the reset can happen at any time, relying solely on idle_timeout risks holding onto dead connections for up to that timeout. Explicitly closing the session guarantees the connection is returned to the pool immediately, preventing accumulation. If you have a long‑running notebook that keeps a session open across many cells, consider wrapping the session acquisition in a helper that always closes the session in a finally block.

Missing Diagnostic Detail

If you are using a database that implements its own connection‑validation (e.g., pool_pre_ping=True in SQLAlchemy), let us know, as that can affect how quickly a stale connection is detected. Also, if you are using a different pool class (e.g., NullPool or SingletonThreadPool), the recommendations may differ.