In line with the title of this blog, I'd like to start posting articles about languages, tools and technologies that I got a chance to use recently.

One of the technologies that I was lucky to learn is PostgreSQL.

I have always been an avid Microsoft technology stack advocate, so naturally most of the time my RDBMS system of choice was Microsoft SQL Server. Now, I'm still holding my opinion that Microsoft SQL Server is a great product, which scales wonderfully from small web applications to large enterprise solutions. The fact however is that for any non-trivial applications, i.e. once the database system requirements outgrow the potential of the free SQL Server Express edition, licensing costs may start to become an issue. And that is (at least for me) exactly the point at which PostgreSQL comes into play.

At a glance

PostgreSQL is a powerful, open-source relational database management system. Feature-wise PostgreSQL is comparable to Microsoft SQL Server, at least when it comes to RDBMS features. It scales well for large workloads and at the same time - you guessed it - it remains free.

My Favorites

There is a ton of introductory articles about PostgreSQL on the web. Rather than adding another one, let me just highlight some of the features that I like and enjoy about PostgreSQL:

PostgreSQL is easy to install

The quick and straightforward installation of PostgreSQL is a big contrast to the bulky and slow Microsoft SQL Server installer.

You may start your database adventures within minutes of downloading the software.

The PostgreSQL installation process typically involves downloading the software, running the installer, and specifying a few basic configuration settings. If you also opt to install the pgAdmin GUI tool (a PostgreSQL counterpart to SQL Server Management Studio), you may start your database adventures within minutes of downloading the software.

Good compatibility with Microsoft SQL Server

I was pleasantly surprised when testing the compatibility between the two systems. I mean, there certainly are areas in which the syntax differs, but overall it's not bad at all.

And for cases where you're porting existing solutions based on Microsoft SQL Server to PostgreSQL, you can take advantage of 3rd party tools that make it easier to perform an initial migration of your SQL server database schemas into PostgreSQL. After evaluating a few of these tools, my ultimate recommendation goes to MSSQL-to-PostgreSQL. This small and easy-to-use tool is fast and performs most of the type conversions automatically.

Query execution speed

Query execution speed constitutes one of the crucial factors of a good database system. In my mind, Microsoft SQL Server has always been a synonym for speed, especially when it comes to large enterprise-grade workloads. And honestly, it might've been concerns about query execution speed, which prevented me from even trying out alternative RDBMS solutions before.

PostgreSQL's Query Optimizer does a very good job of consistently delivering efficient query execution plans, resulting in a solid performance.

Well, with PostgreSQL, I was pleasantly surprised. Query speeds are very good. I don't want to compare SQL Server and PostgreSQL execution speeds here, as I have not done any rigorous speed comparison, but given all the years of working with different versions of SQL Server, I must admit that PostgreSQL's Query Optimizer does a very good job of consistently delivering efficient query execution plans, resulting in a solid performance. I'm yet to test large workloads on PostgreSQL, but so far I'm optimistic that PostgreSQL will be able to handle all possible levels of workloads with ease.

Check out the following screenshot of the Explain feature of pgAdmin. This tool greatly helps with analyzing and troubleshooting PostgreSQL query performance.

Entity Framework Core provider

One of my concerns when considering supporting a non-Microsoft RDBMS system was how good and mature the implementation of its Entity Framework Core database provider would be. Entity Framework Core represents an important piece of the Microsoft technology stack puzzle. An incomplete or buggy Entity Framework provider could make supporting the PostgreSQL data storage a nightmare.

And yet again, I got pleasantly surprised. Npgsql - the recommended ADO.NET and Entity Framework (Core) provider - makes the adoption of PostgreSQL an almost seamless experience. Additionally, the Npgsql provider implements those PostgreSQL language elements, which are not present in the Microsoft SQL Server world. Let me mention here at least support for the ILIKE operator, as well as a range of functions related to the PostgreSQL's LTree data type.

Here's a short snippet of code that illustrates how easy it is to start using PostgreSQL in your Entity Framework Core application:

public void ConfigureServices(IServiceCollection services)
{
    services.AddDbContext<TestContext>(options => 
        options.UseNpgsql(Configuration.GetConnectionString("TestContext")));
}

Wrapping up

From the paragraphs above, one could get the idea that everything is perfect with PostgreSQL and that the language and feature compatibility with Microsoft SQL Server is flawless. Well, no - not exactly. Over time I came across several compatibility and tooling issues (Aargh - those unexpected pgAdmin crashes!). But let me leave those to a future blog post.

Overall, I find PostgreSQL to be a great (and free!!) alternative to Microsoft SQL Server.

In the spirit of a book from my favorite author John Green - The Anthropocene Reviewed - I'm giving PostgreSQL ⭐⭐⭐⭐ (4) stars!