Pools de conexiones, copias de seguridad y permisos

En resumen: Una base de datos detrás de un sitio web en marcha necesita un pool que reutilice conexiones abiertas, copias de seguridad de las que de verdad se pueda restaurar y un usuario con solo los permisos necesarios.

Problema

Viernes por la noche

La tienda lanza una promoción y en cinco minutos llegan 400 personas. El sitio se detiene y el registro dice:

FATAL:  sorry, too many clients already

El programa abre una conexión nueva a la base de datos para cada página y la cierra al final. Abrir una no es gratis: un viaje por la red, un inicio de sesión con contraseña y, en PostgreSQL, un proceso nuevo en el servidor. Y el servidor acepta un número limitado de conexiones (max_connections, 100 por defecto). Cuatrocientas páginas a la vez son cuatrocientas conexiones.

La idea

Un pool de conexiones

Un pool mantiene unas cuantas conexiones abiertas. Una página toma una libre, la usa y la devuelve, y la siguiente toma la misma. Abrir se paga una vez, y la base de datos nunca ve más conexiones que el tamaño del pool.

Aquí hay un pequeño pool de tres conexiones, escrito a mano para que veas qué hace. borrow da una conexión libre y, si no hay ninguna, abre una nueva, pero nunca más de tres. giveBack la devuelve:

Java

static final int MAX = 3;
static final Deque<Connection> free = new ArrayDeque<>();
static int opened = 0;

static Connection borrow() throws SQLException {
    if (!free.isEmpty()) return free.pop();
    if (opened == MAX) throw new SQLException("el pool está vacío");
    opened++;
    Connection conn = DriverManager.getConnection("jdbc:sqlite::memory:");
    setup(conn);
    return conn;
}

static void giveBack(Connection conn) {
    free.push(conn);
}

static String productName(int id) throws SQLException {
    Connection conn = borrow();
    try (PreparedStatement ps = conn.prepareStatement("SELECT name FROM products WHERE id = ?")) {
        ps.setInt(1, id);
        try (ResultSet rs = ps.executeQuery()) {
            if (!rs.next()) return "no existe el producto";  // la conexión nunca se devuelve
            String name = rs.getString("name");
            giveBack(conn);
            return name;
        }
    }
}

for (int id : new int[] { 1, 99, 5, 98, 97, 7 }) {
    try {
        System.out.println("producto " + id + ": " + productName(id));
    } catch (SQLException e) {
        System.out.println("producto " + id + ": error: " + e.getMessage());
    }
}
System.out.println("conexiones abiertas: " + opened + ", libres: " + free.size());

C#

const int Max = 3;
var free = new Stack<SqliteConnection>();
var opened = 0;

foreach (var id in new[] { 1, 99, 5, 98, 97, 7 })
{
    try
    {
        Console.WriteLine($"producto {id}: {ProductName(id)}");
    }
    catch (InvalidOperationException e)
    {
        Console.WriteLine($"producto {id}: error: {e.Message}");
    }
}
Console.WriteLine($"conexiones abiertas: {opened}, libres: {free.Count}");

SqliteConnection Borrow()
{
    if (free.Count > 0) return free.Pop();
    if (opened == Max) throw new InvalidOperationException("el pool está vacío");
    opened++;
    var conn = new SqliteConnection("Data Source=:memory:");
    conn.Open();
    Setup(conn);
    return conn;
}

void GiveBack(SqliteConnection conn) => free.Push(conn);

string ProductName(int id)
{
    var conn = Borrow();
    var cmd = conn.CreateCommand();
    cmd.CommandText = "SELECT name FROM products WHERE id = $id";
    cmd.Parameters.AddWithValue("$id", id);
    var name = cmd.ExecuteScalar();
    if (name is null) return "no existe el producto";  // la conexión nunca se devuelve
    GiveBack(conn);
    return (string)name;
}

Python

MAX = 3
free = []
opened = 0


def borrow():
    global opened
    if free:
        return free.pop()
    if opened == MAX:
        raise RuntimeError("el pool está vacío")
    opened += 1
    conn = sqlite3.connect(":memory:")
    setup(conn)
    return conn


def give_back(conn):
    free.append(conn)


def product_name(product_id):
    conn = borrow()
    row = conn.execute("SELECT name FROM products WHERE id = ?", (product_id,)).fetchone()
    if row is None:
        return "no existe el producto"  # la conexión nunca se devuelve
    give_back(conn)
    return row[0]


for product_id in [1, 99, 5, 98, 97, 7]:
    try:
        print(f"producto {product_id}: {product_name(product_id)}")
    except RuntimeError as e:
        print(f"producto {product_id}: error: {e}")
print(f"conexiones abiertas: {opened}, libres: {len(free)}")

JavaScript

const MAX = 3;
const free = [];
let opened = 0;

