Reducing Network Round-Trips in PostgreSQL when using pgx in Go

Photo by Nathan Dumlao on Unsplash

When working with PostgreSQL databases, it’s common to perform multiple transactions in a single request. For instance, you might need to insert or update a large number of records, which affect multiple tables and you want to ensure that they all succeed or fail together. When using the PGX library standard transaction calls, each statement sends a request to the server, which can be inefficient when working with a large number of SQL statements. The library does include a Batch option which works really well. (Great job!) but I did find the documentation a bit light. I therefore made this a wrapper to easily batch these queries.

Reducing the number of network round-trips is important because it minimizes the likelihood of network failures, which can cause your transactions to fail or rollback. By bundling multiple SQL statements into a single transaction, you can reduce the number of round-trips and improve the reliability of your application.

To solve this problem, we created pgxbatcher, a utility package for executing batches of SQL statements with transaction support using the PGX database driver. With pgxbatcher, you can queue up multiple SQL statements in a single batch and execute them all in one network round-trip.

Here’s an example of how to use batch sql queries using pgxwrapper and pgx.Batch:

import (
 "context"
 "github.com/townsymush/pgxbatcher"
 "github.com/jackc/pgx/v4"
)

func main() {
    // create a connection your PostgreSQL database
    connString := "postgresql://username:password@localhost:5432/mydb"
    conn, err := pgx.Connect(context.Background(), connString)
    if err != nil {
        // handle err
    }
    defer conn.Close()

    // create a new batcher
    batcher := pgxbatcher.New(conn, true) // true wraps queue in transaction

    // queue up some SQL statements
    batcher.Queue("INSERT INTO users (name, email) VALUES ($1, $2)", []interface{}{"Alice", "alice@example.com"})
    batcher.Queue("INSERT INTO users (name, email) VALUES ($1, $2)", []interface{}{"Bob", "bob@example.com"})

    // execute the batch
    err = batcher.Execute(context.Background())
    if err != nil {
        fmt.Println(err.Errors)
        // or you can type cast to pgxbatcher.StatementErrors 
        // to access the underlying errors for each query.
    }
}

In this example, we create a connection to our database and create a new batcher with transaction support. We then queue up two SQL statements to insert records into our “users” table, and execute the batch. If any errors occur during execution, they will be returned as a StatementErrors value.

Overall, pgxbatcher provides a simple and efficient way to reduce the number of network round-trips when executing batches of SQL statements with transaction support using the pgx database driver. By bundling multiple SQL statements into a single batch, you can improve the reliability and performance of your PostgreSQL transactions.

“Thank you for reading! If you enjoyed this post, please consider following me for more content like this. And if you found this utility package helpful, please give it a star on Github. Your support helps me to continue creating useful tools and content for the developer community. Happy coding!”