THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Alexander Kuznetsov

Learning PostgreSql: First Steps

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.

Published Wednesday, October 30, 2013 4:30 PM by Alexander Kuznetsov

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Uri Dimant said:

Great idea Sasha, waiting for the next blog...

PS. We had similar thoughts about MySql so if you are going to cover it as well it will be great :-))))

October 31, 2013 4:07 AM
 

RichB said:

Very interested to hear what the PostgreSQL offers that makes up for a move to Enterprise SQL Server and how you get on.

Hope it goes well and provides a good set of blogging :)

October 31, 2013 6:07 AM
 

DaveB said:

Subscribed! I'll be following this as well. Good luck in the transition. There's some weirdness to PostgreSQL, but I think you'll find your transition to be a smart move.

October 31, 2013 9:23 AM
 

Michael Ball said:

Quite interested in this as I see PostgreSQL has being a viable, low-cost alternative in the database arena.

October 31, 2013 11:04 AM
 

Alexander Kuznetsov said:

Uri,

Unfortunately, right now we are not planning to use MySql at all.

RichB,

PostgreSql has a lot of advantages. We shall be discussing them as we go onwards.

DaveB,

Thank you for the encouragement.

October 31, 2013 12:10 PM
 

Josh said:

RichB (and everyone else), you might want to check out TekPub's series on moving from SQL Server to Postgres:

http://tekpub.com/products/pg

The author's initial blog entry is on the Wayback machine.  He gives a few good selling points:

http://web.archive.org/web/20130119130819/http://wekeroad.com/2012/03/08/something-borrowed-something-new

The author's DBA also has some commentary:

http://datachomp.com/archives/im-leaving-sql-server-for-postgres/

http://datachomp.com/archives/so-long-and-thanks-for-all-the-deadlocks/

November 11, 2013 9:03 AM
 

Alexander Kuznetsov said:

Josh,

Thank you for sharing these great links!

November 11, 2013 10:45 AM

Leave a Comment

(required) 
(required) 
Submit

About Alexander Kuznetsov

Alex Kuznetsov has been working with object oriented languages, mostly C# and C++, as well as with databases for more than a decade. He has worked with Sybase, SQL Server, Oracle and DB2. He regularly blogs on sqlblog.com, mostly about database unit testing, defensive programming, and query optimization. Alex has written a book entitled "Defensive Database Programming with Transact-SQL" and several articles on simple-talk.com and devx.com. Currently he works as an agile developer.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement