ACID, Partial Indexes y Patterns Avanzados en PostgreSQL

Propiedades ACID

ACID es el conjunto de propiedades que garantizan la confiabilidad de las transacciones en bases de datos relacionales. PostgreSQL las implementa de forma rigurosa, y entender cada una es fundamental para tomar buenas decisiones de modelado y arquitectura.

Atomicidad

Una transacción es indivisible: o todas las operaciones dentro de ella se ejecutan con éxito, o ninguna se aplica. Si cualquier paso falla, la base de datos hace rollback automáticamente de todo lo ejecutado hasta ese punto.

BEGIN;
  UPDATE cuentas SET saldo = saldo - 500 WHERE id = 1;
  UPDATE cuentas SET saldo = saldo + 500 WHERE id = 2;
COMMIT;

Si la segunda UPDATE falla, la primera también se revierte. El saldo nunca queda en un estado inconsistente.

Consistencia

Después de cada transacción, la base de datos debe permanecer en un estado válido. Esto significa que constraints, foreign keys, check constraints y triggers se respetan. Una transacción que violaría la integridad referencial es rechazada.

INSERT INTO orders (user_id, total) VALUES (9999, 150.00);
-- ERROR: insert or update on table "orders" violates foreign key constraint

Aislamiento

Las transacciones concurrentes no interfieren entre sí. PostgreSQL usa MVCC (Multi-Version Concurrency Control) para permitir que múltiples transacciones lean y escriban simultáneamente sin bloqueos innecesarios. El nivel de aislamiento por defecto es READ COMMITTED, pero se pueden configurar niveles más restrictivos.

Nivel	Dirty Read	Non-Repeatable Read	Phantom Read
Read Committed	No	Posible	Posible
Repeatable Read	No	No	Posible
Serializable	No	No	No

BEGIN ISOLATION LEVEL SERIALIZABLE;
  SELECT saldo FROM cuentas WHERE id = 1 FOR UPDATE;
  UPDATE cuentas SET saldo = saldo - 100 WHERE id = 1;
COMMIT;

Durabilidad

Una vez que la transacción recibe COMMIT, los datos quedan persistidos de forma permanente — incluso si el servidor cae inmediatamente después. PostgreSQL lo garantiza a través del WAL (Write-Ahead Log), que registra las modificaciones en disco antes de confirmar la transacción.

---

Partial Indexes

Un partial index cubre solo un subconjunto de las filas de una tabla, definido por una cláusula WHERE. El resultado es un índice más pequeño, más rápido y más eficiente en disco.

Unique constraint con soft delete

En aplicaciones que usan soft delete (ej: la gema Discard en Rails), los registros “descartados” reciben un timestamp en discarded_at. Un índice único tradicional incluiría esos registros, impidiendo la reutilización de valores como número de identificación o email.

CREATE UNIQUE INDEX index_employees_on_tax_id
  ON employees USING btree (tax_id)
  WHERE (discarded_at IS NULL);

Con esto, dos registros pueden tener el mismo número de identificación siempre que solo uno de ellos esté activo. Los registros descartados no ocupan espacio en el índice y no interfieren con la constraint de unicidad.

En Rails:

class AddUniqueIndexOnTaxIdToEmployees < ActiveRecord::Migration[7.1]
  def change
    add_index :employees, :tax_id,
      unique: true,
      where: "discarded_at IS NULL",
      name: "index_employees_on_tax_id"
  end
end

Cuándo usar partial indexes

• Tablas con soft delete donde la mayoría de los registros están activos
• Columnas con distribución desigual de valores (ej: solo el 5% de los pedidos tienen status pending)
• Queries que siempre filtran por el mismo predicado

CREATE INDEX idx_orders_pending ON orders(created_at)
  WHERE status = 'pending';

Si la mayoría de las queries buscan pedidos pendientes y estos representan una fracción pequeña de la tabla, este índice es mucho más eficiente que un índice completo.

---

Check Constraints

