Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

PostgreSQL 9.4 Released

postgresql.org

Post reply on HN
Print view View original

181–189 of 189 posts

Re: PostgreSQL 9.4 Released

#181

Post by bkeroack »

gfodor wrote: post #159
bkeroack wrote: post #113

Earlier quoted context omitted.

Imagine you're an early stage startup. You have a handful of overworked, stressed-out engineers trying to ship v1.0. You don't have the time or resources for a dedicated Ops team, so you go with AWS (I think this is a great use case for AWS or any public cloud, btw--it just makes sense at a really early stage startup). You ship your product, get some customers. Fast forward 18-24 months. You have grown enormously, yo…

Congratulations, you're rich and now can pay someone else to deal with this.

Do you seriously think that a two year old startup (in the scenario proposed) is "rich"?

Re: PostgreSQL 9.4 Released

#182

Post by norkakn »

pmontra wrote: post #168
norkakn wrote: post #140

Earlier quoted context omitted.

The most infuriating way is that it will roll back its oplog on an election, and sometimes throw away confirmed writes. Mongos does weird magic as well when it gets confused, and will confirm writes to the wrong shards during "interesting" situations.

This is more than worrysome. I googled for it and found this http://docs.mongodb.org/v2.6/core/replica-set-rollbacks/ Is this behaviour what you are referring to? > A rollback reverts write operations on a former primary when the member rejoins its replica set after a failover. A rollback is necessary only if the primary had accepted write operations that the secondaries had not successfully replicated before the pri…

Yeah, it's worse than it sounds though. If you write to 2/3 nodes, and the third gets elected, you can roll back majority confirmed writes. I think it did even worse things when near capacity, but those are harder to pin down.

Re: PostgreSQL 9.4 Released

#183

Post by norkakn »

threeseed wrote: post #128
norkakn wrote: post #122

Earlier quoted context omitted.

Clustering is really, really hard. Cassandra is one of the better ones out there, but you have to deal with its data model and weird consistency promises (which however weird you think they are, are weirder) The correct way to cluster also changes dramatically depending on your use case. Sure there are things like RAC that promise to make it just work, but those don't scale more than a few nodes. Mongo is kind of the…

> Cassandra is one of the better ones out there I take it you haven't actually used Cassandra much in the last few years. It's data model is almost identical to a typical relational one and it's consistency promises are quite clear: http://www.datastax.com/documentation/cql/3.0/cql/aboutCQL.h... And I've scaled Cassandra clusters from 1 to 100 nodes in hours with no issues. It really is quite simple. Likewise have ha…

Cassandra avoids some of the really visible issues by being AP instead of CP. Hbase hits them, but dodges a bit by only having row level consistency. They are solving very different problems.

The lack of vector clocks in Cassandra can lead to some very non-intuitive (possible wrong) behavior - check out their counter implementation for some rage on that. It's pretty well made though, and I think C*, Hbase and Postgres all have great uses (along with Redis, and a lot of others)

Mongo tends to get things subtly wrong in ways that corrupt data, or that don't scale, and it gives up both A and C.

Re: PostgreSQL 9.4 Released

#184

Post by paulryanrogers »

hype_this wrote: post #164

What a joke. There's not even a working MySQL => Postgres 9.4 migration tool that works. Try them. On paper, Postgres sounds great. But of all the people on here cooing about it, how many are actually using the tool? I don't know who has the stronger hype machine on Hacker News: Postgres or Rust.

Having moved from Mysql to Postgresql in 2005 and continued with it up to the present, I'd say it's a superior database for OLTP, enterprise workloads. Mysql's traditional mode with a transactional engine can work too; though, I've found Postgresql to be a solid, consistent performer for my use cases. I've also used it at home after running into trouble with Sqlite: https://paulrrogers.com/2014/10/self-hosting-owncloud-review... .

Postgresql's excellent documentation made the move much more practical than it would have been using a clearly less documented tool. (One such as MonetDB circa 2005: http://www.monetdb.org/ .)

Similarity to standard SQL and Oracle are also nice if one happens to work in an heavily regulated industry where one may have to migrate to certified platforms.

Re: PostgreSQL 9.4 Released

#185

Post by pmontra »

norkakn wrote: post #182
pmontra wrote: post #168

Earlier quoted context omitted.

This is more than worrysome. I googled for it and found this http://docs.mongodb.org/v2.6/core/replica-set-rollbacks/ Is this behaviour what you are referring to? > A rollback reverts write operations on a former primary when the member rejoins its replica set after a failover. A rollback is necessary only if the primary had accepted write operations that the secondaries had not successfully replicated before the pri…

Yeah, it's worse than it sounds though. If you write to 2/3 nodes, and the third gets elected, you can roll back majority confirmed writes. I think it did even worse things when near capacity, but those are harder to pin down.

