Los 4 approaches con benchmarks reales

"COPY es más rápido" en abstracto no convence. 47 segundos vs 0.8 segundos sí convence. Esta cápsula te muestra los 4 approaches de bulk insert con benchmarks reproducibles. Vas a correr cada uno con la misma data (100,000 filas), medir el tiempo, y ver la diferencia con tus ojos. Después no vas a olvidar cuándo elegir cada uno.

Los 4 approaches son: loop INSERT, executemany, bulk_insert_mappings, y COPY. Cada uno tiene su lugar — ninguno es siempre el correcto. La idea de esta cápsula es que midas vos mismo y desarrolles intuición.

Setup del benchmark: tabla simple, 100k filas, mismo hardware, misma conexión. Variable: el approach. Medimos time.perf_counter() antes y después.

---

Setup del benchmark

# benchmark_setup.py
import asyncio
from sqlalchemy import String, Integer, Numeric
from sqlalchemy.ext.asyncio import create_async_engine, async_sessionmaker
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column


DATABASE_URL = "postgresql+asyncpg://postgres:postgres@localhost/test"


class Base(DeclarativeBase):
    pass


class Task(Base):
    __tablename__ = "tasks_bench"

    id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True)
    title: Mapped[str] = mapped_column(String(200))
    status: Mapped[str] = mapped_column(String(50))
    priority: Mapped[int] = mapped_column(Integer)


async def setup():
    engine = create_async_engine(DATABASE_URL)
    async with engine.begin() as conn:
        await conn.run_sync(Base.metadata.drop_all)
        await conn.run_sync(Base.metadata.create_all)
    await engine.dispose()


asyncio.run(setup())

Generación de datos:

def generate_data(n: int) -> list[dict]:
    return [
        {
            "title": f"Task {i}",
            "status": "pending" if i % 3 != 0 else "completed",
            "priority": i % 5 + 1,
        }
        for i in range(n)
    ]


N = 100_000
data = generate_data(N)

---

Approach 1: Loop INSERT

import time
import asyncio


async def benchmark_loop():
    engine = create_async_engine(DATABASE_URL)
    SessionLocal = async_sessionmaker(engine, expire_on_commit=False)

    # Truncate antes
    async with engine.begin() as conn:
        await conn.execute(text("TRUNCATE tasks_bench"))

    start = time.perf_counter()

    async with SessionLocal() as session:
        for row in data:
            task = Task(**row)
            session.add(task)
        await session.commit()

    elapsed = time.perf_counter() - start
    print(f"Loop INSERT (n={N}): {elapsed:.2f}s")


asyncio.run(benchmark_loop())

Resultado típico:

Loop INSERT (n=100000): 47.32s

Throughput: ~2,100 inserts/segundo. Cada INSERT tiene overhead de SQLAlchemy ORM (event listeners, identity map, etc.) más network round-trip si la DB no está local.

---

Approach 2: executemany

session.execute(insert(Model), [...]) ejecuta múltiples INSERTs en una sola call al driver. PostgreSQL los recibe en un batch, reduciendo round-trips.

from sqlalchemy import insert


async def benchmark_executemany():
    engine = create_async_engine(DATABASE_URL)
    SessionLocal = async_sessionmaker(engine, expire_on_commit=False)

    async with engine.begin() as conn:
        await conn.execute(text("TRUNCATE tasks_bench"))

    start = time.perf_counter()

    async with SessionLocal() as session:
        await session.execute(insert(Task), data)
        await session.commit()

    elapsed = time.perf_counter() - start
    print(f"executemany (n={N}): {elapsed:.2f}s")


asyncio.run(benchmark_executemany())

Resultado típico:

executemany (n=100000): 12.18s

Throughput: ~8,200 inserts/segundo. ~4x más rápido que loop. Sigue pasando por el ORM (procesa cada fila), pero sin overhead de identity map ni event listeners por fila.

Nota: en versiones recientes de SQLAlchemy 2.0+, este approach usa "insertmanyvalues" optimization automáticamente, agrupando múltiples filas en menos statements SQL.

---

Approach 3: bulk_insert_mappings

session.bulk_insert_mappings(Model, [...]) saltea el ORM por completo. No crea instances de Task — solo construye SQL parametrizado y lo ejecuta.

async def benchmark_bulk_insert():
    engine = create_async_engine(DATABASE_URL)
    SessionLocal = async_sessionmaker(engine, expire_on_commit=False)

    async with engine.begin() as conn:
        await conn.execute(text("TRUNCATE tasks_bench"))

    start = time.perf_counter()

    async with SessionLocal() as session:
        # SQLAlchemy 2.0 syntax
        await session.execute(insert(Task), data)
        # Equivalente al "bulk_insert_mappings" de versiones anteriores
        # En 2.0+, insert(Table) con list es la forma canónica
        await session.commit()

    elapsed = time.perf_counter() - start
    print(f"bulk_insert (n={N}): {elapsed:.2f}s")


