Golang.md

This article is about how to plan connections to PostgreSQL in Golang applications.

Recommended libraries

The standard PostgreSQL library is de-facto: lib/pq

Also, the quite popular library is: pgx

I do not impose the use of any libraries, and I make a description of how to use the specified.

lib/pq

The library implements the standard database/sql interface.

Of the connection settings, three parameters are worth noting:

• SetConnMaxLifetime - a tool to make expired connections, not relevant for us.
• SetMaxOpenConns - limit on the maximum number of connections.
• SetMaxIdleConns - sets the maximum number of idle connections.

From my point of view, SetConnMaxLifetime does not need to be installed and left by default.

SetMaxIdleConns should be equal to SetMaxOpenConns, because we have to ensure that the application is enough for all issued connections.

It is worth considering that the connections are established as required. That is, the initial size of the pool first becomes 0, and only then increases to the value of SetMaxOpenConns.

Also, in the connection string, you must specify binary_parameters = yes. This parameter is managed by the OPS team when it assigns you a staging/production connection. But I recommend specifying when you are developing.

The binary_parameters = yes parameter saves a network-round trip and effectively reduces the time query execution under OLTP load conditions.

pgx

This library does not implement database/sql and is a highly specialized thing.

Parameters that are worth paying attention to:

• Config.MaxConnections - similar to SetMaxOpenConns
• ConnConfig.PreferSimpleProtocol = true which is similar to binary_parameters = yes for lib/pq.

Please note that for this library there is no equivalent of SetMaxIdleConns.

Should I use prepared statements?

The short answer is NO.

You must enable binary_parameters = yes for lib/pq and PreferSimpleProtocol for pgx.

Connections to different sources

For applications that are massively working with the database, I highly recommend developing applications with multiple sources:

• Master connection - here we must do transactions with potential changes in data.
• Sync replica connection - here we must read-only transaction from recently updated data.
• Async replica connection - here we have cold reading data with a guaranteed time-lag (minutes).

Thus in the application, we get three connection pool.

it is worth making a reservation that a specific query cannot be bound to a connection. The transaction must be attached to one of the connection pool.

Master

This is a connection for writing/reading, where we are doing UPDATE/DELETE/INSERT in the transaction.

Sync replica

This is a read-only connection for reading recently updated data, where we are doing only SELECT.

Async replica

This is a connection in which we can only do SELECT transaction, but with data that is older than three days.

Query execution timeout and Golang context

Timeouts must be consistent. If you must respond to a user request at a specified time, then the query to the database should be limited.

You can limit the execution time of a query by specifying the statement_timeout, for example: set local statement_timeout = '1min'.

Each query can have its own timeout, and this leads to increases in the number of queries to the database. Moreover, statement_timeout affects each query separately, but not the transaction as a whole.

I highly recommend to you to use the context, it offers the automatic sending of pqCancel() for the queries that worked in context.

For example (limit the whole transaction with one-second limitation):

	ctx, cancel := context.WithTimeout(context.Background(), 1*time.Second)
	defer cancel()
	tx, err := db.BeginTx(ctx, nil) // start transaction with context
	if err != nil {
		panic(err)
	}
	defer tx.Commit()
	_, err = tx.Exec(`select pg_sleep(2)`)
	if err != nil {
		panic(err)
	}

Output:

2019-07-25 18:40:48.851 MSK [27185] ERROR:  canceling statement due to user request
2019-07-25 18:40:48.851 MSK [27185] STATEMENT:  select pg_sleep(2)
panic: pq: canceling statement due to user request

Note:

Unfortunately pgx BeginEx doesnt auto-rollback on context cancellation. This only works with lib/pq driver. In the above example, pgx will block execution and will wait for the to complete query.

Configuration parameters of your application

Summing up:

• The application should be able to work with three sources: Master/Sync/Async connections.
• For each source connection, the following settings must be controlled from config: max_connection.
• For lib/pq must be fixed IdleConns == MaxOpenConns.
• For pgx it should be possible to control PreferSimpleProtocol.

prepared.md

Prepared statements

We cannot use prepared statements, like this:

tx.Exec(`select from table where id = $1`, id)

Workaround: but we can use binary protocol:

This protocol disables anonymous prepared statements, since the data in PostgreSQL do not come in default text but in binary format.

Which leads to:

• We do not use additional network round-trip to send parameters which we must attach to anonymous prepared statements after prepare.
• At the same time we are protected from SQL injections.

Libraries that support it:

• github.com/lib/pq by adding binary_parameters=yes to connection string
• github.com/jackc/pgx by set ConnConfig.PreferSimpleProtocol=true

This approach is used:

• Twitch
• Avito