Compare · 024

Read replicas: PostgreSQL and TimescaleDB

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

Compare PostgreSQL withSQLiteCockroachDBTimescaleDB

Configuration

sgl.yamlidentical
PostgreSQL
TimescaleDB
1version: 1
1version: 1
2
2
3databases:
3databases:
4 - name: sightings
4 - name: sightings
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_sightings.up.sqlidentical
PostgreSQL
TimescaleDB
1CREATE TABLE sightings (
1CREATE TABLE sightings (
2 id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
2 id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
3 species TEXT NOT NULL,
3 species TEXT NOT NULL,
4 place TEXT NOT NULL
4 place TEXT NOT NULL
5);
5);
6
6
7CREATE FUNCTION sighting_label(species TEXT, place TEXT) RETURNS TEXT
7CREATE FUNCTION sighting_label(species TEXT, place TEXT) RETURNS TEXT
8LANGUAGE sql IMMUTABLE STRICT
8LANGUAGE sql IMMUTABLE STRICT
9AS $$ SELECT species || ' at ' || place $$;
9AS $$ SELECT species || ' at ' || place $$;
10
10
11CREATE FUNCTION log_sighting(new_species TEXT, new_place TEXT) RETURNS BIGINT
11CREATE FUNCTION log_sighting(new_species TEXT, new_place TEXT) RETURNS BIGINT
12LANGUAGE plpgsql VOLATILE
12LANGUAGE plpgsql VOLATILE
13AS $$
13AS $$
14DECLARE
14DECLARE
15 new_id BIGINT;
15 new_id BIGINT;
16BEGIN
16BEGIN
17 INSERT INTO sightings (species, place) VALUES (new_species, new_place) RETURNING id INTO new_id;
17 INSERT INTO sightings (species, place) VALUES (new_species, new_place) RETURNING id INTO new_id;
18 RETURN new_id;
18 RETURN new_id;
19END;
19END;
20$$;
20$$;
db/migrations/0001_sightings.down.sqlidentical
PostgreSQL
TimescaleDB
1DROP FUNCTION log_sighting(TEXT, TEXT);
1DROP FUNCTION log_sighting(TEXT, TEXT);
2DROP FUNCTION sighting_label(TEXT, TEXT);
2DROP FUNCTION sighting_label(TEXT, TEXT);
3DROP TABLE sightings;
3DROP TABLE sightings;

Queries

RecordSightingidentical
PostgreSQL · db/queries/sightings.sql
TimescaleDB · db/queries/sightings.sql
1-- sgl.query(RecordSighting, one)
1-- sgl.query(RecordSighting, one)
2INSERT INTO sightings (species, place) VALUES ($1, $2) RETURNING id;
2INSERT INTO sightings (species, place) VALUES ($1, $2) RETURNING id;
LogSightingidentical
PostgreSQL · db/queries/sightings.sql
TimescaleDB · db/queries/sightings.sql
1-- sgl.query(LogSighting, one)
1-- sgl.query(LogSighting, one)
2SELECT log_sighting(:species, :place) AS id;
2SELECT log_sighting(:species, :place) AS id;
ListSightingsidentical
PostgreSQL · db/queries/sightings.sql
TimescaleDB · db/queries/sightings.sql
1-- sgl.query(ListSightings, many)
1-- sgl.query(ListSightings, many)
2SELECT id, sighting_label(species, place) AS label FROM sightings ORDER BY id;
2SELECT id, sighting_label(species, place) AS label FROM sightings ORDER BY id;
CountSightingsidentical
PostgreSQL · db/queries/sightings.sql
TimescaleDB · db/queries/sightings.sql
1-- sgl.query(CountSightings, one, readonly: false)
1-- sgl.query(CountSightings, one, readonly: false)
2SELECT COUNT(*) AS total FROM sightings;
2SELECT COUNT(*) AS total FROM sightings;
ClaimSightingidentical
PostgreSQL · db/queries/sightings.sql
TimescaleDB · db/queries/sightings.sql
1-- sgl.query(ClaimSighting, one)
1-- sgl.query(ClaimSighting, one)
2SELECT id, species FROM sightings WHERE id = $1 FOR UPDATE;
2SELECT id, species FROM sightings WHERE id = $1 FOR UPDATE;
ForgetPlaceidentical
PostgreSQL · db/queries/sightings.sql
TimescaleDB · db/queries/sightings.sql
1-- sgl.query(ForgetPlace, many)
1-- sgl.query(ForgetPlace, many)
2WITH forgotten AS (DELETE FROM sightings WHERE place = $1 RETURNING id) SELECT id FROM forgotten ORDER BY id;
2WITH forgotten AS (DELETE FROM sightings WHERE place = $1 RETURNING id) SELECT id FROM forgotten ORDER BY id;

