How easy is it to put a lock on a file while writing on it? What happens in an environment where you have multiple frontend servers that need to write to the same file? Are you going to have like one server that does the reading/writing from a file and have like a protocol with which other servers can send commands?
It's really funny when I get asked that question, really:
PostgreSQL is a pain to setup, the SQL parser is very finnicky and for the past times I spent too much time trying to work around every idiosyncrasy of Postgres when I could have a similar MySQL instance running in a shorter time
So, MySQL doesn't waste my time with stupid crap.
Now please call me ignorant and downvote me since PostgreSQL fanboys can't take a critique and think their loved database is perfect.
Not calling you ignorant, but for workloads that a simple, untuned mysql installation is sufficient you can just build yourself a simple, default postgresql installation. Use chef, puppet, terraform,... to make that repeatable and the setup pain goes away.
True, Chef and Puppet kind of take the pain away between different linux distributions (which is good). But this would be aiming at a deployment.
However, one common use case (for me) is having to have a one-off install, which unfortunately kind of prevents that.
If my job needed frequently installs of DBs I would have probably gone this way, but it isn't, so today it's PostgreSQL 9.1 on Mac OS, tomorrow (more likely, a year or two from now) it's MySQL on Fedora, etc
Chef and Puppet can deploy just fine into a development machine - either a VM (i'd recommend this) or even on your host machine. I can't imagine any workload that's simple enough to be handled by a vanilla, untuned mysql instance and at the same time not simple enough to be handled by a scripted postgres installation. Things get different once you're getting to more complex workloads, but the effort of tuning a mysql instance to handle those is in the same ballpark as the effort of tuning a postgres instance.
PostgreSQL setup is as easy as yum install postgresql. You may have to tweak pg_hba.conf to get password authentication in some distros, but this is it setup-wise.
As for the parser being finnicky, that is a feature. Hint: an empty string in a timestamp does not mean midnight 1/1/1970. This is one area where mysql did more harm than good: getting developers hooked in sloppy SQL.
What is an hba file? How am I supposed to know what it does?
PostgreSQL has a "createuser" command. Great, but how am I supposed to know this is a PSQL command? (not to mention potential collision with other filenames)
RTFM? This is an acceptable answer, but it disregards completely the user experience.
MySQL is more intuitive. Authentication? User/password. None of this "ident" or "trust" (really?) schemes. Yes, pg_hba is more flexible, but if you have only one DB user... Yes, it's convenient to have a local "trust" connection. Also a little bit more insecure as well.
And there are convenient mysql* commands, you can tell they are mysql commands.
> What is an hba file? How am I supposed to know what it does?
From the section in the docs titled "the pg_hba.conf file", which explain it quite clearly? [1]
> PostgreSQL has a "createuser" command. Great, but how am I supposed to know this is a PSQL command?
Its not a psql command, its a client application. Which you are expected to know by reading either the documentation section on client applications [2] or the documentation on the administrative function for which that particular application is used [3].
Of course, you don't need the program for the function, you can just use SQL. Which, presumably, you are going to be reading the documentation on for any RDBMS, since even the ones that support the standard the best (e.g., between PostgreSQL and MySQL, PostgreSQL by far) still have some implementation specifics you need to be aware of.
> MySQL is more intuitive. Authentication? User/password.
PostgreSQL supports that, and configures it by default in the project-supplied installers (IIRC, for local connections only by default.) You only need to configure authentication if you are using different options, or want to support remote logins.
> Yes, pg_hba is more flexible, but if you have only one DB user...
You don't need a multiuser RDBMS at all?
> And there are convenient mysql* commands, you can tell they are mysql commands.
And how do you know that they exist or what they do without reading the docs?
Beyond the RTFM part of your post, it's fun that people are so defensive and don't understand what I'm saying.
"(createuser) not a psql command, its a client application."
Yeah, so postgresql is the only program that creates users for itself? Createuser is a generic name.
(Some) Postgresql client appplications have no prefix. MySQL apps have.
So that if I see a file called mysqladmin I know it's from mysql. If I see a file called createuser I don't have that information. Who says it's from pgsql and not somebody else?
> And how do you know that they exist or what they do without reading the docs?
An hba file is one of the configuration files. The converse question would be: What is a my.cnf file? How am I supposed to know what it does? The answer is the same for both: RTFM
The CREATE USER issue is just bad faith on your part. It is a de-facto standard, used by Oracle, Transact-SQL and ironically MySQL.
In the end, your complain boils down to:
1. Having to edit pg_hba replacing "ident" with "password"
2. Unfamiliarity with pgsql opposed to familiarity with mysql
A developer that chooses tools primarily based on familiarity is on the slippery slope to dinosaur land. Not that it's bad -- Cobol development still pays well to this day.
I'm not talking about "CREATE USER", I'm talking about /usr/sbin/createuser (or whatever it is installed in your system)
"A developer that chooses tools primarily based on familiarity "
Yes, let me waste time to learn how to use the latest fad on HN, sure (not talking about PostgreSQL here)
And PostgreSQL is made of unicorn blood and my projects will be 200% better if I use it instead of MySQL, sure.
It's a DB. There's a narrow range where it would make a difference to use it instead of scaling to more servers, for example. And you can "always" convert later. (Unless you're FB apparently)
Honestly, this is not my experience at all, and I'm not a DBA. I install postgres, I fiddle with setting up a user & pg_hba.conf to allow connections, and after that it's basically game on.
I've installed it on Linux and Windows and, no, its not. Its pretty much the same work as MySQL to setup, except the install usually wants information to create or use a system user for the DB server in the install, which is a trivial amount of extra work.
I understand this used to be an issue many years ago (IIRC, most of the streamlining of the setup happened in the first couple of major 8.x releases, in 2005-2006, about the same time as official native Windows support was added.)
For example, MySQL accepts both ' and " as string delimiters.
It's very fun trying to get around PGSQL not understanding what you want to do while MySQL understands.
It doesn't require ';' to mark the end of a command
> For example, MySQL accepts both ' and " as string delimiters.
That is, in its default mode, MYSQL does not accept ANSI-standard quoted identifiers (supporting only non-standard use of backquotes for quoted identifiers), and instead treats double quotes as equivalent to single quotes for strings. (Of course, MYSQL has a non-default option to support the standard.)
PostgreSQL, OTOH, supports the standard out of the box, and treates double quoted material as quoted identifiers.
Neither behavior is more finnicky, one is just standards-compliant.
> It's very fun trying to get around PGSQL not understanding what you want to do while MySQL understands
This would appear to only be an issue if your only RDBMS experience is with MySQL and you are adapted to its quirks, since if you have experience with other DBs which adhere to the ANSI standards for basic behavior, MySQL will, by default, be more likely to not understand what you mean than PostgreSQL.
SQLite is a great replacement for MySQL for many of the same reasons and in many of the same circumstances where MySQL is a decent replacement for a non-MySQL RDBMS. Its lighter, simpler, and less overhead for simple tasks.
PostgreSQL is a good SQL database.
And don't take me wrong, I'll take MySQL instead of PostgreSQL for simple and medium projects.
"Is the barrier to entry too low, and problems are from legions of rank amateurs?"
Maybe. Well, "low barrier to entry" is not a defect.