Why Postgres

Given that this book is being published by a .NET tools company, and I am predominantly a .NET developer (even if I do work across various platforms), the million dollar question has to be asked: Why use Postgres when we have MS SQL Server, MongoDB, and many .NET-specific database systems at our disposal?

Well, let's get the obvious one out of the way, which I’ve already mentioned: it’s free. More than that though, it's open. The Postgres open source license allows you to pretty much do anything you want with the application and its code. This includes customizing it for your own in-house purposes and specific needs.

If customizing the core application is not for you, then its highly flexible extension API can be used to create add-on products that integrate into the core system as though they were an integral part that had always been there.

What else can it do?

Postgres over the years has developed some very unique features that simply don't exist in other databases. I'll list some of these soon. It’s also fully 100% compliant with ANSI SQL:2008 standards and runs on many different platforms, including Windows and all flavors of Linux, Unix, and even the likes of Solaris and all recent editions of MacOS. It’s also easy to install on all these platforms; in most cases, it's as simple as installing a single package.

Postgres is also very scalable and has some very impressive upper limits on what it can and cannot achieve.

Postgres Limits

As you can see, these are some very impressive statistics. The reason so much is unlimited is because Postgres stores its databases in regular files in the host file system, so the only realistic upper limit is available disk space.

Postgres also has many different types of indexes available to ensure efficient data access. I'll cover these in more detail in later chapters, but for now the following indexes are available out of the box:

• B-Tree
• Hash
• GiST
• GIN

New index strategies can easily be added using the extension API, just as easily as new data types, table types, storage types and many other things.

Moving on, Postgres is also a fully object-oriented database, meaning it's designed to model data in the same way as object-oriented programming languages like C++ and C#.

Some of the features in the core product are available specifically to support this scenario, the most useful of which is table inheritance.

Table Inheritance

Table inheritance is the process of using database tables in a similar manner to how a developer might extend the functionality in an object-oriented application architecture, allowing the reuse of existing data items, while adding new data items to the table.

Let me give you an example.

Let's assume we have the following database table for our company staff:

It's a fairly simple, standard table definition typical of something that might be found in a normal database.

Let's now assume that each department has its own specific attributes it wishes to keep for each staff member in that department, and let's further assume that each department has different requirements for these attributes.

So for example, the IT department may have the following additions:

• Languages developed in
• Courses taken
• Projects worked on
• Systems owned

The Accounts department might have the following:

• Clients responsible for
• Projects worked on
• Subdepartment
• Office name

In most database systems, you would have to extend your database table to ensure it had all the required fields needed for each of those departments:

Developers have had to deal with this scenario for a long time now, so why does it matter? Well, it’s simple to define this when you first create your database, but what about adding a new department to your system several years after the database was originally created?

Postgres solves this issue with its table inheritance capability. Essentially, it allows you to define the original staff table, as normal. It then allows you to define additional tables with just the extra columns, which “inherits” from the original staff table.

The result is two tables that look like the following:

and

The table inheritance capability does much more than just save typing time.

If you change the FullName, WageBand, PhoneNumber, or Department fields of the previous staff table for example, those changes are made to the parent staff table and the IT staff table.

This way, you can have an application that filters its records based on the department the staff member works in, and shows only the data needed by that department. Someone in another department can still see the basic details of that person from their own application, should they need to.

Essentially, you’re treating the table like a reusable object that can be extended infinitely. Even more so, inherited tables can be further inherited themselves in the same manner.

Imagine how useful this is in an environment where you’re frequently adding new functionality to an application. Rather than create scripts to migrate existing tables to a new schema then possibly have to update many different ORM or data access mechanisms to see the new schema, you can simply add an inherited table to your database, and then add a new class to your data access layer to access only that new, extended table. Existing code will continue to work as normal, and your new functionality will continue as though it has that new table.

Yes, there is more…

Multiple languages embedded directly inside the database allow you to write stored procedures in the following languages (the first four are included out of the box):

• pgSQL / SQL
• Tcl
• Python
• Perl
• C
• C++
• R
• Java
• PHP
• Ruby
• Scheme

…and many others.

All of these procedural languages have normal first class syntax constructions too. Things like For-Each, Do-While, If-Then, and many other constructs work on full table sets directly inside a stored procedure in exactly the same manner as they do with normal applications in the real world.

This pretty much means that language skills you've already acquired can easily be used in developing solutions directly inside the core product.

Moving on, we also have the concept of array types in a regular database column.

Arrays? In a database?

Yes, all of the Postgres default data types can be used as an array type.

What's more, when you have an array type, all of the available programming APIs available allow you to interface your application to Postgres to support these operations natively without any extra work. For example, you can easily define the following in C#:

You can save this array directly into an array field in your database by using normal data access calls and then later retrieve that data field. Your array will remain intact for you to use as you would normally.

Postgres can also validate your data at insert and update times to ensure that you only put data into your database that is valid. Validation already exists for things like email addresses, phone numbers, regular expressions, and more.

So far pretty much everything I've mentioned is included out of the box. We haven't even begun to scratch the surface of the additions that are possible with the many extensions available, many of which are actually included with the core server in the extra contributions folder that is installed with the product.

These extensions allow you to manipulate XML and XSLT directly in the database, add extra encryption functionality, and add new data types (such as ISBN numbers for books) along with appropriate validation rules and logic to ensure that your types contain what you expect.

There are even full-blown feature packs that transform Postgres into something entirely different. Packages like PostGIS (there's a full chapter on it later in the book) transform a PostgreSQL server into a full-fledged GIS data management system with the same spatial functionality of Oracle Spatial.

As if that's not enough, the different versions of Postgres available for different platforms will take advantage of special facilities available on those platforms. For example, there is an x64 (64-bit specific) build for the Windows platform that allows you to have larger upper limits than those previously mentioned. There are builds available that will run across massively parallel super-scalar CPU architectures, and there's even specialist builds that can use things like a GPU for heavy duty number crunching.

For a free system, there is little reason not to choose Postgres as the data store for your application, and as you'll see later, with the latest round of .NET data providers available, the server plays very nicely with things like Entity Framework 4.1 and newer.

One final point I'd like to make is aimed at those who are worried about free products not having support.

Apart from the obvious open source community support that is available, EnterpriseDB (one of the main companies that looks after and runs the various Postgres projects) offers many support packages designed for companies that want to pay for support contracts. They also offer some massive extended professional packages and extensions for companies that wish to pay for the system. These extra packages take things even further than those facilities described so far in this book.

If you want to read more, the full feature matrix page can be viewed at http://www.postgresql.org/about/featurematrix/.