PostgreSQL Level UP Fábio Telles Rodriguez

Fábio Telles Rodriguez • Consultor pela Timbira • DBA Oracle e PostgreSQL + 15 anos • Colaborador da Comunidade Brasileira de PostgreSQL • Blog: savepoint.blog.br • [email protected] • @telles

Infância

Infância • Ingres (1977 - 1985) • Berkeley • Linguagem QUEL • Postgres (1986 - 1994) • Berkely • Ilustra -> Informix -> DB2 • Postgres95 (1995) • PostgreSQL 6.x (1996 - 1999) • PGDG • Linguagem SQL • Estabilização do código

Juventude

Projetado para ser flexível • Funções • Operadores • Funções de agregação • Herança de tabelas • Sobrecarga de operadores • Tipos de dados definidos pelo usuário • Infraestrutura para indexar operadores e tipos de dados novos

Postgres Kaioken

Licença permissiva “Permission to use, copy, modify, and distribute this software and its documentation for any purpose, without fee, and without a written agreement is hereby granted, provided that the above copyright notice and this paragraph and the following two paragraphs appear in all copies.” • https://opensource.org/licenses/postgresql • Licença estilo BSD e MIT • De acordo com a Open Source Iniciative • Não possui o conceito de “Copyleft”, ou seja, você pode fazer um fork e fechar o código!

Forks

Forks https://wiki.postgresql.org/wiki/PostgreSQL_derived_databases • AgensGraph (orientado a grafos) • Aurora (AWS) • BDR (replicação multimaster) • CitusDB (BI) • EnterpriseDB (compatibilidade com Oracle) • HadoopDB (cluster shared nothing) • PipelineDB (streaming) • PostgresX2 (cluster) • Pivotal (BI) • Redshift (BI) • ToroDB (document model)

Desenvolvimento aberto • Código aberto • Roadmap aberto • Listas de discussão abertas • Qualquer pessoa pode desenvolver novas funcionalidades • 4 commit fasts / release • Ciclos de ~1 ano entre cada release • O PostgreSQL não tem dono!!!

Postgres super sayajin

Linguagens procedurais https://wiki.postgresql.org/wiki/PL_Matrix • Core: • SQL, PL/pgSQL, • PL/Perl, PL/Python, PL/TCL • Confiáveis: • PL/sh, PL/R, PL/Java, PL/Lua, PL/v8(javascript), • Teste bem antes de colocar em produção: • PL/PSM, PL/PHP, PL/LoL, PL/Ruby

Postgres super sayajin 3

Extensões ● Contrib: https://www.postgresql.org/docs/current/contrib.html ○ adminpack, amcheck, auth_delay, auto_explain, bloom, btree_gin, btree_gist, citext, cube, dblink, dict_int, dict_xsyn, esarthdistance, file_fdw, fuzzystrmath, hstore, intagg, intarray, isn, lo, ltree, passwordcheck, pg_buffercache, pgcrypto, pg_freespacemap, pg_prewarm, pgrowlocks, pg_stat_statements, pgstattuple, pg_trm, pg_visibility, postgres_fdw, seg, sepgsql, spi, sslinfo, tablefunc, tcn, test_decoding, tsm_system_rows, tsm_system_time, unaccent, uuid-ossp, xml2; ● Software Catalogue: https://www.postgresql.org/download/products/6-postgresql-extensions/ ○ HypoPG, OpenFTS, pg_qualstats, pg_stat_kcache, pg_track_settings, PipelineDB, PL/Proxy, PostGIS, PostPic, prefix, Texcaller, TimeTravel ● Outros (tente no pgxn.org, github ou no Google)

Postgres God!

Foreign Data Wrapper https://wiki.postgresql.org/wiki/Foreign_data_wrappers ● Postgres, Oracle, MySQL, SQLite, Informix, Sybase, etc ● ODBC, JDBC, Multicorn ● Cassandra, CouchDB, InfluxDB, MongoDB, Neo4J, Redis, Riak, etc ● CSV, JSON, XML, compressed files, ● Git, imap, ICAL, RSS, www, etc, ● Mailchimp, facebook, Telegram, Twitter, S3, etc ● Elastic Search, Google BigQuery, Hadoop, HDFS, Hive ● cstore, PGStrom (GPU), faker_fdw, black_hole, etc ● Multicorn ( https://multicorn.org )

