Slashdot Mirror
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.
They are and Oracle has been. Now PostgreSQL does too.
Because by default, MySQL does a bunch of stupid shit with your data?
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.
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
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.
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.
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
"First they came for the slanderers and i said nothing."
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
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.
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.