Amazon RDS - PostgreSQL for .NET Developers
Tags: dotnet, aws, database, postgres
Published: 2022-06-06 15:45:21

[PostgreSQL](https://www.postgresql.org/) is a free, open-source database that emphasizes extensibility and SQL compliance and was first released in 1996. A true competitor to commercial databases such as SQL Server and Oracle, PostgreSQL supports both online transaction processing (OLTP) and online analytical processing (OLAP) and has one of the most advanced performance features available, multi-version concurrency control (MVCC). MVCC supports the simultaneous processing of multiple transactions with almost no deadlock, so transaction-heavy applications and systems will most likely benefit from using PostgreSQL over SQL Server, and there are companies that use PostgreSQL to manage petabytes of data.

Another feature that makes PostgreSQL attractive is that not only does it support your traditional relational database approach, but it also fully supports a JSON/JSONB key/value storage approach that makes it a valid alternative to your more traditional NoSQL databases; so, you can now use a single product to support the two most common data access approaches. Because of its enterprise-level of features and the amount of work it takes to manage and maintain those, even though it is also open source and free software like MySQL and MariaDB, it is slightly more expensive to run PostgreSQL on Amazon RDS than those other open-source products.

## PostgreSQL and .NET

As with any database product that you will access from your .NET application, its level of support for .NET is important. Fortunately for us, there is a large community involved in helping ensure that PostgreSQL is relevant to .NET users. 
Let’s look at what you need to do to get .NET and PostgreSQL working together. The first thing you need to do is to include the necessary NuGet packages, [**Npgsql**](https://www.nuget.org/packages/Npgsql/7.0.0-preview.4) and [**Npgsql.EntityFrameworkCore.PostgreSQL**](https://www.nuget.org/packages/Npgsql.EntityFrameworkCore.PostgreSQL/) as shown in Figure 1. 

<img src="https://billthevestguy.com/wp-content/uploads/2022/05/image-17.png" />
_Figure 1. NuGet packages required to connect to PostgreSQL_

Once you have the packages, the next thing that you need to do is to configure your application to use PostgreSQL. You do this by using the **UseNpgsql** method when overriding the _OnConfiguring_ method in the context class as shown below:

```
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.UseNpgsql("connection string here");
}
```

A connection string for PostgreSQL has six required fields:

- server – server with which to connect
- port – port number on which PostgreSQL is listening
- user id – user name 
- password - password
- database – database with which to connect
- pooling – whether to use connection pooling (true or false)

When working in an ASP.NET Core application the connection string is added to the _appsettings.json_ file as shown in Figure 2.

<img src="https://billthevestguy.com/wp-content/uploads/2022/05/image-18.png" />
_Figure 2. Adding a connection string to an ASP.NET Core application_

Let’s now go create a PostgreSQL database.

## Setting up a PostgreSQL Database on Amazon RDS

Now that we know how to set up our .NET application to access PostgreSQL, let’s go look at setting up a PostgreSQL instance. First, log into the console, go to RDS, select _Create database_. On the Create Database screen, select _Standard create_ and then _PostgreSQL_. You then have a lot of different versions that you can select from, however, the NuGet packages that we used in our earlier example require a reasonably modern version of PostgreSQL, so unless you have any specific reason to use an older version you should always use the default, most updated version.

Once you have defined the version of PostgreSQL that you will use, your next option is to select the **Template** that you would like to use. Note that you only have two different templates to choose from: 
•	_Production_ – defaults are set to support high availability and fast, consistent performance.
•	_Dev/Test_ – defaults are set in the middle of the range.

_Note: Both MySQL and MariaDB had a third template, Free tier, that is not available when creating a PostgreSQL database. That does not mean that you must automatically pay, however, as the AWS Free Tier for Amazon RDS offer provides free use of Single-AZ Micro DB instances running PostgreSQL. It is important to consider that the free usage tier is capped at 750 instance hours per month across all your RDS databases._

Selecting the template sets defaults across the rest of the setup screen and we will call those values out as we go through those items.

Once you select a template, your next setup area is **Availability and durability**. There are three options to choose from:
•	_Multi-AZ DB cluster_ – As of the time of writing, this option is in preview. Selecting this option creates a DB cluster with a primary DB instance and two readable standby instances, with each instance in a different Availability Zone (AZ). Provides high availability, data redundancy and increases capacity to serve read workloads.
•	_Multi-AZ DB instance_ – This option creates a primary DB instance and a standby DB instance in a different AZ. Provides high availability and data redundancy, but the standby instance doesn't support connections for read workloads. This is the default value if you chose the Production template.
•	_Single DB instance_ – This option creates a single DB instance with no standby instances. This is the default value if you chose the Dev/Test template.

The next section, **Settings**, is where you provide the _DB instance identifier_, or database name, and your _Master username_ and _Master password_. Your database identifier value must be unique across all the database instances you have in the current region, regardless of engine option. You also have the option of having AWS auto-generate a password for you.

The next section allows you to select the **DB instance class**. You have the same three filters that you had before of _Standard classes_, _Memory optimized classes_, and _Burstable classes_. Selecting one of the filters changes the values in the instance drop-down box, You need to select Burstable classes and then one of the instances with micro in the title, such as a _db.t3.micro_ as shown in Figure 3.

<img src="https://billthevestguy.com/wp-content/uploads/2022/05/image-19.png" />
_Figure 3. Selecting a free-tier compatible DB instance_

The next section in the setup is the **Storage** section, with the same options that you had available when going through the MySQL and MariaDB setups, though the default values may be different based upon the instance class that you selected. After the storage section are the _Connectivity_ and _Database authentication_ sections that we walked through earlier, so we will not go through them again now – they are standard across all RDS engine options. Selecting the Create database button will take you back to the RDS Databases screen where you will get a notification that the database is being created as well as a button that you can click to access the connection details. Make sure you get the password if you selected for AWS to create your administrative password. You will only be able to access the password this one time.

The pricing for PostgreSQL is slightly higher than MariaDB or MySQL when looking at compatible configurations, about 6% higher.

## Selecting between PostgreSQL and MySQL/MariaDB

There are some significant differences between PostgreSQL and MySQL\MariaDB that can become meaningful when building your .NET application. Some of the more important differences are listed below. There are quite a few management and configuration differences, but those are not mentioned since RDS manages all of those for you!

- _Multi-Version Concurrency Control_ - PostgreSQL was the first DBMS to rollout multi-version concurrency control (MVCC), which means reading data never blocks writing data, and vice versa. If your database is heavily used for both reading and writing than this may be a significant influencer.
- _More types supported_ - PostgreSQL natively supports NoSQL as well as a rich set of data types including Numeric Types, Boolean, Network Address, Bit String Types, and Arrays. It also supports JSON, hstore (a list of comma-separated key/value pairs), and XML, and users can even add new types.
- _Sequence support_ – PostgreSQL supports multiple tables taking their ids from the same sequence while MySQL/MariaDB do not.
- _Index flexibility_ - PostgreSQL can use functions and conditional indexes, which makes PostgreSQL database tuning very flexible, such as not having a problem if primary key values aren’t inserted sequentially.
- _Spatial capability_ – PostgreSQL has much richer support for spatial data management, quantity measurement, and geometric topology analysis.

While PostgreSQL is considered one of the most advanced databases around, that doesn’t mean that it should automatically be your choice. Many of the advantages listed above can be considered advanced functionality that you may not need. If you simply need a place to store rarely changing data, then MySQL\MariaDB may still be a better choice. Why? Because it is less expensive and performs better than PostgreSQL when performing simple reads with simple join. As always, keep your use cases in mind when selecting your database.

_**Note**: AWS contributes to an open-source project called Babelfish for PostgreSQL, which is designed to provide the capability for PostgreSQL to understand queries from applications written for Microsoft SQL Server. Babelfish understands the SQL Server wire-protocol and T-SQL. This understanding means that you can use SQL Server drivers for .NET to talk to PostgreSQL databases. As of this writing, this functionality is not yet available in the PostgreSQL version of RDS. It is, however, available for Aurora PostgreSQL. We will go over this in more detail later in the chapter. The project can be seen at [https://www.babelfishpg.org](https://www.babelfishpg.org)._

MariaDB, MySQL, and PostgreSQL are all open-source databases that have existed for years and that you can use anywhere, including that old server under your desk. The next database we will talk about is only available in the cloud and within RDS, Amazon Aurora.