
SQLite vs MySQL vs PostgreSQL - reqres
https://www.digitalocean.com/community/articles/sqlite-vs-mysql-vs-postgresql-a-comparison-of-relational-database-management-systems
======
mnw21cam
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.

~~~
copergi
>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.

~~~
estebank
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.

------
mercurial
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.

~~~
bsg75
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

------
elithrar
One of the biggest benefits of Postgres is WAL-E
([https://github.com/wal-e/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).

~~~
fdr
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.

~~~
elithrar
> 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!)

------
dkoch
"Again, depending on the choice of the database-engine, MySQL can lack certain
features, such as the full-text search."

Both of the two biggest engines (MyISAM, InnoDB) support full-text search. It
was added to InnoDB in MySQL 5.6/Maria 10.

~~~
McGlockenshire
Given all of the odd default behavior and caveats in InnoDB FTS[1], I'd sooner
just continue to use an external search solution, like ElasticSearch.

1: [http://www.mysqlperformanceblog.com/2013/03/04/innodb-
full-t...](http://www.mysqlperformanceblog.com/2013/03/04/innodb-full-text-
search-in-mysql-5-6-part-2-the-queries/)

------
sdesol
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](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.

~~~
Moto7451
File this under "well that's just your random edge case" but on OS X there
have been long standing issues when trying to use 64bit Perl and MySQL:

Circa 2009:
[http://bixsolutions.net/forum/thread-8.html](http://bixsolutions.net/forum/thread-8.html)

Circa 2011:
[https://rt.cpan.org/Public/Bug/Display.html?id=65462](https://rt.cpan.org/Public/Bug/Display.html?id=65462)

Circa 2014:
[https://discussions.apple.com/thread/3932531](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](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.

------
workhere-io
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/](http://postgresapp.com/)

~~~
daliusd
SQLite does not discourage using it for websites
[https://www.sqlite.org/whentouse.html](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.

~~~
workhere-io
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).

~~~
LordIllidan
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.

E.g. Varnish.

~~~
workhere-io
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.

------
lauriswtf
It appears that Postgres is currently more popular among developers than MySQL
[http://www.databasefriends.co/2014/03/favorite-relational-
da...](http://www.databasefriends.co/2014/03/favorite-relational-
database.html)

------
DigitalSea
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.

------
Udo
+1 for MariaDB. I particularly like the new Aria table type which is blazingly
fast - if you enjoy ligh-weight feature sets.

------
danielstocks
Regarding Postgres vs MySQL performance in conjunction with programming
languages, this is kind of interesting:
[http://www.techempower.com/benchmarks/#section=data-r8&hw=i7...](http://www.techempower.com/benchmarks/#section=data-r8&hw=i7&test=db&d=6)

------
agmartin
SQLite can't be tuned for performance? That's news to me. I was pretty sure
that you can do that.

~~~
jbaiter
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.

------
chacham15
Maybe this is a stupid question, but why is it that MySql can write so much
faster than SQLite?

~~~
dvhh
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.

------
nnq
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

------
nathell
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.

~~~
programminggeek
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.

~~~
talideon
PostgreSQL is one of the _last_ things I'd describe as sexy in any way, shape,
or form. It's utterly, uncompromisingly utilitarian.

~~~
srd
Which does make it sexy for those of us who prefer quality to glossy hype.

~~~
twic
It's Colin Firth rather than Hugh Grant.

------
mwhite
For a quite in-depth comparison of various RDBMS, check out
[http://troels.arvin.dk/db/rdbms/](http://troels.arvin.dk/db/rdbms/)

~~~
dragonwriter
In-depth, but quite dated...

------
rpedela
Missing json, smallserial data types for Postgres.

------
mp3geek
Would've been nice to see a table comparison instead of a huge page dump.

------
tambourine_man
Lots of broken links in the article

~~~
ericgoldberg
And a general lack of links throughout. Who doesn't hyperlink their table of
contents? Or call a table of contents a glossary, for that matter...

------
copergi
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.

~~~
forgotAgain
Agreed. I was surprised to see it with a DigitalOcean moniker.

~~~
ky3
Commoditized sourcing for content marketing, aka hiring some fiverr to hurl
some blogshit.

------
WildUtah
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.

~~~
twic
> 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:

    
    
      sudo -i postgres
      initdb
      createuser wildutah
      createdb -O wildutah wildutah
    

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.

~~~
nasalgoat
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".

------
Fasebook
Getting database consultation from a cloud service is probably not the best
idea ever.

