Compare · 022

Batch and copyfrom: PostgreSQL and TimescaleDB

PostgreSQL and TimescaleDB run the same files for this example: the SQL and the Go are identical.

Compare PostgreSQL withSQLiteCockroachDBTimescaleDBMySQLMariaDBDuckDBClickHouse

Configuration

sgl.yamlidentical
PostgreSQL
TimescaleDB
1version: 1
1version: 1
2
2
3databases:
3databases:
4 - name: tides
4 - name: tides
5 dialect: postgres
5 dialect: postgres
6 migrations: db/migrations
6 migrations: db/migrations
7 queries: db/queries
7 queries: db/queries
8 go:
8 go:
9 package: store
9 package: store
10 out: internal/store
10 out: internal/store
11 driver: database/sql
11 driver: database/sql
12 migrate:
12 migrate:
13 dsn: ${env:DATABASE_URL}
13 dsn: ${env:DATABASE_URL}

Migrations

db/migrations/0001_readings.up.sqlidentical
PostgreSQL
TimescaleDB
1CREATE TABLE readings (
1CREATE TABLE readings (
2 station TEXT NOT NULL,
2 station TEXT NOT NULL,
3 minute BIGINT NOT NULL,
3 minute BIGINT NOT NULL,
4 height_cm BIGINT NOT NULL,
4 height_cm BIGINT NOT NULL,
5 note TEXT,
5 note TEXT,
6 PRIMARY KEY (station, minute)
6 PRIMARY KEY (station, minute)
7);
7);
db/migrations/0001_readings.down.sqlidentical
PostgreSQL
TimescaleDB
1DROP TABLE readings;
1DROP TABLE readings;

Queries

RecordReadingsidentical
PostgreSQL · db/queries/readings.sql
TimescaleDB · db/queries/readings.sql
1-- sgl.query(RecordReadings, batch)
1-- sgl.query(RecordReadings, batch)
2INSERT INTO readings (station, minute, height_cm, note) VALUES ($1, $2, $3, $4);
2INSERT INTO readings (station, minute, height_cm, note) VALUES ($1, $2, $3, $4);
SummariseReadingsidentical
PostgreSQL · db/queries/readings.sql
TimescaleDB · db/queries/readings.sql
1-- sgl.query(SummariseReadings, many)
1-- sgl.query(SummariseReadings, many)
2SELECT station, COUNT(*) AS readings, COUNT(note) AS noted
2SELECT station, COUNT(*) AS readings, COUNT(note) AS noted
3FROM readings
3FROM readings
4GROUP BY station
4GROUP BY station
5ORDER BY station;
5ORDER BY station;

What sgl generates

RecordReadingsidentical
PostgreSQL
TimescaleDB
1func (queries *Queries) RecordReadings(ctx context.Context, params []RecordReadingsParams) error
1func (queries *Queries) RecordReadings(ctx context.Context, params []RecordReadingsParams) error
2
2
3type RecordReadingsParams struct {
3type RecordReadingsParams struct {
4 Station string
4 Station string
5 Minute int64
5 Minute int64
6 HeightCm int64
6 HeightCm int64
7 Note *string
7 Note *string
8}
8}
SummariseReadingsidentical
PostgreSQL
TimescaleDB
1func (queries *Queries) SummariseReadings(ctx context.Context) ([]SummariseReadingsRow, error)
1func (queries *Queries) SummariseReadings(ctx context.Context) ([]SummariseReadingsRow, error)
2
2
3type SummariseReadingsRow struct {
3type SummariseReadingsRow struct {
4 Station string `json:"station"`
4 Station string `json:"station"`
5 Readings int64 `json:"readings"`
5 Readings int64 `json:"readings"`
6 Noted int64 `json:"noted"`
6 Noted int64 `json:"noted"`
7}
7}

The program

