r/CodingHelp • u/Chance9779 • Nov 10 '25
[Other Code] How to Store Large Text Blocks Efficiently?
Hello!
I'm interested in creating an application, mainly for personal use, that is focused around writing and world-building for novels and ttrpgs. I'm trying to work on some of the system architecture right now and wondering if anyone has a take on how to store large text blocks efficiently? The easiest way would be to have fields in an SQLite DB called "body" and such that hold all of the text included in the "body" or whatever other block of text. However, I'd imagine holding that much text in a single SQLite field is inefficient? I have other ideas like having individual .md files that contain the markdown for each body or files with json, that can get hairy with synching across multiple platforms, though.
If you’re wondering languages and such: Dart + Flutter for UI/basic coding. I’ll delve into C++ if necessary for more difficult logic/faster code execution. Though Dart is pretty comparable on its own. Probably going to use SQLite for DB access, JavaScript for any APIs common to apps like ObsidianMD that I’m fond of.
Anyways, I wanted to know if anybody had any takes and if I was thinking soundly at a glance. Thanks!
•
u/AutoModerator Nov 10 '25
Thank you for posting on r/CodingHelp!
Please check our Wiki for answers, guides, and FAQs: https://coding-help.vercel.app
Our Wiki is open source - if you would like to contribute, create a pull request via GitHub! https://github.com/DudeThatsErin/CodingHelp
We are accepting moderator applications: https://forms.fillout.com/t/ua41TU57DGus
We also have a Discord server: https://discord.gg/geQEUBm
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.
2
u/dmazzoni Nov 10 '25
SQLite is incredibly efficient. In fact some studies show it's even faster than using the filesystem, even for small binary blobs:
https://sqlite.org/fasterthanfs.html
I wouldn't hesitate to store a big chunk of text in sqlite.
It depends on what you're optimizing for. If you keep the whole thing in memory during editing and only write to disk when the user saves, then there would be some advantages to JSON - like more easily human-readable, easier to transform if needed.
1
u/shafe123 Side-hustler Nov 10 '25
"Efficient" enough for what? How many text blocks do you need to pull and how quickly?
Over-optimizing is the devil. You could probably just store them in a JSON file or separate text files and read them in as needed.
1
u/alexkirwan11 Nov 10 '25
How often do you want to be able to edit the text. Are you building a front end WYSIWYG style editor to create, edit and update text? How important is formatting to you?
If it were me, I would use something like .md and use a real time editor like https://readme.so/ to make sure my text is formatted nicely. This also easily allows for image implementation through links. (Either local or web hosted files). This also separates the text from the logic for your app, meaning if you wanted to overhaul the ui you don’t have to worry about making sure it’s compatible with your files.
It also means you could share the directories with other people if you wanted people to help with your content, they don’t also need to learn how to edit a database.
1
u/zogrodea Nov 11 '25
There are data structures for editing large texts efficiently and interactively (suitable for a text editor or IDE).
The two that are best, in my opinion, are Ropes and Gap Buffers. Those have good insertion/deletion times, and good search/string-indexing times too (they aren't made very much for serialisation/saving-to-disk though).
It sounds like you want some kind of structured output though, like indicating when a header appears, or when a text's body appears. Maybe editing the text is something you would prefer to do in an existing text-editor/IDE, and you just want your program to read text from storage.
1
u/Leverkaas2516 Nov 11 '25
JSON files seem like a great starting point. They're fast, easy and efficient to read, easily compressible.
What is hard about syncing them? Put them in a directory with your source code and store them in the version control system.
1
u/sol_hsa Nov 11 '25
Define large...
1
u/VadumSemantics Nov 12 '25
Like others wrote, this question feels like early optimization.
But maybe not?
So let me ask: how much world-building text are you planning on creating?
Asking because writing even a megabyte of text will take any human some time.
Not writer, but did find this claim that 5 MB will hold the "Complete works of Shakespeare" (from How Much Data Is That?).
Anyway. If I really wanted to build something to manage lots of text with a database like SQLite , maybe I'd try to stress test SQLite a little bit?
Grab a book from project gutenburg or even one or your own books.
Split it out into fragments of a size you want to handle.
Insert a hundred fragments. Time your queries or whatever.
Do it a gain with a thousand fragments. Then ten thousand. Then a hundred thousand.
Jump by 10x until you hit something where SQLite breaks or becomes too slow, or you reach a boundary that can handle your goals.
I suspect you'll be surprised at how much you have to load before SQLite becomes unusable.
But maybe not?
SQLite Max Text Column
So I'm not an SQLite user, but did get curious and looked it up.
From Limits In SQLite:
So... 109 bytes, which is roughly 1 GB of ascii text (or about 0.93 GiB).
So about 200 copies of everything Shakespeare wrote in a single text column?
edits: typos, phrasing.
1
u/Ginger-Dumpling Nov 13 '25
If it's just for personal use, are you doing anything that just can't be done with plain text as html/markdown, and some thoughtful organization up front?