PostgreSQL 9.4 Released

http://www.postgresql.org/about/news/1557/

by petercooper • 12 years ago

744 188 12 years ago

apetresc• 12 years ago

So when can we expect AWS RDS to support it? :)

bkeroack6• 12 years ago

Not trying to be mean but, why wait? If you're stuck on AWS it's not impossible (though difficult and annoying certainly) to get a decently-performant Postgres instance going. The experience setting it up will lessen the vendor-lock that Amazon has on you (convenience always has a price).

dilap• 12 years ago

Outside observer: Man, I figure setting up a "decently performing" Postgres instance on AWS would be such a common thing to do that it would either be known to be impossible, or have cookie-cutter instructions, if not a script. How is it that it's "difficult and annoying" still?

Not trolling, genuinely curious :)

gfodor5• 12 years ago

The answer is simple, as with any AWS service, a whole slew of concerns (but not all of them) become Not My Problem. I have enough problems.

sarciszewski2• 12 years ago

Best news I've heard all week. Awesome. Time to update my servers :D

gamesbrainiac• 12 years ago

EFF launches an iconic case and PG 9.4 on the same day? This is probably the best day in the year and its not even christmas yet.

pilif3• 12 years ago

Like every year before, the Postgres team has blessed us with an early christmas present. And like every release post before, I'd like to use this opportunity to say thanks to the team for the awesome job they are doing year after year.

It's not just the database itself (and that's awesome on its own right), but it's also all the peripheral stuff: The documentation is seriously amazing and very complete, the tools that come with the database are really good too (like psql which I still prefer to the various UIs out there).

Code-wise, I would recommend anybody to have a look at their git repo and the way how they write commit-messages: They are a pleasure to read and really explain what's going on. If everybody wrote commit messages like this, we'd be in a much better place what code-archeology is concerned.

Patches from the community are always patiently reviewed and, contrary to many other projects, even new contributors are not really required to have a thick skin nor flame retardant suits. The only thing required is a lot of patience as the level of quality required for a patch to go in is very, very high.

Finally, there's #postgresql on Freenode where core developers spend their time patiently helping people in need of support. Some questions could be solved by spending 30 seconds in the (as I said: excellent) manual and some of them point to really obscure issues, but no matter what time it is: Somebody in #postgresql is there to help you.

I think there's no other free software project out there that just gets everything right: Very friendly community, awesome documentation, awesome tools, and of course and awesome product offering to begin with.

Huge thanks to everybody involved.

Also: Huge YAY for jsonb - I have many, many things in mind I can use that for and I have been looking forward to this for a year now.

gdulli• 12 years ago

In addition to those peripheral benefits there's also the intangibles. Mainly that worrying about database stability is just not part of my life the way it was five years ago before I'd fully migrated to PostgreSQL.

gh02t3• 12 years ago

we'd be in a much better place what code-archaeology is concerned.

This sounds like a great setup for a sci-fi novel. 500 years into the future, the infrastructure their distant ancestors coded has begun to fail. Now Biff Miffington, code-archaeologist, must sift through millions of forgotten messages using a mysterious tool remembered only as "git." Its interface is arcane and the remaining messages broken, tainted by the destructive influence of Mountain Dew and Cheetos. Will he unravel the mystery that's causing Candy Crush Saga MCCXXXI to kill its users?

Edit: On what OP actually said, I'd also like to say that Postgres is an awesome product.

frost_knight• 12 years ago

Candy Crush Saga on git? Sounds more like a fantasy novel.

dnfehren• 12 years ago

Ken Macleod's books have a some of this kind of stuff in them - people having to maintain old space stations that run Oracle DB's and stuff like that.

pilif3• 12 years ago

When I say "code archeology", I mean the equivalent of "git blame" without assigning blame - just finding out why stuff is the way it is and not who's responsible because that would be totally pointless, especially years after the incident.

I do like your spin however :-)

arethuza• 12 years ago

Vernor Vinge's A Deepness in the Sky actually has a role described as "programmer archaeologist":

http://en.wikipedia.org/wiki/A_Deepness_in_the_Sky#Interstel...

NoMoreNicksLeft• 12 years ago

In Vernor Vinge's Deepness In The Sky, there really are code archeologists, as it's often simpler to find programs than to write them from scratch.

They believe that the Unix epoch (since extended many times over) dates to the moon landing (approximately correct), since the exact historical explanation has been lost.

