SQLite vs MySQL vs PostgreSQL
digitalocean.com
digitalocean.com
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.
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.
Honest question... Why someone with this usage would use a RDBMS?
Would be a poor choice IMHO.
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.
1/2 :-)
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
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.
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.
(thanks for all your hard work!)
Both of the two biggest engines (MyISAM, InnoDB) support full-text search. It was added to InnoDB in MySQL 5.6/Maria 10.
1: http://www.mysqlperformanceblog.com/2013/03/04/innodb-full-t...
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.
Circa 2009: http://bixsolutions.net/forum/thread-8.html
Circa 2011: https://rt.cpan.org/Public/Bug/Display.html?id=65462
Circa 2014: https://discussions.apple.com/thread/3932531
According to this bug report, it seems to come and go over time:
http://bugs.mysql.com/bug.php?id=61243
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.
In short, use PostgreSQL both for testing and live. I recommend Postgres.app for Mac users: http://postgresapp.com/
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.
You should absolutely understand what you are doing but SQLite is good choice in many cases. Not everyone works on next Facebook.
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).
E.g. Varnish.
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.
* SQLite itself has configurable cache and some other caching options:
a) http://www.sqlite.org/pragma.html#pragma_cache_size
b) https://www.sqlite.org/sharedcache.html
* Node.js has SQLite library with caching option (matter of single line). https://github.com/mapbox/node-sqlite3/wiki/Caching . It might be possible that other languages has something similar as well.
P.S. Here is one of sites running on SQLite http://skaityta.lt/ with some caching.
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.
A more fair comparison would be memory stored database benchmark.
(...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
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.
http://thenextweb.com/insider/2013/07/27/wordpress-now-power... and http://w3techs.com/technologies/details/cm-wordpress/all/all
Let's say their numbers are off by 2 - "only" 9% of public websites on wordpress. That's still a staggering number, and they're all running MySQL.
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.
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.
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.
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.
you misspelled "responsible with data, standards-compliant, concerned with doing things the correct way."
MySQL (and MariaDB) wont let you refer to temporary tables more than once in the same query.
http://dev.mysql.com/doc/refman/5.0/en/temporary-table-probl...
I am not sure that was a very long way when I needed this for an app I was working on.
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.
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.
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.
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.
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.
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.
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:
sudo -i postgres
initdb
createuser wildutah
createdb -O wildutah wildutah
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".