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

cube, rollup are not "bigdata" features

[Anonymous Coward]

If that is so, Oracle is supporting "bigdata" features since 8i
https://docs.oracle.com/cd/F49540_01/DOC/server.815/a68003/rollup_c.htm

Re:cube, rollup are not "bigdata" features

[Anonymous Coward]

Neo, the Oracle's eye can see everything, evidently.
They have been expecting "Big Data" since 1998.
http://www.dba-oracle.com/t_release_dates.htm

Re:cube, rollup are not "bigdata" features

[Anonymous Coward]

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

PostgreSQL is impressive.

[Futurepower(R)]

PostgreSQL is impressive, especially now that software companies are becoming more and more abusive.

Adobe software becomes inoperative if you don't let it contact Adobe every time you start a program.

Microsoft's Software is Malware: Microsoft Windows has a universal back door through which any change whatsoever can be imposed on the users.

Re:PostgreSQL is impressive.

[Anonymous Coward]

Abusive? abusing what exactly?

Re:PostgreSQL is impressive.

[Anonymous Coward]

Don't feed the paranoid-looney trolls. They're trolls just like any other.

And always remember the old phrase about arguing on the internet being analogous to the Special Olympics.

Don't. Feed. The. Trolls.

Re:PostgreSQL is impressive.

[epyT-R]

User's sovereignty over their systems?

Re:PostgreSQL is impressive.

[Anonymous Coward]

Don't feed the ignore-the-man-behind-the-curtain trolls either.

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:PostgreSQL is impressive.

[Art3x]

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.

There's that word again, existential. I still can't figure out what people mean when they use it. I've looked it up, and the formal definitions don't seem to fit the sentence.

Re:PostgreSQL is impressive.

[Anonymous Coward]

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

And this is what it means to fuck the users. To even think you need an army of lawyers to fucking install correctly a damn software boggles the mind. Precisely because companies like Oracle and Microsoft act like the Mafia. Abusiveness is standard in the software industry. From top to bottom. The only difference between Oracle a less software houses is the degree to which they're abusive. But they're still criminals in my book. To be avoided at all costs.

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:PostgreSQL is impressive.

[Anonymous Coward]

There are so many ways people use the term, to the point of being amorphous. But the clearest and most useful way I use it is analogous to how people use terms like "financial" to pertain to "finance". "Existential" pertains to the way a subject (e.g. person) exists.

So if I have a bad day, then that's not inherently existential. If I have a bad day that causes me to call into question my understanding of my entire life, then that *would* be existential.

Re:PostgreSQL is impressive.

[Anonymous Coward]

Try BDR http://2ndquadrant.com/en-us/resources/bdr/

Re:PostgreSQL is impressive.

[Grishnakh]

It's too bad there isn't some way to ban these companies outright. If someone is dumb enough to use a database from a vendor that abuses them this much, they *deserve* to pay through the nose for these audits. Obviously, this behavior isn't a surprise to the customers.

Re:PostgreSQL is impressive.

[Art3x]

Got it. Thanks.

Re:PostgreSQL is impressive.

[phantomfive]

You might also enjoy this comic series, I do.

Re:PostgreSQL is impressive.

[Anonymous Coward]

That's like saying that the woman that chooses to marry the man that will beat her everyday deserves the beating.

That's like saying that the person who chooses to buy a VW car for their clean diesel engines deserves to pay 3 times the tax because they apparently weren't that clean after all.

That's like saying that a person who commutes by bike deserves to get ran over by a speeding car because he knows that some drivers drive so fast that they do notice the cyclists too late and have problems avoiding a collapse.

It's blaming the victims and patting the perpetrators on the back...

Re:PostgreSQL is impressive.

[phantomfive]

That's like saying that the woman that chooses to marry the man that will beat her everyday deserves the beating.

Whether she 'deserves' it or not, she was stupid for marrying him.

Re:PostgreSQL is impressive.

[Anonymous Coward]

The paper is a relation that relates itself to itself or is the relation's relating itself to itself in the relation: The paper is not the relation but is the relation's relating itself to itself.

"What?! What does that even mean?"

I can spend the rest of the day giggling like a little girl whenever I think of this comic

The best part though is the bit at the bottom where they explain it, because I don't get most of the jokes otherwise. Except for Nietzsche, because he is dead.

Re:PostgreSQL is impressive.

[Grishnakh]