angersock• 12 years ago

Testing the waters, I'd played with the idea of a story with basically this setup:

Two new developers start at a company/startup, and are brought in to do a six-month sprint to fix a stalled/broken product after the development team becomes unavailable because reasons. So, they're dropped into a codebase and are trying to pull everything together.

However, as they work through into deeper and deeper parts of the system, the commit messages and comments get more and more cryptic and unsettling, hinting at the reasons for the prior team's dissolution, the business forces that caused that to happen, and maybe something worse/better going on outside.

I'm really lazy though. :(

chetanahuja• 12 years ago

I'm pretty sure Sony has already optioned this plot and trying to get Tom Cruise for Biff.

qooleot2• 12 years ago

Since you're planning on doing some conceptual work with JSONB, just a heads upon one gotcha -

duplicate properties are not allowed. I.E.:

{ task: "do stuff", task: "do other stuff" }

which sometimes is useful when you have front-end data with an N-number of entries but its a form that serializes to an object instead of an array. There are other use cases too.

harel• 12 years ago

I would have been worried if it was allowed. This is exactly what arrays are for.

masklinn• 12 years ago

RFC4627 §2.2 ¶1

The names within an object SHOULD be unique.

RFC2119 §3

[SHOULD], or the adjective "RECOMMENDED", mean that there may exist valid reasons in particular circumstances to ignore a particular item, but the full implications must be understood and carefully weighed before choosing a different course.

Not allowing duplicate keys in JSON objects is very close the exact opposite of a gotcha. Allowing and round-tripping them would be a gotcha.

jackmaney• 12 years ago

Okay, I'll bite.

What, exactly, do you think should happen if you have an object of the form

    { task: "do stuff", task: "do other stuff" }

? Objects in JSON are key-value pairs. A single key goes to a single value. Instead, you should map task to an array of values.

IndianAstronaut• 12 years ago

even new contributors are not really required to have a thick skin nor flame retardant suits

I will have to look into this community more. I've been burned by some oss communities before.

danmaz742• 12 years ago

+1 for #postgresql help. Recently I had a really weird problem, and after a few hours with forums and manuals I went there to ask for help. They saved me I don't know how many more hours that would almost for sure not found the completely unexpected answer. Thanks!

jeffdavis• 12 years ago

Can the documentation or user experience be improved to prevent that issue from happening to someone else?

emcrazyone• 12 years ago

well said @pilif. I've been using PostgreSQL since the early 6.x days as the back end to web sites. As time went on, I developed an Accounting & ERP software suite in C++ with a thin wrapper around libpq. We've pretty much standardized on PostgreSQL for the things you just said: "friendly community, awesome documentation, awesome tools, and of course and awesome product...."

I literally have a multi-million dollar business & product because of it. And without it, we would be stuck with Oracle or MS-SQL.

And we just recently added full text search within our product using the PostgreSQL full text add-on. My customers absolutely love the feature and they love us because of this.

Another HUGE Thank you from me too.

systematical• 12 years ago

Switched over to PostgreSQL for a personal project for the json datatype. Great if you want some mongo-esque document storage without losing out on having a relational database.

http://clarkdave.net/2013/06/what-can-you-do-with-postgresql...

aidos• 12 years ago

That info is for PostgreSQL 9.3 (and 9.2). 9.4 comes with other enhancements if you're using json. jsonb in particular opens up whole new possibilities if you're doing heavier work with json data.

k_sze• 12 years ago

Bye bye MongoDB.

digitalzombie• 12 years ago

How do you cluster in postgresql? Serious question, my prefer noSQL is cassandra and clustering is pretty easy.

I ask this question every year and postgresql have not deliver this. If there is any, there are hardly any documentation on it.

icebraining2• 12 years ago

Postgres-XL, Postgres-XC, pg_shard...

agentultra• 12 years ago

In 9.4, Logical Decoding supplies a new API for reading, filtering and manipulating the PostgreSQL replication stream. This interface is the foundation for new replication tools, such as Bi-Directional Replication, which supports the creation of multi-master PostgreSQL clusters. Other improvements in the replication system, such as replication slots and time-delayed replicas, improve management and utility of replica servers.

throwaway90446• 12 years ago

Just do vnode-based consistent hashing on the client side.

angrybits3• 12 years ago

