Escalando PostgreSQL Vinícius B a il Alonso

Quem sou eu

Quem sou eu • Engenheiro de software na Solides Tecnologia • Graduado em Sistemas para a Internet (UTFPR Guarapuava) • Mestrando em Computação Aplicada (UTFPR Curitiba)

Escalabilidade

Scalability is the measure of a system’s ability to increase or decrease in performance and cost in response to changes in application and system processing demands.

Usuário Aplicação Internet

Aplicação Internet

Load Balancer Internet Replica 1 Replica 2 Replica 3

Questão sobre escalabilidade Exemplo Soluções Arquivos estáticos CSS, Javascript, imagens CDN, cache Tarefas bloqueantes Upload de imagens, cálculos demorados, envio de emails, etc Background jobs Banco de dados Alta demanda de escritas ou leituras, tabelas grandes ???

Load Balancer Internet Replica 3 Replica 2 Replica 1 Banco de dados

PostgreSQL

PostgreSQL • Sistema gerenciador de banco de dados (SGBD) open source • Criado em 1996 • ACID • Atomicidade • Consistência • Isolamento • Durabilidade

Pensando em nível de aplicação

pgbench • Ferramenta utilizada fazer benchmarks • Simula clientes simultáneos • Foi utilizado para fazer os testes nessa apresentação

Otimizações • Detectando consultas mal escritas • Parâmetros padrões do banco de dados • Falta de índices • Falta de vacuuming • Não utilizando materialized views

Detectando consultas mal escritas

Comando EXPLAIN

Extensão pg_stat_statements

Parâmetros padrões do banco

Parâmetros padrões do banco • shared_bu ff ers • wal_bu ff ers • e ff ective_cache_size • work_men • maintenance_work_men

Falta de índices

Falta de índices

Falta de índices Benchm a rk sem índice

Falta de índices Benchm a rk com índice

Falta de Vacuuming

Vacuum

Materialized views

Materialized views

Pensando em nível de arquitetura

Load Balancer Internet Replica 3 Replica 2 Replica 1 Banco de dados

Replica 3 Replica 2 Replica 1 Replica 1 Replica 2 Primário

Técnicas de replicação

Streaming Replication

Streaming Replication • Permite sincronizar dados entre instâncias • Transmite dados do banco primário para as réplicas utilizando arquivos WAL (log shipping) • Replicas operam como somente leitura • Replicas são cópias exatas do banco primário

Replica 3 Replica 2 Replica 1 Replica 1 Replica 2 Primário Escrita INSERT, UPDATE, DELETE WAL Para o id 1 Atualize valor para 5

Con f igurando banco primário • Alterar postgresql.conf para habilitar rede • Criar um usuário para a réplica (boa prática) • Permitir acesso remoto no arquivo pg_hba.conf • Reiniciar banco primário

Con f igurando banco primário H a bilit a r rede no a rquivo postgresql.conf

Con f igurando banco primário Cri a ndo usu á rio d a replic a

Con f igurando banco réplica Permitindo a cesso remoto pg_hb a .conf

Con f igurando banco réplica Copi a ndo a rquivos de con f igur a ç ã o com pg_b a seb a ckup

Tabela pg_stat_replication no banco primário

Logical Replication

Logical Replication • Transmite SQL para suas replicas • Opera utilizando publisher e subscriber • Replicas não precisam ser iguais ao banco primário, podendo replicar apenas uma tabela • Permite utilizar diferentes versões do PostgreSQL

Replica 3 Replica 2 Replica 1 Replica 1 Replica 2 Primário Escrita INSERT, UPDATE, DELETE INSERT INTO users VALUES(1, ‘Teste’, ‘teste@gmail.com');

Con f igurando banco publisher • Alterar o valor de wal_level para logical no arquivo postgresql.conf • Reiniciar o banco • Criar um banco e tabela para teste • Copiar estrutura para o banco de replica • Criar publisher no banco primário • Criar subscription no banco de replica

Con f igurando banco publisher Arquivo postgresql.conf

Con f igurando banco publisher Cri a ndo b a nco pelo psql

Con f igurando banco publisher Cri a ndo t a bel a e popul a ndo

Con f igurando banco publisher Copi a ndo estrutur a p a r a o b a nco de replic a

Con f igurando banco publisher Cri a ndo public a tion p a r a a t a bel a

Con f igurando banco subscriber Cri a ndo subscription p a r a a t a bel a

Connection polling

Connection Polling • É o processo de criar uma conexão com o banco de dados • É necessário fazer uma chamada de sistemas fork e reservar memória • Uma conexão no PostgreSQL consome de 2mb a 3mb

pgbouncer • Gerenciador de conexões • Utiliza fi las • Possui 3 modos • Session pooling (padrão) • Transaction pooling • Statement pooling

Replica 3 Replica 2 Replica 1 Replica 1 Replica 2 Primário pgbouncer

pgbouncer Inst a l a ç ã o e con f igur a ç ã o b á sic a /etc/pgbouncer/pgbouncer.ini

pgbouncer Acess a ndo pelo psql

Particionando tabelas

Particionando tabelas • Técnica utilizada para dividir grandes tabelas • Quando uma tabela é muito grande o vacuum do banco se torna lento e as operações também • Partições começam por uma chave

Particionando tabelas Primário SELECT * FROM people WHERE age > 25; age > 25 age < 25

Particionando tabelas Cri a ndo um a t a bel a com su a s p a rtições

Particionando tabelas Cri a ndo um a t a bel a com su a s p a rtições

Obrigado!

Contato contato@viniciusalonso.com @alonsoemacao viniciusalonso

Referências • https://www.gartner.com/en/information-technology/glossary/ scalability#:~:text=Scalability%20is%20the%20measure%20of,application%20and%20system%20processing%2 0demands. • https://www.databricks.com/glossary/acid- transactions#:~:text=ACID%20is%20an%20acronym%20that,operations%20are%20called%20transactional%20 systems. • https://www.crunchydata.com/blog/tentative-smarter-query-optimization-in-postgres-starts-with- pg_stat_statements • https://www.postgresql.org/docs/current/warm- standby.html#:~:text=PostgreSQL%20implements%20 fi le%2Dbased%20log,far%20side%20of%20the%20globe. • https://www.postgresql.org/docs/current/rules-materializedviews.html • https://www.pgbouncer.org/