News for hackers. Stuff that matters.
Re:Creating a Document-Store Hybrid in Postgres 9.5 by jarjoura on Friday February 05, 2016 @03:03PM (#11044208)
Only thing I would caution here is that Postgres HA is notoriously difficult to set up and get right. Something like CouchDB is designed from the ground up for high availability and scalability. So if your design calls for only a document store, probably best to avoid Postgres.
Re:Creating a Document-Store Hybrid in Postgres 9.5 by empthought on Friday February 05, 2016 @03:19PM (#11044307)
This is flat-out misleading. PostgreSQL is also designed "from the ground up for high availability and scalability," it's just optimized for a different -- and far more common and useful -- set of system assumptions and constraints. Unfortunately those assumptions and constraints don't make good blog or product marketing material copy. "We have a typical LOB app," "our data needs are nothing extraordinary," etc.
Re:Creating a Document-Store Hybrid in Postgres 9.5 by coderzach on Friday February 05, 2016 @03:36PM (#11044414)
Postgres isn't designed from the ground up for high availability or scalability. Most of the in-built functionality for setting up HA wasn't introduced until 9.0, and the process of setting up HA postgres with automatic failover requires third-party tools. As for scalability, postgres can only scale vertically.

If you need a solution that can scale writes to more than than one node, or a solution that has first party support for HA with automatic failover, you shouldn't be using postgres.

As an aside, I find the dogmatic "just use postgres for everything" choir just as bad as the marketing BS associated with noSQL databases.

Re:Creating a Document-Store Hybrid in Postgres 9.5 by snuxoll on Friday February 05, 2016 @03:46PM (#11044483)
Unless you are using synchronous replication (locks up transactions if there is an issue with replication) there's no safe way to do automatic failover with an RDBMS without using shared storage (expensive, pain in the butt).

Corosync and pacemaker make setting up automatic failover with shared storage relatively painless. I don't do it this way personally because shared storage is a giant pain.

Re:Creating a Document-Store Hybrid in Postgres 9.5 by empthought on Friday February 05, 2016 @04:07PM (#11044623)
"High availability" and "high availability with automatic failover" are not the same thing. (The tricky bit is actually the un-failing-over part.) I assure you that people were setting up high availability systems with PostgreSQL 7.x in 2004 or so.

Very few organizations need to scale their database in any other direction than vertically. If it works for Stack Overflow, it's certainly likely to work for your application.

Re:Creating a Document-Store Hybrid in Postgres 9.5 by takeda on Friday February 05, 2016 @04:55PM (#11044933)
It's also lack of understanding the problem you're trying to solve.

For example at my previous job we had a dedicated data store for performing lookups such as ip -> zip code and latitude/longitude -> zip code.

The company decided to use Oracle Coherence and store all data in memory, because it'll be fast. To store all of that information they needed 16 m3.medium machines.

Last year they had a great success optimizing it, because they managed to replace 16 x m3.medium machines to just 3 x c3.2xlarge machines running MongoDB (the data was ~12GB).

I did a POC and put the data in PostgreSQL with proper columns and indices (I just needed to install ip4range and PostGIS), the whole data fit in 600MB! the queries took under at most 2ms on cold cache but generally were under miliseconds, because all the data fit in RAM.

Re:Creating a Document-Store Hybrid in Postgres 9.5 by threeseed on Friday February 05, 2016 @06:48PM (#11045604)
It seems more of a problem with the people than technologies.

Why would you need 16 m3.medium (60GB RAM total) to store 600MB of data ? I've used Oracle Coherence and other grid technologies and something doesn't sound right here. It is just a couple of distributed Java HashMaps we are taking about here.

Likewise if 600MB of data is expanding to 12GB in MongoDB then something is very, very wrong with the design of your schema.

Re:Creating a Document-Store Hybrid in Postgres 9.5 by takeda on Friday February 05, 2016 @10:16PM (#11046377)
Yes problem were people. Too much politics and that's why I left.

Why more data? In case of IP geolocation neither of that technology understood IPs not to mention being able to create a proper index for ranges.

So in case of Mongo, to get a good performance they decided to generate every possible IPv4 address and map it to zip code. To increase efficiency they stored every IP as a 64 bit integer.

In Coherence they did the same thing, but I guess less efficiently (did not look how it was done, since at the time coherence was in the process of being eliminated) I'm guessing maybe they stored is as a string?

Also note that Coherence is a distributed cache that supposed to withstand couple nodes going down, so a lot of data was duplicated.

Re:Creating a Document-Store Hybrid in Postgres 9.5 by empthought on Friday February 05, 2016 @07:49PM (#11045898)
> It seems more of a problem with the people than technologies.

Isn't that what "lack of understanding of the problem you're trying to solve" means?

Though in this case, if the problem had "IP4 ranges" and "geographic data and computation functions" in its scope, then MongoDB is quite inadequate compared to PostgreSQL.

Re:Creating a Document-Store Hybrid in Postgres 9.5 by threeseed on Friday February 05, 2016 @06:44PM (#11045582)
> Very few organizations need to scale their database in any other direction than vertically

Almost every major organisation and plenty of startups/SMEs are investing big into analytics programs. And whilst they don't have massive data sets they are expecting real time performance so being able to scale horizontally is important.

If you could vertically scale I/O that would be one thing but you can't.

Re:Creating a Document-Store Hybrid in Postgres 9.5 by empthought on Friday February 05, 2016 @07:57PM (#11045930)
That's a bunch of read-only replicas though; RDBMSes can do that. Or if it's really big data, then we are talking about Redshift, Greenplum, or Teradata.

There's no need for something like CouchDB.

Re:Creating a Document-Store Hybrid in Postgres 9.5 by snuxoll on Friday February 05, 2016 @06:50PM (#11045622)
PL/Proxy?
Re:Creating a Document-Store Hybrid in Postgres 9.5 by brightball on Friday February 05, 2016 @03:38PM (#11044428)
My best attempt at PostgreSQL marketing material copy. :-) http://www.brightball.com/postgresql/why-should-you-learn-po...
Re:Creating a Document-Store Hybrid in Postgres 9.5 by merb on Saturday February 06, 2016 @10:38AM (#11048129)
Good stuff expect Listen Notify. it's too dependent on the driver. I mean some drivers needs some kind of polling which is, aweful. Mostly you could go around this issue by opening another connection without going through your database pool and building a simple driver just for listen/notify. (jdbc I'm looking at you)
Re:Creating a Document-Store Hybrid in Postgres 9.5 by brightball on Sunday February 07, 2016 @09:10AM (#11052873)
Most of the uses of it that I've seen have taken this approach with a listener on a thread using a different connection.
Re:Creating a Document-Store Hybrid in Postgres 9.5 by snuxoll on Friday February 05, 2016 @03:20PM (#11044314)
PostgreSQL HA is not that difficult to setup, repmgr makes the task pretty easy. If you are talking about automatic failover then that's another story entirely, because it is simply not appropriate for relational databases unless you are using shared storage (expensive, painful) - but if you want to go that route corosync and pacemaker have your back.
Re:Creating a Document-Store Hybrid in Postgres 9.5 by __jal on Friday February 05, 2016 @03:39PM (#11044433)
PG HA really isn't difficult to set up.

You have to know about your environment and how you use the DB, but if that's limiting factor then I'm really not sure why you'd try to set up HA.

Re:Creating a Document-Store Hybrid in Postgres 9.5 by bsaul on Saturday February 06, 2016 @07:23AM (#11047464)
I've read many answers to your comment saying "but it's not that hard". So to all those people : please, write a blog or send a link to one that explains how to setup a HA cluster with automatic failover. I've spent some time trying to setup a simple pg cluster once ( not even HA, just duplicate writes) and it was nothing but straightforward.