Share
Embed
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
8Kdata
March 16, 2016
Transcript
56 slides
-
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 -
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. -
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 ] [, ...] ] -
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 -
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 ] -
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 -
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. -
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 -
Novedades en PostgreSQL 9.5 GROUPING SETS select usuarios, role, sum(accesos),
count(*) from accesos_usuarios group by grouping sets (usuarios, role, ()); -
Novedades en PostgreSQL 9.5 CUBE select usuarios, role, sum(accesos), count(*)
from accesos_usuarios group by cube (usuarios, role); -
Novedades en PostgreSQL 9.5 ROLLUP select role, usuarios, sum(accesos), count(*)
from accesos_usuarios group by rollup (role, usuarios); -
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 -
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 ); -
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; -
Novedades en PostgreSQL 9.5 RLS – Ejemplo – Inicialización tabla
insert into clientes (nombre_cliente, nombre_comercial) values ('cliente1', 'pedro'), ('cliente2','juan'), ('cliente3','jesus'); -
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); -
Novedades en PostgreSQL 9.5 RLS – Ejemplo – ALTER TABLE
alter table clientes enable row level security; \c - pedro -
Novedades en PostgreSQL 9.5 Gestión automática ficheros WAL Nuevos parámetros:
• max_wal_size • min_wal_size Reemplazan a checkpoint_segments -
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) -
Novedades en PostgreSQL 9.5 Block Range Indexes (BRIN) - Ejemplo
create table pedidos ( id serial primary key, fecha_pedido timestamptz, producto text ); -
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); -
Novedades en PostgreSQL 9.5 Block Range Indexes (BRIN) - Ejemplo
CREATE INDEX idx_pedidos_fecha_brin ON pedidos USING BRIN (fecha_pedido); -
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 -
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 -
Novedades en PostgreSQL 9.5 JSONB – Ejemplo Concatenación SELECT '{"nombre":
"Pedro", "edad": 35}'::jsonb || '{"ciudad": "Madrid"}'::jsonb; -
Novedades en PostgreSQL 9.5 JSONB – Ejemplo Sobreescritura SELECT '{"nombre":
"Pedro", "edad": 35}'::jsonb || '{"ciudad": "Madrid", "edad": 40}'::jsonb; -
Novedades en PostgreSQL 9.5 JSONB – Ejemplo Sustracción SELECT '{"nombre":
"Pedro", "edad": 35}'::jsonb - 'edad'; -
Novedades en PostgreSQL 9.5 JSONB – jsonb_set - Ejemplo SELECT
jsonb_set( '{"nombre": "Pedro", "contacto": {"telefono": "34910000000", "fax": "34910000001" }}'::jsonb, '{contacto,telefono}', '"34954000000"'::jsonb); -
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); -
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); -
Novedades en PostgreSQL 9.5 JSONB – jsonb_pretty - Ejemplo select
jsonb_pretty('{"nombre": "Pedro", "contacto": {"telefono": "34910000000", "fax": "34910000001"}}'::jsonb); -
Novedades en PostgreSQL 9.5 Foreign Data Wrappers • Importación de
tablas en masa • IMPORT FOREIGN SCHEMA -
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; -
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'); -
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'); -
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; -
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) -
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.