PostgreSQL 9.5 Released 104
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.
cube, rollup are not "bigdata" features (Score:1)
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 (Score:5, Informative)
They are and Oracle has been. Now PostgreSQL does too.
Re:PostgreSQL is impressive. (Score:5, Interesting)
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: (Score:2)
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: (Score:2)
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: (Score:2)
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 driv
Re: (Score:2)
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: (Score:2)
User's sovereignty over their systems?
Re: (Score:3)
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: (Score:2)
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: (Score:3)
There's that word again, existential. I still can't figure out what people mean when they use it.
Existential angst [wikipedia.org]. 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: (Score:2)
Got it. Thanks.
Re: (Score:2)
Love PostgreSQL (Score:3)
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: (Score:3)
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: (Score:3)
Re: (Score:3)
Re:Love PostgreSQL (Score:5, Informative)
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: (Score:3)
Apart from replication and clustering. Then you need to use some really terrible tools to get something approaching reliable.
Re: (Score:2)
Drupal also supports PostgreSQL.
Re: Love PostgreSQL (Score:2)
No all the cool kids use Erlang the real rockstar language [youtu.be]
Re: (Score:1)
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: (Score:1)
Re: Love PostgreSQL (Score:4, Insightful)
Re: Love PostgreSQL (Score:5, Insightful)
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: (Score:2)
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 b
Re: (Score:2)
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: (Score:2)
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: (Score:3)
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.
Re: Love PostgreSQL (Score:1)
Totally agree, you cannot use an abstraction/db-agnostic layer if you want specific functionality like in TFA!
Re: (Score:2)
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 (Score:1)
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.
Re: (Score:3)
"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: (Score:2)
Is it web scale? (Score:1)
If it's not then I won't use it, won't scale.
Re: (Score:1)
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? (Score:2)
Postgesql can't reliably replicate with slony. Mysql makes it the only option for many cases
Re: (Score:3, Informative)
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: (Score:2)
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 propriet
Re: (Score:2)
Don't you know? Shards are the secret ingredient in the web scale sauce. ;)
Re: (Score:1)
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 lunc
Re: (Score:1)
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: (Score:2)
Re: (Score:1)
May I ask for a realistic scenario?
Re: Is it web scale? (Score:2)
Re: (Score:1)
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: (Score:2)
If you're trying to keep analytics of everything users do (for advertising, of course), then it can add up quickly.....
Re: (Score:1)
Re:proprietary vs postgres (Score:5, Interesting)
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: (Score:1)
>> 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: (Score:3)
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: (Score:2)
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: (Score:2)
For Oracle you need experts. That says enough about their product.
Re: (Score:1)
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: (Score:3)
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: (Score:2)
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: (Score:2)
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 (Score:5, Interesting)
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: (Score:2)
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
Re: (Score:2)
Please support MERGE (Score:2)
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: (Score:2)
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 w
Re: (Score:2)
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 accept
BRIN index looks useful. (Score:3)
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: (Score:2)
Just wondering, do you have a clustered index on the timestamps already?
Re: (Score:2)
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.
Re: (Score:2)
Yeah, early reports described BRIN as "a niche feature", and my reaction was, well, yeah, broadly speaking, but boy that's a huge niche! :)
Skip Locked (Score:4, Insightful)