I don't see what the problem is with blaming victims.

If the victim *didn't know* they'd become a victim, then *obviously* that's not right.

But if the victim KNOWS they're going to become a victim, and they CHOOSE to become a victim, then please explain why I should feel sorry for them and not blame them.

Your examples are stupid. No normal VW customers knew that VW was cheating on the emissions tests. People commute by bike all the time; no one knows they're going to get hit that day, just like no car driver knows he's going to get into a fiery crash that day. Did you completely miss how these Oracle customers buy these companies' services specifically because they KNOW they'll become victims without them? I'm only proposing banning these companies so that anyone who buys into Oracle KNOWS they'll become a victim, and has no way to avoid it except by not becoming an Oracle customer. If you know a vendor is going to treat you like shit, then it's stupid to continue doing business with that vendor.

Re:PostgreSQL is impressive.

[bytesex]

Yeah, but Oracle simply is *that* good. No, really. I'm an avid postgres user, but Oracle still blows any other database out of the water. With ease.

Re:PostgreSQL is impressive.

[Anonymous Coward]

Oracle still blows

QFT

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

[Anonymous Coward]

Shame about it's inability to run 24/7 without destroying usability with it's shitty vacuum nonsense. I'm also assuming they still haven't worked out how to do real indexed for joins, instead of their pseudo shit of background queries that crawl the full set of tables - defeating the point of joined-indexes, something big box DBs have been doing since the 1980s.

Re: Love PostgreSQL

[Anonymous Coward]

only use apps to web the web apps!

Re:Love PostgreSQL

[Anonymous Coward]

Support for PostgreSQL always seems like an afterthought.

While the LAMP stack is certainly not dead, I've noticed that new endeavors favor PostgreSQL. One example is Rust; there are over twice as many contributors to Rust support of PostgreSQL than there is for MySQL, and development of the former is far more active.

Re:Love PostgreSQL

[dskoll]

Drupal also supports PostgreSQL.

Re: Love PostgreSQL

[Billly Gates]

No all the cool kids use Erlang the real rockstar language

Re: Love PostgreSQL

[CastrTroy]

I wouldn't say that PHP is built for MySQL, but that PHP isn't designed to be database agnostic. If you start doing a new project and you want it to work with MySQL, then you look up the documentation on how you connect to MySQL, you'll start going down the path of using mysqli_ and other database specific stuff. Once you are ready to move on and make it database agnostic, you are too far in, and there's too many changes to made. Unless you had the foresight to make everything database agnostic from the beginning and use something like PDO, you are going to be in for a lot of work to get everything working with a different database.

Compare that with something like .Net where all database objects inherit from a set of standard database objects. Even if you don't think about it from the beginning, 95% of the code that connects to the database will already work with any database you want to connect to. There's still the job of making sure your actual SQL works with all the various database engines, but at least a certain amount of the work is already done for you with respect to connecting to the database, sending queries, and getting results back.

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

[Art3x]

I wouldn't say that PHP is built for MySQL, but that PHP isn't designed to be database agnostic. If you start doing a new project and you want it to work with MySQL, then you look up the documentation on how you connect to MySQL, you'll start going down the path of using mysqli_ and other database specific stuff. Once you are ready to move on and make it database agnostic, you are too far in, and there's too many changes to made. Unless you had the foresight to make everything database agnostic from the beginning and use something like PDO, you are going to be in for a lot of work to get everything working with a different database.

Compare that with something like .Net where all database objects inherit from a set of standard database objects. Even if you don't think about it from the beginning, 95% of the code that connects to the database will already work with any database you want to connect to. There's still the job of making sure your actual SQL works with all the various database engines, but at least a certain amount of the work is already done for you with respect to connecting to the database, sending queries, and getting results back.

How is .NET's way different from using PDO?

Yes, PHP has database-specific functions too, like mysqli_connect. But a PHP programmer should find out about PDO if he does any respectable amount of research before diving in.

Re:Love PostgreSQL

[Anonymous Coward]

But many modules don't. Which bites.

Re:Love PostgreSQL

[AlanObject]

Would anyone care to post links or data comparing the latest MySQL to this new Postgres? I am just about to deploy a new project and this would be the best time to decide. My app uses JPA to access the data base and I don't do anything exotic. So there shouldn't be any coding difference between the two.

Re:Love PostgreSQL

[Anonymous Coward]

