PostgreSQL or MySQL (or something else)?

I'm writing a web application in Perl. Database Architecture is top of my planning list and would like /prog/'s opinions. The web application is an file upload host (LOL ORIGINALITY). But seriously, it won't be a general one like Rapidshare, it'll have a very specific target audience.

Anyway, I have seen /prog/ reccomend PostgreSQL numerous times with no reason other than "better than MySQL", though to be honest, MySQL seems like a daftly compacted pile of crap aimed squarely at the CONTENT MANAGEMENT SYSTEM and PHP ENTERPRISE FORUM market.

So I ask you, /prog/, which database engine should I use and why??

If you just need to manage a small set of data without fancy things like foreign keys or clustering, go for [b]SQLite[/b]. And if you ask me why, it's the only one DBMS which is not bloated.

>>1
According to the FAQ: http://www.bash.org/?776099
It does not matter what you use, only the content is where the party-van stops.
Oh shi-

>>2
those things aren't fancy

>>2
your bbcode segfaulted

Anyway, take a look at http://www-css.fnal.gov/dsg/external/freeware/pgsql-vs-mysql.html -- it's fairly thorough. SQLite is okay if you're just interested in a dumb database that you're going to dump a bunch of stuff in for yourself, but please, don't try to make a big web site based on it. That's just shooting yourself in the foot.

BTW, I use Postgres at home because it's more stable and MySQL has broken support for a lot of features, but I use MySQL on my production server because it's faster and PostgreSQL has less fine-grained access controls for multiple users.

>>1
very specific target audience
Porn host, huh?

>>5
Comments from that PgSQL vs MySQL review:

- "Mysql has some basic provisions for referential integrity and transactions/rollbacks." -- More like full transaction support with 4 different isolation levels plus row-level locking share mode and for update selection, with referential integrity checking supporting cascaded updates and deletes in the InnoDB engine. Other engines also support transactions; you might want to try newer or more experimental stuff such as solidDB or Falcon.

- MySQL stored procedures language as of today just plain sucks, it's a piece of crap. Postgres has fucking Python, also Perl and TCL, while you're stuck with that god awful "SQL with flow control" in MySQL, and have no functions outside the database (e.g. no filesystem access). You can't even raise fucking exceptions, so if you want to stop an operation from a trigger you have to provoke an error like dropping a table which doesn't exist. You can extend MySQL in three ways: MyPerl and MyLua, which allow you to write functions (with or without side effects) and SELECT output filters with Perl and Lua, but are supposed to be slow, and you can write them with C as well, which is very fast but complicated. Download the sauce, there's an example for a few functions and select procedures there, such as function SOUNDEX or procedure ANALYSE.

- It says Postgres has schemas, like MySQL didn't. MySQL has schemas. They are not associated with any user by default, but you can make it so with user permissions, as in Oracle.

- mysqladmin (and mysqlshow), save for mysqladmin shutdown, are just for pussies who don't know the actual queries performed, when they are as simple as SHOW PROCESSLIST, for example. MySQL has graphical tools for non-geeky people (Administrator and Query Browser) which allow click-click-click-lol editing of configuration, schemas, etc., all that crap.

- As for special features, MySQL also supports different storage engines, which allow for in-memory tables, compressed tables, or other databases developed by third parties.

- MySQL's InnoDB also features MVCC.

- ALTER TABLE: On MySQL, you can also change the storage engine, in case you want to compare with others.

- NLS: That's a lie, MySQL has great Unicode support, and in fact it has too much support for character sets. It only needed to support Unicode in UTF-8, and maybe UTF-16, but it goes and supports all kinds of character sets and encodings, which can be finegrained: there's a charcter set for the system (e.g. filesystem), then a default charset per server, default per schema, default per table, and even per column, so you can have a single table with different columns in different character sets, but as I said, all character sets are bullshit except for the one and only acceptable character set which is defined by ISO 10646 and semantically enhanced and specified by Unicode. In any case, all character sets come with great collation support for every language, as well as the Unicode Collation Algorithm which is the one you should be using.

- As for dates, MySQL has awesome timezone support with TIMESTAMP columns (you connect and set your time zone, then you can forget time zones ever existed: when you select you obtain all dates in your local time, and when you write you write all dates in your local time).

Also, the review missed very important features:

- Backups: MySQL is not too strong here, but a hot consistent export with InnoDB is possible (InnoDB physical hot backup is possible, but not for free). MyISAM consistent export or hot backup require locking. It has the binary log which logs changes to the database regardless of storage engine.

