If you use SQLite, you can use Goatfish[1]. It's not production-ready or as robust as Postgres's hstore, but it's embedded (since it works with SQLite) and also allows you to index various properties.
I've deployed SQLite to a large number of production software, from embedded systems to web servers handling thousands of daily vistors. In fact, SQLite is my tool of choice when it comes to a small/medium relational-db-based web project.
Sqlite is not a toy db. It is an impressive piece of software engineering.
So, unless you have a very narrow definition of the word production, you should do read up on SQLite (the official website is excellent) and seriously consider it for your next project.
SQLite should absolutely be used in production, where it makes sense. The one area where sqlite has a big disadvantage is concurrent writes. If you have read-only data, cached data, etc. then sqlite can happily be used in a high traffic production system, with millions of rows and many GB of data. It's also a good store for configuration data, or something like an admin backend. The portability of sqlite database files is a really nice feature.
Underreported but very useful are the omnibus GIN/GiST indexes for hstores, which let you index all the fields at once, not unlike the Postgres Full Text Search feature:
11 comments
[ 0.26 ms ] story [ 31.5 ms ] threadhttp://pirt.co.uk/post/25095198148/hybrid-sql-nosql
[1] https://github.com/stochastic-technologies/goatfish
I've deployed SQLite to a large number of production software, from embedded systems to web servers handling thousands of daily vistors. In fact, SQLite is my tool of choice when it comes to a small/medium relational-db-based web project.
Sqlite is not a toy db. It is an impressive piece of software engineering.
So, unless you have a very narrow definition of the word production, you should do read up on SQLite (the official website is excellent) and seriously consider it for your next project.
http://www.postgresql.org/docs/9.1/static/hstore.html#AEN133...
It's more expensive to update than a more selective index, of course, but for the ultimate in "do what I mean" optimization, this is it.
Unfortunately, I don't know if ORMs generate the right kind of SQL to use those indexes, yet, but I don't see why they couldn't.