Overall, Postgresql has much more SQL functionality (ex. common table expressions, etc) and MySQL has dramatically more "operationality" (ex. SYS and performance schema, etc). MySQL has more mature replication but Postgresql has good replication too. MySQL 5.7 (released 2-3 months ago) performance may likely far exceed Postgresql if using primary keys, otherwise maybe not. Postgresql will likely have multi-threaded partitions processing before MySQL. But the latest MySQL release seems to finally lay down the path for it. MySQL has excellent data durability (maybe the best) but DDL changes are not isolated well. PostgreSQL can rollback DDL changes, mysql can't. Both are being developed at high pace at the moment, so they are hard to peg comparatively. Postgresql is developed voluntarily by individuals and support companies, meanwhile Oracle seems to invest a lot in developing MySQL.

Finally, MySQL is licensed with GPL and PostgreSQL with BSD. That could impact your decision the most. If you distribute closed source software Postgresql is the way to go for free.

I hope this helps.

Re:Love PostgreSQL

[Anonymous Coward]

What are "joined-indexes" or "indexed for joins" or whatever you are referring to? A web search doesn't enlighten me one bit.

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

[CastrTroy]

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.

Re: Love PostgreSQL

[dave420]

If you are not using a library to access your database, you are doing it wrong. It is not 1999 any more. PHP supports PDO (as you mentioned), and there are a bunch of great libraries for using whatever database you want, allowing you to flit from one backend to another as you wish. If one doesn't know about PDO, one should not be writing code which uses a database, as clearly there is a knowledge gap.

Re:Love PostgreSQL

[Anonymous Coward]

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

When I was starting with PostgreSQL after years of MySQL (I touched PHP+MySQL first time when I was like 10 back in 1998 after downloading them with library's 56k, first serious contacts with PostgreSQL were back in 2010 or so), my big issue was that the permissions are a lot more complex to manage. Like there have been times where in default configuration the password authentication has not been enabled for TCP connections (I'm somewhat okay with TCP being disabled by default), so you need to go edit separate config file after making PostgreSQL listen for them.

I don't remember the exact issue I had, but there was some fairly simple ACL operation I had to do every single table and it wouldn't apply to new tables. GRANT has been enhanced on newer versions, so it shouldn't be a problem anymore.

But essentially you need to do a lot more to get PostgreSQL in usable state for many web applications compared to MySQL and that doesn't really help the adoption. Sure, if you know what you are doing, PostgreSQL will give you a lot more tools to get job done (we use both PostgreSQL and MongoDB & I think we are abusing PostgreSQL more than Mongo (including essentially using it as a queue with exclusive locks)).

Re: Love PostgreSQL

[Anonymous Coward]

Because performance is terrible.

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: Love PostgreSQL

[Anonymous Coward]

Current replication is pretty good work postgres. I use an F5 balancer in front of several pgpool instances coordinated by some custom scripts that use etcd, and... Wait, I guess that's not all that easy. Nevermind. :)

Re: Love PostgreSQL

[Anonymous Coward]

I'm interested in learning about your time machine, since you're posting this in 2016 even though autovacuum has been on by default since 2010. My production config db has been running on postgres 24x7 for several years, handling thousands of queries per minute (also 24x7) with a dataset in the tens of GB range and around 100K distinct clients. Not huge, but also not some personal blog site.

Re: Love PostgreSQL

[nullchar]

Totally agree, you cannot use an abstraction/db-agnostic layer if you want specific functionality like in TFA!

Re: Love PostgreSQL

[nullchar]

And back in the day, that was sound advice, but if you want to use DB specific features, like in TFA, then you cannot and should not use an abstraction library.

Is it web scale?

[Anonymous Coward]

If it's not then I won't use it, won't scale.

Re:Is it web scale?

[Anonymous Coward]

If it's not then I won't use it, won't scale.

No. PostgreSQL doesn't really scale. It doesn't do active-active shared-storage clusters. It fakes "clusters" with replication - which emphatically does NOT scale.

For what it does, though? It does it very well and makes MySQL/MariaDB look like what it is - crap.

Re:Is it web scale?

[Tablizer]

I don't get it, there's probably like only 30 companies in the US big enough to need "web scale". Why does everyone talk about it as if the chance of being or becoming one of those big-wigs is quite small?

It's like building a parking garage for all your future beemers when you are still a mom-and-pop shop. Is it an ego thing?