What sgl generates

RecordSightingidentical
PostgreSQL
TimescaleDB
1func (queries *Queries) RecordSighting(ctx context.Context, params RecordSightingParams) (RecordSightingRow, error)
1func (queries *Queries) RecordSighting(ctx context.Context, params RecordSightingParams) (RecordSightingRow, error)
2
2
3type RecordSightingParams struct {
3type RecordSightingParams struct {
4 Species string
4 Species string
5 Place string
5 Place string
6}
6}
7
7
8type RecordSightingRow struct {
8type RecordSightingRow struct {
9 ID int64 `json:"id"`
9 ID int64 `json:"id"`
10}
10}
LogSightingidentical
PostgreSQL
TimescaleDB
1func (queries *Queries) LogSighting(ctx context.Context, params LogSightingParams) (LogSightingRow, error)
1func (queries *Queries) LogSighting(ctx context.Context, params LogSightingParams) (LogSightingRow, error)
2
2
3type LogSightingParams struct {
3type LogSightingParams struct {
4 Species string
4 Species string
5 Place string
5 Place string
6}
6}
7
7
8type LogSightingRow struct {
8type LogSightingRow struct {
9 ID *int64 `json:"id"`
9 ID *int64 `json:"id"`
10}
10}
ListSightingsidentical
PostgreSQL
TimescaleDB
1func (queries *Queries) ListSightings(ctx context.Context) ([]ListSightingsRow, error)
1func (queries *Queries) ListSightings(ctx context.Context) ([]ListSightingsRow, error)
2
2
3type ListSightingsRow struct {
3type ListSightingsRow struct {
4 ID int64 `json:"id"`
4 ID int64 `json:"id"`
5 Label string `json:"label"`
5 Label string `json:"label"`
6}
6}
CountSightingsidentical
PostgreSQL
TimescaleDB
1func (queries *Queries) CountSightings(ctx context.Context) (CountSightingsRow, error)
1func (queries *Queries) CountSightings(ctx context.Context) (CountSightingsRow, error)
2
2
3type CountSightingsRow struct {
3type CountSightingsRow struct {
4 Total int64 `json:"total"`
4 Total int64 `json:"total"`
5}
5}
ClaimSightingidentical
PostgreSQL
TimescaleDB
1func (queries *Queries) ClaimSighting(ctx context.Context, id int64) (ClaimSightingRow, error)
1func (queries *Queries) ClaimSighting(ctx context.Context, id int64) (ClaimSightingRow, error)
2
2
3type ClaimSightingRow struct {
3type ClaimSightingRow struct {
4 ID int64 `json:"id"`
4 ID int64 `json:"id"`
5 Species string `json:"species"`
5 Species string `json:"species"`
6}
6}
ForgetPlaceidentical
PostgreSQL
TimescaleDB
1func (queries *Queries) ForgetPlace(ctx context.Context, place string) ([]ForgetPlaceRow, error)
1func (queries *Queries) ForgetPlace(ctx context.Context, place string) ([]ForgetPlaceRow, error)
2
2
3type ForgetPlaceRow struct {
3type ForgetPlaceRow struct {
4 ID int64 `json:"id"`
4 ID int64 `json:"id"`
5}
5}

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 "errors"
6 "errors"
7 "fmt"
7 "fmt"
8 "io"
8 "io"
9
9
10 "seagull.sh/seagull/examples/postgres/024_read_replicas/internal/store"
10 "seagull.sh/seagull/examples/postgres/024_read_replicas/internal/store"
11)
11)
12
12
13// connection is a store.DBTX that prints its name and each statement before running it on
13// connection is a store.DBTX that prints its name and each statement before running it on
14// the embedded database, so the output shows which connection served every query.
14// the embedded database, so the output shows which connection served every query.
15type connection struct {
15type connection struct {
16 *sql.DB
16 *sql.DB
17
17
18 // output receives a line per statement.
18 // output receives a line per statement.
19 output io.Writer
19 output io.Writer
20
20
21 // name labels the connection in the output.
21 // name labels the connection in the output.
22 name string
22 name string
23}
23}
24
24
25// ExecContext prints the statement, then runs it on the database.
25// ExecContext prints the statement, then runs it on the database.
26//
26//
27// Takes query (string) which is the SQL to run.
27// Takes query (string) which is the SQL to run.
28// Takes arguments (...any) which are its bind values.
28// Takes arguments (...any) which are its bind values.
29//
29//
30// Returns sql.Result which is the database's result.
30// Returns sql.Result which is the database's result.
31// Returns error when the statement fails.
31// Returns error when the statement fails.
32func (c connection) ExecContext(ctx context.Context, query string, arguments ...any) (sql.Result, error) {
32func (c connection) ExecContext(ctx context.Context, query string, arguments ...any) (sql.Result, error) {
33 fmt.Fprintf(c.output, "%s %s\n", c.name, query)
33 fmt.Fprintf(c.output, "%s %s\n", c.name, query)
34 return c.DB.ExecContext(ctx, query, arguments...)
34 return c.DB.ExecContext(ctx, query, arguments...)
35}
35}
36
36
37// QueryContext prints the query, then runs it on the database.
37// QueryContext prints the query, then runs it on the database.
38//
38//
39// Takes query (string) which is the SQL to run.
39// Takes query (string) which is the SQL to run.
40// Takes arguments (...any) which are its bind values.
40// Takes arguments (...any) which are its bind values.
41//
41//
42// Returns *sql.Rows which holds the result rows.
42// Returns *sql.Rows which holds the result rows.
43// Returns error when the query fails.
43// Returns error when the query fails.
44func (c connection) QueryContext(ctx context.Context, query string, arguments ...any) (*sql.Rows, error) {
44func (c connection) QueryContext(ctx context.Context, query string, arguments ...any) (*sql.Rows, error) {
45 fmt.Fprintf(c.output, "%s %s\n", c.name, query)
45 fmt.Fprintf(c.output, "%s %s\n", c.name, query)
46 return c.DB.QueryContext(ctx, query, arguments...)
46 return c.DB.QueryContext(ctx, query, arguments...)
47}
47}
48
48
49// QueryRowContext prints the query, then runs it on the database.
49// QueryRowContext prints the query, then runs it on the database.
50//
50//
51// Takes query (string) which is the SQL to run.
51// Takes query (string) which is the SQL to run.
52// Takes arguments (...any) which are its bind values.
52// Takes arguments (...any) which are its bind values.
53//
53//
54// Returns *sql.Row which holds the result row or the error.
54// Returns *sql.Row which holds the result row or the error.
55func (c connection) QueryRowContext(ctx context.Context, query string, arguments ...any) *sql.Row {
55func (c connection) QueryRowContext(ctx context.Context, query string, arguments ...any) *sql.Row {
56 fmt.Fprintf(c.output, "%s %s\n", c.name, query)
56 fmt.Fprintf(c.output, "%s %s\n", c.name, query)
57 return c.DB.QueryRowContext(ctx, query, arguments...)
57 return c.DB.QueryRowContext(ctx, query, arguments...)
58}
58}
59
59
60// sqlStateError is a server error that carries a SQLSTATE code, as pgx's *pgconn.PgError
60// sqlStateError is a server error that carries a SQLSTATE code, as pgx's *pgconn.PgError
61// does.
61// does.
62type sqlStateError interface {
62type sqlStateError interface {
63 error
63 error
64
64
65 // SQLState returns the five-character SQLSTATE code.
65 // SQLState returns the five-character SQLSTATE code.
66 SQLState() string
66 SQLState() string
67}
67}
68
68
69// run records sightings and reads them back through Queries built with NewWithReplica,
69// run records sightings and reads them back through Queries built with NewWithReplica,
70// printing which connection each statement went to. It then sends the queries that only
70// printing which connection each statement went to. It then sends the queries that only
71// look like reads straight to the reader, to show why Seagull routes them to the writer.
71// look like reads straight to the reader, to show why Seagull routes them to the writer.
72//
72//
73// Takes writer (*sql.DB) which is the migrated primary database.
73// Takes writer (*sql.DB) which is the migrated primary database.
74// Takes reader (*sql.DB) which is a read-only connection to the same data.
74// Takes reader (*sql.DB) which is a read-only connection to the same data.
75// Takes output (io.Writer) which receives the routing and the results.
75// Takes output (io.Writer) which receives the routing and the results.
76//
76//
77// Returns error when a query fails other than as the example expects.
77// Returns error when a query fails other than as the example expects.
78func run(ctx context.Context, writer, reader *sql.DB, output io.Writer) error {
78func run(ctx context.Context, writer, reader *sql.DB, output io.Writer) error {
79 // Seagull decides whether each query is read-only when it analyses it, and each
79 // Seagull decides whether each query is read-only when it analyses it, and each
80 // generated method uses the writer or the reader to match. On PostgreSQL a SELECT goes
80 // generated method uses the writer or the reader to match. On PostgreSQL a SELECT goes
81 // to the writer if it locks rows (FOR UPDATE), modifies data in a CTE, or calls a
81 // to the writer if it locks rows (FOR UPDATE), modifies data in a CTE, or calls a
82 // function whose data access is writing or unknown.
82 // function whose data access is writing or unknown.
83 queries := store.NewWithReplica(
83 queries := store.NewWithReplica(
84 connection{DB: writer, output: output, name: "writer"},
84 connection{DB: writer, output: output, name: "writer"},
85 connection{DB: reader, output: output, name: "reader"},
85 connection{DB: reader, output: output, name: "reader"},
86 )
86 )
87
87
88 herring, err := queries.RecordSighting(ctx, store.RecordSightingParams{Species: "herring gull", Place: "the pier"})
88 herring, err := queries.RecordSighting(ctx, store.RecordSightingParams{Species: "herring gull", Place: "the pier"})
89 if err != nil {
89 if err != nil {
90 return fmt.Errorf("recording a herring gull: %w", err)
90 return fmt.Errorf("recording a herring gull: %w", err)
91 }
91 }
92 if _, err := queries.RecordSighting(ctx, store.RecordSightingParams{Species: "kittiwake", Place: "the harbour wall"}); err != nil {
92 if _, err := queries.RecordSighting(ctx, store.RecordSightingParams{Species: "kittiwake", Place: "the harbour wall"}); err != nil {
93 return fmt.Errorf("recording a kittiwake: %w", err)
93 return fmt.Errorf("recording a kittiwake: %w", err)
94 }
94 }
95
95
96 // LogSighting is a SELECT, but log_sighting inserts a row, so it goes to the writer. A
96 // LogSighting is a SELECT, but log_sighting inserts a row, so it goes to the writer. A
97 // plpgsql function may return NULL, so ID is a pointer.
97 // plpgsql function may return NULL, so ID is a pointer.
98 logged, err := queries.LogSighting(ctx, store.LogSightingParams{Species: "puffin", Place: "the cliffs"})
98 logged, err := queries.LogSighting(ctx, store.LogSightingParams{Species: "puffin", Place: "the cliffs"})
99 if err != nil {
99 if err != nil {
100 return fmt.Errorf("logging a puffin: %w", err)
100 return fmt.Errorf("logging a puffin: %w", err)
101 }
101 }
102 if logged.ID == nil {
102 if logged.ID == nil {
103 return errors.New("log_sighting returned NULL")
103 return errors.New("log_sighting returned NULL")
104 }
104 }
105 fmt.Fprintf(output, " logged sighting %d\n", *logged.ID)
105 fmt.Fprintf(output, " logged sighting %d\n", *logged.ID)
106
106
107 // ListSightings calls sighting_label, whose body only concatenates its arguments, so it
107 // ListSightings calls sighting_label, whose body only concatenates its arguments, so it
108 // stays read-only and goes to the reader. With these non-NULL species and place columns,
108 // stays read-only and goes to the reader. With these non-NULL species and place columns,
109 // the concatenation produces a non-NULL Label string.
109 // the concatenation produces a non-NULL Label string.
110 listed, err := queries.ListSightings(ctx)
110 listed, err := queries.ListSightings(ctx)
111 if err != nil {
111 if err != nil {
112 return fmt.Errorf("listing sightings: %w", err)
112 return fmt.Errorf("listing sightings: %w", err)
113 }
113 }
114 for _, sighting := range listed {
114 for _, sighting := range listed {
115 fmt.Fprintf(output, " %d %s\n", sighting.ID, sighting.Label)
115 fmt.Fprintf(output, " %d %s\n", sighting.ID, sighting.Label)
116 }
116 }
117
117
118 // CountSightings is a SELECT, but its header says readonly: false, so it reads from the
118 // CountSightings is a SELECT, but its header says readonly: false, so it reads from the
119 // writer. A real replica lags behind the primary; a read that must see a write made a
119 // writer. A real replica lags behind the primary; a read that must see a write made a
120 // moment ago belongs on the writer.
120 // moment ago belongs on the writer.
121 count, err := queries.CountSightings(ctx)
121 count, err := queries.CountSightings(ctx)
122 if err != nil {
122 if err != nil {
123 return fmt.Errorf("counting sightings: %w", err)
123 return fmt.Errorf("counting sightings: %w", err)
124 }
124 }
125 fmt.Fprintf(output, " %d sightings on the primary\n", count.Total)
125 fmt.Fprintf(output, " %d sightings on the primary\n", count.Total)
126
126
127 // FOR UPDATE locks the row, which only the primary can do.
127 // FOR UPDATE locks the row, which only the primary can do.
128 claimed, err := queries.ClaimSighting(ctx, herring.ID)
128 claimed, err := queries.ClaimSighting(ctx, herring.ID)
129 if err != nil {
129 if err != nil {
130 return fmt.Errorf("claiming sighting %d: %w", herring.ID, err)
130 return fmt.Errorf("claiming sighting %d: %w", herring.ID, err)
131 }
131 }
132 fmt.Fprintf(output, " locked sighting %d, a %s\n", claimed.ID, claimed.Species)
132 fmt.Fprintf(output, " locked sighting %d, a %s\n", claimed.ID, claimed.Species)
133
133
134 // The DELETE inside the CTE makes the whole statement a write.
134 // The DELETE inside the CTE makes the whole statement a write.
135 forgotten, err := queries.ForgetPlace(ctx, "the cliffs")
135 forgotten, err := queries.ForgetPlace(ctx, "the cliffs")
136 if err != nil {
136 if err != nil {
137 return fmt.Errorf("forgetting the cliffs: %w", err)
137 return fmt.Errorf("forgetting the cliffs: %w", err)
138 }
138 }
139 fmt.Fprintf(output, " forgot %d sighting at the cliffs\n", len(forgotten))
139 fmt.Fprintf(output, " forgot %d sighting at the cliffs\n", len(forgotten))
140
140
141 return tryOnReader(ctx, store.New(reader), herring.ID, output)
141 return tryOnReader(ctx, store.New(reader), herring.ID, output)
142}
142}
143
143
144// tryOnReader sends three queries that write or lock straight to the reader, which
144// tryOnReader sends three queries that write or lock straight to the reader, which
145// refuses each one, as a replica would, with SQLSTATE 25006, read_only_sql_transaction.
145// refuses each one, as a replica would, with SQLSTATE 25006, read_only_sql_transaction.
146// pgx reports a server error as a *pgconn.PgError, and its SQLState method gives the
146// pgx reports a server error as a *pgconn.PgError, and its SQLState method gives the
147// code, which stays the same where the wording of the message does not.
147// code, which stays the same where the wording of the message does not.
148//
148//
149// Takes onReader (*store.Queries) which runs everything on the reader.
149// Takes onReader (*store.Queries) which runs everything on the reader.
150// Takes sighting (int64) which is a sighting to try to lock.
150// Takes sighting (int64) which is a sighting to try to lock.
151// Takes output (io.Writer) which receives each refusal.
151// Takes output (io.Writer) which receives each refusal.
152//
152//
153// Returns error when the reader does not refuse one of them.
153// Returns error when the reader does not refuse one of them.
154func tryOnReader(ctx context.Context, onReader *store.Queries, sighting int64, output io.Writer) error {
154func tryOnReader(ctx context.Context, onReader *store.Queries, sighting int64, output io.Writer) error {
155 attempts := []struct {
155 attempts := []struct {
156 name string
156 name string
157 call func() error
157 call func() error
158 }{
158 }{
159 {name: "RecordSighting", call: func() error {
159 {name: "RecordSighting", call: func() error {
160 _, err := onReader.RecordSighting(ctx, store.RecordSightingParams{Species: "gannet", Place: "the bay"})
160 _, err := onReader.RecordSighting(ctx, store.RecordSightingParams{Species: "gannet", Place: "the bay"})
161 return err
161 return err
162 }},
162 }},
163 {name: "LogSighting", call: func() error {
163 {name: "LogSighting", call: func() error {
164 _, err := onReader.LogSighting(ctx, store.LogSightingParams{Species: "gannet", Place: "the bay"})
164 _, err := onReader.LogSighting(ctx, store.LogSightingParams{Species: "gannet", Place: "the bay"})
165 return err
165 return err
166 }},
166 }},
167 {name: "ClaimSighting", call: func() error {
167 {name: "ClaimSighting", call: func() error {
168 _, err := onReader.ClaimSighting(ctx, sighting)
168 _, err := onReader.ClaimSighting(ctx, sighting)
169 return err
169 return err
170 }},
170 }},
171 }
171 }
172 fmt.Fprintln(output, "on the reader:")
172 fmt.Fprintln(output, "on the reader:")
173 for _, attempt := range attempts {
173 for _, attempt := range attempts {
174 err := attempt.call()
174 err := attempt.call()
175 if err == nil {
175 if err == nil {
176 return fmt.Errorf("%s on the reader: want a refusal from the server, got no error", attempt.name)
176 return fmt.Errorf("%s on the reader: want a refusal from the server, got no error", attempt.name)
177 }
177 }
178 refusal, hasState := errors.AsType[sqlStateError](err)
178 refusal, hasState := errors.AsType[sqlStateError](err)
179 if !hasState {
179 if !hasState {
180 return fmt.Errorf("%s on the reader: want a refusal from the server, got %w", attempt.name, err)
180 return fmt.Errorf("%s on the reader: want a refusal from the server, got %w", attempt.name, err)
181 }
181 }
182 fmt.Fprintf(output, " %s refused: SQLSTATE %s\n", attempt.name, refusal.SQLState())
182 fmt.Fprintf(output, " %s refused: SQLSTATE %s\n", attempt.name, refusal.SQLState())
183 }
183 }
184 return nil
184 return nil
185}
185}