Database migrated from MSSQL to PostgreSQL

We recently 'upgraded' our database from MSSQL to PostgreSQL. PostgreSQL is a widely acclaimed open-source database used by Yahoo, Spotify, and others. This post is as much an update for the users of the site as it is to those out there looking to move away from Microsoft's MSSQL and the draconian costs that go along with it.

Biggest takeaways:

1. PostgreSQL database shows performance improvements on several busy pages such as the survivor picks page.
2. Database is now on its own server, which provides better scalability as the site grows in traffic.
3. Database has better security, only specific IPs can access it.
4. A seperate standby database that will mirror the primary database server, so that any kind of outage will not result in lost picks (previously we had a 4 hour vulnerability due to backups on a 4 hour cadence).

Purpose & Reason to Switch to PostgreSQL

Last year during the 1st couple NFL Sundays, the site was either slow or nonresponsive during the busy times of 2 and 5pm when games finish. We immediately moved the site from Everleap hosting where we shared an MSSQL server, to our own VPS with lots of CPU and memory power. We then installed a 6-month trial of the MSSQL database software so that we could have access to the MSSQL statistics and diagnostic data. We eventually identified the smoking gun, which wasn't due to lack of CPU or memory resources, but instead found that we were making too many unncessary database queries. Once we cached the impacted objects to reduce the database connections, the site performance dramatically increased especially during concurrent user activity.

Our hosting company at the time was Everleap. Wlile they were fine overall, like other hosting companies they have plans where you share the MSSQL server, and have limited or no access to the database diagnostics. In order to have a private MSSQL server at these companies you have to pay a huge fee. With the 6-month trial expiring in mid March, we knew we had to switch to a different database due to the hugely draconian licensing costs that Microsoft has placed on their server. They essentially price out anyone who isn't a fortune 500 company. I'm not sure why any company would use their server given that PostgreSQL is as good or in some cases better, and is open source (free). There are plenty of big companies such as Netflix that now use PostgreSQL.

MSSQL vs PostgreSQL Performance

Once I had the test site up running PostgreSQL (4 cores 8G memory), I was able to test it against the production site that was still running MSSQL (its enterprise version on 5 cores, 10G memory). As a test I chose to load an 800 player survivor league standings page. The 'Survivors' and 'Eliminated' tabs were essentially identical in performance. When I loaded the 'Player Picks' tab, again the performance was similar, however when I clicked the 'Group by Player/Clones' button, PostgreSQL was 30% faster. I also found the Manager 'Player Accounts' page that can be slow for large leagues, showed similar performance between the two databases. This is all mostly anectdotal, but the bottom line is that database performance was not impacted by the switch to PostgreSQL.

PostgreSQL Advantages over MSSQL

I've found several advantages of PostgreSQL after years of working with MSSQL:

• PostgreSQL is open source and free, yet performs at the same level as MSSQL.
• The setup and configuration is easy, more accessible, and the documentation is good.
• Security, in that Postgres makes it easy to limit access to specific IP addresses only.
• It was very easy and cost-effective to add a standby server for the site's PostgreSQL databases. What this means is that any crash of the database server will not result in lost picks, instead the standby server will become available to take over as the primary. In the past, we would only take backups every 4 hours of the MSSQL database, because of the costs we were limited in our options to improve on this.
• Because PostgreSQL is open source there are many choices available for monitoring and diagnostics. Here's a shout out to Percona and their awesome Database Monitoring and Management tool. 

Finally, a shout out to intelligent-converters.com and their MSSQL to Postgresql conversion tool, that worked seemlessly and made the conversion process much easier!

Fred Williams

Site Commisioner/Owner

"I want to die peacefully in my sleep like my grandfather, not screaming in terror like his passengers!"