I would emphasise the point made about Postgres read performance. If all you are doing is reading single rows from a single table using an index, then yes Postgres is overkill and other solutions may perform better. However, the overhead that Postgres has is due to a remarkably effective query planner, which can be a godsend if you have a complex multi-table join. A few years ago I was working on a project with a large Postgres database that regularly did queries joining 20 tables together, which Postgres was quite happy to accommodate.
Also, that overhead can be suffered only once for many similar queries if you make use of prepared statements, which can bring the performance of simple read queries up.
Also, bulk writes can be sped up through the COPY IN BINARY interface - we were getting bulk load speeds basically limited by the write speed of our RAID array.
In short, I have been very impressed indeed with Postgres. They do it right.
This is the hallmark of Postgres. Either they do it right or they don't do it at all. For a long time, this worked against them in benchmarks because doing it right meant that mysql was faster (due to cutting corners). It pleases me to no end to see the progress the 9.x series has been making regards to performance, ease of replication setup and marketing.
>If all you are doing is reading single rows from a single table using an index, then yes Postgres is overkill and other solutions may perform better
No, not really. Mysql will perform very slightly better if you are doing just simple selects and they are all with a single connection. But it is a pretty small difference, and it goes away if you have anything else happening at all. This notion that postgresql is somehow "overkill" because it isn't broken is silly.
It is overkill if SQLite is enough. I see no reason to use MySQL, but some companies have spent an awful lot of time customizing it to their needs. I do believe that effort would have given more bang for the buck in PostgreSQL.
I'm not sure why this is being upvoted. It's very light on technical content (how can you mention sqlite and not say that its type system is advisory only is beyond me), and provides no added value compared to what you can get out of a quick google search if you wanted to compare these three widely used RDBMS.
I'll note that I usually disagree with these "why is this on HN" posts, but technical posts should have a higher bar than this.
Are not "upvotes" are the only to save articles in HN, short of commenting?
If this is correct, how many upvotes are people upvoting [1] vs saving for reference, or to read comments later - which for the latter I wish there was a save / bookmark in HN option.
[1] ... and hopefully are not aware the content is sketchy
I believe that in any given month there are lots of really shocked blog authors moaning: "Why did HN vote up those notes to myself instead of when I wrote about something I know?!"
One of the biggest benefits of Postgres is WAL-E (https://github.com/wal-e/wal-e), which allows you to perform streaming backups (based on Postgres' WAL) to <location> (i.e. S3, an file server, etc) and also perform base-backups at regular intervals (so you can replay your WAL backups on top of it).
That's very humbling to hear (I've spent, cumulatively, a lot of time maintaining WAL-E), yet log shipping is a feature of many other database systems.
That said, I do write and maintain WAL-E for reasons that go beyond mere vanity or NIH, and not every data base system may have an accompanying tools at even WAL-E's level of investment (kudos to Heroku) given the use case.
I'm glad you like it so much as to cite it as an advantage.
> I'm glad you like it so much as to cite it as an advantage.
It was a big motivation for me moving away from a document DB. As a "weekend dev", I don't have time to configure complex, redundant backup systems. But my customers will be paying to display data on my site, and therefore I need to ensure I'm backing it up as often as possible.
WAL-E to S3 was simple to configure, is cheap to run, and (most importantly!) easy to restore. I rm -rf'ed my postgres data folder after letting WAL-E run for two weeks on my dev box. Restored, restarted in recovery mode and I was back in action without any problems.
MySQL Advantage: "MySQL can be installed very easily"
I've always heard MySQL's popularity had a lot to do with how easy it was to install, but now a days, I really can't see install ease being a factor. The date for the document says February 21, 2014, so they should know about EnterpriseDB and their installer or maybe my idea of easy is different.
In my opinion EnterpriseDB did a really good job with their Postgres installer. They certainly designed it to be easy to integrate with other products, which makes sense, since more Postgres installs means more potential customers.
I was actually taken back by how easy it was to integrate with my products Linux installer. And what makes the installer really nice is, it puts everything in a single directory, which makes all the difference for my product. My product has a Postgres 9 requirement, and I was really worried about losing potential customers who were running Postgres 8 and could not upgrade. But since the install is pretty much self contained, you can happily run Postgres 8 and 9 on the same machine.
If you know Perl and are curious, download my product (http://gitsense.com/download.html) and take a look at the install.pl file. My only complaint with the db installer is, it doesn't provide a way for you to track the install progress. To work around this, I just fork a process which does the db install and I'll print a dot to STDOUT which lets the user know the install is jugging away.
This has been really frustrating especially when trying to get newbies to try Perl. Honestly you'd think they'd have a test for this.
My solution has been to use Postgres instead. Postgress.app is awesome. Installing it on Linux (or OS X from source or via Homebrew) is hardly difficult. It's no more difficult than MySQL in my experience.
Factor in the fact I don't need to fix sloppy heisenbugs from the command line after the install, Postgres is a lot easier to deploy.
If you're making a web app, IMO you shouldn't use SQLite at all - not even for testing. In a live scenario SQLite has problems with multiple users. And in a test scenario SQLite is too forgiving when it comes to types - which means that once you switch to your live database, e.g. PostgreSQL, errors will occur that you hadn't foreseen because PostgreSQL isn't as forgiving as SQLite type-wise.
In short, use PostgreSQL both for testing and live. I recommend Postgres.app for Mac users: http://postgresapp.com/
SQLite does not discourage using it for websites https://www.sqlite.org/whentouse.html . I use it for some low traffic web sites and I don't see any reason why I shouldn't. Benchmarking shows that my website can handle up to 400 req/s - that's not magic but it is far from my needs. As well it is convenient when memory usage of your web app is no more than 100Mb.
You should absolutely understand what you are doing but SQLite is good choice in many cases. Not everyone works on next Facebook.
I understand your point, but the problem with low-traffic websites is that they often suddenly - without warning - become high-traffic websites for a day or two when some larger website links to them. We've all seen it here on HN: Some article gets linked to, the server goes down, and people are forced to see the article on the Google cache.
So my point is this: Why not instead go for a database that was actually made to handle these kinds of situations? True, using PostgreSQL or MySQL won't in itself ensure that you'll survive a slashdotting/HN'ing, but your chances are better. (And obviously, your chances are even better if you use some sort of caching, but that's a different story).
Never used sqlite for websites, but wouldn't this problem be better handled by caching? I.e. completely bypass the database, especially for users who will never log in.
Sure, but you're paying a price in terms of complexity: Many simple sites with PostgreSQL/MySQL can handle being slashdotted without having a cache (unless you're using a heavy CMS such as WordPress/Drupal). If you use SQLite, you might be forced to use Varnish - and there goes the initial idea of simplicity that caused you to use SQLite in the first place.
Having said that, once your site goes truly high-traffic, you'll obviously need some caching. All I'm saying is that PostgreSQL/MySQL can easily handle moderately high traffic if your website is not a heavy CMS.
I have no problems with multiple users and SQLite. A website with 1000 active users should run fine with just SQLite. Maybe you should start reading https://www.sqlite.org/lockingv3.html
I was testing an app I made in Django using sqlite. Virtualenv was complaining about an error. I don't remember what it was but it was completely irrelevant to the DB. Something like couldn't access the virtualenv's python bin or something. After spending days on it, I was like screw it, I'll come back to that error later and started moving the app towards production state. When I switched to postgresql the error resolved itself.
Yes its type bugs have hit me, as python is also dynamic. And I recently reported type bugs for sqlalchemy's Oracle mapping, so you can run I to problems even on the big DBs as there's a lot of software between you and the btree.
However, sqlalchemy is a godsend for testing. Tests that need a complicated server and are hard to run in a heterogenous dev team don't get run. It's much better to pick sqlite than to mock.
Not quite the in-depth article I expected sadly. Very disappointed.
As someone who has used all three extensively, I would say steer clear of SQLite for large-scale applications or a web app in production. I tend to use SQLite for prototyping because it means you don't have to worry about setting up a database, I wouldn't use it for much more than that.
I used to use MySQL quite a bit, and since version 5.5 MySQL has actually been really good in terms of performance and feature-set. Both MySQL and PostgreSQL have their advantages, and as long as you know when to scale and shard your database it's all relative in the end regardless of which of the two you choose, both can be tuned to do the same things.
Absolutely. Turning on Write-Ahead Logging, disabling synchronous writes and putting temporary tables in memory can help tremendously, especially in situations where there are lots of concurrent read/writes.
Mysql can cache to memory and and defer write operation for latter the most used storage engine (MyIsam) seems to be less resilient and have limited feature compared to sqlite, plus SQLite support for transaction bring some latency and is designed to be resistant to corruption due to power loss or unexpected program crash.
A more fair comparison would be memory stored database benchmark.
Row-level locking, mvcc, multiple write threads. Mysql (default Innodb engine) is actually pretty good at io scalability since ~ 5.5 (2010). We have google, percona, Facebook, oracle to thank for this.
why do people keep saying that mysql is easier to install and configure postgres? maybe you can find more results when googling for "mysql install" or something, but it's just a bit different, not harder.
(...now, I don't even understand why mysql was even created in the first place when even back then there were other open source projects already closer to achieving performance on par with the commercial rdbmss...)
EDIT - correction: sorry, swapped two words by mistake and ended up saing the opposite thing
Can the authors somehow back the statement of MySQL's popularity vs. PostgreSQL? If anything, the latter seems more popular, judging from the frequency of its appearances on HN.
PostgreSQL appears more popular on HN because MySQL isn't as sexy, feature laden, and a lot of Rails developers jumped away from MySQL due to some of its flaws. For all non HN users, MySQL is hugely popular, as is PHP. Both are cheap, widely available, and easy to setup on both Windows and Mac.
MySQL is fantastic for probably 90% of what you'd ever think to use it for. MySQL is also a lot easier to find cheap hosting for and has tons of documentation spread throughout the internet. It's worked well for plenty of websites at ridiculously massive scale for a long time and by the time you ever get "to scale", your problems will almost always be with your code, structure, a poorly designed query, or a severe lack of caching long before you run into problems with MySQL.
PHP and MySQL aren't the sexy HN choice, and there are problems with both, but both will get you a very long ways before they are ever your main problem as a startup.
Seriously. Databases should be as reliable as a rock.
At my first startup, I quickly ran afoul of MySQL's silent string truncation behavior as well as its silent invalid dates = 0 error.
Wait, my database is tossing out my data? If my head could have spun around, it would. In no universe should silent truncation ever have been a default behavior. I've avoided MySQL ever since.
> PHP and MySQL aren't the sexy HN choice, and there are problems with both, but both will get you a very long ways before they are ever your main problem as a startup.
This is only true if you don't have your technological sights set particularly high.
We could not implement some of the things we do, reliably, with PHP and MySQL. We'd spend more time battling the tools than we would working on our actual product.
For me it's not about sexiness - there's just no compelling reason to use MySQL over Postgres if I have the choice, apart from ease of availability on cheap hosting platforms.
In terms of actual functionality, MySQL is worse in almost every respect. It's not the den of horror that some will make it out to be, but it's just not as good.
MySQL is okay so long as "database" means "set of Excel sheets on a server". Honestly, that's what most web developers are comfortable with, what most ORMs target, and, thusly, what many, many "databases" are at the end of the day.
But it's worth noting that relational databases were designed with an entirely other use case in mind, one where the database provides far more functionality than "collection of Excel sheets". For these uses MySQL is a shitshow barely worth considering.
>MySQL is fantastic for probably 90% of what you'd ever think to use it for
No, it is tolerable if you don't know any better. That's like saying a gremlin is fantastic for 90% of what you'd ever think to use it for. If you have a choice, and know other cars exist, then you wouldn't think to use it for anything. If you don't know other cars exist, then you can hardly be believed for claiming it is "fantastic". Mysql is a nightmare. It is incredibly crippled, full of limitations that force you to add complexity to your code to work around them, and offers nothing positive to make up for these downsides.
>but both will get you a very long ways before they are ever your main problem as a startup.
Haha, tell that to our SQL server guy who got stuck doing a PHP/mysql project. He didn't get through the first day without coming to me asking "how do you linux people function when your database can't do anything?". He ran into three separate mysql limitations in the first day he was using it.
Although most of my experience is with MySQL, and to a lesser extent Postgres, I spend two years supporting enterprise applications that had either an Oracle or a SQL Server (either 2008R2 or 2012) backend.
I can confirm that in comparison, SQL Server is a developer's dream. My understanding is that the earlier versions of SQL Server kind of sucked, but the new ones are fantastic. The sibling post's point is valid for 2008R2 and earlier - you had to use a ROW_NUMBER() subquery - but that was one of the very few niggles.
I wasn't a fan of Oracle in general - it's comparatively a nightmare to setup and maintain, and in my opinion the SQL syntax is uglier. But from a dev/support standpoint, Oracle's flashback queries is what impressed me - in Oracle you can write something like SELECT * FROM table AS OF TIMESTAMP. So, say, if you accidentally deleted a couple rows and committed the transaction, you could restore them using a flashback query. My impression was that Oracle was more scalable and supported more enterprisey features, but that's not really my area of expertise.
Honestly, if SQL Server wasn't wildly expensive for any real work, it would be my number one choice and my number one recommendation. Guess you can't have everything. And Oracle, of course, is even more expensive. So Postgres it is!
...well, except on my Dreamhost sites, because MySQL is what they have.
Nah, I took it as an opportunity to educate him. He learned that I am not a linux guy, and that there's actually lots of unix operating systems. And then he learned that mysql isn't the linux database, it is just a database and that he should use postgresql instead.
HN is a very loud bunch on some topics, but it's still a tiny number of people. Every wordpress instance out there is running on MySQL. Wordpress runs > 18% of public websites.
Think about the tens of thousands of cheap, shared web hosting services that provide the same Apache+PHP+MySQL setup. Not many of them are providing Postgres as an option.
I still use MySQL but that's because when I started my last big project, Postgres was still iffy in places (replication, etc) and no where near as popular.
I might still use MySQL for future projects because I still have to know it and I already have it in use.
But if I instantly had no legacy to support and starting a new project, I wouldn't hesitate to use Postgres.
Postgresql seems more popular from reading blogs, news sites, etc because it is moving up in popularity while mysql is moving down. So there is more talk about it, even though there is still less usage. All the people switching to postgresql from mysql and talking about how much better it is, all the people hearing that and asking about it, etc. Mysql is still more popular, notice how lots of the typical PHP turdware doesn't even support postgresql for example.
This is just someone who doesn't appear to have much experience with any of the three, repeating "common knowledge" opinions that are largely misguided and/or outdated. If you want to say X is faster or Y has more "security options" (what?) then link to something to demonstrate that.
I love how easy it is to get started with MySQL and historically it's had a great user manual for beginners.
For years, though, I've felt that the small problems where SQLite is useful now cover all the former MySQL ground. Once you exceed the power of SQLite, the features and performance and standards compliance and reliability and flexibility and correctness constraints and transaction execution and ongoing feature development of Postgres overwhelm the use case for MySQL.
> historically it's had a great user manual for beginners.
The quality of Postgres' documentation was the thing that was a big plus for me more than 10 years ago when I started fiddling with databases. MySQL came with one big 1MB+ HTML file, while Postgres had nicely organized multi-HTML docs. Even if you know nothing about databases, my vote goes to Postgres' docs for really good explanations of what to do, and what's going on. Of course, nowadays everything's easy with apt-get and stuff.
> I love how easy it is to get started with MySQL and historically it's had a great user manual for beginners.
s/My/Postgre/
Seriously, on any platform you might reasonably use in the last five years, PostgreSQL is in the package manager, and installation is one command, just like anything else. Some packagings even create an initial database for you; for those which don't, it's just:
I think the PostgreSQL manual is very good. The one weakness that i can see is that there isn't (that i know of) a single-page soup-to-nuts getting started guide for someone simply using PostgreSQL locally. There are blog posts which do the job, but it would be nice to have something official.
I've converted from MySQL on previous project to MongoDB then to PostgreSQL with my current project, and I have to agree that the time it takes to get up and running with Postgres is still a bit behind MySQL.
The user permission system, the file-based authentication, the strange backslash command structure, the "no feedback" nature of the replication solution vs. the status report in MySQL all make Postgres a lot more "newbie unfriendly".
Postgres requires enough fiddling with the basic user, that it can be off putting. MySQL tends to be just installed almost everywhere, and usually doesn't need any configuration.
Also, that overhead can be suffered only once for many similar queries if you make use of prepared statements, which can bring the performance of simple read queries up.
Also, bulk writes can be sped up through the COPY IN BINARY interface - we were getting bulk load speeds basically limited by the write speed of our RAID array.
In short, I have been very impressed indeed with Postgres. They do it right.