Introduction

Notice: This is not the typical performance’s comparative between SQL Server and PostgreSQL. I only test their performance’s affectation when they run in the same server, that I would never do.

We were asked: “Is it possible to run SQL Server and a PostgreSQL in the same server?”

Obviously, the server would run under Windows. The first thought was: no.

We said that because we would experience problems with I/O and memory pressure, due to the high use of disk and memory that is typical on. We were asked to prove this.

Analysis

We decide to use the DELL’s DVD store benchmark. It’s a benchmark that simulates a DVD store with a simple OLTP database. The benchmark can be run on several databases and SQL Server and PostgreSQL are among them.

We want to see how they would behave when working together, compared to when they’re working alone. It’s not a SQL Server vs. PostgreSQL benchmark because we don’t want to go deep on how DVD store is build, DB drivers’ performance and so on.

We create a DB of 10GB in each RDBM.

Hardware

A brief description about the server:

• Intel Xeon E3-1220 3.1Ghz
• Memory: 8 GB
• RAID controller: LSI Adapter SAS2 2004 Spitfire with 2 SATA disks in RAID 1.

Sofware

• Microsoft Windows Server 2008R2 Standard SP1 64-bits
• SQL Server 2008 Workstation Edition
• PostgreSQL 9.2.4

Preparation

After install the RDBM it’s time to install DVD Store:

• For SQL Server, I use this article. It’s very comprehensive.
• For PostgreSQL in Windows I haven’t found something similar so I do it on my own, finding this issues:
  • The script for creating the database and importing the data is for Linux bash pgsqlds2_create_all.sh so I port it to Windows, pgsqlds2_create_all.bat,.
  • Executing ds2pgsqldriver.exe I get a runtime error.

To solve the runtime error in ds2pgsqldriver.exe I modify the source code: in file c:\ds2\pgsqlds2\ds2pgsqlfns.cs at line 209 I change this block:

int i_row = 0;
if ((customerid_out > 0) && Rdr.NextResult())
{
    while (Rdr.Read())
    {
        title_out[i_row] = Rdr.GetString(0);
        actor_out[i_row] = Rdr.GetString(1);
        related_title_out[i_row] = Rdr.GetString(2);
        ++i_row;
    }
}

For this one:

int i_row = 0;
try
{
    if ((customerid_out > 0) && Rdr.NextResult())
    {     
        while (Rdr.Read() && i_row < title_out.Length)
        {
            title_out[i_row] = Rdr.GetString(0);
            actor_out[i_row] = Rdr.GetString(1);
            related_title_out[i_row] = Rdr.GetString(2);
            ++i_row;
        }
    }
    Rdr.Close();
    t.Commit();
    rows_returned = i_row;
} 
catch (Exception e)
{
    Rdr.Close();
    t.Commit();
    rows_returned = i_row;

    Console.WriteLine("Length of arrays: {0,3}, {1,3}, {2,3}", title_out.Length, actor_out.Length, related_title_out.Length);
    Console.WriteLine("i_row value {0}", i_row);
    Console.WriteLine("Thread {0}: Error inside login: {1}", Thread.CurrentThread.Name, e.Message);
    return (false);
}

And I compile it:

csc.exe /out:ds2pgsqldriver.exe ds2pgsqlfns.cs c:\ds2\drivers\ds2xdriver.cs /d:WIN_32_TIMER /d:GEN_PERF_CTRS "/r:c:\ds2\drivers\Npgsql.dll"

Execution

We execute them with the following parameters:

c:\ds2\pgsqlds2\ds2pgsqldriver.exe --target=127.0.0.1 --run_time=20 --db_size=10GB --n_threads=40 --ramp_rate=10 --pct_newcustomers=0 --warmup_time=0 --think_time=0 > c:\Temp\testresults.txt 2>&1
c:\ds2\sqlserverds2\ds2sqlserverdriver.exe --target=localhost --run_time=20 --db_size=10GB --n_threads=40 --ramp_rate=10 --pct_newcustomers=0 --warmup_time=0 --think_time=0 > c:\Temp\testresults_sqlserver.txt 2>&1

The benchmark result is at the end of the output file:
n_purchase_from_start= 206466 n_rollbacks_from_start= 4110

Results

The numbers are the results of the benchmark. Higher are better.

When running alone, SQL Server is much better than PostgreSQL, but as I said before it is not our goal and it can be for a lot of reasons, for example better implementation of DVD store or better drivers. But still, I’m impressed to see that difference. Test PostgreSQL in Linux is beyond the scope of our analysis, but I’m sure that it would improve a lot.

Conclusion

When working together, SQL Server is the winner, it only decreases 23% but PostgreSQL decreases 82% when they run together.

Here we’ve to notice that SQL Server is a native Windows application, while PostgreSQL is multi platform and probably this compatibility across multiple OS is paid with the efficiency in Windows. So, installing SQL Server and PostgreSQL in the same server, when there’s some workload, the PostgreSQL have important performance degradation.