[NoSQL BA 2021] NoSQL + SQL = PostgreSQL

Slide 1

Slide 1 text

NoSQL + SQL = PostgreSQL @fabriziomello

Slide 2

Slide 2 text

Fabrízio de Royes Mello • Empreendedor • Colaborador PostgreSQL • Pai, Marido, etc ...

Slide 3

Slide 3 text

No content

Slide 4

Slide 4 text

NoSQL nasceu para resolver ... - Escalabilidade - Modelo Relacional - SQL

Slide 5

Slide 5 text

Escalabilidade Vertical e Horizontal

Slide 6

Slide 6 text

http://pudgylogic.blogspot.com/2016/01/horizontal-vs-vertical-scaling.html

Slide 7

Slide 7 text

Escalabilidade VERTICAL (PostgreSQL) ● Índices (btree, hash, gin, gist, brin, bloom, …) ● Particionamento de Tabelas ● Operações Paralelo (queries, índices, etc) ● JIT (compilar execução) ● Full Text Search ● Extensibilidade: ○ PostGIS, TimescaleDB, PGStrom, TableAM

Slide 8

Slide 8 text

Escalabilidade HORIZONTAL (PostgreSQL) 1. Load Balancing com réplicas Read-Only 2. Foreign Data Wrappers 3. CitusDB (extensão)

Slide 9

Slide 9 text

https://www.percona.com/blog/2018/10/02/scaling-postgresql-using-connection-poolers-and-load-balancers-for-an-enterprise-grade-environment/ 1)

Slide 10

Slide 10 text

https://medium.com/@rondineli.gomes.araujo/testing-pgbouncer-pgpool-load-balancing-d0545e6d091 1)

Slide 11

Slide 11 text

http://www.3manuek.com/postgresmanualsharding 2)

Slide 12

Slide 12 text

https://docs.citusdata.com/en/v10.0/develop/reference_processing.html#citus-query-processing 3)

Slide 13

Slide 13 text

https://docs.citusdata.com/en/v10.0/get_started/concepts.html 3)

Slide 14

Slide 14 text

Modelo Relacional Tabelas e Relacionamentos

Slide 15

Slide 15 text

Diagrama ER

Slide 16

Slide 16 text

PostgreSQL (modelo híbrido)

Slide 17

Slide 17 text

PostgreSQL (modelo híbrido) https://gist.github.com/rponte/bf362945a1af948aa04b587f8ff332f8 INT4 / INT8

Slide 18

Slide 18 text

DDL CREATE TABLE customer ( id BIGSERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL, data JSONB ); CREATE INDEX idx_customer_data ON customer USING gin (data); CREATE INDEX idx_customer_data_birthdate ON customer ((data->>'birthdate'));

Slide 19

Slide 19 text

Duas linhas com documentos JSON INSERT INTO customer (name, data) VALUES ( 'Fulano de Tal', $$ { "phones": ["+55 (51) 99888-7766", "+55 (53) 3344-5566" ], "address": "Rua Sem Nome, 12345", "city": "Bagé" } $$::JSONB ), ( 'Beltrano de Tal', $$ { "address": "Avenida Com Nome, 54321", "city": "Bagé", "state": "RS", "birthdate": "1978-01-01" } $$::JSONB );

Slide 20

Slide 20 text

Recuperação das linhas SELECT name, data->>'city', data->>'state', data->'phones', data->>'birthdate' FROM customer WHERE data->>'city' = 'Bagé';

Slide 21

Slide 21 text

Resultados

Slide 22

Slide 22 text

Similar para outros Tipos de Dados ● XML ● HSTORE (chave/valor) ● Arrays

Slide 23

Slide 23 text

SQL muito além do JOIN

Slide 24

Slide 24 text

Lembram desse Diagrama ER ??

Slide 25

Slide 25 text

E se ﬁzéssemos assim?

Slide 26

Slide 26 text

Exemplo SQL -- Using JOIN clause SELECT customer.id, customer.name, city.name AS "city" FROM customer JOIN city ON city.id = customer.city; -- No JOIN clause (thanks to Natural Key) SELECT id, name, city FROM customer;

Slide 27

Slide 27 text

SQL é muito mais que JOIN (SQL:1999) ● LATERAL ● Grouping Sets ● WITH [ RECURSIVE ] ● FILTER (SQL:2003) ● OVER and PARTITION BY (SQL:2003) ● TABLESAMPLE (SQL:2003) ● …