Download to read offline
Uploaded byFabio Telles Rodriguez
Postgres level up
Fábio Telles Rodriguez is a consultant and DBA with over 15 years of experience with Oracle and PostgreSQL. The document provides an overview of the history and development of PostgreSQL from its early versions in the 1970s-1990s to its modern features and widespread adoption today. It highlights key milestones like the introduction of procedural languages, data types, extensions, and replication capabilities. The open development process and large ecosystem of forks and extensions are also summarized.
Postgres level up
- 1. PostgreSQL Level UP Fábio TellesRodriguez
- 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.
- 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.
- 6. Projetado para serflexí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.
- 8. Licença permissiva “Permission touse, 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.
- 10. Forks https://wiki.postgresql.org/wiki/PostgreSQL_derived_databases • AgensGraph (orientadoa 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ódigoaberto • 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.
- 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.
- 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.
- 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) •CREATE AGGREGATE -- define a new aggregate function • CREATE CONSTRAINT TRIGGER -- define a new constraint trigger • CREATE LANGUAGE -- define a new procedural language • CREATE OPERATOR -- define a new operator • CREATE RULE -- define a new rewrite rule • CREATE TYPE -- define a new data type • CREATE VIEW -- define a new view • LISTEN / NOTIFY / UNLISTEN • SET / SET TRANSACTION / SET CONSTRAINTS • LOAD
- 19. 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
- 20. PostgreSQL 7.1 (abr/2001) •Binary Strings
- 21. PostgreSQL 7.2 (fev/2002) •Object Identifier Types • Pseudo-Types • Arrays
- 22. 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
- 23. PostgreSQL 7.3 (nov/2002) •Object Identifier Types • Pseudo-Types • Arrays
- 24. PostgreSQL 8.0 (jan/2005) •CREATE TABLESPACE -- define a new tablespace • SAVEPOINT / RELEASE SAVEPOINT / ROLLBACK TO SAVEPOINT • Composite Types
- 25. PostgreSQL 8.2 (dez/2006) •XML Document Support
- 26. PostgreSQL 8.3 (fev/2008) •Enumerated Types • Text Search Types • UUID Type • XML Type
- 27. 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
- 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.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
- 30. PostgreSQL 9.2 (set/2012) •JSON Type • Range Types
- 31. PostgreSQL 9.3 (set/2013) •CREATE EVENT TRIGGER -- define a new event trigger • CREATE MATERIALIZED VIEW -- define a new materialized view
- 32. PostgreSQL 9.4 (dez/2014) •pg_lsn Type • jsonb
- 33. PostgreSQL 9.6 (set/2016) •CREATE ACCESS METHOD -- define a new access method
- 34. PostgreSQL 10 (out/2017) •CREATE PUBLICATION — define a new publication • CREATE SUBSCRIPTION — define a new subscription • CREATE STATISTICS — define extended statistics
- 35. PostgreSQL 11 (out/2018) •CALL — invoke a procedure • CREATE PROCEDURE — define a new procedure • Domain types
- 36. Futuro ● Novos forkse novos merges; ● Novas extensões e FDWs ● Storage Engines plugáveis; ● Hooks
- 37. 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
- 38. • Development Information •Feature Matrix • Software Catalogue • PGXN • PG-Strom • DB-Engines RDBMS Ranking • DBMS Comparison • Contribuições da Timbira no PostgreSQL Links interessantes
- 39.