The below is by no means an exhaustive comparison of these 3 databases and functionality may not be necessarily ordered in order of importance. These are just our experiences with using these 3 databases. These are the databases we use most often. If we left your favorite database out - please don't take offense. Firebird for one has some neat features such as its small footprint and extensive SQL support, but we have not explored that Db.
People ask us time and time again what's the difference why should you care which database you use. We will try to be very fair in our comparison. We will show equally how PostgreSQL sucks compared to the others. These are the items we most care about or think others most care about. There are numerous other differences if you get deep into the trenches of each.
For those looking to compare MySQL and PostgreSQL you may want to also check out http://www.wikivs.com/wiki/MySQL_vs_PostgreSQL
If you really want to get into the guts of a relational database and the various parts that make it up and how the various databases differentiate in their implementations, we suggest reading Architecture of a Database System by Joseph M. Hellerstein, Michael Stonebraker, and James Hamilton. Architecture of a Database System focuses mostly on Oracle, DB2, and SQL Server but does provide some insight into MySQL and PostgreSQL.
People have pointed out things we omitted and things we got wrong, so we have corrected some of these and will be slowly adding updates.
A lot of people have been making comments on the related Reddit Cross Compare of SQL Server, MySQL, and PostgreSQL thread in addition to this blog. I guess it shows people are really passionate about their databases. Lots of good discussion.
| Feature | Microsoft SQL Server 2005 | MySQL 5 | PostgreSQL 8.3 |
|---|---|---|---|
| OS - Why is this important? Why would you even dream of not running on Windows? If you decide one day that Microsoft is not your best friend in the whole wide world, you can ditch them or at least on your DB Server (could that ever happen?). On a side note, Microsoft can't compete with Oracle on Linux/Unix anyway. If Microsoft has a non-Microsoft DB running on a customer's box, I wonder which database they would prefer - Oracle, IBM DB2, Sun MySQL or PostgreSQL? | Windows XP, Windows 2000+ | Windows (even down to 98?), Linux, Unix, Mac | Windows 2000+, Linux, Unix, Mac |
| Licensing | Commercial - Closed Source, Various levels of features based on version, Free Crippleware | GPL Open Source, Commercial. Here is an interesting blog entry on the subject MySQL free software but not Open Source. The comments are actually much more informative than the article itself. | BSD Open Source |
| Install/Maintenance Process | Hardest most time-consuming and biggest hog of resources of the 3 even when its not doing anything | Easiest | Medium |
| Drivers already installed on Windows | Yes - when you have a windows shop this is huge especially when you are not allowed to install stuff on client desktops and you need to integrate seamlessly with desktop apps. This is why using SQL Server Linked Server to get at yummy features of PostgreSQL comes in handy. | No | No |
| ODBC, JDBC, ADO.NET drivers available | Yes | Yes | Yes |
| Read-Only Views | Yes | Yes | Yes |
| Open Source products available for it | Few except CodePlex/.NET | Many | Few but ramping up and in PHP more than SQL Server |
| Commercial | Many? | Moderate | Few but ramping up |
| Updateable Views | Yes - even for 2 table views will automatically make them updateable if they have keys and update does not involve more than one table. You can write instead of triggers against more complex views to make them updateable | Yes - Single one table views are automatically updateable, some 2 table views are updateable if they don't have left joins and don't involve update of more than one table. If you have more complex views you want to make updateable - good riddance - no support for triggers or rules on views. | Yes, but not automatic. You have to write rules against views to make them updateable but can make very complicated views updateable as a result |
| Materialized/Indexable Views | Yes but varies slightly depending on if you are running SQL Express, Workgroup, Standard, Enterprise and numerous restrictions on your views that makes it of limited use | No? | No, but there are I think 2 contrib modules e.g. matviews that are simple and basically rebuild the materialized view |
| Can add columns and change names, data types of views without dropping | Yes | Yes | No - and extremely annoying if you have views that depend on other views. |
| Can drop tables, (drop, change size, data type of columns), and views used in views - this is a arguably a misfeature but sometimes it comes in handy when you are an EXPERT user :) | Yes - yikes! (but if you schema bind your tables and views, you can not drop dependent objects) | Yes - yikes! | No |
| Graphical View Designer (e.g. you can see tables and select fields drag lines to do joins) included no additional charge | Yes via SQL Management Studio and Express? | No | No |
| Computed Columns | Yes - but we still like using Views more except when we really need the computed column indexed and often we just do triggers. Computed columns are of very limited use since they can't hold roll-ups. | No - but looks like its slated for future release | No - but PostgreSQL has functional indexes so just use a view. |
| Functional Indexes - indexes based on a function | No - but you can create a computed column and create an index on it | No | Yes |
| Partial Indexes - e.g. you want to create a unique index but only consider non-null values | No - but as pointed out you can achieve similar results with an indexed view | No | Yes! |
| ACID compliance - do I dear say this is sometimes over-rated - not all data is created equal and sometimes bulk-insert speed is more important than ACID | Yes | Some storage engines e.g. InnoDB and not MyISAM | Yes |
| Foreign Key - Cascade Update/Delete | Yes | InnoDB and not MyISAM | Yes |
| Multi Row value insert | No - but SQL Server 2008 will have it | Yes | Yes |
| UPSERT logic - where you can simultaneously insert if missing and update if present | No - but SQL Server 2008 will have it via MERGE UPDATE | Yes - via INSERT IGNORE and REPLACE | No |
| Replication - haven't used much except for SQL Server so this is mostly hear-say | Yes - all sorts - log shipping, mirroring, snapshot, transactional and merge etc. and can even have non-SQL Server windows-based subscribers. Its still a bear to get working the way you want it and makes making structural changes difficult. Built-In | Yes - including master-master (built-in) See comments below and from numerours reports a big selling point of MySQL. | Yes but from reports seems to be the least polished of the bunch, although numerours third-party options to choose from that are both free and non-free. PostgreSQL 8.4 or higher is slated to have built-in replication - see core team notes - http://archives.postgresql.org/pgsql-hackers/2008-05/msg00913.php |
| Can program stored procs/functions in multiple languages | Yes - any language that complies with CLR -e.g VB.Net, C#, IronPython - but you need to compile into a dll first - so kind of cheating since you can't see python code right in your db. Upside you don't need IronPython etc. hosted on server, but you can't use the rich environment of Python either and need to have all dependent libraries explictly loaded into SQL Server GAC, a real PITA if you have lots of these dependencies. | No (except C and Pl/SQL) but they are working on it | Yes - PostgreSQL just does it the cool way - we like having our code right there where we can see what it is doing. Downside server must host the language environment. |
| Can define custom aggregate functions | Yes - any .NET language, but not TRANSACT SQL. Why is Transact-SQL thrown out to dust like this? | Yes but only in C as UDF | Yes - any PL language and built-in C, SQL, PLPgSQL. |
| Triggers | Yes | Yes | Yes |
| Table Partitioning | Yes - only Enterprise version - functional, range | Yes? (only applied to NDB cluster), 5.1 will be vastly improved | via Table Inheritance, Constraint Exclusion, RULES and Triggers - basically RANGE. Issues with using foreign-key constraints with inherited tables (plans to improve for 8.4?) |
| Can write Set/Table returning functions that can be used in FROM clause | Yes | No | Yes |
| Support creation of functions - e.g. CREATE FUNCTION | Yes | Yes | Yes |
| Support creation of stored procedures - e.g. CREATE PROCEDURE | Yes | Yes | Sort-Of - CREATE FUNCTION serves the same need |
| Dynamic and action SQL in functions | No - but you can in Stored procedures but you can't call stored procs from SELECT statements so much more limiting than PostgreSQL | No, but can in Stored procedures which aren't callable from SELECT statements so more limiting than PostgreSQL | Yes! - you can do really cool things with action functions in SELECT statements |
| Graphical Explain Tool - no additional charge | Yes - SQL Management Studio/Express | No | Yes - PgAdmin III |
| Job Scheduling Agent controllable from DB Manager client, for running batch sql and shell jobs - no additional charge (not CronTab) | Yes - SQL Agent (not for Express) | No - but upcoming 5.1 will | Yes - PgAgent |
| Access tables from other databases on same server | Yes - server.db.schema.table, can even access disparate data sources via linked server or open query | Yes - db.table, but not easily across servers | Sort of - via Dblink, but much less elegant than MSSQL and MySQL way and much less efficient. Can also access disparate data sources via DBI Link |
| Case-Insensitivity - e.g. LIKE 'abc%' and LIKE 'ABC%' mean the same thing | By default its not case sensitive, but can change this down to the column level. | It is not case-sensitive by default | By default is case-sensitive and a pain to make it not so. Sure you can do ILIKE, but its not indexable and just not the same since an ODBC driver doesn't expose it and is not ANSI compliant. This makes it annoying in environments like MS Access, PHP Gallery where MySQL/MSSQL Server default case insensitivity is more user expected. |
| Date Time support | SQL Server 2005 and below are just really lame. Only have Datetime (no support of timezone or just plain DATE). SQL Server 2008 will have these. | Less lame, has Date and DateTime but none with Timezone | Best - Date, TimeStamp and TimeStamp with Timezone (not to be confused with MySQL's timestamp which autoupdates or SQL Server's deprecated timestamp which is a binary) |
| Authentication | Standard Db security and NT /Active Directory Authentication | Standard Db with table-driven IP like security | Extensive - standard, LDAP, SSPI (can tie in with Active Directory if running on NT server, but still not quite as nice as SQL Server seamless integration), PAM, trust by IP, etc. |
| DISTINCT ON | No | No | Yes |
| WITH ROLLUP | Yes | Yes | No |
| WITH CUBE | Yes | No | No |
| Windowing Functions OVER..PARTITION BY | Yes | No | No |
| COUNT(DISTINCT), AGGREGATE(DISTINCT) | Yes | Yes | Yes |
| OGC Spatial Support - for the My dad is better than your dad fight in the GIS world between SQL Server and PostgreSQL/PostGIS check out A look at PostgreSQL and ArcSDE, Also check out our companion critque of the 3 spatial offerings | No - well Open source MSSQL Spatial add-on has basic support but not as good as PostGIS. Numerous commercial vendors provide spatial extensions for SQL Server 2005 - e.g. Manifold.net, MapDotNet, ESRI ArcSDE come to mind. SQL Server 2008 will have built-in, and geodetic but a lot of functions that PostGIS has will be missing. | Yes - MBR mostly and spatial indexes only work under MyISAM. Limited spatial functions. Some commercial (MapDotNet, Manifold.net), Open source GIS tools gaining steam but still more behind PostGIS. | Yes - PostGIS is great and lots of spatial functions and fairly efficient indexing and lots of open source and commercial support and upcoming ESRI ArcGIS 9.3 supports it too. |
| Schemas | Yes | No | Yes |
| CROSS APPLY | Yes | No | No but can for the most part simulate by putting set returning C/SQL functions in SELECT clause and wrapping more complex functions in an SQL function body. |
| LIMIT .. OFFSET | No - has TOP and ansi compliant ROW_NUMBER() OVER (ORDER BY somefield) As Row --- where ..Row >= ... AND Row <= ... which is much more cumbersome to use | Yes | Yes |
| Advanced Database Tuning Wizard | Yes - SQL Management Studio recommends indexes to put in etc. Very sweet. NOT available for Express or Workgroup. | No | No |
| Maintenance Plan Wizard | Yes via SQL Management Studio - Workgroup and above. Very sweet. Will walk you thru creating backup plan, reindexing plan, error checking and schedule these for you via SQL Agent | No | No |
| Pluggable Storage Engine | No | Yes | No |
| Correlated Subqueries | Yes | Yes | Yes |
| FullText Engine - all 3 have it, but we don't feel right comparing since we haven't used each enough to make an authoritative comparison. Its annoying there is no set standard for doing Full Text SQL queries | Yes | Yes | Yes |
| Sequences /Auto Number | Yes - via IDENTITY property of int field | Yes - via AUTO_INCREMENT of int field | Yes - via serial data type or defaulting to next Sequence of existing sequence object - this is better than MySQL and SQL Server simple auto_increment feature. The reason it is better is that you can use the same sequence object for multiple tables and you can have more than one per table. In the past PostgreSQL sequence was a pain but now you just create it with data type serial if you want it to behave like SQL Server and MySQL and it will automatically drop the sequence if you drop the table it is bound to. |