Notes from Matt Wean

Adventures in Searching with Postgres: Part 1

By on in Database, Rails, Ruby

For a recent project, we built a live-search for over 60,000 records which used both simple pattern matching and full-text search. The records we needed to search were diagnoses that are used to assign patients to a therapy. I had never done full-text search or anything real-time with that many records, so I ended up doing a lot of experimentation. These posts will cover my experience, and I hope they’ll be of value to anyone implementing their own Postgres search.


The Problem

The records we were searching were International Statistical Classification of Diseases (ICD) codes. Each record consists of a 3-7 character code, a short description averaging 11 words, and a few other data and association fields (for this post, I generated 100,000 records matching the real ICD format). We needed to be able to search by code and description, and users would be changing their search query quickly to find the right record, so it needed to be responsive. In part one, I’ll cover the code search where a user enters one or more codes (which may be partial).

Continue reading …