Logo

dev-resources.site

for different kinds of informations.

Advanced PostgreSQL Development with pgx in Go: A Deep Dive

Published at
1/10/2025
Categories
go
backend
database
sql
Author
indalyadav56
Main Article
https://dev.to/indalyadav56/advanced-postgresql-development-with-pgx-in-go-a-deep-dive-3keo
Categories
4 categories in total
go
open
backend
open
database
open
sql
open
Author
12 person written this
indalyadav56
open
Advanced PostgreSQL Development with pgx in Go: A Deep Dive

While the standard database/sql package and sqlx are great general-purpose database tools, pgx offers PostgreSQL-specific features that can significantly improve your application's performance and developer experience. Let's explore how to leverage pgx's powerful capabilities.

Why Choose pgx?

pgx offers several PostgreSQL-specific advantages:

  • Better performance than lib/pq
  • Native support for PostgreSQL data types
  • Automatic prepared statement caching
  • Connection pooling with pgxpool
  • COPY protocol support
  • Listen/Notify support
  • Custom type mapping
  • Batch operations

Getting Started

First, let's set up a new project:

mkdir go-pgx-demo
cd go-pgx-demo
go mod init go-pgx-demo
go get github.com/jackc/pgx/v5
Enter fullscreen mode Exit fullscreen mode

Basic Setup and Connection 

package main

import (
    "context"
    "fmt"
    "os"

    "github.com/jackc/pgx/v5"
    "github.com/jackc/pgx/v5/pgxpool"
)

type Book struct {
    ID            int    
    Title         string
    AuthorID      int    
    PublishedYear int    
    Data          pgx.JSONB // Native PostgreSQL JSONB support
}

func main() {
    // Connection URL with additional options
    dbURL := "postgres://username:password@localhost:5432/bookstore?sslmode=disable&pool_max_conns=10"

    // Create a connection pool
    config, err := pgxpool.ParseConfig(dbURL)
    if err != nil {
        fmt.Fprintf(os.Stderr, "Failed to parse config: %v\n", err)
        os.Exit(1)
    }

    // Custom pool configuration
    config.MaxConns = 10
    config.MinConns = 2

    pool, err := pgxpool.NewWithConfig(context.Background(), config)
    if err != nil {
        fmt.Fprintf(os.Stderr, "Unable to connect to database: %v\n", err)
        os.Exit(1)
    }
    defer pool.Close()
}
Enter fullscreen mode Exit fullscreen mode

Advanced Features and Examples

1. Batch Operations 

func BatchInsertBooks(ctx context.Context, pool *pgxpool.Pool, books []Book) error {
    batch := &pgx.Batch{}

    for _, book := range books {
        batch.Queue(`
            INSERT INTO books (title, author_id, published_year, data)
            VALUES ($1, $2, $3, $4)`,
            book.Title, book.AuthorID, book.PublishedYear, book.Data)
    }

    br := pool.SendBatch(ctx, batch)
    defer br.Close()

    return br.Close()
}
Enter fullscreen mode Exit fullscreen mode

2. COPY Protocol 

func BulkImportBooks(ctx context.Context, pool *pgxpool.Pool, books []Book) error {
    _, err := pool.Exec(ctx, `
        CREATE TEMPORARY TABLE temp_books (
            title TEXT,
            author_id INTEGER,
            published_year INTEGER,
            data JSONB
        )`)
    if err != nil {
        return fmt.Errorf("failed to create temp table: %v", err)
    }

    copyCount, err := pool.CopyFrom(
        ctx,
        pgx.Identifier{"temp_books"},
        []string{"title", "author_id", "published_year", "data"},
        pgx.CopyFromSlice(len(books), func(i int) ([]interface{}, error) {
            return []interface{}{
                books[i].Title,
                books[i].AuthorID,
                books[i].PublishedYear,
                books[i].Data,
            }, nil
        }),
    )

    if err != nil {
        return fmt.Errorf("copy failed: %v", err)
    }

    fmt.Printf("Copied %d records\n", copyCount)
    return nil
}
Enter fullscreen mode Exit fullscreen mode

3. Listen/Notify for Real-time Updates 

