You might not need ElasticSearch

Slide 1

Slide 1 text

Slide 2

Slide 2 text

ElasticSearch é uma ótima ferramenta

Slide 3

Slide 3 text

Leonardo Tegon twitter.com/tegonl github.com/tegon

Slide 4

Slide 4 text

ElasticSearch ! 4 hospedagem 4 monitoramento 4 manutenção You might not need ElasticSearch - @tegonl

Slide 5

Slide 5 text

MVP You might not need ElasticSearch - @tegonl

Slide 6

Slide 6 text

SELECT "products"."title", "categories"."name" AS category_name, "groups"."name" AS group_name FROM "products" INNER JOIN "categories" ON "products"."category_id" = "categories"."id" INNER JOIN "groups" ON "products"."group_id" = "groups"."id" WHERE "products"."title" ILIKE 'batman' OR "categories"."name" ILIKE 'batman' OR "groups"."name" ILIKE 'batman' You might not need ElasticSearch - @tegonl

Slide 7

Slide 7 text

Busca amigável 4 aceitar plurais 4 entender sinônimos 4 desconsiderar artigos e preposições 4 entender erros ortográficos 4 desconsiderar letrar maiúsculas 4 desconsiderar acentos You might not need ElasticSearch - @tegonl

Slide 8

Slide 8 text

Banco de dados You might not need ElasticSearch - @tegonl

Slide 9

Slide 9 text

PostgreSQL Full text search You might not need ElasticSearch - @tegonl

Slide 10

Slide 10 text

texto -> tokens -> lexemes -> documento You might not need ElasticSearch - @tegonl

Slide 11

Slide 11 text

to_tsvector(config, text) You might not need ElasticSearch - @tegonl

Slide 12

Slide 12 text

SELECT to_tsvector('portuguese', 'Batman - O Cavaleiro das Trevas') to_tsvector 'batman':1 'cavaleir':3 'trev':5 You might not need ElasticSearch - @tegonl

Slide 13

Slide 13 text

SELECT to_tsvector('portuguese', 'O Senhor dos Anéis: O Retorno do Rei') to_tsvector 'ané':4 'rei':8 'retorn':6 'senhor':2 You might not need ElasticSearch - @tegonl

Slide 14

Slide 14 text

Extensão para remover acentos CREATE EXTENSION unaccent You might not need ElasticSearch - @tegonl

Slide 15

Slide 15 text

SELECT to_tsvector('portuguese', unaccent('O Senhor dos Anéis: O Retorno do Rei')) to_tsvector 'ane':4 'rei':8 'retorn':6 'senhor':2 You might not need ElasticSearch - @tegonl

Slide 16

Slide 16 text

Query You might not need ElasticSearch - @tegonl

Slide 17

Slide 17 text

to_tsquery(config, text) You might not need ElasticSearch - @tegonl

Slide 18

Slide 18 text

SELECT to_tsquery('portuguese', 'anéis') to_tsquery 'ané' You might not need ElasticSearch - @tegonl

Slide 19

Slide 19 text

SELECT to_tsquery('portuguese', unaccent('anéis')) to_tsquery 'ane' You might not need ElasticSearch - @tegonl

Slide 20

Slide 20 text

Match operator (@@) You might not need ElasticSearch - @tegonl

Slide 21

Slide 21 text

SELECT to_tsvector('portuguese', 'Batman - O Cavaleiro das Trevas') @@ to_tsquery('portuguese', 'trevas') ?column? true You might not need ElasticSearch - @tegonl

Slide 22

Slide 22 text

SELECT to_tsvector('portuguese', 'Batman - O Cavaleiro das Trevas') @@ to_tsquery('portuguese', 'trevas') ?column? true You might not need ElasticSearch - @tegonl

Slide 23

Slide 23 text

SELECT to_tsvector('portuguese', unaccent('O Senhor dos Anéis: O Retorno do Rei')) @@ to_tsquery('portuguese', unaccent('aneis')) ?column? true You might not need ElasticSearch - @tegonl

Slide 24

Slide 24 text

Multi-table Full Text Search You might not need ElasticSearch - @tegonl

Slide 25

Slide 25 text

categories Column Type id bigint name character varying You might not need ElasticSearch - @tegonl

Slide 26

Slide 26 text

groups Column Type id bigint name character varying You might not need ElasticSearch - @tegonl

Slide 27

Slide 27 text

products Column Type id bigint title character varying sales_rank integer category_id bigint group_id bigint You might not need ElasticSearch - @tegonl

Slide 28

Slide 28 text

Multi-table Full Text Search 4 título do produto 4 categoria 4 grupo You might not need ElasticSearch - @tegonl

Slide 29

Slide 29 text

SELECT ( to_tsvector('english', "products"."title") || to_tsvector('english', "categories"."name") || to_tsvector('english', "groups"."name") ) AS document FROM "products" INNER JOIN "categories" ON "products"."category_id" = "categories"."id" INNER JOIN "groups" ON "products"."group_id" = "groups"."id" You might not need ElasticSearch - @tegonl

Slide 30

Slide 30 text

document 'batman':1 'book':5 'fantasi':4 'fiction':3 'scienc':2 'batman':1 'book':5 'fantasi':4 'fiction':3 'scienc':2 'batman':1 'book':5 'fantasi':4 'fiction':3 'scienc':2 'batman':1 'book':5 'fantasi':4 'fiction':3 'scienc':2 'batman':1 'book':5 'fantasi':4 'fiction':3 'scienc':2 'batman':1 'book':5 'fantasi':4 'fiction':3 'scienc':2 'batman':1 'book':5 'comic':2 'graphic':3 'novel':4 'batman':1 'book':5 'comic':2 'graphic':3 'novel':4 'batman':1 'book':5 'comic':2 'graphic':3 'novel':4 'batman':1 'book':5 'comic':2 'graphic':3 'novel':4 'batman':1 'music':3 'soundtrack':2 'batman':1 'music':3 'soundtrack':2 You might not need ElasticSearch - @tegonl

