Upgrade to Pro — share decks privately, control downloads, hide ads and more …

You might not need ElasticSearch

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.

534f215d73d93dae9314db40d9746acd?s=128

Leonardo Tegon

July 17, 2018
Tweet

Transcript

  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