HexaCluster Logo

PostgreSQL Client-side connection pooling in Golang using pgxpool

Introduction

When building production-grade applications in Go that interact with PostgreSQL, efficient database connection management is crucial for performance and reliability. While you might start with single connections using the pgx driver, as your application scales, you’ll need a more sophisticated approach to handle multiple concurrent database operations. This is where connection pools, specifically pgx/v5/pgxpool, come into play.

PostgreSQL connection pooling in Golang using pgxpool

Why Connection Pools Matter

Imagine your Go application suddenly experiences a surge in traffic. Without proper connection pooling, each request spawns a new database connection, overwhelming your PostgreSQL instance. This leads to performance degradation, increased latency, and even service crashes.
Connection pools solve this problem by efficiently managing and reusing database connections, ensuring scalability and reliability.

Previously, we’ve discussed Connecting to PostgreSQL with Go using PGX. If you’re curious about the fundamentals, check it out before diving into connection pooling!

Why Use Connection Pools?

Before diving into implementation, let’s understand the key benefits of using connection pools:

1. Performance Optimization

  • Creating new database connections is resource intensive.
  • Connection pools reuse existing connections instead of creating new ones.
  • This significantly reduces connection overhead and lowers query latency.

2. Resource Management

  • Controls the number of concurrent connections to the database.

  • Prevents database overload, ensuring stable performance.

  • Optimizes memory usage, especially in high-concurrency environments.

3.Connection Lifecycle Management

  • Automatically handles broken connections

  • Manages connection timeouts

  • Performs health checks to ensure available and healthy connections.

Implementation Guide

Complete source code is available at Github go-pgxpool. Check it out later.

Setting Up the Project

First, let’s set up our project with the necessary dependencies:

go get github.com/jackc/pgx/v5/pgxpool
go get github.com/spf13/viper

Database Configuration

We’ll start by defining our database configuration structure:

type DBConfig struct {
    Host              string `mapstructure:"PG_HOST"`
    Port              int    `mapstructure:"PG_PORT"`
    UserName          string `mapstructure:"PG_USERNAME"`
    Password          string `mapstructure:"PG_PASSWORD"`
    DBName            string `mapstructure:"PG_DBNAME"`
    MaxConns          int32
    MinConns          int32
    MaxConnLifeTime   time.Duration
    MaxConnIdleTime   time.Duration
    HealthCheckPeriod time.Duration
}

This structure provides a clean way to manage all database-related configuration parameters.

Application Configuration

Define the App struct to hold the database client:

type App struct {
    DBClient *pgxpool.Pool
}

Creating the Connection Pool

Let’s look at our main function that sets up the connection pool:

func main() {
    // Create a root context with cancellation
    rootCtx, cancel := context.WithCancel(context.Background())
    defer cancel()
    // Initialize database configuration from environment variables
    dbConfig, err := LoadConfig(".env") // Change for yaml or json. ex: config.yaml
    if err != nil { 
        slog.Info("Error loading config", slog.String("error=", err.Error()))
    }
    dbConfig.MaxConns = 10
    dbConfig.MinConns = 2                        // Minimum connections in the pool, default is 0
    dbConfig.MaxConnLifeTime = 30 * time.Minute  // Maximum connection lifetime, default is one hour
    dbConfig.MaxConnIdleTime = 10 * time.Minute  // Maximum idle time, default is 30 minutes
    dbConfig.HealthCheckPeriod = 2 * time.Minute // Health check frequency, default is 60 seconds
    slog.Info("config", slog.Any("c=", dbConfig))
    // Create the connection pool
    db, err := NewPg(rootCtx, dbConfig, WithPgxConfig(dbConfig))
    if err != nil {
        slog.Error("Error connecting to database", slog.String("error", err.Error()))
        panic(err)
    }
    defer db.Close()
    app := &App{
        DBClient: db,
    }
    slog.Info("Application started successfully!")
}

func LoadConfig(configFile string) (*DBConfig, error) {
    var cfg DBConfig
    // Set file name for environment configuration
    viper.SetConfigFile(configFile)
    viper.AutomaticEnv() // Read environment variables
    // Read the configuration file
    if err := viper.ReadInConfig(); err != nil {
        return nil, err
    }
    // Unmarshal into DBConfig
    if err := viper.Unmarshal(&cfg); err != nil {
        return nil, err
    }
    return &cfg, nil
}

