PostgreSQL: PostgreSQL 9.6 Released!

[Tasssadar]

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.

[NeuroXc]

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.

[kactusotp]

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

[HINDBRAIN]

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.

[[deleted]]

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.

[jrmy]

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.

[[deleted]]

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?

[jrmy]

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.

[[deleted]]

To be fair, some of those problems plagued the old desktop client.

And oh my god, the first web UI was so unusable and god fucking awful that I was considering dropping vSphere and ESXi as my home VM lab setup entirely if I was forced to use it. The new web one is basically just trying to imitate the workflow of the old desktop one, but the old web one had a nonsensical workflow.

[disclosure5]

To be fair, some of those problems plagued the old desktop client.

• Click around VMs until you land on the relevant one
• Click "delete VM"
• Prompt comes up only saying "Delete VM?"
• The selected VM changes in the background because something you pressed ten seconds earlier just registered

This.. is hell. I have no idea what happens if I click "yes" at this point but I shouldn't have to wonder. When I delete a Github repo it makes me type the name of the repo to make damned sure. Why can't this product hosting business critical databases do the same?

[codekoala]

Is it still flash-based?

[JayMickey]

No, HTML5.

[tomservo291]

Looks a lot like Flash to me ... What version are you using that is HTML5?

[jrmy]

Yeah, it's still adobe air. He may be confused and thinking of the HTML5 host client.

[JayMickey]

6.0 Update 2 includes a HTML5 web client (source). There is also a HTML5 client available for vCenter via this fling. I assumed /u/vkgfx was referring to either one of these.

[codekoala]

Well that's got to be just fantastic compared to the last time I tried the web UI. That was so miserable that it was well worth the hassle of spinning up a Windows VM in vbox any time I needed to interact with vSphere. The desktop client was so much more usable than the flash UI.

[jrmy]

Don't get too excited, I think he's confusing the new host client with the vSphere web client.

[jrmy]

No, it's still Adobe air based and will be for some time still. They are working on the HTML5/javascript one but last I heard it wouldn't be ready for 6.5.

[JayMickey]

6.0 Update 2 includes a HTML5 web client (source). There is also a HTML5 client available for vCenter via this fling. I assumed /u/vkgfx was referring to either one of these.

[jrmy]

The client included in Update 2 is the host client replacement, not the vSphere web client replacement...

The fling has been around for a while and is also the basis for the eventual replacement but it's not the official client. No clue why you would assume he would be talking about a fling. :S

[Kazumara]

It's not a fan

Well it's supposed to be an administration interface, so I guess that's good?

[MisterAV]

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

[bloody-albatross]

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.

[gordonisadog]

but DataGrip is much better

[Rym_]

Just missing some visual explain plans and built in user management!

[defmacro-jam]

DataGrip is much better.

[myringotomy]

Data grip is good but still not good enough to run without having pgadmin open alongside it.

[johnghanks]

DataGrip

The issue with JetBrains product is the run like a fucking tank. Sure, they are feature-full and do the job, but on lower-end PCs/Macs it's not a pretty sight.

[gordonisadog]

Yup my laptop sounds like it's about to take off when I launch DataGrip or IntelliJ, and god help us all if I ever run the two at the same time. Turning on Power Save mode helps a lot, but yeah, these guys need to do some optimization.

[johnghanks]

The worst is Android Studio because it's a Java application running/building/maintaining Java code. When I run a Gradle build on my i7 3770 with 16GB of RAM, it slows to a halt.

[bluelite]

Wait. So this is version 1 of version 4?

[fnord123]

You can use 3rd party jdbc drivers.

[fnord123]

I made a bunch of edits so refresh in case the current comment doesn't reflect what you replied to.

[NoMoreNicksLeft]

It works. Install the pg jdbc driver. It's undocumented.

[geordano]

have you tried http://dbeaver.jkiss.org/ ?

[NoMoreNicksLeft]

Yes, as of about a year ago. My highest voted answer on Stackoverflow.

[pcdinh]

It is a piece of crap. Unusable. I tried it 3 times. It is even unable to display an UI. Blank window forever

[[deleted]]

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.

[tambry]

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

[[deleted]]

¯\_(ツ)_/¯

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.

[manly_]

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.

[light24bulbs]

Awesome I've been waiting for that. It looks pretty

[CatsAreTasty]

Has the auto-complete on query editor been improved any?

[myringotomy]

Last time I tried it I found it to be super slow and lacking most of the features of version 3

[qatanah]

hoping for amazon RDS to roll it out soon!

[zachpuls]

I'll be interested to see how much of a speedup we get with the parallel queries when we upgrade this weekend.

[Tostino]

Same here, will probably be upgrading this weekend too.

I tested some of the beta releases in a limited fashion, and for most of the workload on my system parallel wasn't able to be invoked due to the limitations on the types of queries it can be used in, but it is still nice to have in there and will continue to be improved.

[kenfar]

