Best way to use a PostgreSQL database as a simple key value store

Question

I am being required to use a postgreSQL database and it will replace my current use of berkeleyDB. Although; I realize this is not an ideal situation, it is beyond my control.

So the question is... If you were required to make postgreSQL into a key value store how would you go about doing this, while making it as efficient as possible?

My values are byte arrays and my key's are strings, I could impose some restrictions on the lengths of these strings.

I assume I should use a blob for my value and primary key column holding the key, but as I am just venturing into this journey I am curious if anyone in the stack overflow community has done this, or if there are any specific 'gotchas' I should look out for.

Answer 1

The extension in Postgresql to properly do this is called hstore. It works in a similar fashion as you would expect other key-value store systems. Just load the extension. The syntax is unique but if you have ever used redis or mongo you will get it quickly. Don't make it harder than it is. I understand, we often don't get to pick our tools and have to make do.
Here is the document page:

http://www.postgresql.org/docs/9.1/static/hstore.html

Answer 2

Another option is to use JSON or JSONB with a unique hash index on the key.

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE key_values (
    key uuid DEFAULT uuid_generate_v4(),
    value jsonb
);

CREATE INDEX idx_key_values ON key_values USING hash (key);

Some queries

SELECT * FROM key_values WHERE key = '1cfc4dbf-a1b9-46b3-8c15-a03f51dde891';
Time: 0.514 ms
postgres=# SELECT * FROM key_values WHERE key = '1cfc4dbf-a1b9-46b3-8c15-a03f51dde890';
Time: 1.747 ms

postgres=# do $$
begin
for r in 1..1000 loop
INSERT INTO key_values (value)
VALUES ('{"somelarge_json": "bla"}');
end loop;
end;
$$;
DO
Time: 58.327 ms

You can't run efficient range queries like with B-tree, but it should have better read/write performance. Index should be about 60% smaller.