5 ms·

A love affair with PostgreSQL

88 points by craigkerstiens 14y ago

zanny 14y 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 14y 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 https://codex.wordpress.org/Using_Alternative_Databases
  - taligent 14y ago
    IMHO it is the NoSQL databases like Redis and MongoDB that seem to be the most popular amongst newer platforms e.g. NodeJS.
    - Firehed 14y ago
      Yes, that does seem to be the trend. However, that's all it really is: a trend. Schemaless databases are good for certain tasks, but are also really terrible for other things. It seems that people interested in using new tech like to use as much new tech as possible for the sake of it, rather than because it's actually the right tool for the job. I know multiple people who have lost literally months of work because they tried to use Mongo in a place where a relational database made a lot more sense (incidentally, they're also using Node), and ultimately had to rebuild and migrate their entire data model layer to Postgres. That's not a knock on Node or Mongo, just an observation. They're the right tools for some things, and the wrong tools for others. If you're choosing your tech stack based on current trends rather than spending a few minutes thinking what problems you're trying to solve, God help you.
    - ishbits 14y ago
      That is a trend. I'm already seeing some startups pulling back into MySQL from Mongo admitting they followed the hype rather than using the best tool for the job. While it could be the perfect fit for some tasks, I know there are a ton of Mongo deployments that would be better served by Postgres or MySQL.
- taligent 14y 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 14y ago
    PostgreSQL horizontally scales in the same fashion as MySQL. What are you talking about? http://instagram-engineering.tumblr.com/post/10853187575/sharding-ids-at-instagram http://instagram-engineering.tumblr.com/post/10853187575/sha... http://postgres-xc.sourceforge.net/ http://postgres-xc.sourceforge.net/ http://www.pgpool.net/mediawiki/index.php/Main_Page http://www.pgpool.net/mediawiki/index.php/Main_Page (you can use this to transparently shard)
    - taligent 14y ago
      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 http://www.cloudpostgres.com It would just be nice to have something as simple and polished built into PostgreSQL.
      - frankwiles 14y ago
        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.
nachteilig 14y ago
hstore support should be nice. Thanks Rails team!
pjungwir 14y 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.
- pilif 14y 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.
  - pjungwir 14y 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?
pilif 14y 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 http://www.postgresql.org/docs/9.2/static/hstore.html
don_draper 14y ago
I just realized you could do prototyping with HStore, and then if or when you need improved performance switch to regular tables.
- bratsche 14y ago
  Only for something super, super simple though. You can't have nested data structures in hstore.
deleted 14y ago
[deleted]
GICodeWarrior 14y ago
Is there an API that doesn't require string concatenation? As-is these APIs appear relatively dangerous from a security perspective.

zanny 14y 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 14y 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 https://codex.wordpress.org/Using_Alternative_Databases
- taligent 14y ago
  IMHO it is the NoSQL databases like Redis and MongoDB that seem to be the most popular amongst newer platforms e.g. NodeJS.
  - Firehed 14y ago
    Yes, that does seem to be the trend. However, that's all it really is: a trend. Schemaless databases are good for certain tasks, but are also really terrible for other things. It seems that people interested in using new tech like to use as much new tech as possible for the sake of it, rather than because it's actually the right tool for the job. I know multiple people who have lost literally months of work because they tried to use Mongo in a place where a relational database made a lot more sense (incidentally, they're also using Node), and ultimately had to rebuild and migrate their entire data model layer to Postgres. That's not a knock on Node or Mongo, just an observation. They're the right tools for some things, and the wrong tools for others. If you're choosing your tech stack based on current trends rather than spending a few minutes thinking what problems you're trying to solve, God help you.
  - ishbits 14y ago
    That is a trend. I'm already seeing some startups pulling back into MySQL from Mongo admitting they followed the hype rather than using the best tool for the job. While it could be the perfect fit for some tasks, I know there are a ton of Mongo deployments that would be better served by Postgres or MySQL.
taligent 14y 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 14y ago
  PostgreSQL horizontally scales in the same fashion as MySQL. What are you talking about? http://instagram-engineering.tumblr.com/post/10853187575/sharding-ids-at-instagram http://instagram-engineering.tumblr.com/post/10853187575/sha... http://postgres-xc.sourceforge.net/ http://postgres-xc.sourceforge.net/ http://www.pgpool.net/mediawiki/index.php/Main_Page http://www.pgpool.net/mediawiki/index.php/Main_Page (you can use this to transparently shard)
  - taligent 14y ago
    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 http://www.cloudpostgres.com It would just be nice to have something as simple and polished built into PostgreSQL.
    - frankwiles 14y ago
      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.

nachteilig 14y ago

hstore support should be nice. Thanks Rails team!

pjungwir 14y 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.

pilif 14y 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.
- pjungwir 14y 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?

pilif 14y 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 http://www.postgresql.org/docs/9.2/static/hstore.html

don_draper 14y ago

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

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

deleted 14y ago

[deleted]

GICodeWarrior 14y ago

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