Compare · 013

Pagination with defaults and limits: 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: rockpools
4 - name: rockpools
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

Migrations

db/migrations/0001_finds.up.sqlidentical
PostgreSQL
TimescaleDB
1CREATE TABLE finds (
1CREATE TABLE finds (
2 id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
2 id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
3 creature TEXT NOT NULL,
3 creature TEXT NOT NULL,
4 pool TEXT NOT NULL,
4 pool TEXT NOT NULL,
5 size_mm BIGINT NOT NULL
5 size_mm BIGINT NOT NULL
6);
6);
db/migrations/0001_finds.down.sqlidentical
PostgreSQL
TimescaleDB
1DROP TABLE finds;
1DROP TABLE finds;

Queries

LogFindidentical
PostgreSQL · db/queries/finds.sql
TimescaleDB · db/queries/finds.sql
1-- sgl.query(LogFind, exec)
1-- sgl.query(LogFind, exec)
2INSERT INTO finds (creature, pool, size_mm) VALUES ($1, $2, $3);
2INSERT INTO finds (creature, pool, size_mm) VALUES ($1, $2, $3);
ListFindsidentical
PostgreSQL · db/queries/finds.sql
TimescaleDB · db/queries/finds.sql
1-- sgl.query(ListFinds, many)
1-- sgl.query(ListFinds, many)
2-- $1 as sgl.param(page_size, default: 3, max: 5)
2-- $1 as sgl.param(page_size, default: 3, max: 5)
3-- $2 as sgl.param(page_offset)
3-- $2 as sgl.param(page_offset)
4SELECT id, creature, pool, size_mm
4SELECT id, creature, pool, size_mm
5FROM finds
5FROM finds
6ORDER BY id
6ORDER BY id
7LIMIT $1 OFFSET $2;
7LIMIT $1 OFFSET $2;
SearchFindsidentical
PostgreSQL · db/queries/finds.sql
TimescaleDB · db/queries/finds.sql
1-- sgl.query(SearchFinds, many)
1-- sgl.query(SearchFinds, many)
2-- $1 as sgl.param(creature, optional: true)
2-- $1 as sgl.param(creature, optional: true)
3-- $2 as sgl.param(page_size, default: 3, max: 5)
3-- $2 as sgl.param(page_size, default: 3, max: 5)
4-- $3 as sgl.param(page_offset)
4-- $3 as sgl.param(page_offset)
5SELECT id, creature, pool, size_mm
5SELECT id, creature, pool, size_mm
6FROM finds
6FROM finds
7WHERE ($1::text IS NULL OR creature = $1)
7WHERE ($1::text IS NULL OR creature = $1)
8ORDER BY size_mm DESC, id
8ORDER BY size_mm DESC, id
9LIMIT $2 OFFSET $3;
9LIMIT $2 OFFSET $3;

What sgl generates

