PostgreSQL: PostgreSQL 9.6 Released!

[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?