

A love affair with PostgreSQL - craigkerstiens
http://blog.remarkablelabs.com/2012/12/a-love-affair-with-postgresql-rails-4-countdown-to-2013

======
pilif
The article is showing off the -> operator and then proceeds to talk about
indexes and using them for querying. Unfortunately, -> can't take advantage of
indexes.

You'd have to use the @> operator and compare a partial hash.

So instead of

    
    
        SELECT * FROM comics WHERE properties -> 'story_arc' = 'Throne of Atlantis'
    

You'd use

    
    
        SELECT * FROM comics WHERE properties @> 'story_arc=>Throne of Atlantis'::hstore
    

The first query will not use an index on properties, the second one will.

See the first paragraph of F.16.3 on
<http://www.postgresql.org/docs/9.2/static/hstore.html>

------
zanny
I'm glad to see Rails supporting Postres more in a world where the future of
MySQL is in flux. Postres is solid.

~~~
taligent
MySQL isn't in flux. It is getting better every single day because of the
contributions from Percona, MariaDB, Facebook, Twitter and even Oracle. It's
just that people have a nervousness because of the Oracle connection.

And PostgreSQL maybe solid but developers want a solution that horizontally
scales. Until this is addressed it is going to find itself increasingly
marginalised.

~~~
codewright
PostgreSQL horizontally scales in the same fashion as MySQL. What are you
talking about?

[http://instagram-
engineering.tumblr.com/post/10853187575/sha...](http://instagram-
engineering.tumblr.com/post/10853187575/sharding-ids-at-instagram)

<http://postgres-xc.sourceforge.net/>

<http://www.pgpool.net/mediawiki/index.php/Main_Page> (you can use this to
transparently shard)

~~~
taligent
No it's not like MySQL which has MySQL Cluster a supported, well documented,
OOTB, easy to use solution with lots of enterprise customers. Similarly
Percona has a very, very impressive product with great support.

Instagram had to roll their own and Postgres XC, PGPool both have always
seemed pretty sketchy. No official support, no notable customers, shocking
complexity and everything just seems all over the place with documentation
from say 2009 referencing PostgreSQL 8 e.g. PgPool beginner guide. They may be
great solutions but do they really look like something that inspires
confidence ?

These guys seem to have the right idea: <http://www.cloudpostgres.com>

It would just be nice to have something as simple and polished built into
PostgreSQL.

~~~
frankwiles
Pretty sure Skype scales PG horizontally pretty darn well. PgPool is probably
the least used, least recommended replication method out there. Streaming
replication in 9, WAL shipping, and things like Londiste (Skype) are what real
large production users use.

------
pjungwir
I also love Postgres, but since hstore can't do nesting, but is only a flat
map of strings to strings, I've been reluctant to try it out. Still, I'm glad
to see it's getting support in Rails.

~~~
pilif
So because $SOLUTION can't do both $FEATURE1 _and_ $FEATURE2, you don't use
$SOLUTION and thus you don't even have $FEATURE1?

Hstore is invaluable when you want to add arbitrary attributes to rows in your
tables (think: different attributes for products in different categories).
It's as if every row had an unlimited (and different from other rows) amount
of columns.

To do this without hstore, you can dump the additional data as a serialized
blob of some kind, but then you lose the ability to efficiently[1] query or
sort for those additional attributes or you use a complicated mess of join
tables, which means increasing cost of querying as you add more fields to
query for (one join per field).

With hstore, you get indexed querying and sorting in addition to an unlimited
amount of fields.

Also: because keys and values are just strings, it's in fact possible to nest
hstores, you just don't get the nice indexing or any kind of type safety past
the first level of hstores.

[1] if your blob is XML or JSON (via pl/v8), you can use functional indexes to
still get indexed and sorted lookups in advance without having to do the
indexing inside the application logic which you would have to in case of any
of the NoSQL stores.

~~~
pjungwir
> So because $SOLUTION can't do both $FEATURE1 and $FEATURE2, you don't use
> $SOLUTION and thus you don't even have $FEATURE1?

More like I choose $SOLUTION2 that _can_ do $FEATURE{1,2}. Maybe that's adding
related tables, maybe it's using a JSON column, maybe it's something else. I
think part of good engineering is picking solutions that keep your options
open. It seems like your data's structure always gets more complex over time,
so hstore is a risky choice.

I'm not saying it's a terrible choice: you could always manage the flatness
the way people use Java properties files, with compound key names to simulate
fancier data structures, but that's sort of a pain. I could definitely see
myself trying out hstore in the future for something like user preferences; I
just am hesitant due to its flatness.

> because keys and values are just strings, it's in fact possible to nest
> hstores

I don't understand this; could you elaborate? Are you saying that an hstore is
really a string?

~~~
bbotond
> Are you saying that an hstore is really a string?

I'm not familiar with hstore but I think he means you can store some kind of
_reference_ under a key.

------
GICodeWarrior
Is there an API that doesn't require string concatenation? As-is these APIs
appear relatively dangerous from a security perspective.

------
don_draper
I just realized you could do prototyping with HStore, and then if or when you
need improved performance switch to regular tables.

~~~
bratsche
Only for something super, super simple though. You can't have nested data
structures in hstore.

------
nachteilig
hstore support should be nice. Thanks Rails team!

