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
   

   View Slide

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
   

   View Slide

3. Infância
   

   View Slide

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
   

   View Slide

5. Juventude
   

   View Slide

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
   

   View Slide

7. Postgres Kaioken
   

   View Slide

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!
   

   View Slide

9. Forks
   

   View Slide

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)
    

    View Slide

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!!!
    

    View Slide

12. Postgres super sayajin
    

    View Slide

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
    

    View Slide

14. Postgres super sayajin 3
    

    View Slide

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)
    

    View Slide

16. Postgres God!
    

    View Slide

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 )
    

    View Slide

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
    

    View Slide

19. PostgreSQL 7.1 (abr/2001)
    • Binary Strings
    

    View Slide

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

    View Slide

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
    

    View Slide

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

    View Slide

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

    View Slide

24. PostgreSQL 8.2 (dez/2006)
    • XML Document Support
    

    View Slide

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

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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
    

    View Slide

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

    View Slide

38. contato@timbira.com.br
    

    View Slide