A love affair with PostgreSQL

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

by craigkerstiens • 14 years ago

88 21 14 years ago

blog.remarkablelabs.com

zanny• 14 years ago

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

kawsper• 14 years ago

Its seems like newer frameworks and languages moves to Postgres.

But where MySQL have always been strong has been in the PHP community. And it still seems to me that there isn't much of a change here. Wordpress is still only thinking about getting Postgres support: https://codex.wordpress.org/Using_Alternative_Databases

taligent3• 14 years ago

IMHO it is the NoSQL databases like Redis and MongoDB that seem to be the most popular amongst newer platforms e.g. NodeJS.

taligent3• 14 years ago

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• 14 years ago

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

http://instagram-engineering.tumblr.com/post/10853187575/sha...

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

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

Shorel• 14 years ago

You are right. MySQL will be used for years and years. And it is getting better every day.

But MySQL is still behind Postgres in features and reliability. I have being bitten by MySQL shortcomings for a year. Last month was the limit of max 64 indexes per table. Before that it was the dozens of stupid limitations in the procedures and functions. Before that it was some stupid bug that only happens in master-master replication. I can go on for days ranting about MySQL.

Think SVN vs Git. SVN can get better everyday and it does get better, but incremental upgrades will never convert SVN to Git. You scale MySQL by putting Memcached in front of it. But this makes MySQL a fast read-only DB. You can't scale MySQL in a OLTP setup.

Postgres had some performance and scalability issues about 3 or 4 years ago. But the foundations were solid. Now Postgres has fixed those issues and the foundations are better than ever. You can scale Postgres in a OLTP setup and it will eat MySQL lunch every time.

nachteilig• 14 years ago

hstore support should be nice. Thanks Rails team!

pjungwir2• 14 years ago

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.

pilif2• 14 years ago

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.

pjungwir2• 14 years ago

> 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?

gokulj• 14 years ago

If you are using Rails and want to use hstore with values other than just strings, take a look at this https://github.com/JackC/surus

pilif2• 14 years ago

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

don_draper• 14 years ago

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

bratsche• 14 years ago

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

GICodeWarrior• 14 years ago

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

A love affair with PostgreSQL

Related Stories