Hi, OP. First, congratulations on launching a product, and thank you for giving it strong copyleft! I ran it as directed, and it's pretty slick. I have some detailed comments on the database side of things that I hope you'll take seriously before trying to scale this. I've ran a distributed Postgres DB at scale for a well-known company that used `yjs` for precisely the same thing you're doing here, so I have some real-world experience with this.
You do not want to run this in Postgres, or any RDBMS for that matter. I promise you. Here [0] is `y-sweet` [1] discussing (at a shallow level) why persisting the actual content in an RDBMS isn't great. At $COMPANY, we ran Postgres on massive EC2s with native NVMe drives for storage, and they still struggled with this stuff (albeit with the rest of the app also using them). Use an object store, use an LSM-tree solution like MyRocks [2], just don't use an RDBMS, and especially not Postgres. It is uniquely bad at this. I'll explain.
Let's say I'm storing RFC2324 [3]. In TXT format, this is just shy of 20 KB. Even if it's 1/5th that size, it doesn't matter for the purposes of this discussion. As you may or may not know, Postgres uses something called TOAST [4] for storing large amounts of data (by default, any time a tuple hits 2 KB). This is great, except there's an overhead to de-TOAST things. This overhead can add up on retrievals.
Then there's WAL amplification. Postgres doesn't really do an `UPDATE`, it does a `DELETE` + `INSERT`. Even worse, it has to write entire pages (8 KB) [5], not just the changed content (there are circumstances in which this isn't true, but assume it is in general). Here's a view of `pg_stat_wal`, after I've been playing with it:
docmost=# SELECT wal_fpi, wal_bytes FROM pg_stat_wal:
wal_fpi | wal_bytes
---------+-----------
1641 | 11537465
(1 row)
Now I'll change a single byte in the aforementioned RFC, and run that again:
docmost=# SELECT wal_fpi, wal_bytes FROM pg_stat_wal;
wal_fpi | wal_bytes
---------+-----------
1654 | 11656052
(1 row)
That is nearly 120 KB of WAL written to change one byte. This is of course dependent upon the size of the document being edited, but it's always going to be bad.
Now let's look at the search query [6], which I've reproduced (mostly; I left out creator_id and the ORDER BY) here:
docmost=# EXPLAIN(ANALYZE, BUFFERS, COSTS) SELECT id, title, icon, parent_page_id, slug_id, creator_id, created_at, updated_at, ts_headline('english', text_content, to_tsquery('english', 'method'), 'MinWords=9, MaxWords=10, MaxFragments=10') FROM pages WHERE space_id = '01906698-1b7c-712b-8d4f-935930b03318' AND tsv @@ to_tsquery('english', 'method');
QUERY PLAN
----------------------------------------------------------------------------------------------------------
Seq Scan on pages (cost=0.00..12.95 rows=1 width=192) (actual time=13.473..48.684 rows=3 loops=1)
Filter: ((tsv @@ '''method'''::tsquery) AND (space_id = '01906698-1b7c-712b-8d4f-935930b03318'::uuid))
Rows Removed by Filter: 3
Buffers: shared hit=32
Planning:
Buffers: shared hit=1
Planning Time: 0.261 ms
Execution Time: 48.717 ms
~50 msec to do a relatively simple SELECT with no JOINs isn't great, and it's from the use of `ts_headline`. Unfortunately, it has to parse the original document, not just the tsvector summary to produce results. If I remove that function from the query, it plummets to sub-msec times, as I would expect.
It doesn't get better if I forcibly disable sequential scans to get it to favor the GIN index on `tsv` (unsurprising, given the small dataset):
QUERY PLAN
----------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on public.pages (cost=106.29..110.56 rows=1 width=192) (actual time=17.983..51.424 rows=3 loops=1)
Recheck Cond: (pages.tsv @@ '''method'''::tsquery)
Filter: (pages.space_id = '01906698-1b7c-712b-8d4f-935930b03318'::uuid)
Heap Blocks: exact=1
Buffers: shared hit=41
-> Bitmap Index Scan on pages_tsv_idx (cost=0.00..106.29 rows=1 width=0) (actual time=1.231..1.231 rows=7 loops=1)
Index Cond: (pages.tsv @@ '''method'''::tsquery)
Buffers: shared hit=25
Planning:
Buffers: shared hit=1
Planning Time: 0.343 ms
Execution Time: 51.647 ms
And speaking of GIN indices, while they're great for this, they also need regular maintenance, else you risk massive slowdowns [7]. This was after having inserted a few large-ish documents similar to the RFC, and creating a few short pages organically.
docmost=# SELECT * FROM pgstatginindex('pages_tsv_idx');
version | pending_pages | pending_tuples
---------+---------------+----------------
2 | 23 | 26
Let's force an early cleanup:
docmost=# EXPLAIN (ANALYZE, BUFFERS, COSTS) SELECT gin_clean_pending_list('pages_tsv_idx'::regclass);
QUERY PLAN
--------------------------------------------------------------------------------------
Result (cost=0.00..0.01 rows=1 width=8) (actual time=16.574..16.577 rows=1 loops=1)
Buffers: shared hit=4659 dirtied=47 written=22
Planning Time: 0.322 ms
Execution Time: 16.776 ms
17 msec doesn't sound like a lot, but bear in mind this was only hitting 4659 pages, or 37 MB. It can get worse.
You should also take a look at the DB config if you're to keep using it, starting with `shared_buffers`, since it's currently at the default value of 128 MB. That is not going to work well for anyone trying to use this for real work.
You should also optimize your column ordering. EDB has a great writeup [8] on why this matters.
Finally, I would like to commend you for using UUIDv7. While ideally I'd love (as someone who works with DBs) to see integers or natural keys, at least these are k-sortable. Oh, and foreign keys – thank you! They're so often eschewed in favor of "we'll handle it in the app", but they can absolutely save your data from getting borked.
[0]: https://digest.browsertech.com/archive/browsertech-digest-fi...
[1]: https://github.com/jamsocket/y-sweet
[2]: http://myrocks.io
[3]: https://www.rfc-editor.org/rfc/rfc2324.txt
[4]: https://www.postgresql.org/docs/current/storage-toast.html
[5]: https://wiki.postgresql.org/wiki/Full_page_writes
[6]: https://github.com/docmost/docmost/blob/main/apps/server/src...
[7]: https://gitlab.com/gitlab-com/gl-infra/production/-/issues/4...
[8]: https://www.enterprisedb.com/blog/rocks-and-sand