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.

Contents

  • 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...

OK

INSERT INTO...

OK

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

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

By Shay Rojansky

Made with Slides.com

Npgsql

Israeli PostgreSQL Meetup, January 2017

  • 1,421

More from Shay Rojansky