

The key value store we ignored (Postgresql) - maxpert
http://blog.creapptives.com/post/14062057061/the-key-value-store-everyone-ignored-postgresql
Postgres had this hstore for structure free storage and nobody ever mentions it. This post will hopefully bring attention to something we ignored long ago.
======
imbriaco
I agree with the author that hstore is very interesting, but the data
structures are not the key selling point in the NoSQL space in my opinion. The
most overlooked advantage to things like Cassandra and Riak are the fact that
you have no single point of failure in the system. If an individual node
fails, there is no operational impact.

Postgres does have (finally!) a nice replication story, so you have data
protection on a second server, but the failover mechanics are much more
complicated and you still have a single point of failure at the master/writer
node. The story gets even more operationally complex when you talk about
sharding and the fact that you now have to have a replica per master -- and it
really needs to be the same size as the master if you want to be able to trust
that you have enough capacity to fail over to it. Suddenly you need to have
half of your database capacity sitting idle.

Now, don't get me wrong, I think Postgres is a wonderful database server. For
the vast majority of applications it is the correct default choice. Very few
applications ever get to the point where they need to scale further than they
can get vertically on a single node and most can tolerate a few minutes of
downtime to fail over to a backup server. Hand-wavy "sharding is easy"
comments, however, ignore a lot of operational reality and that's dangerous.

Understand your use case. Understand the failure modes. Decide how available
your datastore needs to be and how much data you need to support. Know the
consequences of the choices you make.

~~~
fauigerzigerk
Agreed, but of course the massive write parallelism and fault tolerance of
DBMS like Cassandra comes at the cost of dropping ACID, which may cause a lot
of complexity elsewhere in the system. It also comes at the cost of limiting
the types of queries you can perform without resorting to procedural code (at
least in the case of column family based architectures). In other words, it
comes at the cost of productivity.

So, even if sharding is not easy, you can do quite a lot of it in the time you
save by not having to code around the limitations of most noSQL DBMS.

The day will come when RDBMS are secret productivity weapon of smaller
companies that don't feel they have to act like they were Google.

That said, there are very good reasons not to use RDBMS in cases where the
data model or specific access patterns just don't fit. But in most of those
cases, I find that using in memory data structures combined with file system
storage or something like BerkeleyDB is a much better fit than any server
based DBMS.

~~~
dspillett
_> comes at the cost of dropping ACID_

Do remember though that, as discussed here recently, most RDBMSs do not act in
a fully ACID compliant way by default. IIRC it is providing a complete
isolation guarantee often also provides a hefty performance hit so compromises
are made in this area unless you explicitly tell it to be as careful as it
can. I imagine this can cause quite a nightmare for master<->master
replication.

