In this series of blog posts we shall migrate some functionality from SQL Server to PostgreSql 9.2. The emphasis of these blog posts will be on what PostgreSql does differently from Sql Server - I assume that the reader has considerable knowledge of Sql Server, but might know nothing of PostgreSql. Also we shall concentrate on development, not administration. In a true agile fashion, we shall learn only what we need to get this particular job done, and nothing else, but we shall strive to learn it very well.
The reason for migration
Prior to SQL Server 2012, in many cases it used to cheaper to add more memory to solve SQL Server performance problems, and not to spend too much time tuning queries. That was especially true for agile development: if we retire or modify a query, all the effort invested in its tuning may be lost, but the investment in hardware is not lost - the hardware used to speed up obsolete queries stays with us.
So I was wondering if it may be cheaper to port at least some functionality to PostgreSql rather than upgrade the whole system to Enterprise Edition of SQL Server 2012.
I have a system which is a good candidate to try this idea out. I have a system that reads from just a few summary tables, using about a dozen stored procedures. These summary tables use up a considerable amount of storage, and they are not used by any other applications.
So, we are going to migrate a few tables, rewrite just a few stored procedures, and redirect a considerable amount of workload off our SQL Server database to the new PostgreSql one.
This is a very well-defined little project. We are not going to encounter the most difficult problem - discovering the requirements. All we need to do is replicate a working system on a new platform. As such, we shall be able to concentrate on easier problems, such as learning new technologies and ensuring stable and acceptable performance.
In the next post we shall create a table, write to it from a client written in C#, and read the data back into a c# client.