example.goidentical
PostgreSQL
TimescaleDB
1package main
1package main
2
2
3import (
3import (
4 "context"
4 "context"
5 "database/sql"
5 "database/sql"
6 "fmt"
6 "fmt"
7 "io"
7 "io"
8
8
9 "seagull.sh/seagull/examples/postgres/022_bulk_writes/internal/store"
9 "seagull.sh/seagull/examples/postgres/022_bulk_writes/internal/store"
10)
10)
11
11
12const (
12const (
13 // minutesPerWeek is how many readings each tide gauge takes in a week.
13 // minutesPerWeek is how many readings each tide gauge takes in a week.
14 minutesPerWeek = 7 * 24 * 60
14 minutesPerWeek = 7 * 24 * 60
15)
15)
16
16
17// statementCounter is a store.DBTX that counts the statements sent through ExecContext.
17// statementCounter is a store.DBTX that counts the statements sent through ExecContext.
18// QueryContext and QueryRowContext come from the embedded *sql.DB.
18// QueryContext and QueryRowContext come from the embedded *sql.DB.
19type statementCounter struct {
19type statementCounter struct {
20 *sql.DB
20 *sql.DB
21
21
22 // statements is how many times ExecContext has been called.
22 // statements is how many times ExecContext has been called.
23 statements int
23 statements int
24}
24}
25
25
26// ExecContext counts the statement, then runs it on the database.
26// ExecContext counts the statement, then runs it on the database.
27//
27//
28// Takes query (string) which is the SQL to run.
28// Takes query (string) which is the SQL to run.
29// Takes arguments (...any) which are its bind values.
29// Takes arguments (...any) which are its bind values.
30//
30//
31// Returns sql.Result which is the database's result.
31// Returns sql.Result which is the database's result.
32// Returns error when the statement fails.
32// Returns error when the statement fails.
33func (counter *statementCounter) ExecContext(ctx context.Context, query string, arguments ...any) (sql.Result, error) {
33func (counter *statementCounter) ExecContext(ctx context.Context, query string, arguments ...any) (sql.Result, error) {
34 counter.statements++
34 counter.statements++
35 return counter.DB.ExecContext(ctx, query, arguments...)
35 return counter.DB.ExecContext(ctx, query, arguments...)
36}
36}
37
37
38// run records a week of readings from two tide gauges in one batch call, shows how many
38// run records a week of readings from two tide gauges in one batch call, shows how many
39// INSERT statements that took, and summarises what landed in the table.
39// INSERT statements that took, and summarises what landed in the table.
40//
40//
41// Takes database (*sql.DB) which is a migrated database.
41// Takes database (*sql.DB) which is a migrated database.
42// Takes output (io.Writer) which receives the statement counts and the summary.
42// Takes output (io.Writer) which receives the statement counts and the summary.
43//
43//
44// Returns error when a query fails.
44// Returns error when a query fails.
45func run(ctx context.Context, database *sql.DB, output io.Writer) error {
45func run(ctx context.Context, database *sql.DB, output io.Writer) error {
46 counter := &statementCounter{DB: database}
46 counter := &statementCounter{DB: database}
47 queries := store.New(counter)
47 queries := store.New(counter)
48
48
49 // An empty batch is a no-op: nothing is sent to the database.
49 // An empty batch is a no-op: nothing is sent to the database.
50 if err := queries.RecordReadings(ctx, nil); err != nil {
50 if err := queries.RecordReadings(ctx, nil); err != nil {
51 return fmt.Errorf("recording no readings: %w", err)
51 return fmt.Errorf("recording no readings: %w", err)
52 }
52 }
53 fmt.Fprintf(output, "0 readings sent in %d statements\n", counter.statements)
53 fmt.Fprintf(output, "0 readings sent in %d statements\n", counter.statements)
54
54
55 readings := tideReadings("harbour", "pier")
55 readings := tideReadings("harbour", "pier")
56
56
57 // A batch query takes a slice of Params, one per row. On database/sql the generated
57 // A batch query takes a slice of Params, one per row. On database/sql the generated
58 // method writes them as multi-row INSERTs, renumbering the placeholders $1, $2 and on,
58 // method writes them as multi-row INSERTs, renumbering the placeholders $1, $2 and on,
59 // chunked so that no statement binds more than 65535 variables, the most the PostgreSQL
59 // chunked so that no statement binds more than 65535 variables, the most the PostgreSQL
60 // protocol can carry: four columns a row allows 16383 rows a statement, so a week from
60 // protocol can carry: four columns a row allows 16383 rows a statement, so a week from
61 // two gauges takes two. copyfrom generates the same code on database/sql; only the pgx
61 // two gauges takes two. copyfrom generates the same code on database/sql; only the pgx
62 // driver uses COPY.
62 // driver uses COPY.
63 //
63 //
64 // Each chunk is its own statement, so run the call inside RunInTx (concept 23) when
64 // Each chunk is its own statement, so run the call inside RunInTx (concept 23) when
65 // every row must land or none.
65 // every row must land or none.
66 if err := queries.RecordReadings(ctx, readings); err != nil {
66 if err := queries.RecordReadings(ctx, readings); err != nil {
67 return fmt.Errorf("recording readings: %w", err)
67 return fmt.Errorf("recording readings: %w", err)
68 }
68 }
69 fmt.Fprintf(output, "%d readings sent in %d statements\n", len(readings), counter.statements)
69 fmt.Fprintf(output, "%d readings sent in %d statements\n", len(readings), counter.statements)
70
70
71 summary, err := queries.SummariseReadings(ctx)
71 summary, err := queries.SummariseReadings(ctx)
72 if err != nil {
72 if err != nil {
73 return fmt.Errorf("summarising readings: %w", err)
73 return fmt.Errorf("summarising readings: %w", err)
74 }
74 }
75 for _, row := range summary {
75 for _, row := range summary {
76 fmt.Fprintf(output, "%-8s %d readings, %d with a note\n", row.Station, row.Readings, row.Noted)
76 fmt.Fprintf(output, "%-8s %d readings, %d with a note\n", row.Station, row.Readings, row.Noted)
77 }
77 }
78 return nil
78 return nil
79}
79}
80
80
81// tideReadings makes one reading a minute for a week at each station. The height rises
81// tideReadings makes one reading a minute for a week at each station. The height rises
82// and falls in a simple saw-tooth, and the reading on the hour carries a note.
82// and falls in a simple saw-tooth, and the reading on the hour carries a note.
83//
83//
84// Takes stations (...string) which name the gauges.
84// Takes stations (...string) which name the gauges.
85//
85//
86// Returns []store.RecordReadingsParams which holds every reading, ready for the batch.
86// Returns []store.RecordReadingsParams which holds every reading, ready for the batch.
87func tideReadings(stations ...string) []store.RecordReadingsParams {
87func tideReadings(stations ...string) []store.RecordReadingsParams {
88 readings := make([]store.RecordReadingsParams, 0, len(stations)*minutesPerWeek)
88 readings := make([]store.RecordReadingsParams, 0, len(stations)*minutesPerWeek)
89 for _, station := range stations {
89 for _, station := range stations {
90 for minute := range int64(minutesPerWeek) {
90 for minute := range int64(minutesPerWeek) {
91 var note *string
91 var note *string
92 if minute%60 == 0 {
92 if minute%60 == 0 {
93 note = new(fmt.Sprintf("hour %d check", minute/60))
93 note = new(fmt.Sprintf("hour %d check", minute/60))
94 }
94 }
95 readings = append(readings, store.RecordReadingsParams{
95 readings = append(readings, store.RecordReadingsParams{
96 Station: station,
96 Station: station,
97 Minute: minute,
97 Minute: minute,
98 HeightCm: 200 + minute%360,
98 HeightCm: 200 + minute%360,
99 Note: note,
99 Note: note,
100 })
100 })
101 }
101 }
102 }
102 }
103 return readings
103 return readings
104}
104}