tag:blogger.com,1999:blog-20038672.post1949612055337423709..comments2024-03-28T00:58:29.187-04:00Comments on Robert Haas: PostgreSQL Performance vs. Microsoft SQL ServerRobert Haashttp://www.blogger.com/profile/08393677427643988650noreply@blogger.comBlogger3125tag:blogger.com,1999:blog-20038672.post-72730797611654976122010-12-21T15:20:57.397-05:002010-12-21T15:20:57.397-05:00@Mark Callaghan - I'm not aware of any async I...@Mark Callaghan - I'm not aware of any async I/O work under way at the moment, but I agree it's an interesting idea.<br /><br />@Grant Johnson - In PostgreSQL 8.4+, the free space map is sized automatically, so there is no need to tune that parameter.Robert Haashttps://www.blogger.com/profile/08393677427643988650noreply@blogger.comtag:blogger.com,1999:blog-20038672.post-3630514632484574372010-12-21T11:43:44.601-05:002010-12-21T11:43:44.601-05:00Over many years of using both, here is what I have...Over many years of using both, here is what I have found:<br /><br />For applications with plenty of hardware and few users, the two are very comparable for performance. As concurrency grows, PostgreSQL seems to handle it better, likely due to its MVCC model using disk in the existing data files. However, if the data file run out of room, it can be slower expanding used storage than SQL server. Keep your free space maps big enough, and your vacuums frequent enough to deal with it, and it is great.<br /><br />The other time when it really seems to shine is when starved for hardware. PostgreSQL seems to deal much better with all of the CPU's being at 100% and swap going like mad trying to keep up. Remember also that I have always used PostgreSQL on Linux or BSD, so some of this may be how the operating systems handle different loads.Grant Johnsonhttps://www.blogger.com/profile/06772023212277928936noreply@blogger.comtag:blogger.com,1999:blog-20038672.post-54945637484997234732010-12-21T07:37:26.213-05:002010-12-21T07:37:26.213-05:00SQL Server has a really nice hash join including s...SQL Server has a <a href="http://technet.microsoft.com/en-us/library/ms189313.aspx" rel="nofollow">really nice hash join</a> including support for async IO. When will Postgres get async IO to make large external sorts and joins faster? By "async IO" I mean real (use OS facilities) or simulated (use background threads/processes). Either is good enough to reduce the time that the join/sort stalls for IO when the server has many disks and a smaller number of concurrent queries. Parallel query would be even better, but that is much more work.Mark Callaghanhttps://www.blogger.com/profile/09590445221922043181noreply@blogger.com