Skip to content

Instantly share code, notes, and snippets.

@artgillespie

artgillespie/data.json

Last active June 29, 2019 16:31
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save artgillespie/9999ba0a75a8cc64f0419164ad13e693 to your computer and use it in GitHub Desktop.
Save artgillespie/9999ba0a75a8cc64f0419164ad13e693 to your computer and use it in GitHub Desktop.
Download ZIP
lib/pq CopyIn vs "naive" multi-insert benchmarks
Raw
data.json
[
{
"_id": "5d1773ebce1769292c25993c",
"time": "Wed Aug 09 1972 12:05:26 GMT+0000 (UTC)",
"index": 0,
"guid": "ba791989-fcf0-45b1-8129-4e655f1d4f10",
"temp": "76.56",
"humidity": "56.63",
"airq": "0.5137"
},
{
"_id": "5d1773ebd07dd627d8cb3a52",
"time": "Tue Aug 20 1996 23:25:31 GMT+0000 (UTC)",
"index": 1,
"guid": "f28c8bd8-2279-4c99-bf3b-ceb045640258",
"temp": "35.42",
"humidity": "88.87",
"airq": "0.7736"
},
{
"_id": "5d1773eb7426161790f9392d",
"time": "Sun Nov 06 2016 22:55:34 GMT+0000 (UTC)",
"index": 2,
"guid": "fc6690ad-36c0-4d15-93ba-16b5e57732de",
"temp": "105.98",
"humidity": "20.12",
"airq": "0.9698"
},
{
"_id": "5d1773eb446a19c916fcaf1b",
"time": "Fri Oct 24 2003 06:11:43 GMT+0000 (UTC)",
"index": 3,
"guid": "b88cb43f-fb45-4fb9-a62c-150023e8a10b",
"temp": "81.46",
"humidity": "97.63",
"airq": "0.4885"
},
{
"_id": "5d1773eb2d5329983d4d98c1",
"time": "Tue Aug 09 2005 20:32:49 GMT+0000 (UTC)",
"index": 4,
"guid": "13d5404a-e41d-4ad4-ab9d-46ece63d1d81",
"temp": "36.47",
"humidity": "29.54",
"airq": "0.2367"
},
{
"_id": "5d1773eb56cf81b561b2b9a0",
"time": "Wed Feb 16 2011 18:35:07 GMT+0000 (UTC)",
"index": 5,
"guid": "500b36ae-2e88-4d3e-8664-734e1d6c430e",
"temp": "43.53",
"humidity": "77.63",
"airq": "0.1029"
},
{
"_id": "5d1773eb6314d243d8702d6d",
"time": "Sun Apr 10 2011 00:06:50 GMT+0000 (UTC)",
"index": 6,
"guid": "19f89f53-f449-4e27-ab73-de1214911fa3",
"temp": "44.22",
"humidity": "10.09",
"airq": "0.7141"
},
{
"_id": "5d1773eb13ecc37889362a29",
"time": "Fri Nov 27 1987 02:51:17 GMT+0000 (UTC)",
"index": 7,
"guid": "04207dde-176d-4e2a-91d0-39ffaa898750",
"temp": "102.76",
"humidity": "88.36",
"airq": "0.7831"
},
{
"_id": "5d1773eb2d7b1800f52cd467",
"time": "Thu Dec 04 2008 12:08:18 GMT+0000 (UTC)",
"index": 8,
"guid": "494bb525-8d92-443e-9d6b-e3e18b464543",
"temp": "38.7",
"humidity": "58.36",
"airq": "0.9708"
},
{
"_id": "5d1773eb9364495c18ba89bd",
"time": "Mon Sep 26 2016 07:22:32 GMT+0000 (UTC)",
"index": 9,
"guid": "08b9e03d-707e-4c52-a56b-1ad46c91cc52",
"temp": "117.91",
"humidity": "10.28",
"airq": "0.1733"
},
{
"_id": "5d1773ebcbce7d0ad1678770",
"time": "Sun Aug 17 2014 17:04:24 GMT+0000 (UTC)",
"index": 10,
"guid": "5cd49e28-5b82-4c5b-959d-159f93e79629",
"temp": "103.02",
"humidity": "75.81",
"airq": "0.4405"
},
{
"_id": "5d1773eb55d0a6fac384d968",
"time": "Sun Oct 16 1983 20:37:26 GMT+0000 (UTC)",
"index": 11,
"guid": "a3861794-bc42-4435-8cf1-bf15450b6b86",
"temp": "27.55",
"humidity": "51.19",
"airq": "0.3192"
},
{
"_id": "5d1773ebc70aa10039adea9a",
"time": "Tue Oct 12 1999 00:55:30 GMT+0000 (UTC)",
"index": 12,
"guid": "4aa56679-aaf4-488d-9198-93f821cceff2",
"temp": "88.53",
"humidity": "60.72",
"airq": "0.7339"
},
{
"_id": "5d1773eb5648a168332b9344",
"time": "Tue Apr 01 1997 10:34:05 GMT+0000 (UTC)",
"index": 13,
"guid": "7550b44a-68f0-4948-b678-0621a1cd9489",
"temp": "86.4",
"humidity": "70.18",
"airq": "0.669"
},
{
"_id": "5d1773eb78e084cd549fe3dc",
"time": "Sun Apr 19 1981 13:44:45 GMT+0000 (UTC)",
"index": 14,
"guid": "31ddb119-045f-403d-be3c-112621c3a944",
"temp": "115.06",
"humidity": "15.2",
"airq": "0.5328"
},
{
"_id": "5d1773ebb71e676177ba173e",
"time": "Wed Oct 28 1970 18:03:32 GMT+0000 (UTC)",
"index": 15,
"guid": "2bd2d09e-a424-4e56-af46-3723e46ebb24",
"temp": "50.69",
"humidity": "21.95",
"airq": "0.1979"
},
{
"_id": "5d1773eb5ef7d6faa681c98e",
"time": "Sun Mar 27 2005 19:31:09 GMT+0000 (UTC)",
"index": 16,
"guid": "ef405fc6-23da-433c-8ec3-04801e94f572",
"temp": "83.26",
"humidity": "67.36",
"airq": "0.4901"
},
{
"_id": "5d1773eb43f7c1d87ee9acc5",
"time": "Fri Sep 19 1986 05:12:45 GMT+0000 (UTC)",
"index": 17,
"guid": "87df09a3-ecaf-4a6a-a35a-df981b953016",
"temp": "26.34",
"humidity": "28.65",
"airq": "0.9925"
},
{
"_id": "5d1773ebb0db60debd2d8c3f",
"time": "Sun Nov 22 2015 05:03:50 GMT+0000 (UTC)",
"index": 18,
"guid": "61ffbed6-fd90-4232-870c-e1980c2863a0",
"temp": "64.82",
"humidity": "79.17",
"airq": "0.9087"
},
{
"_id": "5d1773eb07606d94dc4a0328",
"time": "Fri Feb 05 1971 05:22:00 GMT+0000 (UTC)",
"index": 19,
"guid": "22fe1524-3502-49e2-affe-b5d22393c45b",
"temp": "97.67",
"humidity": "23.25",
"airq": "0.0403"
},
{
"_id": "5d1773eb7802940ca26376c8",
"time": "Sun Feb 17 1974 20:57:47 GMT+0000 (UTC)",
"index": 20,
"guid": "9f597f5d-f52e-40c4-8f8d-d4b5ad4456de",
"temp": "106.66",
"humidity": "26.88",
"airq": "0.4622"
},
{
"_id": "5d1773eb1d7ecf862d92a464",
"time": "Sun Nov 25 2012 12:23:25 GMT+0000 (UTC)",
"index": 21,
"guid": "0de3b4fb-132e-47fa-bbe7-dfb32e98b1c8",
"temp": "83.67",
"humidity": "26.51",
"airq": "0.7425"
},
{
"_id": "5d1773eb94d221b29121bb80",
"time": "Tue Aug 28 1979 03:16:29 GMT+0000 (UTC)",
"index": 22,
"guid": "ed47cd2d-333c-4855-9ef1-4b09d475d934",
"temp": "68.44",
"humidity": "94.71",
"airq": "0.5258"
},
{
"_id": "5d1773eb0dd84d8eba72c542",
"time": "Sat Jul 21 2001 19:09:31 GMT+0000 (UTC)",
"index": 23,
"guid": "d9d0cada-2588-4990-a125-8db989c746ac",
"temp": "59.32",
"humidity": "26.46",
"airq": "0.3237"
},
{
"_id": "5d1773eb3697359ec26ba188",
"time": "Fri Dec 10 1999 07:52:46 GMT+0000 (UTC)",
"index": 24,
"guid": "1ba1d672-d587-4ec8-ba40-f7e40f764b1a",
"temp": "55.29",
"humidity": "21.06",
"airq": "0.1413"
},
{
"_id": "5d1773eb3d91347b2317bf40",
"time": "Mon Nov 04 1985 22:58:25 GMT+0000 (UTC)",
"index": 25,
"guid": "0053386c-de65-4c52-994e-f28305910403",
"temp": "44.48",
"humidity": "49.8",
"airq": "0.7187"
},
{
"_id": "5d1773eb4497cf3cabb21316",
"time": "Mon Aug 09 1982 18:52:08 GMT+0000 (UTC)",
"index": 26,
"guid": "aa3197ea-16ed-4520-b51e-4d4387b3347a",
"temp": "65.38",
"humidity": "30.7",
"airq": "0.6149"
},
{
"_id": "5d1773eb9aa7595182759e63",
"time": "Mon Jan 24 2000 04:08:21 GMT+0000 (UTC)",
"index": 27,
"guid": "222851bd-42e2-4a0c-bb0b-ee4cfc0c1c4e",
"temp": "55.61",
"humidity": "19.98",
"airq": "0.8211"
},
{
"_id": "5d1773ebe44b21c56d2edd23",
"time": "Mon Mar 05 2018 02:57:35 GMT+0000 (UTC)",
"index": 28,
"guid": "cb45a299-b00a-49f0-acfb-54f1b5b0f899",
"temp": "103.85",
"humidity": "33.43",
"airq": "0.8009"
},
{
"_id": "5d1773eb6309397a814a7f12",
"time": "Mon May 24 2010 03:35:23 GMT+0000 (UTC)",
"index": 29,
"guid": "f6b55da9-37a0-433a-ad08-b5b5a2b29ff7",
"temp": "56.44",
"humidity": "53.85",
"airq": "0.645"
},
{
"_id": "5d1773eb5d3aa0395fa9c8d9",
"time": "Tue Oct 01 1985 15:22:40 GMT+0000 (UTC)",
"index": 30,
"guid": "eb8020a3-22d3-4caa-86ae-1ea7f2f077a2",
"temp": "56.68",
"humidity": 15,
"airq": "0.7776"
},
{
"_id": "5d1773ebf82169813bc9950d",
"time": "Mon Dec 23 1996 09:07:32 GMT+0000 (UTC)",
"index": 31,
"guid": "9fc418d4-d645-44cb-be97-b584026ff18c",
"temp": "25.5",
"humidity": "62.92",
"airq": "0.8804"
},
{
"_id": "5d1773ebc611638a8301d008",
"time": "Wed Oct 21 1992 01:28:56 GMT+0000 (UTC)",
"index": 32,
"guid": "a52f92cf-1883-4ceb-925f-f9aa8f08d903",
"temp": "66.85",
"humidity": "72.9",
"airq": "0.3952"
},
{
"_id": "5d1773eb9c922aea3a72cdc7",
"time": "Fri Feb 14 1986 20:00:09 GMT+0000 (UTC)",
"index": 33,
"guid": "96bf5526-57d3-4b9d-81f2-34a26f5c04ad",
"temp": "60.91",
"humidity": "92.92",
"airq": "0.9765"
},
{
"_id": "5d1773eb3942d04c61ebd806",
"time": "Mon Mar 12 1984 07:03:30 GMT+0000 (UTC)",
"index": 34,
"guid": "2f016b89-15c7-4610-85e6-55db110c5722",
"temp": "67.38",
"humidity": "34.68",
"airq": "0.9463"
},
{
"_id": "5d1773ebbe83fec3e94e527f",
"time": "Wed May 09 1973 05:42:30 GMT+0000 (UTC)",
"index": 35,
"guid": "e819cae0-e515-4490-af6a-0339bb8c0412",
"temp": "101.06",
"humidity": "75.89",
"airq": "0.0234"
},
{
"_id": "5d1773eb9cab4d4aea4d26ce",
"time": "Sun Dec 29 1974 18:57:27 GMT+0000 (UTC)",
"index": 36,
"guid": "3356bda2-6b4b-4b27-8ccc-981c49098be3",
"temp": "99.63",
"humidity": "50.77",
"airq": "0.8214"
},
{
"_id": "5d1773ebaaa9b70309d64b75",
"time": "Thu Aug 09 2018 04:23:15 GMT+0000 (UTC)",
"index": 37,
"guid": "a5390ba8-a4e9-49d3-b731-3e6218f6e1b9",
"temp": "91.44",
"humidity": "46.86",
"airq": "0.5004"
},
{
"_id": "5d1773ebc232f50f6e20a34c",
"time": "Sun Jul 18 1982 09:44:37 GMT+0000 (UTC)",
"index": 38,
"guid": "c89d3a15-d33d-40cb-8ae9-ae435a33605a",
"temp": "27.47",
"humidity": "81.74",
"airq": "0.3787"
},
{
"_id": "5d1773eb953179fe800fe26a",
"time": "Thu Feb 27 2014 21:34:21 GMT+0000 (UTC)",
"index": 39,
"guid": "8e63b7bc-c78e-4943-ac51-967375ff304d",
"temp": "73.76",
"humidity": "10.96",
"airq": "0.3871"
},
{
"_id": "5d1773ebc3f08d7f4621c3ad",
"time": "Thu Jan 23 2014 22:52:30 GMT+0000 (UTC)",
"index": 40,
"guid": "b326d8e8-b0fb-48f8-9389-c510015ad17c",
"temp": "78.68",
"humidity": "53.43",
"airq": "0.5443"
},
{
"_id": "5d1773eb38e9e91e2c646b79",
"time": "Sat Aug 03 1985 20:44:17 GMT+0000 (UTC)",
"index": 41,
"guid": "9f9639b2-a499-4c47-858f-18e1e0048351",
"temp": "95.68",
"humidity": "90.54",
"airq": "0.3013"
},
{
"_id": "5d1773eb75fe5a5dd1c81b62",
"time": "Sat Mar 07 1970 10:35:48 GMT+0000 (UTC)",
"index": 42,
"guid": "d3875f88-f7f7-46ce-bd28-b4d7ed99dad5",
"temp": "110.72",
"humidity": "70.01",
"airq": "0.272"
},
{
"_id": "5d1773eb150abd236e5aace5",
"time": "Fri Sep 26 2003 13:12:32 GMT+0000 (UTC)",
"index": 43,
"guid": "257ef02e-083b-4f27-ad57-50ab0421cff6",
"temp": "80.97",
"humidity": "69.27",
"airq": "0.091"
},
{
"_id": "5d1773ebfad632240ad79763",
"time": "Thu Mar 21 2019 03:29:00 GMT+0000 (UTC)",
"index": 44,
"guid": "962b0ca3-788d-49f7-9664-5d31db3428bb",
"temp": "111.7",
"humidity": "20.86",
"airq": "0.0338"
},
{
"_id": "5d1773eb25bffd0f3c3fc632",
"time": "Mon May 17 1976 18:20:38 GMT+0000 (UTC)",
"index": 45,
"guid": "08e848ca-2f30-4dca-83cd-747ce7bb8696",
"temp": "114.85",
"humidity": "38.87",
"airq": "0.6914"
},
{
"_id": "5d1773eb456e334a3d3cf7e7",
"time": "Thu Feb 05 2015 10:01:56 GMT+0000 (UTC)",
"index": 46,
"guid": "8058ee57-31bd-42f9-93bb-427007c2eb2a",
"temp": "20.54",
"humidity": "54.52",
"airq": "0.3985"
},
{
"_id": "5d1773eb7ab00c8d7716bf8f",
"time": "Thu Apr 16 1981 12:46:19 GMT+0000 (UTC)",
"index": 47,
"guid": "62fba334-0a6a-4983-8d7c-e6eb468fb33a",
"temp": "57.89",
"humidity": "90.14",
"airq": "0.6791"
},
{
"_id": "5d1773eb0d61563373d8a427",
"time": "Fri Jul 15 2011 17:39:56 GMT+0000 (UTC)",
"index": 48,
"guid": "f4b212b8-f40a-45d4-956d-9b1ea4f88e03",
"temp": "33.55",
"humidity": "66.41",
"airq": "0.5755"
},
{
"_id": "5d1773eb92e6ca8086f88b75",
"time": "Mon Nov 18 2013 02:09:05 GMT+0000 (UTC)",
"index": 49,
"guid": "db83a674-11b0-4b60-853e-ccf2d35fa61c",
"temp": "22.29",
"humidity": "28.1",
"airq": "0.9799"
}
]
Raw
main_test.go
package main
import (
"context"
"database/sql"
"database/sql/driver"
"encoding/json"
"os"
"strconv"
"strings"
"testing"
"time"
"github.com/lib/pq"
)
func setupConn() (*sql.DB, error) {
return sql.Open("postgres", "dbname=postgres")
}
func testConn() (*sql.DB, error) {
return sql.Open("postgres", "dbname=test_db")
}
func setupDB() error {
pdb, err := setupConn()
if err != nil {
return err
}
defer pdb.Close()
_, err = pdb.Exec("CREATE DATABASE test_db")
db, err := testConn()
if err != nil {
return err
}
defer db.Close()
sqlstr := `CREATE TABLE IF NOT EXISTS events (
id TEXT NOT NULL,
time TIMESTAMPTZ,
index INT,
guid UUID,
temp REAL,
humidity REAL,
airq REAL)`
_, err = db.Exec(sqlstr)
if err != nil {
return err
}
return nil
}
func teardownDB() error {
db, err := testConn()
if err != nil {
return err
}
_, err = db.Exec("DROP TABLE IF EXISTS events")
if err != nil {
db.Close()
return err
}
db.Close()
pdb, err := setupConn()
if err != nil {
return err
}
defer pdb.Close()
_, err = pdb.Exec("DROP DATABASE test_db")
return err
}
// CustomTime wraps a time.Time so we can parse the incoming JSON format (see
// timeFormat)
type CustomTime struct {
time.Time
}
// This is the date format in our data file, the default for json-generator.com
const timeFormat = "Mon Jan 02 2006 15:04:05 GMT-0700 (UTC)"
// UnmarshalJSON implements the json.Unmarshaler interface
func (e CustomTime) UnmarshalJSON(b []byte) error {
s := strings.Trim(string(b), "\"")
t, err := time.Parse(timeFormat, s)
if err != nil {
return err
}
e.Time = t
return nil
}
// Value implements the sql.driver.Valuer interface, translating a CustomTime to
// a time.Time
func (e CustomTime) Value() (driver.Value, error) {
return e.Time, nil
}
// StrFloat is used to unmarshal our data's string float values to float32
// values
type StrFloat float32
// UnmarshalJSON implements the json.Unmarshaler interface
func (f StrFloat) UnmarshalJSON(b []byte) error {
s := strings.Trim(string(b), "\"")
f64, err := strconv.ParseFloat(s, 32)
if err != nil {
return err
}
f = StrFloat(f64)
return nil
}
// Event is our data type
type Event struct {
ID string `json:"id"`
Time CustomTime `json:"time"`
Index int `json:"index"`
GUID string `json:"guid"`
Temperature StrFloat `json:"temp"`
Humidity StrFloat `json:"humidity"`
AirQuality StrFloat `json:"airq"`
}
func getData() ([]Event, error) {
f, err := os.Open("data.json")
if err != nil {
return nil, err
}
dec := json.NewDecoder(f)
events := make([]Event, 0)
err = dec.Decode(&events)
f.Close()
if err != nil {
return nil, err
}
return events, nil
}
// lets us treat sql.DB and sql.Tx interchangeably
type executer interface {
Exec(sql string, args ...interface{}) (sql.Result, error)
}
// given a sql.DB or sql.Tx, insert events
func multiInsert(dbOrTx executer, events []Event) error {
for _, event := range events {
_, err := dbOrTx.Exec("INSERT INTO events (id, time, index, guid, temp, humidity, airq) VALUES ($1, $2, $3, $4, $5, $6, $7)",
event.ID, event.Time, event.Index, event.GUID, event.Temperature, event.Humidity, event.AirQuality)
if err != nil {
return err
}
}
return nil
}
// naive multi-insert; just db.Exec in a loop
func multipleInsertNoTx(db *sql.DB) error {
events, err := getData()
if err != nil {
return err
}
err = multiInsert(db, events)
return err
}
// less naive insert; use a transaction!
func multipleInsertWithTx(db *sql.DB) error {
events, err := getData()
if err != nil {
return err
}
tx, err := db.BeginTx(context.Background(), nil)
if err != nil {
return err
}
err = multiInsert(tx, events)
if err != nil {
return err
}
err = tx.Commit()
return err
}
// enlightened insert; use pq.CopyIn
func multipleInsertWithCopyIn(db *sql.DB) error {
events, err := getData()
if err != nil {
return err
}
tx, err := db.Begin()
if err != nil {
return err
}
stmt, err := tx.Prepare(pq.CopyIn("events", "id", "time", "index", "guid", "temp", "humidity", "airq"))
if err != nil {
return err
}
for _, event := range events {
_, err = stmt.Exec(event.ID, event.Time, event.Index, event.GUID, event.Temperature, event.Humidity, event.AirQuality)
if err != nil {
return err
}
}
_, err = stmt.Exec()
if err != nil {
return err
}
err = stmt.Close()
if err != nil {
return err
}
err = tx.Commit()
return err
}
func BenchmarkMultipleInsertNoTx(b *testing.B) {
err := setupDB()
if err != nil {
b.Fatalf("setting up db: %s", err)
}
defer teardownDB()
db, err := testConn()
if err != nil {
b.Fatalf("test conn: %s", err)
}
defer db.Close()
for i := 0; i < b.N; i++ {
err = multipleInsertNoTx(db)
if err != nil {
b.Fatalf("multipleInsertNoTx: %s", err)
}
}
}
func BenchmarkMultipleInsertWithTx(b *testing.B) {
err := setupDB()
if err != nil {
b.Fatalf("setting up db: %s", err)
}
defer teardownDB()
db, err := testConn()
if err != nil {
b.Fatalf("test conn: %s", err)
}
defer db.Close()
for i := 0; i < b.N; i++ {
err = multipleInsertWithTx(db)
if err != nil {
b.Fatalf("multipleInsertWithTx: %s", err)
}
}
}
func BenchmarkMultipleInsertWithCopyIn(b *testing.B) {
err := setupDB()
if err != nil {
b.Fatalf("setting up db: %s", err)
}
defer teardownDB()
db, err := testConn()
if err != nil {
b.Fatalf("test conn: %s", err)
}
defer db.Close()
for i := 0; i < b.N; i++ {
err = multipleInsertWithCopyIn(db)
if err != nil {
b.Fatalf("multipleInsertWithTx: %s", err)
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment