Re: Postgres as key/value store

On Sep 27, 2014, at 7:48 PM, snacktime wrote:

> The schema is that a key is a string, and the value is a string or binary.  I am actually storing protocol buffer
messages,but the library gives me the ability to serialize to native protobuf or to json.  Json is useful at times
especiallyfor debugging. 

I don't know if this will apply to you, but i received significant speed improvements on Postgres key searches by using
substringindexes. 

If your keys are just random hashes, this would probably work well for you.
if your keys are person-readable, it's probably not going to work as well as the distribution of prefix characters will
probablybe too uniform. 

But the general idea is twofold:

    1. create an additional partial index on the key field -- CREATE INDEX _entities_id__subst_7 ON
entities(substr(id,1,7));
    2. update your SELECTS to search for both the full string AND the substring

        - WHERE id = :id
        + WHERE (id = :id) AND (substr(id,1,7) = substr(:id, 1, 7))

By adding in the substring query, the planner will (usually) optimize the select by doing a first pass on the substring
index. then it searches that limited set for the rest of matching criteria. 

on a table with 4MM+ records , introducing a substring index/query improved my searches by a few orders of magnitude.
 

before trying this indexing strategy, we were actively looking to migrate this particular query service off of postgres
--it was such a bottleneck and was not scalable.   
now there is no reason to leave in the foreseeable future.


On Sep 27, 2014, at 8:33 PM, Gavin Flower wrote:

>>  This works well because keys are left prefixed with a scope, a delimiter, and then the actual key for the data.
> Then I noticed that your id is actually a compound key, and probably would be better modelled as:

if you're able to standardize the scope out, an index of "(scope, substring(key,1,7))" might work well.

i only used 1,7 as my key arguments, because that was an optimal speed/space mix on my dataset.  depending on yours, a
shorteror longer index might be more appropriate