Slide 31

Slide 31 text

SELECT "products"."title", "categories"."name" AS category_name, "groups"."name" AS group_name FROM "products" INNER JOIN "categories" ON "products"."category_id" = "categories"."id" INNER JOIN "groups" ON "products"."group_id" = "groups"."id" WHERE ( to_tsvector('english', "products"."title") || to_tsvector('english', "categories"."name") || to_tsvector('english', "groups"."name") ) @@ to_tsquery('english', 'batman') You might not need ElasticSearch - @tegonl

Slide 32

Slide 32 text

title category_name group_name Batman Soundtracks Music Batman Soundtracks Music Batman - The Movie Action & Adventure Video Batman - The Movie (Special Edition) Action & Adventure Video Batman - The Movie Action & Adventure DVD You might not need ElasticSearch - @tegonl

Slide 33

Slide 33 text

SELECT "products"."title", "categories"."name" AS category_name, "groups"."name" AS group_name FROM "products" INNER JOIN "categories" ON "products"."category_id" = "categories"."id" INNER JOIN "groups" ON "products"."group_id" = "groups"."id" WHERE ( to_tsvector('english', "products"."title") || to_tsvector('english', "categories"."name") || to_tsvector('english', "groups"."name") ) @@ ( to_tsquery('english', 'batman') && to_tsquery('english', 'action') ) You might not need ElasticSearch - @tegonl

Slide 34

Slide 34 text

title category_name group_name Batman - The Movie Action & Adventure Video Batman - The Movie (Special Edition) Action & Adventure Video Batman - The Movie Action & Adventure DVD You might not need ElasticSearch - @tegonl

Slide 35

Slide 35 text

SELECT "products"."title", "categories"."name" AS category_name, "groups"."name" AS group_name FROM "products" INNER JOIN "categories" ON "products"."category_id" = "categories"."id" INNER JOIN "groups" ON "products"."group_id" = "groups"."id" WHERE ( to_tsvector('english', "products"."title") || to_tsvector('english', "categories"."name") || to_tsvector('english', "groups"."name") ) @@ ( to_tsquery('english', 'batman') && to_tsquery('english', 'dvd') ) You might not need ElasticSearch - @tegonl

Slide 36

Slide 36 text

title category_name group_name Batman - The Movie Action & Adventure DVD You might not need ElasticSearch - @tegonl

Slide 37

Slide 37 text

60K registros = ~600ms You might not need ElasticSearch - @tegonl

Slide 38

Slide 38 text

Materialized view You might not need ElasticSearch - @tegonl

Slide 39

Slide 39 text

CREATE MATERIALIZED VIEW searches AS SELECT "products"."id", "products"."title", "categories"."name" AS category_name, "groups"."name" AS group_name, ( to_tsvector('english', "products"."title") || to_tsvector('english', "categories"."name") || to_tsvector('english', "groups"."name") ) AS document FROM "products" INNER JOIN "categories" ON "products"."category_id" = "categories"."id" INNER JOIN "groups" ON "products"."group_id" = "groups"."id" You might not need ElasticSearch - @tegonl

Slide 40

Slide 40 text

SELECT * FROM searches LIMIT 5 id title category_name group_name document 1 Start and Run a Coffee Bar (Start & Run a) Business & Investing Book 'bar':6 'book':11 'busi':9 'coffe':5 'invest':10 'run':3,8 'start':1,7 2 Start and Run a Profitable Coffee Bar Business & Investing Book 'bar':7 'book':10 'busi':8 'coffe':6 'invest':9 'profit':5 'run':3 'start':1 3 The Power of Maps Nonfiction Book 'book':6 'map':4 'nonfict':5 'power':2 4 Invention and Evolution Science Book 'book':5 'evolut':3 'invent':1 'scienc':4 5 The Names (Vintage Contemporaries (Paperback)) Literature & Fiction Book 'book':8 'contemporari':4 'fiction': 7 'literatur':6 'name':2 'paperback':5 'vintag':3 You might not need ElasticSearch - @tegonl

Slide 41

Slide 41 text

SELECT title, category_name, group_name FROM searches WHERE document @@ ( to_tsquery('english', 'batman') && to_tsquery('english', 'dvd') ) title category_name group_name Batman - The Movie Action & Adventure DVD You might not need ElasticSearch - @tegonl

Slide 42

Slide 42 text

60K registros + materialized view = ~20ms You might not need ElasticSearch - @tegonl

Slide 43

Slide 43 text

Atualizando documentos REFRESH MATERIALIZED VIEW searches You might not need ElasticSearch - @tegonl

Slide 44

Slide 44 text

Index para o documento (tsvector) CREATE INDEX document_index ON searches USING GIN (document) You might not need ElasticSearch - @tegonl

Slide 45

Slide 45 text

60K registros + materialized view + index = ~1ms You might not need ElasticSearch - @tegonl

Slide 46

Slide 46 text

Review ElasticSearch 4 rápido 4 completo (ex: faceted search) 4 escalável You might not need ElasticSearch - @tegonl

Slide 47

Slide 47 text

Review PostgreSQL 4 não adiciona custo de infra 4 syntax mais simples 4 reindexação mais trivial You might not need ElasticSearch - @tegonl

Slide 48

Slide 48 text

obrigado! twitter.com/tegonl github.com/tegon You might not need ElasticSearch - @tegonl