Convenciones de esquema para una base de datos PostgreSQL nueva: nombres, identificadores, marcas de tiempo y texto

Objetivo

Un esquema que se lea de forma coherente, que no necesite identificadores entrecomillados, que almacene el tiempo sin ambigüedad y que dé a cada tabla el mismo esqueleto, de modo que las migraciones, las consultas y el código generado sean predecibles.

Requisitos previos

Una herramienta de migración con archivos versionados, acuerdo sobre una única estrategia de id, y una página breve de convenciones en el repositorio frente a la cual se revisan las migraciones nuevas.

Pasos

1. Nombres: snake_case en minúsculas para tablas, columnas, índices y restricciones, sin depender nunca de las comillas. La documentación indica que los identificadores sin comillas se pliegan a minúsculas, mientras que los entrecomillados distinguen mayúsculas de minúsculas, de modo que un nombre con mayúsculas y minúsculas mezcladas obliga a entrecomillarlo siempre en cada consulta. Conviene mantenerse dentro del límite de 63 bytes para los identificadores, incluidos los nombres de índice generados.
2. Tablas: singular o plural, pero una única elección; las tablas de unión se nombran a partir de ambos lados (order_item). Nombrar las restricciones y los índices con un patrón fijo (orders_customer_id_fkey, orders_created_at_idx) para que los mensajes de error y los planes sean legibles.
3. Ids: bigint GENERATED ALWAYS AS IDENTITY donde los ids secuenciales sean aceptables, una columna UUID donde los ids los generen los clientes o no deban revelar el orden; nunca serial ni int. Una columna de clave foránea lleva el nombre de la tabla referenciada (customer_id) y recibe un índice.
4. Tiempo: timestamptz para cada instante; la documentación indica que el valor se almacena internamente en UTC y se muestra en la zona horaria de la sesión, de modo que es la zona horaria de la sesión, y no el código de la aplicación, la que decide cómo se muestra. timestamp a secas solo para valores de reloj de pared que deliberadamente carecen de zona; date para fechas. Toda tabla recibe created_at timestamptz NOT NULL DEFAULT now() y, donde las filas cambian, updated_at mantenido por la aplicación o por un trigger.
5. Texto: text, con un CHECK (length(x) <= n) cuando un límite importa, en lugar de varchar(n), y nunca char(n); la documentación indica que no hay diferencia de rendimiento entre los tres y que character(n) suele ser el más lento por el relleno.
6. Nulabilidad y valores por defecto: NOT NULL salvo que «desconocido» tenga un significado; booleanos NOT NULL DEFAULT false; vocabularios fijos y pequeños como text con un CHECK o una tabla de referencia en lugar de un tipo ENUM, que resulta más difícil de cambiar más adelante.
7. Dinero y cantidades: numeric con una escala explícita, nunca float; guardar la moneda junto al importe.
8. Escribir las reglas en la página de convenciones con una tabla de ejemplo, y añadir «sigue las convenciones de esquema» a la checklist de revisión de las migraciones.

Resultado esperado

Cada migración produce tablas con el mismo aspecto; las consultas y los mapeos de ORM no necesitan comillas ni conversiones de tipo; los valores de tiempo se comparan correctamente entre zonas.

Límites y base de verificación

Las convenciones son una síntesis del agente que las aporta; los hechos sobre el plegado de identificadores, el almacenamiento de timestamptz y los tipos de caracteres provienen de la documentación citada. Un esquema ya existente debería adoptarlas de forma gradual, en lugar de renombrarlo todo en una sola versión.

Claves internas e identificadores públicos

Usar bigint GENERATED ALWAYS AS IDENTITY como clave primaria y en toda clave foránea, y dar a las filas a las que se accede desde fuera del sistema un identificador público independiente: una columna uuid NOT NULL DEFAULT gen_random_uuid() o un token aleatorio, con su propio índice único. La clave interna se queda en ocho bytes y se inserta en orden; el identificador público es imposible de adivinar y puede sustituirse sin tocar las referencias. Elegir una clave primaria UUID solo cuando los clientes deban crear filas sin un viaje de ida y vuelta a la base de datos, y en ese caso preferir una ordenada en el tiempo (uuidv7() a partir de PostgreSQL 18) frente a una aleatoria, ya que las claves aleatorias dispersan las inserciones por todo el índice y hacen que crezca más rápido. Una clave ordenada en el tiempo revela el orden de creación, así que no sustituye al identificador público donde eso importa.