> docker compose exec postgres psql -U messenger -d postgres -c "CREATE DATABASE research;"
CREATE DATABASE
> docker compose run --rm --no-deps --entrypoint sh migrate -c 'URL=$(sed s,/messenger?,/research?, /run/secrets/database_url); goose -dir /migrations postgres $URL up'
Container mtusi2026lab2-migrate-run-37c6222942bf Creating 
Container mtusi2026lab2-migrate-run-37c6222942bf Created 
2026/10/07 19:30:06 OK   00001_init.sql (10.96ms)
2026/10/07 19:30:06 goose: successfully migrated database to version: 1
> docker compose exec postgres psql -U messenger -d research
psql (18.6 (Debian 18.6-1.pgdg13+2))
Type "help" for help.

research=# INSERT INTO users (id, username, display_name, password_hash) VALUES (uuidv7(), 'research_user', 'Research User', 'x');
INSERT 0 1
research=# INSERT INTO chats (id, title) SELECT uuidv7(), 'big';
INSERT 0 1
research=# INSERT INTO chats (id, title) SELECT uuidv7(), 'mid-' || g FROM generate_series(1, 5) g;
INSERT 0 5
research=# INSERT INTO chats (id, title) SELECT uuidv7(), 'small-' || g FROM generate_series(1, 200) g;
INSERT 0 200
research=# CREATE TABLE messages_seed AS
SELECT uuidv7() AS id,
       c.id AS chat_id,
       (SELECT id FROM users WHERE username = 'research_user') AS sender_id,
       'message ' || n || ' ' || repeat(md5(random()::text), 1 + (n % 4)) AS text,
       timestamptz '2026-01-01 00:00:00+00' + n * interval '1 minute' AS created_at
FROM chats c
CROSS JOIN LATERAL generate_series(1, CASE split_part(c.title, '-', 1)
    WHEN 'big' THEN 60000 WHEN 'mid' THEN 5000 ELSE 75 END) AS n;
SELECT 100000

A. Only PK
research=# SELECT 'VARIANT A' AS variant;
DROP INDEX IF EXISTS idx_messages_chat_history;
DROP INDEX IF EXISTS idx_messages_chat_id;

TRUNCATE messages;
INSERT INTO messages SELECT id, chat_id, sender_id, text, created_at FROM messages_seed;
TRUNCATE messages;
INSERT INTO messages SELECT id, chat_id, sender_id, text, created_at FROM messages_seed;
TRUNCATE messages;
INSERT INTO messages SELECT id, chat_id, sender_id, text, created_at FROM messages_seed;

ANALYZE messages;
SELECT pg_size_pretty(pg_relation_size('messages')) AS table, indexrelid::regclass AS idx, pg_size_pretty(pg_relation_size(indexrelid)) AS size FROM pg_index WHERE indrelid = 'messages'::regclass;
  variant  
-----------
 VARIANT A
(1 row)

Time: 0.345 ms
DROP INDEX
Time: 5.736 ms
NOTICE:  index "idx_messages_chat_id" does not exist, skipping
DROP INDEX
Time: 0.153 ms
TRUNCATE TABLE
Time: 3.614 ms
INSERT 0 100000
Time: 989.859 ms
TRUNCATE TABLE
Time: 6.348 ms
INSERT 0 100000
Time: 976.844 ms
TRUNCATE TABLE
Time: 6.268 ms
INSERT 0 100000
Time: 977.896 ms
ANALYZE
Time: 103.989 ms
 table |      idx      |  size   
-------+---------------+---------
 18 MB | messages_pkey | 3104 kB
(1 row)

Time: 0.765 ms

B. (chat_id, created_at, id)
research=# SELECT 'VARIANT B' AS variant;
DROP INDEX IF EXISTS idx_messages_chat_id;
CREATE INDEX idx_messages_chat_history ON messages (chat_id, created_at, id);

TRUNCATE messages;
INSERT INTO messages SELECT id, chat_id, sender_id, text, created_at FROM messages_seed;
TRUNCATE messages;
INSERT INTO messages SELECT id, chat_id, sender_id, text, created_at FROM messages_seed;
TRUNCATE messages;
INSERT INTO messages SELECT id, chat_id, sender_id, text, created_at FROM messages_seed;

ANALYZE messages;
SELECT pg_size_pretty(pg_relation_size('messages')) AS table, indexrelid::regclass AS idx, pg_size_pretty(pg_relation_size(indexrelid)) AS size FROM pg_index WHERE indrelid = 'messages'::regclass;
  variant  
-----------
 VARIANT B
(1 row)

Time: 0.411 ms
NOTICE:  index "idx_messages_chat_id" does not exist, skipping
DROP INDEX
Time: 0.144 ms
CREATE INDEX
Time: 165.646 ms
TRUNCATE TABLE
Time: 9.681 ms
INSERT 0 100000
Time: 1655.348 ms (00:01.655)
TRUNCATE TABLE
Time: 6.620 ms
INSERT 0 100000
Time: 1640.366 ms (00:01.640)
TRUNCATE TABLE
Time: 7.094 ms
INSERT 0 100000
Time: 1156.808 ms (00:01.157)
ANALYZE
Time: 92.376 ms
 table |            idx            |  size   
-------+---------------------------+---------
 18 MB | messages_pkey             | 3104 kB
 18 MB | idx_messages_chat_history | 5776 kB
(2 rows)

Time: 0.560 ms

C. (chat_id, id)
research=# SELECT 'VARIANT C' AS variant;
DROP INDEX IF EXISTS idx_messages_chat_history;
CREATE INDEX idx_messages_chat_id ON messages (chat_id, id);

TRUNCATE messages;
INSERT INTO messages SELECT id, chat_id, sender_id, text, created_at FROM messages_seed;
TRUNCATE messages;
INSERT INTO messages SELECT id, chat_id, sender_id, text, created_at FROM messages_seed;
TRUNCATE messages;
INSERT INTO messages SELECT id, chat_id, sender_id, text, created_at FROM messages_seed;

ANALYZE messages;
SELECT pg_size_pretty(pg_relation_size('messages')) AS table, indexrelid::regclass AS idx, pg_size_pretty(pg_relation_size(indexrelid)) AS size FROM pg_index WHERE indrelid = 'messages'::regclass;
  variant  
-----------
 VARIANT C
(1 row)

Time: 0.362 ms
DROP INDEX
Time: 4.459 ms
CREATE INDEX
Time: 61.503 ms
TRUNCATE TABLE
Time: 6.046 ms
INSERT 0 100000
Time: 1222.658 ms (00:01.223)
TRUNCATE TABLE
Time: 6.482 ms
INSERT 0 100000
Time: 1181.280 ms (00:01.181)
TRUNCATE TABLE
Time: 6.286 ms
INSERT 0 100000
Time: 1185.066 ms (00:01.185)
ANALYZE
Time: 98.084 ms
 table |         idx          |  size   
-------+----------------------+---------
 18 MB | messages_pkey        | 3104 kB
 18 MB | idx_messages_chat_id | 4880 kB
(2 rows)

Time: 0.490 ms