func ListenForBookUpdates(ctx context.Context, pool *pgxpool.Pool) error {
    conn, err := pool.Acquire(ctx)
    if err != nil {
        return err
    }
    defer conn.Release()

    _, err = conn.Exec(ctx, "LISTEN book_updates")
    if err != nil {
        return err
    }

    for {
        notification, err := conn.Conn().WaitForNotification(ctx)
        if err != nil {
            return err
        }

        fmt.Printf("Received notification on channel %s: %s\n",
            notification.Channel, notification.Payload)
    }
}

// Trigger notification
func NotifyBookUpdate(ctx context.Context, pool *pgxpool.Pool, bookID int) error {
    _, err := pool.Exec(ctx, 
        "SELECT pg_notify('book_updates', $1)",
        fmt.Sprintf("Book %d updated", bookID))
    return err
}
Enter fullscreen mode Exit fullscreen mode

4. Custom Type Handling 

type ISBN string

func (isbn *ISBN) ScanISBN(v interface{}) error {
    if v == nil {
        *isbn = ""
        return nil
    }

    switch v := v.(type) {
    case string:
        *isbn = ISBN(v)
        return nil
    case []byte:
        *isbn = ISBN(string(v))
        return nil
    }

    return fmt.Errorf("cannot scan %T into ISBN", v)
}

type BookWithISBN struct {
    ID    int
    Title string
    ISBN  ISBN
}

func RegisterCustomTypes(conn *pgx.Conn) error {
    isbn := pgtype.NewTextCodec(nil)
    conn.TypeMap().RegisterType(&pgtype.Type{
        Name:  "isbn",
        OID:   pgtype.TextOID,
        Codec: isbn,
    })
    return nil
}
Enter fullscreen mode Exit fullscreen mode

5. Transaction Management 

func TransferBooks(ctx context.Context, pool *pgxpool.Pool, fromAuthorID, toAuthorID int) error {
    return pool.BeginTxFunc(ctx, pgx.TxOptions{
        IsoLevel:   pgx.Serializable,
        AccessMode: pgx.ReadWrite,
    }, func(tx pgx.Tx) error {
        // Update books
        _, err := tx.Exec(ctx, `
            UPDATE books 
            SET author_id = $1 
            WHERE author_id = $2`,
            toAuthorID, fromAuthorID)
        if err != nil {
            return err
        }

        // Update author statistics
        _, err = tx.Exec(ctx, `
            UPDATE author_stats 
            SET book_count = (
                SELECT COUNT(*) 
                FROM books 
                WHERE author_id = $1
            )
            WHERE author_id IN ($1, $2)`,
            toAuthorID, fromAuthorID)
        return err
    })
}
Enter fullscreen mode Exit fullscreen mode

6. Query Row Scanning 

func GetBookDetails(ctx context.Context, pool *pgxpool.Pool, bookID int) (*Book, error) {
    var book Book
    err := pool.QueryRow(ctx, `
        SELECT id, title, author_id, published_year, data
        FROM books
        WHERE id = $1`, bookID).Scan(
        &book.ID,
        &book.Title,
        &book.AuthorID,
        &book.PublishedYear,
        &book.Data,
    )

    if err == pgx.ErrNoRows {
        return nil, fmt.Errorf("book not found")
    }

    return &book, err
}
Enter fullscreen mode Exit fullscreen mode

Performance Optimization

1. Connection Pool Tuning 

func ConfigurePool(config *pgxpool.Config) {
    config.MaxConns = 20
    config.MinConns = 5
    config.MaxConnLifetime = time.Hour
    config.MaxConnIdleTime = 30 * time.Minute
    config.HealthCheckPeriod = time.Minute

    // Custom connection configuration
    config.ConnConfig.RuntimeParams["application_name"] = "MyApp"
    config.ConnConfig.PreferSimpleProtocol = true
}
Enter fullscreen mode Exit fullscreen mode

2. Prepared Statement Cache 

func ConfigurePreparedStatements(config *pgxpool.Config) {
    // Disable prepared statement caching if needed
    config.ConnConfig.PreferSimpleProtocol = true

    // Or configure cache size
    config.ConnConfig.StatementCacheCapacity = 512
}
Enter fullscreen mode Exit fullscreen mode

Best Practices

  1. Use Connection Pooling 
// Don't create individual connections
pool, err := pgxpool.New(context.Background(), dbURL)
Enter fullscreen mode Exit fullscreen mode
  1. Handle Context Properly 
ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
defer cancel()
Enter fullscreen mode Exit fullscreen mode
  1. Use Transactions for Multiple Operations 
