Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Владимир Куликов "Миграция приложений с SQL Server на PostgreSQL"

DotNetRu
December 11, 2018

Владимир Куликов "Миграция приложений с SQL Server на PostgreSQL"

Что может быть проще, чем переход с одной СУБД на другую со стороны кода приложения?
ADO.NET предоставляет базовые интерфейсы по работе с данными, и разница сводится лишь к диалекту SQL и замене используемых классов одного провайдера на другие… Но это всё лишь на первый взгляд.
В данном докладе Вы узнаете про разницу между устройством SQL Server и PostgreSQL, их клиентов, а также про способы, которые помогут улучшить производительность приложений.

DotNetRu

December 11, 2018
Tweet

More Decks by DotNetRu

Other Decks in Programming

Transcript

  1. Agenda • What is PostgreSQL? • How is it working?

    • How to migrate code? • How to improve performance? 2
  2. What is PostgreSQL • Object relational database management system •

    Over 20 years of history • Open source • Large community 4
  3. What is Npgsql • ADO.NET driver for PostgreSQL • Open

    source • 6th in TechEmpower Fortune (physical hardware) • 3th in TechEmpower Fortune (cloud hardware) 7
  4. Changes in code using (var connection = new SqlConnection(connectionString)) using

    (var command = new SqlCommand("DROP TABLE students", connection)) { // ... } using (var connection = new NpgsqlConnection(connectionString)) using (var command = new NpgsqlCommand("DROP TABLE students", connection)) { // ... } Before: After: 8
  5. The reasons of connection exhausting • Application holds connection for

    a too long period • Bad application design • Business logic inside queries and functions • PostgreSQL process model and low connection limit 11
  6. Use connections only when necessary Incorrect: Correct: using (var connection

    = new NpgsqlConnection(connectionString)) using (var command = new NpgsqlCommand("DROP TABLE students", connection)) { await connection.OpenAsync(cancellationToken); // Command initialization goes here await command.ExecuteNonQueryAsync(cancellationToken); } using (var connection = new NpgsqlConnection(connectionString)) using (var command = new NpgsqlCommand("DROP TABLE students", connection)) { // Command initialization should be here await connection.OpenAsync(cancellationToken); await command.ExecuteNonQueryAsync(cancellationToken); } 12
  7. Use pooling middleware • pgBouncer Lightweight connection pooler that provides

    connection pooling. • pgPool II Provides robust query routing and connection pooling for Postgres-based solutions. • Odyssey Advanced multi-threaded PostgreSQL connection pooler and request router. 13
  8. The reasons of plan cache limitation • No explicit statement

    preparation • No plan caching between backend processes 15
  9. Unprepared and prepared statements 0 5000 10000 15000 20000 25000

    30000 SELECT 1 1 table 2 tables 5 tables 10 tables Operations per second Query complexity pgbench –m {simple,prepared} –T 60 Unprepared Prepared 16
  10. Use prepared statements • Manually via the Prepare method of

    NpgsqlCommand • Automatically by setting the Max Auto Prepare parameter in the connection string 17
  11. Use the rich type system for batch processing var points

    = new [] { new Point(10, 20), new Point (30, 40) }; using (var command = new NpgsqlCommand("INSERT INTO points VALUES (@x, @y)", connection)) { var parameterX = command.Parameters.Add(new NpgsqlParameter(“x")); var parameterY = command.Parameters.Add(new NpgsqlParameter(“y")); foreach (var point in points) { parameterX.Value = point.X; parameterY.Value = point.Y; command.ExecuteNonQuery(); } } Instead of multiple command execution: 18
  12. Use the rich type system for batch processing var points

    = new [] { new Point(10, 20), new Point (30, 40) }; using (var command = new NpgsqlCommand("INSERT INTO points SELECT * FROM @p", connection)) { command.Parameters.AddWithValue("p", points); command.ExecuteNonQuery(); } Execute it once with array of composites: 19
  13. How to use composites? • Create the required type via

    CREATE TYPE • Register type mapping in the application CREATE TYPE point AS (x integer, y integer); NpgsqlConnection.TypeMapper.MapComposite<Point>("point"); C# (global registration) SQL C# (per connection registration) connection.TypeMapper.MapComposite<Point>("point"); 20
  14. Prefer generic methods and parameters using (var command = new

    NpgsqlCommand(“SELECT @p", connection)) { command.Parameters.Add(new NpgsqlParameter<int>("p", 42)); command.ExecuteNonQuery(); } 22 • Faster routing to the write method • Low pressure on the GC
  15. Batching/Pipelining using (var cmd = new NpgsqlCommand("SELECT ...; SELECT ..."))

    using (var reader = cmd.ExecuteReader()) { while (reader.Read()) { /* Read first resultset */ } reader.NextResult(); while (reader.Read()) { /* Read second resultset */ } } 23 • Performs single roundtrip to the database • Impact depends on the network latency
  16. Future of Npgsql • Performance improvements • Devirtualization of type

    handlers • Handling composites using dynamic methods • Pipelines/multiplexing • Type handling improvements • ROW to ValueTuple mapping • Non-parameterless constructor support • Monitoring and tracing via DiagnosticsSource 25