PostgreSQL learns to walk and chew gum The open source PostgreSQL database is about to get query parallelisation, starting with just a few processes, and is looking for crash-test dummies to give it a whirl. Developer Robert Haas blogs that he worked with Amit Kapila over “several years” to get the feature working, and if things go well, he hopes to see the feature …
I'm assuming this is referring to the 4 worker threads under the node? I suppose it's rather inefficient if your rig has a lot of cores. But the devil is in the details when it comes to DB's. I imagine there are only so many tasks that can be made parallel.
That said it's nice to see improvements to PostgreSQL. I've never used it myself but always heard it wasn't as performant as some of it's less feature rich cousins.
"I've never used it myself but always heard it wasn't as performant as some of it's less feature rich cousins."
I went looking for MySQL versus PostgreSQL comparison several years ago and the best observation I came across was that folks from either camp were typically comparing a tuned version of the one they were currently using against an untuned out-of-the-box installation of the other.
There's also the usual compromise between performance and reliability. it doesn't matter so much if the post I am currently writing disappears into the ether as a result of a hardware hiccup somewhere between here and hitting the disk serving the El Reg forum, but it's a different story if I'm ordering swag from an online shop.
Well... yes and no. The thing is, both MySQL and Postgres are free and open source. That creates overlap in their market spaces. In terms of using them, yes, Postgres is more reminiscent of Oracle than MySQL. But when you're setting up a service or starting a project, if you're looking at MySQL you can also look at Postgres.
In early days, Postgres was certainly the more robust and sophisticated database. By far, in fact. However, MySQL with MyISAM ran a lot faster. Which made it the DB of choice for the explosion of web forums and CMSs we got in the late '90s and early 2000's that wanted lots of cheap, fast read capability and really didn't care too much about strict data integrity or sophisticated features. Also MySQL had (and has) less of a learning curve. Neither is especially difficult, but MySQL will make smart guesses about what you mean with lots of forgiving defaults and multiple ways of doing things. You can slap on an autoincrement qualifier to a field and you don't have to understand sequences (no, serial is not quite the same); MySQL will default to making everything case-insensitive (just to be helpful) which can actually trip you up but is a great example of how MySQL takes an approach of "I know what the users will want...". Another great thing is to compare query plans between the two. MySQL will give you a basic summation that lets you look at the output and go: "hey, it's not using an index on that join, let's add one". Postgres will give you all the nuts and bolts and let you go: "hey, I wonder if this would run faster if I to set the from_collapse_limit differently here".
Now skip forward and MySQL has more sophistication than it used to (assuming you're using InnoDB and not MyISAM nowadays). Postgres in turn has improved dramatically in performance. For many cases you can use either. But MySQL still carries that legacy of its initial popularity and still has an easier learning curve.
But an easy learning curve can be a two-edged sword. Taking just that little bit longer to really understand Postgres reaps great benefits, imo. It is now performance-equivalent to MySQL InnoDB at the least and remains the more sophisticated of the two. By choice, I will work with Postgres, though often enough I'm called in to consult on MySQL systems and I'm fine with that.
But TL;DR: I think it's absolutely right to talk about comparisons between MySQL and Postgres. They service a lot of the same potential market.
I think Word vs. Notepad is over-doing the comparison. Not least because Notepad is fine for its purpose and people might take this to mean that Postgres is bloated with unnecessary features. It's not, it's a lean piece of machinery, very elegant, very solid. I would go more with something like Surface Pro vs. iPad Pro. And I'm really not attempting to kick off a fan-war with that analogy. I just think that people will know what I mean. Both great bits of hardware, overlapping somewhat in target market, both with plenty of great features. Just one is that little bit more coherent, capable and planned. The iPad Pro is maybe a little bit easier to pick and go "this is familiar" and start poking things, but after a day or two that advantage is gone but one still has a better kickstand and some extra capabilities. But either might be fine for your needs depending on what those are. "Notepad" is a little unfair, imo. MySQL is fine and very capable. It's just that I think Postgres is the more sophisticated and solid. It's what I would choose to build an important system with.
MySQL with MyISAM ran a lot faster
As does anything if you drop integrity checks… which means replacing them with slower and less reliable application code.
MySQL's habit of applying table locks is a killer in many situations. Postgres has always had better concurrency.
But the biggest reason to avoid MySQL is that it fails big time and joins and encourages people to develop non-normalised schema. Relational DBs are all about joins and, done right, they're fast because they use indexes.
That said, Oracle seems to be doing a good job of cleaning up MySQL's warts.
Actually, it's more the case that MariaDB are doing a good job of cleaning up Oracle's MySQL warts.
Nope, can't agree there. MySQL has become more predictable and reliable under Oracle's stewardship. Many long standing bugs/failures have finally been corrected. I'd still recommend Postgres over MySQL but anyone who thinks that Oracle isn't taking MySQL seriously doesn't understand databases.
"However, MySQL with MyISAM ran a lot faster."
Actually, no, it very much isn't. Even on read-only loads InnoDB has been faster for well over a decade (since early MySQL 5.0 releases).
MyISAM may still have specific niche uses, where features that otherwise make it useless are in fact desirable; I can think of 2 cases where I would use it, and they are both quite obscure and very narrow.
>>>>"However, MySQL with MyISAM ran a lot faster."
>>"Actually, no, it very much isn't. Even on read-only loads InnoDB has been faster for well over a decade (since early MySQL 5.0 releases)."
I think you may have misunderstood my post. I said that MySQL with MyISAM ran faster than Postgres back in the early 2000's and that this is one of the reasons MySQL became the most popular free database.
In the early 2000s MySQL's marketing was better and this helped push the LAMP monstrosity and its related bad practices onto a grateful world.
MySQL was a company that understood the importance of getting on PFY's notebooks, which were mostly running Windows and so they provided a Windows installer. Postgres had to be installed using cygwin: this worked fine but wasn't like to win any friends.
This helped favour MySQL and and encouraged development of PHP/MySQL stacks (with some fucking awful code most of the time). Database speed didn't tend to matter as it would be shot in the foot by the lack of connection pooling within a single page, let alone across sessions. And, oh yeah, there was the whole "parametrised queries, we've heard of them" versus "magic quotes" stuff. Magic quotes still make me want to cry.
I did some performance tests on something I was working on at the time and found no significant read performance advantages with MySQL. But it was faster for inserts – look ma! no triggers – as long as you could live with table-locking. Performance went down the toilet with joins; and a developer's life got harder because it meant manually adding indexes for foreign keys that Postgres ensured you got automatically.
Even if Postgres uses an Oracle alike "language", it's far, far less powerful than Oracle - and DB2 too. I'd suggest you to read Oracle manuals (a daunting tasks, giving how large they are), to understand what Oracle really is and can do -. at absurd costs and sometimes in very complex and unfriendly ways, I agree... - but there are reasons why someone accepts to pay those absurd prices.
For example until Postgres makes partitioning automatic - the current implemantation is awful (manual subtables creations, constraints, triggers), parallel processing is also far less useful. Another big limit of Postgres is in its LOBs management, very outdated too.
Postgres *is* prefectly comparable to MySQL - they play in the same league - and under some aspects less powerful too (under others may be better).
Sure, unless you really need some of the high-end features of Oracle Postgres makes perfect sense - but MySQL also, although porting an Oracle application to Postgres may be a little easier, and MySQL is GPL only for GPL applications.
No matter how you try and give analogies they'll fail.
Here's my one.
MySQL is small and fast but scales up terribly.
PostgresQL is bigger and slower but scales up pretty well.
At 200 million entries (real world case), pgSQL uses less than 1/4 the memory and 1/2 the CPU that MySQL does for the same queries on the same data sets. (MySQL was up to 46Gb on a 48Gb machine, so it kept starting to swap).
That's a massively tuned MySQL vs a more-or-less out of the box pgSQL. pgtune was run later but it made very little difference.
This comes up regularly. I can remember a similar conversation to standardize our databases in the 1980s so that we could use a standard SQL everywhere. We looked at Ingres/Postgres and Oracle - One of the justifications for Oracle instead of PostgreSQL was that the proliferation of PCs would allow us to run Oracle 4 on MS-DOS for single users (Yes, we really did consider it).
Here is something I posted over 5 years ago:-
I think that the dominance of MySQL in its large niche is fairly simple to explain. In its early days MySQL had a native Windows Version (January 1998 for Windows 95 and NT), PostgreSQL did not.MySQL had only been working on their product for only a few years before that. PostgreSQL's origins go back much further (to Ingres) from the 1970s, when it ran on minicomputers (including Unix on DEC). The only way of running PostgreSQL on Windows was with tools like Cygwin, it did not run as a native Windows Server until version 8 in 2005.
To get PostgreSQL running on Windows may have been quite challenging to the Windows based crowd who were starting to dominate the low end of the market when the Y2K and web boom accelerated in the late 1990s. Many of these projects were written and developed on a couple of Windows based PCs by people with little formal IT education. These projects had a couple of tables at most, and many of the people who wrote them had little understanding of the possible advantages of referential integrity - Sometimes all of the "logic" layer was written in the front end.
At this time (with the exception of the low end free Micrsoft SQL Server 7 product that could run under Windows 9x), most relational databases were expensive and required considerable expertise to set up. All of this meant that the "just good enough" MySQL free product dominated. Later many of the people who had this background moved to the easily deployed LAMP environment, so MySQL became even more entrenched.
The Register 
Biting the hand that feeds IT
Copyright. All rights reserved © 1998–2024