function borrow() {
  if (free.length > 0) return free.pop();
  if (opened === MAX) throw new Error("el pool está vacío");
  opened++;
  const db = new DatabaseSync(":memory:");
  setup(db);
  return db;
}

function giveBack(db) {
  free.push(db);
}

function productName(id) {
  const db = borrow();
  const row = db.prepare("SELECT name FROM products WHERE id = ?").get(id);
  if (row === undefined) return "no existe el producto"; // la conexión nunca se devuelve
  giveBack(db);
  return row.name;
}

for (const id of [1, 99, 5, 98, 97, 7]) {
  try {
    console.log(`producto ${id}: ${productName(id)}`);
  } catch (e) {
    console.log(`producto ${id}: error: ${e.message}`);
  }
}
console.log(`conexiones abiertas: ${opened}, libres: ${free.length}`);

Míralo

Una conexión perdida

La función del nombre de un producto tiene dos salidas. Cuando el producto existe, devuelve la conexión. Cuando no existe, sale antes de tiempo y la conexión sigue ocupada:

Java

static String productName(int id) throws SQLException {
    Connection conn = borrow();
    try (PreparedStatement ps = conn.prepareStatement("SELECT name FROM products WHERE id = ?")) {
        ps.setInt(1, id);
        try (ResultSet rs = ps.executeQuery()) {
            if (!rs.next()) return "no existe el producto";  // la conexión nunca se devuelve
            String name = rs.getString("name");
            giveBack(conn);
            return name;
        }
    }
}

Salida

producto 1: Pan blanco
producto 99: no existe el producto
producto 5: Leche fresca
producto 98: no existe el producto
producto 97: no existe el producto
producto 7: error: el pool está vacío
conexiones abiertas: 3, libres: 0

C#

string ProductName(int id)
{
    var conn = Borrow();
    var cmd = conn.CreateCommand();
    cmd.CommandText = "SELECT name FROM products WHERE id = $id";
    cmd.Parameters.AddWithValue("$id", id);
    var name = cmd.ExecuteScalar();
    if (name is null) return "no existe el producto";  // la conexión nunca se devuelve
    GiveBack(conn);
    return (string)name;
}

Salida

producto 1: Pan blanco
producto 99: no existe el producto
producto 5: Leche fresca
producto 98: no existe el producto
producto 97: no existe el producto
producto 7: error: el pool está vacío
conexiones abiertas: 3, libres: 0

Python

def product_name(product_id):
    conn = borrow()
    row = conn.execute("SELECT name FROM products WHERE id = ?", (product_id,)).fetchone()
    if row is None:
        return "no existe el producto"  # la conexión nunca se devuelve
    give_back(conn)
    return row[0]

Salida

producto 1: Pan blanco
producto 99: no existe el producto
producto 5: Leche fresca
producto 98: no existe el producto
producto 97: no existe el producto
producto 7: error: el pool está vacío
conexiones abiertas: 3, libres: 0

JavaScript

function productName(id) {
  const db = borrow();
  const row = db.prepare("SELECT name FROM products WHERE id = ?").get(id);
  if (row === undefined) return "no existe el producto"; // la conexión nunca se devuelve
  giveBack(db);
  return row.name;
}

Salida

producto 1: Pan blanco
producto 99: no existe el producto
producto 5: Leche fresca
producto 98: no existe el producto
producto 97: no existe el producto
producto 7: error: el pool está vacío
conexiones abiertas: 3, libres: 0

Tres búsquedas de un producto que no existe y el pool queda vacío. La sexta petición es perfectamente normal, pero no recibe ninguna conexión. Un pool de verdad no falla al instante: espera una conexión libre (hasta 30 segundos en HikariCP) y la página se cuelga. Por eso una fuga se nota primero como «el sitio va lento» y después como «el sitio no responde».

La idea

Devolverla va en finally

La solución es devolver la conexión salga como salga la función:

Java

static String productName(int id) throws SQLException {
    Connection conn = borrow();
    try (PreparedStatement ps = conn.prepareStatement("SELECT name FROM products WHERE id = ?")) {
        ps.setInt(1, id);
        try (ResultSet rs = ps.executeQuery()) {
            return rs.next() ? rs.getString("name") : "no existe el producto";
        }
    } finally {
        giveBack(conn);
    }
}

Salida

producto 1: Pan blanco
producto 99: no existe el producto
producto 5: Leche fresca
producto 98: no existe el producto
producto 97: no existe el producto
producto 7: Queso blanco
conexiones abiertas: 1, libres: 1

C#

string ProductName(int id)
{
    var conn = Borrow();
    try
    {
        var cmd = conn.CreateCommand();
        cmd.CommandText = "SELECT name FROM products WHERE id = $id";
        cmd.Parameters.AddWithValue("$id", id);
        return cmd.ExecuteScalar() as string ?? "no existe el producto";
    }
    finally
    {
        GiveBack(conn);
    }
}

