PDF de programación - PostgreSQL - El gestor de bases de datos relacionales más avanzado del mundo

Introducción

PostgreSQL

Ejemplos

Cierre

PostgreSQL

El gestor de bases de datos relacionales

más avanzado del mundo

Álvaro Herrera

2ndQuadrant Ltd.

http://www.2ndQuadrant.com/
http://www.PostgreSQL.org/

Congreso Nacional de Ciencias
y Tecnologías de la Información

Potosí 2013

Introducción

PostgreSQL

Ejemplos

Cierre

Praga 2013

Introducción

PostgreSQL

Ejemplos

Cierre

Praga 2013 (2)

Introducción

PostgreSQL

Ejemplos

Cierre

Praga 2013 (3)

Introducción

PostgreSQL

Ejemplos

Cierre

Qué es PostgreSQL

• Sistema de almacenamiento y manipulación de datos
• Interoperable con otros DBMS: lenguaje SQL estándar
• Confiable, consistente, robusto (ACID)
• Potente, flexible, extensible
• Excelente rendimiento, escalable a hardware muy grande

Introducción

PostgreSQL

Ejemplos

Cierre

Estadísticas Git

Núm. líneas en repositorio Git

2.5M

2M

1.5M

1M

500k

6.0

7.0

7.2

7.4 8.0 8.1 8.2 8.3

8.4 9.0 9.1 9.2

Introducción

PostgreSQL

Ejemplos

Cierre

Características principales

• Gran soporte de lenguaje SQL

• Transacciones, savepoints
• INNER JOIN, OUTER JOIN
• UNION, INTERSECT, EXCEPT
• subconsultas
• agrupamiento, agregación
• INFORMATION_SCHEMA
• WITH RECURSIVE
• Window functions (LAG, LEAD, ...)
• disparadores (triggers)

Introducción

PostgreSQL

Ejemplos

Cierre

Características principales (cont.)

• resistencia a fallas frente a cortes abruptos de energía

(WAL)

• Replicación usando archivos WAL (9.0)
• Replicación “en flujo” (9.1)
• Replicación “en cascada” (9.2)
• “hot standby”
• Respaldos en caliente, respaldos continuos

Introducción

PostgreSQL

Ejemplos

Cierre

Historia

• 1977 – 1985: Ingres

• Michael Stonebraker, University of California, Berkeley

• 1986 – 1994: Postgres

• M. Stonebraker, University of California, Berkeley

• 1994 – 1995: Postgres95
• Jolly Chen, Andrew Yu

• 1996 – presente: PostgreSQL Global Development Group

• Inicialmente, siete miembros en cinco países

Introducción

PostgreSQL

Ejemplos

Cierre

PostgreSQL Global Development Group

• “core team” de 7 personas
• 38 “major developers”
• 16 empresas contribuyendo activamente
• RedHat, 2ndQuadrant, EnterpriseDB, VMWare,

Redpill Linpro, Cybertec, Aster Data, SRA, NTT, Dalibo,
End Point, OmniTI, Afilias, PGExperts, Credativ,
Command Prompt

Introducción

PostgreSQL

Ejemplos

Cierre

Ejemplos simples

CREATE TABLE meteorologicas (

ciudad varchar(80),
temp_min int,
temp_max int,
precip real,
fecha date

);
INSERT INTO meteorologicas VALUES ('Sucre', 25, 27, 0.25,

'1994-11-27');

CREATE TABLE ciudades (
nombre varchar(80),
ubicacion point

);
INSERT INTO ciudades VALUES ('Sucre', '(-63.0, 10.6)');

Introducción

PostgreSQL

Ejemplos

Cierre

Ejemplos simples (2)

SELECT ciudad, temp_min AS min, temp_max AS max, precip,

fecha, ubicacion

FROM meteorologicas, ciudades
WHERE ciudad = nombre;
precip

fecha

ciudad min max
Sucre
27

25

0.25 1994-11-27 (-63.0,10.6)

ubicacion

Introducción

PostgreSQL

Ejemplos

Cierre

Ejemplos avanzados — Rangos (1)

Introducción

