research-online-postgres-go

Efficiently store online with PostgreSQL and Go

• Збереження стану онлайну користувача в Redis
• Hash, Set чи Sorted set. Який тип даних вибрати для збереження стану онлайну користувача в Redis?
• Batch UPDATE в PostgreSQL

Support Ukraine 🇺🇦

• Help Ukraine via SaveLife fund
• Help Ukraine via Dignitas fund
• Help Ukraine via National Bank of Ukraine
• More info on war.ukraine.ua and MFA of 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

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

make go-bench

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

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

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