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.
Contact Us Today!