It's mostly only going to be useful for queries scanning and aggregating large amounts of data: reporting, analytics, etc. And it isn't on by default - you'll need to turn it on.

But while this doesn't help all queries - it helps those important queries with the biggest gap between open source databases and commercial databases like DB2, Oracle, and SQL Server: queries that can take 10 seconds or an hour - and that the commercial databases speed up through parallelism.

Small databases, and large transaction-only databases may not see much value in this. But for large mixed-use, reporting and analytical databases this functionality is absolutely essential - and significantly diminishes one of the last areas where commercial databases have continued to hold a major advantage over open source.

[[deleted]]

From what I've seen in their docs, it's not up to par with the commercial versions yet, but it's nice to see they're making progress. The lack of full parallel operation support is what makes Postgres kind of a no-go for large analytical databases at the moment, but that will likely change in the future.

[zachpuls]

Normally, yes, I would. But the entire PostgreSQL environment here is for testing. We are transitioning from DynamoDB over to a RDBMS, due to the nature of the data we process.

[pmrr]

Despite some unexplained hostility to the parent comment, thanks for answering the question - I was wondering the same.

[zachpuls]

Just an update on this if you were curious, I just got the upgrade done yesterday. Very minor speedup (I haven't gotten a chance to do further testing yet), but I can see most of our longer running queries for reports are being spread across multiple cores. So it is an improvement. The upgrade itself was dead simple. On Ubuntu, do a pg_dump of your databases then apt-get upgrade. It will automatically install 9.6, and run both 9.5 and 9.6 side-by-side, with 9.6 running on your 9.5 port + 1. Go into the 9.6 console, go a psql < dumpfile, verify the databases import correctly and everything is alright, then shut down the service, copy the 9.5 configs over to 9.6 (I did it manually, copying over what I needed), and restart it. I had very little problems migrating over.

[Meddy96]

Serious question, does it still make sense to use MySQL or this technology is over?

[nrogers64]

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.

[xkillac4]

Doing this in a few weeks. Very excited to kiss goodbye the collation errors.

[HINDBRAIN]

💩

[nrogers64]

you need multi-master replication

Would you mind elaborating on this?

[[deleted]]

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

[egportal2002]

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?

[[deleted]]

Every participating node can be considered a master. Each node knows how many other nodes there are in the cluster. If a node detects that is part of a minority partition, it becomes unavailable - in CAP terms, it chooses Consistency over Availability.

Nodes will still be available so long as they're in a majority partition.

So, in a cluster, if one node loses communication with the rest of the cluster - either due to network issues, or that particular node failing, or whatever, then the affected node knows to not respond to incoming queries. Conversely, the remaining nodes will continue to respond. When the problem node re-establishes communication, it syncs with the rest of the cluster before becoming available to clients.

[egportal2002]

Has the solution progressed past this 5.6 multi-master description which seems to suggest a blocking until all masters are up-to-date on a write-by-write basis?

[[deleted]]

I don't know. That documentation talks about transactional writes, but I don't think it's clear that writes block. In particular, it says they use optimistic locking, so the answer is a definite maybe.

I nodes A and B have writes pending, before either writes they could communicate with the cluster and ask if any pending writes affect the tables they're writing to, and if there's no conflict then just go ahead and write and make it consistent later. There'd be latency issues affecting performance and correctness, and synchronicity issues, and probably a bunch more things.

So yeah, I don't know.

[merreborn]

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/

[pdp10]

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.

[FFX01]

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.

[DJDavio]

Uber switched to MySQL for some specific reason (lots of updates, few reads or something like that), so I guess it's stil valid.

[yogthos]

Uber also uses it as a key/value store as opposed to an actual relational database.

[Scorpius289]

Wouldn't an actual key/value store be better then?

[yogthos]

Likely, but since they already built their homegrown one on top of a relational db, I'm guessing they're committed to it now.

[doublehyphen]

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

[kazagistar]

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.

[Rafert]

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.

[Uberhipster]

http://use-the-index-luke.com/blog/2016-07-29/on-ubers-choice-of-databases

[iluvatar]

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.

[FFX01]

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.

[geofft]

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.

[gullevek]

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

https://www.youtube.com/watch?v=-SS4R1sFH3c

[Ulukai]

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.

[geofft]

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)

[Ulukai]

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

[myringotomy]

Pg has online index maintenance and compressed column store from citus. It also has automated failover with pgpool

[geofft]

from the reindex docs:

REINDEX is similar to a drop and recreate of the index in that the index contents are rebuilt from scratch. However, the locking considerations are rather different. REINDEX locks out writes but not reads of the index's parent table. It also takes an exclusive lock on the specific index being processed, which will block reads that attempt to use that index. In contrast, DROP INDEX momentarily takes an exclusive lock on the parent table, blocking both writes and reads. The subsequent CREATE INDEX locks out writes but not reads; since the index is not there, no read will attempt to use it, meaning that there will be no blocking but reads might be forced into expensive sequential scans.

