User: Password:
|
|
Subscribe / Log in / New account

PostgreSQL 9.0 arrives with many new features

PostgreSQL 9.0 arrives with many new features

Posted Sep 24, 2010 12:59 UTC (Fri) by dmag (guest, #17775)
In reply to: PostgreSQL 9.0 arrives with many new features by flewellyn
Parent article: PostgreSQL 9.0 arrives with many new features

> I'm extremely dubious, on the other hand, of the utility of HStore.

Here's two reasons to use HStore:

1) It can turn O(N) operations into O(1)

Imagine trying to implement "Facebook-like" friend list: Every time a user hits their home page, the DB needs get their "friends". In a typical join table, this will take O(N) disk seeks.

So you think about storing the friend's names right on the user object, maybe as a string. But it will be slow -- you have to parse the string (server-side inside a transaction) to modify it.

So you think about a cache. But that just introduces new problems. ("There are two hard problems in Computer Science: Naming and Cache Invalidation.")

With a HStore, you don't need a cache -- It's O(1) to get friend list, and O(1) to modify it. Of course, the trade-off is duplicate data. (That's not a NoSQL thing. SQL people are always denormalizing for performance. (e.g. "total_order_price" or "num_friends")

2) It's a lot simpler. You have a user object (row) with an HStore friends column. You add friends to it, you subtract friends from it, you get all the friends. You don't need a fancy ORM to hide the complexity of messing about with multiple tables and extra IDs. (The trade-off is no FK checking. But MySQL proved we don't need it ;)

If you squint, HStore is really more like a Docstore than a Key-Value store. (A pure KV suffers from "last in wins" problems because you can't do sub-operations on a value. Only Get and Set.)


(Log in to post comments)

PostgreSQL 9.0 arrives with many new features

Posted Sep 24, 2010 15:47 UTC (Fri) by bronson (subscriber, #4806) [Link]

In your example you're using hstore as a cache, right? You still need to invalidate it.

Or, if you always store a new value whenever the friends list changes, then you could just do that with memcached, redis, etc. I'm not seeing the difference.

Other than being built into Postgres, of course. That's nice, but given how easy memcached and redis are to set up, not a big deal.

PostgreSQL 9.0 arrives with many new features

Posted Sep 24, 2010 18:06 UTC (Fri) by dmag (guest, #17775) [Link]

> you're using hstore as a cache, right?

Yes, but with "HStore as a cache", you can do your cache updates in a transaction. The biggest problem with external caching is keeping them in sync. (To keep it in perfect sync, you'd need to build your own 2-phase commit.)

I can also imagine non-cache uses. For example, "has this user seen this announcement?". Seems silly to create a new table for that.

HStore is just "one more tool in the toolbox". The future of data is all about "picking the right tool", which requires knowing the engineering tradeoffs. (Actually, today it's more like "figuring out the engineering tradeoffs", but it will get easier.)


Copyright © 2017, Eklektix, Inc.
Comments and public postings are copyrighted by their creators.
Linux is a registered trademark of Linus Torvalds