There are a lot of people using "noSQL" options for the wrong reasons (such as
to be buzzword compliant, or because they don't understand SQL), but there are
issues that traditional RDBMSs have that stick-in-the-muds like me (who cringe
at the phrase "eventual consistency" should be more aware of than we generally
are.

Making the right choices about your data storage can be _hard_.

------
henrikschroder
The main selling point of the various NoSQL products out there today isn't the
schemaless storage, instead it's the ability to grow beyond a single server
that's compelling.

228MB of data is nothing, it fits in RAM of any machine. What would the
examples in this blog post look like if it was 228GB of data spread across 10
servers instead? How would you grow/shrink such a cluster? How would you
perform a query that cuts across servers and aggregates data from some of
them? How would you replicate data across multiple servers?

~~~
matwood
_228MB of data is nothing, it fits in RAM of any machine. What would the
examples in this blog post look like if it was 228GB_

You bring up an interesting point. At what data size do the companies using
the various NoSQL DBs feel they _have_ to move beyond a traditional RDBMS? I
work with some traditional RDBMS stores now that are >500GB in size with
tables that add 20M-30M rows/month and querying still isn't an issue.
Admittedly it takes expertise to optimize the system and make it work
efficiently, but that's going to be the case with any datastore.

~~~
mulander
I can sustain this claim. I work with a system implemented almost entirely in
Oracle PL/SQL. Some tables in the system are nearing 800-900 columns, their
size often exceeds 600 GB per table (not many of such large tables though).
Querying isn't a problem at all. Large schema changes are also mostly
painless. The only point at which one has to be really careful is when a
schema change requires actual calculations based on historic data with an
additional write on each record.

~~~
jacques_chester
A lot of criticisms I read about the immutable limits of RDBMSes turn out,
upon closer inspection, to be criticisms of MySQL. Oracle sort of sails past
these limitations like a superliner: expensively and gracefully.

Not that I particularly like Oracle as a programmer. I have to check my
calendar every time I hit the 32-character limit for names or once again have
to write

    
    
        some_field_expressing_truth  varchar2(1);
        constraint "y_or_n" check some_field_expressing_truth in ('Y','N');
    

But relational databases already scale to petabytes and millions of
transactions per minute. Just not in the opensource world ... not yet, anyhow.

~~~
jeltz
Well PostgreSQL does scale to millions of simple read-only transactions per
minute, at least in some benchmarks. And in PostgreSQL 9.2 it will scale much
better, in the synthetic benchmark it reached about 13 million per minute at
high concurrency (compared to about 2.5 million for 9.1).

[http://rhaas.blogspot.com/2011/09/scalability-in-
graphical-f...](http://rhaas.blogspot.com/2011/09/scalability-in-graphical-
form-analyzed.html)

~~~
jacques_chester
Hence "not yet".

It's been amazing watching the performance surge in postgres these past few
years. I wonder if Red Hat or similar will try sponsoring a tilt at the TPC-C
crown in future.

~~~
nl
Back before Sun bought MySQL they started doing a lot of performance work on
Postgres. They didn't go after TPC, but they did show it was only(?) 12%
slower than Oracle for SpecjAppServer:
<http://www.informationweek.com/news/201001901>

------
zobzu
I've been using a lot of MySQL and some NoSQL solutions over the years. I
don't _need_ most of what NoSQL is supposed to offer and uhm, a couple of
years ago I tried PGSQL.

I've never found the same level of finish, performance, etc. It's also very
customizable and very dependable. You can get extremely good performance out
of it.

Finally... the devs are plain AWESOME. Every time I had an issue, question,
etc, not even necessarily related to PGSQL but to SQL and performance in
general they would reply quickly, and accurately (I owe one of them many, many
beers).

To give you an example, a company I worked for was doing a 10M to 10M
comparison (for very specific needs). All businesses we have found were using
custom NoSQL solutions, or even Oracle, with huge clusters, costing a lot, and
taking ~24h for a result.

So we implemented ours. After a year of testing, coding, etc, it was settled
on PGSQL (on FBSD), with the help of the said dev, we were doing the same
comparison,in 3H, on a single quad core system (with heaps of ram).

Sure, maybe the other businesses never cared _that_ much for performance, but
its still astonishing. Most optimizations were made thanks to PGSQL's dev help
were usually speeding up the process by thousand of times.

Disclaimer: I can't detail the comparison (its still under NDA), but it
included very specific stuff, in case you wonder about the time taken, etc.

~~~
einhverfr
_Finally... the devs are plain AWESOME._

A few years ago I got a dreaded call from a customer. A point of sale system
was taking 45 seconds suddenly to post invoices. This is a big deal for a cash
register role, as you can imagine.

So I went on site, ran some diagnostics, isolated the problem query, etc. At
this point they were into the slow part of the afternoon so I asked about it
on the -perform list along with query plans and everything else I could think
of.

Within an hour I had received an email from Tom Lane explaining:

1) What the planner was getting wrong

2) Why the planner was making an error there, and why this was a special
planner case.

3) Giving me suggestions as to how to fix it.

First rate support, let me tell you. (Also, planner behavior was changed in a
release soon after to accommodate my use case.)