Salida

producto 1: Pan blanco
producto 99: no existe el producto
producto 5: Leche fresca
producto 98: no existe el producto
producto 97: no existe el producto
producto 7: Queso blanco
conexiones abiertas: 1, libres: 1

Python

def product_name(product_id):
    conn = borrow()
    try:
        row = conn.execute("SELECT name FROM products WHERE id = ?", (product_id,)).fetchone()
        return row[0] if row else "no existe el producto"
    finally:
        give_back(conn)

Salida

producto 1: Pan blanco
producto 99: no existe el producto
producto 5: Leche fresca
producto 98: no existe el producto
producto 97: no existe el producto
producto 7: Queso blanco
conexiones abiertas: 1, libres: 1

JavaScript

function productName(id) {
  const db = borrow();
  try {
    const row = db.prepare("SELECT name FROM products WHERE id = ?").get(id);
    return row ? row.name : "no existe el producto";
  } finally {
    giveBack(db);
  }
}

Salida

producto 1: Pan blanco
producto 99: no existe el producto
producto 5: Leche fresca
producto 98: no existe el producto
producto 97: no existe el producto
producto 7: Queso blanco
conexiones abiertas: 1, libres: 1

Seis peticiones con una sola conexión: cada una la devuelve y la siguiente la toma. Con las bibliotecas reales no escribes un pool tú mismo. Tomas una conexión del pool y la cierras como en la primera lección del módulo, y cerrar la devuelve al pool en lugar de cerrarla de verdad:

Java

HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:postgresql://localhost:5432/shop");
config.setUsername("shop_app");
config.setPassword(System.getenv("SHOP_DB_PASSWORD"));
config.setMaximumPoolSize(10);
HikariDataSource pool = new HikariDataSource(config);  // una vez, al arrancar

try (Connection conn = pool.getConnection()) {  // close() la devuelve al pool
    // las consultas
}

C#

var password = Environment.GetEnvironmentVariable("SHOP_DB_PASSWORD");
var url = $"Host=localhost;Database=shop;Username=shop_app;Password={password};Maximum Pool Size=20";
await using var dataSource = NpgsqlDataSource.Create(url);  // una vez, al arrancar

await using (var conn = await dataSource.OpenConnectionAsync())  // Dispose la devuelve al pool
{
    // las consultas
}

Python

password = os.environ["SHOP_DB_PASSWORD"]
engine = create_engine(  # una vez, al arrancar
    f"postgresql+psycopg://shop_app:{password}@localhost/shop",
    pool_size=5,
    max_overflow=10,
)

with engine.connect() as conn:  # el final de with la devuelve al pool
    ...  # las consultas

JavaScript

const pool = new pg.Pool({  // una vez, al arrancar
  host: "localhost",
  database: "shop",
  user: "shop_app",
  password: process.env.SHOP_DB_PASSWORD,
  max: 10,
});

const client = await pool.connect();
try {
  // las consultas
} finally {
  client.release();  // la devuelve al pool
}

Más precisamente, para curiosos

Estos ejemplos no los ejecuta la comprobación del sitio, porque necesitan PostgreSQL y los paquetes del pool. Los tamaños por defecto son: HikariCP 10, Npgsql 100, SQLAlchemy 5 (y 10 más si hace falta), pg 10.

Un pool más grande no significa un sitio más rápido. La base de datos tiene los núcleos que tiene, y 200 consultas a la vez solo se esperan unas a otras. La guía de HikariCP sugiere empezar con unas dos veces el número de núcleos del servidor de la base de datos. Si el sitio corre en cinco servidores con 20 conexiones cada uno, la base de datos ve 100. Cuando hay demasiadas conexiones, delante de PostgreSQL se pone un pool aparte como PgBouncer.

La idea

Una copia de seguridad

Algún día la base de datos se dañará: un disco, una consulta equivocada, una migración con un error. Una copia es el único camino de vuelta. En SQLite VACUUM INTO escribe toda la base de datos en un archivo nuevo mientras está en marcha, y ATTACH abre ese archivo como una segunda base de datos en la misma conexión:

SQLbase de datos: tienda

VACUUM INTO '/copia-2026-10-25-0300.db';
ATTACH '/copia-2026-10-25-0300.db' AS copia;

SELECT
  (SELECT COUNT(*) FROM orders) AS orders_now,
  (SELECT COUNT(*) FROM copia.orders) AS orders_in_copy;

VACUUM INTO '/copia-2026-10-25-0300.db'; ATTACH '/copia-2026-10-25-0300.db' AS copia; SELECT (SELECT COUNT(*) FROM orders) AS orders_now, (SELECT COUNT(*) FROM copia.orders) AS orders_in_copy;

▶ EjecutarRestaurar la consultaBase nueva

