Npgsql -
PostgreSQL for .NET

whoami

Shay Rojansky
Long-time Linux/open source developer/enthusiast (installed Slackware somewhere in 1994/5)
Owner of Npgsql
- PostgreSQL driver
- Entity Framework (O/RM) providers
Microsoft MVP (for open source .NET PostgreSQL work)

whois microsoft

Open-source, not just Windows anymore

.NET Core: cross-platform .NET.
MIT license, developed on github.
Microsoft has the most open source contributors on GitHub (Octoverse 2016)
ASP.NET Core in top 10 TechEmpower plaintext benchmark, on Linux.

Introduction
Npgsql Driver Features
Entity Framework Core – O/RM Features for PostgreSQL
Driver/Protocol Internals
Closing Words

PostgreSQL Driver for .NET

Features and Performance

Hello World

using (var conn = new NpgsqlConnection("Host=localhost;Database=mydatabase;..."))
{
    conn.Open();
    using (var cmd = new NpgsqlCommand("SELECT 'hello world'", conn))
        Console.WriteLine(cmd.ExecuteScalar());

    using (var cmd = new NpgsqlCommand("SELECT * FROM foo", conn))
    using (var reader = cmd.ExecuteReader())
        while (reader.Read())
            Console.WriteLine(reader.GetString(0));
}

Layers (e.g. Dapper) make this nicer and more idiomatic

Extended Type Support

PostgreSQL supports a rich set of non-standard data types:
- Domain-specific simple types: inet, macaddr, circle…
- Array
- Range
- Enum
- Composite
New types can be added via extensions

Extended Type Support

var mac = reader.GetFieldValue<PhysicalAddress>(0);
var rng = reader.GetFieldValue<NpgsqlRange<int>(0);
var arr = reader.GetFieldValue<int[]>(0);

// PG: CREATE TYPE mood AS ENUM ('happy', 'sad', 'ok')
enum Mood { Happy, Sad, OK }
NpgsqlConnection.MapEnumGlobally<Mood>();
var mood = reader.GetFieldValue<Mood>(0);


// PG: CREATE TYPE my_composite AS (x int, some_text text)
class MyComposite {
    public int x { get; set; }
    public string SomeText { get; set; }
}
NpgsqlConnection.MapCompositeGlobally<MyComposite>();
var comp = reader.GetFieldValue<MyComposite>(0);

Asynchronous Programming

In sync I/O, your thread blocks while you wait for your DB query to execute:

var cmd = new NpgsqlCommand("UPDATE x SET y=8 WHERE z=9", conn);
cmd.ExecuteNonQuery();  // The thread is stuck
Console.WriteLine("Done");

Bad for scalability – lots of waiting threads.
UI thread blocked -> freeze.
Async releases your thread to do other things.

Callback-Based Async

Unfortunately, async programming usually sucks (NodeJS):

client.connect(function (err) {
  if (err) throw err;

  // execute a query on our database
  client.query('SELECT $1::text as name', ['brianc'], function (err, result) {
    if (err) throw err;

    // just print the result to the console
    console.log(result.rows[0]); // outputs: { name: 'brianc' }

    // disconnect the client
    client.end(function (err) {
      if (err) throw err;
    });
  });
});

Language Support for Async

C# supports async operations in the language:

async Task FooAsync() {
	var cmd = new NpgsqlCommand("UPDATE x SET y=8 WHERE z=9", conn);
	await cmd.ExecuteNonQueryAsync();
	Console.WriteLine("Done");
}

The C# compiler will rewrite this function with callbacks under the hood.
This gives the benefits of async, while programming like it’s sync.

Prepared Statements

In PostgreSQL, preparing statements is important:

SELECT table0.data+table1.data+table2.data+table3.data+table4.data
FROM table0
JOIN table1 ON table1.id=table0.id
JOIN table2 ON table2.id=table1.id
JOIN table3 ON table3.id=table2.id
JOIN table4 ON table4.id=table3.id

Unprepared:

TPS: 953

Prepared:

TPS: 8722

Persisting Prepared Statements

