Best practice for PostgreSQL
Best practice for PostgreSQL
Introduction
This is a work-in-progress. Reader discretion is adviced!
Index
Consider the following when creating an index
- The cost of maintaining index-trees for INSERT, UPDATE, and DELETE operations. Do not use an index if these operations are more performance critical than the retrieval.
- When multiple indices are used in combination:
- The order will impact performance greatly.
- If some of the columns in an combined index is not matched against, the optimizer is likely to revert to a Full Table Scan. Obfuscated Conditions - Smart Logic
- The type of index matters.
Commands (Npgsql)
- Do not concatenate User-Defined types as strings into the SQL commands. (SQL injection preventation)
Parameters
- Use parameters instead of embedding values in the SQL.
- Values sent as parameters will not be interpreted as SQL. (SQL injection preventation)
- Parameters are required for prepared statements. (Performance) Parameters | Npgsql Documentation
- Use positional parameter notation instead of named. (Performance)Positional and named placeholders | Npgsql Documentation
await using var cmd = new NpgsqlCommand("INSERT INTO table (col1) VALUES ($1), ($2)", conn) { Parameters = { new() { Value = "some_value" }, new() { Value = "some_other_value" } } }; await cmd.ExecuteNonQueryAsync();
Functions (Stored Procedures)
- The difference between Postgres functions and stored procedures (after Postgres 11) is relatively small, and mostly related to functionality - not security or performance.
- Calling a function instead of embedding the SQL in a Npgsql command in C# will not prevent SQL injection attacks alone. (See Parameters)
- If a function calls an unsafe function from its body, and pass a parameter value to the it, it could negate the SQL injection prevention that parameters provide.
Prepared statements
- Use prepared statements when you need performance.
- Use persistent prepared statements (Npgsql 3.2) to avoid resets when connections are closed, when using connection pooling.