7 ms·

SQLite vs MySQL vs PostgreSQL

95 points by reqres 13y ago

Fasebook 13y ago
Getting database consultation from a cloud service is probably not the best idea ever.
nathell 13y ago
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.
- JoshGlazebrook 13y ago
  On HN? Yes. Globally? No. 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.
- dvhh 13y ago
  The popularity of something on HN vs popularity in general is not comparable, HN is only a small subset of the general developer population.
  - nerfhammer 13y ago
    I bet most people on HN still use MySQL, they just don't talk about it as much
    - jbeja 13y ago
      I bet that most people that say they don't use MySQL, still use it.
    - wvenable 13y ago
      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.
    - alien3d 13y ago
      i still use mysql.Maybe futured will db2c.
- mgkimsal 13y ago
  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. http://thenextweb.com/insider/2013/07/27/wordpress-now-powers-18-9-of-the-web-has-over-46m-downloads-according-to-founder-matt-mullenweg/#!AB6IF http://thenextweb.com/insider/2013/07/27/wordpress-now-power... and http://w3techs.com/technologies/details/cm-wordpress/all/all 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.
- programminggeek 13y ago
  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.
  - teacup50 13y ago
    > 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.
  - talideon 13y ago
    PostgreSQL is one of the last things I'd describe as sexy in any way, shape, or form. It's utterly, uncompromisingly utilitarian.
    - srd 13y ago
      Which does make it sexy for those of us who prefer quality to glossy hype.
      - twic 13y ago
        It's Colin Firth rather than Hugh Grant.
    - 13y ago
- copergi 13y ago
  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.
- damon_c 13y ago
  Wordpress doesn't work with Postgres out of the box, so that's a big chunk of the internet right there that is using MySQL.
chacham15 13y ago
Maybe this is a stupid question, but why is it that MySql can write so much faster than SQLite?
- dvhh 13y ago
  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.
- morgo 13y ago
  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.
mnw21cam 13y ago
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.
- ajtaylor 13y ago
  > 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.
- copergi 13y ago
  >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 13y ago
    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.
dkoch 13y ago
"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 13y ago
  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-text-search-in-mysql-5-6-part-2-the-queries/ http://www.mysqlperformanceblog.com/2013/03/04/innodb-full-t...
- copergi 13y ago
  Innodb doesn't have fulltext search, it has "fulltext search*". Big difference.
rpedela 13y ago
Missing json, smallserial data types for Postgres.
tambourine_man 13y ago
Lots of broken links in the article
- ericgoldberg 13y ago
  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...
WildUtah 13y ago
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.
- mutation 13y ago
  > 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.
- twic 13y ago
  > 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.
  - collyw 13y ago
    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.
  - 13y ago
elithrar 13y ago
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 13y ago
  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 13y ago
    > 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!)
agmartin 13y ago
SQLite can't be tuned for performance? That's news to me. I was pretty sure that you can do that.
- jbaiter 13y ago
  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.
copergi 13y ago
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.
- davyjones 13y ago
  Seriously. No numbers, no benchmarks. Hearsay sprinkled in. Just another fluff piece without any meat.
- forgotAgain 13y ago
  Agreed. I was surprised to see it with a DigitalOcean moniker.
  - ky3 13y ago
    Commoditized sourcing for content marketing, aka hiring some fiverr to hurl some blogshit.
mwhite 13y ago
For a quite in-depth comparison of various RDBMS, check out http://troels.arvin.dk/db/rdbms/ http://troels.arvin.dk/db/rdbms/
- dragonwriter 13y ago
  In-depth, but quite dated...
workhere-io 13y ago
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/
- olavgg 13y ago
  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 https://www.sqlite.org/lockingv3.html
  - workhere-io 13y ago
    I've addressed this here: https://news.ycombinator.com/item?id=7434651 https://news.ycombinator.com/item?id=7434651
- philtar 13y ago
  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.
- willvarfar 13y ago
  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.
sdesol 13y ago
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 13y ago
  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.
nnq 13y ago
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
mercurial 13y ago
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.
- BugBrother 13y ago
  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?!" 1/2 :-)
- deleted 13y ago
  [deleted]
- bsg75 13y ago
  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
