Porque usar PostgreSQL (Se você ainda não o faz)

Nessa palestra iremos explorar algumas funcionalidades do PostgreSQL, com dicas e truques que irão ajudar no seu uso diário. Além disso você verá porque você deve considerar o PostgreSQL em vez do MySQL se você ainda não fez isso.

Palestra apresentada no DevInSantos 2015

1. @fnando PORQUE USAR POSTGRESQL SE VOCÊ AINDA NÃO O FAZ.
2. @fnando PORQUE USAR POSTGRESQL SE VOCÊ AINDA NÃO O FAZ.
3. NANDO VIEIRA
6. POR QUE NÃO USAR O MYSQL? O MySQL não é o melhor banco de dados que você poderia estar usando. Entenda o porquê.
7. A COMUNIDADE SE DIVIDIU EM DUAS. Com a aquisição da MySQL, surgiu um fork chamado MariaDB. Qual distribuição você irá usar?
8. O MYSQL É POBRE EM FUNCIONALIDADES. Muitas funcionalidades implementadas por outros bancos de dados, incluindo SQLite, não existem no MySQL. https://modern-sql.com/
9. SEM ROLLBACK EM DE MUDANÇAS NO DDL Se você usar Ruby on Rails já deve ter enfrentado problemas de ter que desfazer migrações na mão.
10. CREATE TABLE foo ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL ); SHOW TABLES; +----------------+ | Tables_in_test | +----------------+ | foo | +----------------+ 1 row in set (0.00 sec) MYSQL ROLLBACK; BEGIN;
11. \d List of relations Schema | Name | Type | Owner --------+------+------+------- (0 rows) CREATE TABLE foo ( id serial NOT NULL PRIMARY KEY ); ROLLBACK; POSTGRES BEGIN;
12. INCONSISTÊNCIA DE DADOS O MySQL é praticamente o JavaScript dos bancos de dados.
13. INSERT INTO foo (text_col, numeric_col, date_col) VALUES ("abc", 123.45, "LOL"); CREATE TABLE foo ( text_col VARCHAR(1), numeric_col DECIMAL(1,1), date_col DATE ); MYSQL Query OK, 1 row affected, 3 warnings (0.00 sec) SELECT * FROM foo; +----------+-------------+------------+ | text_col | numeric_col | date_col | +----------+-------------+------------+ | a | 0.9 | 0000-00-00 | +----------+-------------+------------+ 1 row in set (0.00 sec)
14. CREATE TABLE foo ( text_col VARCHAR(1), numeric_col DECIMAL(1,1), date_col DATE ); INSERT INTO foo (text_col) VALUES ('abc'); ERROR: 22001: value too long for type character varying(1) INSERT INTO foo (numeric_col) VALUES (123.45); ERROR: 22003: numeric field overflow DETAIL: A field with precision 1, scale 1 must round to an absolute value less than 1. INSERT INTO foo (date_col) VALUES ('LOL'); ERROR: 22007: invalid input syntax for type date: "LOL" POSTGRES
15. INSERT IGNORE INTO foo (text_col, numeric_col, date_col) VALUES ("abc", 123.45, "LOL"); Query OK, 1 row affected, 3 warnings (0.01 sec) SET global sql_mode = "TRADITIONAL"; MYSQL Î Î INSERT INTO foo (text_col) VALUES ("abc"); ERROR 1406 (22001): Data too long for column 'text_col' at row 1 Dá para corrigir este comportamento… kinda. ¯\_(ϑ)_/¯ INSERT INTO foo (numeric_col) VALUES (123.45); ERROR 1264 (22003): Out of range value for column 'numeric_col' at row 1 INSERT INTO foo (date_col) VALUES ("LOL"); ERROR 1292 (22007): Incorrect date value: 'LOL' for column 'date_col' at row 1
16. COMPARAÇÃO DE STRINGS PODE SER UM PROBLEMA Isso pode levar à falhas de segurança dependendo de como o seu framework funciona.
17. +----+------------------+ | id | email | +----+------------------+ | 1 | john@example.com | | 2 | mary@example.com | +----+------------------+ 2 rows in set, 2 warnings (0.00 sec) SELECT * FROM users; MYSQL +----+------------------+ | id | email | +----+------------------+ | 1 | john@example.com | | 2 | mary@example.com | +----+------------------+ 2 rows in set (0.00 sec) SELECT * FROM users WHERE email = 0; http://fnando.me/1e1
18. FULL-TEXT SEARCH. TRANSAÇÕES. ESCOLHA UM. *Não, você não pode ter ambos. *INNODB TEM SUPORTE PARA FULL-TEXT SEARCH NA VERSÃO 5.6.5+, MAS AINDA NÃO É CONSIDERADO PRONTO PARA PRODUÇÃO.
19. CREATE TABLE articles ( content text not null ) ENGINE MyISAM; SHOW WARNINGS; MYSQL BEGIN; INSERT INTO articles (content) values ('Some text'); ROLLBACK; Query OK, 0 rows affected, 1 warning (0.02 sec) +---------+------+---------------------------------------------------------------+ | Level | Code | Message | +---------+------+---------------------------------------------------------------+ | Warning | 1196 | Some non-transactional changed tables couldn't be rolled back | +---------+------+---------------------------------------------------------------+ 1 row in set (0.01 sec) Î
20. COM POSTGRESQL VOCÊ TEM TUDO ISSO E MUITO MAIS!
21. GRANDE DIVERSIDADE DE TIPOS DE CAMPOS O PostgreSQL possui muitos campos nativamente, como array, json, uuid, e mais.
23. SUPORTE PARA CAMPOS DO TIPO JSON {} Você não precisa mais usar algo como *MongoDB para ser "schema-less". *http://fnando.me/rb
24. JSON PostgreSQL 9.2 Armazena o texto as-it-is Não suporta índices JSON-B PostgreSQL 9.4 Armazena representação binária Suporta índices
25. 0 1.25 2.5 3.75 5 Data Load Insert Select Disk Usage 210% 465% 295% 276% 100% 100% 100% 100% PostgreSQL MongoDB http://fnando.me/1e2 Performance (maior é pior)
26. class CreateUsers < ActiveRecord::Migration def change enable_extension "citext" create_table :users do |t| t.text :name, null: false t.citext :username, null: false t.jsonb :preferences, null: false, default: "{}" end add_index :users, :preferences, using: :gin end end Suporte nativo no Rails à partir da versão 4.2
27. user = User.create!({ name: "John Doe", username: "johndoe", preferences: { twitter: "johndoe", github: "johndoe", blog: "http://example.com" } }) # Show preferences. user.preferences #=> {"blog"=>"http://example.com", "github"=>"johndoe", "twitter"=>"johndoe"} # Get blog. user.preferences["blog"] #=> http://example.com Usando o atributo JSON
28. class User < ActiveRecord::Base store_accessor :preferences, :twitter, :github, :blog end user.twitter = "fnando" Criando métodos de acesso às propriedades
29. # preferences->newsletter = true User.where("preferences @> ?", {newsletter: true}.to_json) # preferences->interests = ["ruby", "javascript", "python"] User.where("preferences -> 'interests' ? :language", language: "ruby") CREATE INDEX preferences_interests_on_users ON users USING GIN ((preferences->'interests')) # preferences->twitter AND preferences->github User.where("preferences ?& array[:keys]", keys: ["twitter", "github"]) # preferences->twitter OR preferences->github User.where("preferences ?| array[:keys]", keys: ["twitter", "github"]) # preferences->state = "SP" AND preferences->city = "São Paulo" User.where("preferences @> ?", {city: "San Francisco", state: "CA"}.to_json) Fazendo queries no campo jsonb
30. SUPORTE PARA CAMPOS DO TIPO ARRAY [] Nem toda lista precisa estar em uma tabela separada de banco de dados.
31. SELECT * FROM articles; id | title | tags ----+----------------------------------------------------------+-------------------------------- 1 | Using ES6 with Asset Pipeline on Ruby on Rails | {javascript,rails,ruby} 2 | Creating generators and executables with Thor | {ruby} 3 | Creating custom Minitest reporters | {ruby,testing} 4 | Using UUID with PostgreSQL and ActiveRecord | {rails,postgresql,activerecord} 5 | Using PostgreSQL and jsonb with Ruby on Rails | {rails,postgresql} 6 | Using PostgreSQL and hstore with Rails | {rails,postgresql} 7 | Using insensitive-case columns in PostgreSQL with citext | {rails,postgresql} 8 | Setting up Ember.js (ember-cli) with Rails | {rails,javascript} 9 | Setting up Ember.js with Rails | {rails,javascript} (9 rows) Selecione todos os posts
32. id | title | tags ----+------------------------------------------------+------------------------- 1 | Using ES6 with Asset Pipeline on Ruby on Rails | {javascript,rails,ruby} 2 | Creating generators and executables with Thor | {ruby} 3 | Creating custom Minitest reporters | {ruby,testing} (3 rows) SELECT * FROM articles WHERE tags && ARRAY['ruby']; Selecione todos os posts marcados com ruby
33. id | title | tags ----+------------------------------------------------+------------------------- 1 | Using ES6 with Asset Pipeline on Ruby on Rails | {javascript,rails,ruby} 2 | Creating generators and executables with Thor | {ruby} 3 | Creating custom Minitest reporters | {ruby,testing} 8 | Setting up Ember.js (ember-cli) with Rails | {rails,javascript} 9 | Setting up Ember.js with Rails | {rails,javascript} (5 rows) SELECT * FROM articles WHERE tags && ARRAY['ruby', 'javascript']; Selecione todos os posts marcados com ruby ou javascript
34. id | title | tags ----+----------------------------------------------------------+--------------------------------- 4 | Using UUID with PostgreSQL and ActiveRecord | {rails,postgresql,activerecord} 5 | Using PostgreSQL and jsonb with Ruby on Rails | {rails,postgresql} 6 | Using PostgreSQL and hstore with Rails | {rails,postgresql} 7 | Using insensitive-case columns in PostgreSQL with citext | {rails,postgresql} (4 rows) SELECT * FROM articles WHERE tags @> ARRAY['rails', 'postgresql']; Selecione todos os posts marcados com rails e postgresql
35. WITH tags AS ( SELECT lower(unnest(tags)) AS tag FROM articles ) SELECT tag, count(tag) AS occurrences FROM tags GROUP BY tag Selecione as tags com contagem de ocorrências (tag cloud) tag | occurrences --------------+------------- postgresql | 4 rails | 7 activerecord | 1 javascript | 3 ruby | 3 testing | 1 (6 rows)
36. ℹAs colunas do tipo array só podem conter um tipo de dados, exclusivamente. Não é possível misturar tipos de dados diferentes.
37. SUPORTE PARA UUIDS COMO CHAVE PRIMÁRIA Evite todos os problemas de gerenciamento de chaves primárias sequenciais de um modo simples.
38. CREATE EXTENSION "uuid-ossp"; CREATE TABLE users ( id uuid PRIMARY KEY NOT NULL DEFAULT uuid_generate_v4(), email citext NOT NULL UNIQUE ); INSERT INTO users (email) VALUES ('john@example.com'); SELECT * FROM users; id | email --------------------------------------+------------------ a38a3f0a-ce50-405a-804b-2c85379f1c5a | john@example.com (1 row)
39. Embora o Rails tenha suporte para uuid, ele não permite configurar facilmente o tipo de chaves primárias. http://fnando.me/1e3
40. E VOCÊ AINDA PODE USAR TIPOS PERSONALIZADOS. Com extensões, é possível adicionar tipos personalizados como e-mail, URLs e mais.
41. http://fnando.me/1ec CREATE EXTENSION citext; CREATE TABLE users ( id serial PRIMARY KEY NOT NULL, username citext NOT NULL UNIQUE ); INSERT INTO users (username) VALUES ('john'); INSERT INTO users (username) VALUES ('JOHN'); ERROR: 23505: duplicate key value violates unique constraint "users_username_key" DETAIL: Key (username)=(JOHN) already exists.
42. http://fnando.me/1ed CREATE EXTENSION emailaddr; CREATE TABLE users ( id serial PRIMARY KEY NOT NULL, email emailaddr NOT NULL UNIQUE ); INSERT INTO users (email) VALUES ('john'); ERROR: 22P02: invalid input syntax for type emailaddr: missing "@"
43. http://fnando.me/1ee CREATE EXTENSION uri; INSERT INTO users (site) VALUES ('invalid'); CREATE TABLE users ( id serial PRIMARY KEY NOT NULL, site uri NOT NULL CHECK ( uri_scheme(site::uri) IS NOT NULL AND uri_scheme(site::uri) ~ '^https?$') ); INSERT INTO users (site) VALUES ('http://example.com'); ERROR: 23514: new row for relation "users" violates check constraint "users_site_check" DETAIL: Failing row contains (2, invalid).
44. GARANTA A INTEGRIDADE DE DADOS Com o PostgreSQL você pode garantir a integridade dos dados com regras personalizadas. ⚠
45. USE CHECKS PARA VALIDAR O FORMATO DOS DADOS ✓ Use constraints para garantir a integridade dos dados antes de persistir as informações.
46. CREATE TABLE events ( id serial NOT NULL PRIMARY KEY, starts_at timestamp NOT NULL, ends_at timestamp NOT NULL CHECK (ends_at >= starts_at) ); INSERT INTO events (starts_at, ends_at) VALUES ('2015-11-28'::timestamp, '2015-11-27'::timestamp); INSERT INTO events (starts_at, ends_at) VALUES ('2015-11-28'::timestamp, '2015-11-29'::timestamp); ERROR: 23514: new row for relation "events" violates check constraint "events_check" DETAIL: Failing row contains (2, 2015-11-28 00:00:00, 2015-11-27 00:00:00).
47. ⚠Apenas constraints de banco de dados podem garantir a integridade das informações. Validações do seu framework são meramente informativas.
48. ACEITE QUE DUPLICAÇÃO DE REGRAS IRÁ EXISTIR Ao garantir a integridade de seus dados, você acabará com regras duplicadas para informar o usuário. E isso não é um problema.
49. CREATE TABLE events ( id serial NOT NULL PRIMARY KEY, starts_at timestamp NOT NULL, ends_at timestamp NOT NULL CHECK (ends_at > starts_at) ); class Event < ActiveRecord::Base validates_datetime :ends_at, after: :starts_at end http://fnando.me/1e4
50. ORGANIZE SUAS QUERIES COM CTES SQL não é das linguagens mais legíveis do mundo, mas você pode aumentar a legibilidade com Common Table Expressions.
51. CTES DEFINEM A NARRATIVA DAS QUERIES Queries complexas podem ser quebradas em pedaços menores e mais simples de entender.
52. CREATE TABLE countries ( id serial PRIMARY KEY NOT NULL, name text NOT NULL, population bigint NOT NULL DEFAULT 0 ); SELECT name FROM countries WHERE population > (SELECT population FROM countries WHERE name = 'Brazil') ORDER BY name ASC; Exibir países mais populosos que o Brasil. name --------------- China India United States Indonesia (4 rows)
53. Exibir países mais populosos que o Brasil. name --------------- China India United States Indonesia (4 rows) WITH brazil AS ( SELECT population FROM countries WHERE name = 'Brazil' ) SELECT name FROM countries, brazil WHERE countries.population > brazil.population ORDER BY countries.population DESC;
54. SUPORTE PARA FULL-TEXT SEARCH O suporte para full-text search do PostgreSQL é suficiente e muito melhor que consultas com LIKE.
56. O Rails possui integração facilitada com full-text search do PostgreSQL através da gem pg_search. http://fnando.me/1eb
57. AGREGAÇÃO COM FILTROS CONDICIONAIS O PostgreSQL permite escrever filtros sem a necessidade de usar hacks.
58. Exibir a quantidade de usuários total, removidos e que ativaram a conta. SELECT COUNT(id) AS total, SUM(CASE WHEN deleted_at IS NOT NULL THEN 1 ELSE 0 END) AS deleted_accounts, SUM(CASE WHEN activated_at IS NOT NULL THEN 1 ELSE 0 END) AS activated_accounts FROM users; total | deleted_accounts | activated_accounts -------+------------------+-------------------- 3000 | 311 | 2266 (1 row)
59. Exibir a quantidade de usuários total, removidos e que ativaram a conta. SELECT COUNT(id) AS total, COUNT(id) FILTER (WHERE deleted_at IS NOT NULL) AS deleted_accounts, COUNT(id) FILTER (WHERE activated_at IS NOT NULL) AS activated_accounts FROM users; total | deleted_accounts | activated_accounts -------+------------------+-------------------- 3000 | 311 | 2266 (1 row)
60. SUPORTE PARA VIEWS COM CACHING O PostgreSQL tem suporte para views que persistem os resultados como se fossem tabelas.
61. Quantidade de cadastros por dia nos últimos 30 dias WITH calendar AS ( SELECT day::date FROM generate_series( current_date - interval '1 month', current_date, INTERVAL '1 day' ) day ) SELECT day, count(id) AS count FROM calendar LEFT JOIN users ON created_at::date = day WHERE date_trunc('month', day) = date_trunc('month', current_date) GROUP BY day ORDER BY day; CREATE MATERIALIZED VIEW subscription_stats AS
62. Quantidade de cadastros por dia nos últimos 30 dias SELECT * FROM subscription_stats ORDER BY day DESC LIMIT 1; day | count ------------+------- 2015-11-26 | 250 (1 row) INSERT INTO users (created_at) VALUES (current_date); SELECT * FROM subscription_stats ORDER BY day DESC LIMIT 1; day | count ------------+------- 2015-11-26 | 250 (1 row)
63. Quantidade de cadastros por dia nos últimos 30 dias SELECT * FROM subscription_stats ORDER BY day DESC LIMIT 1; day | count ------------+------- 2015-11-26 | 251 (1 row) REFRESH MATERIALIZED VIEW subscription_stats;
64. Usando materialized views no ActiveRecord class SubscriptionStats < ActiveRecord::Base def self.refresh connection.execute "REFRESH MATERIALIZED VIEW subscription_stats" end end SubscriptionStats.order(day: :desc).take(1) [#<SubscriptionStats day: "2015-11-26", count: 251>] User.create! SubscriptionStats.refresh SubscriptionStats.order(day: :desc).take(1) [#<SubscriptionStats day: "2015-11-26", count: 252>]
65. SUPORTE PARA WINDOW FUNCTIONS Uma maneira simples de computar cálculos em diversos registros relacionados a um grupo.
66. Listar os salários SELECT * FROM employees; id | name | salary | department ----+------+---------+------------ 1 | John | 4500.00 | accounting 2 | Mary | 5600.00 | sales 3 | Paul | 7700.00 | marketing 4 | Jane | 8400.00 | accounting 5 | Mark | 7400.00 | sales (5 rows)
67. Listar os salários, ordenando pelo ranking no departamento SELECT employees.*, rank() OVER (PARTITION BY department ORDER BY salary DESC) FROM employees; id | name | salary | department | rank ----+------+---------+------------+------ 4 | Jane | 8400.00 | accounting | 1 1 | John | 4500.00 | accounting | 2 3 | Paul | 7700.00 | marketing | 1 5 | Mark | 7400.00 | sales | 1 2 | Mary | 5600.00 | sales | 2 (5 rows)
68. Exibir quantidade de cadastros na última semana SELECT created_at::date, count(id), sum(count(id)) OVER (ORDER BY created_at::date) AS acc_count FROM users WHERE created_at > current_date - interval '1 week' GROUP by created_at::date; created_at | count | acc_count ------------+-------+----------- 2015-11-19 | 154 | 154 2015-11-20 | 143 | 297 2015-11-21 | 152 | 449 2015-11-22 | 140 | 589 2015-11-23 | 125 | 714 2015-11-24 | 126 | 840 2015-11-25 | 160 | 1000 (7 rows)
69. CRIE UMA SÉRIE DE VALORES No PostgreSQL é muito fácil criar séries sem ter que recorrer a loops ou number tables.
70. SELECT number FROM generate_series(1, 10) number; Gerando séries SELECT chr(generate_series(48, 57)) letter UNION SELECT chr(generate_series(65, 90)) letter ORDER BY letter ASC; SELECT day::date FROM generate_series('2015-01-01'::date, '2015-01-31'::date, INTERVAL '1 day') day; SELECT number FROM generate_series(1, 10, 2) number;
71. SELECT created_at::date AS day, count(id) AS count FROM articles WHERE date_trunc('month', created_at) = date_trunc('month', current_date) GROUP by created_at::date ORDER BY day; day | count ------------+------- 2015-11-02 | 1 2015-11-04 | 2 2015-11-05 | 1 ... 2015-11-17 | 1 2015-11-23 | 1 (11 rows) Exibir quantidade de artigos criados por dia (não contínuo)
72. WITH calendar AS ( SELECT day::date FROM generate_series( current_date - interval '1 month', current_date, INTERVAL '1 day' ) day ) SELECT day, count(id) AS count FROM calendar LEFT JOIN articles ON created_at::date = day WHERE date_trunc('month', day) = date_trunc('month', current_date) GROUP BY day ORDER BY day; day | count ------------+------- 2015-11-01 | 0 2015-11-02 | 1 2015-11-03 | 0 2015-11-04 | 2 2015-11-05 | 1 2015-11-06 | 0 2015-11-07 | 2 2015-11-08 | 1 2015-11-09 | 1 2015-11-10 | 1 2015-11-11 | 1 ... 2015-11-23 | 1 2015-11-24 | 0 2015-11-25 | 0 2015-11-26 | 0 (26 rows) Exibir quantidade de artigos criados por dia (contínuo)
73. INSPECIONE SEU BANCO DE DADOS No PostgreSQL é fácil saber o que está acontecendo no seu banco de dados neste momento.
74. INFORMAÇÃO SOBRE TODAS AS CONEXÕES A tabela pg_stat_activity retorna informações sobre todas as conexões abertas no momento.
75. SELECT * FROM pg_stat_activity; -[ RECORD 1 ]----+-------------------------------- datid | 812156 datname | test pid | 50661 usesysid | 10 usename | fnando application_name | psql client_addr | [NULL] client_hostname | [NULL] client_port | -1 backend_start | 2015-11-26 09:45:40.544477-02 xact_start | 2015-11-26 16:26:30.917594-02 query_start | 2015-11-26 16:26:30.917594-02 state_change | 2015-11-26 16:26:30.917644-02 waiting | f state | active backend_xid | [NULL] backend_xmin | 2222027 query | SELECT * FROM pg_stat_activity;
76. LISTA DE QUERIES MAIS LENTAS A extensão pg_stat_statements retorna informações sobre as queries executadas.
77. # /etc/postgresql/9.4/main/postgresql.conf shared_preload_libraries = 'pg_stat_statements' pg_stat_statements.max = 10000 pg_stat_statements.track = all CREATE EXTENSION pg_stat_statements; SELECT pg_stat_statements_reset();
78. SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent FROM pg_stat_statements ORDER BY total_time DESC LIMIT 5; -[ RECORD 1 ]------------------------------------------------------------------- query | UPDATE pgbench_branches SET bbalance = bbalance + ? WHERE bid = ?; calls | 3000 total_time | 5390.771 rows | 3000 hit_percent | 99.9873144741849550
79. LISTA DE LOCKS DO POSTGRESQL Com as tabelas pg_stat_activity e pg_locks é possível saber quais locks estão em uso.
80. SELECT blocked_locks.pid AS blocked_pid, blocked_activity.usename AS blocked_user, blocking_locks.pid AS blocking_pid, blocking_activity.usename AS blocking_user, blocked_activity.query AS blocked_statement, blocking_activity.query AS current_statement_in_blocking_process, blocked_activity.application_name AS blocked_application, blocking_activity.application_name AS blocking_application FROM pg_catalog.pg_locks blocked_locks JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid AND blocking_locks.pid != blocked_locks.pid JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid WHERE NOT blocked_locks.GRANTED http://fnando.me/1ef CREATE VIEW pg_current_locks AS ( );
81. BEGIN; SELECT * FROM users WHERE id = 1 FOR UPDATE; _terminal 1 UPDATE users SET site = 'http://example.com' WHERE id = 1; => Waiting on transaction _terminal 2 SELECT * FROM pg_current_locks; => View locks _terminal 1
82. -[ RECORD 1 ]-------------------------+----------------------------------------------------------- blocked_pid | 33782 blocked_user | fnando blocking_pid | 34236 blocking_user | fnando blocked_statement | update users set site = 'http://example.com' where id = 1; current_statement_in_blocking_process | select * from users where id = 1 for update; blocked_application | psql blocking_application | psql
83. SELECT pg_terminate_backend(34236); SELECT pg_cancel_backend(34236); _terminal 1 FATAL: 57P01: terminating connection due to administrator command LOCATION: ProcessInterrupts, postgres.c:2872 server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Succeeded. SELECT * FROM pg_current_locks; => 0 rows
84. O QUE VEM POR AÍ NO MUNDO DE POSTGRESQL Veja algumas das novas funcionalidades que entrarão nas próximas versões do PostgreSQL. ⋆
85. POSTGRESQL COM SUPORTE A UPSERT A única feature que o MySQL tinha e o PostgreSQL não. Estará disponível na versão 9.5.
86. INSERT INTO visits (url, count) VALUES ('http://example.com', 1) ON CONFLICT (url) DO UPDATE SET count = visits.count + 1; http://fnando.me/1ea SELECT * FROM visits; id | url | count ----+--------------------+------- 6 | http://example.com | 1 (1 row) CREATE TABLE visits ( id serial PRIMARY KEY NOT NULL, url text NOT NULL UNIQUE, count integer NOT NULL );
87. INSERT INTO visits (url, count) VALUES ('http://example.com', 1) ON CONFLICT (url) DO UPDATE SET count = visits.count + 1; SELECT * FROM visits; id | url | count ----+--------------------+------- 6 | http://example.com | 2 (1 row)
88. PARALLEL SEQUENTIAL SCAN Workers irão executar a consulta de forma paralela. Estará disponível na versão 9.6.
89. SELECT * FROM pgbench_accounts WHERE filler LIKE '%a%'; Time: 743.061 ms set max_parallel_degree = 4; SELECT * FROM pgbench_accounts WHERE filler LIKE '%a%'; Time: 213.412 ms http://fnando.me/1e8
90. AINDA É CEDO, PODE RESUMIR PARA MIM? ⏲ tl;dw
91. USE POSTGRESQL EM NOVOS PROJETOS 1. Não faz sentido começar novos projetos em um banco de dados obsoleto como o MySQL.
92. USE SQL MODERNO EM SEUS PROJETOS 2. Felizmente o PostgreSQL é bem adiantado em relação às novas versões da especificação do SQL. https://modern-sql.com
94. https://twitter.com/fuzzychef/status/631222909617311744 I want to thank Oracle for their recent efforts to boost PostgreSQL adoption. Keep up the good work, guys! Josh Berkus @fuzzychef
95. Eu quero agradecer a Oracle pelos seus esforços recentes para aumentar a adoção do PostgreSQL. Continuem com o bom trabalho! Josh Berkus @fuzzychef https://twitter.com/fuzzychef/status/631222909617311744
96. @fnando OBRIGADO.