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

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

Transcript

1. Application migration from SQL Server to PostgreSQL GitHub: YohDeadfall Twitter: YohDeadfall Vladimir Kulikov
2. Agenda • What is PostgreSQL? • How is it working? • How to migrate code? • How to improve performance? 2
3. PostgreSQL 3
4. What is PostgreSQL • Object relational database management system • Over 20 years of history • Open source • Large community 4
5. Very simple process model client postmaster backend Client side Server side 5 backend backend client client
6. Code migration 6
7. What is Npgsql • ADO.NET driver for PostgreSQL • Open source • 6th in TechEmpower Fortune (physical hardware) • 3th in TechEmpower Fortune (cloud hardware) 7
8. 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
9. Is it all? Likelihood no... 9
10. Limited connection count on the server side 10
11. 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
12. 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
13. 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
14. Limited query plan caching 14
15. The reasons of plan cache limitation • No explicit statement preparation • No plan caching between backend processes 15
16. 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
17. Use prepared statements • Manually via the Prepare method of NpgsqlCommand • Automatically by setting the Max Auto Prepare parameter in the connection string 17
18. 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
19. 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
20. 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
21. Other performance improvements 21
22. 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
23. 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
24. Future of Npgsql 24
25. 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
26. Thank you! 26