Lo mejor de FOSDEM 2014 para PostgreSQL

Los mejores trucos, las mejores funcionalidades, ¡todo lo que te perdiste si no pudiste venir!

Transcript

1. Lo mejor de FOSDEM y PostgreSQL Álvaro Hernández Tortosa <aht@Nosys.es>
2. Acerca de mí • Álvaro Hernández Tortosa <aht@Nosys.es> • Fundador y Director Técnico en NOSYS • ¿Qué hacemos en NOSYS? ✔ Formación, consultoría y desarrollo de software con PostgreSQL (y Java) ✔ Partners de EnterpriseDB ✔ Formación avanzada en Java con Javaspecialists.eu: Java Master Course y Java Concurrency Course ✔ Partners de Amazon AWS. Formación y consultoría en AWS • Twitter: @ahachete • LinkedIn: http://es.linkedin.com/in/alvarohernandeztortosa/
3. Lo que pasó en FOSDEM quedó en... ¡pues no! Aquí lo contamos todo (o casi) • Novedades relevantes de 9.4 • Indexando ando • Algunos trucos para desarrolladores
4. PostgreSQL 9.4: filtros en agregados • En PostgreSQL 9.3: SELECT pais, count(*) AS usuarios, sum( CASE WHEN activo THEN 1 ELSE 0 END ) usuarios_activos FROM usuarios GROUP BY pais; • En 9.4: SELECT pais, count(*) AS usuarios, count(*) FILTER (WHERE activo) usuarios_activos FROM usuarios GROUP BY pais;
5. PostgreSQL 9.4: agregados sobre sets ordenados • En PostgreSQL 9.3: A nivel de aplicación o con joins con generate_series... • En 9.4: SELECT rank('2014-02-04 13:42:51.093038') WITHIN GROUP (ORDER BY tiempo ASC) FROM llegadas;
6. PostgreSQL 9.4: agregados sobre sets ordenados II • También está mode(), que devuelve el valor moda: SELECT clase, mode() WITHIN GROUP (ORDER BY nota_examen) FROM calificacion GROUP BY clase; • Hay otros adicionales, como percent_rank(), dense_rank(), percentile_cont(), percentile_disc() INSERT INTO i SELECT generate_series(1,10000); SELECT percent_rank(5000) WITHIN GROUP (ORDER BY i) FROM i; – Devuelve, no sorprendentemente: 0.4999
7. PostgreSQL 9.4: agregados hipotéticos • Algunos de los anteriores agregados sobre conjuntos ordenados permiten valores hipotéticos; esto es, valores que “existirían”, pero no existen en la tabla. INSERT INTO i VALUES (1), (100); SELECT percentile_cont(.2) WITHIN GROUP (ORDER BY i) FROM i; percentile_cont ----------------- 20.8 SELECT rank(5) WITHIN GROUP (ORDER BY i) FROM i; rank ------ 2
8. PostgreSQL 9.4: vistas • Vistas materializadas: se verán en la siguiente charla :) • Vistas actualizables: ➔ Se amplía el conjunto de casos en el que se pueden actualizar los campos de la vista. ➔ Se añade soporte de “WITH CHECK OPTION” para que impida (o no) la introducción de valores que sean visibles en la vista. Valores LOCAL y CASCADE.
9. PostgreSQL 9.4: unnest de más de un array • Esto vale oro: SELECT * FROM unnest( '{f1c1,f2c1,f3c1}'::text[], '{f1c2,f2c2,f3c2}'::text[] ); unnest | unnest --------+-------- f1c1 | f1c2 f2c1 | f2c2 f3c1 | f3c2
10. PostgreSQL 9.4: unnest de más de un array (II) • Con ORDINALITY (y WITH para que quede más bonito): WITH t (a, b, o) AS ( SELECT * FROM unnest( '{f1c1,f2c1,f3c1}'::text[], '{f1c2,f2c2,f3c2}'::text[] ) WITH ORDINALITY ) SELECT o, a, b FROM t; o | a | b ---+------+------ 1 | f1c1 | f1c2 2 | f2c1 | f2c2 3 | f3c1 | f3c2
11. PostgreSQL 9.4: DBA • pg_prewarm: extensión para “calentar” la caché, de forma que nuevos servidores o servidores en hot standby tengan shared_buffers lleno y no sufran penalización en la primera lectura de disco. • Custom background workers: ahora pueden tener memoria dinámica e inicialización dinámica (no sólo en postmaster startup). • ALTER SYSTEM SET postgresqlconf_param = valor Edita postgresql.conf.auto, que tiene precedencia sobre postgresql.conf
12. PostgreSQL 9.4: DBA (II) • recovery_target=immediate: permite que una instancia en recuperación (recovery.conf) pase a modo online (aceptando queries) tan pronto como alcance estado consistente (sin necesidad de esperar a procesar todos los WAL pendientes). • Réplicas de lectura desfasadas: permite que la replicación se aplique de forma retrasada en la réplica, de manera que ante fallo humano se puedan recuperar datos de la réplica sin necesidad de tener que recuperar la base de datos mediante PITR. Parámetro min_recovery_apply_delay
13. PostgreSQL 9.4: y de regalo... ¡NoSQL! • Hstore jerárquico: funcionalmente equivalente a JSON, al permitir documentos embebidos. Gran extensión, con amplísimo soporte de operadores e índices sobre los valores del campo hstore. SELECT 'a=>1,b=>{c=>3,d=>{4,5,6}},1=>f'::hstore -> 'b'; • Más funciones JSON y jsonb: Se amplían las funciones para trabajar con JSON, y se añade el tipo nativo “jsonb”, que es una representación binaria más compacta para JSON (no preserva espacios en blanco ni permite claves duplicadas).
14. Indexando ando: importancia de SQL e índices • Según un whitepaper de Percona, de las principales causas de indisponibilidad de una base de datos, destacan: ➔ 38% mal SQL ➔ 15% estructura de datos e índices • Según observaciones particulares, entorno al 50% de los problemas de rendimiento de SQL son por malos índices. • ¿Quién hace los índices? ¿Los DBAs o los programadores? Los índices no son tunning de bases de datos, son responsabilidad de los programadores. De hecho, su uso depende exactamente de las queries que se hagan.
15. Indexando ando: ¿es bueno este índice? P1 CREATE INDEX tabla_indice ON tabla (col_fecha); SELECT texto, col_fecha FROM tabla WHERE extract(year FROM col_fecha) = 2013
16. Indexando ando: ¿es bueno este índice? R1 El índice no se usa. Deben crearse índices sobre expresiones o bien usar en el WHERE comparación de rangos de fechas.
17. Indexando ando: ¿es bueno este índice? P2 CREATE INDEX tabla_indice ON tabla (a, col_fecha); SELECT id, a, col_fecha FROM tabla WHERE a = 'valor' ORDER BY col_fecha DESC LIMIT 1;
18. Indexando ando: ¿es bueno este índice? R2 Es una solución casi óptima. La cláusula LIMIT 1 sobre un campo ordenado permite el uso del índice, incluso aunque el orden sea inverso. No hace falta ordenar todas las soluciones y recorrer el conjunto ordenado.
19. Indexando ando: ¿quién acierta más estos tests?
20. Trucos desarrolladores: operadores sobre arrays select name, tags from agents where tags @> array['double-agent','probation']; => -[ RECORD 1 ]-------------------------- name | Sterling Archer tags | {double-agent,probation,arrears} -[ RECORD 2 ]-------------------------- name | Barry Dylan tags | {double-agent,probation,arrears}
21. Trucos desarrolladores: generate_series SELECT generate_series(1, 3); SELECT generate_series(1, 10, 2); SELECT generate_series( now() - '1 week'::interval, now(), '1 hour'::interval );
22. Trucos desarrolladores: rangos SELECT daterange( '["Jan 1 2013", "Jan 15 2013")' ) @> 'Jan 10 2013'::date; SELECT numrange(3,7) && numrange(4,12); numrange(5,15) + numrange(10,20) => '[5, 20)' numrange(5,15) * numrange(10,20) => '[10, 15)'
23. Trucos desarrolladores: rangos (II) Con exclusion constraint, podemos definir constraint de tablas que preveen operaciones como rangos solapados (fundamental para bases de datos como reservas de recursos físicos, etc): ALTER TABLE reserva_salas ADD EXCLUDE USING gist (reservado WITH =, periodo WITH &&);
24. Trucos desarrolladores: índices • Índices parciales: se trata de índices sobre un subconjunto de valores de una tabla. Permiten reducir el tamaño del índice, eliminando valores que no importan: CREATE INDEX tabla_indice ON tabla (cols) WHERE NOT borrado; • Índices sobre expresiones: se pueden crear índices no sólo sobre columnas existentes, sino también sobre expresiones arbitrarias sobre las columnas: CREATE INDEX tabla_indice ON tabla (lower(nombre));