PostgreSQL 9.5 Released

iamvego writes: Later than the typical release cadence, PostgreSQL 9.5 has finally been released, and brings with it a slew of new features including UPSERT functionality, row-level security, and some big data features (CUBE/ROLLUP, join pushdown for foreign data wrappers, TABLESAMPLE, BRIN indexing and more). The previous release had brought about some new JSON functions and operators, but they only queried the data; 9.5 comes with new operators which now allow modification of JSON values, so it no longer has to be manipulated outside of the database. PostgreSQL's wiki has a more detailed overview of the new features.

Comments

Love PostgreSQL

[LWATCDR]

I have always liked it better than MySQL. Just wish more frameworks and CMSs supported it as the primary database. Support for PostgreSQL always seems like an afterthought.

Re:Love PostgreSQL

[Penguinisto]

Agreed, though it's most likely because for the vast majority of what a CMS does with a DB, MySQL is Just Good Enough(tm), which is why most CMS users just plop in a MySQL instance (and then promptly ignore it save for the occasional backups).

Re:Love PostgreSQL

[phantomfive]

That seems to be changing. Django, for example, supports postgres (and all the cool kids use Django)

Re: Love PostgreSQL

[pak9rabid]

Because by default, MySQL does a bunch of stupid shit with your data?

Re:Love PostgreSQL

[radix07]

I have heard that Heroku is actually starting to be a big reason people are starting look at PostgreSQL more as well. It's the default database offered and it is very well implemented. After trying it there and learning a bit about it, I don't see why you wouldn't use PostgreSQL

Re: Love PostgreSQL

[Art3x]

Why not just use MySQL? It's free and PHP is built for it so you can run web applications on it.

How is PHP built for MySQL? PHP isn't built for MySQL. PHP can use MySQL. In the same way, PHP can use PostgreSQL. And in the same way, you run web applications on it.

I've been using PHP and PostgreSQL for dozens of web applications for over a decade.

Re:Love PostgreSQL

[phantomfive]

After trying it there and learning a bit about it, I don't see why you wouldn't use PostgreSQL

PostgreSQL is better than MySQL in basically every way

Re: Love PostgreSQL

[LWATCDR]

"Why not just use MySQL? It's free and PHP is built for it so you can run web applications on it."
1. PostgreSQL is free.
2. PHP works fine with PostgreSQL. PHP is not built for it.
3. You are not a programer in anyway shape or form are you? "so you can run web applications on it" What? BTW lots of "web applications" are not written in PHP. Python, Perl, Node.js and many other languages/environments are used for web programs.

Re:Love PostgreSQL

[dave420]

Apart from replication and clustering. Then you need to use some really terrible tools to get something approaching reliable.

Re: Love PostgreSQL

[Art3x]

You just basically repeated what I said. PDO is the way to go, but it actually takes a little more effort to find that it exists. Why don't they deprecate MySQLi and put a warning on the documentation that you really shouldn't be using MySQL specific functions at all? If you look up tutorials for PHP and MySQL, almost none of them recommend the use of PDO or even mention it.

That hasn't been my experience. Even before PDO was out, I was positively assaulted by articles about database abstraction, including distinctions between abstracting just data access (like PDO, where it uses the same functions no matter the database but you still write SQL) to abstracting the data itself (like Object-Relational Mappers, which keep you from having to write any SQL).

That being said, I'm against writing the app in a way that you can just change out the database back-end. I used to be for it. I used to try to use just the subset of SQL common to most databases. But I got spoiled by PostgreSQL over the years. There's too many useful things that it has and MySQL doesn't. No, I can't one day switch from PostgreSQL to MySQL, but never will I want to and hopefully won't need to.

The use of ORMs just moves the problem. If I don't use an ORM and instead write PostgreSQL-specific code, then I'm locking myself in to one particular database brand. But if I use an ORM, then I'm locking myself into that ORM and that scripting language. I can't one day switch my application from PHP to Python. Not that I would, but I'm more likely to want to move from PHP to Python or Node.js, than to move from PostgreSQL to MySQL or any other database.

I recommend picking either PostgreSQL or SQLite from the get-go and sticking with it. I strenuously suggest PostgreSQL no matter how lite your database needs. But if that's impractical, then I suggest SQLite. Forget the rest.

Re:cube, rollup are not "bigdata" features

[Anonymous Coward]

They are and Oracle has been. Now PostgreSQL does too.

Re:PostgreSQL is impressive.

[Penguinisto]

We use it a *lot* - and it's great for what we use it for.

Now if they can only get active-active working at the same level as an Oracle RAC server? Just maybe we can all listen to the sweet, sweet sound of Larry Ellison howling in existential agony.

Re:PostgreSQL is impressive.

[Anonymous Coward]

