PDF de programación - Lo mejor de FOSDEM y PostgreSQL

Lo mejor de FOSDEM y PostgreSQL

Álvaro Hernández Tortosa <[email protected]>



Acerca de mí

● Álvaro Hernández Tortosa <[email protected]>
● 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/

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



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;



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;



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



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



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.



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



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



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



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



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).



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.



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



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.



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;



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.



Indexando ando: ¿quién acierta más estos tests?



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}



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
);



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)'



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 &&);



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));