FULL METAL JACKET

or how did i stop worrying and learned to love postgres

Text Search

You will be implementing a search engine for shop.pr

Quoth the raven

Being the responsible adult that I am, oblivious of the what the implications of such task were, i proceeded in the only way reasonable:

After a bit of consideration, we choose to use Amazon's CloudSearch, and lo and behold, enter Miguel:

So we have to use postgres.

Views

We would need to query the db each time we ant to look for something, these queries would have a polymorphic relationship to the individual result and the text column being searched.

Indexing Documents

This approach contains the "same information" but its stored in a table, every object that should be searchable must be in the table and each time a new one is created it should be added to the table.

So we have to use postgres.

Is incredibly slow. Now imagine adding a couple of joins there.

Using Views

CREATE VIEW searches AS

  SELECT
    statuses.id AS searchable_id,
    'Status' AS searchable_type,
    comments.body AS term
  FROM statuses
  JOIN comments ON statuses.id = comments.status_id

  UNION

  SELECT
    statuses.id AS searchable_id,
    'Status' AS searchable_type,
    statuses.body AS term
  FROM statuses

  UNION

  SELECT
    users.id AS searchable_id,
    'User' AS searchable_type,
    users.name AS term
  FROM users