HexaRocket Launched by the Creators of Ora2Pg
HexaRocket is the world's first AI-enabled Database Migration Service simplifying Oracle to PostgreSQL database migrations including data migration and continuous replication.
Request us for a free migration

https://hexarocket.com/
Please enable JavaScript in your browser to complete this form.
Type of Migration assistance

Pool Configuration and Creation

We’ll implement two approaches to create the connection pool as follows.

1.Basic Configuration (Simpler but Less Configurable. Comes with default settings)

func NewBasicPg(ctx context.Context, dbConfig *DBConfig) (*pgxpool.Pool, error) {
    // Connection URL
    connString := fmt.Sprintf("postgresql://%s:%s@%s:%d/%s",
        dbConfig.UserName, dbConfig.Password, dbConfig.Host, dbConfig.Port, dbConfig.DBName)
    // Create a connection pool
    pool, err := pgxpool.New(ctx, connString)
    if err != nil {
        slog.Error("Unable to create connection pool", slog.String("error", err.Error()))
        return nil, err
    }
    // Verify the connection
    if err = pool.Ping(ctx); err != nil {
        return nil, fmt.Errorf("unable to ping database: %w", err)
    }
    slog.Info("Successfully connected to database")
    return pool, nil
}

2.Advanced Configuration (Recommended for Production)-

// Create a pgx connection config from DBConfig
func WithPgxConfig(dbConfig *DBConfig) *pgx.ConnConfig {
    // Create the dsn string
    connString := strings.TrimSpace(fmt.Sprintf(
        "user=%s password=%s dbname=%s host=%s port=%d",
        dbConfig.UserName, dbConfig.Password, dbConfig.DBName,
        dbConfig.Host, dbConfig.Port))
    config, err := pgx.ParseConfig(connString)
    if err != nil {
        slog.Error("Error parsing connection config", slog.String("error", err.Error()))
        panic(err)
    }
    return config
}
// Create a new connection pool with the provided configuration
func NewPg(ctx context.Context, dbConfig *DBConfig, pgxConfig *pgx.ConnConfig) (*pgxpool.Pool, error) {
    var db *pgxpool.Pool
    // Parse the pool configuration from connection string
    config, err := pgxpool.ParseConfig(pgxConfig.ConnString())
    if err != nil {
        slog.Error("Error parsing pool config", slog.String("error", err.Error()))
        return nil, err
    }
    // Apply pool-specific configurations
    config.MaxConns = dbConfig.MaxConns
    config.MinConns = dbConfig.MinConns
    config.MaxConnLifetime = dbConfig.MaxConnLifeTime
    config.MaxConnIdleTime = dbConfig.MaxConnIdleTime
    config.HealthCheckPeriod = dbConfig.HealthCheckPeriod
    // Ensure singleton instance of the pool
    pgOnce.Do(func() {
        db, err = pgxpool.NewWithConfig(ctx, config)
    })
    // Verify the connection
    if err = db.Ping(ctx); err != nil {
        slog.Error("Unable to ping database", slog.String("error", err.Error()))
        return nil, fmt.Errorf("unable to ping database: %w", err)
    }
    slog.Info("Successfully connected to database")
    return db, nil
}

Notice that we have used different format for building the connection strings. The first one is URI and the other is libpq(key-value) format. Using pgx, both can be used interchangeably.

Working with the Connection Pool

There are two main approaches to using the connection pool.

1. Direct Pool Usage (Recommended for Most Cases)

This approach lets the pool handle connection management. This internally calls the Acquire function to get the connection from the pool.

func (app *App) DoDirectPoolOperations(ctx context.Context) error {
    // Simple query directly using pool
    var userCount int
    err := app.DBClient.QueryRow(ctx,
        "SELECT COUNT(*) FROM users").Scan(&userCount)
    if err != nil {
        return fmt.Errorf("error executing query: %v", err)
    }
    slog.Info("User count", slog.Int("count", userCount))
    // Multiple rows query
    rows, err := app.DBClient.Query(ctx,
        "SELECT id, name FROM users")
    if err != nil {
        return fmt.Errorf("error querying users: %v", err)
    }
    for rows.Next() {
        var (
            id   int
            name string
        )
        err = rows.Scan(&id, &name)
        if err != nil {
            return fmt.Errorf("error reading user: %v", err)
        }
        slog.Info("User retrieved", slog.Int("id", id), slog.String("name", name))
    }
    defer rows.Close()
    return nil
}