There's usually trade-offs on data tools such that having potential scalability will create here-and-now difficulties when small. I'm skeptical of anybody claiming a free trade-off lunch.

Re:Is it web scale?

[Tablizer]

Correction

Re: Why does everyone talk about it as if the chance of being or becoming one of those big-wigs is quite small?

Rework: Why does everyone talk about it when the chance of being or becoming one of those big-wigs is quite small?

Re: Is it web scale?

[Billly Gates]

Postgesql can't reliably replicate with slony. Mysql makes it the only option for many cases

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

Re:Is it web scale?

[PostPhil]

The term "cluster" in generic SQL terms regards having more than database managed by an SQL DBMS. Merely having more than one database in a PostgreSQL install is a "cluster".

I assume you're referring to "sharding" and conflating it with Oracle's vendor-specific RAC (or something similar) as "clustering"? There's nothing stopping you from using something like pg_shard and/or the CitusDB fork to treat multiple installs as a single logical database. There are other solutions, some open-source and some proprietary, but I don't feel like doing your homework.

Replication is not meant to be the same as sharding. They are not "faking" clustering with replication. Just because you've only searched for replication solutions and not searched for sharding solutions doesn't mean they don't exist.

You're going to need to back up your claims that PostgreSQL doesn't scale. The rest of the world disagrees.

Re:Is it web scale?

[kthreadd]

Well, being able to scale horizontally is still useful even if you're a small player. Just being able to fire up a second database host and automatically scale up is wonderful for the few times you're actually running out of capacity. Being able to scale up with little effort doesn't mean that you have to scale up big.

Re:Is it web scale?

[Tablizer]

May I ask for a realistic scenario?

Re: Is it web scale?

[kthreadd]

You run out of capacity on the database server. You set up another one and now you have two. Problem solved. I see it all the time. They have lots of load balanced web servers handling all the front end traffic, but just one monolithic database server in the back that is holding everything back.

Re:Is it web scale?

[Anonymous Coward]

If it's not then I won't use it, won't scale.

I assume you are referring to this: https://www.youtube.com/watch?v=b2F-DItXtZs

Re: Is it web scale?

[Tablizer]

Can't the traditional RDBMS shops buy a new bigger data disk, copy the data files over, and change the paths in the config? True, you probably have to take the DB down during this, but it doesn't seem like a huge effort.

I'm still not seeing it unless something akin to hot-swap is needed.

Maybe those shops failed to partition the data disk separate from the software disk. That's bad planning, not a bad DB system.
 

Re:Is it web scale?

[phantomfive]

I don't know exactly how big 'web scale' is, but I do know several companies who are not in the fortune 1000 that have petabytes of data.....
If you're trying to keep analytics of everything users do (for advertising, of course), then it can add up quickly.....

Re:Is it web scale?

[QRDeNameland]

I assume you're referring to "sharding" and conflating it with Oracle's vendor-specific RAC (or something similar) as "clustering"?

Don't you know? Shards are the secret ingredient in the web scale sauce. ;)

Re:Is it web scale?

[alienpenguin]

How about this http://www.postgres-xl.org/ and this https://github.com/postgres-x2 ?

Re: Is it web scale?

[Anonymous Coward]

Could be CPU, memory or network capacity as well. Everything runs out eventually.

proprietary vs postgres

[Anonymous Coward]

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?

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

[Anonymous Coward]

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?

The advantage is that you get the luxury of getting assfucked by Oracle's salepeople, Oracle's technical department and maybe Ellison himself. It's a good prospect for you and your company.

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:proprietary vs postgres

[dr.newton]

I hates me some Oracle, but if you want really fast synchronous multi-master relational database clusters and can't live without that, then it's probably the way to go. Assuming you have a couple million bucks lying around. Per year.

If you're starting out now, build your application around async replication like that in Postgres instead of relying on multi-master and use anything but Oracle.

Re:proprietary vs postgres

[Anonymous Coward]

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

These 2 are silly reasons to hate Oracle, really. Regarding the command line, SQL*Plus can quite be a swiss-army knife in the hands of those who know how to use it.

Re:proprietary vs postgres

[Anonymous Coward]

Mod parent -99, Flamebait. You never pass up a chance to slag Oracle on Slashdot, silly!

Re:proprietary vs postgres

[t33jster]

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?