Aquí se ve para qué sirve la copia. Un script borra por error las reseñas del café, y la copia de hace un minuto las devuelve:

SQLbase de datos: tienda

VACUUM INTO '/copia-2026-10-25-1415.db';
DELETE FROM reviews WHERE product_id = 17;

ATTACH '/copia-2026-10-25-1415.db' AS copia;
INSERT INTO reviews
SELECT * FROM copia.reviews WHERE product_id = 17;

SELECT id, product_id, rating FROM reviews WHERE product_id = 17;

VACUUM INTO '/copia-2026-10-25-1415.db'; DELETE FROM reviews WHERE product_id = 17; ATTACH '/copia-2026-10-25-1415.db' AS copia; INSERT INTO reviews SELECT * FROM copia.reviews WHERE product_id = 17; SELECT id, product_id, rating FROM reviews WHERE product_id = 17;

▶ EjecutarRestaurar la consultaBase nueva

Ejecuta el cuadro por segunda vez y VACUUM INTO se detiene, porque el archivo ya existe. En la vida real es igual: cada copia es un archivo nuevo con la fecha y la hora en el nombre.

Más precisamente, para curiosos

En PostgreSQL pg_dump hace una copia de una base de datos, y pg_basebackup con un archivo WAL permite restaurar a un momento exacto («a las 14:14, antes del script»). En MySQL es mysqldump. Una regla habitual es 3-2-1: tres copias, en dos tipos de soporte, una de ellas en otro sitio.

Pruébalo

Comprobar después de restaurar

Una copia de la que nunca has restaurado es solo una esperanza. Después de cada restauración (al menos en una base de pruebas) compruebas que la base de datos está entera:

SQLbase de datos: tienda

PRAGMA integrity_check;

PRAGMA integrity_check;

▶ EjecutarRestaurar la consultaBase nueva

y cuántas filas tiene cada tabla, para compararlo con los números de antes de la copia.

Para cada una de las seis tablas de la tienda muestra su nombre (columna table_name) y su número de filas (columna row_count).

SQLbase de datos: tienda

SELECT 'customers' AS table_name, COUNT(*) AS row_count FROM customers;

SELECT 'customers' AS table_name, COUNT(*) AS row_count FROM customers;

Comprobar▶ Ejecutar

Ejercicio

Recupera lo borrado sin perder lo nuevo

La copia de anoche se ha restaurado en la tabla reviews_2026_10_24. Esta mañana un script borró reseñas de reviews y después un cliente escribió una reseña nueva. Recupera las reseñas borradas desde la copia sin perder la nueva.

SQLbase de datos: tienda

SELECT * FROM reviews_2026_10_24 WHERE id NOT IN (SELECT id FROM reviews);

SELECT * FROM reviews_2026_10_24 WHERE id NOT IN (SELECT id FROM reviews);

Comprobar▶ Ejecutar

La idea

Tantos privilegios como haga falta

SQLite no tiene usuarios: quien puede abrir el archivo puede hacerlo todo. Las bases de datos de servidor sí los tienen, y el sitio entra con su propio usuario, que solo puede hacer lo que necesita:

CREATE ROLE shop_app LOGIN PASSWORD '…';
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO shop_app;

Sin DROP, sin ALTER y sin acceso a las demás bases de datos del servidor. Si alguien entra en el sitio, puede hacer tanto como shop_app. Las migraciones se ejecutan con otro usuario, dueño de las tablas, y las dos contraseñas quedan fuera del código, como en la primera lección del módulo.

Test

Ponte a prueba

1. El sitio abre una conexión nueva a PostgreSQL para cada página. ¿Qué pasa en una hora punta?

La base de datos crea un pool por sí solaNada, la base de datos es rápidaCada página paga por abrir una, y más allá de max_connections la base de datos rechaza conexiones nuevas

2. El pool tiene 10 conexiones. Una función sale antes de tiempo sin devolver su conexión. ¿Qué pasa?

Nada, si la consulta era un SELECTEl pool la recupera sola después de la consultaCada camino así pierde una conexión, hasta que el pool se vacía

3. ¿Cuándo sabes que una copia de seguridad funciona?

Cuando el comando de copia no dio ningún errorCuando el archivo pesa más de ceroCuando has restaurado la base de datos desde ella y los datos están

4. ¿Qué privilegios necesita el usuario con el que entra el sitio?

Solo SELECTSELECT, INSERT, UPDATE y DELETE sobre sus tablas, sin DROP y sin cambiar el esquemaTodos, para que ninguna consulta falle

Comprobar

Empezar la lección de nuevo

Lecciones relacionadas

• Se basa en: DELETE: borrar filas · Conectarse a la base de datos desde el código · Inyección SQL y parámetros · Migraciones del esquema
• Ejercicios sobre el tema: La base de datos en el programa