2. Explicit Connection Acquisition

Use this approach when you need more control over the connection.

func (app *App) DoExplicitConnectionOperations(ctx context.Context) error {
    // Acquire connection explicitly
    conn, err := app.DBClient.Acquire(ctx)
    if err != nil {
        return fmt.Errorf("error acquiring connection: %v", err)
    }
    defer conn.Release()
    type User struct {
        Id   int
        Name string
    }
    // Multiple rows query
    rows, err := conn.Query(ctx,
        "SELECT id, name FROM users")
    if err != nil {
        return fmt.Errorf("error querying users: %v", err)
    }
    users, err := pgx.CollectRows(rows, pgx.RowToAddrOfStructByNameLax[User])
    if err != nil {
        return fmt.Errorf("error reading rows: %v", err)
    }
    for _, user := range users {
        slog.Info("User retrieved", slog.Int("id", user.Id), slog.String("name", user.Name))
    }
    defer rows.Close()
    // Exec for insert/update/delete
    result, err := conn.Exec(ctx,
        "UPDATE users SET last_login = NOW() WHERE id = @id", pgx.NamedArgs{"id": 1})
    if err != nil {
        return fmt.Errorf("error updating user: %v", err)
    }
    slog.Info("Rows affected", slog.Int64("rows_affected", result.RowsAffected()))
    return nil
}

If you observe, I’ve used pgx.CollectRows combined with pgx.RowToAddrOfStructByNameLax to collect all the records returned by the query. We’ll explore more functions like these in the upcoming articles.

Monitoring Pool Statistics

func (app *App) monitorPoolStats() {
    stats := app.DBClient.Stat()
    slog.Info("Pool stats",
        slog.Int("total_connections", int(stats.TotalConns())),
        slog.Int("acquired_connections", int(stats.AcquiredConns())),
        slog.Int("idle_connections", int(stats.IdleConns())),
        slog.Int("max_connections", int(stats.MaxConns())),
    )
}

Application flow and Pool Maintenance in action

Application flow

Pool Maintenance

Best Practices and Recommendations

1.Configuration Values

  • Set MaxConns based on your application’s concurrency needs

  • Keep some minimum connections (MinConns) ready for better performance

  • Use reasonable connection lifetime values to prevent stale connections

2.Error Handling

  • Always handle connection errors appropriately

  • Use context for timeout management

  • Implement proper connection release in defer statements

3.Connection Usage

  • Use direct pool methods for simple operations

  • Only acquire explicit connections when necessary

  • Always close rows after queries

  • Use proper context management

4.Monitoring

  • Regularly monitor pool statistics

  • Log connection errors and pool state

  • Set up alerts for pool exhaustion

Conclusion

Connection pooling with pgx/v5/pgxpool provides a robust solution for managing database connections in Go applications. By following these patterns and best practices, you can build efficient and reliable database interactions in your applications.

The code examples provided demonstrate production-ready implementations that you can adapt to your specific needs. Remember to always consider your application’s specific requirements when configuring the connection pool parameters.

In the next article, we’ll explore advanced topics such as transaction management and batch operations using pgx/v5.

Subscribe to our Newsletters and Stay tuned for more interesting topics.

Contact Us Today!

Contact Us - Submission Form
Please enable JavaScript in your browser to complete this form.
Machine Learning Services
PostgreSQL Consulting Services
Migration Services

Author

  • Aditya Surishetti

    Aditya Suirshetti is a Software Development Engineer at HexaCluster, where he focuses on building robust solutions for real-world challenges. With a passion for problem-solving, Aditya leverages his expertise in Go, Node.js, and Python to develop innovative applications. He possesses extensive knowledge of Java and C++, enabling him to tackle diverse programming tasks with precision and efficiency. Additionally, Aditya is curious about machine learning and generative AI, adding more tools to his toolbox for tackling new challenges. He's also passionate about PostgreSQL and interested in working on open-source projects, further enriching his understanding of the technological landscape. Aditya's commitment to learning and staying updated makes him a valuable software development team member at HexaCluster.

Add A Comment

We're glad you have chosen to leave a comment. Please keep in mind that all comments are moderated according to our privacy policy, and all links are nofollow. Do NOT use keywords in the name field. Let's have a personal and meaningful conversation.

Our PostgreSQL Services

Our Machine Learning Services

Share article

HexaCluster Logo

Get in Touch

© 2023 Company. All Rights Reserved. Privacy Policy.