Moksh

Posted on Jul 15

Go with PostgreSQL: Best Practices for Performance and Safety

Go and PostgreSQL make a rock-solid backend pairing fast, efficient, and well-tested. But getting the most out of it means paying attention to the details.

In this post, we'll cover some real-world best practices for using PostgreSQL from Go with examples, edge cases, and mistakes you (hopefully) only make once.

1. Use the Right Driver

If you're building a new Go service today, prefer pgx over the older lib/pq driver. It's faster, better maintained, and gives you more control.

If you want to keep using the database/sql interface while benefiting from pgx under the hood, use the stdlib wrapper:

import (
    "database/sql"
    "github.com/jackc/pgx/v5/stdlib"
)

db, err := sql.Open("pgx", "postgres://user:pass@localhost/dbname")

You get better performance with familiar ergonomics. Just make sure you manage connections yourself (more on that next).

2. Manage Connection Pooling

Go won’t automatically protect you from flooding Postgres with open connections. You must set sane limits:

db.SetMaxOpenConns(25)
db.SetMaxIdleConns(5)
db.SetConnMaxLifetime(time.Hour)

These values depend on your workload and database capacity. Monitor PostgreSQL using pg_stat_activity to keep an eye on active, idle, and waiting connections.

3. Use Prepared Statements (Especially in Loops)

If you’re executing the same SQL repeatedly especially inside loops or goroutines use prepared statements. This avoids re-parsing and guards against SQL injection:

stmt, err := db.Prepare("SELECT * FROM users WHERE email=$1")
if err != nil {
    log.Fatal(err)
}
defer stmt.Close()

row := stmt.QueryRow("user@example.com")

Caution: Prepared statements are tied to the connection they were created on. Don’t share them across goroutines unless you really know what you’re doing.

4. Handle Null Values Properly

PostgreSQL loves NULL, but Go does not.

Trying to scan a NULL into a plain string or int will cause errors. Use sql.NullString, sql.NullInt64, or a helper library like guregu/null.

type User struct {
    Name sql.NullString
    Age  sql.NullInt64
}

Always check .Valid before accessing the value:

if user.Name.Valid {
    fmt.Println("Name:", user.Name.String)
}

Verbose? Yes. Safe? Absolutely. Null handling bugs can silently corrupt your data.

5. Timezones Can Be Sneaky

Always store time in UTC using PostgreSQL’s timestamptz type and convert to local time only on the frontend.

Here’s why:

What not to do:

eventTime := time.Now() // Local system time, maybe IST
db.Exec("INSERT INTO events (starts_at) VALUES ($1)", eventTime)

If the column is timestamp (without time zone), Postgres stores the value as-is, with no timezone info. So 2025-07-15 10:00:00 IST becomes just that.

Later, when a UTC user fetches the event:

row := db.QueryRow("SELECT starts_at FROM events WHERE id=$1", eventID)
row.Scan(&eventTime)
fmt.Println(eventTime.UTC())

This prints 2025-07-15 10:00:00 UTC off by 5.5 hours. Oops.

Fix it like this:

Use timestamptz in your schema:

ALTER TABLE events 
ADD COLUMN starts_at TIMESTAMPTZ;

Always insert UTC times from Go:

eventTime := time.Now().UTC()
db.Exec("INSERT INTO events (starts_at) VALUES ($1)", eventTime)

Let the frontend handle local-time conversion that’s where timezones actually matter to users.

Bonus: You can also force the DB session to use UTC:

db.Exec("SET TIME ZONE 'UTC'")

This avoids accidental shifts during queries or reporting.

Final Thoughts

Using Postgres in Go isn’t difficult but doing it well requires intention, especially around connections, data types, and time handling.

Use pgx with database/sql if you want speed + familiarity
Always manage your connection pool to avoid overload
Prepare statements, especially inside loops
Handle NULL explicitly with the right types
Store timestamps in UTC, display them in the user’s timezone

Your users may never thank you but your future self (and your database) definitely will.

If you found this helpful, drop a ❤️ or comment. What are your favorite Go + Postgres tips?

DEV Community