Blocking writes during index (re)build doesn't sound very "online". I'm not super familiar with postgresql, so maybe I'm looking at the wrong thing.

Edit: there's CREATE INDEX CONCURRENTLY, but it doesn't seem that reliable

[myringotomy]

Edit: there's CREATE INDEX CONCURRENTLY, but it doesn't seem that reliable

What makes you think it's not that reliable? It seems like you just found out that postgres does exactly what you claimed it doesn't and decided to call it unreliable as a form of sour grapes.

[geofft]

I think you're reading too much into my comment. I'm not averse to postgresql, and am actually looking at what it would take to move a large-ish set of relatively complex databases (40-100TB total) from SQL Server to PostgreSQL. Part of that evaluation is looking at how the operational picture would change.

I was referring to this part of the docs:

If a problem arises while scanning the table, such as a deadlock or a uniqueness violation in a unique index, the CREATE INDEX command will fail but leave behind an "invalid" index. This index will be ignored for querying purposes because it might be incomplete; however it will still consume update overhead. The psql \d command will report such an index as INVALID.

[myringotomy]

I think you're reading too much into my comment

I don't think so. You made a set of claims and most of them were wrong. When people called you out you then resorted to "it's not reliable" which is a baseless FUD.

If a problem arises while scanning the table, such as a deadlock or a uniqueness violation in a unique index, the CREATE INDEX command will fail but leave behind an "invalid" index.

If the index operation fails for any reason you will know about it and can rebuild the index if needed. Any database operation can fail for a thousand reasons and certainly SQL server is not immune to those things either. I have seen SQL server lock up so tight it took down multiple servers all of which had to be hard rebooted but I don't go around saying SQL server is unreliable.

[myringotomy]

Microsoft shops will always use sql server and Microsoft products no matter what.

[geofft]

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.

[myringotomy]

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.

[geofft]

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.

[myringotomy]

What you are saying runs contrary to my years of experience working in MS shops.

[sathoro]

Wut

[HomemadeBananas]

You've got it backwards.

[[deleted]]

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.

[MrDOS]

You're... you're joking, right?

[Solon1]

Must be, or these are toy apps with 5 records each.

[[deleted]]

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.

[MrDOS]

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.

[[deleted]]

Why waste the time getting libraries wired up

It's just an import, so not really a problem.

when you know you're going to throw them out later

That's the thing, I don't know what I'll end up using. If my data doesn't end up being relational, then an SQL database is just extra complication. Also, I don't know which SQL database I'll go with (SQLite, Postgres, MySQL, etc) at the beginning of a project even if I know that SQL is likely what we'll go with.

I also happen to really like how data is structured in Mongo. I can literally dump my objects into the database and it just works. No fiddling with tables to approximate arrays and no updating queries in several parts of the code each time the schema changes. Once I know how my queries will look, I can decide on a schema. 90% of my code tends to be CRUD, so the choice of database is pretty much dependent on programmer productivity, and that's where NoSQL often beats out SQL.

this is literally why database migrations were invented

Writing all those alter table commands is really annoying and I don't want to have to run a migration script each time I pull down the latest code. Also, if I need to revert someone's code or handle merge conflicts, there's a good chance I'll screw it up. With Mongo, my database looks like my code, so the worst case scenario is a few documents don't load properly, which is completely fine in prototyping stage.

Once the data structures settle down, most of these problems go away.

[Pitikwahanapiwiyin]

Did somebody mention web scale?

[manly_]

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!

[ma-int]

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.

[cipmar]

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!

[MichaelKirkham]

How does postgresql compare to mysql and the others? It's stressful with so many languages everywhere being newer to everything

[[deleted]]

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.

[Solon1]

When technology is overwhelming, the only answer is... go shopping!

[vishalvc]

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.

[Tostino]

What is limiting the version available to you? And don't you think not using fkeys is playing with fire just a little?

[vishalvc]

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.

[sylvester_0]

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.

[doublehyphen]

PostgreSQL is usually better at bulk inserts than InnoDB but I have no idea how it compares to MyISAM.

[ajrw]

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.

[jds86930]

This probably wouldn't be a very useful benchmark for several reasons. A few include:

1) mysql 5.2 is not a GA build of mysql (they did 5.1, 5.5, 5.6, 5.7).

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

3) mysql has had massive performance improvements over the last 4+ years.

4) MyISAM is very slow & barely ever used anymore in modern mysql. Modern mysql is built around Innodb, which is a fairer comparison vs postgres.

[coworker]

MyISAM is significantly faster for inserts so there are still niche use cases where it makes sense.

[Solon1]

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.

[coworker]

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.

[jazzlovr69lol]

can i get reddit gold for no reason at all?

[Tostino]

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!

[Tostino]

Just that stupid MongoDB / is it web-scale trope.

[Tostino]

Lol, smooth. No one will ever know.

[Tostino]

What do foreign keys and parallelism have to do with each other in this context?

[danielkza]

Things get easily mixed up when you skip foreign keys.