So the "majority" write concern is not safe. However it seems that the number of nodes that must confirm a write can be configured http://docs.mongodb.org/manual/core/replica-set-write-concer... From what I read there it seems that if you want to get a confirmation from all the nodes and one gets offline you'll be waiting forever (but there is wtimeout). I'm afraid that any db cluster with any technology has similar problems.

Re: PostgreSQL 9.4 Released

#186

Post by tomiko_nakamura »

skybrian wrote: post #114
lsaferite wrote: post #110

Earlier quoted context omitted.

But that's not really a correct thing to expect is it? If an object is an unordered set of name/value pairs then multiple serialized versions of the object may indeed be the same object data.

It's not something you can assume, but sorting the keys is sometimes a desirable feature in a JSON serializer. (Just like pretty-printing is.)

AFAIK the default serializer does not guarantee the order to be stable, but I haven't participated in the development of this feature that closely. So maybe I'm wrong.

Anyway, it should be possible to write a simple serializer on your own - a trivial PL/V8 function should suffice, I guess. And then you can define a CAST using that function (but generally adding casts is a bit dangerous, as it may have unexpected consequences).

Re: PostgreSQL 9.4 Released

#187

Post by tomiko_nakamura »

hype_this wrote: post #164

What a joke. There's not even a working MySQL => Postgres 9.4 migration tool that works. Try them. On paper, Postgres sounds great. But of all the people on here cooing about it, how many are actually using the tool? I don't know who has the stronger hype machine on Hacker News: Postgres or Rust.

We certainly are. We're operating an analytical service operated on PostgreSQL - tens of TBs of data, hundreds of machines, tens of thousands of clients. Initially it was running on MySQL, but because of various reasons we migrated to PostgreSQL ~3y ago and never regretted that.

The reasons were both technical (better performance with this kind of workload, great reliability, excellent code quality, ...) and political (we have contributed numerous patches to PostgreSQL - not sure if you ever tried to do that with MySQL).

If you think there's a simple MySQL -> PostgreSQL migration tool (or a migration between arbitrary databases), you're foolish. Databases are not that interchangeable - all databases have some issues with specific workaround, and the 'good bits' are database-specific too. And those things are anchored in the application code, so if you think there's a simple migration tool, you'll be disappointed.

Re: PostgreSQL 9.4 Released

#188

Post by tomiko_nakamura »

curiously wrote: post #125

I just recently began using PostgreSQL albiet an older version. Does this 9.4 mean that MongoDB is now pretty much a dud? Being able to store, manipulate, query JSON data AND have SQL on a established wheel that have been proven reliable and polished far longer than the age of most other databases? Are there any code examples (preferably Python) that show how to use JSONB? I'd love to see some examples on how to quer…

I'm not a big fan of MongoDB, but I don't think the introduction of JSONB in PostgreSQL 9.4 makes it a dud (which does not mean MongoDB is not a dud for other reasons).

JSONB allows you to do a lot of things that people are often doing with MongoDB (or document databases in general), but there are still some features not available in PostgreSQL. Built-in sharding, for example. There are external tools to do that with PostgreSQL, and I do have my doubts about MongoDB (partially because I only hear about the horror stories), and I expect similar features in PostgreSQL 9.5 / 9.6, but at the moment it's not there.

Not sure what you mean by Python examples - you can either fetch the data as 'text' and convert it in the application (e.g. json.loads) or just use psycopg2 with an adapter (http://initd.org/psycopg/docs/extras.html) and you'll get the data as Python dictionaries.

The best source of examples is the official documentation (http://www.postgresql.org/docs/9.4/static/datatype-json.html) and the "NoSQL on ACID" training from Bruce Momjian and Thom Brown (https://wiki.postgresql.org/images/d/de/NoSQL_training_-_pgc...).

Re: PostgreSQL 9.4 Released

#189

Post by tomiko_nakamura »

photograve wrote: post #25

Anyone has a performance benchmark and/or has experience on the scalability on this version?

That really depends on what version you're using now, and what exactly you mean by scalability.

If you're using 9.1 or older, you may see a significant improvement in OLTP workloads on many-core machines (making it linearly scalable to >64 CPUs). This happened in 9.2 (i.e. ~2 years ago).

The main improvement in 9.4 I'm aware of is the GIN fastscan, which significantly improves performance of applications using GIN indexes (e.g. full-text).

Of course, there are many other performance improvements on various places - the principle is not to make the new version slower.

Some interesting numbers were presented in this talk at pgconf.eu 2014, including 9.4 beta (but there should be no significant differences): http://www.slideshare.net/fuzzycz/performance-archaeology-40...

Post reply on HN