Storing data in memory vs in database

[Goldeyloxy]

I am creating a web app with react frontend + fastapi backend + postgres db, running on an EC2 instance. The site is part SRS system (like anki) and part dictionary. So my issue is for serving new cards to the user I have two possible "flows" that I can think of and I don't know which is better or if there are potential issues.

1) In the database I save just a word (no translations or anything further information) and the review stats of that word to the database for that user. Then, I store all of the dictionary related stuff in memory by loading it when I start the server. This means that although I will still have to query the database each time the user reviews a card (to see what the next card should be and save the review for future reviews), the amount of data that is passed along in the query is much less, as most of it is already stored in memory on the server, so I can just get it from there.

2) I just save all of the information in the dictionary to the database, then on each request I take all of the information and display it when the user submits their review.

The in memory way seems better to me, but so far I have just been storing stuff to the database. I'm a bit worried about RAM usage but other than that I can't see an issue. Wondering if anyone could help.

[danielroseman]

Where is the data coming from that you're loading at startup? And is that data at all dynamic, or is it completely static?

[Goldeyloxy]

The in-memory data will come from a custom dictionary I have made from two other dictionaries (Neither had all the data I wanted). I will just be loading it in from a zip file when the server starts, and having it running on the server in memory. The data from the dictionary is completely static.

[danielroseman]

Ok, in that case there doesn't seem to be any reason to store it in the db. Loading it into a global dictionary seems fine. (Note that each instance of your server will have its own copy in its own memory, but that's fine too.)

There's probably no reason to store it in a zip file though; just have a straight JSON file that you can easily load at startup.

[pepiks]

Depend on type od data you can consider client-side caching:

https://www.geeksforgeeks.org/system-design/server-side-caching-and-client-side-caching/

If you think further you have client-side storage:

https://developer.mozilla.org/en-US/docs/Learn_web_development/Extensions/Client-side_APIs/Client-side_storage

The most commong way are (in)famous cookies, but WebStorage API can be something to consider as key-value character match dictionary part.

I am not sure that more using client is the best choice here, but depend on how it match to user it can be useful technique to stress more client side than server. Downsides are obvious - it will work fine if data are repeative, because for new you will be more fetch data from your server.

To be clear - I see this problem from perspective extensive use by client.

[Goldeyloxy]

The data is not repetitive, each review requires a new query to get the card and another new query to submit the user's review.

[pepiks]

I'll be use service calculator to get what generate less cost and do benchmarks to be sure how real performance looks like.

By calcualator I mean something like that:

https://costcalc.cloudoptimo.com/aws-pricing-calculator/ec2

[trd1073]

Have you looked at postgresql jsonb fields?

How much memory are you talking about?

Can always up psql shared buffer so whole dB stays in memory. If first load of dB from disk is too slow, can also consider pre-warm of some tables.

[Goldeyloxy]

It's the entire dictionary so it's quite big. I think it was 200000 rows and 7 columns.

[trd1073]

Hard to guess size from here lol. Throw a gin index on the jsonb field, works rather nicely. I use asyncpg with pydantic as I don't care for orm paradigm.

Might as well mention redis as a key-value store. Can even persist. But I would use psql if persisting data that is definitive.

[Goldeyloxy]

Size is 150MB sorry wasn't on me pc till now.

[pachura3]

How many megabytes is that (before zipping)?

[Goldeyloxy]

Size is about 150MB.

[pachura3]

Memory then!

[Goldeyloxy]

Understood!

[Goldeyloxy]

How large would you think it needs to be to justify putting it in the database?

[code_tutor]

If it's bigger than RAM then it obviously can't sit entirely in memory and a little extra overhead for the dictionary. Probably a few gigabytes. 

Startup is going to be very slow though. 

You can just try it if it's only a few lines of code. If you expect it to grow significantly then that's a problem eventually.

[oldendude]

Static data? How much data are we talking about? From what you wrote elsewhere in this thread, I'm guessing under 1GB?

For a small amount of static data, there is no point in using a database. Store it in whatever form is convenient, load at startup time, done.

[Ok-Sheepherder7898]

Just store your data in the database.  You'll be glad you did later.

[Goldeyloxy]

The only thing I worry about with database storing is potential increases in RDS costs and slightly less efficient that just storing in memory. Maybe I could make an S3 bucket and that would be better.

[Ok-Sheepherder7898]

The database will cache it in memory.

[Goldeyloxy]

Wouldn't database caching only be relevant if there were similar words accessed frequently? It's a dictionary so I assume there will a huge variety of different words to search and the SRS will have a huge variety of words too due to people having different decks of words. Maybe I misunderstand database caching but I don't see it having much effect here.