Naive vs Optimized Inserts in PostgreSQL: 5× Faster, Sub-Millisecond Latency, but a Hidden Bottleneck

9 min readSep 2, 2025

Executive Summary

Efficient data ingestion is a critical challenge for high-throughput systems. This case study explores two approaches to inserting large batches of data into PostgreSQL using Go:

Naive Approach — Iterative INSERT statements executed per request.
Optimized Approach — Leveraging PostgreSQL’s high-performance bulk insertion mechanism (COPY FROM STDIN) via the pq.CopyIn API.

We conducted controlled load testing with k6 under two traffic scenarios:

50 Virtual Users (VUs) for 30s
500 VUs for 60s

We compared throughput, latency distribution, and error rates. Results show that while the naive implementation degrades under high concurrency, the optimized approach achieves 5–6× higher throughput with ultra-low latency — but suffers queue saturation at extreme loads.

Background

Efficient ingestion pipelines are critical for real-world systems such as:

Logging platforms
IoT telemetry collectors
Analytics engines

The naive approach, inserting rows one by one, incurs significant overhead from repeated query parsing, transaction handling, and network round trips.

PostgreSQL’s COPY FROM STDIN provides a bulk insert path with much lower overhead. The Go pq driver exposes this via pq.CopyIn, enabling buffered multi-row inserts in a single round trip.

Get Arif Rahman’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

This study benchmarks both approaches.

Naive Implementation

// server_naive.go
package main

import (
 "database/sql"
 "encoding/json"
 "log"
 "net/http"
 "time"

 _ "github.com/lib/pq"
)

type Order struct {
 OrderID string  `json:"order_id"`
 UserID  string  `json:"user_id"`
 Total   float64 `json:"total"`
 Created int64   `json:"created_at"`
}

var db *sql.DB

func createTable() {
 _, err := db.Exec(`CREATE TABLE IF NOT EXISTS orders (
  order_id TEXT PRIMARY KEY,
  user_id TEXT,
  total NUMERIC,
  created_at TIMESTAMP
 )`)
 if err != nil {
  log.Fatal(err)
 }
}

func orderHandler(w http.ResponseWriter, r *http.Request) {
 if r.Method != http.MethodPost {
  http.Error(w, "only POST", http.StatusMethodNotAllowed)
  return
 }
 var o Order
 if err := json.NewDecoder(r.Body).Decode(&o); err != nil {
  http.Error(w, "bad payload", http.StatusBadRequest)
  return
 }
 // synchronous insert (naive)
 _, err := db.Exec("INSERT INTO orders (order_id, user_id, total, created_at) VALUES ($1,$2,$3,TO_TIMESTAMP($4))",
  o.OrderID, o.UserID, o.Total, o.Created)
 if err != nil {
  http.Error(w, "db error", http.StatusInternalServerError)
  return
 }
 w.WriteHeader(http.StatusCreated)
}

func main() {
 var err error
 db, err = sql.Open("postgres", "postgres://postgres:postgres@localhost:5437/postgres?sslmode=disable")
 if err != nil {
  log.Fatal(err)
 }
 db.SetMaxOpenConns(50)
 createTable()
 http.HandleFunc("/orders", orderHandler)
 srv := &http.Server{
  Addr:         ":8080",
  ReadTimeout:  5 * time.Second,
  WriteTimeout: 10 * time.Second,
 }
 log.Println("naive server listening :8080")
 log.Fatal(srv.ListenAndServe())
}

This approach issues one round trip per row, magnifying latency and CPU overhead.