asyncio.run(benchmark_bulk_insert())

Resultado típico:

bulk_insert (n=100000): 4.27s

Throughput: ~23,400 inserts/segundo. ~3x más rápido que executemany. Sin event listeners, sin identity map, sin instance creation. Solo SQL.

Limitaciones críticas:

• No dispara before_insert/after_insert events.
• No dispara @validates decorators.
• No aplica column defaults Python (default=lambda: ...) — solo SQL defaults (server_default=).
• No devuelve PKs autogenerados (necesitas re-fetch).

Si tu modelo depende de cualquiera de estos para correctness, NO usar bulk_insert_mappings.

---

Approach 4: COPY con asyncpg

COPY FROM STDIN es el bulk loader nativo de PostgreSQL. Bypasea SQL parsing por fila — recibe data en formato binario o CSV y la escribe directamente al heap.

import asyncpg


async def benchmark_copy():
    # asyncpg directo (no SQLAlchemy)
    conn = await asyncpg.connect(
        "postgresql://postgres:postgres@localhost/test"
    )

    await conn.execute("TRUNCATE tasks_bench")

    start = time.perf_counter()

    # copy_records_to_table acepta una lista de tuples
    records = [(row["title"], row["status"], row["priority"]) for row in data]

    await conn.copy_records_to_table(
        "tasks_bench",
        records=records,
        columns=["title", "status", "priority"],
    )

    elapsed = time.perf_counter() - start
    print(f"COPY (n={N}): {elapsed:.2f}s")

    await conn.close()


asyncio.run(benchmark_copy())

Resultado típico:

COPY (n=100000): 0.83s

Throughput: ~120,000 inserts/segundo. 60x más rápido que loop, 14x más rápido que executemany.

¿Por qué es tan rápido? PostgreSQL recibe data en formato binario eficiente, sin parse de SQL por fila, sin checks de constraints en formato verboso. Es literalmente "copia esto al heap, luego rebuilds indexes".

---

Tabla comparativa

Approach	Tiempo	Throughput	Eventos ORM	Limitaciones
Loop INSERT	47.32s	2.1k/s	Sí (todos)	Lento, no escala
executemany	12.18s	8.2k/s	Sí (todos)	Aceptable hasta ~10k filas
bulk_insert	4.27s	23.4k/s	No	Saltea events, validators, defaults Python
COPY	0.83s	120k/s	No	No soporta ON CONFLICT directo, requiere temp table para upserts

Frente al cliente final:

• 100 filas: cualquiera. Diferencia <100ms invisible.
• 1,000 filas: executemany suficiente.
• 10,000 filas: bulk_insert preferible (4s vs 12s = 3x mejor).
• 100,000+ filas: COPY obligatorio (0.8s vs 4s = otra mejora 5x).

---

La curva no es lineal

Aumentando N, la curva no escala linealmente:

N	Loop	executemany	bulk_insert	COPY
1k	0.5s	0.15s	0.06s	0.02s
10k	4.5s	1.3s	0.5s	0.1s
100k	47s	12s	4.3s	0.8s
1M	~480s	~115s	~42s	~7s
10M	OOM o timeout	~1100s	~410s	~62s

Loop a 10M filas ya es operación de minutos. Con loop, 100M filas son horas → días. Con COPY, 100M filas son ~10 minutos.

Lección: el approach correcto depende del tamaño actual y del crecimiento esperado. Si tu app va a importar 100k filas hoy y 10M en 6 meses, escribí COPY desde día 1.

---

Trampas y errores comunes

1. Comparar approaches sin truncate.

Si no haces TRUNCATE antes de cada benchmark, los inserts del run anterior afectan al siguiente (por bloat, por triggers, por checks). Truncate antes garantiza condiciones idénticas.

2. Ejecutar en psql y comparar con código Python.

psql \copy es local, sin overhead de network ni Python. Comparar contra Python directly es injusto. Compara approaches dentro de Python.

3. Olvidar await session.commit().

Sin commit, las filas no se persisten — el benchmark se ve "rapidísimo" pero no hizo nada real.

4. Usar INSERT ... RETURNING cuando no necesitas IDs.

RETURNING * requiere PostgreSQL devuelva todas las columnas insertadas. Para bulk, esto es overhead grande. Si no necesitas las IDs, no uses RETURNING.

5. Medir desde dentro de un test framework con teardown lento.

Si corres benchmark dentro de pytest con teardown que hace cleanup, los tiempos pueden inflar. Correr benchmarks en script standalone.

6. Comparar approaches con índices distintos.

Si tu tabla tiene 5 índices, cada INSERT updatea 5 índices. Cada approach paga ese costo. Pero si comparas tabla con índices vs tabla sin, los números no son comparables. Mismo schema en todos los benchmarks.

