You might not need ElasticSearch

ElasticSearch é um ótima ferramenta, mas será que eu preciso dele agora?
Essa palestra mostra algumas features de fulltext search do PostgreSQL e alguns prós e contras que podem ajudar na hora de decidir qual ferramenta usar para a busca do seu website.

1. You might not need ElasticSearch
2. ElasticSearch é uma ótima ferramenta
3. Leonardo Tegon twitter.com/tegonl github.com/tegon
4. ElasticSearch ! 4 hospedagem 4 monitoramento 4 manutenção You might not need ElasticSearch - @tegonl
5. MVP You might not need ElasticSearch - @tegonl
6. 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
7. 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
8. Banco de dados You might not need ElasticSearch - @tegonl
9. PostgreSQL Full text search You might not need ElasticSearch - @tegonl
10. texto -> tokens -> lexemes -> documento You might not need ElasticSearch - @tegonl
11. to_tsvector(config, text) You might not need ElasticSearch - @tegonl
12. SELECT to_tsvector('portuguese', 'Batman - O Cavaleiro das Trevas') to_tsvector 'batman':1 'cavaleir':3 'trev':5 You might not need ElasticSearch - @tegonl
13. 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
14. Extensão para remover acentos CREATE EXTENSION unaccent You might not need ElasticSearch - @tegonl
15. 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
16. Query You might not need ElasticSearch - @tegonl
17. to_tsquery(config, text) You might not need ElasticSearch - @tegonl
18. SELECT to_tsquery('portuguese', 'anéis') to_tsquery 'ané' You might not need ElasticSearch - @tegonl
19. SELECT to_tsquery('portuguese', unaccent('anéis')) to_tsquery 'ane' You might not need ElasticSearch - @tegonl
20. Match operator (@@) You might not need ElasticSearch - @tegonl
21. SELECT to_tsvector('portuguese', 'Batman - O Cavaleiro das Trevas') @@ to_tsquery('portuguese', 'trevas') ?column? true You might not need ElasticSearch - @tegonl
22. SELECT to_tsvector('portuguese', 'Batman - O Cavaleiro das Trevas') @@ to_tsquery('portuguese', 'trevas') ?column? true You might not need ElasticSearch - @tegonl
23. 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
24. Multi-table Full Text Search You might not need ElasticSearch - @tegonl
25. categories Column Type id bigint name character varying You might not need ElasticSearch - @tegonl
26. groups Column Type id bigint name character varying You might not need ElasticSearch - @tegonl
27. products Column Type id bigint title character varying sales_rank integer category_id bigint group_id bigint You might not need ElasticSearch - @tegonl
28. Multi-table Full Text Search 4 título do produto 4 categoria 4 grupo You might not need ElasticSearch - @tegonl
29. 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
30. 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
31. 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
32. 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
33. 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
34. 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
35. 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
36. title category_name group_name Batman - The Movie Action & Adventure DVD You might not need ElasticSearch - @tegonl
37. 60K registros = ~600ms You might not need ElasticSearch - @tegonl
38. Materialized view You might not need ElasticSearch - @tegonl
39. 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
40. 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
41. 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
42. 60K registros + materialized view = ~20ms You might not need ElasticSearch - @tegonl
43. Atualizando documentos REFRESH MATERIALIZED VIEW searches You might not need ElasticSearch - @tegonl
44. Index para o documento (tsvector) CREATE INDEX document_index ON searches USING GIN (document) You might not need ElasticSearch - @tegonl
45. 60K registros + materialized view + index = ~1ms You might not need ElasticSearch - @tegonl
46. Review ElasticSearch 4 rápido 4 completo (ex: faceted search) 4 escalável You might not need ElasticSearch - @tegonl
47. Review PostgreSQL 4 não adiciona custo de infra 4 syntax mais simples 4 reindexação mais trivial You might not need ElasticSearch - @tegonl
48. obrigado! twitter.com/tegonl github.com/tegon You might not need ElasticSearch - @tegonl