Optimized Implementation with pq.CopyIn

 // server_optimized.go
 package main

 import (
  "context"
  "database/sql"
  "encoding/json"
  "fmt"
  "log"
  "net/http"
  "sync"
  "time"

  _ "github.com/lib/pq"
 )

 type Order struct {
  OrderID string  `json:"order_id"`
  UserID  string  `json:"user_id"`
  Total   float64 `json:"total"`
  Created int64   `json:"created_at"`
 }

 var (
  db        *sql.DB
  queue     chan Order
  batchSize = 200
  batchWait = 100 * time.Millisecond
  wg        sync.WaitGroup
 )

 // pqCopyIn generates a COPY statement for bulk insert
 func pqCopyIn(table string, columns ...string) string {
  cols := ""
  for i, col := range columns {
   if i > 0 {
    cols += ", "
   }
   cols += col
  }
  return fmt.Sprintf("COPY %s (%s) FROM STDIN", table, cols)
 }

 func createTable() {
  _, err := db.Exec(`CREATE TABLE IF NOT EXISTS orders (
   order_id TEXT PRIMARY KEY,
   user_id TEXT,
   total NUMERIC,
   created_at TIMESTAMP
  )`)
  if err != nil {
   log.Fatal(err)
  }
 }

 func orderHandler(w http.ResponseWriter, r *http.Request) {
  if r.Method != http.MethodPost {
   http.Error(w, "only POST", http.StatusMethodNotAllowed)
   return
  }
  var o Order
  if err := json.NewDecoder(r.Body).Decode(&o); err != nil {
   http.Error(w, "bad payload", http.StatusBadRequest)
   return
  }
  // minimal validation + enqueue
  select {
  case queue <- o:
   w.WriteHeader(http.StatusAccepted) // async
  default:
   // queue full -> backpressure
   http.Error(w, "server busy", http.StatusTooManyRequests)
  }
 }

 func worker(ctx context.Context) {
  defer wg.Done()
  buf := make([]Order, 0, batchSize)
  timer := time.NewTimer(batchWait)
  for {
   timer.Reset(batchWait)
   select {
   case <-ctx.Done():
    flush(buf)
    return
   case o := <-queue:
    buf = append(buf, o)
    if len(buf) >= batchSize {
     flush(buf)
     buf = buf[:0]
    }
   case <-timer.C:
    if len(buf) > 0 {
     flush(buf)
     buf = buf[:0]
    }
   }
  }
 }

 func flush(batch []Order) {
  if len(batch) == 0 {
   return
  }
  tx, err := db.Begin()
  if err != nil {
   log.Println("tx begin error:", err)
   return
  }
  stmt, err := tx.Prepare(pqCopyIn("orders", "order_id", "user_id", "total", "created_at"))
  if err != nil {
   log.Println("prepare error:", err)
   tx.Rollback()
   return
  }
  for _, o := range batch {
   _, err := stmt.Exec(o.OrderID, o.UserID, o.Total, time.Unix(o.Created, 0))
   if err != nil {
    log.Println("exec error:", err)
   }
  }
  stmt.Exec()
  stmt.Close()
  if err := tx.Commit(); err != nil {
   log.Println("commit error:", err)
  }
 }

 func main() {
  var err error
  db, err = sql.Open("postgres", "postgres://postgres:postgres@localhost:5437/postgres?sslmode=disable")
  if err != nil {
   log.Fatal(err)
  }
  db.SetMaxOpenConns(50)
  createTable()

  queue = make(chan Order, 20000)

  ctx, cancel := context.WithCancel(context.Background())
  wg.Add(1)
  go worker(ctx)

  http.HandleFunc("/orders", orderHandler)
  srv := &http.Server{
   Addr: ":8080",
  }
  log.Println("optimized server listening :8080")
  go func() {
   log.Fatal(srv.ListenAndServe())
  }()

  // simple trap: run for 24h or ctrl-c
  select {}
  _ = cancel
  wg.Wait()
 }

This method dramatically reduces overhead by batching inserts.

Load Testing Setup

We used k6 to simulate concurrent clients hitting an HTTP endpoint that triggers inserts. Each scenario is defined with a constant number of virtual users (VUs) for a set duration.

k6 Script

// loadtest/post_k6.js
import http from 'k6/http';
import { check, sleep } from 'k6';
import { Trend } from 'k6/metrics';

export let latencyTrend = new Trend('latency_ms');

export let options = {
  vus: __ENV.VUS ? parseInt(__ENV.VUS) : 50,
  duration: __ENV.DURATION || '30s',
  thresholds: {
    'http_req_duration': ['p(95)<500'], // example threshold
  },
};

