Database Level UP

Como o PostgreSQL se tornou o banco de dados mais flexível do mercado

Palestra realizada no Meetup #3 do Databases SP: https://www.meetup.com/pt-BR/Databases-SP/events/258178011/

Transcript

1. PostgreSQL Level UP Fábio Telles Rodriguez
2. Fábio Telles Rodriguez • Consultor pela Timbira • DBA Oracle e PostgreSQL + 15 anos • Colaborador da Comunidade Brasileira de PostgreSQL • Blog: savepoint.blog.br • telles@timbira.com.br • @telles
3. Infância
4. 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
5. Juventude
6. 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
7. Postgres Kaioken
8. 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!
9. Forks
10. 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)
11. 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!!!
12. Postgres super sayajin
13. 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
14. Postgres super sayajin 3
15. 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)
16. Postgres God!
17. 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 )
18. 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
19. PostgreSQL 7.1 (abr/2001) • Binary Strings
20. PostgreSQL 7.2 (fev/2002) • Object Identifier Types • Pseudo-Types • Arrays
21. 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
22. PostgreSQL 7.3 (nov/2002) • Object Identifier Types • Pseudo-Types • Arrays
23. PostgreSQL 8.0 (jan/2005) • CREATE TABLESPACE -- define a new tablespace • SAVEPOINT / RELEASE SAVEPOINT / ROLLBACK TO SAVEPOINT • Composite Types
24. PostgreSQL 8.2 (dez/2006) • XML Document Support
25. PostgreSQL 8.3 (fev/2008) • Enumerated Types • Text Search Types • UUID Type • XML Type
26. 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
27. 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
28. 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
29. PostgreSQL 9.2 (set/2012) • JSON Type • Range Types
30. PostgreSQL 9.3 (set/2013) • CREATE EVENT TRIGGER -- define a new event trigger • CREATE MATERIALIZED VIEW -- define a new materialized view
31. PostgreSQL 9.4 (dez/2014) • pg_lsn Type • jsonb
32. PostgreSQL 9.6 (set/2016) • CREATE ACCESS METHOD -- define a new access method
33. PostgreSQL 10 (out/2017) • CREATE PUBLICATION — define a new publication • CREATE SUBSCRIPTION — define a new subscription • CREATE STATISTICS — define extended statistics
34. PostgreSQL 11 (out/2018) • CALL — invoke a procedure • CREATE PROCEDURE — define a new procedure • Domain types
35. Futuro • Novos forks e novos merges; • Novas extensões e FDWs • Storage Engines plugáveis; • Hooks
36. 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
37. • Development Information • Feature Matrix • Software Catalogue • PGXN • PG-Strom • DB-Engines RDBMS Ranking • DBMS Comparison • Contribuições da Timbira no PostgreSQL Links interessantes
38. contato@timbira.com.br