LogFindidentical
PostgreSQL
TimescaleDB
1func (queries *Queries) LogFind(ctx context.Context, params LogFindParams) error
1func (queries *Queries) LogFind(ctx context.Context, params LogFindParams) error
2
2
3type LogFindParams struct {
3type LogFindParams struct {
4 Creature string
4 Creature string
5 Pool string
5 Pool string
6 SizeMm int64
6 SizeMm int64
7}
7}
ListFindsidentical
PostgreSQL
TimescaleDB
1func (queries *Queries) ListFinds(ctx context.Context, params ListFindsParams) ([]ListFindsRow, error)
1func (queries *Queries) ListFinds(ctx context.Context, params ListFindsParams) ([]ListFindsRow, error)
2
2
3type ListFindsParams struct {
3type ListFindsParams struct {
4 PageSize int
4 PageSize int
5 PageOffset int
5 PageOffset int
6}
6}
7
7
8type ListFindsRow struct {
8type ListFindsRow struct {
9 ID int64 `json:"id"`
9 ID int64 `json:"id"`
10 Creature string `json:"creature"`
10 Creature string `json:"creature"`
11 Pool string `json:"pool"`
11 Pool string `json:"pool"`
12 SizeMm int64 `json:"size_mm"`
12 SizeMm int64 `json:"size_mm"`
13}
13}
SearchFindsidentical
PostgreSQL
TimescaleDB
1func (queries *Queries) SearchFinds(ctx context.Context, params SearchFindsParams) ([]SearchFindsRow, error)
1func (queries *Queries) SearchFinds(ctx context.Context, params SearchFindsParams) ([]SearchFindsRow, error)
2
2
3type SearchFindsParams struct {
3type SearchFindsParams struct {
4 Creature *string
4 Creature *string
5 PageSize int
5 PageSize int
6 PageOffset int
6 PageOffset int
7}
7}
8
8
9type SearchFindsRow struct {
9type SearchFindsRow struct {
10 ID int64 `json:"id"`
10 ID int64 `json:"id"`
11 Creature string `json:"creature"`
11 Creature string `json:"creature"`
12 Pool string `json:"pool"`
12 Pool string `json:"pool"`
13 SizeMm int64 `json:"size_mm"`
13 SizeMm int64 `json:"size_mm"`
14}
14}

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/013_pagination/internal/store"
9 "seagull.sh/seagull/examples/postgres/013_pagination/internal/store"
10)
10)
11
11
12// run logs seven rock pool finds and pages through them. A parameter in a LIMIT or OFFSET
12// run logs seven rock pool finds and pages through them. A parameter in a LIMIT or OFFSET
13// clause becomes a plain int. On page_size, default: 3 replaces a zero value and max: 5
13// clause becomes a plain int. On page_size, default: 3 replaces a zero value and max: 5
14// caps a larger one, before the query runs, so a caller cannot ask for every row at once;
14// caps a larger one, before the query runs, so a caller cannot ask for every row at once;
15// a negative size or offset becomes 0.
15// a negative size or offset becomes 0.
16//
16//
17// SearchFinds is the shape of a search endpoint: an optional filter, a fixed order and a
17// SearchFinds is the shape of a search endpoint: an optional filter, a fixed order and a
18// page, all in one static query.
18// page, all in one static query.
19//
19//
20// Takes database (*sql.DB) which is a migrated database.
20// Takes database (*sql.DB) which is a migrated database.
21// Takes output (io.Writer) which receives each page.
21// Takes output (io.Writer) which receives each page.
22//
22//
23// Returns error when a query fails.
23// Returns error when a query fails.
24func run(ctx context.Context, database *sql.DB, output io.Writer) error {
24func run(ctx context.Context, database *sql.DB, output io.Writer) error {
25 queries := store.New(database)
25 queries := store.New(database)
26
26
27 finds := []store.LogFindParams{
27 finds := []store.LogFindParams{
28 {Creature: "crab", Pool: "north", SizeMm: 45},
28 {Creature: "crab", Pool: "north", SizeMm: 45},
29 {Creature: "starfish", Pool: "east", SizeMm: 80},
29 {Creature: "starfish", Pool: "east", SizeMm: 80},
30 {Creature: "crab", Pool: "east", SizeMm: 60},
30 {Creature: "crab", Pool: "east", SizeMm: 60},
31 {Creature: "blenny", Pool: "north", SizeMm: 70},
31 {Creature: "blenny", Pool: "north", SizeMm: 70},
32 {Creature: "crab", Pool: "south", SizeMm: 30},
32 {Creature: "crab", Pool: "south", SizeMm: 30},
33 {Creature: "prawn", Pool: "north", SizeMm: 25},
33 {Creature: "prawn", Pool: "north", SizeMm: 25},
34 {Creature: "crab", Pool: "north", SizeMm: 55},
34 {Creature: "crab", Pool: "north", SizeMm: 55},
35 }
35 }
36 for _, find := range finds {
36 for _, find := range finds {
37 if err := queries.LogFind(ctx, find); err != nil {
37 if err := queries.LogFind(ctx, find); err != nil {
38 return fmt.Errorf("logging a %s: %w", find.Creature, err)
38 return fmt.Errorf("logging a %s: %w", find.Creature, err)
39 }
39 }
40 }
40 }
41
41
42 pages := []struct {
42 pages := []struct {
43 label string
43 label string
44 parameters store.ListFindsParams
44 parameters store.ListFindsParams
45 }{
45 }{
46 {label: "size 0 uses the default of 3", parameters: store.ListFindsParams{PageSize: 0, PageOffset: 0}},
46 {label: "size 0 uses the default of 3", parameters: store.ListFindsParams{PageSize: 0, PageOffset: 0}},
47 {label: "the next page, offset 3", parameters: store.ListFindsParams{PageSize: 3, PageOffset: 3}},
47 {label: "the next page, offset 3", parameters: store.ListFindsParams{PageSize: 3, PageOffset: 3}},
48 {label: "size 50 is capped at 5", parameters: store.ListFindsParams{PageSize: 50, PageOffset: 0}},
48 {label: "size 50 is capped at 5", parameters: store.ListFindsParams{PageSize: 50, PageOffset: 0}},
49 }
49 }
50 for _, page := range pages {
50 for _, page := range pages {
51 listed, err := queries.ListFinds(ctx, page.parameters)
51 listed, err := queries.ListFinds(ctx, page.parameters)
52 if err != nil {
52 if err != nil {
53 return fmt.Errorf("listing finds, %s: %w", page.label, err)
53 return fmt.Errorf("listing finds, %s: %w", page.label, err)
54 }
54 }
55 fmt.Fprintf(output, "%s:\n", page.label)
55 fmt.Fprintf(output, "%s:\n", page.label)
56 for _, find := range listed {
56 for _, find := range listed {
57 fmt.Fprintf(output, " #%d %s in the %s pool\n", find.ID, find.Creature, find.Pool)
57 fmt.Fprintf(output, " #%d %s in the %s pool\n", find.ID, find.Creature, find.Pool)
58 }
58 }
59 }
59 }
60
60
61 for _, offset := range []int{0, 2} {
61 for _, offset := range []int{0, 2} {
62 crabs, err := queries.SearchFinds(ctx, store.SearchFindsParams{
62 crabs, err := queries.SearchFinds(ctx, store.SearchFindsParams{
63 Creature: new("crab"),
63 Creature: new("crab"),
64 PageSize: 2,
64 PageSize: 2,
65 PageOffset: offset,
65 PageOffset: offset,
66 })
66 })
67 if err != nil {
67 if err != nil {
68 return fmt.Errorf("searching crabs from %d: %w", offset, err)
68 return fmt.Errorf("searching crabs from %d: %w", offset, err)
69 }
69 }
70 fmt.Fprintf(output, "biggest crabs, offset %d:\n", offset)
70 fmt.Fprintf(output, "biggest crabs, offset %d:\n", offset)
71 for _, crab := range crabs {
71 for _, crab := range crabs {
72 fmt.Fprintf(output, " #%d %dmm\n", crab.ID, crab.SizeMm)
72 fmt.Fprintf(output, " #%d %dmm\n", crab.ID, crab.SizeMm)
73 }
73 }
74 }
74 }
75 return nil
75 return nil
76}
76}