Skip to main content

Database Indexes

Ids: uuid v7​

chats.rooms.id and chats.messages.id are uuid, generated by chats.uuid_generate_v7(): time-ordered UUIDs (RFC 9562 UUIDv7), so ids stay roughly sequential like the old bigint identities and don't fragment the primary key indexes the way random UUIDv4 would. The function works on any Postgres version Supabase ships, without extensions (native uuidv7() only arrives with Postgres 18).

chats.users.id is not generated: it must match auth.users.id.

Generate message ids the same way on the client, with const Uuid().v7(): SupabaseChatController.sendMessage stores the id as-is, which is how it recognizes the Realtime echo of its optimistic copy.

Message partitioning​

chats.messages is a native PARTITION BY RANGE ("createdAt") table managed by pg_partman:

  • weekly partitions (p_interval => '7 days'), 4 created in advance;
  • a default partition (chats.messages_default) catches any row outside the generated range;
  • no retention: chat history is kept forever, partitioning only keeps indexes, vacuum and queries fast as history grows;
  • the hourly pg_cron job partman-run-maintenance runs partman.run_maintenance_proc() and chats.ensure_messages_partitions_rls() (RLS isn't inherited by partitions created later).

pg_partman is installed in its own partman schema, so its tables aren't exposed through the chats grants. To change the interval or add retention, adapt the partman.create_parent(...) call and the partman.part_config update in 01_database_schema.sql.

After a 1.x migration, weekly partitions start 4 weeks before the migration and older messages land in the default partition (one partition per week since the first message would mean hundreds of partitions, each scanned by per-room queries). Split it later with partman.partition_data_proc() if you want.

Primary keys​

Postgres requires the partition key in every unique index of a partitioned table, so the messages primary key is composite. Nothing references messages.id with a foreign key.

ALTER TABLE ONLY chats.users ADD CONSTRAINT users_pkey PRIMARY KEY (id);
ALTER TABLE ONLY chats.rooms ADD CONSTRAINT rooms_pkey PRIMARY KEY (id);
-- chats.room_members
PRIMARY KEY ("roomId", "userId")
ALTER TABLE ONLY chats.messages ADD CONSTRAINT messages_pkey PRIMARY KEY (id, "createdAt");

Secondary indexes​

-- Rooms list and membership lookups by user (the primary key covers lookups by room).
CREATE INDEX IF NOT EXISTS "room_members_userId_idx" ON chats.room_members USING btree ("userId");

-- Foreign key to auth.users.
CREATE INDEX ON chats.messages USING btree ("authorId");

-- Message pagination (newest first, per room), the last-message trigger and
-- rooms_l."unreadCount": all filter by room and order/compare by "createdAt".
CREATE INDEX IF NOT EXISTS "messages_roomId_createdAt_idx"
    ON chats.messages USING btree ("roomId", "createdAt" DESC);

-- Pinned messages: few rows per room, a partial index keeps it tiny.
CREATE INDEX IF NOT EXISTS "messages_roomId_pinned_idx"
    ON chats.messages USING btree ("roomId", "createdAt" DESC)
    WHERE pinned;

Indexes created on the partitioned parent are propagated by Postgres to every partition, current and future. The composite ("roomId", "createdAt" DESC) index supersedes the old messages_roomId_idx, which the script drops.