Skip to content

doutivity/research-online-postgres-go

BranchesTags

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 

History

16 Commits
.github/workflows
.github/workflows
 
 
internal/storage/postgres
internal/storage/postgres
 
 
migrations
migrations
 
 
.gitignore
.gitignore
 
 
LICENSE
LICENSE
 
 
Makefile
Makefile
 
 
README.md
README.md
 
 
batch_exec_update_online_storage.go
batch_exec_update_online_storage.go
 
 
batch_exec_update_online_storage_test.go
batch_exec_update_online_storage_test.go
 
 
batch_exec_upsert_online_storage.go
batch_exec_upsert_online_storage.go
 
 
batch_exec_upsert_online_storage_test.go
batch_exec_upsert_online_storage_test.go
 
 
docker-compose.yml
docker-compose.yml
 
 
go.mod
go.mod
 
 
go.sum
go.sum
 
 
main.go
main.go
 
 
main_test.go
main_test.go
 
 
online_storage.go
online_storage.go
 
 
online_storage_test.go
online_storage_test.go
 
 
sqlc.yaml
sqlc.yaml
 
 
tx_loop_update_online_storage.go
tx_loop_update_online_storage.go
 
 
tx_loop_update_online_storage_test.go
tx_loop_update_online_storage_test.go
 
 
tx_loop_upsert_online_storage.go
tx_loop_upsert_online_storage.go
 
 
tx_loop_upsert_online_storage_test.go
tx_loop_upsert_online_storage_test.go
 
 
unnest_update_online_storage.go
unnest_update_online_storage.go
 
 
unnest_update_online_storage_test.go
unnest_update_online_storage_test.go
 
 
unnest_upsert_online_storage.go
unnest_upsert_online_storage.go
 
 
unnest_upsert_online_storage_test.go
unnest_upsert_online_storage_test.go
 
 
user_online_pair.go
user_online_pair.go
 
 

Repository files navigation

Efficiently store online with PostgreSQL and Go

Support Ukraine 🇺🇦

Testing

make env-up
make docker-go-version
make docker-pg-version
make migrate-up
make go-test
make go-bench
make env-down

Schema

CREATE TABLE user_online
(
    user_id BIGINT    NOT NULL PRIMARY KEY,
    online  TIMESTAMP NOT NULL
);

Examples

TRUNCATE user_online;

INSERT INTO user_online (user_id, online)
VALUES (1, '2023-08-07 10:01:00'),
       (2, '2023-08-07 10:02:00'),
       (3, '2023-08-07 10:03:00'),
       (4, '2023-08-07 10:04:00'),
       (5, '2023-08-07 10:05:00'),
       (6, '2023-08-07 10:06:00'),
       (7, '2023-08-07 10:07:00'),
       (8, '2023-08-07 10:08:00'),
       (9, '2023-08-07 10:09:00'),
       (10, '2023-08-07 10:10:00'),
       (11, '2023-08-07 10:11:00'),
       (12, '2023-08-07 10:12:00')
ON CONFLICT (user_id) DO UPDATE
    SET online = excluded.online;

START TRANSACTION;
INSERT INTO user_online (user_id, online)
VALUES (1, '2023-08-07 11:01:00')
ON CONFLICT (user_id) DO UPDATE
    SET online = excluded.online;

INSERT INTO user_online (user_id, online)
VALUES (2, '2023-08-07 11:02:00')
ON CONFLICT (user_id) DO UPDATE
    SET online = excluded.online;
COMMIT;

START TRANSACTION;
UPDATE user_online
SET online = '2023-08-07 12:03:00'
WHERE user_id = 3;

UPDATE user_online
SET online = '2023-08-07 12:04:00'
WHERE user_id = 4;
COMMIT;

UPDATE user_online
SET online = CASE user_id
                 WHEN 5 THEN '2023-08-07 13:05:00'::TIMESTAMP
                 WHEN 6 THEN '2023-08-07 13:06:00'::TIMESTAMP
    END
WHERE user_id IN (5, 6);

UPDATE user_online AS to_t
SET online = from_t.online
FROM (
         VALUES (7, '2023-08-07 14:07:00'::TIMESTAMP),
                (8, '2023-08-07 14:08:00'::TIMESTAMP)
     ) AS from_t (user_id, online)
WHERE to_t.user_id = from_t.user_id;

-- version 1
SELECT *
FROM unnest(
             ARRAY[9, 10],
             ARRAY['2023-08-07 15:09:00'::TIMESTAMP, '2023-08-07 15:10:00'::TIMESTAMP]
         ) AS from_t (user_id, online);

-- version 2 supported https://github.com/sqlc-dev/sqlc/issues/958
SELECT unnest(ARRAY[9, 10])                                                              AS user_id,
       unnest(ARRAY['2023-08-07 15:09:00'::TIMESTAMP, '2023-08-07 15:10:00'::TIMESTAMP]) AS online;

