Neon developer on retainer: pgvector inserts disappear on connection close — autocommit=False default — uncommitted transaction rolled back by scale-to-zero — 3 weeks — Neon serverless PostgreSQL developer on monthly retainer

Neon serverless PostgreSQL • psycopg2 2.9.9 • Python 3.11 • pgvector 0.7.4 • 500 document embeddings inserted per batch • psycopg2 autocommit=False (default) • no conn.commit() call • Neon scale-to-zero suspends compute after 5-min idle • all connections terminated • uncommitted transaction rolled back • all vectors disappear • integration test passes (same-session reads) • 3 weeks • fix: conn.commit() + canary-row startup check • 2.0h retainer engagement

Southfield App Backend Ltd. built a document search feature in August 2026 using pgvector on Neon serverless PostgreSQL. The indexing pipeline inserted 500 document embeddings per batch and returned a success response. The search endpoint consistently returned zero results after any period of inactivity — but worked correctly immediately after inserting a batch within the same application session. The developer spent three weeks checking DELETE triggers, foreign key cascades, row-level security policies, and Neon branch configurations. Integration tests passed every time. They engaged a Neon retainer developer.

The indexing pipeline at the time of the engagement:

# vector_store.py
# Southfield App Backend Ltd.
# Neon serverless PostgreSQL, psycopg2 2.9.9, Python 3.11, pgvector 0.7.4

import psycopg2
import psycopg2.extras

# Neon connection string from the Neon console
NEON_DSN = "postgresql://user:pass@ep-quiet-fog-12345.us-east-2.aws.neon.tech/neondb?sslmode=require"

def get_connection():
    conn = psycopg2.connect(NEON_DSN)
    # psycopg2 default: autocommit=False
    # Every execute() implicitly opens a transaction
    # The transaction is NOT committed until conn.commit() is called
    # If the connection closes without commit(), the transaction is rolled back
    return conn

def insert_embeddings(documents: list[dict]) -> int:
    conn = get_connection()
    psycopg2.extras.register_vector(conn)
    with conn.cursor() as cur:
        records = [
            (doc["id"], doc["title"], doc["content"], doc["embedding"])
            for doc in documents
        ]
        psycopg2.extras.execute_values(
            cur,
            "INSERT INTO documents (id, title, content, embedding) VALUES %s",
            records,
            template="(%s, %s, %s, %s::vector)",
        )
        # execute_values() returns with no error
        # The INSERT is in an open transaction — visible within this connection
        # NOT yet committed to disk — NOT visible to other connections
    conn.close()
    # ← conn.close() with an open uncommitted transaction → ROLLBACK
    # ← All inserted rows are discarded
    # ← No error raised; close() succeeds silently
    # ← BUG: conn.commit() never called                    ← BUG
    return len(documents)