The things to consider are beyond what developers see a RDBMS for, but they include things like DR, management tools, and support (I assume developers see RDBMS as nothing more than a "persistence layer," and if that's offensive to you, then it's not directed at you). If you're paying Oracle (or MS, or IBM, etc) for licenses, its because of the value of the data they're storing is worth the cost of the licenses & support. For what it costs, Postgres is amazingly functional.

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.

Re:proprietary vs postgres

[swilver]

For Oracle you need experts. That says enough about their product.

Re:proprietary vs postgres

[reg]

I tried Oracle many years ago... Nothing seemed to work right. I had all kinds of funky failures, things that would not connect, etc. I assumed it was because I was just skim reading the documentation. So I read everything carefully, reinstalled from scratch, checked every last setting, etc. Some problems resolved themselves because the defaults were all wrong, but still nothing really worked. Eventually I resorted to asking for help, and after some time, discovered that the problem was that my server name started with a "U" and there was a bug for servers starting with letters "T-Z" or something like that! Anyone that can code a bug like that should not be allowed near a computer. I uninstalled and installed postgres - never looked back. I just wish I could get the MS folk to give up in SQL Server now...

Regards,
-Jeremy

Re:proprietary vs postgres

[Anonymous Coward]

I shudder to think WTF you mean by documentation. There's lot's of Secret Knowledge(TM) that you need for Oracle. If it's in something referred to as a Document, it isn't public. In contrast, everything I've ever needed to know about postgres was instantly found in the official manual. It's so well organized I rarely have to perform a search to find something. It's only when I know so little about a topic that I'm looking for the wrong thing that I need to use search. That's how damn good their documentation is. I've been using postgres since the 90's.

Re:proprietary vs postgres

[Big Hairy Ian]

ORACLE - You will never find a more wretched hive of scum and villainy

Re:proprietary vs postgres

[Anonymous Coward]

And my favorite:

- all the days in the fictional year 0. This of course results in that all dates B.C have the wrong day of week :-)

This works:
select to_date('00010101','YYYYMMDD')-1 from dual;
31-DEC-0000

Even if this doesn't:
select to_date('31-DEC-0000','DD-MON-YYYY') from dual;
ERROR at line 1:
ORA-01841 :(full) year must be between -4713 and +9999, and not be 0

Please support MERGE

[WaffleMonster]

What I really would have liked to see is support for merge. MERGE is already supported by all of the platforms I care about. I don't even care about tradeoffs and syntax of 'UPSERT' schemes all I care about is one thing working across the board and MERGE is currently the closest to that.

Re:Please support MERGE

[Anonymous Coward]

> MERGE is already supported by all of the platforms I care about
Well, kind of, if you mean MSSQL. Read this and be sick (I was) https://www.mssqltips.com/sqls...
BTW there are other serious problems with MSSQL. I no longer consider it fit for purpose. I'd love to try postgres sometime.

Re:Please support MERGE

[Anonymous Coward]

What I really would have liked to see is support for merge. MERGE is already supported by all of the platforms I care about. I don't even care about tradeoffs and syntax of 'UPSERT' schemes all I care about is one thing working across the board and MERGE is currently the closest to that.

MERGE doesn't have proper concurrency semantics defined (see this for an example). That's fine if you can do an exclusive table lock, or if you are doing a once-a-day upload at 3 AM. In that case you could do take a table lock in PostgreSQL, do an UPDATE in a CTE and then do an INSERT on any rows that were not successfully updated. That's pretty much all MERGE is. However the PostgreSQL team wanted an implementation that would have properly defined and understandable concurrency semantics.

Re:Please support MERGE

[Anonymous Coward]

I don't see any inherent advantage from the syntax of merge versus the syntax of upsert. Both feel a bit awkward/afterthought.

MERGE requires a semicolon and from memory it is limited to one 'when matched and' clause,so you can't for example

merge into target (using source) on source.id = target.id when matched and target.checksum target.checkum then update when not matched then insert...

Also you have to list the columns about 6 times - in the select statement for the source, in the definition of source, in the update statement (twice) and in the insert statement (twice).

I'm not saying postgres' implementation is any better.

Re:Please support MERGE

[WaffleMonster]

Well, kind of, if you mean MSSQL.

MSSQL and Oracle allow the same merge syntax to be used without modification. The more databases support MERGE the easier it is to support additional platforms.

Read this and be sick (I was)

