News for hackers. Stuff that matters.
Re:Modern SQL in PostgreSQL by dtech on Sunday February 08, 2015 @06:08PM (#9018966)
MySQL (and thus MariaDB I presume) and SQLite seem to be pretty poor in supporting these "new" features.

SQLite seems logical because it needs to be kept lean for embedding purposes, but do people know why MySQL is lagging behind so much?

Re:Modern SQL in PostgreSQL by jeffdavis on Monday February 09, 2015 @02:44AM (#9020098)
It seems to be a philosophical difference. A lot of people seem to think that the database should only support primitive operations, and the rest should be left to application code.

I am not one of those people -- I think a good database system (like postgres) can make many things dramatically simpler.

Re:Modern SQL in PostgreSQL by gaius on Monday February 09, 2015 @02:56AM (#9020121)
In the 90s the MySQL guys believed that transactions and referential integrity, should be done in the application...
Re:Modern SQL in PostgreSQL by ddorian43 on Monday February 09, 2015 @05:24AM (#9020498)
Maybe you need just key-value access and the rest to be in the app, maybe you need just file access and the rest in app, maybe you need just hd-block-access and the rest in app etc
Re:Modern SQL in PostgreSQL by barrkel on Sunday February 08, 2015 @07:26PM (#9019184)
My experience with PostgreSQL and MySQL is that PostgreSQL supports lots of features, but performance needs a very careful eye - often a neat feature isn't as useful as it seems because optimization isn't good enough. Whereas MySQL tends to be very good at what it can do, and makes it easy to do silly things, like turn off referential integrity in a slow query to boost performance.

It almost feels like a worse-is-better story. As a programmer, PostgreSQL is much better to work with; more tools, better EXPLAIN, more features, more types, more of almost everything. But to use in the heat of battle, it's less clear-cut. PostgreSQL's replication story is complicated. MySQL master-master replication is fairly easy to set up, and if you use a master as a hot failover, it all mostly just works; when the primary site comes back up, it resyncs with the failover. PostgreSQL has a lot of different replication stories - without a strong central narrative, it's hard to gain confidence.

Re:Modern SQL in PostgreSQL by moe on Monday February 09, 2015 @12:01AM (#9019763)
it all mostly just works

Yes, that sums up MySQL pretty well.

And then one day, one of your masters segfaults for no discernible reason. And when you restart it, the replication process (or even the InnoDB recovery) fails to resume with a generic error message that you can't find any useful information about in the mess that MySQL calls "documentation".

That's when you realise that "it all mostly just works" is really not what you want from your database.

Re:Modern SQL in PostgreSQL by ako on Monday February 09, 2015 @01:28AM (#9019943)
I did some tests with Postgres recently to test query optimization. Was pretty impressed, especially where it applied the correct index on an inline view (2nd example): * http://mxblog.eu/link/post/sql-is-about-what-data-you-want-n... * http://mxblog.eu/link/post/impact-of-inline-views-on-query-p...
Re:Modern SQL in PostgreSQL by lgunsch on Monday February 09, 2015 @11:34AM (#9022013)
I've never been with a company that didn't have serious problems with MySQL replication. At least once a month master-master replication breaks horribly and must be manually fixed; often by having to delete and remove data. MySQL replication half-works at best.
Re:Modern SQL in PostgreSQL by barrkel on Monday February 09, 2015 @02:37PM (#9023164)
I look forward to the day that happens at my work then :)

To be clear, our master/master replication is strictly read-only at one site and read-write at the other site, and never read-write simultaneously. We have yet to see issues in production under fairly hefty write load, and we've failed over numerous times, and back again. But it's only been 10 months or so.

Re:Modern SQL in PostgreSQL by noehp on Sunday February 08, 2015 @08:21PM (#9019307)
This doesn't match my experience at all. MySQL performance is highly variable, and it's incredibly easy to write simple queries that have truly abysmal performance.

Postgres on the other hand, has always tended towards making things work well (a step that mysql often skips), and then work quickly thereafter.

A lot of MySQL acolytes say Postgres is slow because, unlike MySQL, it doesn't ship with unsafe defaults. MySQL doesn't just allow you to do dumb things, it starts off with many of those settings as the defaults.

To me, the real problem is that people likebarrkel exist. He doesn't know what he's on about, but he likes MySQL. Most of wheat he wrote is flatly false, but he said it confidently. And he's employed someplace that probably uses MySQL.

MySQL got adoption for two reasons:

1) it used to be easier to install; and

2) it has unsafe defaults that mean if an idiot runs a benchmark, it wins.

That's it. That's how it won market. After that, it was network effects, and nothing else. MySQL is a turd. It requires substantial expertise to use MySQL because it is such an awful and dangerous tool. It slows you down as you get better. But most of the people who use it don't know any better, or (like barrkel) they spew nonsense that is the opposite of reality. So it wins.

Network effects suck.

Re:Modern SQL in PostgreSQL by barrkel on Sunday February 08, 2015 @11:07PM (#9019686)
He doesn't know what he's on about, but he likes MySQL.

You're putting words in my mouth that I didn't say. I don't like MySQL. I prefer PostgreSQL. And I have had a few rough times optimizing some queries in PostgreSQL, whereas I've had fewer such bad times with MySQL, despite using it more often. It's anecdata. Take it for what it's worth.

Time sinks in MySQL have come more from its crappy defaults, from its bizarre error handling (or lack thereof) in bulk imports, and most recently, a regression caused by a null pointer in the warning routine.

If I were working on my own project, I'd probably go with PostgreSQL and figure out the replication story. But I'm not. I do use PostrgeSQL on my personal projects.

(If there was one feature I'd add to PostgreSQL, it would be some means of temporarily and selectively disabling referential integrity. Not deferring it, not removing and readding foreign keys, just disabling. The app I work on does regular 10k-1M+ row bulk inserts, usually into a new table every time (10s of thousands of tables), but sometimes appending to an already 100M+ row table. It would be nice to have referential integrity outside of the bulk inserts, but not pay the cost on bulk insert.)

Re:Modern SQL in PostgreSQL by joevandyk on Monday February 09, 2015 @12:08AM (#9019774)
You can disable all triggers in a session.

   set session_replication_role='replica';
If memory serves me correctly. Foreign keys are maintained by triggers.
Re:Modern SQL in PostgreSQL by random_number on Monday February 09, 2015 @04:35AM (#9020340)
http://stackoverflow.com/a/2681413

SET CONSTRAINTS ALL DEFERRED

Then do your inserts, followed by whatever work needs to be done without referential integrity, in the same transaction.

[dead]
Re:Modern SQL in PostgreSQL by barrkel on Monday February 09, 2015 @01:09PM (#9022611)
>> performance needs a very careful eye

> Clearly implying that performance doesn't require a careful eye on MySQL

This is highly ironic, since you later say:

> you suck at logic

Funny chap!

(My point was that all the extra features in PostgreSQL give you more solution space, but not all of the solution space is suited to your problem. You have to watch out you're using the specific features in a way that pays off. Whereas MySQL is a bit like a hammer; you need to shape your problem like a nail, otherwise you won't be able to hit it at all. But when you do hit it, it mostly works. It's not a precision instrument though.)

>> makes it easy to do silly things, like turn off referential integrity in a slow query to boost performance.

> Which is really misleading, because it ships with most of that stuff off.

MySQL ships with referential integrity turned on - InnoDB is the default back end. Care to explain more?

(Personally, I wouldn't use MyISAM for my phone book, never mind in production.)

Re:Modern SQL in PostgreSQL by noehps on Sunday February 15, 2015 @06:50PM (#9054675)
" InnoDB is the default back end"

This comment reveals that you're utterly and completely ignorant. If you think InnoDB treats your data with respect by default, you've clearly never actually paid close attention to anything.

Re:Modern SQL in PostgreSQL by tkyjonathan on Monday February 09, 2015 @06:40AM (#9020672)
As a MySQL DBA, I can tell you that neither MySQL nor Postgres are slow. MySQL has less features than Postgres and that was intentional. MySQL was supposed to be a basic feature set that serves 90%-95% of websites and has easy to set up replication. MySQL has always had more the mentality of light weight and fast. Postgres is a fine database. It has a lot of features I would like to see in MySQL. However, Postgres is still fighting issues with regular replication while MySQL is now progressing to sync/group-replication.

The features that Postgres already has can be solved in MySQL, but take some work. Sometimes they require me to use temporary tables and pre-calculated summary tables.

MySQL's speed is only variable by the queries that are run against it. MySQL is "simple enough" for developers to write queries for it and in 10%-20% of those cases, those queries could be a bit more optimal or the data model could use some more tweaking. In terms of getting things done, you can do a whole lot before needing someone like me to come along and tweak things.

A performance audit from someone like me every 6-9 months after your company's website has been in use for >3 years can be perfectly fine.

Also the main reason MySQL won out back in the 90s was because they had better documentation and answered questions on their forums faster.

Re:Modern SQL in PostgreSQL by skrebbel on Monday February 09, 2015 @01:12AM (#9019914)
> To me, the real problem is that people likebarrkel exist.

Seriously? you're getting worked up about a database and you conclude that it would be better if certain people didn't exist?

I'd like to remind you that this is HN, not the Linux kernel dev list. For all its flaws, HN still is about civility. You just wished someone out of existence over a database, can you please stop that? Grow up!

Re:Modern SQL in PostgreSQL by 72deluxe on Monday February 09, 2015 @05:19AM (#9020483)
I have noticed that comments here are getting abusive. I got replied to with a post full of profanity for making an observation on GNOME, not taking sides on either cases for the argument.

It makes for an uncomfortable community.

Re:Modern SQL in PostgreSQL by barrkel on Monday February 09, 2015 @06:31PM (#9024510)
He got worse. https://news.ycombinator.com/item?id=9022358

He created that account to "go after" me with a lot of vitriol. It's a bit mysterious though. Why would someone work themselves up into such rage over a database? Normally, you'd explain this as teenage frustration or something. But he's clearly very unhappy, lashing out.

More mystifying than offensive, since it's impossible to take seriously. How can you deal with these people.

Re:Modern SQL in PostgreSQL by 72deluxe on Wednesday February 11, 2015 @06:44AM (#9032340)
It can be quite upsetting though, particularly with the vitriolic posts from complete strangers. If it was from someone I'd known for years I would cope with it better because you can just shrug and agree to disagree, no need for real offensiveness in any situation though.

But from complete strangers who are hammering profanity and abuse into their keyboards as hard as they can, that is quite unnecessary and doesn't make for a nice community. If someone was speaking the things some people on here post to my face, I'd be incredibly offended and they wouldn't be the sort of person you'd want to work with, hang around with or even live next door to. But they don't seem to mind typing it????

These posts rear their head in C++ articles and anything to do with OSX it seems. Really disappointing.

I suppose the best way to deal with it is just detach from it for a while, use another forum, go outside, look at the birds or stroke a cat or something.

There's something very therapeutic about picking up a fluffy cat (I have 4 British Shorthairs, great for fussing) or simply watching sparrows and small birds go about their business in the dust or seed feeders. They continue working without worries, but work hard to survive still and seem happy about it (as far as a bird can be happy). I find it a contrast to us sat in yellow-lit offices with deadlines, stresses, possibly incompetent managers/colleagues and concerns about our existence/paying bills etc.

Re:Modern SQL in PostgreSQL by Gigablah on Sunday February 08, 2015 @10:44PM (#9019633)
Speaking as someone who made the switch from MySQL to Postgres... I'm just happy that I can now trust subqueries and schema updates to not blow up my database. And I don't know how I got by without lateral joins and hstore.
Re:Modern SQL in PostgreSQL by ecopoesis on Sunday February 08, 2015 @09:33PM (#9019492)
I would also say MySQL was adopted by lots of people because for a long time it had a better replication story then Postgres. Easy to setup replication was a pretty killer feature, even if it didn't always quite work as expected.
Re:Modern SQL in PostgreSQL by empthought on Sunday February 08, 2015 @09:45PM (#9019521)
I think "easier replication" and "unsafe defaults" kind of go hand in hand...
Re:Modern SQL in PostgreSQL by laurencerowe on Sunday February 08, 2015 @08:28PM (#9019324)
MySQL also had much cheaper connection setup costs which was a big deal for the CGI and PHP crowds who setup the world at the beginning of every request.
Re:Modern SQL in PostgreSQL by 72deluxe on Monday February 09, 2015 @05:14AM (#9020464)
This is very true. If they used something like pgpool-ii it'd be fine. But that would require more effort for the server administrators.
Re:Modern SQL in PostgreSQL by juliangregorian on Monday February 09, 2015 @04:53AM (#9020399)
Why are you coming down so hard on barrkel, it seems like you essentially agree!?
Re:Modern SQL in PostgreSQL by threeseed on Sunday February 08, 2015 @08:44PM (#9019364)
A simpler and faster product won ? I am truly shocked.

And the issues around "safety" stopped being a concern for most developers a decade ago when the ORM was invented. So this idea that you need "substantial expertise" to use it is simply ridiculous.

Re:Modern SQL in PostgreSQL by weaksauce on Sunday February 08, 2015 @06:29PM (#9019023)
Oracle influence? Postgres is the leader now in mindshare and features for the opensource offerings. If you like MySQL you could check out mariadb
Re:Modern SQL in PostgreSQL by threeseed on Sunday February 08, 2015 @09:07PM (#9019436)
> but do people know why MySQL is lagging behind so much

Because for 99% of the MySQL user base there isn't a need for these features.

Those using MySQL at scale are using them as dumb key-value stores with horizontally sharding. Those who aren't typically are using them with an ORM and so they aren't dealing with the database at the SQL layer.

Everyone else who is manually writing SQL generally was on or moved to Oracle, Teradata, SQL Server, PostgreSQL etc anyway.

Re:Modern SQL in PostgreSQL by tkyjonathan on Monday February 09, 2015 @06:16AM (#9020604)
This whole statement is 99% wrong