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.

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
}

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

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.