- Replication: MySQL is very strong here and has good replications support, which works great as a backup method and a way to infinitely (yes) scale reads. (Multi-master replication or circular replication won't work well though.) I've heard Postgres replication is a joke (a trigger hack, only replicates INSERT, UPDATE and DELETE, and breaks more often than /b gets posted to). Also, MySQL's replication is not ENTERPRISE, it's simple.

- Clustering. Clustering is an ENTERPRISE feature that will help your business maximize profits by reducing operation costs and increasing customer satisfaction thanks to its XML-based two-tier AJAX engine. MySQL's cluster is good for one application, and terrible for most, so don't touch it. There are commercial clusters but I've heard they aren't the bomb either. And I've heard Postgres is about the same, only without an official in-memory cluster like MySQL's.

PostgreSQL is cool. MySQL is a patched-up hackjob. Take your pick.

My main concerns about PostgreSQL are performance (considering replication sucks and I wouldn't know about clustering), and the fact it's so similar to Oracle. Similar to Oracle means ENTERPRISE, thus it's most surely bloated beyond measure and shamefully overengineered, with features that you will almost never use, some of which will get in your way, and the most complex way to solve every problem through overgeneralization, overdefinition, and overeverything.

NLS: That's a lie, MySQL has great Unicode support
Bull. Shit.
I have still been entirely unable to get a rather large database I'm using converted entirely to utf-8, without having to rebuild the entire server, reconfigure it so that ALL tables are unicode, and then rebuild the database from scratch. I *thought* it was UTF-8 to begin with, but holy shit! Even though both the database and my connection were configured from the start to use UTF-8, somewhere else it was still in Latin-1 mode, so everything got mangled into Latin-1 reinterpreted as UTF-8. Why is there a difference? it should just put the data in the table, and stop defecating on it first.
I'm not saying that full Unicode isn't possible, but my experiences have been unpleasant. And before you say something like "diaf noob retard, you suck", I was reading the documentation thoroughly, and even had someone else (who had even held a job with AB for a couple years) next to me at the time. And it still ate my data. Fortunately I had a backup.
On the other hand, PostgreSQL just worked out of the box. SQLite just worked out of the box. Fucking Oracle worked out of the box. Why does MySQL have to do it some retarded way that breaks data by default?

then you can forget time zones ever existed
Oh goody gumdrops, that's such a fantastic idea! Let's have people understand less about what their code is doing. This smacks of PHP's magic_quotes_gpc among other braindead blunders. When will developers learn? In order to be an even marginally proficient sysadmin/coder/whatever, you NEED to understand what your code is doing -- and moreover, you need to know what your data is doing, not to have magic timezone conversion shit going on behind your back.

Similar to Oracle means ENTERPRISE, thus it's most surely bloated beyond measure and shamefully overengineered, with features that you will almost never use, some of which will get in your way, and the most complex way to solve every problem through overgeneralization, overdefinition, and overeverything.
that's bullshit. Of course, if you have a little tiny weenie DB, Oracle is overkill. but those features are there for a reason. Of course, if you've never had to do 10+ hours queries, you wouldn't know.

>>10
SHOW VARIABLES LIKE '%charset%' would have told you what's still latin1 and what did you have to set. You must have missed something because I got it working right in like 15 minutes. Also, SET GLOBAL and edit your config files, I hope you weren't setting just local variables? Finally, be sure to issue SET NAMES utf8 upon connecting; some libraries are retarded.

Why does MySQL have to do it some retarded way that breaks data by default?
Because the world is still full of 8-shits using backwards fags (often Amerifags who don't care for Unicode). Like I said, if MySQL supports n character sets, it supports n - 1 more character sets than it should.

Let's have people understand less about what their code is doing.
Nonsense, you don't have to be unaware of timezones, you just get them handled properly.

This smacks of PHP's magic_quotes_gpc among other braindead blunders.
Not at all; magic quotes are one of the worst misfeatures ever written by man that works by corrupting data. TIMESTAMP fields just allow you to do timezone calculations in the database, as you could do character set or type conversions. It is a database facility.

not to have magic timezone conversion shit going on behind your back.
It's not magic, it's perfectly clear and it's explicit by your setting of the timezone. It's also simple and impossible to fuck up with it. It doesn't fuck with data or deal with it in a way that forces you to reconvert it or fucks with other transformations you may want to perform.

http://wiichan.net/c/kareha.pl/1175117971/
somewhat relevant, i think.

those features are there for a reason.
yes, they're there because oracle sucks so much it needs to list them seperately. I have the extreme displeasure of having to manage and maintain oracle databases.

Now, postgres and mysql both suck in their own varying and irritating ways, but I would pick either over oracle anyday. I can't say one is easier than the other because it all depends on what you're doing.

IMHO, DB2 and MSSQL are tops and everything else somewhere below, however, if you need a free database without some retarded limit or opensource one...sucks to be you! Or you can use postgres and go crazy, or use mysql and go crazy in some different way.

btw postgres is easier to get up and running instead of mysql if you don't really want to fuck around with databases.

MySQL is fast.

EXCEPT WHEN YOU WANNA USE SUBQUERIES LOLOLOL

Optimize THIS

Lain.