Go to Google and put in Oracle licensing. There's dozens of companies that are there just to make sure you set up Oracle correctly and pay enough so you won't be surprised during an audit that "oh you mean if I didn't turn off the default option at install time I owe $30,000 per cpu core for this feature we turned off the next day?" Because Oracle doesn't disable features you don't pay for, they just come back later and check for anything that had ever been enabled for even a single cpu cycle, and bills you the difference.

This isn't a "omg fr33 softwarez ftw!!1!" perspective, this is a "laywer up before you even TALK to an oracle salesperson" perspective.

Re:proprietary vs postgres

[Art3x]

Question:

I like postgres, but I've never used Oracle (or any proprietary DB engine, to be honest), so I was wondering: is there any advantage in using a proprietary database vs using postgres?

No. I've used both PostgreSQL and Oracle. Oracle was worse.

Oracle was harder to install support for on Linux. Oracle had no data type for a mere date, only date and time together. Oracle's command-line tool on Linux was horrible compared to PostgreSQL's psql tool.

Re:proprietary vs postgres

[Tablizer]

It's probably easier to find Oracle experts and documentation because it's more common.

It's kind of a Catch-22 for PosgreSql: you can't get market-share until you get market-share.

Re:proprietary vs postgres

[mrchaotica]

I have never used Oracle, but I can say that MS SQL Server is pretty convenient to work with if you're hooking it to .NET code via LINQ and developing in Visual Studio. I wouldn't necessarily call that an "advantage," though...

Re:PostgreSQL is impressive.

[phantomfive]

There's that word again, existential. I still can't figure out what people mean when they use it.

Existential angst. Might be summarized as, "I don't know what to do," or described poetically as, "The unbearable lightness of being."

How it applies to Larry Ellison, you'll have to figure out yourself. Maybe an implication that his purpose for existing is gone from the world, his life's work is dead.

Re: Is it web scale?

[Anonymous Coward]

slony is the oldest replication option for postgresql but it is also one of the worst.
For a hot standby use the builtin Streaming Binary replication http://www.postgresql.org/docs/current/static/warm-standby.html
For a multi-master setup use BRD http://bdr-project.org/docs/stable/index.html
For a master slave with select queries balancing setup Pgpool-II https://wiki.postgresql.org/wiki/Pgpool-II

BRIN index looks useful.

[shocking]

I'm logging around 500,000 position reports for aircraft each day, which are naturally ordered by time (I also index on the ICAO24 airframe number). A lot of the queries I do involve the timestamps, which of course are only moving in one direction during the course of the day.

Re:proprietary vs postgres

[swilver]

How about it treating empty varchars as NULLs? Good reason to hate it. How about not offering standard data types, like int, long, boolean? Or how about the fact that Oracle still cannot do transactional DDL?

Re:proprietary vs postgres

[swilver]

I've used both in commercial environments. Since we abstract everything away in Java using ORM layers, you hardly notice the "Oracle" underneath, so it's workable.

However, any direct contact with Oracle DB's reveals their ugly roots -- it seems the entire product is something bolted on top of an engine build in the 80's, with all kinds of quirks that nobody expects anymore of a modern DB. Here are some the quirks I hated:

- VARCHAR empty string is treated as NULL by Oracle. No switch to turn it off anywhere.
- Oracle does not do transactional DDL -- that is if I upgrade my database by dropping some tables and moving some data around, and the process fails halfway due to some inconsistency, you're left to sort it out on your own... no rollbacks.
- No database types for 32/64 bit integers, so there's often a mismatch with the type in the database and the type in your programs. No boolean type. No date type.
- Error messages are (were?) often of the kind "duplicate key violated" -- no additional info which key, which row or columns were involved
- Their tools feel like they were build in the 80's -- and the 3rd party tools aren't much better. Both mysql and postgresql have way better tools.
- Doing INSERTs in a Table which happens to have some foreign keys can result in "too many open cursors" -- what cursor? The internal one it created itself to check the foreign key? What do I care? This results in code that has to do COMMITS every 50 rows (or whatever you have configured the db with).
- Schemas, users, databases... it seems to be all the same in Oracle.
- Many settings in Oracle are global for all databases, but many of those settings would be much more useful if they could be set per database

Oracle however seems to have little interest in fixing these things and getting some good will from people that actually have to work with their systems. They only care about selling the big features, never mind that almost nobody needs them.

Try PostgreSQL -- by the time you feel it no longer fits your needs, consult and expert to look at how you do your queries and indices (the #1 cause of bad performance). If it still doesn't fit your needs after that, you might have a reason to try Oracle.

Skip Locked

[djbckr]

I noticed one feature that will be extremely useful for managing queues is the SKIP LOCKED feature. I'm very pleased this made it into the release. I'll be testing performance on this.