Hipsters hacking on PostgreSQL With the rise of NoSQL, it's easy to assume that old-school relational databases are simply living out their dinosaur dreams for legacy applications. But a funny thing happened on the way to the SQL cemetery: PostgreSQL became cool again. Yes, PostegreSQL. PostgreSQL was developed in 1986 by Michael Stonebraker. It was meant …
I've been a PostgreSQL user since 1998 or so and have a number of databases running off it. The current version, 9.1x supports streaming replication very well along with other solutions like slony. The PostgreSQL team loves 3rd part integration too, so there are options there too. It has proven to be absolutely rock-solid over the years and it is about time people noticed.
Another person also using it since 1998. I don't think PostgreSQL ever tried to be hip, cool or popular - it just wanted to "do the job" and do it well.
SQLite is another arrow in the quiver, but for the most part it's still PostgreSQL for me.
(Surely everyone remembers the never ending mySQL vs PostgreSQL cluster-fest-fights on Slashdot every second month?)
... I'm quite annoyed with SQLite. Don't get me wrong, it's quite a neat little toy and it's not the code that annoys me (much; the lacksadaisical type support makes it indeed but a useful toy). No, it's the developers that like it so much they'll stick it in anywhere just because.
Guys, if you have a legitimate reason, like really requiring ACID, having usefully structured data, and not wanting to reinvent the wheel, fine, sure, go for it. But do be honest. If all you really need is, say, a key/value store, there's even faster, neater, cuter and smaller things Out There you could be linking your program against.
SQLite is threatening to become a buzzword itself. That gives rise to claims like "uses SQLite!!1!" as if that obviously makes your crappy bloated app better. We can do with less of that, lest someone'll insist to add xml support to it so any programs using it will be doubly buzzword compliant or something. Quit it already, sheesh.
Unfortunately, MySQL gained the early momentum - possibly because it may have been faster at reading large datasets in the early days. - but for my money Postgresql has always been a solid database.
A key part for me is that the documentation for PG is elegant and well written - MySQL's is pretty bloated.
MySQL has the benefit of being a stupidly easy to set up glorified file access method; just the ticket for backing a website or something. That kind of braindead mostly-read querying it does very well indeed. It's just that as soon as you need Real Guarantees it drops the ball. You can shuffle backends around a bit, but that just makes it more of a hobbyist jumble. Then again, most people don't even understand what could possibly go wrong, in fact plenty of MySQL developers seem to not care in the least about the corruption lurking in its corner cases. Contrast this with PostgreSQL. I don't know if the difference could be any bigger, but if so it'd have to work at it.
I'll too take pgsql over mysql in almost any circumstance, and where I wouldn't I'd look at some of the NoSQL things first. If I'm going to go without the guarantees and/or structure SQL brings, I might as well take something that's honest about it.
MySQL was faster in the early days
Only with simple queries with few, if any, joins. In addition, it didn't support referential integrity, which ruled it out for a lot of people. Those people tended to be more familiar with commercial databases like Oracle or DB2, so PostgreSQL languished.
MySQL should only be used for data you can afford to lose - the last company I used it at had a 80GB database corrupt itself thanks to bugs in the InnoDB engine. This was a system with a significant proportion of writing going on, and as the replication totally screwed up the hot standby we lost a lot of data. At least is screwed up after the daily backup had been taken, otherwise we'd have lost more than a days worth of data.
" It's just that as soon as you need Real Guarantees it drops the ball. You can shuffle backends around a bit, but that just makes it more of a hobbyist jumble "
This used to be true, maybe a decade and a bit ago, but MySQL has grown up and is a Proper Database now. It is the back end for an awful lot of *very* big systems that deal with huge amounts of traffic. iPlayer, for example, uses MySQL databases, and I've used it for everything from medical systems to telecoms billing.
These days, it's just another database. It has its idiosyncracies, like all complex software, but it's a damned useful tool in a lot of cases.
PostgreSQL is great. Being initially a PHP coder, and taught by a certain Kevin Yank, I naturally tended toward MySQL for my DB hobbyist gear. But that was back in my school years. After going full-time and being put to work on an open-source cross-platform project, I was exposed to all four major databases, and using PostgreSQL alongside MySQL all day long just highlights to you how much useful functionality is/was missing from the latter.
A couple of years ago MySQL stopped being the inferior but familiar solution, and started actively annoying me. I tried using PostgreSQL as my primary platform and never looked back. It's fast, easy to understand, open source, and comes with everything I need. I love it.
I make enough of my own mistakes without worrying what surprises the database might throw at me.
PostgreSQL has always striven to be a ACID compliant, transactional database that implements as much of SQL as possible. It's easy to install, requires minimal configuration and the performance is good. I've had no trouble using it and therefore no reason to contemplate switching to something else.
I started using Postgres around 1995 (this is before PostgreSQL) & by 98 I was one of the developers so I'm a bit biased but whenever I need to do something serious then I always use it & the more recent versions are simply getting better.
Large DB's, no problem. My biggest currently runs around 7+Gig & yet it's lightning fast. I'd dread trying to do that in mysql.
Right now I'm setting up my own OpenStreetMap server covering the UK & guess what, they use Postgresql. From what I read a full earth DB is around 90-100Gb!
It's a pity I ran out of spare time 10 years ago to continue working on it personally but it's still weird that even now I get people emailing me with questions about large objects or JDBC :-(
For what it's worth, 100GB (and thus 7GB) is not really a large database these days - even MySQL should be able to handle a few hundred gigs of data without totally falling over. You need to push into the terabyte range to start separating the real DBMSen from the poseurs.
I think this article clearly illustrates that Matt Assay actually knows nothing about software development, databases and actual usages. In the real world, relational databases are still the norm and NoSQL solutions are virtually non-existent.
People running around in the VC world or hipster communities may think otherwise (as indicated by this article), but those are the facts. For most data storage problems in software development relational databases are just fine and usually a hell of a lot more trustworthy and future-proof than NoSQL solutions.
The two database types are optimised for entirely different purposes. Most NoSQL databases are fantastic for very rapid writes and absolutely hopeless when it comes to complex queries or transactional integrity so they're used primarily for tasks where lots of data needs to be logged very quickly and then analysed later via end-of-day batch jobs e.g. traffic analysis of sites under very heavy load. Journalists don't seem to research anything beyond SQL=database of some kind.
Agreed: it really is an embarrassing article displaying an almost complete lack of knowledge what databases are. Mr Asay might want to reconsider his choice of career, maybe a barista, interior decorator or insurance salesman would suit him better? This way to some clues but he might just be better off perpetuating his ignorance at the fount of all slop.
* relational databases can guarantee data integrity. If you don't have this in an application that has financial transaction then you are being negligent and will fail
* open source database are also available in commercial versions or with commercial support. The Enterprise DB release of Postgres also includes support for Oracle's PL/SQL to make migration easier
* if all you want is really fast writes then switch off constraint checks. Be warned it that you do this at your own risk but it's your database to use it as you wish
Assuming this is a relatively recent version of Postgres, had you turned auto-vacuum off? If not, then you may have needed to configure it to be more aggressive. Whenever I've used Postgres for high volum systems with significant numbers of writes to a handful of rows, I've found a manual vacuum unnecessary as auto-vacuum has been able to keep up. I have had to setup the occasional cron job to manually reindex heavily written tables though.
At work, I built a full text/html/pdf/richtext template engine into Pg as well as some rather cute parrallel processing stuff that is very NDA - its an ideal platform especially with things like windowing functions :-)
A long time ago, we used to be an Ingres and oracle shop - Then CA killed Ingres and Oracle's move from a DB provider to a systems/service provider killed its relationship with us and our clients.
We started using PG when it was little more than "Post Ingres". In those days the code was beta, unofficial and very unstable but showed a lot of promise. Today it is the most stable relational platform I work with.
The authors comparison with mysql is technically a joke and lacks any commercial comparison.
Also he fails to mention the many very powerfull mysql to PG migration toolkits out there include my own
"best of the rest" mix and match jobbie.
And comparing Oracle or PG is no longer even possible.
The last Oracle based system we were involved with was for a SMS/MMS + micropayments vendor and the official bugfix/closure from oracle for daily system crashes on what was supported hardware/os/sofwtare was "just reboot every night". Yep, a 24x7 business critical system had such bad memory leaks it had to be rebooted every night and this was an acceptable solution. Not a workaround while they tried to find out why a "Proven" hardware+os+release was fatally unstable but the fix - closed - end of.
At least with PG, we dont have to reverse engineer the code and patch binaries! And Sun is just as bad at support as the old Oracle.
IMHO platforms like Oracle are commercially a major business risk as support (unless you are tier1) is pretty much non existent these days.
EnterpriseDB is good as a general all round supported platform but if you want to do something special then you have to weigh up the alternatives and given our niche market E-DB has not been the best fit for our client base - yet. Saying that, I only hear good things about them but this article reads like a puff piece written to plug EnterpriseDB.
Has the author recently bought shares and is trying to make a quick buck? From the article it is painfully obvious he has no idea what is it talking about either technically of commercially.
Regarding commercially issues such a sthe cost of sysdamins and the ability to certify and train them are business critical. The "Prosperity" of Pg can be seen in the salaries offered for good PG and Oracle DBAs.
In the UK is seems that PG is now as good salary wise as Oracle which says a lot about its acceptance as a commercially accepted solution.
About 300 lines of Python and reuse of the GNU Mailman lockfile module. But that's for very lightweight key/value tables which need some kind of contention management so a pair of simultaneous updates don't clobber a table. The original motivation was that Python CGI programming was free on the webhost I was using but MySQL cost an additional $5/month/domain. Interestingly GNU Mailman also doesn't seem to require a database for its persistence requirements, probably due to relatively low update frequency having the same advantage of not needing to keep a daemon in server memory all the time, so I was able to borrow the module which did the most difficult part.
Those constraints are now over and I use MySQL for other things on the same server, but my NIH homebrew database solution just seems to work, uses almost zero resource for what it does and has never thrown an error. I don't doubt the need to migrate to PostgreSQL if the transactions I support need more serious scaling, ACID guarantees and rollback etc, but I gather MySQL doesn't do all this stuff so reliably either.
We've been using postgres with RoR (after abandoning MySQL and sqlite for local development) for about 18 months now. It's ace, bulletproof and fast. It does everything we need, on the rare occasions it's not appropriate we tend to use MongoDB.
Well done Mr Stonebaker and cheers for the article el Reg, it's nice to read a bit of history about the tools we're using.
MySQL is fast when using the MyISAM storage engine; but it will constantly lose rows and needs the "repair" command run on a regular basis if there are more than just 500MB of data in a table.
Postgresql has never done this kind of cheating and appeared to be slower to the untrained kids and ignorant MBA "talent" who control $$/€€. Also, MySQL had all these graphical toys for the illiterate, self-trained programmer Proletariat. Professionals were using "create table XXX(....." and do this to the present day. They store that DDL file inside their SVN or git repository and can track all changes to the structure this way.
Deutsche Börse is in the process of transitioning their systems to Linux and Postgresql, because they are fed up with the Oracle antics. It shows how capable this RDBMS really is.
Regarding NoSQL - it might be the right tool to run these Massive Teenager Communications Systems such as Twatter and Fsckbook. 99% of developers will still use the "traditional" RDBMS and SQL, though.
I think you should look into PostgreSQL for other reasons than the vendor.
There are plenty enough of MySQL forks around and given that MySQL is released as GPL it will be picked up by developers when Oracle drops the ball on MySQL.
Apparently I am a PostgreSQL hipster, already since 2004ish, but felt I need to give you good advice why to move away, don't do that for the wrong reason.
... had poisoned whole generation of programmers thinking (incl. me). With no clue we approached everything through relational prism that we studied in university. MySQL was the only OSS alternative that cut enough corners to make relational DBMS fit the problems we tried to solve. For which the relational paradigm wasn't a good fit anyways.
So. then comes along the NoSQL stuff and you find out that you have much better tools than relational for these kinds of problems (simple websites, blogging sites, feed sites, social sites, etc.). Along the way you might see all the other problems that really need realational (ERP systems etc.). But now you need real relational not some half-baked all corners cut MySQL stuff. Yeah that also goes for InnoDB which might have become something if Oracle hadn't freezed it.
Then you try out Postgre (because it's free) and when it works for big workloads (yes it does in skilled hands) then you are hooked. If by chance you then check out Oracle ... it feels like a twelve ton dinosaur ("erm. no boolean type? whaat - implicit commit with DDL?"). If by any miracle you don't have the 1% of projects that need something special - Oracle RAC or Spatial or whatnot. Then Postgre is the most mainstream option available - there just is no other as popular, as functional and as cheap solution for these kinds of problem domains that really require relational DBMS.
PS: been using MySQL (both MyISAM/InnoDB) over 10 years, PostgreSQL over 5 years and Oracle over 5 years.
I have 16 years' Oracle experience and started looking at porting my app from Oracle 8 to PostgreSQL 5 years ago due to Oracle price-gouging. The company got sold before it could be rolled out in production, but I opted for postgres at my new startup, where I run a sharded PostgreSQL farm on a 64-virtual node cluster. We handle a sustained 5,000 transactions per second, each one being a stored procedure call. We use the same architecture as Skype with PL/Proxy, which even gives you map/reduce capabilities with the expressive power of SQL instead of hand-written Java.
PostgreSQL is extremely robust. It scales very well and has top-notch data integrity features. SQL coverage is very high, and its stored-procedure support is the best of any DB (you can use PL/pgSQL, Perl, Python, JavaScript/V8 and even R). Extending the DB with user-defined functions in C for performance is also fairly easy.
MySQL used to perform poorly in heavy write environments, but has improved somewhat since, but PostgreSQL should really be seen as an Oracle alternative. It may lack some of the creature comforts of Oracle, but is orders of magnitude less DBA-intensive to administer. The licensing savings alone are the reason why NTT is funding so much of the development behind PostgreSQL (and half the worldwide community is in Japan). On the minus sides, partitioning requires some assembly, stored procedure programming lacks debuggers, DTrace is required to get the same level of observability as Performance Pack, and single queries are not parallelized so it's not optimal for data warehousing tasks.
Jesus.... I've read The Register for some years, and never seen such a crap. How can a guy who supposedly have some TI knowledge as he writes for The Reg be so ignorant about what is the de facto standard for open-source databases ? Postgres is THE database. It's rock solid, very mature, with a feature set that is better than many commercial databases, it's used worldwide by major mainstream companies and governments (http://www.postgresql.org/about/users/), it's one of the most active developed free software projects, it's supported by major corporations like HP, SUN, Fujitsu and many others (http://www.postgresql.org/about/sponsors/) , It runs on all major operating systems, including Linux, UNIX (AIX, BSD, HP-UX, SGI IRIX, Mac OS X, Solaris, Tru64), and Windows, it supports heavy loads (from the about:"There are active PostgreSQL systems in production environments that manage in excess of 4 terabytes of data"), so... what the heck is this guy talking about ? Where was him in the last 10 years ? kidnapped by aliens I suppose.
I hope to see better articles in The Reg than this.
The Register 
Biting the hand that feeds IT
Copyright. All rights reserved © 1998–2024