export default function () {
  const url = 'http://localhost:8080/orders';
  const payload = JSON.stringify({
    order_id: `${__VU}-${__ITER}-${Date.now()}`,
    user_id: `user-${Math.floor(Math.random()*1000)}`,
    total: Math.random() * 200,
    created_at: Math.floor(Date.now()/1000)
  });
  const params = { headers: { 'Content-Type': 'application/json' } };
  const res = http.post(url, payload, params);
  latencyTrend.add(res.timings.duration);
  check(res, {
    'status 2xx or 202': (r) => r.status === 201 || r.status === 202,
  });
  sleep(0.01);
}

Results

We present the raw k6 outputs for transparency.

Naive Implementation: 50 VUs, 30s

          /\      |‾‾| /‾‾/   /‾‾/   
     /\  /  \     |  |/  /   /  /    
    /  \/    \    |     (   /   ‾‾\  
   /          \   |  |\  \ |  (‾)  | 
  / __________ \  |__| \__\ \_____/ .io

     execution: local
        script: loadtest/post_k6.js
        output: -

     scenarios: (100.00%) 1 scenario, 50 max VUs, 1m0s max duration (incl. graceful stop):
              * default: 50 looping VUs for 30s (gracefulStop: 30s)


     ✓ status 2xx or 202

     checks.........................: 100.00% ✓ 23667     ✗ 0    
     data_received..................: 1.9 MB  62 kB/s
     data_sent......................: 5.8 MB  190 kB/s
     http_req_blocked...............: avg=4.55µs    min=0s      med=1µs     max=2.48ms   p(90)=4µs     p(95)=5µs    
     http_req_connecting............: avg=1.51µs    min=0s      med=0s      max=899µs    p(90)=0s      p(95)=0s     
   ✓ http_req_duration..............: avg=52.72ms   min=839µs   med=41.18ms max=4.57s    p(90)=68.03ms p(95)=85.8ms 
       { expected_response:true }...: avg=52.72ms   min=839µs   med=41.18ms max=4.57s    p(90)=68.03ms p(95)=85.8ms 
     http_req_failed................: 0.00%   ✓ 0         ✗ 23667
     http_req_receiving.............: avg=18.11µs   min=3µs     med=9µs     max=4.93ms   p(90)=35µs    p(95)=50µs   
     http_req_sending...............: avg=18.67µs   min=2µs     med=6µs     max=6.97ms   p(90)=30µs    p(95)=57µs   
     http_req_tls_handshaking.......: avg=0s        min=0s      med=0s      max=0s       p(90)=0s      p(95)=0s     
     http_req_waiting...............: avg=52.69ms   min=828µs   med=41.14ms max=4.57s    p(90)=68.01ms p(95)=85.78ms
     http_reqs......................: 23667   770.34576/s
     iteration_duration.............: avg=63.48ms   min=10.93ms med=51.75ms max=4.58s    p(90)=78.87ms p(95)=96.81ms
     iterations.....................: 23667   770.34576/s
     latency_ms.....................: avg=52.729605 min=0.839   med=41.184  max=4570.771 p(90)=68.0326 p(95)=85.8083
     vus............................: 50      min=50      max=50 
     vus_max........................: 50      min=50      max=50 


running (0m30.7s), 00/50 VUs, 23667 complete and 0 interrupted iterations
default ✓ [======================================] 50 VUs  30sNaive Implementation: 500 VUs, 60s

k6 run -e VUS=500 -e DURATION=60s loadtest/post_k6.js

          /\      |‾‾| /‾‾/   /‾‾/   
     /\  /  \     |  |/  /   /  /    
    /  \/    \    |     (   /   ‾‾\  
   /          \   |  |\  \ |  (‾)  | 
  / __________ \  |__| \__\ \_____/ .io

     execution: local
        script: loadtest/post_k6.js
        output: -

     scenarios: (100.00%) 1 scenario, 500 max VUs, 1m30s max duration (incl. graceful stop):
              * default: 500 looping VUs for 1m0s (gracefulStop: 30s)


     ✓ status 2xx or 202

     checks.........................: 100.00% ✓ 282618      ✗ 0     
     data_received..................: 23 MB   376 kB/s
     data_sent......................: 70 MB   1.2 MB/s
     http_req_blocked...............: avg=31.26µs   min=0s      med=1µs     max=42.5ms   p(90)=3µs      p(95)=4µs     
     http_req_connecting............: avg=29.07µs   min=0s      med=0s      max=37.6ms   p(90)=0s       p(95)=0s      
   ✓ http_req_duration..............: avg=95.8ms    min=2.25ms  med=35.95ms max=6.83s    p(90)=222.44ms p(95)=349.38ms
       { expected_response:true }...: avg=95.8ms    min=2.25ms  med=35.95ms max=6.83s    p(90)=222.44ms p(95)=349.38ms
     http_req_failed................: 0.00%   ✓ 0           ✗ 282618
     http_req_receiving.............: avg=14.75µs   min=3µs     med=7µs     max=19.9ms   p(90)=25µs     p(95)=36µs    
     http_req_sending...............: avg=18.33µs   min=2µs     med=5µs     max=37.27ms  p(90)=30µs     p(95)=59µs    
     http_req_tls_handshaking.......: avg=0s        min=0s      med=0s      max=0s       p(90)=0s       p(95)=0s      
     http_req_waiting...............: avg=95.77ms   min=2.23ms  med=35.92ms max=6.83s    p(90)=222.39ms p(95)=349.34ms
     http_reqs......................: 282618  4699.858012/s
     iteration_duration.............: avg=106.17ms  min=12.39ms med=46.28ms max=6.84s    p(90)=232.87ms p(95)=360.27ms
     iterations.....................: 282618  4699.858012/s
     latency_ms.....................: avg=95.809351 min=2.257   med=35.9535 max=6832.902 p(90)=222.4463 p(95)=349.3865
     vus............................: 500     min=500       max=500 
     vus_max........................: 500     min=500       max=500 


running (1m00.1s), 000/500 VUs, 282618 complete and 0 interrupted iterations
default ✓ [====================================] 500 VUs  1m0s

Optimized Implementation: 50 VUs, 30s

          /\      |‾‾| /‾‾/   /‾‾/   
     /\  /  \     |  |/  /   /  /    
    /  \/    \    |     (   /   ‾‾\  
   /          \   |  |\  \ |  (‾)  | 
  / __________ \  |__| \__\ \_____/ .io

     execution: local
        script: loadtest/post_k6.js
        output: -

     scenarios: (100.00%) 1 scenario, 50 max VUs, 1m0s max duration (incl. graceful stop):
              * default: 50 looping VUs for 30s (gracefulStop: 30s)


     ✓ status 2xx or 202

     checks.........................: 100.00% ✓ 128766      ✗ 0     
     data_received..................: 10 MB   348 kB/s
     data_sent......................: 32 MB   1.1 MB/s
     http_req_blocked...............: avg=2.82µs   min=0s      med=1µs     max=6.37ms   p(90)=3µs     p(95)=3µs    
     http_req_connecting............: avg=371ns    min=0s      med=0s      max=1.4ms    p(90)=0s      p(95)=0s     
   ✓ http_req_duration..............: avg=674.36µs min=46µs    med=426µs   max=85.68ms  p(90)=1.23ms  p(95)=1.69ms 
       { expected_response:true }...: avg=674.36µs min=46µs    med=426µs   max=85.68ms  p(90)=1.23ms  p(95)=1.69ms 
     http_req_failed................: 0.00%   ✓ 0           ✗ 128766
     http_req_receiving.............: avg=18.53µs  min=2µs     med=8µs     max=28.42ms  p(90)=19µs    p(95)=46µs   
     http_req_sending...............: avg=39.62µs  min=1µs     med=6µs     max=61.05ms  p(90)=67µs    p(95)=197µs  
     http_req_tls_handshaking.......: avg=0s       min=0s      med=0s      max=0s       p(90)=0s      p(95)=0s     
     http_req_waiting...............: avg=616.2µs  min=40µs    med=381µs   max=85.67ms  p(90)=1.13ms  p(95)=1.56ms 
     http_reqs......................: 128766  4291.848355/s
     iteration_duration.............: avg=11.63ms  min=10.09ms med=11.36ms max=106.48ms p(90)=12.58ms p(95)=13.33ms
     iterations.....................: 128766  4291.848355/s
     latency_ms.....................: avg=0.674364 min=0.046   med=0.426   max=85.688   p(90)=1.232   p(95)=1.696  
     vus............................: 50      min=50        max=50  
     vus_max........................: 50      min=50        max=50  


running (0m30.0s), 00/50 VUs, 128766 complete and 0 interrupted iterations
default ✓ [================================] 50 VUs  30s

Optimized Implementation: 500 VUs, 60s

k6 run -e VUS=500 -e DURATION=60s loadtest/post_k6.js

          /\      |‾‾| /‾‾/   /‾‾/   
     /\  /  \     |  |/  /   /  /    
    /  \/    \    |     (   /   ‾‾\  
   /          \   |  |\  \ |  (‾)  | 
  / __________ \  |__| \__\ \_____/ .io

     execution: local
        script: loadtest/post_k6.js
        output: -

     scenarios: (100.00%) 1 scenario, 500 max VUs, 1m30s max duration (incl. graceful stop):
              * default: 500 looping VUs for 1m0s (gracefulStop: 30s)


     ✗ status 2xx or 202
      ↳  34% — ✓ 596429 / ✗ 1125559

     checks.........................: 34.63%  ✓ 596429       ✗ 1125559
     data_received..................: 248 MB  4.1 MB/s
     data_sent......................: 427 MB  7.1 MB/s
     http_req_blocked...............: avg=10.3µs   min=0s      med=1µs     max=260.3ms  p(90)=3µs     p(95)=3µs    
     http_req_connecting............: avg=6.29µs   min=0s      med=0s      max=71.32ms  p(90)=0s      p(95)=0s     
   ✓ http_req_duration..............: avg=3.66ms   min=32µs    med=1.55ms  max=380.99ms p(90)=8.54ms  p(95)=13.35ms
       { expected_response:true }...: avg=3.93ms   min=32µs    med=1.49ms  max=280.71ms p(90)=9.51ms  p(95)=15.46ms
     http_req_failed................: 65.36%  ✓ 1125559      ✗ 596429 
     http_req_receiving.............: avg=346.87µs min=2µs     med=8µs     max=325.36ms p(90)=166µs   p(95)=1.3ms  
     http_req_sending...............: avg=1ms      min=2µs     med=9µs     max=344.53ms p(90)=2.74ms  p(95)=4.8ms  
     http_req_tls_handshaking.......: avg=0s       min=0s      med=0s      max=0s       p(90)=0s      p(95)=0s     
     http_req_waiting...............: avg=2.31ms   min=25µs    med=779µs   max=380.71ms p(90)=5.68ms  p(95)=8.63ms 
     http_reqs......................: 1721988 28683.278909/s
     iteration_duration.............: avg=16.99ms  min=10.06ms med=13.42ms max=574.09ms p(90)=27.31ms p(95)=34.64ms
     iterations.....................: 1721988 28683.278909/s
     latency_ms.....................: avg=3.665953 min=0.032   med=1.555   max=380.998  p(90)=8.544   p(95)=13.359 
     vus............................: 500     min=500        max=500  
     vus_max........................: 500     min=500        max=500  


running (1m00.0s), 000/500 VUs, 1721988 complete and 0 interrupted iterations
default ✓ [====================================] 500 VUs  1m0s

Deep Analysis

1. Naive vs Optimized Go Code

Naive Insert (per request)

stmt := `INSERT INTO events (user_id, event_type, metadata, created_at) VALUES ($1, $2, $3, NOW())`
_, err := db.Exec(stmt, userID, eventType, metadata)

Each request = 1 parse + 1 execution + 1 round trip.
Heavy parsing and network overhead.
Postgres can handle thousands of TPS, but saturates quickly.

Optimized Insert (batched via pq.CopyIn)

tx, _ := db.Begin()
stmt, _ := tx.Prepare(pq.CopyIn("events", "user_id", "event_type", "metadata", "created_at"))
for _, ev := range batch {
    _, _ = stmt.Exec(ev.UserID, ev.EventType, ev.Metadata, time.Now())
}
_, _ = stmt.Exec()
_ = stmt.Close()
_ = tx.Commit()

COPY is extremely efficient.
Requires batching coordinator (queue).
If the queue overflows, requests fail.

👉 The 65% failures at 500 VUs are due to queue saturation, not Postgres itself.

2. Test Result Analysis

Naive (50 VUs, 30s): ~770 req/s, avg 52ms, p95 85ms, ✅ stable but long tails.
Naive (500 VUs, 60s): ~4,700 req/s, avg 95ms, p95 349ms, ⚠️ DB saturated but no errors.
Optimized (50 VUs, 30s): ~4,292 req/s, avg 0.67ms, p95 1.7ms, 🚀 5.5× throughput with sub-ms latency.
Optimized (500 VUs, 60s): ~28k total req/s, but only ~9,945 req/s succeed (65% fail). Successful requests avg 3.7ms, p95 13ms. ❌ Queue overflow dominates.

3. Comparative Study

4. Why Optimized Fails at 500 VUs

COPY batching requires in-memory queue.
At extreme load, the queue fills up → requests fail before batching.
Postgres itself is not the bottleneck (latency for successful inserts remains low).
The issue is coordination layer (queue/worker design).

5. Recommendations

✅ Bounded Queue + Backpressure

Use Go channels with capacity limits.
If full:
Retry with exponential backoff, or
Fail fast (avoid timeouts).

✅ Asynchronous Worker Pool

Route requests into Kafka / RabbitMQ / NATS.
Workers pull batches and write via COPY.
Moves failures away from HTTP layer → eventual consistency.

✅ Adaptive Batch Size

Dynamic policy:
Flush at 100 rows, or
Flush every 200ms.
Prevents unbounded queues while keeping latency low.

✅ Connection Pool Tuning

Tune pgxpool / database/sql with max_open_conns matching CPU cores / Postgres workers.
COPY workloads prefer fewer large connections over many small ones.

🎯 Conclusion

Naive inserts are stable but slow under high load.
Optimized COPY batching boosts throughput by 5–6× with ultra-low latency.
However, without queue management, the system collapses at extreme concurrency (500 VUs).
Long-term solution: COPY batching + message queue + worker pool → high throughput and resilience against overload.

Arif Rahman

Naive vs Optimized Inserts in PostgreSQL: 5× Faster, Sub-Millisecond Latency, but a Hidden Bottleneck

Executive Summary

Background

Get Arif Rahman’s stories in your inbox

Naive Implementation

Optimized Implementation with pq.CopyIn

Load Testing Setup

k6 Script

Results

Naive Implementation: 50 VUs, 30s

Optimized Implementation: 50 VUs, 30s

Optimized Implementation: 500 VUs, 60s

Deep Analysis

1. Naive vs Optimized Go Code

Naive Insert (per request)

Optimized Insert (batched via pq.CopyIn)

2. Test Result Analysis

3. Comparative Study

4. Why Optimized Fails at 500 VUs

5. Recommendations

🎯 Conclusion

Written by Arif Rahman

No responses yet

More from Arif Rahman

This is a simple REST API built using VB.NET

Prerequisites

Handling Cache Stampede in High-Traffic Systems (with Redis)

In large-scale, high-traffic systems, cache (for example, Redis) is often seen as the performance “savior.” But with the wrong patterns…

Building a Scalable File Upload Service with Go: Complete Guide to Chunked Uploads and Resumable…

Introduction to Modern File Upload Architectures

🔥 Stop Using kill -9: The Dangerous Command Every Developer Misuses (Until It’s Too Late)

The truth about Linux kill signals — and why your graceful shutdown isn’t working.

Recommended from Medium

How To Run Claude Like a Tech Lead, Not a Senior Engineer

You already know how to brief one session. The leverage now is running four of them and still shipping code you’d put your own name on.

What Should Programmers Do While the AI Writes Code?

Hint: not start another AI coding loop

Sex for Rent in Germany: The Hidden Risk Behind the Housing Shortage

A student arriving in Munich with €380 available for housing faces an awkward piece of arithmetic. The median room in a shared flat now…

The 12 Data Architecture Patterns Every Data Engineer Should Master in 2026

From Lakehouse and Medallion to Data Mesh and Event-Driven systems — understand the architectures behind modern data platforms.

28 Core System Design Concepts, Explained Through the Failures They Prevent

Building blocks of scalable systems — organized by the production problem each one is designed to solve.

How to Become an AI Engineer in 2026: The Complete Learning Roadmap

Whether you’re starting from scratch or transitioning from software engineering, here’s a structured roadmap to learn the skills companies…