Insertar miles de filas en bloque snippet

-- one statement, one round trip — 2 rows here, thousands scale the same way:
INSERT INTO users (name, email) VALUES
    ('Ada', 'ada@x.test'),
    ('Grace', 'grace@x.test')
RETURNING id;

-- the ceiling does the arithmetic for you: Postgres allows at most 65535 bind
-- parameters per statement, so rows-per-chunk = floor(65535 / columns) —
-- 2 columns => 32767 rows max per statement. Chunk there, not at a guess.

// node-postgres: build $1..$N placeholders, flatten the rows, send ONE statement:
const cols = ['name', 'email'];
const rows = [['Ada', 'ada@x.test'], ['Grace', 'grace@x.test']];

const tuples = rows
  .map((_, r) => `(${cols.map((_, c) => `$${r * cols.length + c + 1}`).join(', ')})`)
  .join(', ');
const sql = `INSERT INTO users (${cols.join(', ')}) VALUES ${tuples}`;
await pool.query(sql, rows.flat()); // 2 rows x 2 cols = $1..$4, one round trip

// the faster lane: COPY FROM STDIN via pg-copy-streams beats every INSERT form

type Row = Record<string, string | number>;

function buildInsert(
  table: string,
  rows: Row[],
): { sql: string; args: (string | number)[] } {
  const cols = Object.keys(rows[0]);
  const tuples = rows.map((row, r) =>
    `(${cols.map((_, i) => `$${r * cols.length + i + 1}`).join(', ')})`);
  const args = rows.flatMap((row) => cols.map((c) => row[c]));
  return {
    sql: `INSERT INTO ${table} (${cols.join(', ')}) VALUES ${tuples.join(', ')}`,
    args,
  };
}

// the cap chunker: MAX_PARAMS = 65535, chunk = Math.floor(MAX_PARAMS / cols.length)
for (const chunk of chunkRows(rows, 65535 / cols.length)) {
  await pool.query(buildInsert('users', chunk));
}

import (
	"database/sql"
	"fmt"
	"strings"
)

// one statement per chunk: VALUES ($1,$2),($3,$4),... + one flat args slice:
func insertChunk(db *sql.DB, rows [][2]string) error {
	var b strings.Builder
	b.WriteString("INSERT INTO users (name, email) VALUES ")
	args := make([]any, 0, len(rows)*2)
	for i, r := range rows {
		if i > 0 {
			b.WriteByte(',')
		}
		fmt.Fprintf(&b, "($%d,$%d)", i*2+1, i*2+2) // $1,$2 / $3,$4 ...
		args = append(args, r[0], r[1])
	}
	_, err := db.Exec(b.String(), args...) // ONE round trip for the whole chunk
	return err
}

// chunk rows at 65535/2 = 32767 per statement, call insertChunk per chunk

use sqlx::query_builder::QueryBuilder;

// push_values builds one multi-VALUES statement with every value bound:
let mut qb = QueryBuilder::new("INSERT INTO users (name, email) ");
qb.push_values(&rows, |mut b, (name, email)| {
    b.push_bind(name).push_bind(email);
});
qb.build().execute(&pool).await?;

// the faster lane is Postgres COPY — tokio_postgres::Client::copy_in:
//   let sink = client.copy_in("COPY users FROM STDIN (FORMAT csv)").await?;
//   sink.send(csv_bytes).await?; // one stream, no statement, no binds

// one prepare, a (?,?) x N placeholder string, one execute with a flat array:
$rows = [['Ada', 'ada@x.test'], ['Grace', 'grace@x.test']];

$placeholders = implode(', ', array_fill(0, count($rows), '(?, ?)'));
$stmt = $pdo->prepare("INSERT INTO users (name, email) VALUES {$placeholders}");
$stmt->execute(array_merge(...$rows)); // flat: a0,a1,b0,b1 — one round trip

# psycopg3 — executemany is NOW one round trip (pipeline mode):
cur.executemany(
    'INSERT INTO users (name, email) VALUES (%s, %s)',
    rows,
)

# psycopg2 needed the workaround — one multi-VALUES statement instead:
#   from psycopg2.extras import execute_values
#   execute_values(cur, 'INSERT INTO users (name, email) VALUES %s', rows)

# the bulk loader — beats every INSERT form:
with cur.copy('COPY users (name, email) FROM STDIN') as copy:
    for name, email in rows:
        copy.write_row((name, email))

// Postgres: Npgsql's binary COPY — the bulk loader:
await using (var writer = await conn.BeginBinaryImportAsync(
                 "COPY users (name, email) FROM STDIN (FORMAT BINARY)"))
{
    foreach (var (name, email) in rows)
    {
        await writer.StartRowAsync();
        await writer.WriteAsync(name, NpgsqlDbType.Text);
        await writer.WriteAsync(email, NpgsqlDbType.Text);
    }
    await writer.CompleteAsync(); // omit this and the whole COPY rolls back
}

// SQL Server instead: SqlBulkCopy is its native bulk path:
//   using var bulk = new SqlBulkCopy(conn) { DestinationTableName = "users" };
//   bulk.WriteToServer(dataTable);

String sql = "INSERT INTO users (name, email) VALUES (?, ?)";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
    conn.setAutoCommit(false);
    int n = 0;
    for (String[] row : rows) {
        ps.setString(1, row[0]);
        ps.setString(2, row[1]);
        ps.addBatch();                     // accumulate — never execute per row
        if (++n % 1000 == 0) ps.executeBatch(); // flush periodically
    }
    ps.executeBatch();                     // the tail
    conn.commit();
}

connection.prepareStatement("INSERT INTO users (name, email) VALUES (?, ?)").use { ps ->
    connection.autoCommit = false
    rows.chunked(1000).forEach { chunk -> // the batch-size tuning knob
        chunk.forEach { (name, email) ->
            ps.setString(1, name)
            ps.setString(2, email)
            ps.addBatch()
        }
        ps.executeBatch()
    }
    connection.commit()
}

require 'sequel'

# import(columns, array-of-arrays) = one multi-VALUES statement, values bound:
DB[:users].import(
  [:name, :email],
  [['Ada', 'ada@x.test'], ['Grace', 'grace@x.test']],
  slice: 500, # chunks at 500 rows per statement
)
# multi_insert(rows_of_hashes) is the same API in hash shape

# the pg gem's COPY path — fastest possible:
#   DB.copy_data('COPY users (name, email) FROM STDIN') do |copy|
#     rows.each { |name, email| copy.put_copy_data("#{name}\t#{email}\n") }
#   end