Skip to content

PostgreSQL Hoja de referencia

Potente sistema de bases de datos relacionales de código abierto con características avanzadas, extensibilidad y cumplimiento SQL.

01

Primeros pasos

Conectar con psql

psql es el cliente de línea de comandos de PostgreSQL. Los meta-comandos empiezan con barra invertida (\). Usa \l para listar bases de datos, \d para describir tablas y \? para ayuda. Las cadenas de conexión (postgresql://) son cómodas y las soportan la mayoría de drivers. Usa \i nombre.sql para correr un archivo SQL. Fja la variable de entorno PGPASSWORD o el archivo ~/.pgpass para no teclear contraseñas.

postgresql
# connect to a database
psql -h localhost -p 5432 -U postgres -d mydb
# -h host, -p port, -U user, -d database

# connect via connection string
psql "postgresql://user:pass@localhost:5432/mydb"

# common psql meta-commands
\l              # list databases
\c mydb         # connect to mydb
\dt             # list tables
\d users        # describe table
\dn             # list schemas
\df             # list functions
\du             # list roles/users
\q              # quit
\?              # help on meta-commands
\timing         # toggle query timing

Crear y eliminar bases de datos

CREATE DATABASE crea una base de datos nueva; puedes especificar propietario, codificación, locale y plantilla. template0 es una plantilla limpia sin datos específicos de locale; template1 (por defecto) puede personalizarse. DROP DATABASE exige que no haya conexiones activas — WITH (FORCE) (13+) las desconecta primero. No puedes eliminar la base de datos a la que estás conectado. ALTER DATABASE RENAME actualiza el nombre pero no el directorio en disco.

postgresql
# create a database
CREATE DATABASE mydb
  WITH OWNER alice
  ENCODING 'UTF8'
  LC_COLLATE 'en_US.UTF-8'
  LC_CTYPE 'en_US.UTF-8'
  TEMPLATE template0
  CONNECTION LIMIT 100;

# create from a template (clone)
CREATE DATABASE mydb_copy TEMPLATE mydb;

# drop a database (must disconnect first)
DROP DATABASE IF EXISTS mydb;

# drop with force (PostgreSQL 13+)
DROP DATABASE IF EXISTS mydb WITH (FORCE);

# rename a database
ALTER DATABASE mydb RENAME TO newdb;

Esquemas y search_path

Los esquemas son espacios de nombres para los objetos de la base de datos — como carpetas para tablas. El esquema por defecto es 'public'. search_path determina qué esquemas se buscan al referenciar nombres no cualificados. Usa esquemas para organizar apps multi-inquilino, separar preocupaciones o gestionar versiones. CASCADE elimina los objetos dependientes; RESTRICT (por defecto) se niega si existen objetos. Los esquemas son más flexibles que las bases de datos para multi-tenancy dentro de una sola conexión.

postgresql
# create a schema
CREATE SCHEMA IF NOT EXISTS app_schema AUTHORIZATION alice;

# set the search path (schema lookup order)
SET search_path TO app_schema, public;
SHOW search_path;

# create a table in a specific schema
CREATE TABLE app_schema.users (id serial PRIMARY KEY);

# move a table between schemas
ALTER TABLE public.users SET SCHEMA app_schema;

# list objects in a schema
SELECT * FROM information_schema.tables
WHERE table_schema = 'app_schema';

# drop a schema (and its objects)
DROP SCHEMA IF EXISTS app_schema CASCADE;

Configuración y ajustes

Los ajustes de PostgreSQL viven en postgresql.conf. SHOW lee el valor actual; SET lo cambia para la sesión; SET LOCAL solo para la transacción actual; ALTER DATABASE/ROLE fija valores por defecto que persisten. Algunos ajustes (shared_buffers, max_connections) exigen reiniciar el servidor; otros (work_mem, statement_timeout) se aplican al instante. Usa pg_reload_conf() para aplicar los cambios del archivo de configuración sin reiniciar. Monitorea log_min_duration_statement para las consultas lentas.

postgresql
# view a setting
SHOW shared_buffers;
SHOW max_connections;

# set a parameter for the session
SET work_mem = '64MB';
SET statement_timeout = '30s';

# set a parameter for a transaction
BEGIN;
SET LOCAL work_mem = '256MB';
-- heavy sort here
COMMIT;  -- reverts to previous value

# set at database or role level (persists)
ALTER DATABASE mydb SET log_min_duration_statement = 100;
ALTER ROLE alice SET search_path TO app_schema, public;

# reload config without restart
SELECT pg_reload_conf();

# view config file location
SHOW config_file;

Scripting y variables de psql

psql soporta scripting con variables (\set), inclusión de archivos (\i), redirección de salida (\o) y ejecución de shell (\!). Las variables se sustituyen con :nombrevar. Usa \set para bucles y condicionales de scripting. El flag -f corre un archivo y sale, útil para trabajos por lotes y migraciones. Para scripting complejo, considera funciones PL/pgSQL o herramientas externas como sqitch o Flyway para gestionar migraciones.

postgresql
# run a SQL file
psql -d mydb -f script.sql
psql -d mydb < script.sql

# inside psql
\i /path/to/script.sql

# use psql variables
\set table_name 'users'
SELECT * FROM :table_name;

# prompt for input
\echo -n 'Enter user ID: ' \set user_id `read x && echo $x`
SELECT * FROM users WHERE id = :user_id;

# output to file
\o results.txt
SELECT * FROM users;
\o

# execute shell command
\! date
02

Tablas y restricciones

Crear tablas

serial auto-crea una secuencia para enteros autoincrementales; usa GENERATED ALWAYS AS IDENTITY (estándar SQL, preferido desde PG10) en su lugar para tablas nuevas. Restricciones: PRIMARY KEY (única + no nula), UNIQUE, NOT NULL, CHECK, REFERENCES (clave foránea). ON DELETE CASCADE elimina las filas dependientes cuando se elimina el padre; SET NULL pone la FK a NULL. Las tablas TEMP desaparecen tras la sesión — genial para resultados intermedios.

postgresql
CREATE TABLE users (
  id          serial PRIMARY KEY,
  username    varchar(50) UNIQUE NOT NULL,
  email       varchar(255) UNIQUE NOT NULL,
  password    text NOT NULL,
  age         integer CHECK (age >= 0 AND age <= 150),
  role        varchar(20) NOT NULL DEFAULT 'user',
  created_at  timestamptz NOT NULL DEFAULT now(),
  updated_at  timestamptz NOT NULL DEFAULT now()
);

# create with a foreign key
CREATE TABLE posts (
  id          serial PRIMARY KEY,
  user_id     integer NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  title       text NOT NULL,
  body        text,
  published   boolean DEFAULT false
);

# create a temporary table (session-scoped)
CREATE TEMP TABLE temp_stats AS
  SELECT user_id, count(*) FROM posts GROUP BY user_id;

Columnas de identidad (PG10+)

GENERATED ALWAYS AS IDENTITY es el reemplazo estándar SQL de serial — ata la secuencia a la columna, así que eliminar la columna elimina la secuencia (serial deja secuencias huérfanas). ALWAYS impide los inserts manuales del ID (usa OVERRIDING SYSTEM VALUE para forzar). BY DEFAULT se comporta como serial (permite insertar manualmente). Usa columnas de identidad para tablas nuevas; serial está bien para las existentes. Ambas usan secuencias por debajo.

postgresql
# preferred over serial (SQL standard)
CREATE TABLE products (
  id    integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name  text NOT NULL
);

# allow manual override (like serial behavior)
CREATE TABLE products (
  id    integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name  text NOT NULL
);

# restart a sequence
ALTER TABLE products ALTER COLUMN id RESTART WITH 1000;

# view identity info
SELECT * FROM pg_sequences WHERE sequencename = 'products_id_seq';

Alterar tablas

ALTER TABLE es cómo evolucionas el esquema sin recrear tablas. Añadir una columna con un valor por defecto (no volátil) es rápido desde PG11 (sin reescritura completa de tabla). Cambiar el tipo de columna puede exigir una reescritura completa y una cláusula USING para la conversión. CASCADE elimina los objetos dependientes (vistas, claves foráneas) — úsalo con cautela. Añade restricciones con nombre para gestionarlas mejor en el futuro. Usa IF EXISTS/IF NOT EXISTS para hacer los scripts idempotentes.

postgresql
# add a column
ALTER TABLE users ADD COLUMN bio text;
ALTER TABLE users ADD COLUMN score numeric DEFAULT 0;

# drop a column
ALTER TABLE users DROP COLUMN IF EXISTS bio;
ALTER TABLE users DROP COLUMN bio CASCADE;  -- drop dependent objects

# rename a column
ALTER TABLE users RENAME COLUMN username TO handle;

# change column type
ALTER TABLE users ALTER COLUMN age TYPE smallint USING age::smallint;

# set/drop default
ALTER TABLE users ALTER COLUMN role SET DEFAULT 'member';
ALTER TABLE users ALTER COLUMN role DROP DEFAULT;

# add constraint
ALTER TABLE users ADD CONSTRAINT email_check
  CHECK (email ~ '^[^@]+@[^@]+\.[^@]+$');

Restricciones

Las restricciones imponen la integridad de datos a nivel de base de datos — prefíeralas siempre a las comprobaciones de la aplicación. Las claves compuestas funcionan para tablas de unión. ON UPDATE CASCADE propaga los cambios de PK a las FKs; ON DELETE SET NULL pone la FK a nulo (la columna FK debe ser nullable). Las restricciones DEFERRABLE comprueban al momento del commit (no por sentencia), útiles para referencias circulares. Nombra tus restricciones para gestionarlas fácilmente — los nombres autogenerados son feos y varían entre bases de datos.

postgresql
# named constraint for easy management
ALTER TABLE users
  ADD CONSTRAINT positive_age CHECK (age >= 0);

# composite primary key
CREATE TABLE enrollments (
  student_id integer REFERENCES students(id),
  course_id  integer REFERENCES courses(id),
  PRIMARY KEY (student_id, course_id)
);

# unique constraint on multiple columns
ALTER TABLE users ADD CONSTRAINT unique_email_domain
  UNIQUE (email, domain);

# foreign key with actions
ALTER TABLE posts
  ADD FOREIGN KEY (user_id) REFERENCES users(id)
  ON UPDATE CASCADE ON DELETE SET NULL;

# drop a constraint
ALTER TABLE users DROP CONSTRAINT positive_age;

# defer a constraint until commit
SET CONSTRAINTS unique_email_domain DEFERRED;

Herencia de tablas

La herencia de tablas de PostgreSQL es única — las tablas hijas heredan las columnas de los padres. Consultar al padre devuelve filas de cada tabla hija salvo que uses ONLY. Es útil para la partición de tablas (pre-partición declarativa) y para modelar taxonomías. Sin embargo, la herencia tiene peculiaridades: las claves foráneas no se propagan y las restricciones UNIQUE solo aplican por tabla. Para necesidades de partición nuevas, usa la partición declarativa (PARTITION BY) en su lugar — es más robusta y mejor soportada.

postgresql
# PostgreSQL supports table inheritance
CREATE TABLE people (
  id   serial PRIMARY KEY,
  name text NOT NULL
);

CREATE TABLE employees (
  salary numeric,
  dept   text
) INHERITS (people);

CREATE TABLE customers (
  credit_limit numeric
) INHERITS (people);

# query parent sees all children
SELECT * FROM people;          -- includes employees and customers
SELECT * FROM ONLY people;     -- only people, not children

# insert into a child
INSERT INTO employees (name, salary, dept)
  VALUES ('Alice', 50000, 'Engineering');
03

Tipos de datos

Tipos numéricos

Usa tipos enteros dimensionados a los datos — smallint para rangos pequeños, bigint para grandes. numeric/decimal es exacto (esencial para dinero) pero más lento que real/double. Nunca uses float para dinero — los errores de redondeo se acumulan. serial es herencia; prefiere GENERATED AS IDENTITY. El tipo money es consciente del locale e inflexible — usa numeric(10,2) en su lugar. Para el autoincremento con huecos, las secuencias pueden saltarse números ante rollback/fallo (por diseño, por rendimiento).

postgresql
# integer types (use the smallest that fits)
smallint    -- 2 bytes, -32768 to 32767
integer     -- 4 bytes, -2B to 2B
bigint      -- 8 bytes, very large

# auto-incrementing (use identity, not serial)
id integer GENERATED ALWAYS AS IDENTITY

# decimal/numeric (exact)
numeric(precision, scale)   -- e.g. numeric(10,2) for currency
decimal                     -- alias for numeric

# floating point (inexact, faster)
real        -- 4 bytes, 6 decimal digits
double precision  -- 8 bytes, 15 decimal digits

# special
serial / bigserial   -- legacy auto-increment (prefer identity)
money                -- currency (prefer numeric)

# examples
CREATE TABLE products (
  price numeric(10,2),        -- $99999999.99
  weight real,                -- 1.5 (kg)
  quantity integer DEFAULT 0
);

Tipos de texto y caracteres

En PostgreSQL, varchar, char y text tienen idéntico rendimiento — text es el preferido para longitud ilimitada, varchar(n) cuando necesitas una restricción de longitud. char(n) rellena con espacios y rara vez es útil. Cambiar la longitud de varchar es una operación solo de metadatos (rápida). LIKE usa los comodines % y _; ILIKE no distingue mayúsculas; ~ es regex POSIX. Para búsqueda de texto completo, usa tsvector/tsquery (cubierta después). Guarda siempre el texto como text/varchar, no como bytea.

postgresql
# three text types (all variable-length)
varchar(n)   -- varchar with max length
char(n)      -- fixed-length, padded with spaces
text         -- unlimited length (preferred)

# in practice, all three perform the same
# varchar(n) adds a length check; char(n) pads (rarely useful)

CREATE TABLE articles (
  title    varchar(200) NOT NULL,
  slug     varchar(100) UNIQUE,
  body     text,
  summary  text DEFAULT ''
);

# string functions
SELECT length(body), char_length(title), octet_length(title)
FROM articles;

# change length limit (fast, no rewrite)
ALTER TABLE articles ALTER COLUMN title TYPE varchar(500);

# pattern matching
SELECT * FROM articles WHERE title LIKE '%postgres%';
SELECT * FROM articles WHERE title ILIKE '%postgres%'; -- case-insensitive
SELECT * FROM articles WHERE slug ~ '^[a-z0-9-]+$';   -- regex

Tipos de fecha y hora

Usa siempre timestamptz (no timestamp) para las marcas de tiempo — guarda en UTC y convierte a la zona horaria de la sesión al mostrar. timestamp (sin tz) es ingenuo y causa bugs en apps de varias zonas horarias. interval es potente para la aritmética ('1 day', '2 hours', '3 months'). date_trunc redondea hacia abajo a una unidad. Las cadenas de formato de to_char/to_timestamp son flexibles. Fja la zona horaria por sesión o rol — los datos se guardan en UTC sin importar nada.

postgresql
# date/time types
date              -- date only (4 bytes)
time              -- time only (8 bytes)
timetz            -- time with time zone (12 bytes)
timestamp         -- timestamp without tz (8 bytes)
timestamptz       -- timestamp WITH time zone (8 bytes, preferred)
interval          -- time span (16 bytes)

# always use timestamptz for timestamps
CREATE TABLE events (
  id         serial PRIMARY KEY,
  created_at timestamptz DEFAULT now(),
  scheduled  timestamptz,
  duration   interval
);

# common operations
SELECT now();                               -- current timestamp
SELECT current_date;                         -- today
SELECT created_at + interval '1 hour';       -- add time
SELECT age('2025-01-01', '2024-01-01');       -- interval between
SELECT date_trunc('month', created_at);       -- truncate to month
SELECT to_char(created_at, 'YYYY-MM-DD HH:MI:SS');
SELECT to_timestamp('2025/01/15', 'YYYY/MM/DD');
SELECT EXTRACT(YEAR FROM created_at);

# timezone handling
SET timezone = 'UTC';
SET timezone = 'Asia/Shanghai';

JSON y JSONB

jsonb es JSON binario — más rápido de consultar e indexable, pero ligeramente más lento de insertar y no conserva el orden de las claves. Usa jsonb para cualquier trabajo nuevo. -> devuelve jsonb, ->> devuelve text. @> (contención) es el operador JSON más potente y funciona genial con índices GIN. jsonb_set modifica una ruta, || fusiona objetos, - elimina claves. Para consultas frecuentes sobre un campo JSON, añade un índice GIN o un índice de expresión sobre el valor extraído. JSONB es ideal para esquemas flexibles dentro de una base de datos relacional.

postgresql
# json stores text; jsonb stores binary (faster, preferred)
CREATE TABLE products (
  id    serial PRIMARY KEY,
  data  jsonb
);

# insert JSON
INSERT INTO products (data) VALUES
  ('{"name": "Widget", "price": 19.99, "tags": ["sale", "new"]}');

# query JSON fields
SELECT data->'name'        FROM products;  -- returns jsonb
SELECT data->>'name'       FROM products;  -- returns text
SELECT data->'tags'->0     FROM products;  -- first tag (jsonb)

# filter by JSON
SELECT * FROM products WHERE data->>'name' = 'Widget';
SELECT * FROM products WHERE data @> '{"tags": ["sale"]}';  -- containment

# modify JSON
UPDATE products SET data = jsonb_set(data, '{price}', '29.99');
UPDATE products SET data = data || '{"sale": true}';        -- merge
UPDATE products SET data = data - 'sale';                    -- remove key

# index JSON (GIN index)
CREATE INDEX idx_products_data ON products USING GIN (data);
CREATE INDEX idx_products_name ON products ((data->>'name'));

Arrays y tipos personalizados

Los arrays de PostgreSQL se indexan desde 1 (a diferencia de la mayoría de lenguajes). ANY y @> son los principales operadores de consulta; unnest() expande arrays a filas (genial para joins). Los arrays son potentes pero úsalos con criterio — si consultas/filtrás frecuentemente elementos del array, una tabla normalizada puede ser mejor. Los enums se ordenan y se validan al insertar. Los tipos compuestos permiten columnas estructuradas. Para datos anidados complejos, considera JSONB en lugar de arrays de compuestos.

postgresql
# array columns
CREATE TABLE teams (
  id      serial PRIMARY KEY,
  name    text,
  members text[]           -- array of text
);

INSERT INTO teams (name, members) VALUES
  ('Engineering', ARRAY['Alice', 'Bob', 'Carol']),
  ('Sales', '{"Dave", "Eve"}');

# query arrays
SELECT * FROM teams WHERE 'Alice' = ANY(members);
SELECT * FROM teams WHERE members @> ARRAY['Alice'];  -- contains
SELECT members[1] FROM teams;            -- 1-indexed!
SELECT array_length(members, 1) FROM teams;
SELECT unnest(members) FROM teams WHERE id = 1;  -- expand to rows

# custom enum type
CREATE TYPE mood AS ENUM ('happy', 'sad', 'neutral');
CREATE TABLE persons (id serial PRIMARY KEY, current_mood mood);
INSERT INTO persons (current_mood) VALUES ('happy');

# composite type
CREATE TYPE address AS (street text, city text, zip text);
CREATE TABLE contacts (id serial, addr address);
04

Operaciones CRUD

INSERT

RETURNING es una característica de PostgreSQL que devuelve las filas insertadas/actualizadas/eliminadas — elimina la necesidad de un SELECT aparte tras el insert. ON CONFLICT (UPSERT) es potente: DO UPDATE usa EXCLUDED para referenciar la fila propuesta, DO NOTHING la salta silenciosamente. El insert de varias filas es mucho más rápido que los inserts individuales. INSERT ... SELECT copia datos entre tablas. Para cargas masivas, usa COPY (aún más rápido) — se salta la sobrecarga del parseo SQL.

postgresql
# basic insert
INSERT INTO users (username, email, age)
VALUES ('alice', '[email protected]', 30);

# multi-row insert
INSERT INTO users (username, email) VALUES
  ('bob', '[email protected]'),
  ('carol', '[email protected]'),
  ('dave', '[email protected]');

# insert with RETURNING (get auto-generated values)
INSERT INTO users (username, email)
VALUES ('eve', '[email protected]')
RETURNING id, username, created_at;

# insert from a query (INSERT ... SELECT)
INSERT INTO archive_users (username, email)
SELECT username, email FROM users WHERE active = false;

# upsert (insert or update on conflict)
INSERT INTO users (username, email) VALUES ('alice', '[email protected]')
ON CONFLICT (username)
DO UPDATE SET email = EXCLUDED.email
RETURNING id;

# do nothing on conflict
INSERT INTO users (username, email) VALUES ('alice', '[email protected]')
ON CONFLICT (username) DO NOTHING;

UPDATE

Incluye siempre WHERE salvo que pretendas actualizar todas las filas. UPDATE ... FROM permite joins en las actualizaciones (no estándar pero potente). RETURNING muestra qué cambió — esencial para auditar. Las expresiones CASE habilitan actualizaciones condicionales en una sola sentencia. Las actualizaciones que no cambian ningún valor son no-ops (sin WAL, sin disparo de trigger a nivel de sentencia). Las actualizaciones grandes pueden inflar la tabla — lotifícalas y haz VACUUM después.

postgresql
# basic update
UPDATE users SET email = '[email protected]' WHERE id = 1;

# update multiple columns
UPDATE users
SET email = '[email protected]', age = 31, updated_at = now()
WHERE id = 1;

# update with RETURNING
UPDATE users SET role = 'admin' WHERE id = 1
RETURNING id, username, role;

# update from another table
UPDATE posts p
SET title = p.title || ' (archived)'
FROM users u
WHERE p.user_id = u.id AND u.role = 'admin';

# conditional update with CASE
UPDATE products
SET price = CASE
  WHEN price < 10 THEN price * 1.2
  WHEN price < 50 THEN price * 1.1
  ELSE price
END;

# update all rows (be careful!)
UPDATE users SET status = 'active';

DELETE y TRUNCATE

DELETE elimina filas una por una (dispara triggers, visible en transacciones). TRUNCATE elimina todas las filas al instante (DDL, no transaccional de la misma forma, reinicia las secuencias con RESTART IDENTITY). Usa TRUNCATE para vaciar tablas enteras — es órdenes de magnitud más rápido. CASCADE trunca las tablas con claves foráneas que referencian a esta. DELETE con RETURNING es útil para logs de auditoría. Usa siempre WHERE con DELETE — sin él, lo eliminas por completo (con seguridad dentro de una transacción, catastrófico si se confirma).

postgresql
# basic delete
DELETE FROM users WHERE id = 1;

# delete with RETURNING
DELETE FROM users WHERE active = false
RETURNING id, username;

# delete based on a join
DELETE FROM posts
WHERE user_id IN (
  SELECT id FROM users WHERE role = 'deleted'
);

# delete using USING (join syntax)
DELETE FROM posts p
USING users u
WHERE p.user_id = u.id AND u.status = 'deleted';

# truncate (fast, resets sequences, DDL-like)
TRUNCATE TABLE posts;
TRUNCATE TABLE posts, comments;          -- multiple tables
TRUNCATE TABLE posts RESTART IDENTITY;   -- reset sequences
TRUNCATE TABLE posts CASCADE;            -- also truncates FK refs

# delete all rows (slower than TRUNCATE, but transactional)
DELETE FROM posts;

Fundamentos de SELECT

DISTINCT ON es una extensión de PostgreSQL que devuelve la primera fila por grupo — potente para consultas de 'último por categoría' (debe ORDER BY la columna del DISTINCT ON primero). LIMIT/OFFSET es paginación simple pero lenta para offsets grandes — usa paginación por keyset (WHERE id > last_id) por mejor rendimiento. Especifica siempre las columnas explícitamente en producción (no SELECT *) para evitar sorpresas cuando cambie el esquema. Usa FETCH FIRST n ROWS ONLY para la sintaxis estándar SQL.

postgresql
# basic select
SELECT id, username, email FROM users;

# filtering
SELECT * FROM users WHERE age >= 18 AND role = 'user';
SELECT * FROM users WHERE role IN ('admin', 'moderator');
SELECT * FROM users WHERE age BETWEEN 18 AND 65;
SELECT * FROM users WHERE email IS NOT NULL;
SELECT * FROM users WHERE username LIKE 'al%';

# sorting and limiting
SELECT * FROM users ORDER BY created_at DESC LIMIT 10;
SELECT * FROM users ORDER BY age DESC, username ASC
OFFSET 20 LIMIT 10;  -- pagination (or use FETCH)

# distinct
SELECT DISTINCT role FROM users;
SELECT DISTINCT ON (user_id) * FROM posts
ORDER BY user_id, created_at DESC;  -- latest post per user

# column aliases and expressions
SELECT
  username AS name,
  EXTRACT(YEAR FROM created_at) AS join_year,
  age * 365 AS age_in_days
FROM users;

MERGE (PG15+)

MERGE (estándar SQL, PG15+) es más potente que ON CONFLICT: puede insertar, actualizar Y eliminar en una sola sentencia según condiciones de coincidencia. Úsalo para operaciones de sincronización complejas (ETL, merges de almacenes de datos). Para upserts simples, ON CONFLICT es más conciso y ampliamente soportado. MERGE exige una fuente (tabla o consulta) y una condición de coincidencia. Las cláusulas WHEN MATCHED/NOT MATCHED definen las acciones. Cada rama puede tener condiciones AND opcionales para control fino.

postgresql
# MERGE: insert, update, or delete in one statement (PG15+)
MERGE INTO products p
USING new_prices n
ON p.sku = n.sku
WHEN MATCHED AND p.price != n.price THEN
  UPDATE SET price = n.price, updated_at = now()
WHEN MATCHED AND n.discontinued = true THEN
  DELETE
WHEN NOT MATCHED THEN
  INSERT (sku, name, price) VALUES (n.sku, n.name, n.price);

# simpler upsert (still works, often preferred)
INSERT INTO products (sku, name, price) VALUES ('A1', 'Widget', 9.99)
ON CONFLICT (sku)
DO UPDATE SET price = EXCLUDED.price;

# MERGE with a source query
MERGE INTO inventory i
USING (SELECT product_id, sum(qty) AS total FROM orders GROUP BY product_id) o
ON i.product_id = o.product_id
WHEN MATCHED THEN UPDATE SET quantity = i.quantity - o.total;
05

Consultar y filtrar

WHERE y condiciones

ILIKE es el LIKE que no distingue mayúsculas (extensión de PostgreSQL). ~ es regex POSIX (sensible a mayúsculas), ~* sin distinguirlas, !~ la negación. IS DISTINCT FROM trata NULL como un valor comparable (NULL = NULL es NULL, no verdadero). ANY/ALL funcionan con arrays y subconsultas. Para coincidencia de texto compleja, considera la búsqueda de texto completo (tsvector) o índices trigram (pg_trgm) por rendimiento. Indexa siempre las columnas usadas en las cláusulas WHERE para tablas grandes.

postgresql
# comparison operators
SELECT * FROM products WHERE price > 100;
SELECT * FROM products WHERE price <> 0;        -- not equal
SELECT * FROM products WHERE name IS DISTINCT FROM 'Test';

# logical operators
SELECT * FROM products
WHERE (price > 50 AND category = 'electronics')
   OR (price < 10 AND category = 'books');

# IN, BETWEEN, LIKE/ILIKE
SELECT * FROM products WHERE category IN ('books', 'toys');
SELECT * FROM products WHERE price BETWEEN 10 AND 50;
SELECT * FROM products WHERE name LIKE '_o%';     -- second char 'o'
SELECT * FROM products WHERE name ILIKE '%widget%';

# IS NULL / IS NOT NULL
SELECT * FROM products WHERE description IS NULL;
SELECT * FROM products WHERE description IS NOT DISTINCT FROM NULL;

# ANY/ALL with arrays
SELECT * FROM products WHERE id = ANY(ARRAY[1,2,3]);
SELECT * FROM products WHERE price > ALL(ARRAY[10, 20, 30]);

# regex matching
SELECT * FROM products WHERE name ~ '^[A-C]';       -- starts with A-C
SELECT * FROM products WHERE name ~* 'widget';       -- case-insensitive
SELECT * FROM products WHERE name !~ 'test';         -- does not match

ORDER BY y paginación

La paginación por keyset (WHERE id > last_id) es dramáticamente más rápida que OFFSET para datasets grandes — OFFSET escanea y descarta filas. ORDER BY random() es lento en tablas grandes; usa TABLESAMPLE para muestreo estadístico. NULLS FIRST/LAST controla dónde se ordenan los NULLs (el valor por defecto varía según ASC/DESC). Incluye siempre un ORDER BY al paginar — sin él, el orden de filas no está definido. Para paginación estable, ordena por una columna única.

postgresql
# basic sorting
SELECT * FROM users ORDER BY created_at DESC;
SELECT * FROM users ORDER BY last_name ASC, first_name ASC;

# sort by expression
SELECT * FROM products ORDER BY (price * 1.1) DESC;
SELECT * FROM events ORDER BY created_at::date;

# NULLS handling
SELECT * FROM users ORDER BY last_login DESC NULLS LAST;
SELECT * FROM users ORDER BY last_login DESC NULLS FIRST;

# LIMIT/OFFSET pagination (simple but slow for large offsets)
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 0;   -- page 1
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 10;  -- page 2

# keyset pagination (faster, use for large datasets)
SELECT * FROM users WHERE id > 100 ORDER BY id LIMIT 10;

# FETCH (SQL standard)
SELECT * FROM users ORDER BY id FETCH FIRST 10 ROWS ONLY;

# random rows
SELECT * FROM products ORDER BY random() LIMIT 5;  -- slow on large tables
SELECT * FROM products TABLESAMPLE BERNOULLI(1);    -- 1% sample

GROUP BY y agregados

GROUP BY colapsa filas por las columnas de agrupación; los agregados (count, sum, avg, min, max) computan por grupo. HAVING filtra grupos (WHERE filtra filas antes de agrupar). ROLLUP añade subtotales en cada nivel; CUBE añade todas las combinaciones; GROUPING SETS te permite especificar exactamente qué agrupaciones quieres — cada una es potente para reportes. count(*) cuenta filas; count(columna) cuenta valores no nulos. Usa count(DISTINCT col) para conteos únicos.

postgresql
# basic grouping
SELECT category, count(*) FROM products GROUP BY category;
SELECT user_id, sum(amount) FROM orders GROUP BY user_id;

# multiple aggregates
SELECT
  category,
  count(*)           AS total,
  avg(price)         AS avg_price,
  min(price)         AS min_price,
  max(price)         AS max_price,
  sum(price)         AS total_value
FROM products
GROUP BY category;

# HAVING (filter on aggregates, like WHERE for groups)
SELECT user_id, count(*) AS order_count
FROM orders
GROUP BY user_id
HAVING count(*) > 5;

# GROUP BY with multiple columns
SELECT category, status, count(*)
FROM products
GROUP BY category, status
ORDER BY category, status;

# GROUP BY ROLLUP/CUBE (subtotals)
SELECT category, status, count(*)
FROM products
GROUP BY ROLLUP (category, status);  -- adds subtotals and grand total

# GROUP BY GROUPING SETS
SELECT category, status, count(*)
FROM products
GROUP BY GROUPING SETS ((category, status), (category), ());

DISTINCT y operaciones de conjuntos

UNION combina resultados y elimina duplicados (lento); UNION ALL conserva los duplicados (rápido, preferido cuando sabes que no hay duplicados o no te importan). INTERSECT devuelve las filas comunes; EXCEPT la diferencia. Las operaciones de conjuntos exigen tipos y conteos de columnas compatibles. DISTINCT ON es una joya de PostgreSQL: devuelve la primera fila por grupo según el ORDER BY — perfecto para consultas de 'último/más caro por categoría'. Todas las operaciones de conjuntos pueden encadenarse pero usa paréntesis para controlar la precedencia.

postgresql
# distinct rows
SELECT DISTINCT category FROM products;
SELECT DISTINCT category, status FROM products;

# DISTINCT ON (first row per group)
SELECT DISTINCT ON (category) *
FROM products
ORDER BY category, price DESC;  -- most expensive per category

# UNION (combine, remove duplicates)
SELECT 'user' AS type, username FROM users
UNION
SELECT 'admin' AS type, username FROM admins;

# UNION ALL (faster, keeps duplicates)
SELECT username FROM active_users
UNION ALL
SELECT username FROM inactive_users;

# INTERSECT (common rows)
SELECT product_id FROM orders_2024
INTERSECT
SELECT product_id FROM orders_2025;

# EXCEPT (rows in first but not second)
SELECT product_id FROM all_products
EXCEPT
SELECT product_id FROM discontinued_products;

# set operations with ORDER BY (applies to the whole result)
SELECT username FROM users
UNION
SELECT username FROM admins
ORDER BY username;

CASE y lógica condicional

Las expresiones CASE añaden lógica if/then/else a SQL. El CASE simple empareja un valor; el CASE buscado evalúa condiciones. La cláusula FILTER (PostgreSQL) es más limpia que CASE dentro de los agregados para el conteo condicional. CASE puede aparecer en SELECT, WHERE, ORDER BY e incluso GROUP BY. Úsalo para pivotes, formato condicional y lógica de negocio que pertenezca a la base de datos. Para lógica multirrama compleja, considera una función o vista.

postgresql
# simple CASE
SELECT
  name,
  CASE category
    WHEN 'book' THEN 'Media'
    WHEN 'laptop' THEN 'Electronics'
    ELSE 'Other'
  END AS category_group
FROM products;

# searched CASE (more flexible)
SELECT
  name,
  CASE
    WHEN price < 10 THEN 'Cheap'
    WHEN price < 50 THEN 'Moderate'
    WHEN price < 100 THEN 'Expensive'
    ELSE 'Luxury'
  END AS price_tier
FROM products;

# CASE in aggregate (pivot-like)
SELECT
  count(*) AS total,
  count(*) FILTER (WHERE status = 'active') AS active_count,
  count(*) FILTER (WHERE status = 'inactive') AS inactive_count
FROM users;

# CASE for conditional sorting
SELECT * FROM products
ORDER BY
  CASE WHEN featured THEN 0 ELSE 1 END,
  name;
06

Joins

Joins INNER y OUTER

INNER JOIN devuelve solo las filas coincidentes. LEFT JOIN conserva todas las filas de la izquierda (columnas derechas NULL si no hay coincidencia) — el join más común para 'incluir datos relacionados si existen'. RIGHT JOIN se usa rara vez (reescríbelo como LEFT). FULL OUTER JOIN conserva ambos lados. El WHERE p.id IS NULL tras un LEFT JOIN es el patrón 'anti-join' para hallar filas sin coincidencias. Califica siempre los nombres de columna con alias de tabla (u.id, p.user_id) para evitar ambigüedad.

postgresql
# INNER JOIN (only matching rows)
SELECT u.username, p.title
FROM users u
INNER JOIN posts p ON u.id = p.user_id;

# LEFT JOIN (all left rows, matching right or NULL)
SELECT u.username, p.title
FROM users u
LEFT JOIN posts p ON u.id = p.user_id;

# RIGHT JOIN (all right rows, matching left or NULL)
SELECT u.username, p.title
FROM users u
RIGHT JOIN posts p ON u.id = p.user_id;

# FULL OUTER JOIN (all rows from both, NULLs where no match)
SELECT u.username, p.title
FROM users u
FULL OUTER JOIN posts p ON u.id = p.user_id;

# find rows with no match (anti-join pattern)
SELECT u.username
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
WHERE p.id IS NULL;  -- users with no posts

Joins multi-tabla

PostgreSQL puede unir muchas tablas, pero cada join añade coste — asegúrate de que los joins usen índices. Los self-joins son útiles para datos jerárquicos (pero considera CTEs recursivos para jerarquías profundas). Al unir con agregación, LEFT JOIN + count(p.id) (no count(*)) da 0 para los usuarios sin publicaciones. El optimizador elige el orden de joins, pero escribir consultas claras ayuda. Para consultas multi-tabla complejas, considera crear una vista.

postgresql
# join three tables
SELECT u.username, p.title, c.text AS comment
FROM users u
JOIN posts p ON u.id = p.user_id
JOIN comments c ON p.id = c.post_id;

# join with different conditions
SELECT
  u.username,
  p.title,
  l.name AS liked_by
FROM posts p
JOIN users u ON p.user_id = u.id
LEFT JOIN likes l ON p.id = l.post_id AND l.user_id != u.id;

# self-join (e.g., employees and their managers)
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

# join with aggregation
SELECT u.username, count(p.id) AS post_count
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.id, u.username
ORDER BY post_count DESC;

CROSS JOIN y LATERAL

CROSS JOIN produce un producto cartesiano (cada fila de A con cada fila de B) — rara vez lo que quieres, pero útil con generate_series para generar datos. LATERAL es potente: permite que una subconsulta referencie columnas de la consulta externa, como una función por fila. Es ideal para consultas de 'top N por grupo' (mucho más limpio que las funciones de ventana para casos simples). LEFT JOIN LATERAL ... ON true incluye las filas sin coincidencias.

postgresql
# CROSS JOIN (Cartesian product — every combination)
SELECT u.username, c.name
FROM users u
CROSS JOIN categories c;
-- same as: SELECT ... FROM users, categories;

# useful for generating combinations
SELECT generate_series(1, 5) AS n, c.name
FROM categories c
CROSS JOIN generate_series(1, 5);

# LATERAL (subquery can reference outer query)
SELECT u.username, recent.title
FROM users u
CROSS JOIN LATERAL (
  SELECT title FROM posts
  WHERE user_id = u.id
  ORDER BY created_at DESC
  LIMIT 3
) recent;

# LATERAL with LEFT JOIN (include users with no posts)
SELECT u.username, recent.title
FROM users u
LEFT JOIN LATERAL (
  SELECT title FROM posts WHERE user_id = u.id
  ORDER BY created_at DESC LIMIT 3
) recent ON true;

Estrategias de join (EXPLAIN)

PostgreSQL elige las estrategias de join según el tamaño de tabla, los índices y las estadísticas. Nested Loop es O(N*M) — bien para búsquedas pequeñas o indexadas. Hash Join construye una tabla hash sobre la entrada menor y luego sondea — excelente para joins grandes sin ordenar. Merge Join exige ambas entradas ordenadas — genial cuando los índices aportan el orden. No fuerces estrategias en producción; deja que el optimizador decida. Usa EXPLAIN (ANALYZE) para ver tiempos reales y ajustar índices/estadísticas si hace falta.

postgresql
# see how PostgreSQL joins tables
EXPLAIN SELECT * FROM users u JOIN posts p ON u.id = p.user_id;

# join strategies you'll see:
#   Nested Loop:   for each left row, scan right (good for small/indexed)
#   Hash Join:     hash one table, probe with other (good for large unsorted)
#   Merge Join:    both sorted, merge (good for sorted/indexed large tables)

# force a join strategy (for testing, not production)
SET enable_nestloop = off;
SET enable_hashjoin = off;
SET enable_mergejoin = off;

# check actual execution stats
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM users u JOIN posts p ON u.id = p.user_id;

# join removal: PostgreSQL can remove unnecessary joins
# (e.g., LEFT JOIN to a table only used for FK validation)
EXPLAIN SELECT u.* FROM users u LEFT JOIN posts p ON u.id = p.user_id;

Consejos de rendimiento de joins

La optimización de join de mayor impacto es indexar las columnas de claves foráneas — PostgreSQL NO crea automáticamente índices para las FKs. Mantén las estadísticas frescas con ANALYZE (el auto-vacuum lo hace, pero un ANALYZE manual tras cargas masivas ayuda). Evita envolver columnas de join en funciones (impide el uso del índice). Selecciona solo las columnas necesarias. Para tablas enormes, considera la partición para que el optimizador pueda podar particiones. Monitorea los joins lentos con pg_stat_statements.

postgresql
# always index foreign key columns
CREATE INDEX idx_posts_user_id ON posts(user_id);

# index join columns on both sides
CREATE INDEX idx_orders_product_id ON orders(product_id);
CREATE INDEX idx_products_id ON products(id);  -- PK already indexed

# composite index for multi-column joins
CREATE INDEX idx_posts_user_created ON posts(user_id, created_at);

# analyze tables for accurate statistics
ANALYZE users;
ANALYZE posts;

# avoid joining on expressions (prevents index use)
-- BAD:  JOIN ON lower(a.email) = lower(b.email)
-- GOOD: JOIN ON a.email = b.email (normalize case on insert)

# limit columns selected (reduces I/O)
SELECT u.username, p.title  -- not SELECT *
FROM users u JOIN posts p ON u.id = p.user_id;

# partition large tables for join pruning
SELECT * FROM orders o
JOIN products p ON o.product_id = p.id
WHERE o.order_date >= '2025-01-01';  -- prunes partitions
07

Agregaciones y funciones de ventana

Funciones de agregación

PostgreSQL tiene agregados ricos más allá de count/sum/avg. string_agg y array_agg concatenan valores en una cadena/array — genial para evitar consultas N+1. bool_or/bool_and agregan booleanos. percentile_cont computa percentiles exactos (mediana = 0.5) — muy útil para analítica. mode() devuelve el valor más frecuente. La cláusula FILTER (count(*) FILTER (WHERE...)) es más limpia que CASE para la agregación condicional. ORDER BY dentro de los agregados controla el orden de salida.

postgresql
# standard aggregates
SELECT
  count(*)              AS total_rows,
  count(distinct category) AS unique_categories,
  sum(price)            AS total_value,
  avg(price)            AS avg_price,
  min(price)            AS min_price,
  max(price)            AS max_price,
  stddev(price)         AS std_dev,
  variance(price)       AS variance
FROM products;

# string aggregation
SELECT
  user_id,
  string_agg(tag, ', ' ORDER BY tag) AS tags
FROM post_tags
GROUP BY user_id;

# array aggregation
SELECT
  user_id,
  array_agg(title ORDER BY created_at DESC) AS recent_titles
FROM posts
GROUP BY user_id;

# boolean aggregation
SELECT
  bool_or(published)    AS any_published,
  bool_and(published)   AS all_published
FROM posts;

# statistical aggregates
SELECT
  percentile_cont(0.5) WITHIN GROUP (ORDER BY price) AS median,
  percentile_cont(0.95) WITHIN GROUP (ORDER BY price) AS p95,
  mode() WITHIN GROUP (ORDER BY price) AS most_common
FROM products;

Funciones de ventana

Las funciones de ventana computan sobre un conjunto de filas relacionadas con la fila actual — no colapsan filas como GROUP BY. PARTITION BY define el grupo de ventana; ORDER BY define el orden dentro. ROW_NUMBER es siempre única; RANK salta números tras los empates; DENSE_RANK no salta. LAG/LEAD acceden a otras filas — perfectas para el análisis de series temporales. Las cláusulas de marco (ROWS BETWEEN ...) controlan qué filas se incluyen en la ventana. Las funciones de ventana se evalúan tras WHERE/GROUP BY/HAVING.

postgresql
# ROW_NUMBER: unique sequential number
SELECT
  title,
  price,
  ROW_NUMBER() OVER (ORDER BY price DESC) AS rank
FROM products;

# RANK and DENSE_RANK (handle ties)
SELECT
  title,
  category,
  price,
  RANK() OVER (PARTITION BY category ORDER BY price DESC) AS cat_rank,
  DENSE_RANK() OVER (PARTITION BY category ORDER BY price DESC) AS dense_rank
FROM products;

# LAG and LEAD (compare to previous/next row)
SELECT
  date,
  revenue,
  LAG(revenue, 1) OVER (ORDER BY date) AS prev_day,
  revenue - LAG(revenue, 1) OVER (ORDER BY date) AS daily_change,
  LEAD(revenue, 1) OVER (ORDER BY date) AS next_day
FROM daily_sales;

# running total
SELECT
  date,
  revenue,
  SUM(revenue) OVER (ORDER BY date) AS running_total,
  AVG(revenue) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg_7d
FROM daily_sales;

Marcos de ventana y NTILE

Los marcos de ventana controlan qué filas ve una función de ventana. El marco por defecto para agregados es RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW (suma acumulada). Para LAST_VALUE, debes extender el marco a UNBOUNDED FOLLOWING o devuelve la fila actual (el marco por defecto termina en la actual). NTILE divide las filas en N cubetas — útil para cuartiles/deciles. FIRST_VALUE/LAST_VALUE devuelven valores de las filas del borde del marco. Entender los marcos es la clave para dominar las funciones de ventana.

postgresql
# frame types
#   ROWS:   exact row count
#   RANGE:  logical range (e.g., same value)
#   GROUPS: groups of peer rows

SELECT
  date,
  revenue,
  # running sum from start to current
  SUM(revenue) OVER (ORDER BY date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative,

  # 7-day moving average
  AVG(revenue) OVER (ORDER BY date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg_7d,

  # sum of all rows (no frame = entire partition)
  SUM(revenue) OVER () AS grand_total
FROM daily_sales;

# NTILE: divide rows into N equal buckets
SELECT
  name,
  price,
  NTILE(4) OVER (ORDER BY price DESC) AS price_quartile
FROM products;

# FIRST_VALUE / LAST_VALUE
SELECT
  name,
  category,
  price,
  FIRST_VALUE(name) OVER (PARTITION BY category ORDER BY price DESC) AS most_expensive,
  LAST_VALUE(name) OVER (
    PARTITION BY category ORDER BY price DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS least_expensive
FROM products;

Cláusula FILTER

La cláusula FILTER es una extensión de PostgreSQL más limpia y a menudo más rápida que CASE dentro de los agregados. Es especialmente útil para reportes tipo pivote donde necesitas varios agregados condicionales. La consulta escanea la tabla una vez y computa la totalidad de los agregados en una sola pasada — mucho más eficiente que múltiples subconsultas. FILTER forma parte del estándar SQL (a diferencia de muchas extensiones de PG) y también lo soportan otras bases de datos.

postgresql
# FILTER (PostgreSQL extension, cleaner than CASE)
SELECT
  count(*) AS total,
  count(*) FILTER (WHERE status = 'active') AS active,
  count(*) FILTER (WHERE status = 'inactive') AS inactive,
  count(*) FILTER (WHERE status = 'banned') AS banned,
  sum(amount) FILTER (WHERE status = 'active') AS active_revenue
FROM orders
GROUP BY user_id;

# equivalent with CASE (more verbose)
SELECT
  count(*) AS total,
  count(CASE WHEN status = 'active' THEN 1 END) AS active,
  sum(CASE WHEN status = 'active' THEN amount ELSE 0 END) AS active_revenue
FROM orders
GROUP BY user_id;

# FILTER with multiple aggregates in one pass (efficient)
SELECT
  category,
  count(*) FILTER (WHERE price > 100) AS expensive_count,
  avg(price) FILTER (WHERE price > 100) AS expensive_avg,
  count(*) FILTER (WHERE price <= 100) AS cheap_count,
  avg(price) FILTER (WHERE price <= 100) AS cheap_avg
FROM products
GROUP BY category;

Expresiones de tabla comunes (CTE)

Los CTEs (cláusula WITH) mejoran la legibilidad de las consultas y permiten reutilizar subconsultas. PostgreSQL 12+ incrusta los CTEs no recursivos por defecto (así el rendimiento iguala a las subconsultas); antes de la 12, los CTEs eran cercas de optimización. Los CTEs RECURSIVE son potentes para datos jerárquicos/de árbol — la consulta ancla siembra, la parte recursiva crece. Los CTEs con RETURNING habilitan el movimiento complejo de datos (eliminar de una tabla, archivar en otra) en una sentencia. Usa CTEs para romper consultas complejas en pasos legibles.

postgresql
# basic CTE (WITH clause)
WITH active_users AS (
  SELECT id, username FROM users WHERE active = true
)
SELECT au.username, count(p.id) AS post_count
FROM active_users au
LEFT JOIN posts p ON au.id = p.user_id
GROUP BY au.username;

# multiple CTEs (can reference each other)
WITH user_stats AS (
  SELECT user_id, count(*) AS post_count FROM posts GROUP BY user_id
),
top_users AS (
  SELECT user_id FROM user_stats WHERE post_count > 10
)
SELECT u.username, us.post_count
FROM users u
JOIN top_users tu ON u.id = tu.user_id
JOIN user_stats us ON u.id = us.user_id;

# recursive CTE (tree traversal)
WITH RECURSIVE org_tree AS (
  SELECT id, name, manager_id, 0 AS depth
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, ot.depth + 1
  FROM employees e
  JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT id, name, depth FROM org_tree ORDER BY depth;

# CTE for DML (data modification)
WITH deleted AS (
  DELETE FROM users WHERE active = false RETURNING id
)
INSERT INTO archived_users (user_id)
SELECT id FROM deleted;
08

Índices

Índices B-Tree

B-tree es el tipo de índice por defecto y más común — soporta igualdad, rango y ordenación. Los índices compuestos se buscan de izquierda a derecha, así que pon las columnas de igualdad primero, luego las de rango/ordenación. Los índices parciales (cláusula WHERE) ahorran espacio y son más rápidos — úsalos para las consultas filtradas comunes (por ejemplo, WHERE active = true). Los índices de expresión habilitan indexar resultados de funciones. CONCURRENTLY construye sin bloquear escrituras (más lento pero seguro para producción). ¡Indexa siempre las claves foráneas!

postgresql
# create a B-tree index (default)
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_posts_created ON posts(created_at DESC);

# unique index (enforces uniqueness)
CREATE UNIQUE INDEX idx_users_username ON users(username);

# composite index (order matters!)
CREATE INDEX idx_posts_user_created
  ON posts(user_id, created_at DESC);

# partial index (only rows matching the condition)
CREATE INDEX idx_active_users ON users(last_login)
  WHERE active = true;

# expression index (index on a function)
CREATE INDEX idx_users_lower_email ON users(lower(email));
# query must use the same expression:
#   SELECT * FROM users WHERE lower(email) = '[email protected]'

# check index usage
SELECT * FROM pg_stat_user_indexes WHERE relname = 'users';

# drop an index
DROP INDEX IF EXISTS idx_users_email;
DROP INDEX CONCURRENTLY idx_users_email;  -- no lock, slower

Índices GIN y GiST

Los índices GIN son ideales cuando cada fila tiene múltiples valores indexados (claves JSONB, elementos de array, tokens de texto). Soportan los operadores @> (contiene), ? (existe la clave) y el texto completo @@. Los índices GIN son mayores pero manejan estas consultas eficientemente. GiST es para datos geométricos (puntos, polígonos) y tipos de rango — soporta solape (&&), contiene (@>), contenido (<@). Tanto GIN como GiST son más lentos de construir/actualizar que B-tree pero habilitan consultas que B-tree no puede. Usa GIN para JSONB y texto completo; GiST para consultas espaciales y de rango.

postgresql
# GIN (Generalized Inverted Index) — for multi-value columns
# great for JSONB, arrays, full-text search

CREATE INDEX idx_products_data ON products USING GIN (data);        -- JSONB
CREATE INDEX idx_posts_tags ON posts USING GIN (tags);              -- array
CREATE INDEX idx_articles_search ON articles USING GIN (search_vector); -- full-text

# query patterns that use GIN
SELECT * FROM products WHERE data @> '{"tags": ["sale"]}';
SELECT * FROM posts WHERE tags && ARRAY['python'];
SELECT * FROM articles WHERE search_vector @@ to_tsquery('postgres & index');

# GiST (Generalized Search Tree) — for geometric/range data
CREATE INDEX idx_locations ON places USING GIST (location);  -- point/polygon
CREATE INDEX idx_events ON events USING GIST (during);       -- tsrange

# geometric queries
SELECT * FROM places
WHERE location <@ box '(0,0),(10,10)';

# range queries
SELECT * FROM events
WHERE during && tsrange('2025-01-01', '2025-02-01');

BRIN y otros tipos de índice

BRIN (Block Range INdex) es extremadamente compacto (KB no GB) — guarda mín/máx por rango de bloques. Es efectivo cuando el orden físico de filas coincide con el orden de consulta (por ejemplo, logs de series temporales donde los datos nuevos se añaden). BRIN da un filtrado 'suficientemente bueno' que elimina la mayoría de bloques; PostgreSQL luego comprueba las filas restantes. Para tablas enormes de solo adición, BRIN es un antes y un después. Los índices Hash solo soportan igualdad (no rangos) pero son rápidos para búsquedas puntuales. SP-GiST encaja con datos desequilibrados como las tablas de enrutamiento IP.

postgresql
# BRIN (Block Range Index) — tiny, for naturally ordered data
# best for huge tables where data is physically ordered (e.g., time-series)

CREATE INDEX idx_logs_timestamp ON logs USING BRIN (timestamp);
# index is kilobytes, not gigabytes!

# BRIN works by storing min/max for each block range
# effective when physical order matches logical order (e.g., append-only logs)

# compare sizes:
#   B-tree on 1B rows: ~20GB
#   BRIN on 1B rows:   ~2MB (10000x smaller)

# SP-GiST (space-partitioned GiST) — for non-balanced data
CREATE INDEX idx_prefix ON routes USING SPGiST (prefix);  -- e.g., IP routing

# Hash index (equality only, crash-safe since PG10)
CREATE INDEX idx_users_session ON users USING HASH (session_token);
# only supports = operator, not ranges or sorting

# check what index types are available
SELECT amname FROM pg_am WHERE amtype = 'i';

Mantenimiento y análisis de índices

Monitorea el uso de índices con pg_stat_user_indexes — los índices sin usar desperdician espacio y ralentizan las escrituras. Elimina los duplicados (mismas columnas, misma tabla). REINDEX reconstruye los índices inflados (ocurre con muchas actualizaciones/eliminaciones); CONCURRENTLY (12+) evita bloquear. El hinchado de índices es normal — autovacuum los mantiene pero a veces hace falta un REINDEX manual. Para índices muy grandes, considera REINDEX CONCURRENTLY o pg_repack (herramienta de terceros que reconstruye sin locks). Prueba siempre los cambios de índices en staging.

postgresql
# find unused indexes (candidates for removal)
SELECT
  schemaname, relname, indexrelname,
  idx_scan AS scans,
  pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND schemaname = 'public'
ORDER BY pg_relation_size(indexrelid) DESC;

# find duplicate indexes
SELECT pg_size_pretty(sum(pg_relation_size(idx))::bigint) AS size,
       (array_agg(idx::text))[1] AS indexes
FROM (
  SELECT indexrelid::regclass AS idx, indrelid::regclass AS rel
  FROM pg_index
  GROUP BY indrelid, indkey
  HAVING count(*) > 1
) sub;

# REINDEX (rebuild a bloated index)
REINDEX INDEX idx_users_email;
REINDEX TABLE CONCURRENTLY users;  -- PG12+, no lock

# check index bloat
SELECT
  relname, pg_size_pretty(pg_relation_size(relid)) AS size,
  pg_size_pretty(pg_indexes_size(relid)) AS index_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_relation_size(relid) DESC;

Estrategia de indexación

Empieza con PK (autoindexada) y FK (manual). Añade índices para los caminos de consulta calientes. Los índices compuestos siguen la regla del prefijo más a la izquierda — ordena las columnas por selectividad y patrones de consulta. INCLUDE (índice cubriente) permite que las consultas se satisfagan solo desde el índice (escaneo de solo índice) sin traer la fila — enorme para cargas intensivas de lectura. No indexes en exceso: cada índice añade sobrecarga de escritura. Revisa y poda regularmente usando pg_stat_user_indexes. Haz siempre EXPLAIN para verificar el uso del índice.

postgresql
# 1. index primary keys (auto-created)
# 2. index foreign keys (NOT auto-created!)
CREATE INDEX idx_posts_user_id ON posts(user_id);

# 3. index columns in WHERE, JOIN, ORDER BY, GROUP BY
CREATE INDEX idx_users_status ON users(status);
CREATE INDEX idx_orders_created ON orders(created_at);

# 4. use composite indexes for multi-column queries
# leftmost prefix rule: (a, b, c) helps WHERE a=?, WHERE a=? AND b=?
# but NOT WHERE b=? alone
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);

# 5. partial indexes for common filters
CREATE INDEX idx_active_orders ON orders(created_at)
  WHERE status = 'active';

# 6. covering indexes (INCLUDE for index-only scans)
CREATE INDEX idx_products_cat_price ON products(category, price)
  INCLUDE (name);  -- name available without heap fetch

# 7. don't over-index — every index slows writes
# drop indexes used < 10 times/month (check pg_stat_user_indexes)

# verify the index is used
EXPLAIN SELECT * FROM orders WHERE user_id = 1;
09

Transacciones y concurrencia

Fundamentos de transacciones

Las transacciones agrupan sentencias en una unidad atómica — todas tienen éxito o todas fallan. BEGIN empieza, COMMIT guarda, ROLLBACK deshace. Los savepoints permiten la reversión parcial dentro de una transacción — útil para la recuperación de errores en operaciones complejas. Por defecto, PostgreSQL confirma automáticamente cada sentencia. Para operaciones de varias sentencias (transferencias, inserts multi-fila), usa siempre transacciones explícitas. Mantén las transacciones cortas para reducir la contención de locks y los deadlocks.

postgresql
# explicit transaction
BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;  -- or ROLLBACK to undo

# savepoints (partial rollback)
BEGIN;
  INSERT INTO orders (user_id, amount) VALUES (1, 50);
  SAVEPOINT order_inserted;
  INSERT INTO order_items (order_id, product_id) VALUES (1, 100);
  -- oops, wrong product
  ROLLBACK TO order_inserted;
  INSERT INTO order_items (order_id, product_id) VALUES (1, 200);
COMMIT;

# transaction control
BEGIN;
  -- statements
SAVEPOINT my_savepoint;
  -- more statements
ROLLBACK TO my_savepoint;  -- undo back to savepoint
RELEASE my_savepoint;      -- remove savepoint (commit its changes)
COMMIT;
-- or ROLLBACK to undo everything

# auto-commit (default — each statement is its own transaction)
SET autocommit = on;  -- default

Niveles de aislamiento

El valor por defecto de PostgreSQL (READ COMMITTED) es bueno para la mayoría de apps — sin lecturas sucias, pero una relectura en la misma transacción puede ver datos nuevos. REPEATABLE READ en PostgreSQL es más fuerte que el estándar SQL — también previene fantasma (usando aislamiento por instantánea). SERIALIZABLE es el más fuerte pero puede abortar transacciones que entran en conflicto — tu app debe reintentar ante SQLSTATE 40001. Para la mayoría de apps web, READ COMMITTED está bien. Usa SERIALIZABLE para la corrección crítica (libros contables financieros) con lógica de reintentos.

postgresql
# set isolation level for a transaction
BEGIN ISOLATION LEVEL READ COMMITTED;   -- default
BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN ISOLATION LEVEL SERIALIZABLE;

# or set it for the session
SET default_transaction_isolation = 'serializable';

# READ COMMITTED (default):
#   - each query sees committed data at query start
#   - no dirty reads, but non-repeatable reads and phantoms possible

# REPEATABLE READ:
#   - each query sees snapshot from transaction start
#   - no non-repeatable reads, but serialization anomalies possible
#   - PostgreSQL's RR is stronger than SQL standard (no phantoms!)

# SERIALIZABLE:
#   - strongest isolation, behaves as if transactions ran one at a time
#   - may abort with serialization failure (retry needed)
#   - uses SSI (Serializable Snapshot Isolation)

# check current level
SHOW transaction_isolation;

# handle serialization failures (retry)
BEGIN ISOLATION LEVEL SERIALIZABLE;
  -- work
COMMIT;  -- may fail with SQLSTATE 40001 -> retry the transaction

Locks y deadlocks

SELECT FOR UPDATE bloquea filas para actualizar (previene la modificación concurrente) — útil para los patrones de 'leer, comprobar, actualizar'. Los advisory locks son locks a nivel de aplicación que usan claves enteras — geniales para coordinar procesos distribuidos. Los deadlocks ocurren cuando dos transacciones se esperan mutuamente; PostgreSQL los detecta y aborta una (captura y reintenta). Para evitar deadlocks, adquiere los locks en un orden consistente entre transacciones. Mantén las transacciones cortas. Monitorea pg_locks para las sesiones atascadas.

postgresql
# row-level locks (acquired by UPDATE, DELETE, SELECT FOR UPDATE)
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;        -- lock row
SELECT * FROM accounts WHERE id = 1 FOR NO KEY UPDATE; -- weaker lock
SELECT * FROM accounts WHERE id = 1 FOR SHARE;         -- allow reads, block writes

# advisory locks (application-level locks)
SELECT pg_advisory_lock(12345);          -- session-level
SELECT pg_advisory_unlock(12345);
SELECT pg_try_advisory_lock(12345);      -- non-blocking
SELECT pg_advisory_xact_lock(12345);     -- transaction-level (auto-released)

# view active locks
SELECT pid, mode, granted, query
FROM pg_locks l JOIN pg_stat_activity a ON l.pid = a.pid
WHERE NOT l.granted;

# terminate a blocked session
SELECT pg_cancel_backend(pid);    -- cancel query
SELECT pg_terminate_backend(pid); -- kill session

# deadlocks (PostgreSQL detects and resolves by killing one transaction)
-- Transaction A: UPDATE users WHERE id=1; UPDATE users WHERE id=2;
-- Transaction B: UPDATE users WHERE id=2; UPDATE users WHERE id=1;
-- -> ERROR: deadlock detected

MVCC y aislamiento por instantánea

El MVCC de PostgreSQL significa que los lectores nunca bloquean a los escritores y viceversa — cada transacción ve una instantánea. Las actualizaciones crean versiones nuevas de fila; las versiones antiguas permanecen hasta que VACUUM las elimina. Las transacciones de larga vida impiden que VACUUM limpie las versiones antiguas, causando hinchado. ¡Mantén las transacciones cortas! Monitorea las sesiones 'idle in transaction' — sostienen instantáneas y bloquean la limpieza. El rebasamiento del ID de transacción es un problema serio (autovacuum lo previene, pero vigila si autovacuum está sufriendo).

postgresql
# PostgreSQL uses MVCC (Multi-Version Concurrency Control)
# each transaction sees a consistent snapshot of data

# visible transactions
SELECT txid_current();           -- current transaction ID
SELECT txid_snapshot_xmin(txid_current_snapshot());

# see long-running transactions (can prevent vacuum)
SELECT
  pid,
  age(clock_timestamp(), xact_start) AS duration,
  state,
  query
FROM pg_stat_activity
WHERE state IN ('active', 'idle in transaction')
ORDER BY xact_start;

# long transactions block vacuum (old row versions can't be removed)
# check for this:
SELECT
  pid,
  age(txid_current(), backend_xmin) AS xmin_age
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY xmin_age DESC;

# vacuum and the transaction ID wraparound
# (autovacuum handles this, but monitor it!)
SELECT relname, last_autovacuum, last_autoanalyze, n_dead_tup
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000;

Patrones de bloqueo

FOR UPDATE bloquea filas pesimísticamente — úsalo para consistencia crítica (transferencias). El bloqueo optimista (columna de versión) es mejor para baja contención: sin locks, solo reintentar ante conflicto. SKIP LOCKED es perfecto para colas de trabajos — varios workers toman trabajos distintos sin bloquearse. NOWAIT falla rápido si está bloqueado (úselo para 'intentar actualizar, no esperar'). Los advisory locks coordinan operaciones a nivel de aplicación (migraciones, trabajos cron). Elige pesimista para alta contención, optimista para baja contención.

postgresql
# pessimistic locking (lock before work)
BEGIN;
  SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
  -- now safe to modify
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

# optimistic locking (check version, retry on conflict)
-- uses a version column
UPDATE products SET stock = stock - 1, version = version + 1
WHERE id = 1 AND version = 5;  -- 0 rows = conflict, retry

# SKIP LOCKED (process queue items without blocking)
SELECT * FROM job_queue
WHERE status = 'pending'
ORDER BY created_at
FOR UPDATE SKIP LOCKED
LIMIT 10;
-- grabs 10 jobs, skips locked ones (other workers don't wait)

# NOWAIT (fail immediately if locked)
SELECT * FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;
-- ERROR: could not obtain lock if row is locked

# named lock for coordination
SELECT pg_advisory_lock(hashtext('migrate_users'));
-- only one session can hold this lock
SELECT pg_advisory_unlock(hashtext('migrate_users'));
10

Vistas y vistas materializadas

Crear vistas

Las vistas son consultas guardadas que se comportan como tablas. Las vistas simples (una tabla, sin agregados) son actualizables automáticamente — los inserts/updates pasan a la tabla subyacente. WITH CHECK OPTION impide inserts/updates que harían la fila invisible a través de la vista. Usa vistas para simplificar consultas complejas, imponer seguridad (acceso a nivel de columna) y aportar una API estable. CASCADE elimina los objetos dependientes. Las vistas no guardan datos — corren la consulta cada vez.

postgresql
# simple view
CREATE VIEW active_users AS
SELECT id, username, email FROM users WHERE active = true;

# use it like a table
SELECT * FROM active_users WHERE username LIKE 'a%';

# view with computed columns
CREATE VIEW user_summary AS
SELECT
  u.id,
  u.username,
  count(p.id) AS post_count,
  max(p.created_at) AS last_post
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.id, u.username;

# updatable view (simple views can be inserted/updated through)
CREATE VIEW user_emails AS
SELECT id, email FROM users;
INSERT INTO user_emails (id, email) VALUES (1, '[email protected]');  -- works!

# WITH CHECK OPTION (prevent inserts that wouldn't be visible)
CREATE VIEW active_users AS
SELECT * FROM users WHERE active = true
WITH CHECK OPTION;
-- INSERT with active=false fails

# modify or drop a view
ALTER VIEW active_users RENAME TO current_users;
DROP VIEW IF EXISTS active_users CASCADE;

Vistas materializadas

Las vistas materializadas guardan el resultado de la consulta — mucho más rápido de consultar pero rancio hasta refrescar. Úsalas para agregados caros (dashboards, reportes). CONCURRENTLY refresca sin bloquear la vista (los lectores siguen) pero exige un índice único. Programa los refrescos con pg_cron o herramientas externas. Para datos casi en tiempo real, refresca cada pocos minutos; para analítica histórica, a diario. La contrapartida es frescura frente a velocidad de consulta — las vistas materializadas son la clave de la analítica rápida sobre tablas grandes.

postgresql
# create a materialized view (stores the result)
CREATE MATERIALIZED VIEW sales_summary AS
SELECT
  date_trunc('day', created_at) AS day,
  product_id,
  count(*) AS order_count,
  sum(amount) AS total_revenue
FROM orders
GROUP BY 1, 2
WITH DATA;  -- or WITH NO DATA to populate later

# refresh (re-runs the query)
REFRESH MATERIALIZED VIEW sales_summary;
REFRESH MATERIALIZED VIEW CONCURRENTLY sales_summary;  -- needs unique index

# create a unique index for concurrent refresh
CREATE UNIQUE INDEX idx_sales_summary_day_product
ON sales_summary(day, product_id);

# schedule refresh (use pg_cron extension or external scheduler)
-- pg_cron: SELECT cron.schedule('0 * * * *', 'REFRESH MATERIALIZED VIEW CONCURRENTLY sales_summary');

# drop
DROP MATERIALIZED VIEW IF EXISTS sales_summary;

# use it (like a regular table)
SELECT * FROM sales_summary WHERE day = '2025-01-01';

Vistas de seguridad

Las vistas son una herramienta de seguridad potente: exponen solo las columnas/filas que un usuario debería ver sin conceder acceso a la tabla. Concede SELECT sobre la vista, no sobre la tabla subyacente. Para seguridad a nivel de fila, considera las políticas RLS (Row Level Security) integradas de PostgreSQL en lugar de vistas — son más robustas y funcionan con acceso directo a la tabla. Las vistas con variables de sesión (current_setting) habilitan el filtrado por usuario. Usa vistas para crear una capa de API estable que oculte la complejidad del esquema a las aplicaciones.

postgresql
# column-level security (hide sensitive columns)
CREATE VIEW public_users AS
SELECT id, username, created_at FROM users;
-- password, email columns are hidden

# row-level security via view
CREATE VIEW user_posts AS
SELECT * FROM posts WHERE user_id = current_setting('app.current_user_id')::int;

-- set the user context per session
SET app.current_user_id = '42';
SELECT * FROM user_posts;  -- only sees user 42's posts

# view with joins for simplified access
CREATE VIEW order_details AS
SELECT
  o.id AS order_id,
  o.created_at,
  u.username,
  p.name AS product_name,
  oi.quantity,
  oi.price
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id;

# grant access to view but not underlying tables
GRANT SELECT ON public_users TO app_readonly;
REVOKE SELECT ON users FROM app_readonly;

Gestión de vistas

CREATE OR REPLACE VIEW tiene limitaciones — puedes añadir columnas al final pero no quitarlas ni reordenarlas. Para cambios significativos, elimina y recrea (pero comprueba primero las dependencias). Las vistas pueden depender de otras vistas; eliminar una vista base exige CASCADE. Usa pg_views para inspeccionar definiciones. El bloque DO para refrescar todas las vistas materializadas es práctico para los scripts de mantenimiento — pero usa CONCURRENTLY donde sea posible para evitar bloqueos.

postgresql
# list views
SELECT viewname, definition FROM pg_views WHERE schemaname = 'public';

# get view definition
\d+ view_name  -- in psql

# create or replace (limited — can't change columns)
CREATE OR REPLACE VIEW user_summary AS
SELECT id, username, email FROM users WHERE active = true;
-- can add columns at the end, but can't remove/reorder

# to change columns significantly, drop and recreate
DROP VIEW IF EXISTS user_summary;
CREATE VIEW user_summary AS
SELECT id, username FROM users WHERE active = true;

# view dependencies (what depends on this view)
SELECT dependee.relname AS view, depender.relname AS depends_on
FROM pg_depend d
JOIN pg_class dependee ON d.objid = dependee.oid
JOIN pg_class depender ON d.refobjid = depender.oid
WHERE dependee.relname = 'user_summary';

# refresh all materialized views
DO $$
DECLARE r RECORD;
BEGIN
  FOR r IN SELECT matviewname FROM pg_matviews WHERE schemaname = 'public' LOOP
    EXECUTE 'REFRESH MATERIALIZED VIEW ' || r.matviewname;
  END LOOP;
END $$;

Vistas recursivas

Las vistas recursivas (CREATE RECURSIVE VIEW) envuelven un CTE recursivo — genial para recorrer árboles/grafos. Incluye siempre un límite de profundidad/distancia en la parte recursiva para evitar bucles infinitos (ciclos en los datos). La columna de ruta (concatenación de texto) es una forma simple de rastrear y mostrar el camino del recorrido. Para jerarquías muy profundas, considera guardar una tabla de cierre o la extensión ltree por mejor rendimiento. Las vistas recursivas son cómodas pero pueden ser lentas en datasets grandes — añade índices sobre las columnas de join.

postgresql
# recursive view for hierarchical data
CREATE RECURSIVE VIEW employee_tree(id, name, manager_id, level, path) AS
SELECT
  id, name, manager_id, 0, name::text
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT
  e.id, e.name, e.manager_id, et.level + 1,
  et.path || ' > ' || e.name
FROM employees e
JOIN employee_tree et ON e.manager_id = et.id;

# query the tree
SELECT * FROM employee_tree ORDER BY path;

# find all subordinates (2 levels deep)
SELECT * FROM employee_tree
WHERE path LIKE 'CEO > Engineering > %' AND level <= 3;

# view for graph traversal (e.g., friend connections)
CREATE RECURSIVE VIEW friend_chain(person1, person2, distance, path) AS
SELECT person1, person2, 1, person1 || '->' || person2
FROM friendships
UNION
SELECT
  fc.person1, f.person2, fc.distance + 1,
  fc.path || '->' || f.person2
FROM friend_chain fc
JOIN friendships f ON fc.person2 = f.person1
WHERE fc.distance < 6;  -- limit depth to prevent infinite loops
11

Funciones y operadores

Funciones de cadena

PostgreSQL tiene funciones de cadena completas. position() y strpos() hallan subcadenas. substring() con FROM/FOR es estándar SQL; substr() es más compacto. split_part() es práctico para datos delimitados. initcap() pone en mayúscula cada palabra. lpad/rpad rellenan cadenas a un ancho fijo (útil para formatear). string_to_array/array_to_string convierten entre cadenas y arrays. Para coincidencia de patrones, LIKE/ILIKE (comodines), ~ (regex) y SIMILAR TO (patrón SQL) cubren la mayoría de necesidades.

postgresql
# length and position
SELECT length('hello'), char_length('hello'), octet_length('hello');
SELECT position('lo' in 'hello');          -- 4
SELECT strpos('hello', 'lo');               -- 4

# substring and slicing
SELECT substring('hello' from 2 for 3);     -- 'ell'
SELECT substring('hello' from 2);           -- 'ello'
SELECT substr('hello', 2, 3);               -- 'ell'
SELECT left('hello', 3);                    -- 'hel'
SELECT right('hello', 3);                   -- 'llo'

# case and trim
SELECT upper('hello'), lower('HELLO'), initcap('hello world');
SELECT trim('  hello  '), ltrim('xxhello', 'x'), rtrim('helloxx', 'x');
SELECT btrim('xxhelloxx', 'x');             -- trim both sides

# split and join
SELECT split_part('a,b,c', ',', 2);         -- 'b' (1-indexed)
SELECT string_to_array('a,b,c', ',');       -- {a,b,c}
SELECT array_to_string(ARRAY['a','b','c'], ', ');  -- 'a, b, c'

# replace and pad
SELECT replace('hello', 'l', 'L');          -- 'heLLo'
SELECT lpad('5', 3, '0');                   -- '005'
SELECT rpad('5', 3, '0');                   -- '500'
SELECT repeat('ab', 3);                     -- 'ababab'
SELECT reverse('hello');                    -- 'olleh'

Funciones de fecha/hora

now() y current_timestamp son equivalentes (hora de inicio de la transacción). EXTRACT/date_part obtienen componentes. date_trunc redondea hacia abajo a una unidad — esencial para agrupar datos por tiempo. age() devuelve intervalos (legibles por humanos). to_char/to_date/to_timestamp manejan el formato/parseo. generate_series con fechas es increíblemente útil para rellenar huecos en datos de series temporales (haz LEFT JOIN contra él para mostrar días de conteo cero). Usa siempre timestamptz; la aritmética de fechas respeta la zona horaria de la sesión.

postgresql
# current date/time
SELECT now(), current_timestamp, transaction_timestamp();
SELECT current_date, current_time;

# extract components
SELECT EXTRACT(YEAR FROM now()), EXTRACT(MONTH FROM now());
SELECT date_part('dow', now());     -- day of week (0=Sunday)
SELECT date_part('epoch', now());   -- Unix timestamp

# date arithmetic
SELECT now() + interval '1 day';
SELECT now() - interval '2 hours';
SELECT age('2025-01-01');                    -- interval since date
SELECT age('2025-01-01', '2024-01-01');      -- interval between dates
SELECT date '2025-01-15' - date '2025-01-01'; -- 14 (integer days)

# truncation and rounding
SELECT date_trunc('month', now());           -- first day of month, 00:00
SELECT date_trunc('hour', now());            -- current hour, 00 minutes

# formatting
SELECT to_char(now(), 'YYYY-MM-DD HH:MI:SS');
SELECT to_char(now(), 'Day, DD Mon YYYY');
SELECT to_char(12345.678, 'FM999,999.00');
SELECT to_date('2025/01/15', 'YYYY/MM/DD');
SELECT to_timestamp(1700000000);             -- Unix epoch

# generate series (date ranges)
SELECT generate_series(
  '2025-01-01'::date,
  '2025-01-31'::date,
  '1 day'::interval
);

Funciones numéricas y matemáticas

round() con dos argumentos funciona sobre numeric (no sobre double — castea primero). Las funciones trigonométricas usan radianes (usa radians()/degrees() para convertir). random() devuelve 0-1; combínalo con matemáticas para rangos. generate_series es el caballo de batalla de PostgreSQL — genera filas al vuelo para secuencias, rangos de fechas y rellenar huecos. gcd/lcm (13+) son prácticos. Para cálculos financieros, usa siempre numeric (exacto) — las funciones de punto flotante como round sobre double pueden tener problemas de precisión.

postgresql
# rounding
SELECT round(3.14159, 2);    -- 3.14
SELECT ceil(3.1), ceiling(3.1);  -- 4
SELECT floor(3.9);           -- 3
SELECT trunc(3.14159, 2);    -- 3.14 (truncate, no rounding)

# power and roots
SELECT power(2, 10);         -- 1024
SELECT sqrt(16);             -- 4
SELECT cbrt(27);             -- 3
SELECT exp(1);               -- 2.718... (e^x)
SELECT ln(10), log(100);     -- natural log, base-10 log

# trigonometry (radians!)
SELECT sin(radians(90));     -- 1 (convert degrees to radians)
SELECT degrees(1.5708);      -- ~90
SELECT pi();                 -- 3.14159...

# random
SELECT random();             -- 0 to 1
SELECT floor(random() * 100)::int;  -- 0 to 99
SELECT setseed(0.5);         -- seed for reproducibility

# sequences and ranges
SELECT generate_series(1, 10);
SELECT generate_series(1, 10, 2);   -- 1, 3, 5, 7, 9
SELECT generate_series(0, 1, 0.1);  -- 0.0, 0.1, ..., 1.0

# absolute, sign, gcd
SELECT abs(-5), sign(-5);    -- 5, -1
SELECT gcd(12, 8);           -- 4 (PG 13+)
SELECT lcm(12, 8);           -- 24 (PG 13+)

Funciones condicionales y de NULL

COALESCE devuelve el primer valor no NULL — esencial para valores por defecto. NULLIF devuelve NULL si los valores coinciden — más limpio que CASE para evitar la división por cero. GREATEST/LEAST comparan valores (los NULLs se propagan en GREATEST pero no siempre en LEAST). CASE es el condicional general. ISNULL/NOTNULL son atajos de PG. La comparación de filas (ROW(a,b) < ROW(c,d)) compara lexicográficamente — útil para la paginación por clave compuesta. Entender el comportamiento de NULL (NULL = NULL es NULL, no verdadero) es crucial en PostgreSQL.

postgresql
# COALESCE (return first non-NULL)
SELECT COALESCE(nickname, username, email, 'anonymous') FROM users;

# NULLIF (return NULL if two values are equal)
SELECT NULLIF(status, '') FROM users;  -- '' becomes NULL
-- useful to avoid division by zero:
SELECT total / NULLIF(count, 0) FROM stats;  -- NULL instead of error

# GREATEST / LEAST
SELECT GREATEST(1, 2, 3);        -- 3
SELECT LEAST(NULL, 1, 2);        -- NULL (NULLs propagate)
SELECT GREATEST(a, b, c) FROM numbers;

# CASE expressions
SELECT
  CASE
    WHEN age < 18 THEN 'minor'
    WHEN age >= 65 THEN 'senior'
    ELSE 'adult'
  END AS age_group
FROM users;

# ISNULL / NOTNULL (PostgreSQL shortcuts)
SELECT * FROM users WHERE email ISNULL;    -- IS NULL
SELECT * FROM users WHERE email NOTNULL;   -- IS NOT NULL

# row comparison
SELECT ROW(1, 2) < ROW(1, 3);  -- true (compares column by column)

Funciones de array y JSON

Las funciones de array habilitan la manipulación potente de datos dentro de la base de datos: unnest expande arrays a filas (genial para joins), array_agg es lo inverso. Operadores JSONB: -> y #> devuelven jsonb, ->> y #>> devuelven text. @> (contiene) es el operador JSONB más importante y usa índices GIN. ? comprueba la existencia de claves. jsonb_build_object construye JSON desde columnas. jsonb_set/||/- modifican el JSON. Estas funciones hacen de JSONB un tipo de datos de primera clase — flexible pero consultable.

postgresql
# array functions
SELECT array_length(ARRAY[1,2,3], 1);    -- 3
SELECT array_append(ARRAY[1,2], 3);       -- {1,2,3}
SELECT array_prepend(1, ARRAY[2,3]);      -- {1,2,3}
SELECT array_concat(ARRAY[1,2], ARRAY[3,4]); -- {1,2,3,4}
SELECT array_remove(ARRAY[1,2,3], 2);     -- {1,3}
SELECT array_replace(ARRAY[1,2,1], 1, 9); -- {9,2,9}
SELECT unnest(ARRAY[1,2,3]);              -- 3 rows
SELECT array_agg(name) FROM users;        -- aggregate to array

# JSONB functions
SELECT data->'name'           -- jsonb (key access)
SELECT data->>'name'          -- text (key access)
SELECT data#>'{address,city}' -- jsonb (path access)
SELECT data#>>'{address,city}'-- text (path access)
SELECT jsonb_build_object('name', username, 'age', age) FROM users;
SELECT jsonb_object_keys(data) FROM products;  -- keys as rows
SELECT jsonb_array_elements(data->'tags') FROM products;  -- expand array

# JSONB predicates
SELECT data @> '{"active": true}'     -- contains
SELECT data ? 'name'                   -- key exists
SELECT data ?| ARRAY['name','email']   -- any key exists
SELECT data ?& ARRAY['name','email']   -- all keys exist

# modify JSONB
SELECT jsonb_set(data, '{price}', '29.99')
SELECT data || '{"new": true}'         -- merge
SELECT data - 'old_key'                -- remove key
SELECT data #- '{nested,old_key}'      -- remove nested key
12

Triggers

Funciones de trigger

Las funciones de trigger deben devolver trigger y se escriben en PL/pgSQL (u otros lenguajes). TG_OP, TG_TABLE_NAME, OLD y NEW son variables especiales disponibles dentro de los triggers. Los triggers BEFORE pueden modificar NEW (y saltarse operaciones devolviendo NULL). Los AFTER son para efectos secundarios (logs de auditoría, notificaciones). FOR EACH ROW se dispara por fila; FOR EACH STATEMENT una vez. Los triggers son potentes pero pueden causar efectos secundarios ocultos — úsalos para preocupaciones transversales (auditoría, marcas de tiempo), no para lógica de negocio.

postgresql
# create a trigger function (must return trigger)
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS trigger AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

# attach the trigger
CREATE TRIGGER set_updated_at
  BEFORE UPDATE ON users
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at();

# now any UPDATE on users sets updated_at automatically
UPDATE users SET email = '[email protected]' WHERE id = 1;
-- updated_at is set automatically

# audit log trigger
CREATE OR REPLACE FUNCTION audit_log()
RETURNS trigger AS $$
BEGIN
  INSERT INTO audit_table (table_name, operation, user_name, changed_at, row_data)
  VALUES (
    TG_TABLE_NAME, TG_OP, current_user, now(),
    to_jsonb(OLD)  -- or NEW for insert
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER users_audit
  AFTER INSERT OR UPDATE OR DELETE ON users
  FOR EACH ROW EXECUTE FUNCTION audit_log();

Triggers de sentencia vs de fila

Los triggers FOR EACH ROW se disparan por fila (pueden ser lentos para operaciones masivas). FOR EACH STATEMENT se dispara una vez (no puede acceder a OLD/NEW individuales, pero puede usar tablas de transición para lotes). Los triggers BEFORE pueden modificar/saltar filas; los AFTER son para efectos secundarios. La cláusula WHEN filtra cuándo se dispara el trigger (evaluada por fila). Las tablas de transición (REFERENCING NEW TABLE/OLD TABLE) dan a los triggers de sentencia acceso a todas las filas cambiadas — genial para auditar operaciones masivas sin sobrecarga por fila.

postgresql
# ROW trigger (fires once per affected row)
CREATE TRIGGER log_each_change
  AFTER UPDATE ON products
  FOR EACH ROW
  WHEN (OLD.price IS DISTINCT FROM NEW.price)
  EXECUTE FUNCTION log_price_change();

# STATEMENT trigger (fires once per statement, regardless of rows)
CREATE TRIGGER refresh_summary
  AFTER INSERT OR UPDATE OR DELETE ON orders
  FOR EACH STATEMENT
  EXECUTE FUNCTION refresh_sales_summary();

# BEFORE vs AFTER
# BEFORE: can modify NEW, skip the operation (return NULL), or change values
# AFTER: cannot modify the row, used for side effects (logs, notifications)

# conditional trigger (WHEN clause — evaluated per row)
CREATE TRIGGER check_high_value
  BEFORE INSERT ON orders
  FOR EACH ROW
  WHEN (NEW.amount > 10000)
  EXECUTE FUNCTION require_approval();

# transition tables (NEW/OLD as sets, for statement triggers)
CREATE TRIGGER audit_orders
  AFTER INSERT ON orders
  REFERENCING NEW TABLE AS new_rows
  FOR EACH STATEMENT
  EXECUTE FUNCTION log_bulk_insert();

Triggers de evento (DDL)

Los triggers de evento se disparan ante comandos DDL (CREATE/ALTER/DROP) — útiles para auditar cambios de esquema o prevenir operaciones peligrosas en producción. Usan tg_event y tg_tag para identificar el comando. Casos de uso comunes: impedir DROP TABLE, registrar cada comando DDL por cumplimiento, autogenerar scripts de migración. Los triggers de evento se disparan DESPUÉS del comando (ddl_command_end) o pueden interceptar (ddl_command_start). Son a nivel de base de datos, no de tabla. Úsalos con moderación — añaden sobrecarga a cada operación DDL.

postgresql
# event triggers fire on DDL events (CREATE, ALTER, DROP)
CREATE OR REPLACE FUNCTION no_drop_table()
RETURNS event_trigger AS $$
BEGIN
  IF tg_event = 'ddl_command_end' AND tg_tag = 'DROP TABLE' THEN
    RAISE EXCEPTION 'Dropping tables is not allowed in production';
  END IF;
END;
$$ LANGUAGE plpgsql;

# register the event trigger
CREATE EVENT TRIGGER protect_tables
  ON ddl_command_end
  WHEN tag IN ('DROP TABLE')
  EXECUTE FUNCTION no_drop_table();

# log all DDL changes
CREATE OR REPLACE FUNCTION log_ddl()
RETURNS event_trigger AS $$
DECLARE
  obj record;
BEGIN
  INSERT INTO ddl_log (event, tag, user_name, object_identity)
  VALUES (tg_event, tg_tag, current_user, NULL);
END;
$$ LANGUAGE plpgsql;

CREATE EVENT TRIGGER ddl_logger
  ON ddl_command_end
  EXECUTE FUNCTION log_ddl();

# drop an event trigger
DROP EVENT TRIGGER IF EXISTS protect_tables;

Gestión de triggers

Deshabilita los triggers temporalmente para cargas masivas (mucho más rápido) — ¡pero recuerda reactivarlos! session_replication_role = 'replica' deshabilita la totalidad de los triggers y reglas — úsalo para importaciones masivas o replicación. ALTER TABLE ... DISABLE TRIGGER es más dirigido. Reactiva siempre los triggers tras cargas masivas. Monitorea el uso de triggers — pueden causar problemas de rendimiento ocultos (cada trigger añade sobrecarga por fila). information_schema.triggers lista cada trigger; \d+ en psql es más rápido para inspeccionar.

postgresql
# list triggers
SELECT
  event_object_table AS table_name,
  trigger_name,
  action_timing,       -- BEFORE/AFTER/INSTEAD OF
  event_manipulation,  -- INSERT/UPDATE/DELETE
  action_statement     -- function call
FROM information_schema.triggers;

# or use psql
\d+ users  -- shows triggers on the table

# enable/disable triggers
ALTER TABLE users DISABLE TRIGGER set_updated_at;
ALTER TABLE users ENABLE TRIGGER set_updated_at;
ALTER TABLE users DISABLE TRIGGER ALL;    -- all triggers
ALTER TABLE users ENABLE TRIGGER ALL;

# session_replication_role (disable for replication)
SET session_replication_role = 'replica';
-- triggers don't fire (useful for bulk data loads)
SET session_replication_role = 'origin';  -- re-enable

# drop a trigger
DROP TRIGGER IF EXISTS set_updated_at ON users;

# rename a trigger
ALTER TRIGGER set_updated_at ON users RENAME TO update_timestamp;

Patrones comunes de triggers

Patrones comunes de triggers: marcas de tiempo automáticas (updated_at), logs de auditoría (rastrear cada cambio por cumplimiento), imposición de reglas de negocio (validación entre tablas) y sincronización de datos desnormalizados (mantener contadores). Cada patrón resuelve un problema real pero añade complejidad y sobrecarga. Considera alternativas: defaults para marcas de tiempo, logging de auditoría a nivel de aplicación, restricciones CHECK para reglas simples, vistas materializadas para desnormalizar. Usa triggers cuando la lógica deba vivir en la base de datos (múltiples apps acceden a los datos, o la regla es innegociable).

postgresql
# 1. Auto-update updated_at
CREATE FUNCTION set_updated_at() RETURNS trigger AS $$
BEGIN NEW.updated_at = now(); RETURN NEW; END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER users_updated_at BEFORE UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

# 2. Audit log (track all changes)
CREATE FUNCTION audit() RETURNS trigger AS $$
BEGIN
  INSERT INTO audit_log (table_name, op, old_data, new_data, changed_by, changed_at)
  VALUES (TG_TABLE_NAME, TG_OP,
    CASE WHEN TG_OP IN ('UPDATE','DELETE') THEN to_jsonb(OLD) END,
    CASE WHEN TG_OP IN ('INSERT','UPDATE') THEN to_jsonb(NEW) END,
    current_user, now());
  RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

# 3. Enforce business rules
CREATE FUNCTION check_budget() RETURNS trigger AS $$
BEGIN
  IF EXISTS (
    SELECT 1 FROM departments d
    WHERE d.id = NEW.dept_id AND d.budget < NEW.amount
  ) THEN
    RAISE EXCEPTION 'Amount exceeds department budget';
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

# 4. Sync denormalized data
CREATE FUNCTION update_post_count() RETURNS trigger AS $$
BEGIN
  UPDATE users SET post_count = (
    SELECT count(*) FROM posts WHERE user_id = NEW.user_id
  ) WHERE id = NEW.user_id;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;
13

Procedimientos almacenados y PL/pgSQL

Funciones PL/pgSQL

PL/pgSQL es el lenguaje procedimental de PostgreSQL — añade variables, condicionales, bucles y manejo de errores al SQL. Las funciones devuelven un valor o tabla. SELECT INTO asigna resultados de consulta a variables. RETURN QUERY devuelve filas de una consulta. Los parámetros pueden tener valores por defecto. Usa funciones para encapsular lógica reutilizable, consultas complejas y reglas de negocio. Mantén las funciones simples y bien nombradas. Por rendimiento, considera funciones SQL (LANGUAGE sql) para consultas simples — el optimizador puede incrustarlas.

postgresql
# basic function
CREATE OR REPLACE FUNCTION add(a integer, b integer)
RETURNS integer AS $$
BEGIN
  RETURN a + b;
END;
$$ LANGUAGE plpgsql;

# function with variables and logic
CREATE OR REPLACE FUNCTION get_user_post_count(user_id integer)
RETURNS integer AS $$
DECLARE
  post_count integer;
BEGIN
  SELECT count(*) INTO post_count
  FROM posts WHERE posts.user_id = get_user_post_count.user_id;

  IF post_count IS NULL THEN
    RETURN 0;
  END IF;

  RETURN post_count;
END;
$$ LANGUAGE plpgsql;

# function returning a table (set-returning)
CREATE OR REPLACE FUNCTION get_recent_posts(limit_count integer DEFAULT 10)
RETURNS TABLE(id integer, title text, created_at timestamptz) AS $$
BEGIN
  RETURN QUERY
  SELECT posts.id, posts.title, posts.created_at
  FROM posts
  ORDER BY posts.created_at DESC
  LIMIT limit_count;
END;
$$ LANGUAGE plpgsql;

# call functions
SELECT get_user_post_count(42);
SELECT * FROM get_recent_posts(5);

Estructuras de control

PL/pgSQL tiene estructuras de control completas: IF/ELSIF/ELSE, CASE, LOOP/EXIT/CONTINUE, WHILE, FOR (rangos de enteros) y FOREACH (iteración de arrays). Usa EXIT WHEN para romper bucles. FOR i IN 1..n itera rangos (inclusivos). FOREACH itera arrays. EXECUTE corre SQL dinámico (con format() para la inyección segura de identificadores). Mantén los bucles en la base de datos pequeños — los bucles grandes deberían ser SQL por conjuntos siempre que sea posible. Para operaciones masivas, usa INSERT/UPDATE con SELECT en lugar de bucles fila por fila.

postgresql
# IF/ELSIF/ELSE
CREATE OR REPLACE FUNCTION categorize_price(price numeric)
RETURNS text AS $$
BEGIN
  IF price IS NULL THEN RETURN 'unknown';
  ELSIF price < 10 THEN RETURN 'cheap';
  ELSIF price < 50 THEN RETURN 'moderate';
  ELSE RETURN 'expensive';
  END IF;
END;
$$ LANGUAGE plpgsql;

# CASE expression (inside BEGIN...END)
CREATE OR REPLACE FUNCTION get_discount(tier text)
RETURNS numeric AS $$
BEGIN
  RETURN CASE tier
    WHEN 'gold'   THEN 0.20
    WHEN 'silver' THEN 0.10
    WHEN 'bronze' THEN 0.05
    ELSE 0.00
  END;
END;
$$ LANGUAGE plpgsql;

# LOOP (with EXIT)
CREATE OR REPLACE FUNCTION find_first_null(table_name text)
RETURNS integer AS $$
DECLARE i integer := 1; val text;
BEGIN
  LOOP
    EXECUTE format('SELECT col FROM %I WHERE id = %s', table_name, i) INTO val;
    EXIT WHEN val IS NULL;
    i := i + 1;
    EXIT WHEN i > 1000;  -- safety limit
  END LOOP;
  RETURN i;
END;
$$ LANGUAGE plpgsql;

# WHILE and FOR loops
CREATE OR REPLACE FUNCTION sum_n(n integer)
RETURNS integer AS $$
DECLARE total integer := 0;
BEGIN
  FOR i IN 1..n LOOP
    total := total + i;
  END LOOP;
  RETURN total;
END;
$$ LANGUAGE plpgsql;

Procedimientos almacenados (PG11+)

Los procedimientos (PG11+) difieren de las funciones: pueden gestionar transacciones (COMMIT/ROLLBACK dentro), no devuelven valores como las funciones (usa parámetros OUT en su lugar) y se llaman con CALL. Usa procedimientos para operaciones de varias sentencias que necesiten control de transacciones (ETL, migraciones de datos). Usa funciones para computaciones y consultas que devuelvan valores. Los procedimientos son la forma estándar SQL de encapsular lógica transaccional. Antes de PG11, las funciones no podían hacer commit/rollback — los procedimientos llenan ese hueco.

postgresql
# PROCEDURE (unlike FUNCTION, can manage transactions)
CREATE OR REPLACE PROCEDURE transfer_money(
  from_account integer,
  to_account integer,
  amount numeric
) AS $$
BEGIN
  -- inside a procedure, we can use COMMIT/ROLLBACK
  UPDATE accounts SET balance = balance - amount WHERE id = from_account;
  UPDATE accounts SET balance = balance + amount WHERE id = to_account;

  -- check for negative balance
  IF EXISTS (SELECT 1 FROM accounts WHERE id = from_account AND balance < 0) THEN
    ROLLBACK;
    RAISE EXCEPTION 'Insufficient funds';
  END IF;

  COMMIT;
END;
$$ LANGUAGE plpgsql;

# call a procedure (CALL, not SELECT)
CALL transfer_money(1, 2, 100.00);

# procedure with output parameters
CREATE OR REPLACE PROCEDURE get_stats(
  OUT user_count integer,
  OUT post_count integer
) AS $$
BEGIN
  SELECT count(*) INTO user_count FROM users;
  SELECT count(*) INTO post_count FROM posts;
END;
$$ LANGUAGE plpgsql;

CALL get_stats(?, ?);  -- output params

Manejo de errores

RAISE EXCEPTION aborta la transacción con un error personalizado. Los bloques EXCEPTION capturan errores (como try/catch) — comunes para la degradación elegante. SQLSTATE es el código de error de 5 caracteres; SQLERRM el mensaje. USING HINT/DETAIL añade contexto útil. RAISE NOTICE es genial para depurar (visible en psql). Captura errores concretos (division_by_zero, unique_violation) en lugar de OTHERS para no enmascarar bugs reales. Dentro de EXCEPTION, la transacción está en estado abortado — solo puedes hacer commit/rollback, no correr más consultas (hasta los procedimientos de PG11+).

postgresql
# RAISE for messages and exceptions
CREATE OR REPLACE FUNCTION validate_age(age integer)
RETURNS void AS $$
BEGIN
  IF age < 0 THEN
    RAISE EXCEPTION 'Age cannot be negative: %', age
      USING HINT = 'Please provide a valid age (0-150)';
  ELSIF age > 150 THEN
    RAISE EXCEPTION 'Age seems unrealistic: %', age;
  ELSE
    RAISE NOTICE 'Age validated: %', age;
  END IF;
END;
$$ LANGUAGE plpgsql;

# TRY/CATCH with EXCEPTION block
CREATE OR REPLACE FUNCTION safe_divide(a numeric, b numeric)
RETURNS numeric AS $$
DECLARE result numeric;
BEGIN
  result := a / b;
  RETURN result;
EXCEPTION
  WHEN division_by_zero THEN
    RAISE NOTICE 'Division by zero, returning NULL';
    RETURN NULL;
  WHEN OTHERS THEN
    RAISE NOTICE 'Unexpected error: %', SQLERRM;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

# RAISE levels: DEBUG, LOG, INFO, NOTICE, WARNING, EXCEPTION
# EXCEPTION aborts the transaction; others just log

# get error details
SELECT sqlstate, message, detail, hint, context
FROM pg_stat_activity;  -- or in exception handler: SQLSTATE, SQLERRM

SQL dinámico

El SQL dinámico (EXECUTE) corre cadenas como SQL — esencial cuando los nombres de tabla/columna son variables. Usa siempre format() con %I (identificadores) y %L (literales) para prevenir la inyección SQL — ¡nunca concatenes entrada de usuario directamente! USING pasa parámetros con seguridad (como las sentencias preparadas). El SQL dinámico es potente pero opaco para el planificador de consultas y las herramientas de análisis estático. Úsalo cuando sea necesario (nombres de tabla dinámicos, generación de DDL) pero prefiere SQL estático donde puedas. Pruébalo a fondo — los errores de SQL dinámico son de runtime, no de compilación.

postgresql
# EXECUTE for dynamic queries
CREATE OR REPLACE FUNCTION count_rows(table_name text)
RETURNS integer AS $$
DECLARE result integer;
BEGIN
  EXECUTE format('SELECT count(*) FROM %I', table_name) INTO result;
  RETURN result;
END;
$$ LANGUAGE plpgsql;

# format() for safe SQL construction
#   %I = identifier (table/column name, quoted)
#   %L = literal (value, quoted)
#   %s = simple string substitution (UNSAFE — avoid for user input)
SELECT format('SELECT * FROM %I WHERE id = %L', 'users', 42);
-- 'SELECT * FROM users WHERE id = 42'

# dynamic SQL with USING for parameters
CREATE OR REPLACE FUNCTION get_user_by(field text, value text)
RETURNS setof users AS $$
BEGIN
  RETURN QUERY EXECUTE format(
    'SELECT * FROM users WHERE %I = $1', field
  ) USING value;
END;
$$ LANGUAGE plpgsql;

# bulk operations with dynamic SQL
CREATE OR REPLACE FUNCTION archive_old_data(days_old integer)
RETURNS integer AS $$
DECLARE deleted_count integer;
BEGIN
  EXECUTE format(
    'WITH deleted AS (
       DELETE FROM logs WHERE created_at < now() - interval '%s days'
       RETURNING *
     ) SELECT count(*) FROM deleted'
  ) INTO deleted_count USING days_old;

  RETURN deleted_count;
END;
$$ LANGUAGE plpgsql;
14

Seguridad y permisos

Usuarios y roles

En PostgreSQL, usuarios y roles son la misma entidad — un 'usuario' es solo un rol con privilegio LOGIN. Crea roles de grupo (sin LOGIN) para gestionar permisos centralizadamente y luego haz GRANT de ellos a los roles con login. VALID UNTIL fija la expiración de la contraseña. CONNECTION LIMIT acota las conexiones concurrentes por rol. SET search_path por rol es genial para el aislamiento multi-inquilino. Usa siempre roles para agrupar permisos en lugar de concederlos a usuarios individuales. DROP ROLE exige eliminar primero sus privilegios y membresías.

postgresql
# create a role (users are roles that can log in)
CREATE ROLE app_user WITH LOGIN PASSWORD 'secret';
CREATE ROLE analyst WITH LOGIN PASSWORD 'secret' VALID UNTIL '2025-12-31';

# create a role that cannot log in (for grouping)
CREATE ROLE readonly;

# alter a role
ALTER ROLE app_user WITH PASSWORD 'new_secret';
ALTER ROLE app_user WITH VALID UNTIL 'infinity';
ALTER ROLE app_user CONNECTION LIMIT 10;
ALTER ROLE app_user SET search_path TO app_schema, public;

# grant role to another (inherit permissions)
GRANT readonly TO app_user;
GRANT readonly TO analyst;

# view roles
\du  -- in psql
SELECT rolname, rolsuper, rolcanlogin FROM pg_roles;

# drop a role (must revoke privileges first)
REVOKE readonly FROM app_user;
DROP ROLE app_user;

Conceder privilegios

Privilegios: SELECT/INSERT/UPDATE/DELETE/TRUNCATE/REFERENCES/TRIGGER para tablas; USAGE/CREATE para esquemas; USAGE/SELECT para secuencias; EXECUTE para funciones; CONNECT/CREATE/TEMPORARY para bases de datos. Las concesiones a nivel de columna son potentes por seguridad. ALTER DEFAULT PRIVILEGES concede permisos sobre objetos futuros — esencial cuando se crean tablas nuevas con frecuencia. Concede siempre USAGE de secuencia a los roles que insertan en tablas con columnas serial/identity. Revoca los privilegios en exceso para un acceso de mínimo privilegio.

postgresql
# grant table privileges
GRANT SELECT, INSERT, UPDATE ON products TO app_user;
GRANT ALL ON products TO admin;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

# grant column-level privileges
GRANT SELECT (id, name, email) ON users TO app_user;
-- app_user can only see those columns

# grant schema privileges
GRANT USAGE ON SCHEMA app_schema TO app_user;
GRANT CREATE ON SCHEMA app_schema TO developer;

# grant sequence privileges (for serial/identity)
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_user;

# grant database privileges
GRANT CONNECT ON DATABASE mydb TO app_user;
GRANT CREATE ON DATABASE mydb TO developer;
GRANT TEMPORARY ON DATABASE mydb TO app_user;

# grant function privileges
GRANT EXECUTE ON FUNCTION get_user_post_count(integer) TO app_user;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO app_user;

# revoke privileges
REVOKE INSERT, UPDATE ON products FROM app_user;

# alter default privileges (for future objects)
ALTER DEFAULT PRIVILEGES FOR ROLE postgres
  IN SCHEMA public GRANT SELECT ON TABLES TO readonly;

Row Level Security (RLS)

Row Level Security (RLS) impone el control de acceso por fila — los usuarios ven solo las filas que coinciden con la política. USING filtra las lecturas; WITH CHECK valida las escrituras. Fija variables de sesión específicas de la app (SET app.user_id) para identificar al usuario actual. FORCE RLS somete incluso al propietario de la tabla a las políticas. El atributo de rol BYPASSRLS se salta el RLS por completo (para administradores). RLS es potente para apps multi-inquilino — sin necesidad de vistas por usuario. Ten cuidado: las políticas RLS deben ser correctas, pues filtran datos silenciosamente. Pruébalas a fondo con distintos usuarios.

postgresql
# enable RLS on a table
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

# create a policy (users see only their own posts)
CREATE POLICY user_posts ON posts
  FOR SELECT
  USING (user_id = current_setting('app.user_id')::int);

# policy for all operations
CREATE POLICY user_posts_all ON posts
  USING (user_id = current_setting('app.user_id')::int)
  WITH CHECK (user_id = current_setting('app.user_id')::int);

# USING:    filter existing rows (SELECT, UPDATE, DELETE)
# WITH CHECK: validate new rows (INSERT, UPDATE)

# set the user context per session
SET app.user_id = '42';
SELECT * FROM posts;  -- only sees user 42's posts

# bypass RLS (for admins)
ALTER TABLE posts FORCE ROW LEVEL SECURITY;  -- even owner respects RLS
GRANT BYPASSRLS TO admin_role;  -- role-level bypass

# policy for admins (see all)
CREATE POLICY admin_all ON posts
  FOR ALL
  TO admin_role
  USING (true)
  WITH CHECK (true);

# disable RLS
ALTER TABLE posts DISABLE ROW LEVEL SECURITY;

Autenticación y SSL

pg_hba.conf es la primera línea de defensa — controla qué hosts/usuarios pueden conectarse y cómo. Usa siempre scram-sha-256 (no md5 ni trust). hostssl exige SSL para la conexión. sslmode=require garantiza el cifrado; verify-full también verifica el certificado (lo más seguro). Para producción: usa SSL para todas las conexiones remotas, acota pg_hba.conf a IPs conocidas y usa scram-sha-256. Recarga con pg_reload_conf() tras editar. Monitorea pg_stat_activity para conexiones no autorizadas.

postgresql
# pg_hba.conf controls who can connect and how
# host  database  user  address  method
#   host all all 127.0.0.1/32 scram-sha-256
#   host all all 0.0.0.0/0   scram-sha-256
#   hostssl all all 0.0.0.0/0 scram-sha-256  # require SSL

# reload pg_hba.conf
SELECT pg_reload_conf();

# authentication methods:
#   trust:       no password (NEVER use in production)
#   md5:         legacy password hash (deprecated)
#   scram-sha-256:  modern password auth (default, recommended)
#   cert:        client certificate
#   peer:        OS username matches DB username (local only)

# set password encryption
SET password_encryption = 'scram-sha-256';
ALTER ROLE app_user PASSWORD 'secret';  -- re-hash with scram

# SSL connections
SHOW ssl;                  -- is SSL enabled?
SHOW ssl_cert_file;
SHOW ssl_key_file;

# connect with SSL
psql "postgresql://user@host/db?sslmode=require"
# sslmode: disable, allow, prefer, require, verify-ca, verify-full

# view active connections
SELECT pid, usename, client_addr, ssl, ssl_cipher
FROM pg_stat_activity;

Cifrado y auditoría

pgcrypto aporta cifrado a nivel de columna: pgp_sym_encrypt/decrypt para cifrado reversible, crypt/gen_salt para hashear contraseñas. Usa bcrypt (bf) para contraseñas — es lento por diseño (resiste la fuerza bruta). Por cumplimiento, pgAudit registra todas las operaciones DDL y DML en el log del servidor. Para datos sensibles, prefiere el cifrado en la capa de aplicación (las claves no deberían vivir en la base de datos). Hashea siempre las contraseñas (nunca las cifres de forma reversible). Monitorea pg_stat_activity para auditar las consultas activas. Combina estas herramientas para una defensa en profundidad.

postgresql
# encrypt columns with pgcrypto
CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE users (
  id serial PRIMARY KEY,
  email text,
  ssn bytea  -- encrypted
);

INSERT INTO users (email, ssn) VALUES
  ('[email protected]', pgp_sym_encrypt('123-45-6789', 'secret_key'));

SELECT pgp_sym_decrypt(ssn, 'secret_key') AS ssn FROM users;

# one-way hashing (passwords)
SELECT crypt('password', gen_salt('bf'));      -- bcrypt
SELECT crypt('password', gen_salt('md5'));     -- MD5 (weak)
-- store the hash, verify with:
SELECT crypt('password', stored_hash) = stored_hash;

# audit with pgAudit extension
CREATE EXTENSION pgaudit;
ALTER SYSTEM SET pgaudit.log = 'write, ddl';
SELECT pg_reload_conf();
-- logs all writes and DDL to the PostgreSQL log

# session auditing
SELECT
  pid, usename, application_name, client_addr,
  query_start, state, query
FROM pg_stat_activity
WHERE state = 'active';
15

Copia de seguridad y restauración

Fundamentos de pg_dump

pg_dump crea un respaldo lógico (sentencias SQL para recrear la base de datos). El formato custom (-F c) está comprimido y soporta restauración paralela y selectiva — prefiérelo siempre. El formato directorio (-F d) habilita el volcado y la restauración paralelos. pg_dumpall respalda todas las bases de datos más los roles y tablespaces (úsalo para respaldos completos del clúster). --clean --if-exists hace los volcados idempotentes (pueden restaurarse varias veces). Para bases de datos grandes, considera --jobs para el volcado paralelo (solo formato directorio).

postgresql
# backup a single database
pg_dump -h localhost -U postgres -d mydb -F c -f mydb.dump
# -F c: custom format (compressed, supports parallel restore)
# -F p: plain SQL (human-readable, default)
# -F d: directory format (parallel, multiple files)

# backup with options
pg_dump -d mydb -F c -f mydb.dump \
  --no-owner           -- don't include ownership commands
  --no-privileges      -- don't include GRANT/REVOKE
  --schema=app_schema  -- only this schema
  --table=users        -- only this table
  --data-only          -- only data, no schema
  --schema-only        -- only schema, no data
  --clean              -- include DROP statements
  --if-exists          -- use IF EXISTS in DROPs

# backup all databases
pg_dumpall -h localhost -U postgres -f all_databases.sql
# includes roles, tablespaces, and all databases

# compress a plain SQL dump
pg_dump -d mydb | gzip > mydb.sql.gz

# backup a remote database
pg_dump "postgresql://user:pass@remote-host:5432/mydb" -F c -f mydb.dump

pg_restore

pg_restore restaura desde los formatos custom/directorio (no SQL plano — para eso usa psql). --jobs=N habilita la restauración paralela (mucho más rápida en multinúcleo). --clean --if-exists elimina primero los objetos existentes. --no-owner es esencial al restaurar a un usuario distinto. --list muestra el contenido del volcado; usa --table/--schema para la restauración selectiva. Para restauraciones grandes, deshabilita triggers y restricciones temporalmente y luego reactiva. Prueba siempre las restauraciones — un respaldo que no puedes restaurar no vale nada.

postgresql
# restore from custom format
pg_restore -h localhost -U postgres -d mydb -F c mydb.dump

# restore with options
pg_restore -d mydb mydb.dump \
  --clean              -- drop objects before recreating
  --if-exists          -- use IF EXISTS in DROPs
  --no-owner           -- don't restore ownership
  --schema=app_schema  -- only this schema
  --table=users        -- only this table
  --data-only          -- only data
  --jobs=4             -- parallel restore (4 threads)
  --verbose

# restore to a new database
createdb newdb
pg_restore -d newdb mydb.dump

# restore from plain SQL
psql -d mydb -f mydb.sql
gunzip -c mydb.sql.gz | psql -d mydb

# list contents of a dump
pg_restore -l mydb.dump

# restore specific items
pg_restore -d mydb mydb.dump --table=users --table=posts

# generate SQL to inspect (without restoring)
pg_restore mydb.dump > mydb.sql
pg_restore --list mydb.dump  -- list items with indices

Respaldo físico y PITR

pg_basebackup crea una copia binaria del directorio de datos completo — más rápida que pg_dump para bases de datos grandes y soporta PITR. El archivado de WAL (Write-Ahead Log) es esencial para PITR: archiva cada archivo WAL, y luego restaura el respaldo base + reproduce el WAL hasta cualquier punto en el tiempo. recovery_target_time te permite recuperar a una marca de tiempo concreta (deshacer errores como un DROP TABLE accidental). pg_create_restore_point crea un objetivo de recuperación con nombre. PITR es el estándar de oro de los respaldos — combínalo con pg_dump para respaldos lógicos.

postgresql
# physical backup with pg_basebackup (online)
pg_basebackup -h localhost -U repluser -D /backup/base \
  -Ft -z -P          -- tar format, compressed, progress
  --wal-method=stream  -- stream WAL files separately

# set up WAL archiving (postgresql.conf)
#   archive_mode = on
#   archive_command = 'cp %p /archive/%f'
SELECT pg_reload_conf();

# Point-in-Time Recovery (PITR):
# 1. Restore base backup
# 2. Create recovery.signal file
# 3. Configure recovery (postgresql.conf or recovery.conf):
#      restore_command = 'cp /archive/%f %p'
#      recovery_target_time = '2025-01-15 14:30:00'
#      recovery_target_action = 'promote'
# 4. Start PostgreSQL — it replays WAL to the target time

# create a recovery point
SELECT pg_create_restore_point('before_migration');

# check WAL archiving status
SELECT * FROM pg_stat_archiver;

Configuración de replicación

La replicación por streaming: el primario envía WAL a las réplicas en tiempo real. pg_basebackup -R configura una réplica en un comando. Las réplicas son de solo lectura (hot standby) y pueden servir consultas de lectura. Monitorea el retraso de replicación (pg_stat_replication en el primario, pg_last_xact_replay_timestamp en la réplica). La promoción (pg_ctl promote) convierte una réplica en primaria — úsala para failover. Para failover automático, usa herramientas como Patroni, repmgr o pg_auto_failover. La replicación en cascada reduce la carga del primario encadenando réplicas.

postgresql
# on primary (postgresql.conf):
#   wal_level = replica
#   max_wal_senders = 10
#   hot_standby = on  (for replica)

# create replication role
CREATE ROLE repluser WITH REPLICATION LOGIN PASSWORD 'secret';

# in pg_hba.conf, allow replication connections:
#   host replication repluser 0.0.0.0/0 scram-sha-256

# on replica, create standby using pg_basebackup:
pg_basebackup -h primary-host -U repluser -D /var/lib/postgresql/data \
  -Fp -Xs -P -R
# -R creates standby.signal and configures primary_conninfo

# monitor replication
# on primary:
SELECT * FROM pg_stat_replication;
SELECT * FROM pg_current_wal_lsn();

# on replica:
SELECT * FROM pg_stat_wal_receiver;
SELECT now() - pg_last_xact_replay_timestamp() AS replication_lag;

# promote a replica to primary (failover)
pg_ctl promote -D /var/lib/postgresql/data

# cascading replication
#   replica can also be a primary to other replicas
#   set cascade_level or use primary_conninfo pointing to another replica

Replicación lógica

La replicación lógica (PG10+) replica cambios a nivel de tabla — más flexible que la física: distintas versiones de PostgreSQL, tablas selectivas, esquemas distintos en cada lado. El publicador crea una PUBLICACIÓN; el suscriptor crea una SUSCRIPCIÓN. Los datos iniciales se copian y luego los cambios llegan en streaming en tiempo real. Los conflictos (por ejemplo, INSERT con PK existente) detienen la replicación — manéjalos con SKIP o arreglos manuales. Genial para actualizaciones sin tiempo de inactividad (replicar de la versión antigua a la nueva y luego cambiar). Úsala para compartir datos entre bases de datos o replicación parcial.

postgresql
# logical replication (PG10+) — replicate specific tables
# on publisher:
CREATE PUBLICATION my_pub FOR TABLE users, posts;

# add more tables to the publication
ALTER PUBLICATION my_pub ADD TABLE comments;

# on subscriber:
CREATE SUBSCRIPTION my_sub
  CONNECTION 'host=publisher-host user=repluser dbname=mydb'
  PUBLICATION my_pub;

# initial data copy happens automatically
# subsequent changes are replicated in real-time

# monitor logical replication
SELECT * FROM pg_stat_subscription;
SELECT * FROM pg_replication_slots;

# replicate only specific operations
CREATE PUBLICATION insert_only FOR TABLE logs WITH (publish = 'insert');

# control conflict resolution
# (subscriber applies changes; conflicts cause replication to stop)
# handle with: ALTER SUBSCRIPTION ... SKIP or manual resolution

# cross-version replication (e.g., PG14 -> PG16)
# logical replication works across major versions!

# remove
DROP SUBSCRIPTION my_sub;
DROP PUBLICATION my_pub;
16

Rendimiento y ajuste

EXPLAIN

EXPLAIN muestra el plan de consulta; ANALYZE corre la consulta y muestra los tiempos reales. Busca Seq Scan en tablas grandes (índice faltante), un alto Rows Removed by Filter (consulta inefectiva) y ordenaciones externas (aumenta work_mem). Index Only Scan es el más rápido (necesita un índice cubriente). Bitmap Scan está entre Seq Scan e Index Scan. Los números de 'cost' son estimaciones del planificador; 'actual time' es real. Usa siempre EXPLAIN (ANALYZE, BUFFERS) para ajustar — revela el verdadero cuello de botella. Compara filas estimadas contra reales para detectar estadísticas rancias.

postgresql
# basic EXPLAIN (query plan without running)
EXPLAIN SELECT * FROM users WHERE email = '[email protected]';

# EXPLAIN ANALYZE (actually runs the query, shows real stats)
EXPLAIN ANALYZE SELECT * FROM users JOIN posts ON users.id = posts.user_id;

# with buffer stats
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM large_table WHERE id = 42;

# with format (JSON, TEXT, XML, YAML)
EXPLAIN (FORMAT JSON) SELECT * FROM users;

# common plan nodes:
#   Seq Scan:        full table scan (slow for large tables)
#   Index Scan:      index lookup + heap fetch
#   Index Only Scan: index-only (fastest, needs covering index)
#   Bitmap Scan:     bitmap of matching rows, then fetch
#   Hash Join:       hash one table, probe with other
#   Nested Loop:     for each left row, scan right (small datasets)
#   Sort:            explicit sort (look for "Sort Method: external merge" = bad)

# warning signs:
#   "Seq Scan on large_table" -> missing index
#   "Sort Method: external disk" -> increase work_mem
#   "Rows Removed by Filter: 999999" -> bad filter, missing index

VACUUM y ANALYZE

El MVCC de PostgreSQL crea filas muertas en UPDATE/DELETE — VACUUM las recupera. autovacuum corre automáticamente (¡mantenlo activado!) pero puede necesitar ajuste para tablas con muchas escrituras. VACUUM FULL reescribe la tabla (recupera espacio para el sistema operativo) pero toma un lock exclusivo — usa pg_repack en su lugar para reorganizar en línea. ANALYZE actualiza las estadísticas que usa el planificador — crítico tras cargas masivas. Las estadísticas rancias causan planes de consulta malos. Ajusta autovacuum_scale_factor por tabla (menor para tablas grandes y con muchas escrituras). Monitorea n_dead_tup para detectar el retraso del vacuum.

postgresql
# VACUUM (reclaim space from dead rows, don't lock)
VACUUM users;            -- regular vacuum
VACUUM FULL users;       -- rewrites table (locks, reclaims to OS)
VACUUM ANALYZE users;    -- vacuum + update statistics
VACUUM (VERBOSE, ANALYZE) users;  -- with output

# autovacuum (should be ON in production)
SHOW autovacuum;  -- should be on
SHOW autovacuum_vacuum_threshold;     -- default 50
SHOW autovacuum_vacuum_scale_factor;  -- default 0.2 (20% dead rows)

# tune autovacuum per table
ALTER TABLE users SET (
  autovacuum_vacuum_scale_factor = 0.1,  -- vacuum at 10% dead rows
  autovacuum_analyze_scale_factor = 0.05
);

# ANALYZE (update planner statistics — critical for query plans)
ANALYZE users;         -- sample the table
ANALYZE;               -- all tables

# check dead tuples (need vacuuming)
SELECT relname, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

# check autovacuum activity
SELECT pid, datname, query
FROM pg_stat_activity
WHERE query LIKE '%autovacuum%';

Memoria y configuración

shared_buffers es la caché compartida de PostgreSQL (el 25% de la RAM es la recomendación clásica). effective_cache_size informa al planificador sobre la caché del sistema operativo (fjalo al 50-75% de la RAM, es solo una pista). work_mem es por operación de ordenación/hash — fijarlo demasiado alto con muchas conexiones puede agotar la memoria. maintenance_work_mem acelera VACUUM y CREATE INDEX. Para SSDs, fija random_page_cost=1.1 (el 4.0 por defecto asume HDDs lentos). Estos ajustes afectan dramáticamente al rendimiento — haz siempre benchmarks de los cambios.

postgresql
# key memory settings (in postgresql.conf)
SHOW shared_buffers;        -- default 128MB (set to 25% of RAM)
SHOW effective_cache_size;  -- default 4GB (set to 50-75% of RAM)
SHOW work_mem;              -- default 4MB (per-sort/hash memory)
SHOW maintenance_work_mem;  -- default 64MB (for VACUUM, CREATE INDEX)

# recommended production settings (example for 16GB RAM):
#   shared_buffers = 4GB
#   effective_cache_size = 12GB
#   work_mem = 64MB          (careful: per-operation, not global)
#   maintenance_work_mem = 512MB
#   max_connections = 100
#   random_page_cost = 1.1   (for SSDs; default 4.0 is for HDDs)

# check current connections vs max
SELECT count(*), state FROM pg_stat_activity GROUP BY state;
SHOW max_connections;

# WAL settings for write performance
SHOW wal_buffers;        -- default -1 (auto-tuned to 1/32 shared_buffers)
SHOW checkpoint_timeout; -- default 5min
SHOW max_wal_size;       -- default 1GB

# parallel query
SHOW max_parallel_workers;        -- default 8
SHOW max_parallel_workers_per_gather; -- default 2
SET max_parallel_workers_per_gather = 4;  -- more parallelism

# reload config
SELECT pg_reload_conf();

Optimización de consultas

Las mayores victorias: indexa las columnas FK, usa COPY para cargas masivas, evita OFFSET para paginar (usa keyset) y no envuelvas columnas indexadas en funciones. EXISTS a menudo es más rápido que IN para subconsultas. SELECT * impide los escaneos de solo índice y desperdicia I/O. Los inserts por lotes (multi-fila o COPY) son 10-100 veces más rápidos que los individuales. Tras cargas masivas, corre ANALYZE para que el planificador tenga estadísticas precisas. Haz siempre EXPLAIN (ANALYZE) antes y después de los cambios para verificar las mejoras.

postgresql
# 1. use indexes (check with EXPLAIN)
# bad:  SELECT * FROM users WHERE lower(email) = '[email protected]'
# good: SELECT * FROM users WHERE email = '[email protected]'
#   (or use an expression index on lower(email))

# 2. avoid SELECT * (reduces I/O, enables index-only scans)
SELECT id, username FROM users WHERE active = true;

# 3. use EXISTS instead of IN for subqueries
-- BAD:  SELECT * FROM users WHERE id IN (SELECT user_id FROM posts)
-- GOOD: SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM posts WHERE user_id = u.id)

# 4. batch inserts (much faster than row-by-row)
INSERT INTO logs (msg) VALUES ('a'), ('b'), ('c');  -- one statement
-- or use COPY for bulk loads:
COPY logs FROM '/path/to/file.csv' WITH (FORMAT csv);

# 5. use CTEs or subqueries for readability, but check performance
# (PG12+ inlines CTEs; before that, CTEs were optimization fences)

# 6. avoid OFFSET for pagination (slow for large offsets)
-- BAD:  SELECT * FROM posts ORDER BY id OFFSET 100000 LIMIT 10
-- GOOD: SELECT * FROM posts WHERE id > 100000 ORDER BY id LIMIT 10

# 7. use partial indexes for common filtered queries
CREATE INDEX idx_active_users ON users(last_login) WHERE active = true;

# 8. analyze after bulk loads
ANALYZE products;  -- update statistics for the planner

Monitorear consultas

pg_stat_statements es la herramienta de monitoreo más valiosa — rastrea conteos de consultas, tiempo total/medio y filas. Habilita en shared_preload_libraries. pg_stat_activity muestra las consultas en tiempo real (busca duraciones largas y 'idle in transaction'). Monitorea el porcentaje de tuplas muertas para el hinchado. Halla los índices sin usar (idx_scan=0) para eliminarlos — desperdician espacio y ralentizan las escrituras. Fja log_min_duration_statement para registrar automáticamente las consultas lentas. Combina estas herramientas con EXPLAIN para identificar y arreglar cuellos de botella sistemáticamente.

postgresql
# find slow queries (needs pg_stat_statements extension)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT
  query,
  calls,
  mean_exec_time AS avg_ms,
  total_exec_time AS total_ms,
  rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

# currently running queries
SELECT
  pid,
  now() - query_start AS duration,
  state,
  query
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY duration DESC;

# long-running transactions (block vacuum)
SELECT
  pid,
  now() - xact_start AS transaction_duration,
  state,
  query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start;

# table sizes (find bloat candidates)
SELECT
  relname,
  pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
  n_live_tup, n_dead_tup,
  round(n_dead_tup::numeric / NULLIF(n_live_tup, 0) * 100, 2) AS dead_pct
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 20;

# index usage (find unused indexes)
SELECT relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;
17

Particionamiento

Particionamiento por rango

El particionamiento por rango es ideal para series temporales. PostgreSQL enruta automáticamente los inserts a la partición correcta y poda particiones en las consultas (solo escanea las relevantes). Crea una partición DEFAULT para capturar los datos fuera de rango (o configura un trabajo de particionado para crear particiones futuras). Cada partición es una tabla separada — puedes gestionarlas independientemente (elimina datos viejos con DROP TABLE en lugar de un DELETE lento). El sub-particionamiento (rango y luego hash) funciona para datasets muy grandes. Automatiza la creación de particiones con la extensión pg_partman.

postgresql
# partition by date range (most common for time-series)
CREATE TABLE orders (
  id          bigserial,
  created_at  timestamptz NOT NULL,
  user_id     integer NOT NULL,
  amount      numeric
) PARTITION BY RANGE (created_at);

# create monthly partitions
CREATE TABLE orders_2025_01 PARTITION OF orders
  FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

CREATE TABLE orders_2025_02 PARTITION OF orders
  FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');

# create a default partition (catches anything outside range)
CREATE TABLE orders_default PARTITION OF orders DEFAULT;

# insert (automatically routed to correct partition)
INSERT INTO orders (created_at, user_id, amount)
VALUES ('2025-01-15', 1, 100.00);  -- goes to orders_2025_01

# query (partition pruning — only scans relevant partitions)
SELECT * FROM orders WHERE created_at >= '2025-01-01' AND created_at < '2025-02-01';

# sub-partition (e.g., range by date, then hash by user)
CREATE TABLE orders_2025_01 PARTITION OF orders
  FOR VALUES FROM ('2025-01-01') TO ('2025-02-01')
  PARTITION BY HASH (user_id);

Particionamiento por lista

El particionamiento por lista es para valores discretos (regiones, categorías, IDs de inquilino). Cada partición guarda las filas de valores concretos. Como el por rango, PostgreSQL poda particiones en las consultas. Esto es genial para apps multi-inquilino — los datos de cada inquilino están aislados, pueden respaldarse/restaurarse independientemente y pueden moverse a otro almacenamiento. Usa DEFAULT para valores inesperados. Para añadir una partición nueva para un valor nuevo, CREATE TABLE ... FOR VALUES IN (...). Desacopla una partición con ALTER TABLE ... DETACH PARTITION (útil para archivar).

postgresql
# partition by discrete values (e.g., region, category)
CREATE TABLE users (
  id       serial,
  name     text,
  region   text NOT NULL
) PARTITION BY LIST (region);

CREATE TABLE users_us PARTITION OF users
  FOR VALUES IN ('US', 'CA');

CREATE TABLE users_eu PARTITION OF users
  FOR VALUES IN ('UK', 'DE', 'FR', 'ES');

CREATE TABLE users_apac PARTITION OF users
  FOR VALUES IN ('CN', 'JP', 'AU', 'SG');

CREATE TABLE users_other PARTITION OF users DEFAULT;

# insert routes automatically
INSERT INTO users (name, region) VALUES ('Alice', 'US');  -- users_us

# query prunes to relevant partition
SELECT * FROM users WHERE region = 'JP';  -- only scans users_apac

# multi-region apps benefit: partition by tenant
CREATE TABLE tenant_data (
  id          serial,
  tenant_id   integer NOT NULL,
  data        jsonb
) PARTITION BY LIST (tenant_id);

CREATE TABLE tenant_1 PARTITION OF tenant_data FOR VALUES IN (1);
CREATE TABLE tenant_2 PARTITION OF tenant_data FOR VALUES IN (2);

Particionamiento por hash

El particionamiento por hash distribuye las filas uniformemente entre particiones hasheando la clave de partición — genial para repartir carga. Las consultas que filtran por la clave de partición (WHERE user_id = 42) podan a una partición; las de rango (WHERE user_id > 100) escanean todas. El número de particiones queda fijo al crear (el módulo debe ser potencia de 2) — añadir más después exige recrear la tabla. Elige el conteo de particiones según el tamaño de datos esperado. Usa el particionamiento por hash cuando necesites distribución uniforme y consultes siempre por la clave de partición.

postgresql
# partition by hash (distribute evenly)
CREATE TABLE events (
  id          bigserial,
  user_id     integer NOT NULL,
  event_type  text,
  data        jsonb
) PARTITION BY HASH (user_id);

# create 4 partitions (modulus 4, remainder 0-3)
CREATE TABLE events_0 PARTITION OF events
  FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE events_1 PARTITION OF events
  FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE events_2 PARTITION OF events
  FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE events_3 PARTITION OF events
  FOR VALUES WITH (MODULUS 4, REMAINDER 3);

# insert distributes by hash of user_id
INSERT INTO events (user_id, event_type) VALUES (42, 'login');  -- routes to a partition

# query by user_id prunes to one partition
SELECT * FROM events WHERE user_id = 42;

# note: range queries on user_id scan ALL partitions
SELECT * FROM events WHERE user_id > 100;  -- scans all 4

# adding partitions later requires recreating (modulus must be power of 2)
# plan partition count upfront!

Mantenimiento de particiones

DETACH PARTITION convierte una partición en una tabla independiente — perfecto para archivar datos viejos sin bloquear al padre. ATTACH PARTITION añade una tabla existente como partición nueva (exige esquema y restricciones coincidentes). Usa tablespaces para el almacenamiento por niveles (mueve particiones viejas a discos más baratos). pg_partman automatiza la creación y mantenimiento de particiones — esencial para series temporales. La exclusión de restricciones (fjada a 'partition' por defecto) es el mecanismo de poda de particiones. Planifica siempre la creación de particiones con antelación — quedarte sin particiones futuras causa fallos de INSERT.

postgresql
# detach a partition (keep data, remove from parent)
ALTER TABLE orders DETACH PARTITION orders_2024_01;
-- now orders_2024_01 is a standalone table

# detach and archive
ALTER TABLE orders DETACH PARTITION orders_2024_01;
ALTER TABLE orders_2024_01 RENAME TO orders_archive_2024_01;
-- move to cheaper storage, export, etc.

# attach an existing table as a partition
CREATE TABLE orders_2025_03 (LIKE orders);
ALTER TABLE orders ATTACH PARTITION orders_2025_03
  FOR VALUES FROM ('2025-03-01') TO ('2025-04-01');

# move partition to different tablespace (tiered storage)
CREATE TABLESPACE cold_storage LOCATION '/mnt/cold';
ALTER TABLE orders_2024_01 SET TABLESPACE cold_storage;

# create future partitions automatically (pg_partman extension)
CREATE EXTENSION pg_partman;
SELECT partman.create_parent('public.orders', 'created_at', 'native', 'monthly');
-- pg_partman auto-creates future partitions

# constraint exclusion (older partitioning, avoid for new work)
SET constraint_exclusion = on;  -- default 'partition' is usually fine

# check partition info
SELECT
  inhrelid::regclass AS partition,
  pg_get_expr(c.relpartbound, c.oid) AS partition_bounds
FROM pg_inherits
JOIN pg_class c ON inhrelid = c.oid
WHERE inhparent = 'orders'::regclass;

Estrategia de particionamiento

Particiona cuando las tablas se hagan grandes (>100GB) o cuando necesites un ciclo de vida rápido de datos (eliminar particiones viejas en lugar de DELETE). Elige la clave de partición según tu patrón de consulta más común — la poda de particiones solo funciona si filtras por la clave de partición. Las restricciones únicas sobre tablas particionadas DEBEN incluir la clave de partición (PG lo impone). Los índices sobre tablas particionadas se aplican automáticamente a todas las particiones. Evita demasiadas particiones (sobrecarga de planificación). Para series temporales, las particiones mensuales son un punto dulce común. Usa pg_partman para la automatización.

postgresql
# when to partition:
#   - table > 100GB (hard to vacuum, slow queries)
#   - time-series data (drop old data fast)
#   - multi-tenant (isolate tenants)
#   - parallel maintenance (vacuum per partition)

# choosing partition key:
#   - most common filter in queries (enables pruning)
#   - time for time-series (created_at, ordered_at)
#   - tenant_id for multi-tenant

# choosing partition type:
#   - RANGE: time-series, ordered data
#   - LIST: discrete categories (region, tenant)
#   - HASH: even distribution, always query by key

# partition count:
#   - too few:  doesn't help (large partitions)
#   - too many: planning overhead (1000+ partitions slow planning)
#   - sweet spot: 50-500 partitions

# indexes on partitioned tables
CREATE INDEX idx_orders_user ON orders(user_id);  -- creates index on all partitions
CREATE INDEX idx_orders_created ON orders(created_at);

# unique constraint must include partition key
CREATE TABLE orders (...) PARTITION BY RANGE (created_at);
ALTER TABLE orders ADD CONSTRAINT unique_order
  UNIQUE (id, created_at);  -- must include created_at!

# foreign keys TO partitioned tables (PG12+)
ALTER TABLE order_items ADD FOREIGN KEY (order_id, order_date)
  REFERENCES orders(id, created_at);
18

Características avanzadas

Búsqueda de texto completo

PostgreSQL tiene búsqueda de texto completo integrada: tsvector (texto preprocesado), tsquery (términos de búsqueda) y el operador @@. Los índices GIN hacen las búsquedas rápidas. to_tsvector tokeniza y reduce palabras (inglés, chino, etc. — configura el idioma). ts_rank puntúa los resultados; ts_headline resalta las coincidencias. El tsvector_update_trigger mantiene automáticamente la columna de búsqueda. Para búsqueda simple, compite con Elasticsearch — sin servicio externo. Para búsqueda compleja a escala, considera Elasticsearch/OpenSearch, pero empieza con el FTS de PostgreSQL.

postgresql
# create a search column (tsvector)
ALTER TABLE articles ADD COLUMN search_vector tsvector;

UPDATE articles SET search_vector =
  to_tsvector('english', title || ' ' || body);

# index the search vector (GIN)
CREATE INDEX idx_articles_search ON articles USING GIN (search_vector);

# search
SELECT * FROM articles
WHERE search_vector @@ to_tsquery('english', 'postgres & index');

# match any word (OR)
SELECT * FROM articles
WHERE search_vector @@ to_tsquery('postgres | mysql');

# phrase search
SELECT * FROM articles
WHERE search_vector @@ phraseto_tsquery('full text search');

# rank results
SELECT
  title,
  ts_rank(search_vector, query) AS rank
FROM articles, to_tsquery('postgres') query
WHERE search_vector @@ query
ORDER BY rank DESC;

# highlight matches
SELECT
  ts_headline('english', body, query) AS highlighted
FROM articles, to_tsquery('postgres') query
WHERE search_vector @@ query;

# auto-update search_vector with a trigger
CREATE TRIGGER articles_search_update
  BEFORE INSERT OR UPDATE ON articles
  FOR EACH ROW EXECUTE FUNCTION
  tsvector_update_trigger(search_vector, 'pg_catalog.english', title, body);

Consultas JSONB avanzadas

Las expresiones de ruta JSONB (PG12+) usan el lenguaje de rutas SQL/JSON ($) para consultas potentes — filtros, predicados y extracción en una sola expresión. jsonb_agg y jsonb_build_object construyen JSON a partir de datos relacionales — genial para respuestas de API. El índice GIN jsonb_path_ops es menor y más rápido que el GIN por defecto pero solo soporta @> y los operadores de ruta (@?, @@). Usa jsonb_path_query para extraer elementos de array coincidentes, y jsonb_agg para construir arrays JSON desde filas — perfecto para generar respuestas de API directamente en SQL sin un ORM.

postgresql
# JSONB path queries
SELECT data#>>'{address,city}' FROM users;  -- text at path

# JSON Path (PG12+) — powerful JSON querying
SELECT * FROM products
WHERE data @? '$.tags[*] ? (@ == "sale")';

# find products with price > 100 in JSON
SELECT * FROM products
WHERE data @? '$.price ? (@ > 100)';

# extract values with JSON Path
SELECT jsonb_path_query(data, '$.tags[*]') FROM products;

# JSONB aggregation (build JSON from rows)
SELECT
  jsonb_agg(jsonb_build_object('id', id, 'name', name)) AS products
FROM products
WHERE category = 'electronics';

# group by JSON field
SELECT
  data->>'category' AS category,
  count(*) AS count
FROM products
GROUP BY data->>'category'
ORDER BY count DESC;

# JSONB containment and existence
SELECT * FROM products WHERE data @> '{"color": "red"}';
SELECT * FROM products WHERE data ? 'discount';
SELECT * FROM products WHERE data ?| ARRAY['discount', 'sale'];
SELECT * FROM products WHERE data ?& ARRAY['discount', 'sale'];

# JSONB with GIN index for fast queries
CREATE INDEX idx_products_data ON products USING GIN (data jsonb_path_ops);
# jsonb_path_ops: smaller index, supports @> and path operators

Geoespacial con PostGIS

PostGIS convierte a PostgreSQL en una base de datos espacial completa — añádelo con CREATE EXTENSION. Usa geography para coordenadas GPS (lat/lng) y geometry para coordenadas proyectadas. ST_DWithin halla puntos dentro de una distancia; ST_Distance mide con precisión. El operador <-> habilita consultas KNN (vecino más cercano) que usan el índice GiST eficientemente. Crea siempre un índice GiST sobre las columnas geometry/geography. Ojo, ST_MakePoint toma (lng, lat) — ¡longitud primero! Para cargas geoespaciales serias, PostGIS compite con sistemas GIS dedicados.

postgresql
-- enable the PostGIS extension
CREATE EXTENSION IF NOT EXISTS postgis;

-- create a table with geography (lat/lng)
CREATE TABLE places (
  id serial PRIMARY KEY,
  name text,
  location geography(POINT, 4326)
);

-- insert a point (lng, lat — note the order!)
INSERT INTO places (name, location)
VALUES ('Eiffel Tower', ST_MakePoint(2.2945, 48.8584)::geography);

-- find places within 5 km
SELECT name, ST_Distance(location, ST_MakePoint(2.3522, 48.8566)::geography) AS meters
FROM places
WHERE ST_DWithin(location, ST_MakePoint(2.3522, 48.8566)::geography, 5000);

-- bounding box query (uses GiST index)
SELECT name FROM places
WHERE location && ST_MakeEnvelope(2.2, 48.8, 2.4, 48.9, 4326);

-- find the nearest 10 places (KNN query)
SELECT name, location <-> ST_MakePoint(2.35, 48.85)::geography AS dist
FROM places
ORDER BY dist
LIMIT 10;

-- compute the area of a polygon (in square meters)
SELECT ST_Area(ST_GeomFromText(
  'POLYGON((2.3 48.8, 2.4 48.8, 2.4 48.9, 2.3 48.9, 2.3 48.8))', 4326)::geography
) AS sq_meters;

Foreign Data Wrappers (FDW)

Los Foreign Data Wrappers (FDW) te permiten consultar fuentes de datos externas como si fueran tablas locales. postgres_fdw se conecta a otros servidores PostgreSQL; file_fdw lee archivos CSV. Existen otros FDWs para MySQL, Oracle, SQLite, APIs REST, etc. Las consultas se empujan al servidor remoto cuando es posible (WHERE, JOIN), pero los joins entre servidores traen los datos localmente. FDW es genial para integración de datos, migraciones y consultas federadas sin ETL. El rendimiento es más lento que las tablas locales por la sobrecarga de red.

postgresql
-- enable the postgres_fdw extension (foreign PostgreSQL)
CREATE EXTENSION IF NOT EXISTS postgres_fdw;

-- create a foreign server
CREATE SERVER foreign_db
  FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (host 'remote.host', dbname 'remotedb', port '5432');

-- map a local user to the foreign server
CREATE USER MAPPING FOR local_user
  SERVER foreign_db
  OPTIONS (user 'remote_user', password 'secret');

-- import a schema from the foreign server
IMPORT FOREIGN SCHEMA public
  FROM SERVER foreign_db
  INTO remote_schema;

-- query a foreign table like a local one
SELECT * FROM remote_schema.users WHERE active = true;

-- file_fdw: read CSV files as tables
CREATE EXTENSION file_fdw;
CREATE SERVER csv_server FOREIGN DATA WRAPPER file_fdw;
CREATE FOREIGN TABLE logs (
  id int, level text, message text, ts timestamp
) SERVER csv_server
  OPTIONS (filename '/var/log/app.csv', format 'csv', header 'true');

-- join local and remote data
SELECT l.id, u.name FROM local_orders l
  JOIN remote_schema.users u ON l.user_id = u.id;

Advisory locks y concurrencia

Los advisory locks son locks definidos por la aplicación no atados a ninguna fila de tabla — perfectos para coordinar. Los locks de sesión persisten hasta liberarse explícitamente o terminar la sesión; los de transacción se liberan solos en COMMIT/ROLLBACK. pg_try_advisory_lock no bloquea — genial para los patrones de 'solo un worker'. Combínalo con FOR UPDATE SKIP LOCKED para el procesado seguro de colas de trabajos. Los locks se identifican por bigint o dos enteros. Emparéja siempre el lock con el unlock en los caminos de error. Los advisory locks son la columna vertebral de los sistemas seguros de trabajos en segundo plano concurrentes en PostgreSQL.

postgresql
-- acquire a session-level advisory lock (held until session ends)
SELECT pg_advisory_lock(12345);

-- release it
SELECT pg_advisory_unlock(12345);

-- transaction-level lock (auto-released on COMMIT/ROLLBACK)
BEGIN;
SELECT pg_advisory_xact_lock(67890);
-- critical section here
COMMIT;  -- lock auto-released

-- try-lock (non-blocking, returns true/false)
SELECT pg_try_advisory_lock(12345);  -- true if acquired, false if held

-- two-key variant (namespace + id)
SELECT pg_advisory_lock(100, 42);

-- use case: ensure only one worker runs a job
SELECT CASE
  WHEN pg_try_advisory_lock(999) THEN run_job()
  ELSE 'skipped: another worker is running'
END;

-- use case: per-row processing lock
SELECT id, pg_advisory_lock(hashtext(id::text))
FROM tasks WHERE status = 'pending'
LIMIT 10 FOR UPDATE SKIP LOCKED;
19

Extensiones

Gestión de extensiones

Las extensiones añaden funcionalidad más allá del núcleo de PostgreSQL — instálalas con CREATE EXTENSION. pg_available_extensions muestra qué hay instalado en disco; pg_extension qué está cargado. Las extensiones deben instalarse por base de datos. Algunas (como pg_stat_statements) también necesitan una entrada de configuración en shared_preload_libraries. CASCADE elimina los objetos dependientes. Fja siempre las versiones de extensiones en producción por reproducibilidad. Las extensiones son una fortaleza clave de PostgreSQL — lo convierten en una plataforma, no solo una base de datos.

postgresql
-- list all available extensions
SELECT * FROM pg_available_extensions ORDER BY name;

-- list installed extensions
SELECT * FROM pg_extension;

-- install an extension (requires superuser or privileges)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- install in a specific schema
CREATE EXTENSION postgis SCHEMA geo;

-- update an extension to a new version
ALTER EXTENSION pg_stat_statements UPDATE TO '1.10';

-- remove an extension
DROP EXTENSION IF EXISTS postgis CASCADE;

-- check extension versions
SELECT name, default_version, installed_version
FROM pg_available_extensions
WHERE installed_version IS NOT NULL;

pg_stat_statements (perfilar consultas)

pg_stat_statements es la extensión de PostgreSQL más valiosa — registra estadísticas de ejecución para cada consulta. Halla las consultas lentas por total_exec_time (impacto) o mean_exec_time (coste por consulta). Un rows/call alto sugiere índices faltantes. La extensión normaliza las consultas (reemplazando literales por $1) para que las consultas similares se agreguen. Reinicia las estadísticas tras los despliegues para seguir el rendimiento fresco. Es la primera herramienta a la que recurrir al optimizar — muestra exactamente a dónde va el tiempo.

postgresql
-- enable in postgresql.conf first:
-- shared_preload_libraries = 'pg_stat_statements'
-- then restart PostgreSQL

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- find the slowest queries by total time
SELECT
  query,
  calls,
  round(total_exec_time::numeric, 2) AS total_ms,
  round(mean_exec_time::numeric, 2) AS avg_ms,
  round(max_exec_time::numeric, 2) AS max_ms,
  rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- find queries with highest average time
SELECT query, calls, round(mean_exec_time, 2) AS avg_ms
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

-- find queries that scan the most rows
SELECT query, rows, calls, rows / calls AS avg_rows
FROM pg_stat_statements
ORDER BY rows DESC
LIMIT 10;

-- reset stats (e.g., after a deploy)
SELECT pg_stat_statements_reset();

pgcrypto (cifrado y hasheo)

pgcrypto aporta hasheo (digest, HMAC), generación de UUID (gen_random_uuid) y cifrado (PGP simétrico/asimétrico). gen_random_uuid() es la forma estándar de generar UUID v4 (integrado desde PG13, pero pgcrypto lo aporta para versiones antiguas). Para almacenar contraseñas, prefiere bcrypt o argon2 en el código de la aplicación — el hasheo de pgcrypto es para la integridad de los datos, no para contraseñas. El cifrado PGP es adecuado para cifrar columnas en reposo. Gestiona siempre las claves de cifrado fuera de la base de datos en producción.

postgresql
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- hash a password with SHA-256
SELECT encode(digest('mypassword', 'sha256'), 'hex');

-- generate a UUID v4
SELECT gen_random_uuid();

-- HMAC (keyed hashing)
SELECT encode(hmac('message', 'secretkey', 'sha256'), 'hex');

-- encrypt data with PGP symmetric encryption
SELECT armor(pgp_sym_encrypt('sensitive data', 'my_passphrase'))
  AS encrypted_blob;

-- decrypt data
SELECT pgp_sym_decrypt(
  dearmor('-----BEGIN PGP MESSAGE-----...-----END PGP MESSAGE-----'),
  'my_passphrase'
) AS decrypted;

-- store encrypted columns
CREATE TABLE secrets (
  id serial PRIMARY KEY,
  data bytea  -- store pgp_sym_encrypt() output here
);

INSERT INTO secrets (data)
VALUES (pgp_sym_encrypt('credit card number', 'passphrase'));

UUID y generación de IDs

Usa gen_random_uuid() (PG13+) para UUIDs aleatorios — está integrado y no exige extensión. uuid-ossp aporta UUIDs v1 (basados en tiempo) y v5 (espacio de nombres) si los necesitas. Para enteros autoincrementales, prefiere GENERATED ALWAYS AS IDENTITY (PG10+) sobre serial — es estándar SQL, evita los inserts manuales accidentales y maneja los permisos limpiamente. RETURNING id obtiene el ID insertado en un solo viaje. Los UUIDs son geniales para sistemas distribuidos; las columnas de identidad son más simples para apps de un solo nodo.

postgresql
-- built-in UUID v4 (PostgreSQL 13+)
SELECT gen_random_uuid();

-- using uuid-ossp extension (for other UUID versions)
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
SELECT uuid_generate_v1();  -- time-based
SELECT uuid_generate_v4();  -- random
SELECT uuid_generate_v5('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11', 'name'); -- namespace+name

-- UUID primary key
CREATE TABLE events (
  id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
  data jsonb,
  created_at timestamptz DEFAULT now()
);

-- identity columns (PostgreSQL 10+, preferred over serial)
CREATE TABLE users (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text NOT NULL
);

-- compare: serial vs identity
-- serial: old, separate sequence, can be overridden
-- identity: SQL standard, tied to table, cleaner

-- get the last inserted ID
INSERT INTO users (name) VALUES ('Alice') RETURNING id;

Catálogo de extensiones útiles

El ecosistema de extensiones de PostgreSQL es vasto. pg_trgm habilita la coincidencia difusa de texto (operador %) y consultas LIKE rápidas. PostGIS añade capacidades geoespaciales. pg_partman automatiza la creación de particiones para series temporales. TimescaleDB y Citus son extensiones de terceros para series temporales y escalado horizontal respectivamente. hstore es herencia — usa jsonb en su lugar. unaccent quita los diacríticos para la búsqueda internacionalizada. Explora las extensiones disponibles con pg_available_extensions, y revisa el PGXN (PostgreSQL Extension Network) para extensiones de la comunidad.

postgresql
-- PostGIS: geospatial data
CREATE EXTENSION postgis;

-- pg_trgm: trigram fuzzy text search
CREATE EXTENSION pg_trgm;
SELECT * FROM users WHERE name % 'Jon';  -- fuzzy match
CREATE INDEX idx_users_name_trgm ON users USING GIN (name gin_trgm_ops);

-- btree_gin / btree_gist: B-tree support in GIN/GiST
CREATE EXTENSION btree_gin;

-- hstore: key-value store (legacy, use jsonb instead)
CREATE EXTENSION hstore;

-- pg_partman: partition management
CREATE EXTENSION pg_partman;

-- timescaledb: time-series optimization (third-party)
-- citus: distributed PostgreSQL (third-party)

-- intarray: integer array operations
CREATE EXTENSION intarray;
SELECT * FROM posts WHERE tags && ARRAY[1,2,3];

-- unaccent: remove accents for search
CREATE EXTENSION unaccent;
SELECT unaccent('café');  -- 'cafe'
20

Monitoreo y solución de problemas

Consultas activas (pg_stat_activity)

pg_stat_activity es tu vista en tiempo real de lo que hace PostgreSQL. Comprueba el state (active, idle, idle in transaction). Las consultas de larga duración y las sesiones idle-in-transaction son problemas comunes — estas últimas sostienen locks e impiden el vacuum. pg_cancel_backend envía un SIGINT (la consulta se cancela, la sesión sobrevive); pg_terminate_backend mata la sesión por completo. Investiga siempre antes de matar. Fja idle_in_transaction_session_timeout para matar automáticamente las transacciones atascadas. Monitorea los conteos de conexiones para no golpear max_connections.

postgresql
-- see all active queries
SELECT
  pid,
  usename,
  application_name,
  client_addr,
  state,
  wait_event_type,
  wait_event,
  query_start,
  now() - query_start AS duration,
  query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;

-- find long-running queries (> 5 minutes)
SELECT pid, now() - query_start AS duration, query
FROM pg_stat_activity
WHERE state = 'active'
  AND now() - query_start > interval '5 minutes';

-- count connections by state
SELECT state, count(*)
FROM pg_stat_activity
GROUP BY state;

-- terminate a runaway query (use with caution!)
SELECT pg_cancel_backend(12345);     -- cancel the query
SELECT pg_terminate_backend(12345);  -- kill the session

-- find idle-in-transaction sessions (hold locks)
SELECT pid, usename, xact_start,
  now() - xact_start AS xact_duration,
  query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY xact_start;

Locks y bloqueos

La contención de locks es un problema común de producción. pg_locks muestra la totalidad de los locks activos; los locks no concedidos significan que una sesión espera. La consulta de la cadena de bloqueo identifica exactamente quién bloquea a quién — esencial para depurar deadlocks y consultas lentas. AccessExclusive (de ALTER TABLE, DROP, VACUUM FULL) bloquea por completo — corre en ventanas de mantenimiento. Usa lock_timeout para evitar que las sesiones esperen para siempre. Las transacciones largas son el culpable habitual — mantén las transacciones cortas y confirma pronto.

postgresql
-- see all locks
SELECT
  pid,
  relation::regclass AS table_name,
  mode,
  granted,
  query
FROM pg_locks
WHERE granted = false;

-- find blocking chains (who blocks whom)
SELECT
  blocked.pid AS blocked_pid,
  blocked.query AS blocked_query,
  blocking.pid AS blocking_pid,
  blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_locks bl ON bl.pid = blocked.pid AND NOT bl.granted
JOIN pg_locks ul ON ul.locktype = bl.locktype
  AND ul.relation IS NOT DISTINCT FROM bl.relation
  AND ul.granted
JOIN pg_stat_activity blocking ON ul.pid = blocking.pid;

-- lock modes: AccessShare, RowShare, RowExclusive,
--   ShareUpdateExclusive, Share, ShareRowExclusive,
--   Exclusive, AccessExclusive

-- check for table-level locks
SELECT relation::regclass, mode, pid, granted
FROM pg_locks
WHERE locktype = 'relation' AND granted = false;

-- set a lock timeout for a session
SET lock_timeout = '5s';
SET statement_timeout = '30s';

Vacuum e hinchado

PostgreSQL usa MVCC — UPDATE/DELETE crean tuplas muertas que VACUUM recupera. Autovacuum corre automáticamente pero puede necesitar ajuste para tablas grandes o con muchas escrituras. n_dead_tup muestra la limpieza pendiente; un dead_pct alto significa que autovacuum no da abasto. VACUUM FULL recupera espacio de disco pero toma un lock AccessExclusive — usa pg_repack para la eliminación de hinchado en línea. Las transacciones largas bloquean el vacuum (pueden ver filas viejas), causando hinchado y riesgo de rebasamiento del ID de transacción. Monitorea xid_age para prevenir el rebasamiento (falla forzada cerca de los 2 mil millones).

postgresql
-- check table bloat (dead tuples)
SELECT
  relname,
  n_live_tup,
  n_dead_tup,
  round(n_dead_tup::numeric / NULLIF(n_live_tup, 0) * 100, 2) AS dead_pct,
  last_autovacuum,
  last_manual_vacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

-- manual vacuum (analyze updates planner stats)
VACUUM ANALYZE users;
VACUUM FULL users;  -- reclaims space, locks table!

-- check autovacuum settings
SHOW autovacuum;
SHOW autovacuum_vacuum_threshold;
SHOW autovacuum_vacuum_scale_factor;

-- tune autovacuum per table
ALTER TABLE big_table SET (
  autovacuum_vacuum_scale_factor = 0.05,
  autovacuum_analyze_scale_factor = 0.02
);

-- check if any transactions prevent vacuum
SELECT pid, age(clock_timestamp(), xact_start) AS xact_age, query
FROM pg_stat_activity
WHERE state IN ('idle in transaction', 'active')
ORDER BY xact_age DESC;

-- check wraparound risk (transaction ID age)
SELECT
  relname,
  age(relfrozenxid) AS xid_age
FROM pg_class
WHERE relkind IN ('r', 't', 'm')
ORDER BY xid_age DESC LIMIT 10;

Análisis de logs

El logging se configura en postgresql.conf. log_min_duration_statement registra las consultas lentas (0 para todas, 100 para >100ms). log_lock_waits atrapa la contención de locks. El logging CSV te permite consultar los logs como tabla foránea (file_fdw). Prefijos comunes: %t (marca de tiempo), %p (PID), %u (usuario), %d (base de datos). Para producción, usa un recolector de logs como pgBadger para parsear y visualizar. No registres cada consulta en producción (sobrecarga de I/O) — usa pg_stat_statements para el análisis agregado en su lugar.

postgresql
-- in postgresql.conf, enable slow query logging:
-- log_min_duration_statement = 100  -- log queries > 100ms
-- log_line_prefix = '%t [%p] %u@%d '
-- log_lock_waits = on
-- log_temp_files = 0
-- log_autovacuum_min_duration = 0

-- check current log settings
SHOW log_min_duration_statement;
SHOW log_destination;

-- view server logs (if using CSV logging)
CREATE FOREIGN TABLE pg_log (
  log_time timestamp,
  user_name text,
  database_name text,
  process_id int,
  connection_from text,
  session_id text,
  session_line_num bigint,
  command_tag text,
  session_start_time timestamp,
  virtual_transaction_id text,
  transaction_id bigint,
  error_severity text,
  sql_state_code text,
  message text,
  detail text,
  hint text,
  internal_query text,
  internal_query_pos int,
  context text,
  query text,
  query_pos int,
  location text,
  file_name text,
  file_line_num int,
  application_name text
) SERVER pglog
  OPTIONS (filename '/var/log/postgresql/postgres.csv', format 'csv');

-- query logs with SQL
SELECT log_time, error_severity, message
FROM pg_log
WHERE error_severity = 'ERROR'
ORDER BY log_time DESC LIMIT 20;

Comprobaciones de salud y métricas

Métricas de salud clave: ratio de aciertos de caché (apunta a >99% — si es bajo, aumenta shared_buffers), ratio de uso de índices (bajo significa índices faltantes o consultas malas), tamaños de tablas (atrapa el hinchado temprano) y conteos de conexiones. pg_stat_user_tables y pg_statio_user_tables son minas de oro de datos operativos. Configura el monitoreo con Prometheus + postgres_exporter para dashboards. Vigila: tuplas muertas creciendo, ratio de aciertos de caché declinando, conexiones aumentando y tablas creciendo más rápido de lo esperado. Las comprobaciones de salud regulares previenen emergencias de medianoche.

postgresql
-- database size
SELECT pg_size_pretty(pg_database_size('mydb'));

-- table sizes (with indexes)
SELECT
  relname,
  pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
  pg_size_pretty(pg_relation_size(relid)) AS table_size,
  pg_size_pretty(pg_indexes_size(relid)) AS index_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 10;

-- cache hit ratio (should be > 99%)
SELECT
  sum(heap_blks_hit) AS hits,
  sum(heap_blks_read) AS reads,
  round(sum(heap_blks_hit)::numeric /
    NULLIF(sum(heap_blks_hit) + sum(heap_blks_read), 0) * 100, 2) AS hit_ratio_pct
FROM pg_statio_user_tables;

-- index usage ratio
SELECT
  sum(idx_scan) AS index_scans,
  sum(seq_scan) AS seq_scans,
  round(sum(idx_scan)::numeric / NULLIF(sum(idx_scan) + sum(seq_scan), 0) * 100, 2) AS index_usage_pct
FROM pg_stat_user_tables;

-- connection stats
SELECT count(*) AS total,
  count(*) FILTER (WHERE state = 'active') AS active,
  count(*) FILTER (WHERE state = 'idle') AS idle,
  count(*) FILTER (WHERE state = 'idle in transaction') AS idle_in_txn
FROM pg_stat_activity;

-- uptime and version
SELECT version();
SELECT pg_postmaster_start_time();

Was this helpful?