PostgreSQL

Ejemplos

Cierre

Ejemplos avanzados — Rangos (2)

Presupuesto de $13.000–$15.000, autos en ese rango

SELECT * FROM autos WHERE

( autos.precio_min <= 13000 AND autos.precio_min <= 15000 AND

autos.precio_max >= 13000 AND autos.precio_max <= 15000 )

OR

( autos.precio_min <= 13000 AND autos.precio_min <= 15000 AND

autos.precio_max >= 13000 AND autos.precio_max >= 15000 )

OR

( autos.precio_min >= 13000 AND autos.precio_min <= 15000 AND

autos.precio_max >= 13000 AND autos.precio_max <= 15000 )

OR

( autos.precio_min >= 13000 AND autos.precio_min <= 15000 AND

autos.precio_max >= 13000 AND autos.precio_max >= 15000 )

ORDER BY autos.precio_min;

Introducción

PostgreSQL

Ejemplos

Cierre

Ejemplos avanzados — Rangos (3)

Presupuesto de $13.000–$15.000, autos en ese rango

SELECT * FROM autos WHERE
autos.rango_precio && int4range(13000, 15000, '[]')
ORDER BY lower(autos.rango_precio)
rango_precio
id
5 Suzuki Grand Nomade [11.000,15.001)
[12.000,14.001)
6

Mitsubishi Lancer

nombre

Introducción

PostgreSQL

Ejemplos

Cierre

Ejemplos avanzados – jerarquías de datos

WITH RECURSIVE x AS (

SELECT *

FROM empleados
WHERE empleado_id = 2

UNION ALL

SELECT empleados.*

FROM x
JOIN empleados ON (empleados.jefe = x.empleado_id)

)
SELECT * FROM x;
empleado_id

nombre
2 Andrés
3 José
5 Felipe
4 Cecilia

jefe
1
2
2
3

Introducción

PostgreSQL

Ejemplos

Cierre

Funciones ventana

SELECT depto, empno, salario,

avg(salario) OVER (PARTITION BY depto)::int AS prom,
salario - avg(salario) OVER (PARTITION BY depto)::int

AS dif

FROM salarios ORDER BY dif DESC

depto
desarrollo
ventas
personal
desarrollo
desarrollo
ventas
personal
ventas
ventas
desarrollo
desarrollo

empno
8
6
2
11
10
1
5
4
3
9
7

salario
6000
5500
3900
5200
5200
5000
3500
4800
4800
4500
4200

prom
5020
5025
3700
5020
5020
5025
3700
5025
5025
5020
5020

dif
980
475
200
180
180
-25
-200
-225
-225
-520
-820

Introducción

PostgreSQL

Ejemplos

Cierre

CTEs de escritura

WITH eliminados AS (

DELETE FROM ONLY registro

WHERE fecha >= '2009-01-01'

AND fecha < '2009-02-01'

RETURNING *

)

INSERT INTO registro_200901
SELECT * FROM eliminados;

Introducción

PostgreSQL

Ejemplos

Cierre

Comunidad

• Modelo de desarrollo
• Soporte informal

• listas de correo — http://www.postgresql.org/list
• IRC (#postgresql-es, #postgresql en freenode.net)
• StackOverflow.com
• Proyectos relacionados

• contrib, github, PgFoundry

• Soporte comercial

• consultoría, soporte, capacitación, hosting, ...

Introducción

PostgreSQL

Ejemplos

Cierre

Comunidad

Introducción

PostgreSQL

Ejemplos

Cierre

2ndQuadrant

http://www.2ndQuadrant.com/

Introducción

PostgreSQL

Ejemplos

Cierre

Preguntas

Introducción

PostgreSQL

Ejemplos

Cierre

Material de otras charlas

• Roland Sonnenschein, “Three success stories”, Praga

2013

• Jonathan Katz, “Range Types: your life will never be the

same”, Praga 2013

• Greg Stark, “Recursive Queries”, Ottawa 2009
• Hitoshi Harada, “Windowing Functions”, Ottawa 2009
• David Fetter, “Writable CTEs (the next big thing)”, Paris

2009