Because multi-master clustering is extremely hard to do when you need transactional semantics. For those that need it, scaling up is by far the easiest path. Most of us will never exceed the growth rate of server capacity over time, so you can just keep going up. HA using stand-bys and failover is well documented, and quite sufficient I might add.

If you don't need transactional semantics and you do need globally distributed multi-master key/value storage, I would not switch from Cassandra to PG, you already have a good solution for that.

If you don't need transactional semantics and you also do not need globally distributed multi-master key/value storage, use whatever you want, it doesn't really matter.

norkakn7• 12 years ago

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 worst in this - it clusters in one weird way, has bad tooling, and subtly destroys your data at scale.

The general philosophy with postgres is to do it right, or not do it. There are ways to do specific kinds of clustering, but all of them (just like mongo, oracle, etc) have a lot of nuances to them.

If you have a natural shard key, use a bunch of schemas and table inheritance, and eat the downtime during re-shards. Check out citus as well. They have their issues, but they can help you hook up what you need.

mslot• 12 years ago

Depending on your exact requirements, you can use pg_shard [1] for real-time inserts/update/delete, or CitusDB [2] for scaling out selects, or combine them.

[1] https://github.com/citusdata/pg_shard [2] http://www.citusdata.com/

Disclaimer: I work for Citus Data.

steventhedev2• 12 years ago

Quick question regarding JSONB:

Is attribute order stable? Obviously, order is not preserved, but if the order changes on subsequent accesses, this causes problems if you ever serve content directly from a jsonb field without sorting the attributes manually.

arthursilva2• 12 years ago

Fields are always sorted (internally), I didn't test but I assume it's preserved on the wire (as json) as well.

matthewrudy• 12 years ago

According to the json spec

    "An object is an unordered set of name/value pairs."

So you should not be relying on ordering of json fields.

steventhedev2• 12 years ago

In theory, correct. In practice, here are the two use cases which that approach breaks:

1. Cache control using etags. If the content changes by a single byte, even if semantically identical, the etag should change. Hence '{"foo":1,"bar":2}' is not equivalent to '{"bar":2,"foo":1}'. I can see serving such information directly, or embedding it into a larger JSON response.

2. Committed JSON files. This is an anti-pattern, but one I've seen many times. In one case, it was a translation file that was generated in a separate project. When I joined, it had been like that for more than 5 years. It was like that when I left, although I at least monkey patched REXML to emit sorted attributes in XML (also unordered in theory).

So while in practice, I don't depend on a specific ordering, but I need the order (whatever it is) to be stable. For all I care, it could be sorted by the cryptographic hash of the keys, so long as it is consistent.

Just to gather what others have said here: JSONB appears to store fields sorted lexicographically for binary search purposes, and emits them in that order as well. This could be undocumented behavior, yet important for the above two use cases.

jeltz2• 12 years ago

The attributes are stored and returned in sorted order. The reason for this is to allow binary search in JSON objects with many keys.

azdle• 12 years ago

Why would this cause problems? Order is irrelevant in JSON. "An object is an unordered set of name/value pairs."[0] From a there is absolutely no difference between {"name": "Patrick", "age": 24} and {"age": 24, "name": "Patrick"}.

[0] http://json.org/

EDIT: Well, now four of us have replied at the same time saying the same thing, I'd say this topic is now well covered.

gburt• 12 years ago

Of course, it is still an interesting question of whether it would be practically stable in Postgres or not.

skybrian2• 12 years ago

Sometimes it matters whether serialization to a string is a deterministic function. If the JSON is the same, you want the output to be the same. A JSON parser won't care, but it's useful to do a string comparison without parsing (for example when diffing output).

atonse2• 12 years ago

Great news! I'd love to move over to this from MongoDB for a project that has high uptime requirements. But while I think the JSON will really replace it, does PG have a solution for High Availability (like replica sets) in the works?

I'm newer to Postgres so am not sure. Replica Sets are the killer feature for me, more so than just storing JSON documents. I'd appreciate if someone can chime in. I've done some googling but there seem to be multiple strategies for replication.

arthursilva2• 12 years ago

PG has async/sync/hybrid replication for years already. It's not as tooled as MongoDB but there's some tools like http://www.repmgr.org/ to amend it.

internetisthesh3• 12 years ago

Every time I look into tools like this they are quite far behind for example AlwaysOn in MSSQL. For example, with PG I have to reseed the original master if it comes back online after an outage. It's as far as I can tell not fully automatic and transparent to me as the guy responsible for managing it. With SQL and elasticsearch+ZooKeeper for example nodes can go up and down without anyone noticing it and me not having to do anything. Is this still a place where PG is behind?

