Split large Etherpad postgres database (Archive from Stackoverflow)

[wilkis3]

Question will be deleted from Stackoverflow by the Community Bot

I have a large postgres database. The sql dump has a size of 59GiB and hundreds of millions of entries (Etherpad).

How can I split the database by padIDs, groupIDs etc.? Postgres is very slow and needs 4 sec. per request and tools like ripgrep needs more then 10 hours to print all searched entries.

Are there any ideas how can I fix this problem? Any other tools or workarounds?

Current workaround

I have a list with all needed pad and groupIDs. Then I merge all IDs into a regex string (^[a-z0-9]+:g.xxxx|^[a-z0-9]+:g.xxxx|^[a-z0-9]+:g.xxxxxx). This string have a size of 2,6 MiB.

Now ripgrep comes with: rg --file=grep_regex.arg -N --regex-size-limit 3G -I etherpad.sql >> new-database.sql This ripgrep command takes more then 10 hours and that is way too long.

Technical setup

• Ubuntu 20.04, 64GB RAM, AMD Ryzen 7 3700X 8-Core Processor (16 CPUs)
• Postgres 12.9
• ripgrep 11.0.2

[wilkis3]

Comments

You could try increasing --dfa-size-limit to somethint very large, but given the size of your regex, you'll almost certainly have a bad time. A >2MB regex is huge, and your expectations are not particularly reasonable. The best case is building a DFA from that, which is what increasing the DFA size limit will hopefully do. –
BurntSushi5
Mar 14 at 22:43

Also, ten hours to just run a simple query on 59GB sounds too long to me personally. So something seems a little fishy here personally. But hard to say anything specific without a way to try it myself. –
BurntSushi5
Mar 14 at 22:44

(I'm the author of ripgrep btw. Full disclosure.) –
BurntSushi5
Mar 14 at 22:44

I voted to reopen this. This question could use a little more detail, but it is absolutely on topic! –
BurntSushi5
Mar 15 at 10:00

Or next time, just ask a question using GitHub Discussions on the ripgrep repo. –
BurntSushi5
Mar 15 at 11:56
Thanks. I increased the DFA and regex size limit to 40G and the command takes 2 hours. If I increase the DFA size limit to more then 40G, the process is terminated by the OOM killer. –
wilkis
Mar 16 at 18:15
What details do you need? –
wilkis
Mar 16 at 18:18

The OOM killer factoid indeed indicates that your regex is just too massive. So I think my previous statement about you having unreasonable expectations holds. :) –
BurntSushi5
Mar 16 at 22:22

In terms of detaila, I would want a way to reproduce it myself. So you would likely need to find a way to reproduce the problem.om data you can share, e.g., by scrubbing the data so that it doesn't contain amything you don't want to share. –
BurntSushi5
Mar 16 at 22:23

[BurntSushi]

Thanks for posting this! StackOverflow and the way people moderate it is completely beyond my comprehension.

FWIW, if you can somehow distill this down into something that I can reproduce, then it's likely I could do a quick analysis and come up with a more detailed answer. 59GB is of course too much to share (even if you could share the content), so the idea here would be to do one of the following: 1) reproduce the problem on a public data set, or 2) shrink your data set and censor it somehow in a way that your perf problems are still present, but on a different scale, or 3) write a short program that can generate the data set.

[wilkis3]

The data is not public(private notes from different peoples and groups). But I will try it to generate a data set with non-private data, this takes a little bit of time. (My next problem is that Postgres requires too many resources😄)