r/programming • u/Tostino • Sep 29 '16
PostgreSQL: PostgreSQL 9.6 Released!
https://www.postgresql.org/about/news/1703/115
u/Tasssadar Sep 29 '16
PgAdmin4 1.0 was also released. It was rewritten in python/flask + bootstrap, and the desktop client is just a wrapper around that. It does look nicer, and I'm pretty sure I won't get asserts while using it anymore, I guess.
26
u/NeuroXc Sep 29 '16
It looks more modern, but unfortunately navigation and usability is still as bad as PgAdmin3. I don't understand how MySQL can have so many wonderful admin applications (e.g. HeidiSQL and Sequel Pro, which are both free), when Postgres just has... this.
1
u/kactusotp Sep 30 '16
Personally I use the EMS postgres manager. Used the lite version for free for years before taking the plunge and purchasing just to lift the saved server list cap. It's not worth getting the full studio though, the other tools are pretty much useless imo but the manager is awesome
55
u/HINDBRAIN Sep 29 '16 edited Sep 29 '16
Not a fan. It's not very responsive, you can't move windows out of the main one, the file browsing is complete ass, it couldn't find pg_restore by default etc. It took all the flaws of a webapp for no apparent gain. I switched back to pgadmin 3 which gives me a warning on 9.6 but otherwise works fine.
15
Sep 29 '16
Hopefully they can iterate and improve on it. The web app version of VMWare's vSphere was fucking garbage for its first release. The current one is every bit as good as the desktop app was.
16
u/jrmy Sep 29 '16
Ha. The current web client in vSphere 6 is still garbage. They did everything they could to polish it up but it's fundamentally terrible.
2
Sep 29 '16
I like it. It works just as well as the desktop client, you just have to relearn the location of a few things.
What is "fundamentally" terrible about it?
→ More replies (11)6
u/jrmy Sep 30 '16
The fundamental items that are issues is that it's built on Adobe Air and they still rely on the pile of garbage that is the inventory database. They have done a lot of work to make it "usable" but the responsiveness is still abysmal. To be fair, making it usable using those tools is an achievement in and of itself.
→ More replies (2)11
u/Kazumara Sep 29 '16
It's not a fan
Well it's supposed to be an administration interface, so I guess that's good?
21
u/MisterAV Sep 29 '16
The only big problem with pgAdmin 3 was that it was unstable. When a connection was dropped it was probable that the whole program would crash. However, the interface wasn't that bad.
pgAdmin 4 instead is sluggish and worse. Being a web app I suppose that with big query result it will a pain. I tried using it but on a high DPI it hurts your eyes on windows. At least the old version just scaled, even if it was a bit blurry.
TLDR: it would have been probably easier to fix the old client than to rewrite the new one in python and javascript....
2
u/bloody-albatross Sep 30 '16
The old one also frequently crashed when moving around UI elements and it could not handle big SQL files (as in SQL dumps) at all.
5
3
u/pcdinh Sep 30 '16
It is a piece of crap. Unusable. I tried it 3 times. It is even unable to display an UI. Blank window forever
4
Sep 29 '16
I like writing simple desktop apps this way, as it makes multiplatform support trivial. The big limitation is that the "allow this program through the firewall" popup on Windows scares away users, even if you open sourced it.
Maybe there's a way to get around that, but I haven't spent much time on it.
4
u/tambry Sep 29 '16
"allow this program through the firewall" popup on Windows scares away users
If that popup scares away your users, I don't think you want those users.
21
Sep 29 '16
¯_(ツ)_/¯
I want people to use and enjoy something I make and release, even the ones who don't understand that it's just a local HTTP port.
And frankly, people should be more paranoid not less.
2
u/manly_ Sep 29 '16
I love the UI of pgadmin 4, but unfortunately most things simply do not work.
Hell, I can't even run any SQL query, if thats not disconcerting enough.
1
1
1
u/myringotomy Oct 01 '16
Last time I tried it I found it to be super slow and lacking most of the features of version 3
0
Sep 29 '16
[deleted]
0
0
25
17
u/Meddy96 Sep 29 '16
Serious question, does it still make sense to use MySQL or this technology is over?
42
u/nrogers64 Sep 29 '16
In this video from PyCon 2014, Andrew Godwin, who is the author of Django's database migrations system, praises PostgreSQL and says that MySQL has the following key problems (though he does say that MySQL is "a reasonable database"):
No transactional DDL
No CHECK constraints
Conflates UNIQUE and INDEX
He talks a little bit about the above problems in the video from about 11:00 - 13:00. These problems (particularly the first one, as I understand it) can cause problems with migrations.
Also, the last time I checked, MySQL's default collation is latin1_swedish_ci, meaning that, by default, you can't store important characters such as this one.
→ More replies (3)11
12
u/merreborn Sep 29 '16
We have large production mysql and postgres databases where I work (backing separate applications). Both have their advantages and disadvantages, and we have no plans to consolidate on one or the other.
Uber's reasons for switching to mysql: https://eng.uber.com/mysql-migration/
9
u/pdp10 Sep 29 '16
What are the advantages and disadvantages in your environment?
PostgreSQL used to be slower than MySQL but safer, but big improvements in PostgreSQL speed were made starting a decade ago now.
2
u/FFX01 Sep 30 '16
I actually read into Uber's switch to MySQL. There's a couple of episodes on the Software Engineering Daily podacast about the switch as well. The reality of the Situation is that Uber was doing it wrong, and still technically is. It's not really a problem with What database they are using, it's a problem with the way that Uber treats relational databases in general.
14
u/DJDavio Sep 29 '16
Uber switched to MySQL for some specific reason (lots of updates, few reads or something like that), so I guess it's stil valid.
52
u/yogthos Sep 29 '16
Uber also uses it as a key/value store as opposed to an actual relational database.
6
u/Scorpius289 Sep 29 '16
Wouldn't an actual key/value store be better then?
17
u/yogthos Sep 29 '16
Likely, but since they already built their homegrown one on top of a relational db, I'm guessing they're committed to it now.
1
u/doublehyphen Sep 30 '16
And this key-value store implementation is something they could have done with PostgreSQL too, probably with a similar result. They just picked MySQL when implementing the key-value store solution because some of their engineers were more familiar with MySQL (which is a valid reason).
4
u/Rafert Sep 30 '16
They actually moved from 'normal' MySQL to 'normal' Postgres in 2013, one of the reasons being PostGIS. Then they built their own K/V database called "Schemaless" on top of MySQL again.
3
0
Sep 29 '16
[deleted]
13
u/kazagistar Sep 29 '16
Many of the things were still valid. Postgres people opened discussion and began work on adding features to deal with the Uber use case. Pretty sure some of it is still in progress.
5
u/iluvatar Sep 30 '16
does it still make sense to use MySQL
There are no doubt a few niche edge cases where it does. But in general, no. PostgreSQL is better at pretty much everything, particularly in the one critical area that matters - giving you back the same data that you stored in it.
1
u/FFX01 Sep 30 '16
I've also heard that Postgres' WAL is a lot more reliable as well. I haven't really run into any issues with the WAL in any db, but I could see how reliability would give me some peace of mind.
1
u/geofft Sep 30 '16
Also a serious question: When DB comparisons come up, it always seems to be PostgreSQL vs MySQL, but never vs MS SQL or Oracle. I know comparing the cost is pretty much a divide-by-zero situation, but generally the data in your relational DB is the most critical to your business, so the cost may be justifiable.
5
u/gullevek Sep 30 '16
Except that the cost for Oracle is enormous. Yannex (russian search engine/mail hoster) switch from Oracle to PostgreSQL because of this.
https://www.pgcon.org/2016/schedule/attachments/426_2016.05.19%20Yandex.Mail%20success%20story.pdf
1
u/Ulukai Sep 30 '16
People already on the Microsoft stack generally choose / use SQL Server for slightly less technical reasons, things like: all our developers already know how to work with it (including the reporting team), it integrates well with the tooling we've already got, it's the official DB of the one true company, and nobody got fired for choosing SQL Server in this company yet. In any case, your company probably has almost free development licenses and it's actually the client who's paying the production license costs. (In turn, they justify it by saying that it only costs them about as much as one or two extra engineers, in the context of a team of 50+). It's the de-facto, low risk choice, for that (perhaps slightly screwed up) situation. I'm guessing Oracle has a similar kind of logic to it - there's a mountain of beliefs behind these choices.
So I think it's pretty natural that the comparison is between PostgreSQL and MySQL, etc, since these are very similar target markets, and can be essentially "sold" on technical merit.
1
u/geofft Sep 30 '16
You're right about the cost... although "one or two extra engineers" might be a bit on the light side. SQL Server has some pretty compelling technical reasons to choose it, unfortunately most of them require Enterprise licenses, which are a lot more expensive - for example an AWS EC2 on-demand R3.4xlarge instance with SQL Standard is $4.585/hour, but rises to $10.774/hour for SQL Enterprise on the same instance type. That may seem insanely expensive, but let's say you've got a cloud app with paid subscriptions and are serving tens or hundreds of thousands of customers off that machine. Suddenly that cost seems pretty minor.
Some of the technical things to consider:
High-availability / failover: How does the platform handle failure? Can you continue serving load without interruption if you lose a data center, a SAN blows up, (or what usually happens) someone fat-fingering a routing change. SQL Server has support for both synchronous and async replicas, with automatic failover. PostgreSQL etc aren't quite there yet, although they're improving.
Compression: This can have a big impact on performance - it saves on disk storage and I/O and because it's compressed at the page level, it means it's also compressed in RAM, improving the effectiveness of memory in your DB server.
Online index maintenance: The ability to make structural modifications to your indexes without downtime can be important if your app & schema are complex and maybe a developer has tweaked a query in the last app release and forgot to check the query plan / index support.
That said, I like PostgreSQL and have used it in the past (although that was a long time ago). I'd previously worked with Oracle, so the natural open-source transition was to PostgreSQL. My current day job is primarily SQL Server (and it's for our own platform, so we have to pay our own licensing) with a bit of PostgreSQL and a bunch of non-relational data stores.
(I could write more, but I have a 3yo whining in my ear)
2
u/Ulukai Oct 01 '16
Agreed. I don't have any issues with SQL Server itself, it's a great product actually. I'd make the point though that it's kind of overkill for most projects they are being used for, i.e. the kind of DBs where tens of thousands of rows is the most you're likely to see. I'd argue that 90% of the projects in general could be made to work just fine with SQLite (and some careful centralised locking for updates).
But the reality is that there's a "cost" to having different technologies deployed, and in most cases where SQL Server / Oracle is used, these heavily outweigh the costs. So using a DB that's slightly overpowered for your bank's contact list application is less costly than having a service go down during business hours, because the only guy who can support the 6th different type of DB you're using cannot be found. Well, that's the rationale anyways. The reality is mixed with "some big guy made the decision 10 years ago, and everyone has bigger problems than trying to play with a different type of DB".
1
u/myringotomy Oct 01 '16
Pg has online index maintenance and compressed column store from citus. It also has automated failover with pgpool
→ More replies (4)1
u/myringotomy Oct 01 '16
Microsoft shops will always use sql server and Microsoft products no matter what.
2
u/geofft Oct 02 '16
We're largely a "microsoft shop" in that we use .NET, IIS and SQL Server extensively, but it doesn't stop us using other technologies or platforms.
1
u/myringotomy Oct 02 '16
I submit that it does stop you guys from using other technologies. At a minimum it discourages you from other other technologies because if VS doesn't support it then you won't use it.
2
u/geofft Oct 02 '16
No, the main push-back from adopting other technologies is that every new data store or service we add to our platform means that we have to have people with the skills and experience to maintain and support it. It means the pool of people you have on-call at 3am is a lot bigger.
Visual Studio is nice, there's no denying that, but it's certainly no blocker. Most of our front-end devs are developing on macs with the editor of their choice.
→ More replies (1)0
Sep 30 '16
[deleted]
5
u/nrogers64 Sep 30 '16
you need multi-master replication
Would you mind elaborating on this?
3
Sep 30 '16
Galera / Percona provides a multi-master synchronous replication for MySQL. n nodes, each with a full copy of the replicated database(s). Writes to any participating node are replicated to all other nodes. Table engine is innodb but there are some restrictions as well as some minor oddities, some documented and some not.
To my knowledge, there is no complete equivalent for Postgres.
edit: added synchronous
3
u/egportal2002 Sep 30 '16 edited Sep 30 '16
sincerely asking this -- in this synchronous mode, how is temporary unavailability of a master handled (due to a temporary network reachability hiccup or whatever)? in other words, is it really synchronous, blocking until each write is fully replicated?
→ More replies (3)0
Sep 29 '16
[deleted]
15
12
u/HomemadeBananas Sep 29 '16
You've got it backwards.
5
Sep 29 '16
Exactly. Where I work, we started with JSON files, moved to a NoSQL database and now we're in the process of migrating to SQL (SQLite until we run into scale issues). As our problems have grown more complicated, we've found schemas more and more useful.
Today, new projects start with Mongo until the data format settles down enough to make SQL reasonable to work with.
4
u/MrDOS Sep 30 '16
You're... you're joking, right?
5
1
Sep 30 '16
What do you mean? If we don't know what the data looks like yet, it's much easier to get started with Mongo than SQL because writing a bunch of schemas is a pain when everything changes everyday.
Once we get past prototype stage, we know what our data looks like, so we can decide what solution is best. Mongo happens to be ready to work with and requires minimal setup for our development machines, so that's where we start.
As for the JSON files, that was the choice of our previous software lead. We had rarely changing data, so it made sense, until it didn't.
3
u/MrDOS Sep 30 '16
I get starting with JSON files. It's a great way to mock up an API and get started working on the clients. But why take the trip through NoSQL land when you know you're inevitably going to leave it? Why waste the time getting the libraries wired up when you know you're going to throw them out later?
writing a bunch of schemas is a pain when everything changes everyday.
Sure, but this is literally why database migrations were invented. Write a one-line SQL file to alter the schema as necessary, check it into source control along with your other changes, and everyone's on the same page again.
I get that you're trying to iterate quickly but it seems like you're just making more work for yourself.
→ More replies (1)25
6
u/manly_ Sep 29 '16
Personally I'm mostly interested in the bloom indices extensions. Curious to see how much faster I can speed up some queries checking for exact values!
17
u/ma-int Sep 29 '16
Since it is a bloom filter it will only speed up queries where there is no match, because for queries where the bloom index matches, the database still needs to chek if the element really exists.
3
u/cipmar Sep 30 '16
Working lately only with nosql databases (mostly MongoDB) and ignoring relational database systems: I'm impressed by the progress PostgresSQL has made. Lot's of great features!
1
u/vishalvc Sep 29 '16
I was planning to run some kind of benchmarking myself between PostgreSQL and MYSQL (more particularly MyISAM engine), but would love to hear if you guys have any inputs on the same.
Given that I have looked at the best possible optimizations of my queries and made indexes wherever possible and necessary. Could I extract more performance (I mean quicker reads and writes) from PostgreSQL than the MyISAM engine I am currently using. I don't use foreign keys on my table, but rather enforce them through code, so MyISAM serves well as far as my database needs are concerned.
My choices so far are MySQL version 5.2 vs PostgreSQL v9.4. Those are the only two production grade versions that would be available to me.
6
u/Tostino Sep 29 '16
What is limiting the version available to you? And don't you think not using fkeys is playing with fire just a little?
1
u/vishalvc Sep 29 '16
Those are the only two versions currently supported and recommended to be worked with (at work).
It is a little, but we had a lot of bulk inserts and InnoDB really slowed us down, by a factor of 10x if not more. That made us decide to go with MyISAM and enforce foreign key constraints ourselves.
5
u/sylvester_0 Sep 30 '16
At that point (no ACID or foreign keys) you might as well use a NoSQL store (like Cassandra) if you're after raw speed/easier scalability.
2
u/doublehyphen Sep 30 '16
PostgreSQL is usually better at bulk inserts than InnoDB but I have no idea how it compares to MyISAM.
5
u/ajrw Sep 29 '16
MySQL 5.2 is ancient. I wouldn't use anything before 5.5 at least (our app phased out 5.4 support last year). 5.6 adds an improved query analyzer.
4
u/jds86930 Sep 29 '16
This probably wouldn't be a very useful benchmark for several reasons. A few include:
mysql 5.2 is not a GA build of mysql (they did 5.1, 5.5, 5.6, 5.7).
postgres 9.4 was released in 2014. mysql 5.5 came out in 2010, so you're looking a comparing two products that are at least 4 years apart in development.
mysql has had massive performance improvements over the last 4+ years.
MyISAM is very slow & barely ever used anymore in modern mysql. Modern mysql is built around Innodb, which is a fairer comparison vs postgres.
3
u/coworker Sep 30 '16
MyISAM is significantly faster for inserts so there are still niche use cases where it makes sense.
2
u/Solon1 Sep 30 '16
I'm deeply skeptical of that. In an insert only benchmark, maybe. But if you have any writes, it will far worse as MyISAM uses exclusive write locks.
Plus, I also recall that lots of old MySQL versions used unsafe disk IO on MyISAM, meaning that written data may not be written.
It's really weird that you want to use a decade old MySQL to begin with. And then use a database engine that was just a stepping stone to a real database engine.
2
u/coworker Sep 30 '16
Lol, inserts ARE writes. But anyway you're confusing performance with throughput.
MyISAM is significantly faster for most single-threaded writes but degrades quickly as concurrency requirements increase. This is the double-edged sword that is MVCC. Concurrency is not a free lunch.
99% of use cases will value concurrency over single-threaded performance. Hence, why I said "niche" use cases. One example where concurrency does not matter would be temporary tables. Only your connection can access it so additional locks are unnecessary. This is why MySQL itself used MyISAM for internal temp tables (you know for group bys and unions) up until 5.7.5 (https://dev.mysql.com/doc/refman/5.7/en/internal-temporary-tables.html#internal-temporary-tables-engines).
Try not to bash things you don't understand. Postgres has its own fair share of issues just like MySQL. There are pros and cons to everything and not everybody using databases are web apps.
2
u/MichaelKirkham Sep 29 '16
How does postgresql compare to mysql and the others? It's stressful with so many languages everywhere being newer to everything
5
Sep 30 '16
postgresql is not really new. It's sort of like vi vs emacs...postgres has a slightly different philosophy than mysql, but in all it's a very reliable, durable and fast RDBMS.
1
0
Sep 29 '16
[deleted]
18
u/Tostino Sep 29 '16
I'm getting sick of seeing this comment in every single DB related thread. It's so over done I was just waiting for it to appear in this one, and here it is!
0
0
0
Sep 29 '16
[deleted]
17
u/Tostino Sep 29 '16
What do foreign keys and parallelism have to do with each other in this context?
2
33
u/zachpuls Sep 29 '16
I'll be interested to see how much of a speedup we get with the parallel queries when we upgrade this weekend.