en4bz• 12 years ago

Did you take a look at Slony-I?

http://slony.info/

gfodor5• 12 years ago

AFAIK RDS has turnkey read only replicas (and hot standby.)

devonnulled• 12 years ago

Hey there. I gave a talk at PGConfNYC '14 earlier this year about our solution to HA using open source solutions. The animations and stuff don't really work too well on Slideshare, but it should point you in the right direction:

http://www.slideshare.net/TeamARIN/building-a-high-availabil...

pgSQL doesn't have anything built in for fencing, failing-over, etc. by default, but by using stuff like Pacemaker, you can get the job done with a little elbow grease.

illumen• 12 years ago

Do you have any scripts to set up a cluster for this? Or know where to look?

tracker14• 12 years ago

There are some open-source options, and several closed source options for this... parts are closer to baked in with 9.4, but being realistic, you need to defer to a commercial option.

EnterpriseDB pricing for this isn't too bad (about $7k/cpu-socket/year), which is a lot less than MS-SQL, DB2 or Oracle for most uses... but it's imho a feature that should be in the box.

jsmeaton• 12 years ago

Pricing per socket really really frustrates me. I work in an Oracle shop where we're hamstrung by similar pricing.

radicalbyte• 12 years ago

Next year I really need to switch from MSSQL to Postgres. The work that the Postgres team have done in the last 2-3 years is really amazing.

They are also clearly reaping the benefits of some very smart architectural decisions, and that gives me the confidence that they will be able to continue innovating in the coming years.

bsg753• 12 years ago

There is actually a lot of overlap in the feature set. When I moved from MSSQL to a gig that was all Postgres it was fairly smooth to do so, the main differences being OS related. Thus you don't need to switch over entirely to add a useful tool to the toolbox.

MSSQL has some advantages in parallel query execution and data warehousing enhancements - if you can afford the licensing for the latter. The other view on that is you can tip up as many Postgres production instances as you have hardware for without impacting the software budget.

g8oz• 12 years ago

If only there were a way to run performant Postgres on Windows. Maybe it's time learn Hyper-V so I can run it in a Linux VM.

jrapdx3• 12 years ago

That's close to what I'm doing, except running FreeBSD 10.1 in Hyper-V. It's actually pretty easy to set up since FBSD 10.1 is available for download as a VHD. Just need to specify the VHD image file as the disk to attach when creating a new HV VM.

It's somewhat more fun/challenging to configure network access for the VM. Once that's done it's very handy for developing web servers/apps. With the web server running in the VM, the client/browser on the host points to the VM just like any remote site. PostgreSQL in the VM performs quite well.

IMO this kind of development is much more satisfactory in a unix environment than under Windows and Hyper-V provides a convenient way to get there.

jeltz2• 12 years ago

Looking forward to when we move to 9.4 so I can start using "sum(foo) FILTER (WHERE bar)" instead of the ugly "sum(CASE WHEN bar THEN foo END)".

wvenable• 12 years ago

Wow, that's fantastic. My queries are littered with that type of construct. Unfortunately, we are using SQL Server and Microsoft hardly ever makes any developer-friendly enhancements to T-SQL.

tracker14• 12 years ago

True enough... I can't even begin to wrap my head around the XML wrangling functions in T-SQL, I feel like a n00b copying JS methods for jQuery when I go there. That said, it's probably one of the easiest RDBMS servers to administrate for small-medium sized deployments.

sarciszewski2• 12 years ago

I've written migration unit testing tools before between SQL Server and PostgreSQL specifically (there are specific gotchas between the two).

If you need help migrating to PGSQL send me an email and I'll help you escape from MSSQHell. :D

wiredfool• 12 years ago

Ooohh. I hadn't seen that one. That's going to make things so much better for the accounting code.

electrum• 12 years ago

In Presto, we support the popular but non-standard IF construct as a shorthand for CASE, so you can do this: sum(if(bar, foo))

We also have a count_if() function: count_if(bar)

http://prestodb.io/docs/current/functions/conditional.html#i...

http://prestodb.io/docs/current/functions/aggregate.html#cou...

netcraft• 12 years ago

How long till someone creates a library with the mongo api?

remeh• 12 years ago

Actually ... https://github.com/umitanuki/mongres