Prepared statements + short-lived connections =
In Npgsql 3.2, prepared statements are persisted:

using (var conn = new NpgsqlConnection(...))
using (var cmd = new NpgsqlCommand("<some_sql>", conn) {
    conn.Open();
    conn.Prepare();
    // The first time on this physical connection, preparation takes place.
    // After that, the same prepared statement is reused.
    conn.ExecuteNonQuery();
}

Automatic Preparation

Prepared statements + O/RMs =
Npgsql 3.2 includes automatic preparation: all the benefits, no need to call Prepare().
Statement is prepared after being executed 5 times.
We eject least-recently used statements to make room for new ones.

Not Preparing Your Statements

Roundtrip per Statement

NPGSQL

POSTGRESQL

INSERT INTO...

Batching/Pipelining

NPGSQL

POSTGRESQL

INSERT INTO...; INSERT INTO ...

OK; OK

Batching / Pipelining

The performance improvement is dramatic.
The further away the server is, the more the speedup (think cloud).
Supported for all CRUD operations, not just insert.
For bulk insert, binary COPY is an even better option.
Entity Framework Core takes advantage of this.

var cmd = new NpgsqlCommand("INSERT INTO ...; INSERT INTO ...", conn);
cmd.ExecuteNonQuery();

Buffering

Buffer resultsets? Rows? Columns??
JDBC/psycopg2 buffer resultsets by default
Npgsql has fixed send/receive buffers
.NET has API for sequential column access
Read/write should never copy needlessly

Other Features

Fully binary data transfer
Binary COPY
Waiting for notifications
Kerberos login (Windows/Linux), SSL/TLS
Distributed transactions

Entity Framework Core PostgreSQL Support

Exposing PostgreSQL-Specific Features in a Generic O/RM

What is EF Core?

Entity Framework (currently 6.x) is Microsoft’s O/RM, first released in 2008.
EF Core is a total rewrite, focusing on cross-platform/cross-database support, a lighter, more modular design and NoSQL support.
Released with .NET Core, MIT-licensed, works on Linux/MacOS…
Microsoft maintains providers for SQL Server and Sqlite, the community does others.

Querying in O/RMs - Not Fun

Disadvantages:
- No compile-time syntax checking.
- If some column name changes, boom.
- Not such a great advantage over hand-coding SQL.

Some string language (e.g. Hibernate HQL):

String hql = "FROM Employee E WHERE E.id > 10 ORDER BY E.salary DESC";
Query query = session.createQuery(hql);
List results = query.list();

Better But Not Amazing

Notice the ‘and_’ – very important (can’t use Python and).

Using an API (Python’s SQLAlchemy):

for name, in session.query(User.name).\
...     filter(and_(User.name.like('Shay%'), User.Age > 18)).\
...     order_by(User.id)

?!?!

Hibernate's Criteria API:

List cats = sess.createCriteria(Cat.class)
    .add( Restrictions.like("name", "Fritz%") )
    .add( Restrictions.or(
        Restrictions.eq( "age", new Integer(0) ),
        Restrictions.isNull("age")
    ) )
    .list();

C# and LINQ

C# has Language Integrated Query built into the language:

List<int> list = ...;
var nums = list
   .Where(i => i % 2 == 0)
   .OrderBy(i => i)
   .Take(10)
   .Select(i => i * 2)
   .ToList();

The language already has the basic building blocks.
Functional programming

var myBlogs =
    from b in blogs
    where b.Name.StartsWith("B")
    orderby b.LastUpdate
    select b;

LINQ and Entity Framework

The same building blocks work on database tables:

var names = ctx.Users
    .Where(u => u.Name.Contains("Shay") && u.Age > 18)
    .OrderBy(u => u.Id)
    .ToList();

Entity Framework receives an expression tree, which it then translates to SQL.
Query database tables using the same code we use with objects.

... back to PostgreSQL

There’s an Npgsql provider for Entity Framework Core.
A provider is needed to support PostgreSQL-specific things: SQL dialect, types, etc.
Since O/RMs are a generic abstraction layer, they hide database-specific features.
But EF Core makes it possible to expose PostgreSQL-specific functionality.

Type Mapping

Mapping totally PostgreSQL-specific types:

public class Blog
{
	public int Id { get; set; }
	public string Name { get; set; }
	public IPAddress Address { get; set; }
	public string[] Tags { get; set; }
}

Simple

PostgreSQL-specific

type

Not so simple

PostgreSQL-specific

type

Translating Method Calls

PostgreSQL has great built-in support for regular expressions:

SELECT * FROM blogs WHERE name ~ '^\+?[\d\s]+\(?[\d\s]{10,}\)?$'

Wouldn’t it be nice to expose this in an O/RM?

var myBlogs =
    from b in blogs
    where Regex.IsMatch(b.Name, "a+b")
    select b;

This code will run as-is against another database, but will be client-evaluated.

This transfers all blog details over the network.

PG Customizations of DB Objects

modelBuilder
    .Entity<Blog>()
    .HasIndex(e => e.SomeField)
    .ForNpgsqlHasMethod("gin");

EF Core allows arbitrary "annotations" on your model, which translate into DDL.

Creating your model:

A Few Words on Collaboration

EF Core development happens on github.
Work on the PostgreSQL provider started very early, when everything was in early beta.
Input from PostgreSQL helped shape EF Core – the Microsoft team was very accessible and extremely enthusiastic/receptive.
In addition to discussions, many PRs have made into the core.

Adventures in Database Driver Development

Minimizing Roundtrips

NPGSQL

POSTGRESQL

BEGIN

INSERT INTO...

var tx = conn.BeginTransaction();   // Sends BEGIN
// do stuff in the transaction

Prepend, prepend

BeginTransaction() simply writes the BEGIN into the write buffer, but doesn’t send. It will be sent with the next query.
Another example: when closing a (pooled) connection, we need to reset its state.
This is also prepended, and will be sent when the connection is “reopened” and a query is sent.

The PostgreSQL Protocol

NPGSQL

Parse

Bind

Describe

Execute

Sync

Parse

Complete

Bind

Complete

Row

Description

Data

Row

Data

Row

Ready For

Query

POSTGRESQL

Batching

NPGSQL

Bind1

POSTGRESQL

Exec1

Bind1

Exec1

Sync

BC1

Data1

BC2

Data2

RFQ

PG starts sending result here

If these are big...

Deadlocks are Fun

One option is to use non-blocking I/O. We send protocol messages until a send “would block”. We switch to reading, and send the rest later.
Unfortunately, .NET I/O doesn’t support non-blocking I/O very well.
Instead, later protocol messages are sent asynchronously – sending and receiving happens at the same time.

Summary

Microsoft isn’t the bad guy anymore – full commitment to open-source and cross-platform.
.NET Core is a real option, whatever OS you’re using.
C# (and other .NET languages) is a very modern language that continues to evolve very quickly.
PostgreSQL is fully supported in .NET, Npgsql provides high-performance and feature-rich access.

@shayrojansky

roji@roji.org

https://slides.com/shayrojansky/npgsql-2017-01-tlv

Npgsql - PostgreSQL for .NET

whoami

whois microsoft

Open-source, not just Windows anymore

Contents

PostgreSQL Driver for .NET

Hello World

Extended Type Support

Extended Type Support

Asynchronous Programming

Callback-Based Async

Language Support for Async

Prepared Statements

Persisting Prepared Statements

Automatic Preparation

Not Preparing Your Statements

Roundtrip per Statement

Batching/Pipelining

Batching / Pipelining

Buffering

Other Features

Entity Framework Core PostgreSQL Support

What is EF Core?

Querying in O/RMs - Not Fun

Better But Not Amazing

?!?!

C# and LINQ

LINQ and Entity Framework

... back to PostgreSQL

Type Mapping

Translating Method Calls

PG Customizations of DB Objects

A Few Words on Collaboration

Adventures in Database Driver Development

Minimizing Roundtrips

Prepend, prepend

The PostgreSQL Protocol

Batching

Deadlocks are Fun

Summary

Npgsql -
PostgreSQL for .NET