Novedades en PostgreSQL 9.5

Análisis y ejemplos de las nuevas características aparecidas en PostgreSQL 9.5 más relevantes. Conferencia en el marco de PgDayCuba en la conferencia Informática 2016. La Habana, Cuba

Transcript

1. PostgreSQL 9.5 Novedades Álvaro Hernández <aht@8kdata.com>
2. Novedades en PostgreSQL 9.5 Acerca de* • Investigación y desarrollo en bases de datos • Consultoría, Formación y Soporte en PostgreSQL • Fundadores de PostgreSQL España, el 5o PUG mayor del mundo (>500 members as of today) • Acerca de mí: CEO en 8Kdata: @ahachete http://linkd.in/1jhvzQ3 www.8kdata.com
3. Novedades en PostgreSQL 9.5 UPSERT
4. Novedades en PostgreSQL 9.5 UPSERT – El problema Paso 1- Creamos una tabla → create table accesos_usuarios (usuarios text primary key, accesos int); Paso 2 Insertamos en la tabla → insert into accesos_usuarios values ('8kdata',1); Paso 3- Volvemos a insertar en la tabla el mismo valor → insert into accesos_usuarios values ('8kdata',1); ERROR: duplicate key value violates unique constraint "accesos_usuarios_pkey" DETAIL: Key (usuarios)=(8kdata) already exists.
5. Novedades en PostgreSQL 9.5 UPSERT Postgres UPSERT está orientado al INSERT [ WITH [ RECURSIVE ] with_query [, ...] ] INSERT INTO table_name [ AS alias ] [ ( column_name [, ...] ) ] { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query } [ ON CONFLICT [ conflict_target ] conflict_action ] [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]
6. Novedades en PostgreSQL 9.5 UPSERT CONFLICT_TARGET puede ser: ( { index_column_name | ( index_expression ) } [ COLLATE collation ] [ opclass ] [, ...] ) [ WHERE index_predicate ] ON CONSTRAINT constraint_name
7. Novedades en PostgreSQL 9.5 UPSERT CONFLICT_ACTION puede ser: DO NOTHING DO UPDATE SET { column_name = { expression | DEFAULT } | ( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] ) | ( column_name [, ...] ) = ( sub-SELECT ) } [, ...] [ WHERE condition ]
8. Novedades en PostgreSQL 9.5 UPSERT - Ejemplo insert into accesos_usuarios (usuarios, accesos) values ('8kdata',1) on conflict (usuarios) do update set accesos = accesos_usuarios.accesos + EXCLUDED.accesos; Se incrementa el valor del acceso con el valor excluido
9. Novedades en PostgreSQL 9.5 UPSERT - Ejemplo insert into accesos_usuarios (usuarios, accesos) values ('8kdata',1), ('8kdata2',1) on conflict do nothing; Sólo inserta la segunda tupla sin excepciones.
10. Novedades en PostgreSQL 9.5 UPSERT - Ejemplo insert into accesos_usuarios as a (usuarios, accesos) values ('8kdata',1),('usuario2',1) on conflict (usuarios) do update set accesos = a.accesos + EXCLUDED.accesos where a.usuarios not like '8k%'; Actualiza con condiciones
11. Novedades en PostgreSQL 9.5 Nuevas características en GROUP BY
12. Novedades en PostgreSQL 9.5 GROUPING SETS select usuarios, role, sum(accesos), count(*) from accesos_usuarios group by grouping sets (usuarios, role, ());
13. Novedades en PostgreSQL 9.5 CUBE select usuarios, role, sum(accesos), count(*) from accesos_usuarios group by cube (usuarios, role);
14. Novedades en PostgreSQL 9.5 ROLLUP select role, usuarios, sum(accesos), count(*) from accesos_usuarios group by rollup (role, usuarios);
15. Novedades en PostgreSQL 9.5 RLS
16. Novedades en PostgreSQL 9.5 Control de seguridad a nivel de fila (RLS) • Control a nivel de fila ALTER TABLE … ENABLE/DISABLE ROW LEVEL SECURITY ALTER TABLE … NO FORCE/FORCE ROW LEVEL SECURITY
17. Novedades en PostgreSQL 9.5 RLS – Ejemplo – Creación tabla create table clientes ( id serial primary key, nombre_cliente text not null unique, nombre_comercial text not null );
18. Novedades en PostgreSQL 9.5 RLS – Ejemplo – Creación usuarios create user pedro; create user juan; create user jesus; grant all on table clientes to pedro, juan, jesus;
19. Novedades en PostgreSQL 9.5 RLS – Ejemplo – Inicialización tabla insert into clientes (nombre_cliente, nombre_comercial) values ('cliente1', 'pedro'), ('cliente2','juan'), ('cliente3','jesus');
20. Novedades en PostgreSQL 9.5 RLS – Ejemplo – CREATE POLICY create policy mostrar_propios_clientes on clientes for all to public using (nombre_comercial=current_user);
21. Novedades en PostgreSQL 9.5 RLS – Ejemplo – ALTER TABLE alter table clientes enable row level security; \c - pedro
22. Novedades en PostgreSQL 9.5 Gestión automática del número de ficheros WAL
23. Novedades en PostgreSQL 9.5 Gestión automática ficheros WAL Nuevos parámetros: • max_wal_size • min_wal_size Reemplazan a checkpoint_segments
24. Novedades en PostgreSQL 9.5 BRIN
25. Novedades en PostgreSQL 9.5 Block Range Indexes (BRIN) • Almacenamiento de metadatos de un rango de páginas • Índices de pequeño tamaño • Útiles en tablas grandes y ordenadas de manera natural (por ejemplo, facturas por fecha) • Búsquedas más lentas que índices B-TREE • Nuevo parámetro: pages_per_range • Bloque de 128 páginas (1 Mb)
26. Novedades en PostgreSQL 9.5 Block Range Indexes (BRIN) - Ejemplo create table pedidos ( id serial primary key, fecha_pedido timestamptz, producto text );
27. Novedades en PostgreSQL 9.5 Block Range Indexes (BRIN) - Ejemplo INSERT INTO pedidos (fecha_pedido, producto) SELECT x, 'PostgreSQL_9_5' FROM generate_series('2014-01-01 00:00:00'::timestamptz, '2016-01-01 00:00:00'::timestamptz, '2 seconds'::interval ) a(x);
28. Novedades en PostgreSQL 9.5 Block Range Indexes (BRIN) - Ejemplo
29. Novedades en PostgreSQL 9.5 Block Range Indexes (BRIN) - Ejemplo
30. Novedades en PostgreSQL 9.5 Block Range Indexes (BRIN) - Ejemplo CREATE INDEX idx_pedidos_fecha_brin ON pedidos USING BRIN (fecha_pedido);
31. Novedades en PostgreSQL 9.5 Block Range Indexes (BRIN) - Ejemplo
32. Novedades en PostgreSQL 9.5 Mejoras en el rendimiento
33. Novedades en PostgreSQL 9.5 Mejoras en el rendimiento • En ordenaciones en memoria • Utilización de strxfrm() para extraer caracteres en comparaciones y ordenaciones • En equipos multi-CPU y gran cantidad de memoria • Reducción de memoria consumida por Backend • Mejora concurrencia sustitución buffer compartido
34. Novedades en PostgreSQL 9.5 Novedades con JSONB
35. Novedades en PostgreSQL 9.5 JSONB • Los datos pueden ser modificados ✔ Concatenación / Sobreescritura jsonb || jsonb → ✔ Sustracción jsonb-text → ✔ Función jsonb_set ✔ Función jsonb_pretty
36. Novedades en PostgreSQL 9.5 JSONB – Ejemplo Concatenación SELECT '{"nombre": "Pedro", "edad": 35}'::jsonb || '{"ciudad": "Madrid"}'::jsonb;
37. Novedades en PostgreSQL 9.5 JSONB – Ejemplo Sobreescritura SELECT '{"nombre": "Pedro", "edad": 35}'::jsonb || '{"ciudad": "Madrid", "edad": 40}'::jsonb;
38. Novedades en PostgreSQL 9.5 JSONB – Ejemplo Sustracción SELECT '{"nombre": "Pedro", "edad": 35}'::jsonb - 'edad';
39. Novedades en PostgreSQL 9.5 JSONB – Ejemplo Sustracción SELECT '["rojo","negro","azul"]'::jsonb - 1;
40. Novedades en PostgreSQL 9.5 JSONB – jsonb_set
41. Novedades en PostgreSQL 9.5 JSONB – jsonb_set - Ejemplo SELECT jsonb_set( '{"nombre": "Pedro", "contacto": {"telefono": "34910000000", "fax": "34910000001" }}'::jsonb, '{contacto,telefono}', '"34954000000"'::jsonb);
42. Novedades en PostgreSQL 9.5 JSONB – jsonb_set - Ejemplo SELECT jsonb_set( '{"nombre": "Pedro", "contacto": {"telefono": "34910000000", "fax": "34910000001" }}'::jsonb, '{contacto,telefon}', '"34954000000"'::jsonb, false);
43. Novedades en PostgreSQL 9.5 JSONB – jsonb_set - Ejemplo SELECT jsonb_set( '{"nombre": "Pedro", "contacto": {"telefono": "34910000000", "fax": "34910000001" }}'::jsonb, '{contacto,telefon}', '"34954000000"'::jsonb, true);
44. Novedades en PostgreSQL 9.5 JSONB – jsonb_pretty - Ejemplo select jsonb_pretty('{"nombre": "Pedro", "contacto": {"telefono": "34910000000", "fax": "34910000001"}}'::jsonb);
45. Novedades en PostgreSQL 9.5 Mejoras en FDW
46. Novedades en PostgreSQL 9.5 Foreign Data Wrappers • Importación de tablas en masa • IMPORT FOREIGN SCHEMA
47. Novedades en PostgreSQL 9.5 Foreign Data Wrappers - Ejemplo 1)Creación bases de datos create database origen; create database destino; 2)Creamos tablas en origen create table k1 as select generate_series(1,20) as id; create table k2 as select generate_series(1,30) as id; create table k3 as select generate_series(1,40) as id;
48. Novedades en PostgreSQL 9.5 Foreign Data Wrappers - Ejemplo • Nos conectamos a destino create extension postgres_fdw ; create server src foreign data wrapper postgres_fdw options(dbname 'origen'); create user mapping for curso server src options (user 'curso');
49. Novedades en PostgreSQL 9.5 Foreign Data Wrappers - Ejemplo • Importamos una tabla create foreign table tabla_desde_origen(id int4) server src options (table_name 'k1');
50. Novedades en PostgreSQL 9.5 Foreign Data Wrappers - Ejemplo • Importamos el esquema create schema desde_origen; import foreign schema public from server src into desde_origen;
51. Novedades en PostgreSQL 9.5 Index-Only Scan
52. Novedades en PostgreSQL 9.5 Index-Only Scan Soporte Index-Only Scan para: • Índices B-Tree • Índices SP-GiST • Índices GIST (Nuevo en PostgreSQL 9.5)
53. Novedades en PostgreSQL 9.5 PG_REWIND
54. Novedades en PostgreSQL 9.5 PG_REWIND Sincroniza un directorio de datos con otro directorio de datos creado anteriormente desde el primero. Hace uso de los WAL y copia aquellos que son necesarios Resincronización servidor maestro después de fallo.
55. Novedades en PostgreSQL 9.5 Más información • https://www.8kdata.com/es • http://www.postgresql.org/