Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Database Level UP

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/

Fábio Telles Rodriguez

February 06, 2019
Tweet

More Decks by Fábio Telles Rodriguez

Other Decks in Programming

Transcript

  1. PostgreSQL
    Level UP
    Fábio Telles Rodriguez

    View full-size 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
    [email protected]
    • @telles

    View full-size slide

  3. 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 full-size slide

  4. 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 full-size slide

  5. Postgres Kaioken

    View full-size slide

  6. 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 full-size slide

  7. 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 full-size slide

  8. 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 full-size slide

  9. Postgres super sayajin

    View full-size slide

  10. 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 full-size slide

  11. Postgres super sayajin 3

    View full-size slide

  12. 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 full-size slide

  13. Postgres God!

    View full-size slide

  14. 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 full-size slide

  15. 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 full-size slide

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

    View full-size slide

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

    View full-size slide

  18. 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 full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  23. 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 full-size slide

  24. 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 full-size slide

  25. 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 full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  33. 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 full-size slide

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

    View full-size slide