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

Cars, Databases, and Benchmarks

Over the last few months we have migrated some functionality from Sql Server to PostgreSql, and developed a couple of new systems powered by PostgreSql. So far I do not see that either of these two products is better than another - they are different. Even though I guess this is the right time to write something like "N reasons why PostgreSql is better than Sql Server", or vise versa:  "N reasons why Sql Server PostgreSql is better than PostgreSql", or both ( I guess I could write it both ways), I am not going to write along these lines.

I do not see either of these two products being better than another - they are just different. I am migrating to PostgreSql and choosing it for my new systems because it makes practical sense given the specific circumstances, considering the specific requirements I am dealing with right now. I guess in agile environments it makes sense not to be too partial to any particular technology, but to choose the right technology for the problem at hand and to be open-minded.

Let me provide an analogy between choosing and using  a database and choosing and using a car. I am definitely not the first to introduce the analogy between cars and databases. Consider, for example, the logo of Sql Rally conference, with the presumably car's RpM needle deeply in the red. This logo has always kept me wondering: what exactly is the message this is trying to convey? We are not supposed to drive cars like this - it means abusing and eventually ruining the engine, does it not?

Also let us consider the super-sleek race car on the cover page of a book about administering SQL Server. This cover page is also kind of surprising: is it related to the content of the book? If the answer is yes, does it mean that the database requires a full-time team of highly skilled mechanics, maintaining it every several minutes? If we are in the business of winning races, I guess this is the way to do it, although I've never done anything like that.

However, if all we need from a car is to reliably get from point A to point B, then there are simpler and cheaper alternatives to a race car, such as a common kind of sedan or van or SUV - this is what most of us are actually driving to get from point A to point B.

Let us extend this analogy to gas mileage and TPC benchmarks. As I drive my car, I do not really get the gas mileage as specified in the manual. On the highway, I may get somewhat more miles per gallon than the number in the manual. In the city, it is the other way around - I burn more fuel than specified in the manual. My mileage may and does vary - this is not a surprise at all. The roads I drive on might be quite different from the ones the standard mileage is measured on. Also I am definitely not a perfect driver - driving is not my core competence, which probably impacts the mileage I am getting.

Similarly, as we develop systems that use databases, our results may be quite different from the ones in TPC benchmarks. Our workloads might be quite different from the standardized ones actually used in standard benchmarks. More importantly, we are not full-time experts in any database engine. Instead, we are more or less close to full stack developers. As such, we are not going to spend months squeezing every last bit of performance from every SQL query - we have neither the time nor the expertise to do so. More likely we would expect to spend 20% of effort and hopefully get 80% of results. Our typical usage of databases is not like driving a race car trying to win a race - it is more like a daily commute in a typical commuter's car: we can command some reasonable effort to get where we want, but we cannot compete in an all-out race twice a day, every day.

Also I would not typically consider a checklist of available/missing features as the most important criteria when comparing databases. Just like with cars, some features are important or mandatory, others are irrelevant, and this all depends on the workload we are dealing with. For example, having a 4WD/AWD is a must in Upper Peninsula, but it is probably useless in Southern Texas.

Instead, we should evaluate the overall efficiency: how much time and money we spend building and maintaining the thing, and how well it solves users' problems.

Regarding migrations from SQL Server to PostgreSql, there are quite a few details we need to be aware of. In the past I've described a few of these details, and I am going to continue that, so stay tuned.

 

Published Tuesday, April 22, 2014 8:45 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

 

RichB said:

Rather depends on where you work and what you are doing - there are some places where squeezing as much performance and speed as possible out is core, others where just rolling out another oversized bit of kit to maintain that just sits around averaging 10% cpu (of which around 7% is av and other services) is the way it gets done.

April 24, 2014 4:35 AM
 

Alexander Kuznetsov said:

RichB,

I concur: squeezing as much performance and speed as possible may in some cases make sense. For example if a query stays unchanged forever, or if it runs on lots of hardware, or both - then eventually the effort spent on ultimate performance tuning will pay off.

Suppose, however, that the half life of a query is unknown and possibly short. If the query becomes obsolete soon after we worked long and hard to speed it up, then all the effort spent optimizing it to the fullest is wasted. In such cases spending too much time optimizing is a sheer waste of time and money.

April 24, 2014 10:25 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