mcx• 12 years ago

There is also ToroDB: https://github.com/torodb/torodb, HN post: https://news.ycombinator.com/item?id=8527013

photograve• 12 years ago

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

tomiko_nakamura4• 12 years ago

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

codeaken• 12 years ago

Did PostgreSQL just kill MongoDB?

organsnyder2• 12 years ago

No.

bonif3• 12 years ago

You're right, lots of amateur-hour developers might still be using mongodb

jpgvm• 12 years ago

MongoDB has been dead for a long time.

threeseed7• 12 years ago

Only someone with a poor understanding of the industry would think that. Mongo is killing it in the enterprise and their partnerships with Teradata et al is only going to cement their position for likely decades to come.

Plus MongoDB's pluggable engine approach will definitely breath some new life into it.

nemo44x4• 12 years ago

This is exactly right. I can't believe how snide and ignorant a few of the commenters are (except I can because it's the same folks with the same FUD every time). MongoDB isn't standing still and it is getting better and better with every release. Not to mention there are 100's of massive projects running on MongoDB with success.

Something about MongoDB really drives the Postgres community (and certain NoSQL DB fans) nuts and I'm guessing it's that MongoDB is eating their lunch, growing faster than them, is gaining popularity faster, etc. Keep in mind MongoDB is many years younger too and is maturing more quickly now. Plus, developers absolutely love working with it.

It's a tool like any other and still has issues but I'm afraid some here are dancing on its grave well before it has even shown signs of letting up on its growth.

chx• 12 years ago

I doubt. It seems that MongoDB still has a much richer query syntax than jsonb.

Disclaimer: I consult for MongoDB Inc.

squigs25• 12 years ago

I'm really pumped about the update to GIN indexes, and the ability to concurrently update materialized views. Both enhancements are huge for the postgres ecosystem, and especially for productionizing postgres databases.

petercooper3• 12 years ago

Are there any good books coming out that cover Postgres 9.4? I know the docs are okay but I want something with more of a narrative structure as my history with Postgres is spotty. The only one I've found so far is O'Reilly's "PostgreSQL: Up and Running, 2nd Edition" coming out this month but would prefer a personal rec.

percept• 12 years ago

_Beginning Postgres_, Cooper Press (2015)

bsg753• 12 years ago

Any ISBN or link for that? Google mostly returns old unrelated titles.

VieElm• 12 years ago

I have O'Reilly's "PostgreSQL: Up and Running, 2nd Edition" (You can buy the ebook already) and it's mostly about setting it up and administering postgres and a look at the tools around postgres. It's not a book that goes in depth on actually on how to write SQL for postgres, although it has a secton on this, or develop applications with postgres. It's more of an ops book. Anyway, I like it because coming from MySQL I was unfamiliar with how to set up and use postgres and this book set me straight.

petercooper3• 12 years ago

Thanks to Safari, I'm now several chapters into the book and it's perfect for me - thanks :) It was the ops side of things that was the biggest sticking point for me, although it seems like it'll dig into the JSONB stuff later on too.

gfodor5• 12 years ago

JSONB is getting a lot of attention (and deservedly so) but logical decoding is much more exciting to me. Being able to capture postgres transactions to put into a durable log (like Kafka) for downstream processing is a fundamental tool needed to build a unified logging architecture. If you've worked with hadoop you've probably tried to approximate this by hand by taking regular snapshots of your database or something, but this is much, much more sane. Really exciting. Great work postgres team!

aswanson• 12 years ago

Even though rails hides it from me, I honestly don't mind directly working with the SQL interface on this db, and it's language interfaces are awesome. Thanks, team.

tiffanyh• 12 years ago

I really wish someone would update these benchmarks to the latest releases of Scientifc Linux, FreeBSD & Dragonfly

http://www.dragonflybsd.org/performance/

rpedela2• 12 years ago

I am pretty happy about the addition of ALTER SYSTEM. I haven't tried it yet, but I think it will make automatic failover to a standby easier to implement. Does anyone have experience with this?

tracker14• 12 years ago

Just want to say to the PostgreSQL and EnterpriseDB guys that it's always great to see the progress on this. My hopes for 9.5/10 is that we will see PLV8 and Replication baked into the actual release.

PLV8 is such a natural fit with the new JSON(B) types that it's probably going to become the most used extension with that data type... And imho sorely missing from the out of the box experience. I'm glad that they've concentrated on getting the data structure and storage right first. Hopefully we'll see this in vNext.

As to replication, I understand that this is part of EnterpriseDB's business model, just the same not having the basic replication pieces baked in, is still lacking compared to other databases. Even if the graphical tooling was commercial only, and all the knob frobbing via config or command line is more complex, having it in the box is a must imho. I actually really like how MongoDB handles their replica sets, and where RethinkDB is going with this as well. Though they aren't transactional SQL databases primarily, it's a must have feature these days. Replication with automagic failover is a feature that has gone past enterprise-only.

One last piece, would be if there were built in functions similar to the String.prototype.normalize that was added to JavaScript... so that input strings could be normalized easier for comparison/indexing, though PLV8 support could/would bring this readily.

All the same, thanks for all of your hard work, and I look forward to the future of PostgreSQL.

internetisthesh3• 12 years ago

Yes, replication with auto failover is far away from enterprice-only today. If you pay 5$/month you can get a SQL Database in Azure running on 3 nodes with auto failover. One node is synchronous and the second one is async updated. I wouldnt choose a database today unless setting up somerhing like that is obvious and trivial.

mgkimsal• 12 years ago

Is there an equivalent to the MySQL handlersocket stuff in postgresql?

http://www.percona.com/doc/percona-server/5.5/performance/ha...

http://www.slideshare.net/akirahiguchi/handlersocket-2010062...

It might go against the "no transaction" crowd, but seems useful for performance-critical needs. I'm scheduling a bit of testing time with it next week to see if it's something I'd roll out in production (Maria 10 system)

jvinet• 12 years ago

With JSONB here, JSONPath starts to become very interesting...

http://goessner.net/articles/JsonPath/

http://blog.redfin.com/devblog/2012/03/json_in_postgres.html

That article was written before JSON/JSONB showed up, but the idea remains the same.

I didn't have plv8 installed, so I did some plumbing code in plpython. plv8 would be more suitable though.

https://github.com/jvinet/pg-jsonpath

davidw• 12 years ago

There are a lot of things that have changed since my first programming job, back in 1997. Things that I still use and love: Postgres, and Emacs.

elchief• 12 years ago

Full notes here:

http://www.postgresql.org/docs/9.4/static/release-9-4.html

My favourite parts:

Allow views to be automatically updated even if they contain some non-updatable columns

Allow control over whether INSERTs and UPDATEs can add rows to an auto-updatable view that would not appear in the view. This is controlled with the new CREATE VIEW clause WITH CHECK OPTION.

Allow security barrier views to be automatically updatable

mrmondo• 12 years ago

Congratulations to the PostgreSQL team for continuously supporting and improving what is in my mind the best all-round database server out there.

curiously• 12 years ago

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 query every record that contains a key in a json, or order rows based on a value in a json object.

off topic: If Meteor.js implements PostgreSQL 9.4 I would seriously consider using it again. That and maybe make DDP scalable.

tomiko_nakamura4• 12 years ago

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...).

davidgerard• 12 years ago

Postgres is about to be the new hotness. I mentioned to our hosting provider that we were looking into moving our in-house Oracle and MySQL to Postgres (off Oracle because it's expensive, off MySQL 'cos it's shit) and he said more than a few customers were looking into this precise move.

We're just getting into PG now, and it's just really nice to set up and use. I really wish more web stuff properly supported PG and didn't pretty much require MySQL.

ageyfman• 12 years ago

we currently use 9.4 beta, and it's been rock solid for us. We chose it because of the jsonb data type. JSONB has been a great fit for the type of work that we needed it to do.

taspeotis• 12 years ago

It looks like PostgreSQL is on track to slowly succeed MySQL as the de-facto open source database.

Microsoft tentatively seems to be settling on them as the preferred RDBMS for non-Windows platforms [1]:

Within ASP.NET 5 our primary focus is on SQL Server, and then PostgreSQL to support the standard Mac/Linux environment.

I use EF+SQL Server and they're very much complementary and provide an excellent developer experience. NHibernate+SQL Server is woeful unless you want to use the loosely-typed Criteria stuff. NH's LINQ provider is terrible and it gets confused at the drop of a hat (call Distinct and then OrderBy? "I'm sorry Dave, I'm afraid I can't do that"). At this point I'm convinced only MS know how to write LINQ providers that won't fall over the moment you try to do something useful with them.

Microsoft writing a LINQ provider for PgSql is a great thing for running .NET code on non-Windows platforms.

[1] http://blogs.msdn.com/b/adonet/archive/2014/12/02/ef7-priori...

zapov2• 12 years ago

At this point I'm convinced only MS know how to write LINQ providers that won't fall over the moment you try to do something useful with them.

I would argue Revenj + Postgres provide much better developer experience. But as you said, it's not written by Microsoft so that attitude doesn't help it out.

I'm 100% sure Microsoft can't write LINQ provider which actually understands Postgres and can use it to the fullest (as Revenj can).

dodyg3• 12 years ago

The problem with Revenj is that it seems to tied to dsl-platform, which looks like an online service. People would rather to have their compiler tools with them. You don't want your code investments to go 'poof' should the online service fails.

dodyg3• 12 years ago

LLBLGen Pro has an excellent LINQ provider as well.

otis_inf2• 12 years ago

Thanks, dodyg :)