7. Asumir que tu hardware da los mismos números.

47s en mi laptop puede ser 80s en tu container, 25s en tu server bare metal. Lo que importa es la proporción entre approaches (60x diferencia) — esa es consistente.

8. No considerar concurrencia.

Estos benchmarks son single-connection. En producción real, múltiples requests concurrentes pueden saturar la DB. COPY consume más CPU/IO que executemany — si tienes 10 imports concurrentes, executemany puede ser mejor por menos contention.

---

Ejercicio: reproducir benchmarks

Setup: PostgreSQL local (via Docker o nativo), Python 3.12+, pip install sqlalchemy[asyncio] asyncpg.

Paso 1: crear el script benchmark.py con los 4 approaches.

# benchmark.py
import asyncio
import time

# ... copy de las funciones de arriba

async def main():
    print(f"Benchmarking with N = {N}")
    await benchmark_loop()
    await benchmark_executemany()
    await benchmark_bulk_insert()
    await benchmark_copy()


if __name__ == "__main__":
    asyncio.run(main())

Paso 2: correr.

python benchmark.py

Paso 3: documentar los resultados.

Approach	Tiempo	Throughput
Loop	?	?
executemany	?	?
bulk_insert	?	?
COPY	?	?

Paso 4: repetir con N distintos (1k, 10k, 1M) y graficar la curva.

Paso 5: experimentar.

• ¿Qué pasa si comentas await session.commit() en alguno?
• ¿Qué pasa si agregas un índice a la tabla? ¿Cómo cambia cada approach?
• ¿Qué pasa si la tabla tiene un trigger PL/pgSQL BEFORE INSERT? ¿Cuáles approaches lo disparan?

Ver discusión

Paso 4 — curva con N distintos:

La curva muestra que la diferencia se hace más pronunciada con N grande. Para N=100, los 4 approaches están en el rango de milliseconds — diferencia invisible. Para N=1M, la diferencia es minutos.

Paso 5 — experimentos:

Sin commit: los datos no persisten. La función "termina" pero la tabla está vacía. Si comparás benchmarks "sin commit", los números son falsos.

Con índices: cada INSERT updatea cada índice. Loop e executemany sufren más (cada fila paga el cost). bulk_insert y COPY también pero menos (PostgreSQL puede optimizar batch updates). Para imports masivos a tabla con muchos índices, considerar drop indexes → import → recreate indexes.

Con trigger PL/pgSQL: triggers de PostgreSQL (no de SQLAlchemy) sí se disparan en TODOS los approaches incluyendo COPY. Lo que se saltea con bulk_insert/COPY son eventos del ORM Python, no triggers de la DB.

Lecciones clave:

1. La diferencia es real y reproducible.
2. La proporción es lo que importa (60x), no el número absoluto.
3. Trade-offs son distintos: COPY más rápido pero pierde eventos Python; bulk_insert balance medio.
4. Triggers DB (PL/pgSQL) sí funcionan con todos.

---

Resumen y siguiente paso

Lo que aprendiste:

• 4 approaches con benchmarks reales: 47s → 12s → 4.3s → 0.8s para 100k filas.
• Loop INSERT: simple, suficiente <100 filas.
• executemany: bueno hasta ~10k filas, mantiene events.
• bulk_insert (insert(Model), list): rápido pero saltea events Python.
• COPY: 60x más rápido que loop, requiere asyncpg directo (no SQLAlchemy ORM).
• Curva no lineal: diferencias se amplifican con N grande.
• Trade-offs específicos: events ORM, defaults Python, RETURNING IDs.

Antes de avanzar, deberías poder:

• Reproducir los benchmarks en tu setup.
• Decidir el approach correcto basado en N y constraints (events necesarios?).
• Explicar por qué COPY es tan rápido (binary format, no parse per row).
• Justificar la decision matrix por tamaño en code review.

En la siguiente cápsula profundizamos en el más potente: COPY con asyncpg. Vas a aprender el patrón completo — copy_records_to_table vs copy_to_table, serialización de tipos especiales (datetime, JSON, NULL), formato binario para casos extremos, y manejo de errores. Es el patrón que usás cuando bulk_insert no alcanza.

---

Recursos

1. PostgreSQL Docs — COPY — referencia oficial.
2. asyncpg — copy_records_to_table — referencia.
3. SQLAlchemy 2.0 — Insertmanyvalues — la optimization de executemany en 2.0+.
4. Brandur Leach — Postgres bulk insert benchmarks — benchmarks profundos.
5. Citus Data — Bulk loading benchmarks — comparación detallada.
6. pg_bulkload — extensión para casos extremos (>100M filas).
7. Tom Augspurger — pandas to_sql performance — perspectiva data engineering.

---

Cápsula 02 de 08 — Módulo 7 — SQL Patterns for Production APIs Guide