-- version 1
UPDATE user_online AS to_t
SET online = from_t.online
FROM unnest(
             ARRAY[9, 10],
             ARRAY['2023-08-07 15:09:00'::TIMESTAMP, '2023-08-07 15:10:00'::TIMESTAMP]
         ) AS from_t (user_id, online)
WHERE to_t.user_id = from_t.user_id;

-- version 2
UPDATE user_online AS to_t
SET online = from_t.online
FROM (
         SELECT unnest(ARRAY[9, 10])                                                              AS user_id,
                unnest(ARRAY['2023-08-07 15:09:00'::TIMESTAMP, '2023-08-07 15:10:00'::TIMESTAMP]) AS online
     ) AS from_t
WHERE to_t.user_id = from_t.user_id;

-- version 1
INSERT INTO user_online (user_id, online)
VALUES (unnest(ARRAY[11, 12]),
        unnest(ARRAY['2023-08-07 16:11:00'::TIMESTAMP, '2023-08-07 16:12:00'::TIMESTAMP]))
ON CONFLICT (user_id) DO UPDATE
    SET online = excluded.online;

-- version 2
INSERT INTO user_online (user_id, online)
SELECT user_id, online
FROM unnest(
             ARRAY[11, 12],
             ARRAY['2023-08-07 16:11:00'::TIMESTAMP, '2023-08-07 16:12:00'::TIMESTAMP]
         ) AS from_t (user_id, online)
ON CONFLICT (user_id) DO UPDATE
    SET online = excluded.online;

-- version 3
INSERT INTO user_online (user_id, online)
SELECT user_id, online
FROM (
         SELECT unnest(ARRAY[11, 12])                                                             AS user_id,
                unnest(ARRAY['2023-08-07 16:11:00'::TIMESTAMP, '2023-08-07 16:12:00'::TIMESTAMP]) AS online
     ) AS from_t
ON CONFLICT (user_id) DO UPDATE
    SET online = excluded.online;

SELECT *
FROM user_online
ORDER BY user_id;

Benchmark (Postgres 15.3) (Go 1.20) (PC) Intel(R) Core(TM) i7-8750H CPU @ 2.20GHz

make go-bench
Name ns/op B/op allocs/op
TxLoopUpdate 63_966_207 168_056 5_003
TxLoopUpsert 69_837_876 168_056 5_003
UnnestUpdate 7_950_833 234_930 2_027
UnnestUpsert 234_930 2_027
BatchExecUpdate 18_686_485 495_235 5_030
BatchExecUpsert 19_463_064 503_235 5_030
name time/op
TxLoopUpdate 71.1ms ±12%
TxLoopUpsert 73.2ms ± 8%
UnnestUpdate 8.41ms ± 5%
UnnestUpsert
BatchExecUpdate 20.2ms ±10%
BatchExecUpsert 20.3ms ±10%
name B/op
TxLoopUpdate 160kB ± 0%
TxLoopUpsert 168kB ± 0%
UnnestUpdate 235kB ± 0%
UnnestUpsert 235kB ± 0%
BatchExecUpdate 495kB ± 0%
BatchExecUpsert 503kB ± 0%
name allocs/op
TxLoopUpdate 5.00k ± 0%
TxLoopUpsert 5.00k ± 0%
UnnestUpdate 2.03k ± 0%
UnnestUpsert 2.03k ± 0%
BatchExecUpdate 5.03k ± 0%
BatchExecUpsert 5.03k ± 0%

Benchmark (Postgres 16.0) (Go 1.21) (PC) Intel(R) Core(TM) i7-12700H

make go-bench
Name ns/op B/op allocs/op
TxLoopUpdate 19_786_396 160_135 5_005
TxLoopUpsert 20_168_659 168_135 5_005
UnnestUpdate 3_528_682 234_985 2_028
UnnestUpsert 4_129_712 234_985 2_028
BatchExecUpdate 6_984_122 495_315 5_032
BatchExecUpsert 6_630_488 503_316 5_032
name time/op
TxLoopUpdate 20.45ms ± 2%
TxLoopUpsert 26.59ms ± 24%
UnnestUpdate 3.785m ± 4%
UnnestUpsert 4.235m ± 6%
BatchExecUpdate 7.044ms ± 1%
BatchExecUpsert 7.004ms ± 8%
name B/op
TxLoopUpdate 156.4kB ± 0%
TxLoopUpsert 164.2kB ± 0%
UnnestUpdate 229.5kB ± 0%
UnnestUpsert 229.5kB ± 0%
BatchExecUpdate 483.7kB ± 0%
BatchExecUpsert 491.5kB ± 0%
name allocs/op
TxLoopUpdate 5.005k ± 0%
TxLoopUpsert 5.005k ± 0%
UnnestUpdate 2.028k ± 0%
UnnestUpsert 2.028k ± 0%
BatchExecUpdate 5.032k ± 0%
BatchExecUpsert 5.032k ± 0%

