In our May 2008 issue of Postgres OnLine Journal, we cross compared Microsoft SQL Server 2005, MySQL 5, and PostgreSQL 8.3. Some people mentioned well since 8.4 has now come out, shouldn't we go back and update the reference. We deliberated and decided not to. To be fair all 3 products have released new versions, so it would seem unfair to compare a newer PostgreSQL against older versions of MS SQL Server and MySQL. We have therefore decided to repeat our exercise and include parts people felt we should have covered, as well as comparing the latest and greatest stable release of each product.
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.
People have been posting comments on Reddit as well
| Feature | Microsoft SQL Server 2008 | MySQL 5.1 | PostgreSQL 8.4 |
|---|---|---|---|
| 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 Desktop/Server | Windows Desktop/Server , Linux, Unix, Mac | Windows Desktop/Server, Linux, Unix, Mac |
| Licensing | Commercial - Closed Source, Various levels of features based on version, Free Crippleware (4 GB limit but free for embedding inclusion) | GPL Open Source, Commercial. Now owned by Oracle, and some of us are still wondering how Oracle will make hay out of its new found treasure. Will they use to upsell. Monty Widenius has an interesting blog entry explaining the MySQL dual licensing and how it has changed. | BSD Open Source. Its the freest of all and many argue about it. You can fork it all you want without giving back and make your own commercial derivative. Though ironically there are not all that many forks. Netezza forked it initially (though its unclear if any PostgreSQL code lives on in their product), GreenPlum forked it, EnterpriseDb forked to give Oracle features, but to their credit gives back a lot of fixes to the community. |
| Install/Maintenance Process | Most time-consuming to install and most dependencies, but lots of wizards to help you forget everything and mail you when somethng bad happens (non-free versions) | Still Easiest. There are a lot of prepackaged products with it and its install rarely fails. | Medium (I put PostgreSQL at medium because on occasion we have run into issues where we have manually init the db on Windows. Those are rare though. On Linux its still the deal that MySQL often comes pre-installed for you and for PostgreSQL you have to figure it out yourself or they give you some super antiquated version. With PostgreSQL Yum respository its much easier now if you manage your own box. |
| 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 | Moderate -- still a lot of commercial stuff hasn't been certified on 2008 because 2008 is so new | Moderate | Moderate -- I think PostgreSQL has improved since last we checked, but probably still lower than MySQL penetration. |
| 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 | Yes - sort of - as of 8.4 you can now add columns to the end of view without dropping it. |
| 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 - (but if you schema bind your tables and views, you can not drop dependent objects so this does appear to be the best of both worlds) | Yes - yikes! | No |
| Graphical Query/View Designer (e.g. you can see tables and select fields drag lines to do joins) included no additional charge. As people pointed out there are lots of commercial and free tools that will do this for you. We'll provide a listing and brief summary of features etc. of some of these in our product showcase later. | Yes via SQL Management Studio and Studio Express and pretty nice. | No | Yes. As of PgAdmin III 1.9, but its kind of hockey -- doesn't do JOINS right. |
| 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 | Yes - as of SQL server 2008 See Tom's notes and called Filtered Indexes. | 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, PBXT (see comments from Giuseppe Maxia) and (not MyISAM) | Yes |
| Foreign Key - Cascade Update/Delete | Yes | InnoDB and not MyISAM | Yes |
| Multi Row value insert | Yes | Yes | Yes |
| UPSERT logic - where you can simultaneously insert if missing and update if present | Yes via MERGE UPDATE | Yes - via INSERT IGNORE, REPLACE INSERT ON DUPLICATE UPDATE | 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.5 or higher is slated to have built-in replication. Sorry guys this did not make it in. Slony is still used for replication, and many like it but find it finicky and harder to use than MySQL. |
| Can program stored procs/functions in multiple languages | Yes - In theory any language that complies with CLR -e.g VB.Net, C#, IronPython - but you need to compile into a dll first and then load the dll into the database. The dll is stored as part of the database and the dependencies registered in the SQL Server GAC - a real PITA if you have lots of these dependencies than are non-standard. | No (except C and Pl/SQL) | Yes - PostgreSQL just does it the cool way (common ones PL/PgSQL, sql, PL/Python, PL/Perl, PL/R) - we like having our code right there where we can see what it is doing. Downside server must host the language environment. It now supports variadic functions similar to Oracle. Neither MySQL nor SQL Server support that. |
| 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 | via Table Inheritance, Constraint Exclusion, RULES and Triggers - basically RANGE. Issues with using foreign-key constraints with inherited tables in 8.4 the constraint_exclusion has another option called "partition" which is the new default. Which basically means you can have constraint_exclusion for partitioning and not have your other queries suffer. |
| 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 (someone on Reddit mentioned maatkit visual explain for MySQL. This is still a text format though and not quite as pretty as SQL Server or PgAdmin III graphical explain plan. Are there others? | 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), administer via Management Studio. Can do sql, sql maintainence plans, batch scripts, and SSIS work flows. Its still the best. Has wizard for setting up maintenance plans. | Yes - though appears can only use it for MySQL sql calls. | Yes - PgAgent and can run postgresql sql as well as batch scripts. Administrated via PgAdmin III. |
| 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. Across servers you need Federated storage engine See Rob Wultsch note | 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 but depends on character set (see comments from Giuseppe Maxia) | By default is case-sensitive , but in 8.4 we have newer contrib citext integrated to define case insensitivity fields. |
| Date Time support | Finally they support plain date - Date, DateTime,DateTimeOffset. (Date and DateTimeOffset are new) | Date and DateTime but none with Timezone, but you can have timezone, see Rob Wultsch comments. | (Seems pretty much on par with SQL Server) - Not much changed - 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). Has Interval which neither MySQL nor MS SQL Server have. |
| 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. |
| Column Level Permissions | Yes | Yes | Yes (introduced in 8.4) |
| DISTINCT ON | No | No | Yes |
| WITH ROLLUP | Yes | Yes | No |
| WITH CUBE | Yes | No | No |
| Windowing Functions OVER..PARTITION BY | Yes | No | Yes - and its way better than SQL Server 2008 |
| Common Table Expressions and Recursive queries | Yes | No | Yes |
| 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 | Yes - now built-in, but we aren't allowed to provide benchmarks for obvious reasons. If you use it, you'll really want to install the SQL Server 2008 Spatial Extension tools. The upcoming SQL Server 2008 R2 (currently in CTP is supposed to have Report Builder with map integration features which should be interesting). It has geodetic which PostGIS does not. SQL Server 2008 and PostGIS have pretty identical commercial support for spatial, but PostGIS still has a much larger Open source tool belt following. | 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 - ESRI ArcGIS 9.3, MapInfo, Manifold, CadCorp, FME , no geodetic but expect the first version of geodetic in PostGIS 1.5, and fairly robust geodetic in PostGIS 2.0. |
| Schemas | Yes | No (technically MySQL is implemented as a single db with schemas -- according the the information_schema schema, though in practice its not quite as clear cut as Oracle) | Yes |
| CROSS APPLY | Yes | No | No but can for the most part simulate by putting set returning functions in SELECT clause. As of 8.4, all set returning functions can be used in the SELECT regardless of language it is written in. |
| 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 (no ansi compliant way) | Yes, and also supports the ansi compliant ROW_NUMBER() OVER (ORDER BY somefield) |
| 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 |
| Query Planner for complex queries (like doing correlated joins, lots of joins, lots of aggregates etc)-- the thing that figures out how to navigate data based on SQL Statement and histograms and stuff. This is off the cuff rating and varies based on kinds of queries you write. For the joe blow blog or CMS or plain read SELECt ... FROM, this is probably not important and all 3 will perform adequately. We do a lot of statistical and financial apps where ability to run complex queries against millions of records in under 5 seconds is important. | Moderate (but supports parallel processing out of the box). | Sucks Okay this was a spatial analysis and we can argue why beat a dead horse. But this is just a bounding box query. | Best. PostgreSQL doesn't support parallel processing out of the box, but supports shared reads and with GridSQL (which we haven't tried), you do get parallelism |
| 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. |
| Transactional DDL - ability to rollback CREATE, ALTER etc statements | Yes (I couldn't find any documentation on this, but I tested it and it correctly rolls back). There is a caveat that can't roll back DDL within a DDL trigger. | No | Yes - see this comparative analysis |