Las check constraints validan datos a nivel de base de datos, independientemente de la aplicación. A diferencia de las validaciones en código, no pueden ser evitadas — cualquier operación que viole la regla es rechazada.

ALTER TABLE products
  ADD CONSTRAINT chk_price_positive CHECK (price > 0);

ALTER TABLE orders
  ADD CONSTRAINT chk_status_valid
  CHECK (status IN ('pending', 'confirmed', 'shipped', 'delivered', 'cancelled'));

Los intentos de insertar datos inválidos fallan inmediatamente:

INSERT INTO products (name, price) VALUES ('Widget', -10);
-- ERROR: new row for relation "products" violates check constraint "chk_price_positive"

Las check constraints son especialmente útiles para:

• Garantizar que valores numéricos estén dentro de rangos válidos
• Restringir columnas de status a valores permitidos
• Validar relaciones entre columnas de la misma fila (ej: end_date > start_date)

ALTER TABLE events
  ADD CONSTRAINT chk_dates_valid CHECK (end_date > start_date);

---

Exclusion Constraints

Las exclusion constraints son una generalización de las unique constraints. Mientras un índice único impide valores duplicados exactos, una exclusion constraint impide que filas entren en conflicto según un operador arbitrario.

El caso de uso clásico es impedir la superposición de intervalos — por ejemplo, reservas de salas que no deben solaparse en el tiempo.

CREATE EXTENSION IF NOT EXISTS btree_gist;

ALTER TABLE reservations
  ADD CONSTRAINT no_overlapping_reservations
  EXCLUDE USING gist (
    room_id WITH =,
    tsrange(start_time, end_time) WITH &&
  );

El operador && verifica superposición de rangos. Si alguien intenta reservar la sala 3 de 14h a 16h cuando ya existe una reserva de 15h a 17h, la base de datos lo rechaza:

INSERT INTO reservations (room_id, start_time, end_time)
  VALUES (3, '2026-03-24 14:00', '2026-03-24 16:00');
-- ERROR: conflicting key value violates exclusion constraint "no_overlapping_reservations"

Esta validación es imposible de implementar de forma confiable solo en el código de la aplicación, porque las condiciones de carrera entre transacciones concurrentes pueden crear superposiciones.

---

Connection Pooling y Timeouts

Cada conexión en PostgreSQL es un proceso del sistema operativo que consume típicamente 5-10 MB de memoria. Con cientos de workers, el número de conexiones puede superar fácilmente el límite de la base de datos.

Connection Pooling

Un connection pooler como PgBouncer actúa como middleware entre la aplicación y PostgreSQL, manteniendo un pool más pequeño de conexiones del lado del servidor.

[Rails Worker 1] ---\
[Rails Worker 2] -----> [PgBouncer] ----> [PostgreSQL]
[Rails Worker 3] ---/     (20 server      (max_connections = 100)
  (100 client              connections)
   connections)

El modo transaction pooling generalmente ofrece el mejor equilibrio: las conexiones del servidor se comparten entre clientes y se asignan solo durante una transacción.

Timeouts para disponibilidad

Los timeouts evitan que queries atascadas consuman conexiones indefinidamente. Se configuran en PostgreSQL o en la aplicación:

-- Timeout global para statements (en milisegundos)
SET statement_timeout = '5s';

-- Timeout para adquisición de locks
SET lock_timeout = '3s';

-- Timeout para conexiones idle en transacción
SET idle_in_transaction_session_timeout = '30s';

En Rails, se configura via database.yml:

production:
  variables:
    statement_timeout: 5000
    lock_timeout: 3000

La combinación de connection pooling con timeouts agresivos es lo que mantiene las aplicaciones estables bajo alta concurrencia.

---

Referencias

• PostgreSQL - CREATE INDEX
• PostgreSQL - Transaction Isolation
• PostgreSQL - Check Constraints
• PostgreSQL - Exclusion Constraints
• PgBouncer - Lightweight Connection Pooler
• High Performance PostgreSQL for Rails - Andrew Atkinson
• Discard Gem - Soft Deletes for ActiveRecord