Share
Embed
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
Transcript
38 slides
-
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 • 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 -
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 -
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 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!!! -
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 -
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) -
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.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 8.0 (jan/2005) • CREATE TABLESPACE -- define a new
tablespace • SAVEPOINT / RELEASE SAVEPOINT / ROLLBACK TO SAVEPOINT • Composite Types -
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.3 (set/2013) • CREATE EVENT TRIGGER -- define a
new event trigger • CREATE MATERIALIZED VIEW -- define a new materialized view -
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