3 ms·

Also, SQLite Full Text Search with a prefix query (you could also use `:memory:` as the address to simulate in-memory redis life): https://sqlite.org/fts3.html

Also, SQLite Full Text Search with a prefix query (you could also use `:memory:` as the address to simulate in-memory redis life): https://sqlite.org/fts3.html https://sqlite.org/fts3.html (and also https://sqlite.org/fts5.html https://sqlite.org/fts5.html) -- make sure to use the a stemming tokenizer as well. Why SQLite might be a good idea for your project/small service: https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html

kuschku 9y ago
Actually, at that point you’d want to use PostgreSQL’s Full Text Search. SQLite’s is a lot more limited in performance, and a lot less usable.
- hardwaresofton 9y ago
  SQLite wins in one area that can be crucial -- simplicity. PostgreSQL is a fantastic and robust database, and it's FTS capabilities are almost certainly good-enough (especially if you consider SQLite's to be good-enough), but it is necessarily complex to support the amount of robustness it offers. SQLite offers a fraction of the robustness of Postgres, but that difference is not necessarily reflected in the correctness/speed of a simple full text search capability meant to support auto-complete (as in the article). Unfortunately I don't have number to back up this assertion, but I'd be very surprised if SQLite FTS was even 10x slower, maybe someone should do that comparison, I'll put it down and make a humble attempt at comparing these two approaches someday.
  - kuschku 9y ago
    Make sure to also run a test with multiple threads concurrently interacting with SQLite. Due to SQLite's exclusive locking mechanism, this causes actual issues with concurrent writes and reads.
    - jamiesonbecker 9y ago
      Good point - but reads never blocked except during a (concurrent) write, which wouldn't likely be much of a problem with the presumably read-heavy autocomplete scenario described here. Incidentally, even that's not even an issue anymore, if you enable SQLite's write-ahead logging support: https://www.sqlite.org/wal.html https://www.sqlite.org/wal.html

kuschku 9y ago

Actually, at that point you’d want to use PostgreSQL’s Full Text Search. SQLite’s is a lot more limited in performance, and a lot less usable.

hardwaresofton 9y ago
SQLite wins in one area that can be crucial -- simplicity. PostgreSQL is a fantastic and robust database, and it's FTS capabilities are almost certainly good-enough (especially if you consider SQLite's to be good-enough), but it is necessarily complex to support the amount of robustness it offers. SQLite offers a fraction of the robustness of Postgres, but that difference is not necessarily reflected in the correctness/speed of a simple full text search capability meant to support auto-complete (as in the article). Unfortunately I don't have number to back up this assertion, but I'd be very surprised if SQLite FTS was even 10x slower, maybe someone should do that comparison, I'll put it down and make a humble attempt at comparing these two approaches someday.
- kuschku 9y ago
  Make sure to also run a test with multiple threads concurrently interacting with SQLite. Due to SQLite's exclusive locking mechanism, this causes actual issues with concurrent writes and reads.
  - jamiesonbecker 9y ago
    Good point - but reads never blocked except during a (concurrent) write, which wouldn't likely be much of a problem with the presumably read-heavy autocomplete scenario described here. Incidentally, even that's not even an issue anymore, if you enable SQLite's write-ahead logging support: https://www.sqlite.org/wal.html https://www.sqlite.org/wal.html