Bulk insert optimization pattern for static/reference data sync
Optimizes bulk database inserts for static data sync with batching and FK pre-filtering.
/plugin marketplace add aaronwald/dlawskillz/plugin install dlaw@dlawskillzThis skill inherits all available tools. When active, it can use any tool Claude has access to.
Use this pattern when syncing large volumes of reference/static data from external APIs to a database.
Instead of individual INSERT statements, batch rows into single queries:
// Bad: N queries for N rows
for _, item := range items {
db.Exec("INSERT INTO items (...) VALUES ($1, $2, ...)", item.A, item.B)
}
// Good: 1 query for N rows (batch size 500)
valueStrings := make([]string, 0, len(batch))
valueArgs := make([]interface{}, 0, len(batch)*numCols)
for i, item := range batch {
base := i * numCols
valueStrings = append(valueStrings, fmt.Sprintf(
"($%d, $%d, $%d, NOW())", base+1, base+2, base+3))
valueArgs = append(valueArgs, item.A, item.B, item.C)
}
query := fmt.Sprintf(`INSERT INTO items (...) VALUES %s
ON CONFLICT (pk) DO UPDATE SET ...`,
strings.Join(valueStrings, ", "))
db.ExecContext(ctx, query, valueArgs...)
When child records reference parent records, pre-filter to avoid FK violations:
// Collect unique parent IDs from batch
parentIDs := collectUniqueParentIDs(childRecords)
// Query which parents exist
rows, _ := db.Query(
"SELECT id FROM parents WHERE id = ANY($1) AND deleted_at IS NULL",
pq.Array(parentIDs))
existingParents := make(map[string]struct{})
for rows.Next() {
var id string
rows.Scan(&id)
existingParents[id] = struct{}{}
}
// Filter children to only those with existing parents
validChildren := filterByExistingParents(childRecords, existingParents)
Match the external API's rate limits:
const (
DefaultRateDelay = 250 * time.Millisecond // 4 req/sec
Min429Wait = 5 * time.Second // Min wait on 429
Max429Wait = 120 * time.Second // Cap wait time
MaxRetries = 10
)
Log batch progress for visibility:
result, _ := db.ExecContext(ctx, query, valueArgs...)
rows, _ := result.RowsAffected()
fmt.Printf("[DB] batch: %d attempted, %d affected\n", len(batch), rows)
See: ssmd/internal/secmaster/store.go
bulkUpsertEvents() - bulk INSERT for eventsbulkUpsertMarkets() - bulk INSERT with FK pre-filteringUpsertMarketBatch() - orchestrates pre-filter + batch INSERTApplies Anthropic's official brand colors and typography to any sort of artifact that may benefit from having Anthropic's look-and-feel. Use it when brand colors or style guidelines, visual formatting, or company design standards apply.
Creating algorithmic art using p5.js with seeded randomness and interactive parameter exploration. Use this when users request creating art using code, generative art, algorithmic art, flow fields, or particle systems. Create original algorithmic art rather than copying existing artists' work to avoid copyright violations.
Create beautiful visual art in .png and .pdf documents using design philosophy. You should use this skill when the user asks to create a poster, piece of art, design, or other static piece. Create original visual designs, never copying existing artists' work to avoid copyright violations.