Important distinctions between DBMSs [closed]

Question

DBA is a career I'm strongly considering after school, but my limited experience mostly involves some PL/SQL Oracle web programming, some basic PHP/MySQL web pages, and some queries in SQL Server. DBMSs are important for my senior project:

+ a presentation highlighting the pros/cons/features of the more popular ones 
  (Oracle, SQL Server, MySQL, DB2, and PostgreSQL)

+ choosing a DBMS for our actual project. 

I'm not asking "Which is best?", but what are some important differences that you have found in your experiences? I have a handle on the differences between InnoDB and MyISAM, and the features tables from the Wiki db comparison, but there's obviously a lot more to it when you're in the field.
So, besides the easy ones (cost,support, OS's, reports for Oracle and SQL Server), what are the issues that you guys have encountered that make, break, or just add some really nice functions that help make your life easier(or harder) as a DBA?

Lastly, we building the framework for a social network. We're using the free micro-instances from AWS, so although that means under 700MB of RAM and a single core, that puts (I think) the free editions of Oracle and SQL Server on the same footing as the open-source DBMS's. The original plan is a LAMP environment, but after researching the topic above, PostgreSQL sounds like it could be useful, given that a social network would utilize a lot of writes (a benefit of InnoDB, as I understand it), but it also sounded like that's the only engine that it uses, whereas MySQL can use both InnoDB and MyISAM for optimization (and I'm sure Facebook uses MySQL for some good reasons).

tl;dr What are some key differences between DBMS's that you've experienced? If you were building a social network that, ideally, would eventually scale to 1 million users, which would you use?

Answer 1

This question is probably a bit too broad to be canonically answerable, but I'll give it a shot.

A few points in no particular order:

• Not only is Oracle quite expensive, but since it's from entirely different stock than other relational database systems, there are some "impedance mismatches" (for lack of a better phrase) between Oracle and other RDBMS systems. i.e. in Oracle temporary space is handled radically differently from SQL Server (temporary tablespace vs tempdb), it doesn't have multiple databases on one server (but Oracle-style schemas are a solid replacement) and tablespaces and redo logs are entirely foreign beasts that I know nothing about bar the name. Look here and here for further explication on the SQL Server-Oracle differences.

  • This is only an issue if you have a heterogenous database environment (database systems from different providers), if you're exclusively Oracle then that woouldn't be an issue. Similarly, DB2 is an independent system (and I'm sure it has it's own little idiosyncracies), however MS SQL and Sybase are similar (originally they were the same codebase; splintered in 1995 or so), and MySQL and PostgresSQL, as you noted, have similarities.
• Management functions (and their ease of use) are crucial for a DBA's sanity. Unfortunately, that's something that only really comes with experience, and all I can tell you is that SQL Server's job system is the best I've used for automatic tasks (short of cron + shell scripts).
• Similarly to management functions, little 'gotchas' in database systems can cause hours of 'fun' for DBAs. (Data/Log boundaries in Sybase are a classic example; ask me if you want more info.)

• Facebook uses a customised MySQL install to meet their performance requirements. They're hitting limits in places most people don't even know exist. So:

  • This is not something you're going to run into if your scaling horizon is 1 million consumers.
  • The other good thing about most social network database requirements is that you can, sometimes, relax strict ACID-compliance and move to a model called 'eventual consistency', which is how most big data systems work. However, as noted, with a horizon of 1 million consumers, pretty much any of the database systems you've listed will be able to handle the load easily (assuming you have proper hardware and proper configuration and your application developers don't write 'The Daily WTF'-style code.)

So, to answer the sixty-four thousand dollar question of "which DBMS would I use as the back end of a million-consumer social network?", I would answer "SQL Server" purely because it's the one I'm the most familiar with and I know how to configure it for the requirements. As to which you should use, I'd say a little practical test is in order. Figure out what you're likely to need to do (install the DBMS, create a database, create tables, add users, add permissions, etc etc.) and write a little checklist of those things. Then, for each of the DBMS systems on your shortlist, grab the express/free/trial version and run through the checklist, noting down how long it takes you to figure out how to do those things and whether how it's done makes sense (how intuitive it is).

After you're all done, go through that list, and the one that's easiest/fastest to use for you is the one you should use.

In a corporate environment, quite often the business will persist with using database systems well beyond the time they should have upgraded to the next version or completely swapped providers. There's two facets to the reason why, the first is risk (if the database goes kerplunk in a permanent fashion, the entire business will probably follow) and the second is institutional knowledge - everyone knows how to use the current system, and there's a cost (both in training and efficiency) while people transition to a new system.