DigitalSea 13y ago
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 13y ago
+1 for MariaDB. I particularly like the new Aria table type which is blazingly fast - if you enjoy ligh-weight feature sets.
lauriswtf 13y ago
It appears that Postgres is currently more popular among developers than MySQL http://www.databasefriends.co/2014/03/favorite-relational-database.html http://www.databasefriends.co/2014/03/favorite-relational-da...
mp3geek 13y ago
Would've been nice to see a table comparison instead of a huge page dump.
danielstocks 13y ago
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&test=db&d=6 http://www.techempower.com/benchmarks/#section=data-r8&hw=i7...

95 points by reqres 13y ago

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

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.

JoshGlazebrook 13y ago
On HN? Yes. Globally? No. 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.
dvhh 13y ago
The popularity of something on HN vs popularity in general is not comparable, HN is only a small subset of the general developer population.
- nerfhammer 13y ago
  I bet most people on HN still use MySQL, they just don't talk about it as much
  - jbeja 13y ago
    I bet that most people that say they don't use MySQL, still use it.
  - wvenable 13y ago
    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.
  - alien3d 13y ago
    i still use mysql.Maybe futured will db2c.
mgkimsal 13y ago
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. http://thenextweb.com/insider/2013/07/27/wordpress-now-powers-18-9-of-the-web-has-over-46m-downloads-according-to-founder-matt-mullenweg/#!AB6IF http://thenextweb.com/insider/2013/07/27/wordpress-now-power... and http://w3techs.com/technologies/details/cm-wordpress/all/all 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.
programminggeek 13y ago
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.
- teacup50 13y ago
  > 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.
- talideon 13y ago
  PostgreSQL is one of the last things I'd describe as sexy in any way, shape, or form. It's utterly, uncompromisingly utilitarian.
  - srd 13y ago
    Which does make it sexy for those of us who prefer quality to glossy hype.
    - twic 13y ago
      It's Colin Firth rather than Hugh Grant.
  - 13y ago
copergi 13y ago
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.
damon_c 13y ago
Wordpress doesn't work with Postgres out of the box, so that's a big chunk of the internet right there that is using MySQL.

chacham15 13y ago

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

dvhh 13y ago
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.
morgo 13y ago
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.

mnw21cam 13y ago

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.

ajtaylor 13y ago
> 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.
copergi 13y ago
>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 13y ago
  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.

dkoch 13y ago

"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 13y ago
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-text-search-in-mysql-5-6-part-2-the-queries/ http://www.mysqlperformanceblog.com/2013/03/04/innodb-full-t...
copergi 13y ago
Innodb doesn't have fulltext search, it has "fulltext search*". Big difference.

rpedela 13y ago

Missing json, smallserial data types for Postgres.

tambourine_man 13y ago

Lots of broken links in the article

ericgoldberg 13y ago
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...

WildUtah 13y ago

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.

mutation 13y ago
> 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.
twic 13y ago
> 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.
- collyw 13y ago
  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.
- 13y ago

elithrar 13y ago

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 13y ago
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 13y ago
  > 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!)

agmartin 13y ago

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

jbaiter 13y ago
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.

copergi 13y ago

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.

davyjones 13y ago
Seriously. No numbers, no benchmarks. Hearsay sprinkled in. Just another fluff piece without any meat.
forgotAgain 13y ago
Agreed. I was surprised to see it with a DigitalOcean moniker.
- ky3 13y ago
  Commoditized sourcing for content marketing, aka hiring some fiverr to hurl some blogshit.

mwhite 13y ago

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

dragonwriter 13y ago
In-depth, but quite dated...

workhere-io 13y ago

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/

olavgg 13y ago
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 https://www.sqlite.org/lockingv3.html
- workhere-io 13y ago
  I've addressed this here: https://news.ycombinator.com/item?id=7434651 https://news.ycombinator.com/item?id=7434651
philtar 13y ago
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.
willvarfar 13y ago
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.

sdesol 13y ago

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 13y ago
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.

nnq 13y ago

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

mercurial 13y ago

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.

BugBrother 13y ago
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?!" 1/2 :-)
deleted 13y ago
[deleted]
bsg75 13y ago
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

DigitalSea 13y ago

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 13y ago

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

lauriswtf 13y ago

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

mp3geek 13y ago

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

danielstocks 13y ago

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&test=db&d=6 http://www.techempower.com/benchmarks/#section=data-r8&hw=i7...