5 ms·

Using Schema-less in Django with Postgres

90 points by craigkerstiens 14y ago

craigkerstiens 14y ago
For those interested there's also a Rails equivalent post at - http://schneems.com/post/19298469372/you-got-nosql-in-my-postgres-using-hstore-in-rails http://schneems.com/post/19298469372/you-got-nosql-in-my-pos...
- willlll 14y ago
  And my railsconf talk: http://confreaks.com/videos/891-railsconf2012-schemaless-sql-the-best-of-both-worlds http://confreaks.com/videos/891-railsconf2012-schemaless-sql...
bjpirt 14y ago
I'd been meaning to publish this post on doing a similar thing with Backbone and Node.js. Now seems as good a time as any... http://pirt.co.uk/post/25095198148/hybrid-sql-nosql http://pirt.co.uk/post/25095198148/hybrid-sql-nosql
StavrosK 14y ago
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. [1] https://github.com/stochastic-technologies/goatfish https://github.com/stochastic-technologies/goatfish
- danskil 14y ago
  You should not use SQLite in production.
  - rlander 14y ago
    I downvoted you. Here's why: 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.
  - StavrosK 14y ago
    This is true, I should have said "it's not mature".
  - lost-theory 14y ago
    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.
- danskil 14y ago
  You should not use SQLite in production.
fdr 14y ago
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: http://www.postgresql.org/docs/9.1/static/hstore.html#AEN133526 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.
antihero 14y ago
So what exactly is the benefit of this? Is it superfast like HandlerSocket?

For those interested there's also a Rails equivalent post at - http://schneems.com/post/19298469372/you-got-nosql-in-my-postgres-using-hstore-in-rails http://schneems.com/post/19298469372/you-got-nosql-in-my-pos...

willlll 14y ago
And my railsconf talk: http://confreaks.com/videos/891-railsconf2012-schemaless-sql-the-best-of-both-worlds http://confreaks.com/videos/891-railsconf2012-schemaless-sql...

bjpirt 14y ago

I'd been meaning to publish this post on doing a similar thing with Backbone and Node.js. Now seems as good a time as any... http://pirt.co.uk/post/25095198148/hybrid-sql-nosql http://pirt.co.uk/post/25095198148/hybrid-sql-nosql

StavrosK 14y ago

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. [1] https://github.com/stochastic-technologies/goatfish https://github.com/stochastic-technologies/goatfish

danskil 14y ago
You should not use SQLite in production.
- rlander 14y ago
  I downvoted you. Here's why: 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.
- StavrosK 14y ago
  This is true, I should have said "it's not mature".
- lost-theory 14y ago
  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.
danskil 14y ago
You should not use SQLite in production.

fdr 14y ago

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: http://www.postgresql.org/docs/9.1/static/hstore.html#AEN133526 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.

antihero 14y ago

So what exactly is the benefit of this? Is it superfast like HandlerSocket?