------
sqrt17
In my opinion, Postgresql + hstore + PLv8js (Javascript as a language for
stored procedures, see <http://code.google.com/p/plv8js/>) could really rock
the world if it became an accepted standard and got some people behind it.

It's not so great if you start using something and one year later see that the
original authors lost interest and move on, leaving you with 10k lines of code
that depend on a pray-for-no-bugs unsupported piece of software.

~~~
grncdr
Totally agree. I've been building out a new system with hstore and it's been
great, planning to do some benchmarking with plv8js as well. If you've got a
dataset that you can conceivably scale vertically to accomodate, postgres is a
solid _and_ feature-packed database.

------
djb_hackernews
> ...database grew to a size of 239691780 bytes (Just 228MB)

> ...gives me 14689 rows just under 360ms on average

Uh, 360ms seems like an awfully long time to query such a small dataset.

~~~
joevandyk
I think that includes the time to transfer the data.

~~~
djb_hackernews
I sure hope not. That's usually not the way you benchmark database queries.

~~~
cbs
Then its a good thing that line in the article had nothing to do with
benchmarking a database query.

------
jedberg
reddit used (and still for the most part uses) postgres as a schemaless
storage (although they don't use hstore).

Also, I'm pretty sure Heroku uses hstore, because they're the ones that taught
me about it.

So not _everyone_ forgot about it.

~~~
maxpert
Just read the comments around and you will see the dogma people face on daily
basis despite the huge databases of reddit!

~~~
code_duck
dogma... what do you mean?

------
solitaire
Documentation about hstore in postgresql
<http://www.postgresql.org/docs/9.1/static/hstore.html>

------
chrismealy
Anybody using this with rails?

<https://github.com/softa/activerecord-postgres-hstore>

~~~
jashkenas
Yes -- but not with that module. We're using it to tag documents (individual
rows in Postgres and Solr) with user-defined metadata.

[http://blog.documentcloud.org/blog/2011/05/arbitrary-
metadat...](http://blog.documentcloud.org/blog/2011/05/arbitrary-metadata/)

... which can then be used to power custom indexes of particular collections
of documents, like this one:

[http://www.nytimes.com/interactive/2011/12/02/us/oil-and-
gas...](http://www.nytimes.com/interactive/2011/12/02/us/oil-and-gas-
leases.html)

The fun bit being, that in both Postgres and Solr, you can still run a precise
search (including joins for access control, sharing, and boolean full text
clauses) with a single query because the HStore keys and values are indexed.

~~~
tptacek
How did you build it? Are you using hstore SQL directly? Do you just have
migrations with raw SQL in them?

~~~
jashkenas
Let's dig through it a bit. Our hstore column isn't directly on the document
row, but rather in a joined "docdata" table. There was a semi-raw migration to
add it:

[https://github.com/documentcloud/documentcloud/blob/master/d...](https://github.com/documentcloud/documentcloud/blob/master/db/migrate/20110429150927_add_data_to_documents.rb)

The "Docdata" model handles serializing JSON hashes of keys -> string values
into the hstore format, and parsing 'em back out. This is a little loose, but
works for most of the simple things you'd like for tagging documents (senator:
Kerry, statecode: WV, recordid: 019281)

[https://github.com/documentcloud/documentcloud/blob/master/a...](https://github.com/documentcloud/documentcloud/blob/master/app/models/docdata.rb)

... the resulting effect is that from the Rails side, working with
"document.data" directly is like working with a lazy-loaded hash.

The more interesting bit is the search side of things, here's the query parser
and query object (which generates SQL and Solr queries):

[https://github.com/documentcloud/documentcloud/blob/master/l...](https://github.com/documentcloud/documentcloud/blob/master/lib/dc/search/parser.rb)

[https://github.com/documentcloud/documentcloud/blob/master/l...](https://github.com/documentcloud/documentcloud/blob/master/lib/dc/search/query.rb)

... for any given query, we detect if Solr is required (if full-text is
involved), or if the search can be satisfied purely in Postgres. So there's
one way of generating the key:value query for each back end.

Hope that answers the question.

(Edit:) Forgot to mention that we add a little extra on top of vanilla hstore
"key:value" queries. You can write "dob: *" to show documents with any senator
tagged, and "dob: !" to show documents that haven't been tagged with a date of
birth yet.

~~~
tptacek
This is awesome, thank you (didn't think to check if you were in Github, but
the explanations are great too).

------
keeran
Thoughtbot posted a similar Postgres article recently:

[http://robots.thoughtbot.com/post/13829210385/the-durable-
do...](http://robots.thoughtbot.com/post/13829210385/the-durable-document-
store-you-didnt-know-you-had-but)

------
7952
I tried hstore on a project for many of the reasons mentioned. I just hated
having to always represent data as strings.

~~~
tptacek
Isn't this effectively what Redis does too?

There are whole Salvatore posts about doing binary data structures in Redis
string values.

~~~
iFire
<http://redis.io/topics/data-types>

Strings are the most basic kind of Redis value. Redis Strings are binary safe,
this means that a Redis string can contain any kind of data, for instance a
JPEG image or a serialized Ruby object.

------
mikeytown2
how does this compare to MySQL's HandlerSocket?

~~~
maxpert
I haven't done benchmarks yet but if you are looking for HandlerSocket
benchmarks <http://blog.creapptives.com/post/3329352663/the-nosql-dogma> is
the link

~~~
mikeytown2
Those benchmarks are very flawed as HandlerSocket only works in InnoDB. This
is a better post on the subject:
[http://yoshinorimatsunobu.blogspot.com/2010/10/using-
mysql-a...](http://yoshinorimatsunobu.blogspot.com/2010/10/using-mysql-as-
nosql-story-for.html)

------
protik001
@chrismealy.. <http://croak.eu/rTBVKX>

~~~
nullymcnull
Quit spamming this bullshit here. HN is a text-based discussion forum.

