N+1 is just the tip of the iceberg. Beneath it lie Cartesian Explosion, Lost Update, God Table, Replication Lag, ORM Trap, and 15 other far more dangerous problems — complete with real-world case studies and Golang solutions.
"Every time a production system suddenly slows down for no apparent reason, the database is always the prime suspect — and almost always guilty."
N+1 queries are like pimples — easy to spot, everyone knows how to treat them. The dangerous ones are the internal diseases that go undetected until the system collapses in production. This article covers 20 database problems across 5 different categories, each with real-world case studies and practical Golang solutions.
Database problems aren't just about slow queries. There are 5 major categories to understand:
Problems with how queries are written and executed
Problems with write operations and concurrency
Problems with data integrity and isolation
Problems with table structure design
Problems with infrastructure and ORM
What's the Problem?
Occurs when two tables are JOINed without a proper condition. The result: the row count of both tables multiplies, potentially reaching millions of rows in seconds.
Case Study
An e-commerce startup with products (50,000 rows) and tags (200 rows):
-- Looks innocent, but this is a time bomb
SELECT p.*, t.*
FROM products p, tags t
WHERE p.category = 'electronics';
-- Result: 50,000 × 200 = 10,000,000 rowsDatabase server crashed within 3 minutes after the feature was deployed.
Solution in Golang
// ❌ Dangerous: implicit cross join
func GetProductsWithTagsBad(db *sql.DB) ([]ProductTag, error) {
rows, err := db.Query(`
SELECT p.id, p.name, t.name as tag_name
FROM products p, tags t
WHERE p.category = 'electronics'
`)
return scanRows(rows, err)
}
// ✅ Correct: explicit INNER JOIN with condition
func GetProductsWithTagsGood(db *sqlx.DB, category string, limit, offset int) ([]Product, error) {
var products []Product
err := db.Select(&products, `
SELECT p.id, p.name, p.price
FROM products p
INNER JOIN product_tags pt ON pt.product_id = p.id
INNER JOIN tags t ON t.id = pt.tag_id
WHERE p.category = $1
ORDER BY p.id
LIMIT $2 OFFSET $3
`, category, limit, offset)
if err != nil {
return nil, fmt.Errorf("get products: %w", err)
}
productIDs := extractIDs(products)
tags, err := getTagsByProductIDs(db, productIDs)
if err != nil {
return nil, err
}
return mergeProductTags(products, tags), nil
}Every time you write a JOIN without an explicit ON, always ask: "What's the maximum number of rows this could return?" If you can't answer with certainty — there's a problem.
What's the Problem?
A query without LIMIT fetches all matching data. In development with 100 rows, it's unnoticeable. In production with 5 million rows? Gigabytes of memory gone in an instant.
Case Study
A digital content platform with 3 million articles. The "export all articles" feature worked fine for 2 years. In year 3, a single click of the export button caused 45 minutes of downtime due to server OOM.
Solution in Golang
// ✅ Cursor-based streaming for large data exports
func ExportArticlesCursor(ctx context.Context, db *sql.DB, batchSize int) (<-chan Article, <-chan error) {
articles := make(chan Article, batchSize)
errc := make(chan error, 1)
go func() {
defer close(articles)
defer close(errc)
lastID := 0
for {
batch, err := fetchBatch(ctx, db, lastID, batchSize)
if err != nil {
errc <- err
return
}
if len(batch) == 0 {
return
}
for _, article := range batch {
select {
case articles <- article:
case <-ctx.Done():
errc <- ctx.Err()
return
}
}
lastID = batch[len(batch)-1].ID
}
}()
return articles, errc
}
func fetchBatch(ctx context.Context, db *sql.DB, afterID, limit int) ([]Article, error) {
rows, err := db.QueryContext(ctx, `
SELECT id, title, content, status, created_at
FROM articles
WHERE status = 'published' AND id > $1
ORDER BY id ASC
LIMIT $2
`, afterID, limit)
if err != nil {
return nil, err
}
defer rows.Close()
return scanArticles(rows)
}What's the Problem?
Filtering a query on a column without an index forces the database to read every row. This is O(n) — the larger the table, the slower it gets, linearly.
Case Study
A fintech company with a transactions table (50 million rows). Querying transaction history without an index: 8 seconds. After adding an index: 12ms. A 666x difference.
Solution in Golang
// ✅ Detect missing indexes with EXPLAIN ANALYZE
func AnalyzeQuery(ctx context.Context, db *sql.DB, query string, args ...interface{}) error {
explainQuery := "EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) " + query
var result []byte
if err := db.QueryRowContext(ctx, explainQuery, args...).Scan(&result); err != nil {
return err
}
if strings.Contains(string(result), "Seq Scan") {
log.Warn().Str("query", query).Msg("Sequential scan detected — consider adding index")
}
return nil
}
// ✅ Proper indexes for common query patterns
/*
-- Composite index for per-user transaction queries
CREATE INDEX CONCURRENTLY idx_transactions_user_created
ON transactions(user_id, created_at DESC);
-- Partial index for specific statuses only
CREATE INDEX CONCURRENTLY idx_transactions_pending
ON transactions(created_at) WHERE status = 'pending';
*/
// ✅ Query using composite index with cursor pagination
func GetUserTransactions(ctx context.Context, db *sql.DB, userID, limit int, cursor *time.Time) ([]Transaction, error) {
if cursor == nil {
rows, err := db.QueryContext(ctx, `
SELECT id, user_id, amount, status, created_at
FROM transactions
WHERE user_id = $1
ORDER BY created_at DESC LIMIT $2
`, userID, limit)
defer rows.Close()
return scanTransactions(rows, err)
}
rows, err := db.QueryContext(ctx, `
SELECT id, user_id, amount, status, created_at
FROM transactions
WHERE user_id = $1 AND created_at < $2
ORDER BY created_at DESC LIMIT $3
`, userID, cursor, limit)
defer rows.Close()
return scanTransactions(rows, err)
}Enable pg_stat_statements in PostgreSQL to see the slowest queries in production. Integrate OpenTelemetry in Golang to auto-trace every query with its duration.
What's the Problem?
SELECT * fetches all columns including large ones (BLOB, JSONB, long TEXT) that aren't needed. Network I/O and memory usage bloat for nothing.
Case Study
A /users/list API for a name dropdown. The users table has a profile_photo column (BYTEA, ~2MB) and settings column (JSONB, ~50KB). For 100 users: 205MB transferred from the DB — when all that's needed is id + name = ~2KB.
Solution in Golang
// ❌ Anti-pattern: fetch all columns
func GetAllUsersBad(db *sqlx.DB) ([]User, error) {
var users []User
return users, db.Select(&users, "SELECT * FROM users")
}
// ✅ Use a specific DTO per use case
type UserListItem struct {
ID int `db:"id"`
Name string `db:"name"`
}
func GetUserListForDropdown(db *sqlx.DB) ([]UserListItem, error) {
var users []UserListItem
return users, db.Select(&users, `
SELECT id, name FROM users
WHERE active = true ORDER BY name LIMIT 500
`)
}What's the Problem?
When the parameter type doesn't match the column type, the database is forced to cast — and the index on that column becomes unusable.
Case Study
A phone_number column of type VARCHAR with an index. The developer sends an int64 as a parameter. PostgreSQL has to cast every value to an integer — the query goes from 1ms to 8 seconds.
Solution in Golang
// ❌ Wrong: type doesn't match VARCHAR column
func GetUserByPhoneBad(db *sql.DB, phone int64) (*User, error) {
row := db.QueryRow("SELECT id, name FROM users WHERE phone_number = $1", phone)
var u User
return &u, row.Scan(&u.ID, &u.Name)
}
// ✅ Correct: use string matching the column type
type PhoneNumber string
func GetUserByPhoneGood(db *sql.DB, phone PhoneNumber) (*User, error) {
row := db.QueryRow("SELECT id, name FROM users WHERE phone_number = $1", string(phone))
var u User
return &u, row.Scan(&u.ID, &u.Name)
}WHERE created_at::date = '2026-01-01' is just as dangerous. Casting in a WHERE clause always invalidates indexes. Use: WHERE created_at >= '2026-01-01' AND created_at < '2026-01-02'.
What's the Problem?
A long-running transaction blocks all other operations on the same rows. In a concurrent system, this creates a growing queue until mass timeout.
Case Study
A concert ticketing platform. A ticket purchase transaction calls an external API (2 seconds) plus a payment gateway (3 seconds) inside a single database transaction. Result: massive deadlock, server unresponsive within 10 minutes.
Solution in Golang
// ❌ Anti-pattern: external calls inside a DB transaction
func BuyTicketBad(ctx context.Context, db *sql.DB, userID, ticketID int) error {
tx, _ := db.BeginTx(ctx, nil)
defer tx.Rollback()
tx.ExecContext(ctx, "UPDATE tickets SET status='reserved' WHERE id=$1", ticketID)
validateUserWithExternalAPI(userID) // ❌ 2 second lock!
processPayment(userID, ticketID) // ❌ 3 second lock!
return tx.Commit()
}
// ✅ Separate: validate → short lock → payment outside transaction
func BuyTicketGood(ctx context.Context, db *sql.DB, userID, ticketID int) error {
// Step 1: Validate BEFORE opening a transaction
if err := validateUserWithExternalAPI(userID); err != nil {
return fmt.Errorf("user validation: %w", err)
}
// Step 2: Short DB transaction — DB operations only
reservationID, err := reserveTicketSkipLocked(ctx, db, userID, ticketID)
if err != nil {
return fmt.Errorf("reserve ticket: %w", err)
}
// Step 3: Payment AFTER the DB transaction completes
if err := processPayment(userID, reservationID); err != nil {
_ = cancelReservation(ctx, db, reservationID) // compensating transaction
return fmt.Errorf("process payment: %w", err)
}
return confirmTicket(ctx, db, reservationID)
}
// ✅ SELECT FOR UPDATE SKIP LOCKED for high-concurrency scenarios
func reserveTicketSkipLocked(ctx context.Context, db *sql.DB, userID, ticketID int) (int, error) {
var id int
err := db.QueryRowContext(ctx, `
WITH locked AS (
SELECT id FROM tickets
WHERE id = $1 AND status = 'available'
FOR UPDATE SKIP LOCKED LIMIT 1
)
UPDATE tickets SET status = 'reserved', reserved_by = $2, reserved_at = NOW()
WHERE id IN (SELECT id FROM locked)
RETURNING id
`, ticketID, userID).Scan(&id)
if err == sql.ErrNoRows {
return 0, ErrTicketNotAvailable
}
return id, err
}What's the Problem?
Every INSERT or UPDATE operation doesn't just write to the table — it also writes to every index on that table. A table with 15 indexes can generate 16 write operations for 1 INSERT.
Case Study
An events table in an analytics system with 20 indexes (accumulated over years of "just in case" additions). Initial INSERT throughput was 10,000/sec. After auditing and removing 12 unused indexes: 47,000/sec — nearly 5x faster.
Solution in Golang
// ✅ Audit unused indexes in PostgreSQL
const unusedIndexQuery = `
SELECT
schemaname,
tablename,
indexname,
idx_scan,
pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND indexrelname NOT LIKE 'pg_%'
ORDER BY pg_relation_size(indexrelid) DESC;
`
func AuditUnusedIndexes(ctx context.Context, db *sql.DB) error {
rows, err := db.QueryContext(ctx, unusedIndexQuery)
if err != nil {
return err
}
defer rows.Close()
log.Info().Msg("=== Unused Indexes (candidates for removal) ===")
for rows.Next() {
var schema, table, index, size string
var scans int64
rows.Scan(&schema, &table, &index, &scans, &size)
log.Info().Str("index", index).Str("table", table).Str("size", size).Msg("unused index")
}
return rows.Err()
}
// ✅ For bulk inserts, use the COPY protocol via pgx (much faster)
func BulkInsertEvents(ctx context.Context, pool *pgxpool.Pool, events []Event) error {
_, err := pool.CopyFrom(
ctx,
pgx.Identifier{"events"},
[]string{"user_id", "event_type", "payload", "created_at"},
pgx.CopyFromSlice(len(events), func(i int) ([]interface{}, error) {
return []interface{}{
events[i].UserID,
events[i].Type,
events[i].Payload,
events[i].CreatedAt,
}, nil
}),
)
return err
}What's the Problem?
Thousands of goroutines writing to the same row or table simultaneously. The database must serialize all these writes one by one — creating a single bottleneck.
Case Study
An online game leaderboard system. The total_score column in the global_stats table is updated every time a player completes a mission. When 5,000 concurrent players are active, this table becomes a hot spot — p99 latency spikes to 12 seconds.
Solution in Golang
// ❌ Anti-pattern: direct update to a single row
func UpdateGlobalScoreBad(ctx context.Context, db *sql.DB, delta int) error {
_, err := db.ExecContext(ctx, `
UPDATE global_stats SET total_score = total_score + $1
`, delta)
return err
}
// ✅ Solution 1: Write buffer — accumulate updates, flush periodically
type ScoreBuffer struct {
mu sync.Mutex
delta int
}
var buffer = &ScoreBuffer{}
func AddScore(delta int) {
buffer.mu.Lock()
buffer.delta += delta
buffer.mu.Unlock()
}
func FlushScoreBuffer(ctx context.Context, db *sql.DB) error {
buffer.mu.Lock()
delta := buffer.delta
buffer.delta = 0
buffer.mu.Unlock()
if delta == 0 {
return nil
}
_, err := db.ExecContext(ctx, `
UPDATE global_stats SET total_score = total_score + $1
`, delta)
return err
}
// ✅ Solution 2: Sharded counter — split one row into N rows
func UpdateShardedScore(ctx context.Context, db *sql.DB, userID, delta int) error {
shardID := userID % 16 // 16 shards
_, err := db.ExecContext(ctx, `
INSERT INTO score_shards (shard_id, delta)
VALUES ($1, $2)
`, shardID, delta)
return err
}
func GetTotalScore(ctx context.Context, db *sql.DB) (int64, error) {
var total int64
err := db.QueryRowContext(ctx, `
SELECT COALESCE(SUM(delta), 0) FROM score_shards
`).Scan(&total)
return total, err
}What's the Problem?
Two goroutines perform a "check then insert" simultaneously. Both check "data doesn't exist yet" at the same time — then both INSERT — and one fails with a duplicate key error or, worse, duplicate data enters without an error.
Case Study
A notification system. Two events arrive simultaneously for the same user. Both check WHERE user_id = X → nothing found → both INSERT. Result: the user receives duplicate notifications twice.
Solution in Golang
// ❌ Anti-pattern: check-then-insert without atomicity
func CreateUserProfileBad(ctx context.Context, db *sql.DB, userID int) error {
var count int
db.QueryRowContext(ctx, "SELECT COUNT(*) FROM profiles WHERE user_id = $1", userID).Scan(&count)
// Race condition window: another goroutine could insert here!
if count == 0 {
_, err := db.ExecContext(ctx, "INSERT INTO profiles (user_id) VALUES ($1)", userID)
return err
}
return nil
}
// ✅ Use INSERT ... ON CONFLICT (atomic upsert)
func UpsertUserProfile(ctx context.Context, db *sql.DB, userID int, data ProfileData) error {
_, err := db.ExecContext(ctx, `
INSERT INTO profiles (user_id, bio, avatar_url, updated_at)
VALUES ($1, $2, $3, NOW())
ON CONFLICT (user_id) DO UPDATE SET
bio = EXCLUDED.bio,
avatar_url = EXCLUDED.avatar_url,
updated_at = NOW()
`, userID, data.Bio, data.AvatarURL)
return err
}
// ✅ Or use INSERT ... ON CONFLICT DO NOTHING for idempotent inserts
func EnsureUserExists(ctx context.Context, db *sql.DB, userID int) error {
_, err := db.ExecContext(ctx, `
INSERT INTO users (id, created_at)
VALUES ($1, NOW())
ON CONFLICT (id) DO NOTHING
`, userID)
return err
}What's the Problem?
An isolation level that is too low allows a transaction to read uncommitted data (dirty read) or see new data that appears mid-transaction (phantom read).
Case Study
An accounting system. A financial report is generated using the READ UNCOMMITTED isolation level. The report shows a balance that never existed — because it read transactions that were later rolled back.
Solution in Golang
// ✅ Set the appropriate isolation level for your needs
func GenerateFinancialReport(ctx context.Context, db *sql.DB) (*Report, error) {
// Snapshot isolation: all reads from a consistent snapshot
tx, err := db.BeginTx(ctx, &sql.TxOptions{
Isolation: sql.LevelRepeatableRead, // or LevelSerializable for critical operations
ReadOnly: true,
})
if err != nil {
return nil, err
}
defer tx.Rollback()
// All queries here read from the snapshot at the time the tx started
report, err := buildReport(ctx, tx)
if err != nil {
return nil, err
}
return report, tx.Commit()
}
/*
Isolation level guide:
- READ COMMITTED (PostgreSQL default): suitable for normal operations
- REPEATABLE READ: suitable for reports that need consistency within a single transaction
- SERIALIZABLE: suitable for critical financial operations, but slower
*/What's the Problem?
Two transactions read the same value, both modify it, and the one that commits last overwrites the changes made by the first transaction. The first change is simply lost.
Case Study
Two admins editing the same user profile at the same time. Admin A changes the email, Admin B changes the name. Whoever saves last overwrites the other's changes — one update disappears without a trace.
Solution in Golang
// ✅ Optimistic locking with version/updated_at column
type UserProfile struct {
ID int `db:"id"`
Name string `db:"name"`
Email string `db:"email"`
Version int `db:"version"` // incremented on every update
UpdatedAt time.Time `db:"updated_at"`
}
func UpdateUserProfileOptimistic(ctx context.Context, db *sql.DB, profile UserProfile) error {
result, err := db.ExecContext(ctx, `
UPDATE users
SET name = $1, email = $2, version = version + 1, updated_at = NOW()
WHERE id = $3 AND version = $4 -- only update if version matches
`, profile.Name, profile.Email, profile.ID, profile.Version)
if err != nil {
return err
}
rows, err := result.RowsAffected()
if err != nil {
return err
}
if rows == 0 {
// Version mismatch — someone already updated before us
return ErrConflict
}
return nil
}
// ✅ Pessimistic locking with SELECT FOR UPDATE
func UpdateUserProfilePessimistic(ctx context.Context, db *sql.DB, userID int, fn func(*UserProfile) error) error {
tx, err := db.BeginTx(ctx, nil)
if err != nil {
return err
}
defer tx.Rollback()
var profile UserProfile
err = tx.QueryRowContext(ctx, `
SELECT id, name, email, version FROM users WHERE id = $1 FOR UPDATE
`, userID).Scan(&profile.ID, &profile.Name, &profile.Email, &profile.Version)
if err != nil {
return err
}
if err := fn(&profile); err != nil {
return err
}
_, err = tx.ExecContext(ctx, `
UPDATE users SET name = $1, email = $2, version = version + 1 WHERE id = $3
`, profile.Name, profile.Email, profile.ID)
if err != nil {
return err
}
return tx.Commit()
}What's the Problem?
The application writes to the primary but reads from a replica that hasn't synced yet. The user performs an action, then sees data as if the action never happened — even though it was already saved.
Case Study
A social media platform. A user posts a tweet and is immediately redirected to their profile. But the tweet doesn't appear because it was read from a replica with a 2-second lag. The user thinks the post failed and posts again — duplicate tweet.
Solution in Golang
// ✅ Sticky session: after writing, read from primary for a period of time
type DBRouter struct {
primary *sql.DB
replicas []*sql.DB
// map userID → deadline for reading from primary
stickyMu sync.RWMutex
sticky map[int]time.Time
}
func (r *DBRouter) AfterWrite(userID int) {
r.stickyMu.Lock()
r.sticky[userID] = time.Now().Add(5 * time.Second)
r.stickyMu.Unlock()
}
func (r *DBRouter) ReadDB(userID int) *sql.DB {
r.stickyMu.RLock()
deadline, ok := r.sticky[userID]
r.stickyMu.RUnlock()
if ok && time.Now().Before(deadline) {
return r.primary // read from primary
}
return r.replicas[userID%len(r.replicas)] // read from replica
}
// ✅ Or use token-based consistency
func CreatePost(ctx context.Context, db *DBRouter, userID int, content string) (string, error) {
postID, err := insertPost(ctx, db.primary, userID, content)
if err != nil {
return "", err
}
// Mark that this user needs read-your-writes for the next 5 seconds
db.AfterWrite(userID)
return postID, nil
}What's the Problem?
All data from various entities is mixed into one giant table. Any query becomes slow due to the large row size. ALTER TABLE becomes a terrifying operation.
Case Study
A SaaS platform with a users table containing 240 columns — ranging from personal data, UI preferences, billing info, analytics flags, to feature flags. Even a SELECT id, name FROM users query still has to read the full 8KB row from disk.
Solution in Golang
// ❌ Anti-pattern: God table
// users(id, name, email, phone, address, city, country,
// stripe_customer_id, plan_type, billing_cycle,
// ui_theme, ui_language, notification_prefs,
// last_login, login_count, ...240 other columns)
// ✅ Decompose by domain
/*
users(id, name, email, created_at) -- core identity
user_billing(user_id, stripe_id, plan, cycle) -- billing domain
user_preferences(user_id, theme, language, notif_settings) -- preferences
user_activity(user_id, last_login, login_count) -- analytics
*/
// ✅ In Golang, fetch only the domain you need
type UserCore struct {
ID int `db:"id"`
Name string `db:"name"`
Email string `db:"email"`
}
type UserBilling struct {
UserID int `db:"user_id"`
StripeID string `db:"stripe_id"`
Plan string `db:"plan"`
}
func GetUserWithBilling(ctx context.Context, db *sqlx.DB, userID int) (*UserCore, *UserBilling, error) {
var core UserCore
var billing UserBilling
// Two small queries > one query from a God table
errCh := make(chan error, 2)
go func() { errCh <- db.GetContext(ctx, &core, "SELECT id, name, email FROM users WHERE id=$1", userID) }()
go func() { errCh <- db.GetContext(ctx, &billing, "SELECT user_id, stripe_id, plan FROM user_billing WHERE user_id=$1", userID) }()
for i := 0; i < 2; i++ {
if err := <-errCh; err != nil && err != sql.ErrNoRows {
return nil, nil, err
}
}
return &core, &billing, nil
}What's the Problem?
A "one table for all dynamic attributes" design with the structure (entity_id, attribute_name, value). It looks flexible, but querying all attributes of a single entity requires N joins or N queries.
Case Study
A CMS with a page_attributes(page_id, key, value) table. Rendering one page with 30 attributes requires 30 rows that must be pivoted in the application. When listing 1,000 pages at once: 30,000 rows transferred.
Solution in Golang
// ❌ EAV anti-pattern
// page_attributes: page_id=1, key='title', value='Hello'
// page_attributes: page_id=1, key='slug', value='hello'
// page_attributes: page_id=1, key='published', value='true'
// ✅ Solution 1: Use regular columns for known attributes
/*
CREATE TABLE pages (
id SERIAL PRIMARY KEY,
title TEXT NOT NULL,
slug TEXT UNIQUE NOT NULL,
published BOOLEAN DEFAULT false,
created_at TIMESTAMPTZ DEFAULT NOW()
);
*/
// ✅ Solution 2: JSONB for truly dynamic attributes
/*
CREATE TABLE pages (
id SERIAL PRIMARY KEY,
title TEXT NOT NULL,
metadata JSONB DEFAULT '{}' -- for dynamic attributes
);
CREATE INDEX idx_pages_metadata ON pages USING GIN(metadata);
*/
func GetPageWithMetadata(ctx context.Context, db *sql.DB, slug string) (*Page, error) {
var page Page
var metadataJSON []byte
err := db.QueryRowContext(ctx, `
SELECT id, title, slug, metadata FROM pages WHERE slug = $1
`, slug).Scan(&page.ID, &page.Title, &page.Slug, &metadataJSON)
if err != nil {
return nil, err
}
json.Unmarshal(metadataJSON, &page.Metadata)
return &page, nil
}
// ✅ Query JSONB with an index
func GetPagesByTag(ctx context.Context, db *sql.DB, tag string) ([]Page, error) {
rows, err := db.QueryContext(ctx, `
SELECT id, title, slug
FROM pages
WHERE metadata @> $1::jsonb -- GIN index is used!
`, fmt.Sprintf(`{"tags": ["%s"]}`, tag))
defer rows.Close()
return scanPages(rows, err)
}What's the Problem?
Over-normalization splits data that is frequently read together across too many tables. A simple query requires 7-10 JOINs, making it difficult for the query planner to optimize.
Case Study
An e-commerce platform with a query to display an order: JOINs to orders, order_items, products, categories, users, addresses, payment_methods, shipping_options — 8 JOINs for a single page. Query plan: 45ms. After denormalizing a few fields: 3ms.
Solution in Golang
// ✅ Denormalize fields that are frequently read together
/*
-- Instead of joining to products every time, store a snapshot when the order is created
ALTER TABLE order_items ADD COLUMN product_name TEXT;
ALTER TABLE order_items ADD COLUMN product_price NUMERIC(10,2);
-- Populate on insert
INSERT INTO order_items (order_id, product_id, product_name, product_price, qty)
SELECT $1, p.id, p.name, p.price, $2
FROM products p WHERE p.id = $3;
*/
// ✅ In Golang, create a flat DTO for display needs
type OrderDisplayItem struct {
ItemID int `db:"item_id"`
ProductName string `db:"product_name"` // denormalized
ProductPrice float64 `db:"product_price"` // denormalized
Qty int `db:"qty"`
Subtotal float64 `db:"subtotal"`
}
func GetOrderItems(ctx context.Context, db *sql.DB, orderID int) ([]OrderDisplayItem, error) {
rows, err := db.QueryContext(ctx, `
SELECT id as item_id, product_name, product_price, qty,
product_price * qty AS subtotal
FROM order_items
WHERE order_id = $1
`, orderID)
// No JOINs! Data is already stored when the order was created.
defer rows.Close()
return scanOrderItems(rows, err)
}What's the Problem?
Forgotten rows.Close() calls or transactions that are never rolled back prevent connections from returning to the pool. The pool runs dry, and all subsequent requests block waiting for an available connection.
Solution in Golang
// ✅ Proper pool configuration
func NewDB(dsn string) (*sql.DB, error) {
db, err := sql.Open("pgx", dsn)
if err != nil {
return nil, err
}
db.SetMaxOpenConns(25)
db.SetMaxIdleConns(10)
db.SetConnMaxLifetime(5 * time.Minute)
db.SetConnMaxIdleTime(2 * time.Minute)
return db, nil
}
// ✅ Correct pattern: defer rows.Close() IMMEDIATELY after checking for errors
func GetCategories(ctx context.Context, db *sql.DB) ([]Category, error) {
rows, err := db.QueryContext(ctx, "SELECT id, name FROM categories")
if err != nil {
return nil, err
}
defer rows.Close() // ← ALWAYS here
var categories []Category
for rows.Next() {
var c Category
if err := rows.Scan(&c.ID, &c.Name); err != nil {
return nil, err
}
categories = append(categories, c)
}
return categories, rows.Err()
}
// ✅ Monitor pool health
func MonitorDBPool(db *sql.DB) {
stats := db.Stats()
if float64(stats.InUse)/float64(stats.MaxOpenConnections) > 0.8 {
log.Warn().
Int("in_use", stats.InUse).
Int64("wait_count", stats.WaitCount).
Msg("DB pool usage > 80% — consider increasing MaxOpenConns or optimizing queries")
}
}What's the Problem?
In PostgreSQL, UPDATE and DELETE don't immediately remove data — they create dead tuples that still occupy disk space. Without proper VACUUM, a table can grow 10x larger than it should, and all queries slow down because they have to read more disk pages.
Case Study
A sessions table in a web application deleting millions of rows per day. After 6 months, a table that should be 100MB has grown to 8GB. Queries on this table take 5 seconds because sequential scans read through 8GB of data.
Solution in Golang
// ✅ Detect table bloat
const bloatQuery = `
SELECT
tablename,
pg_size_pretty(pg_total_relation_size(tablename::regclass)) AS total_size,
pg_size_pretty(pg_relation_size(tablename::regclass)) AS table_size,
n_dead_tup,
n_live_tup,
ROUND(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_pct
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;
`
func CheckTableBloat(ctx context.Context, db *sql.DB) error {
rows, err := db.QueryContext(ctx, bloatQuery)
if err != nil {
return err
}
defer rows.Close()
for rows.Next() {
var table, totalSize, tableSize string
var deadTup, liveTup int64
var deadPct float64
rows.Scan(&table, &totalSize, &tableSize, &deadTup, &liveTup, &deadPct)
if deadPct > 20 {
log.Warn().
Str("table", table).
Float64("dead_pct", deadPct).
Str("total_size", totalSize).
Msg("Table bloat detected — consider VACUUM ANALYZE")
}
}
return nil
}
// ✅ For high-delete tables, use partitioning instead of DELETE
/*
-- Partition the sessions table per week
CREATE TABLE sessions (
id UUID DEFAULT gen_random_uuid(),
user_id INT NOT NULL,
expires_at TIMESTAMPTZ NOT NULL
) PARTITION BY RANGE (expires_at);
-- Drop old partition = instant cleanup without bloat
DROP TABLE sessions_2026_w01;
*/What's the Problem?
ALTER TABLE in PostgreSQL acquires an Access Exclusive Lock — blocking all reads and writes. On a table with 100 million rows, this can last minutes to hours.
Case Study
An engineering team adds a nullable column to the orders table (80 million rows) during peak traffic. The migration takes 8 minutes. During that time, all requests touching the orders table timeout. Incident P1.
Solution in Golang
// ✅ Use phased migration for zero-downtime
/*
Step 1: Add nullable column WITHOUT default (instant in PostgreSQL 11+)
ALTER TABLE orders ADD COLUMN notes TEXT;
Step 2: Backfill data in batches (does not lock the table)
UPDATE orders SET notes = '' WHERE id BETWEEN 1 AND 100000 AND notes IS NULL;
UPDATE orders SET notes = '' WHERE id BETWEEN 100001 AND 200000 AND notes IS NULL;
-- etc., in small batches
Step 3: Set default and NOT NULL after all rows are populated
ALTER TABLE orders ALTER COLUMN notes SET DEFAULT '';
ALTER TABLE orders ALTER COLUMN notes SET NOT NULL;
*/
// ✅ In Golang: run backfill as a background job
func BackfillColumnInBatches(ctx context.Context, db *sql.DB, batchSize int) error {
var minID, maxID int
db.QueryRowContext(ctx, "SELECT MIN(id), MAX(id) FROM orders WHERE notes IS NULL").
Scan(&minID, &maxID)
for start := minID; start <= maxID; start += batchSize {
end := start + batchSize - 1
_, err := db.ExecContext(ctx, `
UPDATE orders SET notes = ''
WHERE id BETWEEN $1 AND $2 AND notes IS NULL
`, start, end)
if err != nil {
return fmt.Errorf("backfill batch %d-%d: %w", start, end, err)
}
// Small pause to give the database a breather
time.Sleep(10 * time.Millisecond)
}
return nil
}What's the Problem?
ORM uses lazy loading: relationships aren't fetched until first accessed. In a loop, this produces hidden N+1 queries — invisible from the code, but happening behind the scenes.
Case Study
GORM in Go. The developer writes:
var orders []Order
db.Find(&orders) // 1 query
for _, o := range orders {
fmt.Println(o.User.Name) // ← GORM fetches users one by one!
}
// Total: 1 + N queriesSolution in Golang
// ❌ Lazy loading trap with GORM
func GetOrdersWithUserBad(db *gorm.DB) ([]Order, error) {
var orders []Order
db.Find(&orders)
for _, o := range orders {
_ = o.User.Name // Triggers a query per order!
}
return orders, nil
}
// ✅ Use Preload (eager loading) in GORM
func GetOrdersWithUserGood(db *gorm.DB) ([]Order, error) {
var orders []Order
err := db.Preload("User").Preload("Items").Find(&orders).Error
// GORM generates: SELECT * FROM orders; SELECT * FROM users WHERE id IN (...);
return orders, err
}
// ✅ Or better yet: write a raw query with JOIN for full control
func GetOrdersWithUserRaw(ctx context.Context, db *sqlx.DB, limit int) ([]OrderWithUser, error) {
var result []OrderWithUser
err := db.SelectContext(ctx, &result, `
SELECT o.id, o.total, o.status,
u.id as user_id, u.name as user_name, u.email as user_email
FROM orders o
INNER JOIN users u ON u.id = o.user_id
WHERE o.status != 'cancelled'
ORDER BY o.created_at DESC
LIMIT $1
`, limit)
return result, err
}What's the Problem?
ORMs sometimes generate queries far more complex than necessary — nested subqueries, redundant conditions, or repeated SELECTs for the same thing.
Case Study
A GORM-based application. A query for "active users with their latest order" uses GORM's method chaining and produces a query with 3 levels of nested subqueries and an execution time of 800ms. Rewritten as raw SQL: 12ms.
Solution in Golang
// ❌ GORM method chaining that generates poor queries
func GetActiveUsersWithLatestOrderBad(db *gorm.DB) ([]User, error) {
var users []User
db.Where("active = ?", true).
Preload("Orders", func(db *gorm.DB) *gorm.DB {
return db.Order("created_at desc")
}).
Find(&users)
return users, nil
// Generated: suboptimal nested subquery
}
// ✅ Raw SQL for complex queries — transparent and EXPLAIN-able
const activeUsersWithLatestOrderSQL = `
SELECT DISTINCT ON (u.id)
u.id, u.name, u.email,
o.id as latest_order_id,
o.total as latest_order_total,
o.created_at as latest_order_at
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.active = true
ORDER BY u.id, o.created_at DESC NULLS LAST
LIMIT $1
`
type UserWithLatestOrder struct {
ID int `db:"id"`
Name string `db:"name"`
Email string `db:"email"`
LatestOrderID *int `db:"latest_order_id"`
LatestOrderTotal *float64 `db:"latest_order_total"`
LatestOrderAt *time.Time `db:"latest_order_at"`
}
func GetActiveUsersWithLatestOrderGood(ctx context.Context, db *sqlx.DB, limit int) ([]UserWithLatestOrder, error) {
var result []UserWithLatestOrder
err := db.SelectContext(ctx, &result, activeUsersWithLatestOrderSQL, limit)
return result, err
}No Cartesian JOINs, every query has LIMIT, EXPLAIN ANALYZE has been run, parameter types match columns, transactions are short.
Unused indexes removed, bulk inserts use COPY protocol, counters use sharding or buffers, upserts use ON CONFLICT.
Isolation level matches requirements, optimistic/pessimistic locking for concurrent updates, read-your-writes handled for replicas.
No God Table, EAV replaced with JSONB, denormalize frequently-read fields, migrations use zero-downtime patterns.
rows.Close() is always deferred, pool is configured and monitored, VACUUM runs regularly, ORM uses eager loading.
Database problems aren't just about slow queries. It's about understanding the entire data lifecycle — from how data is written, stored, and read, to how the system behaves under concurrency. Every problem above shares one thing in common: they're invisible in development, and only explode in production.
Your best weapons: EXPLAIN ANALYZE before deploying, pg_stat_statements in production, OpenTelemetry tracing in Golang, and code reviews that always ask — "What will this look like at 1 million rows and 10,000 concurrent users?"
