elementos desarrolladores - repositorio.siu.edu.ar
Post on 08-Jul-2022
9 Views
Preview:
TRANSCRIPT
SQL++Consideraciones para desarrolladores
Capacitación PostgreSQL
AgendaAlgunas ideas con tipos de datosNormalizaciónAlgo sobre estructura jerárquicas.Indización & trucos.Un touch de tsearch.SQL tips.Hábitos de Diseño y Buenas prácticas.Entendiendo el Explain.FTSXMLServidores de desarrolloCTELISTEN-NOTIFYHerencia
Tipos de datos
Use textMás eficiente y menos 'ruidoso'No más límites ficticios.
Trate de evitar los casteosSi sus consultas apestan de casteos, ud esta guardando los datos de una forma poco conveniente.
Trate de manipular datos con funcionesEvite el dolor de cabeza posterior de datos mal grabados.Evite que lo haga la aplicación. La idea es usar el servidor de varios u$s ;)
Consistencias
Fuerce la consistencia de sus datos a través de tipos y dominios propios:
CREATE DOMAIN nom_ape as TEXT CHECK (VALUE ~ $$\w(\s|),(\s|)\w$$);CREATE DOMAIN texto as TEXT CHECK (VALUE ~ ' '^\\w(\s|),(\s|)\\w$');SELECT 'l , l'::nom_ape;CREATE DOMAIN us_postal_code AS TEXTCHECK(VALUE ~ '^\\d{5}$'OR VALUE ~ '^\\d{5}-\\d{4}$');
Definiendo setsTrate de utilizar ENUM
Extendiendo mis tipos
Un workaround rebuscado
En 8.4 tenemos funciones más especializadas para manejo de arrays.
NormalizaciónRefresquemos la memoria....
1NFTodas las columnas deben contener valores escalares.
2NFTodas las columnas que no son llave, deben depender funcionalmente de la PK.
3NFTodas las columnas que no son llave, deben depender directamente de la PK.
DenormailizaciónDenormalice para enfrentar problemas de performance en consultas complejas.
Desventaja: Inserts, updates y deletes más lentos.Que porcentaje de cada sentencia DML hay en mi aplicación?
Utilizar por lo menos 2 días el log statement activado y luego parsear para obtener estos datos.
Por defecto 3NF, más normalización implica:Mayor complejidad.Menos redundancia.+ norm... +tablas .... = joins aborrecedoresMayor escalabilidad (relativa)
Joins
Left join suele ser más optimizable que INNER JOIN.Si sus consultas aborrecen de joins identicos entre sí, debería pensar en vistas materializadas o redundancia en una tabla separada.La cantidad de JOINS puede disparar GEQO u otros métodos de optimización.
Natural y Surrogate Keys
NK , relación natural con la tupla.DNI, matrícula
SKMuchas aplicaciones 3rd party la utilizan.La más famosa es 'id'Hace menos clara la lectura de consultas.No necesariamente los integer mejoran los JOINS ni reducen el I/O desde los archivos.Si se la utiliza, tratar de que sea invisible.Utilice nombres explícitos (actor_id, film_id).
Generación de SK
SERIAL es el maquillaje de las secuencias.
Tener cuidado en los dump/restore.
Evite querer limpiar 'huecos'SK están hechas solamente para dar unicidad.
Alternativas:UUID()Timestamp. WARNING cuando tenemos muchas transacciones concurrentes.
Foreign Keys
Siempre utilice REFERENCES.Siempre explicite las clausulas:
ON UPDATE CASCADEON DELETE RESTRICTON DELETE SET NULL
Modelo Adyacente
Guarde el id padre sobre la misma tabla.Favorece modificaciones en los árboles.Los self-joins pueden ser más performantes.
Consultas más lentasRequiere WITH RECURSIVE (incorporada a 8.4) o CONNECTBY (esta ultima viene con el contrib tablefunc)Rápido para update, lento para búsqueda.
Modelo Full Path
Guarda la ruta completa en cada hijo.Viola la teoría de normalización.
SELECT * from tabla WHERE ruta LIKE '1\23\45';Los movimientos de árboles solo requieren la modificación de la columna ruta.
UPDATE tablaSET ruta = replace(ruta,'/1/23/42','/1/5/19')WHERE ruta LIKE /1/23/42%';
Son lentos para UPDATE, rápidos para el SELECT.
Indización
Sobre-indexación
Todos los DML tocan los índices.BitMap y Multicolumn
BM utiliza mayor memoriaMC implica utilizar un indice por cada columna.
Asegurece de que utiliza los índices.pg_stat_all_indexes.
En vez de utilizar muchos índices, es preferible recurrir a parámetros de almacenamiento de TOAST.Prefiera aumentar la performance con índices por expresión o 'particionados'.
Índices
Índices
Otro ejemplito
FTS
Full text Search
¿Hace busquedas de texto con '%esto%' en campos de más de 300 caracteres? ¿O utiliza Regex?
Si tiene + de 8.3, piense en tsearch (viene incorporado)
Indices especiales:GIN pesado para updates, veloz para búsqueda.GiST más liviano para updates, no tan veloz para búsquedas.
Si nos interesa el orden o formato del texto NO DEBEMOS USAR FTS SOLO, ya que este evita repeticiones y reordena de menor a mayor.
tsearch
Tipo de datoPara tsearch
Consulta conOperadores
Tsearch
Leemos un archivo y lo volcamos en una tabla que contiene un solo campo de tipo tsvector.
Tsearch
Finalmente podremos hacer nuestra primer consulta básica.Nos falta algo? Si! El índice!
Para tsvector los tipos de índices a utilizar son GiN y GiST.
Más acerca de Tsearch
Técnica de Large Object combinada con FTS.
XML
En el siguiente ejemplo veremos:Leer de un archivo que contiene un xml con una primitiva <add>.Separar los atributos <doc>.Realizar búsquedas y ordenamientos con xpath.
-- Extracción ejemplo de un archivo XML con primitiva <add>INSERT INTO main (SELECT pg_read_file('unr.xml',0,800000)::xml);
– Insertar en una tabla los atributos docsINSERT INTO docs ( SELECT p[i] FROM (SELECT xpath('//doc',big)::anyarray FROM main) p(p), (SELECT generate_series(1,array_upper(xpath('//doc',big)::anyarray,1)) FROM main) i(i));
ConsultasSELECT regexp_matches(xpath('//field[@name="date"]',doc)::text,$$\d{4}$$) FROM docs WHERE xpath('//field[@name="date"]',doc)::text ~ '2005';
SELECT ctid FROM docs WHERE xpath('//field[@name="date"]',doc)::text ~ '2005' ORDER BY regexp_matches(xpath('//field[@name="date"]',doc)::text,$$\d{4}$$);
SELECT * FROM docs WHERE xpath('//field[@name="date"]',doc)::text ~ '2005' ORDER BY regexp_matches(xpath('//field[@name="date"]',doc)::text,$$\d{4}$$) LIMIT 10;
XML + TOAST
La combinación de ambas nos puede dar una funcionalidad 'similar' a Storage Big Table.
ALTER TABLE docs ALTER COLUMN docs SET STORAGE EXTERNAL;
Esto obliga a que el contenido de la columna se guarde fuera de linea y sin compresión.
SQL & PL tips
Alter con usingCREATE TABLE tablita ASSELECT 'emanuel calvo franco es dba en el siu'::text as texto;select length(texto) from tablita;
ALTER TABLE tablitaALTER COLUMN texto TYPE VARCHAR(20)USING CASE (length(texto) > 20)WHEN true THEN substring(texto,1,20)ELSE texto END;
SELECT length(texto) from tablita;
Timestamp
Quitar los milisegundos en timestampcurrent_timestamp::timestamp(0)
PL
Siempre explicite los casteos:For i in 1..100000 loop s:= 4e3::int; end loop;
Retornar RECORD? Como?!CREATE FUNCTION ops(OUT a int, OUT b int, IN c int) RETURNS record AS $$ BEGIN a:= c+1; b:=c+2; RETURN; END; $$ LANGUAGE plpgsql;SELECT ops(8); SELECT ops(8).*;
PL
Trucos con funciones IMMUTABLE, ya que son evaluadas previo a la generación del plan:
CREATE FUNCTION preCharge(code text) RETURNS text[] AS $f$ SELECT ARRAY(SELECT var::text FROM tabla WHERE col = code); $f$ LANGUAGE SQL IMMUTABLE;SELECT ..... AND col = ANY(preCharge('CODE'));
Porque no select *Consulta sin documentación implícita.La consulta puede romper la aplicación si hay modificaciones en el DDL.Anti-performanceNo usar en producción.El muestreo de datos es lo que quizás mayor carga proporcional tenga.
CASEWHERE func_lenta() = 'fulano' OR func_lenta() = 'otro'WHERE CASE func_lenta() WHEN 'fulano THEN 1 WHEN 'otro' THEN 2 ELSE 3 END
Foreach ($rows as $id => $row)if () UPDATE tablaSET col * 0.90 WHERE id = $idELSE ...UPDATE tabla set r =(CASE WHEN r>2 THEN r*.90ELSE e * 1.10 END);
Group by
Agregados propios :0
Simular rowcount
CTID
Que es el CTID?Es la columna de identificación de registro.Indica la posición 'física' del registro en la tabla.Es extremadamente veloz debido a qe su valor es de registro.No sirve para buscar un registro en particular, debido a que el ctid puede cambiar luego de un vacuum.
Eliminación de una tupla duplicada con CTID
Un registro
Valores NULL
Evite valores nulos en columnas que posiblemente requieran ser indizadas.
Afecta la performance.Suele ser 'sucio'.Incluso es preferible un determinado caractér, en lugar de un valor NULL.
Buenas practicasAntes de actualizar/eliminar tuplas, chequear con SELECT.Es buena practica utilizar RETURNING en INSERT y UPDATE.Comente las tablas y columnas!!! (comment on table actor is 'Tabla con nombres de actores';)Bloqueos explícitos.
Más seguros. Más lentos.
Técnicas
Tecnicas Preferiblemente, utilicemos caracteres para los joins.Otra técnica es la de 'hashear' textos largos.
Mejora cuando todos los campos son distintos.Debemos tratar colisiones de manera 'manual'.
Prueba
Ejemplo
Sin hash
Tips de buen diseñoRegla simple : “una idea, una tabla”Probablemente ud necesite más tablas si:
No hay una llave unica.Hay más de una llave única.
Necesitara más tablas si:Esta haciendo muchos joins de uno a uno.
Es mentira que los joins mejoran utilizando ints en vez de text.
EXPLAIN y ESTADÍSTICAS
Explain es tu amigo...
… pero a los amigos se los entiende! ¬¬ generalmente...
Conceptos básicosCoste (es calculado en base a reglas detalladas en el postgresql.conf)
OperadorTiempo (no vamos a entrar en explicaciones filosóficas de que es esto :)
Tamaño de datos (esto podría tener una explicación más extensa , pero es entendible de solo pensar en ella)
Los valores siempre son estimados!! Pueden variar de acuerdo a las estadísticas y otros factores.
Coste
Cost( 1ra fila … ultima) de acuerdo a valores de coste explícitos en el postgresql.conf
Operadores
Seq ScanIndex scanSort (*work_mem)
UniqueLimitAggregateAppendResult
Nested LoopMerge JoinHash and Hash JoinGroupSubquery Scan and SubplanTid Scan (raro)Materialize
Explain VS Explain Analyze
Algunos operadores +
select * from accounts a, history h where a.aid = h.aid;
Algo con estadísticas
Recuerde
Actualizar
estadísticas
ALTER TABLE chares ALTER COLUMN texto SET STATISTICS 10;
Dan vueltas
A mayor frecuencia de aparición, favorecerá que el
optimizador opte por el seqscanPor sobre otros operadores :O
La importancia del Analyze
La recolección de estadísticas es decisiva para la selección de la ruta menos costosa.La variable que indica el valor por defecto y por columna de las estadísticas es default_statistic_target (10).ALTER TABLE tabla ALTER COLUMN columna SET STATISTICS 1000;
CTE (8.4)
WITH tx(i) as(select * from generate_series(1,8) )select * from tx;
WITH RECURSIVE tx(i) as(select 1 UNION ALL select * from generate_series(1,8) g(k) ) select * from tx;
WITH RECURSIVE t(n) AS (VALUES (1)UNION ALLSELECT n+1 FROM t WHERE n < 50)SELECT sum(n) FROM t;
WITH RECURSIVE t(n,m,o,p) AS (SELECT 1,1,1,1UNION ALLSELECT n+1, m+2 ,o+3, n+m+o FROM t)SELECT * FROM t LIMIT 10;
Table Functions (tablefunc contrib)
Connect bySELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'row2', 0, '-')AS t(keyid text, parent_keyid text, level int, branch text);
keyid | parent_keyid | level | branch -------+--------------+-------+----------------row2 | | 0 | row2row4 | row2 | 1 | row2-row4row6 | row4 | 2 | row2-row4-row6row5 | row2 | 1 | row2-row5(4 rows)
Crosstab
Windowing Functions
LISTEN - NOTIFY
Utilidad
Permite notificar a un proceso de una determinada acción.
En el caso de los replicadores, utilizan NOTIFY para indicar cuando se efectua una inserción – actualización de un registro.
PHP?
NOTIFY prueba;
Herencia Avanzada
Configuración de servidores
Servidores de desarrollo
Track_activities = offLogging_collector = offFsync = offmax_prepared_statements = 0 (exepto si desarrollamos herramientas de replicación)Autovacuum = offEn lo posible contar con un set de pruebas similar o extraído de producción.
Gracias!!!
top related