-1

I am currently making a system for users to generate flashcards for Languages.
This involves adding information such as the definition, pronunciation, and example sentences for a word. So far, I have had success by loading all of the information into a Python Dictionary, however as I add more features/words, I will run out of memory.

I am curious if I should either move to loading text files on the fly, or move the information to a MySQL database. The most important metric is lookup speed, and I know a lot of work has gone into making SQL databases performant, however I may be able to have better performance by specializing the text files for my use case (sort the files alphabetically, or create a hash lookup system)

The information stored will be static, and users can choose what information they want to include in the flashcards. Right now, the dataset is about 10GB in-memory. Adding more features and languages will take this to above 50GB

I'd appreciate any recommendations on possible approaches

5
  • 1
    Why is lookup speed an issue? Do you load the whole data set for a language at once? When are you loading the data set or parts of it?
    – Jannik
    Commented Jun 23 at 10:44
  • 3
    If you're deciding between SQL RDBMS and juggling various files, there's a strong chance that you would really like SQLite: a flexible file-based datastore with ability to index your data and execute SQL queries. SQLite is likely to be faster than managing text files yourself. But it's still file-bound. If you have multiple writer processes, or have processes distributed across multiple nodes, you'd probably want a full RDBMS (probably Postgres, but MySQL/MariaDB can also be acceptable).
    – amon
    Commented Jun 23 at 12:30
  • @Jannik I would like users to submit a text file through a web app, and have a deck of cards returned. The faster I can do this, the better the experience it will be Commented Jun 25 at 21:24
  • 50GB of data does not constitute a 'large dataset' in 2024. At best, it's on the large end of small datasets.
    – JimmyJames
    Commented Jun 28 at 15:53

2 Answers 2

2

Storing data in memory with a hash table lookup will be faster than any remote database (assuming the same specs) due to the network latency.

Hash tables offer O(1) lookup complexity, while MySQL with InnoDB engine uses B-Trees with O(log n) lookup complexity.

However, you mentioned "users" so you have app/business running. When designing production systems, consider long-term scalability:

  • In-memory solutions tie data size to server capacity, limiting vertical scaling.
  • Adding server instances with in-memory storage requires full data replication so your instance would have to be as big as previous one - thus more expensive.

Concurrency is another concern. With in-memory (or plain files) structures, simultaneous write operations may require locking the entire structure, potentially causing bottlenecks. Databases provide mechanisms to handle concurrency efficiently.

If you want to keep the data as close as possible to the app server I recommend going with SQLite. You can keep it next to the app in the file, in-memory or remote when you decide to scale instances (litestream, rqlite, dqlite). Or if you are fine with full RDBMS then MySQL with tuning will be also good.

1

A database sounds suitable for your use case. If you use indices appropriately it should provide very fast lookups, on the order of tens to hundreds of milliseconds. But possibly the greatest advantage of databases is that there are many great implementations and tools available. If you do not want to deal with a separate database installation there are embedded databases like SQLite. One possible downside is that databases are a huge and complex topic that will require some effort to learn, especially when doing more advanced stuff. On the other hand there are often great documentation available for the more common use cases.

But there are use cases for using plain files as storage. You probably do not need all of the features provided by a database, especially if your data does not change. So you can create a implementation that is more specialized for your particular application, and avoid some of the overhead in a full database system. The downside is that you need to create that implementation yourself, with all costs and the risks that imply. Another downside is that any other developer will have a much harder time learning and understanding a custom storage system.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.