otis_inf2• 12 years ago

Microsoft writing a LINQ provider for PgSql is a great thing for running .NET code on non-Windows platforms.

I've heard nothing of this kind with respect to npgsql, which is the current .NET provider for PostgreSql. Microsoft has never spent a minute on EF support in Npgsql, so that they bother now is a first. The thing is that to support EF in npgsql (or any other ADO.NET provider), the ADO.NET provider has to contain a command interpreter which interprets the command trees coming from EF's linq provider, and which are then to be used to create SQL statements. This isn't simple at all, and as the command trees change with EF7, it will be a struggle for MS to get a lot of ADO.NET providers support EF7 at the start.

Microsoft's only great linq provider is the one in Linq to Sql: it is able to handle a tremendous amount of edge cases. The thing with linq providers is that a general linq provider gets you only that far: a tremendous amount of cases are 'special cases' which have to get their own path to get from the expression-tree to specific sql. e.g.: ctx.A.Select(a=>a.B.Cs);. This gives a set of sets of C instances. To do this, you have to know at the materialization side which C rows belong to which set (as you have to group them by B, which isn't in the projection). Linq to Sql has a specific piece of code for this, it produces a specifically grafted SQL query which contains an extra column so the materializer can know which C rows to group together. EF doesn't, it obtains a big joined soup.

Irony is that EF7's linq provider will be built on Relinq, which is also the base of NHibernate's linq provider, and they didn't re-use the Linq to sql linq provider, which is kind of odd, considering linq to sql's is pretty db agnostic.

Writing a linq provider isn't simple btw. It took me a full year full time to write the one for LLBLGen Pro.

hype_this• 12 years ago

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.

forgottenpass• 12 years ago

Just because you haven't used it doesn't put the adoption of postgres on the same level as a still experimental programming language. It's a solid open source solution in the run of the mill domain of RDBMS. It has a weird level of hype these days in certain pockets of the internet as mysql's hayday is long gone, and the whole "no sql" fervor is gone and now understood to have been oversold.

I've worked on projects that were using it a decade ago. To this day at a different job I'm still building on it. Shipping it our products to customers, building internal tools on it, and we've have purchased a handful of self-hosted applications that have happened to run on it.

You're generalizing from your own experience and assuming that's everyone's experience. Availability of dump and load tools to move from mysql don't really that much. MySQL isn't poison just because there is a new hotness. Keeping old shit on mysql, and using postgres for new things is a much more common migration strategy.

lucian19002• 12 years ago

Both Postgres and Rust are grounded in solid, desirable concepts. That's why people like them.

Of course, they are on two sides of the maturity spectrum. Postgres is used by lots of people and we're happy it works so well. Rust is not even released yet, but looks very promising.

paulryanrogers• 12 years ago

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.

tomiko_nakamura4• 12 years ago

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.

nonuby• 12 years ago

Whats a good place to suggest a postgresql [json] improvement, my message was intercepted when posting to pg-performance. A major one at the moment is that offset does the select projection on discarded rows (common to use offset in paging), under normal circumstances this isn't a problem, however when does a json operation such as reading a field ->> this causes major performance degrading. Note of course only immutable functions can this optimization apply. There are several workarounds but if PostgreSQL wants to win back some nosql heads it should be straight forward.

In addition to update a json field isn't straight forward, these operations should be supported by first class inbuilt functions.

Its getting close but its not quite a nosql killer yet if they are targetting people who didn't originally come from rdbms background..

PostgreSQL 9.4 Released

Related Stories