This article is worthless as far as I'm concerned. There is a long history of errata with virtually every statement. It doesn't mean the statement is buggy or you will ever see any issues in real world usage.

Concurrency arguments are downright foolish and only realizable when you make the mistake of assuming a model stronger than MVCC. People who think it is rational to plant read locks or otherwise believe the data they read out EVER under ANY circumstance means anything in relation to subsequent changes regardless of transactional context are the problem not the syntax.

Trigger argument is equally worthless because trigger execution is managed by the database and calls are routinely coalesced to the transaction level for performance not the statement level as many blindly assume. This is merely a reflection of the ignorance of the author.

BTW there are other serious problems with MSSQL. I no longer consider it fit for purpose. I'd love to try postgres sometime.

To me personally MERGE syntax has value because it makes it easier to support multiple databases. What people chose to run is not my call, not my business. I don't care about opinions and feelings.

Re:Please support MERGE

[WaffleMonster]

MERGE doesn't have proper concurrency semantics defined (see this for an example).

Not my problem MSSQL users have been brain damaged by the worthless default concurrency model of SQL Server.

If your going to use MERGE or any other statement to insert records based on data you've read then you damn well better have a plan to ensure consistency because it is NOT the databases job to do this for you.

If you are updating data based on data you've read then you damn well better have a plan to ensure consistency because it is NOT the databases job to do this for you.

Read locks are only an acceptable solution in trivial toy systems because they don't scale.

That's fine if you can do an exclusive table lock, or if you are doing a once-a-day upload at 3 AM. In that case you could do take a table lock in PostgreSQL, do an UPDATE in a CTE and then do an INSERT on any rows that were not successfully updated. That's pretty much all MERGE is.

All I care about is cross platform compatibility updating records. Given MERGE allows WHERE clause to be used with UPDATE I do not consider it to be defective in any way.

Re:Please support MERGE

[Anonymous Coward]

original AC here
> There is a long history of errata with virtually every statement
READ THE ARTICLE BEFORE COMMENTING. It's not just forgivable errata. Some is serious wontfix.
> Concurrency arguments are downright foolish...
this is just weird. See my reply to your other comment.

Re:Please support MERGE

[Anonymous Coward]

original AC here
> MSSQL users have been brain damaged by the worthless default concurrency model of SQL Server.
This is important to me. Please provide refs (assuming you actually know what you're talking about).
>...damn well better have a plan to ensure consistency because it is NOT the databases job to do this for you.
Eh? yes it is, modulo your choice of transiso levels. And ditto for your next assertion of this (apparent) stupidity. Justify or retract.
> Read locks are only an acceptable solution in trivial toy systems because they don't scale
ah, you think MVCC is a magic wand, dontcha. Oh dear

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:BRIN index looks useful.

[swilver]

Just wondering, do you have a clustered index on the timestamps already?

Re:BRIN index looks useful.

[Xtifr]

Yeah, early reports described BRIN as "a niche feature", and my reaction was, well, yeah, broadly speaking, but boy that's a huge niche! :)

Re:BRIN index looks useful.

[shocking]

Nope - this has been my 1st time with Postgres/PostGIS and I did the bare minimum to get it running. There's also a GIST index on the location field. I'm logging between 400000-550000 position reports a day.

Love my PostgreSQL, not mySQL

[Anonymous Coward]

Ignorant comment, Anon. PHP is not built for MySQL, but PHP can be used with it, just like PHP can be used with PostgreSQL. PostgreSQL is also free. In fact, freer, since it isn't owned by one of its commercial rivals (Do you think Oracle will ever let MySQL grow so it can challenge Oracle's flagship product?)

I've used both Postgreql and Mysql commercial settings: PostgreSQL is a solid quality software fit for enterprise use. MySQL isn't and we had frequent data corruption and performance problems with it.

Re:Love my PostgreSQL, not mySQL

[JImbob0i0]

(Do you think Oracle will ever let MySQL grow so it can challenge Oracle's flagship product?)

This is where MariaDB comes in of course and unless you really love Oracle the only sane thing to do these days is to use that instead if you *need* MySQL ...

That being said I prefer postgresql myself.

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.

UPSERT is awesome

[Anonymous Coward]

UPSERT is awesome functionality to have when working with data(bases)!

Mod Up

[Anonymous Coward]

Pay attention, grasshoppers. THIS is how you qualify a statement. Without the qualification, I would have written off his statement as mere opinion.