Benchmark (Postgres 16.0) (Go 1.21) (vultr.com VPS Bare Metal 32 GB 6 cores / 12 threads @ 4.0 GHz) ($185/month) Intel(R) Xeon(R) E-2286G CPU @ 4.00GHz

make go-bench
Name ns/op B/op allocs/op
TxLoopUpdate 68_896_024 160_135 5_005
TxLoopUpsert 67_894_824 168_135 5_005
UnnestUpdate 9_638_288 234_985 2_028
UnnestUpsert 234_985 2_028
BatchExecUpdate 17_348_125 495_315 5_032
BatchExecUpsert 17_150_259 503_316 5_032
name time/op
TxLoopUpdate 69.56ms ± 2%
TxLoopUpsert 70.65ms ± 4%
UnnestUpdate 10.20ms ± 5%
UnnestUpsert
BatchExecUpdate 17.81ms ± 1%
BatchExecUpsert 17.66ms ± 2%
name B/op
TxLoopUpdate 156.4kB ± 0%
TxLoopUpsert 164.2kB ± 0%
UnnestUpdate 229.5kB ± 0%
UnnestUpsert 229.5kB ± 0%
BatchExecUpdate 483.7kB ± 0%
BatchExecUpsert 491.5kB ± 0%
name allocs/op
TxLoopUpdate 5.005k ± 0%
TxLoopUpsert 5.005k ± 0%
UnnestUpdate 2.028k ± 0%
UnnestUpsert 2.028k ± 0%
BatchExecUpdate 5.032k ± 0%
BatchExecUpsert 5.032k ± 0%

Benchmark (Postgres 16.0) (Go 1.21) (vultr.com VPS Bare Metal 128 GB 8 cores / 16 threads @ 3.2 GHz) ($350/month) Intel(R) Xeon(R) E-2388G CPU @ 3.20GHz

make go-bench
Name ns/op B/op allocs/op
TxLoopUpdate 46_126_147 160_135 5_005
TxLoopUpsert 45_719_610 168_135 5_005
UnnestUpdate 5_123_888 234_985 2_028
UnnestUpsert 234_985 2_028
BatchExecUpdate 11_179_808 495_315 5_032
BatchExecUpsert 11_252_240 503_316 5_032
name time/op
TxLoopUpdate 46.83ms ± 1%
TxLoopUpsert 47.21ms ± 2%
UnnestUpdate 5.196ms ± 1%
UnnestUpsert
BatchExecUpdate 11.21ms ± 0%
BatchExecUpsert 11.36ms ± 1%
name B/op
TxLoopUpdate 156.4kB ± 0%
TxLoopUpsert 164.2kB ± 0%
UnnestUpdate 229.5kB ± 0%
UnnestUpsert 229.5kB ± 0%
BatchExecUpdate 483.7kB ± 0%
BatchExecUpsert 491.5kB ± 0%
name allocs/op
TxLoopUpdate 5.005k ± 0%
TxLoopUpsert 5.005k ± 0%
UnnestUpdate 2.028k ± 0%
UnnestUpsert 2.028k ± 0%
BatchExecUpdate 5.032k ± 0%
BatchExecUpsert 5.032k ± 0%

Benchmark (Postgres 16.0) (Go 1.21) (vultr.com VPS Bare Metal 256 GB 24 cores / 48 threads @ 2.9 GHz) ($725/month) AMD EPYC 7443P 24-Core Processor

make go-bench
Name ns/op B/op allocs/op
TxLoopUpdate 50_295_785 160_135 5_005
TxLoopUpsert 51_178_502 168_135 5_005
UnnestUpdate 4_817_787 234_985 2_028
UnnestUpsert 234_985 2_028
BatchExecUpdate 10_703_234 495_315 5_032
BatchExecUpsert 10_770_682 503_316 5_032
name time/op
TxLoopUpdate 52.05ms ± 3%
TxLoopUpsert 52.37ms ± 3%
UnnestUpdate 5.005ms ± 3%
UnnestUpsert
BatchExecUpdate 10.95ms ± 8%
BatchExecUpsert 11.38ms ± 9%
name B/op
TxLoopUpdate 156.4kB ± 0%
TxLoopUpsert 164.2kB ± 0%
UnnestUpdate 229.5kB ± 0%
UnnestUpsert 229.5kB ± 0%
BatchExecUpdate 483.7kB ± 0%
BatchExecUpsert 491.5kB ± 0%
name allocs/op
TxLoopUpdate 5.005k ± 0%
TxLoopUpsert 5.005k ± 0%
UnnestUpdate 2.028k ± 0%
UnnestUpsert 2.028k ± 0%
BatchExecUpdate 5.032k ± 0%
BatchExecUpsert 5.032k ± 0%

About

Efficiently store online with PostgreSQL and Go

dou.ua/forums/topic/35261/

Topics

postgresql

Resources

Readme

License

MIT license
Activity
Custom properties

Stars

2 stars

Watchers

0 watching

Forks

0 forks
Report repository

Releases

No releases published

Packages

No packages published

Languages