Tengo una tabla OLTP ocupada con 30 columnas y 50 millones de filas y quiero evitar duplicados en ella.
¿Qué enfoque debo tomar?
Hasta ahora se me ocurrieron estos:
Con este último, siento que habrá muchas molestias para regenerar esa columna hash si cambia un esquema de tabla.
¿Tal vez hay algunos otros enfoques en los que no pensé?
... acaba de salir con una función hash incorporada para registros , que es sustancialmente más barata que mi función personalizada. ¡Especialmente para muchas columnas! Ver:
Eso hace que el índice de expresión sea mucho más atractivo que una columna más un índice generado. Por lo que sólo:
CREATE UNIQUE INDEX tbl_row_uni ON tbl (hash_record_extended(tbl.*,0));Esto normalmente funciona, también:
CREATE UNIQUE INDEX tbl_row_uni ON tbl (hash_record_extended(tbl,0)); Pero la primera variante es más segura. En la segunda variante, tbl se resolvería en la columna si existiera una columna con el mismo nombre.
Proporcioné una solución para ese problema exactamente en dba.SE recientemente:
Está bastante cerca de tu tercera idea:
Básicamente, un hash generado del lado del servidor muy eficiente colocado como columna 31 con restricción UNIQUE .
CREATE OR REPLACE FUNCTION public.f_tbl_bighash(col1 text, col2 text, ... , col30 text) RETURNS bigint LANGUAGE sql IMMUTABLE PARALLEL SAFE AS 'SELECT hashtextextended(textin(record_out(($1,$2, ... ,$30))), 0)'; ALTER TABLE tbl ADD COLUMN tbl_bighash bigint NOT NULL GENERATED ALWAYS AS (public.f_tbl_bighash(col1, col2, ... , col30)) STORED -- append column in last position , ADD CONSTRAINT tbl_bighash_uni UNIQUE (tbl_bighash); La belleza de esto: funciona de manera eficiente sin cambiar nada más. (Excepto, posiblemente, cuando use SELECT * o INSERT INTO sin lista de objetivos o similar).
Y también funciona para valores NULL (tratándolos como iguales).
Tenga cuidado si algún tipo de columna tiene una representación de texto no inmutable. (Como timestamptz ). La solución se prueba con todas las columnas de text .
Si el esquema de la tabla cambia , elimine primero la restricción UNIQUE , vuelva a crear la función y vuelva a crear la columna generada, idealmente con una sola declaración ALTER TABLE , para que no vuelva a escribir la tabla dos veces.
Alternativamente , use un índice de expresión UNIQUE basado en public.f_tbl_bighash() . Mismo efecto. Al revés: no hay columna de tabla adicional. Desventaja: un poco más caro, computacionalmente.