def search_documents(query_embedding: list[float], top_k: int = 10):
    conn = get_connection()
    psycopg2.extras.register_vector(conn)
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT id, title, content,
                   1 - (embedding <=> %s::vector) AS similarity
            FROM documents
            ORDER BY embedding <=> %s::vector
            LIMIT %s
            """,
            (query_embedding, query_embedding, top_k),
        )
        return cur.fetchall()
    # Returns empty list — table has 0 committed rows

# The integration test that always passed:
#
# def test_insert_and_search():
#     docs = [{"id": 1, "title": "Test", "content": "...", "embedding": [...]}]
#     conn = get_connection()  # ← same connection for both operations
#     insert_embeddings_with_conn(conn, docs)  # ← INSERT within transaction
#     results = search_with_conn(conn, docs[0]["embedding"])  # ← reads from same transaction
#     assert len(results) == 1   # ← passes: reads own uncommitted writes
#     # conn closed here — transaction rolled back
#     # Test passes because it reads within the same transaction scope
#
# The production bug:
# insert_embeddings() called from /api/index endpoint (connection A)
# → INSERT rows → close() → ROLLBACK (never committed)
# 5+ minutes later: Neon scale-to-zero fires (compute suspended)
# search_documents() called from /api/search endpoint (new connection B)
# → SELECT * FROM documents → 0 rows
# Even without Neon scale-to-zero: connection B never sees connection A's uncommitted rows

Why autocommit=False and Neon scale-to-zero interact badly

psycopg2's default autocommit=False means every connection implicitly begins a transaction on the first execute() call. The transaction accumulates changes in memory and in PostgreSQL's write-ahead log as pending. These changes are visible to the same connection (you can SELECT your own uncommitted INSERTs within the same transaction), but they are not visible to any other connection and are not persisted to disk until conn.commit() is called explicitly.

Neon's scale-to-zero feature suspends the compute endpoint after a configurable idle period and terminates all active connections. From PostgreSQL's perspective, terminating a connection with an open transaction is equivalent to the client calling ROLLBACK — all uncommitted changes are discarded and the database is returned to its state before the transaction. The combination is silent: psycopg2's conn.close() succeeds without error even with uncommitted data; Neon's scale-to-zero fires with no application-visible signal. The rollback is invisible until a new connection opens and queries find zero rows where thousands were expected.

This bug also occurs without Neon scale-to-zero — any application crash, container restart, or connection pool timeout that closes a connection before commit() will cause the same data loss. Neon's scale-to-zero amplifies the failure rate because it is a scheduled, guaranteed connection terminator that fires after every period of inactivity, not just on failures.

# Reproducing the bug in isolation
# psycopg2 2.9.9, Python 3.11

import psycopg2
import psycopg2.extras

NEON_DSN = "postgresql://..."

def demonstrate_uncommitted_visibility():
    conn_a = psycopg2.connect(NEON_DSN)   # autocommit=False
    psycopg2.extras.register_vector(conn_a)

    with conn_a.cursor() as cur:
        cur.execute(
            "INSERT INTO documents (id, title, content, embedding) VALUES (%s, %s, %s, %s::vector)",
            (999, "Canary", "Canary content", [0.1] * 1536),
        )
        # Row is in an open transaction on conn_a
        cur.execute("SELECT COUNT(*) FROM documents WHERE id = 999")
        count_within_transaction = cur.fetchone()[0]
        print(f"Count within same transaction: {count_within_transaction}")
        # Output: 1  ← visible to same connection
    # conn_a closed WITHOUT commit() → implicit ROLLBACK

    conn_b = psycopg2.connect(NEON_DSN)   # new connection
    with conn_b.cursor() as cur:
        cur.execute("SELECT COUNT(*) FROM documents WHERE id = 999")
        count_after_rollback = cur.fetchone()[0]
        print(f"Count from new connection after rollback: {count_after_rollback}")
        # Output: 0  ← row was never committed; ROLLBACK discarded it

demonstrate_uncommitted_visibility()
# This is the exact pattern that caused 3 weeks of production debugging:
# same-connection tests pass, cross-connection production queries fail

The fix: conn.commit() and a canary-row startup check

# vector_store.py — FIXED
# Neon serverless PostgreSQL, psycopg2 2.9.9, Python 3.11, pgvector 0.7.4

import psycopg2
import psycopg2.extras
import time

NEON_DSN = "postgresql://user:pass@ep-quiet-fog-12345.us-east-2.aws.neon.tech/neondb?sslmode=require"

def get_connection():
    conn = psycopg2.connect(NEON_DSN)
    psycopg2.extras.register_vector(conn)
    return conn

def insert_embeddings(documents: list[dict]) -> int:
    conn = get_connection()
    try:
        with conn.cursor() as cur:
            records = [
                (doc["id"], doc["title"], doc["content"], doc["embedding"])
                for doc in documents
            ]
            psycopg2.extras.execute_values(
                cur,
                "INSERT INTO documents (id, title, content, embedding) VALUES %s",
                records,
                template="(%s, %s, %s, %s::vector)",
            )
        conn.commit()    # ← FIXED: commit before closing the connection
    except Exception:
        conn.rollback()  # explicit rollback on error — good practice
        raise
    finally:
        conn.close()
    return len(documents)

def search_documents(query_embedding: list[float], top_k: int = 10):
    conn = get_connection()
    try:
        with conn.cursor() as cur:
            cur.execute(
                """
                SELECT id, title, content,
                       1 - (embedding <=> %s::vector) AS similarity
                FROM documents
                ORDER BY embedding <=> %s::vector
                LIMIT %s
                """,
                (query_embedding, query_embedding, top_k),
            )
            return cur.fetchall()
    finally:
        conn.close()

# Alternative: use autocommit=True for insert-heavy workloads
# Each execute() is immediately committed — no explicit commit() needed
# Trade-off: no multi-statement transactions (each INSERT commits independently)
def get_connection_autocommit():
    conn = psycopg2.connect(NEON_DSN)
    psycopg2.extras.register_vector(conn)
    conn.autocommit = True   # every execute() auto-commits
    return conn
# Canary-row startup check
# Run at application startup to verify writes are committed across connections
# Catches: missing commit(), wrong Neon branch, read-replica routing issues

import psycopg2
import psycopg2.extras
import time

CANARY_ID = -1   # sentinel ID that production data never uses

def assert_writes_committed(dsn: str) -> None:
    """
    Inserts a canary row using one connection, closes it, opens a new connection,
    and verifies the row is present. Fails if writes are lost (e.g., missing commit()).
    """
    # Step 1: insert canary row and commit on connection A
    conn_a = psycopg2.connect(dsn)
    psycopg2.extras.register_vector(conn_a)
    with conn_a.cursor() as cur:
        cur.execute("DELETE FROM documents WHERE id = %s", (CANARY_ID,))
        cur.execute(
            "INSERT INTO documents (id, title, content, embedding) VALUES (%s, %s, %s, %s::vector)",
            (CANARY_ID, "_canary_", "_startup_check_", [0.0] * 1536),
        )
    conn_a.commit()     # ← must commit before closing
    conn_a.close()      # connection A closed

    # Step 2: open a completely new connection and verify the canary is present
    conn_b = psycopg2.connect(dsn)
    with conn_b.cursor() as cur:
        cur.execute("SELECT COUNT(*) FROM documents WHERE id = %s", (CANARY_ID,))
        count = cur.fetchone()[0]

    # Step 3: clean up canary row
    with conn_b.cursor() as cur:
        cur.execute("DELETE FROM documents WHERE id = %s", (CANARY_ID,))
    conn_b.commit()
    conn_b.close()

    if count != 1:
        raise RuntimeError(
            f"Write durability check failed: canary row (id={CANARY_ID}) was not found "
            f"in a new connection after commit. Possible causes: autocommit=False with no "
            f"commit() call; connected to a read replica; connected to a different Neon branch."
        )
    print("[OK] Write durability check passed: canary row committed and visible across connections.")

# Call at application startup
assert_writes_committed(NEON_DSN)

# Also: reconnect handler for Neon scale-to-zero events
def execute_with_reconnect(dsn: str, fn, max_retries: int = 2):
    """Re-establishes the connection if Neon scale-to-zero drops it."""
    for attempt in range(max_retries + 1):
        try:
            return fn(psycopg2.connect(dsn))
        except psycopg2.OperationalError as e:
            if "server closed the connection" in str(e) and attempt < max_retries:
                time.sleep(1)  # brief pause before reconnect attempt
                continue
            raise

Disabling scale-to-zero for latency-sensitive workloads

Neon's scale-to-zero is enabled by default on the free tier and optional on paid tiers. For production applications with predictable traffic where sub-second cold start latency is required, disabling scale-to-zero (setting the autoscaling minimum to 0.25 compute units or higher) prevents the connection termination events that expose the missing commit() bug. However, disabling scale-to-zero does not fix the underlying issue — an application that uses psycopg2 with autocommit=False and never calls commit() will still lose all writes on any connection close, including application crashes, container restarts, and connection pool timeouts. The correct fix is always to add conn.commit(); disabling scale-to-zero only removes one of the connection-close triggers.

# Neon compute configuration options for production deployments
# Set via Neon console → Project → Compute settings

# Option A: disable scale-to-zero (paid tiers only)
# autoscaling_min_cu = 0.25  (keeps compute alive; eliminates scale-to-zero cold starts)
# autoscaling_max_cu = 1.0   (scales up for burst traffic)
# Tradeoff: higher monthly cost; compute runs 24/7

# Option B: keep scale-to-zero; add warmup ping in application
# Add a scheduled ping every 4 minutes to prevent idle timeout:
import threading, time, psycopg2

def warmup_ping(dsn: str, interval: int = 240):
    while True:
        time.sleep(interval)
        try:
            conn = psycopg2.connect(dsn)
            with conn.cursor() as cur:
                cur.execute("SELECT 1")
            conn.close()
        except Exception:
            pass  # reconnect on next real request

# Start ping thread at app startup (daemon=True so it doesn't block shutdown)
threading.Thread(target=warmup_ping, args=(NEON_DSN,), daemon=True).start()
# Tradeoff: prevents scale-to-zero; free tier compute remains active
# Does NOT replace conn.commit() — still required for data durability

Validation

# Validation log — October 31 2026
# Neon serverless PostgreSQL, psycopg2 2.9.9, Python 3.11, pgvector 0.7.4

# ---- Before fix ----
# insert_embeddings() called with 500 docs: returned 500 (no error)
# search_documents() called from new connection: 0 results
# Repro: same-session SELECT within uncommitted transaction: 500 rows visible
# Cross-connection SELECT: 0 rows
# Neon scale-to-zero fires after 5 min: compute suspended → connection terminated → ROLLBACK

# ---- After fix (conn.commit() added) ----
# assert_writes_committed(NEON_DSN): [OK] Write durability check passed
# insert_embeddings() called with 500 docs: conn.commit() called; 500 rows committed
# Application restarted (new connections):
#   search_documents() called: 500 rows returned ← now persisted
# Neon scale-to-zero fired and compute resumed:
#   search_documents() called after cold start: 500 rows returned ← data survived rollback window
# Idle 10 minutes → scale-to-zero → new connection:
#   SELECT COUNT(*) FROM documents: 500 rows ← committed data survives scale-to-zero

# ---- Canary check results ----
# assert_writes_committed() run at startup: PASSED
# Canary row (id=-1) found in new connection after commit: confirmed

# ---- Reconnect handler ----
# Simulated scale-to-zero by force-closing Neon compute: OperationalError raised
# execute_with_reconnect() caught error, retried: SUCCESS on second attempt
# p99 latency for reconnect scenario: 1,400ms (Neon cold start ~1,200ms + retry)

# Retainer engagement cost: 2.0h (estimated $150-230 at mid-level rates)

Related retainer posts

Track retainer hours without a client portal

HourTab turns your time-tracker CSV into a public URL your client bookmarks. No login. No portal. CSV in, URL out.

See pricing →