PostgreSQL 7.0 (mai/2000) • Numeric Types • Monetary Type • Character Types • Date/Time Types • Boolean Type • Geometric Types • Network Address Data Types • Bit String Types

PostgreSQL 7.1 (abr/2001) • Binary Strings

PostgreSQL 7.2 (fev/2002) • Object Identifier Types • Pseudo-Types • Arrays

PostgreSQL 7.3 (nov/2002) • CREATE CAST -- define a user-defined cast • CREATE CONVERSION -- define a user-defined encoding conversion • CREATE DOMAIN -- define a new domain • CREATE OPERATOR CLASS -- define a new operator class for indexes • CREATE SCHEMA -- define a new schema

PostgreSQL 7.3 (nov/2002) • Object Identifier Types • Pseudo-Types • Arrays

PostgreSQL 8.0 (jan/2005) • CREATE TABLESPACE -- define a new tablespace • SAVEPOINT / RELEASE SAVEPOINT / ROLLBACK TO SAVEPOINT • Composite Types

PostgreSQL 8.2 (dez/2006) • XML Document Support

PostgreSQL 8.3 (fev/2008) • Enumerated Types • Text Search Types • UUID Type • XML Type

PostgreSQL 8.4 (jul/2009) • CREATE FOREIGN DATA WRAPPER -- define a new foreign-data wrapper • CREATE SERVER -- define a new foreign server • CREATE USER MAPPING -- define a new mapping of a user to a foreign server • CREATE TEXT SEARCH CONFIGURATION -- define a new text search configuration • CREATE TEXT SEARCH DICTIONARY -- define a new text search dictionary • CREATE TEXT SEARCH PARSER -- define a new text search parser • CREATE TEXT SEARCH TEMPLATE -- define a new text search template • CREATE OPERATOR FAMILY -- define a new operator family

PostgreSQL 9.1 (set/2011) • CREATE COLLATION -- define a new collation • CREATE EXTENSION -- install an extension • CREATE FOREIGN TABLE -- define a new foreign table • SECURITY LABEL -- define or change a security label applied to an object

PostgreSQL 9.1 (set/2011) • CREATE COLLATION -- define a new collation • CREATE EXTENSION -- install an extension • CREATE FOREIGN TABLE -- define a new foreign table • SECURITY LABEL -- define or change a security label applied to an object

PostgreSQL 9.2 (set/2012) • JSON Type • Range Types

PostgreSQL 9.3 (set/2013) • CREATE EVENT TRIGGER -- define a new event trigger • CREATE MATERIALIZED VIEW -- define a new materialized view

PostgreSQL 9.4 (dez/2014) • pg_lsn Type • jsonb

PostgreSQL 9.6 (set/2016) • CREATE ACCESS METHOD -- define a new access method

PostgreSQL 10 (out/2017) • CREATE PUBLICATION — define a new publication • CREATE SUBSCRIPTION — define a new subscription • CREATE STATISTICS — define extended statistics

PostgreSQL 11 (out/2018) • CALL — invoke a procedure • CREATE PROCEDURE — define a new procedure • Domain types

Futuro ● Novos forks e novos merges; ● Novas extensões e FDWs ● Storage Engines plugáveis; ● Hooks

PGConf.Brasil 2019 ● 1, 2 e 3 de agosto ● Hotel Century Flat ● Inscrições abertas ● Chamada de trabalhos aberta até 28/02 ● www.pgconf.com.br

• Development Information • Feature Matrix • Software Catalogue • PGXN • PG-Strom • DB-Engines RDBMS Ranking • DBMS Comparison • Contribuições da Timbira no PostgreSQL Links interessantes

[email protected]