err := pool.BeginTxFunc(ctx, pgx.TxOptions{}, func(tx pgx.Tx) error {
    // Multiple operations here
    return nil
})
Enter fullscreen mode Exit fullscreen mode
  1. Proper Resource Cleanup 
rows, err := pool.Query(ctx, "SELECT * FROM books")
if err != nil {
    return err
}
defer rows.Close()
Enter fullscreen mode Exit fullscreen mode

Common Pitfalls and Solutions

  1. Connection Leaks: Always release acquired connections
  2. Transaction Management: Use BeginTxFunc for automatic cleanup
  3. Resource Exhaustion: Configure pool sizes appropriately
  4. Error Handling: Check for specific error types

Monitoring and Debugging 

type QueryTracer struct {
    logger *log.Logger
}

func (qt *QueryTracer) TraceQueryStart(ctx context.Context, conn *pgx.Conn, data pgx.TraceQueryStartData) context.Context {
    qt.logger.Printf("Query started: %s", data.SQL)
    return ctx
}

func (qt *QueryTracer) TraceQueryEnd(ctx context.Context, conn *pgx.Conn, data pgx.TraceQueryEndData) {
    qt.logger.Printf("Query completed in %s", data.Duration)
}
Enter fullscreen mode Exit fullscreen mode

Integration with Other Tools

pgx works well with:

  • sqlc for query generation
  • migrate for database migrations
  • postgresql-contrib for additional features

Conclusion

pgx is the go-to choice for PostgreSQL applications in Go when you need:

  • Maximum performance
  • PostgreSQL-specific features
  • Advanced connection pooling
  • Real-time notifications
  • Batch operations

Resources

Happy coding with pgx! πŸš€

sql Article's
30 articles in total
Favicon
SQL Performance Tuning: Best Practices for Faster Queries
Favicon
Simple SQL Generator using AWS Bedrock
Favicon
Uses of Snowflake Schema
Favicon
Why Successful Companies Don't Have DBAs
Favicon
Explaining DELETE vs DROP vs TRUNCATE in SQL
Favicon
Like IDE for SparkSQL: Support Pycharm! SparkSQLHelper v2025.1.1 released
Favicon
PostgreSQL plan_cache_mode
Favicon
Primeiros Passos no PostgreSQL: Um Guia Completo para Iniciantes
Favicon
SQL Injection - In Just 5 Minutes!
Favicon
What are the benefits of using bounded quantifiers in regex
Favicon
Find logged Microsoft SQL Server Messages
Favicon
RAG - Creating the SQLite database and config file
Favicon
Front-End to Full-Stack Journey Part 3: Server Proxies, React and Progress
Favicon
How to Simplify Oracle Databases with a GUI Tool
Favicon
Mastering SQL Joins - Inner, Outer, Cross, and Self-Joins with Examples
Favicon
SQL 101 | Chapter 3: Mastering Data Retrieval with SELECT Statements
Favicon
Observability 2.0 - The Best Thing Since Sliced Bread
Favicon
Bringing Together Containers & SQL
Favicon
Advanced PostgreSQL Development with pgx in Go: A Deep Dive
Favicon
A Guide to Manage Access in SQL - GRANT, REVOKE, and Access Control
Favicon
Explaining Relation and Data Independence in SQL
Favicon
Supercharge Your Go Database Layer with sqlc: Type-Safe SQL Made Easy
Favicon
A Quick Guide to SQL Data Modification Commands with Examples
Favicon
Search for the closest matching record within the group：From SQL to SPL
Favicon
Mastering SAP SD: Streamlining Sales and Distribution
Favicon
To work with an actual SQL Server, you'll need to install and set up the SQL Server environment, create a database, and then interact with it using SQL queries. Here's a step-by-step guide: 1. Install SQL Server Read more
Favicon
SQL VS NoSQL
Favicon
This Is Probably the Most Perfect Programming for Solving SQL Data Analysis Pain Points
Favicon
Subqueries
Favicon
Calculate monthly account balance and fill in missing dates:From SQL to SPL

Featured ones:

Favicon
Open
abubakersiddique761
Favicon
Open
0x3d_site
Favicon
Open
0x2e_tech
Favicon
Open
0x4c-quest
Favicon
Open
gittech