Umm… I’m picking up hints of premature optimization here. I understand that speed is important here, but the way you describe the situation, you have a very small data set, that doesn’t seem to need to be updated more than occasionally. Is there some reason you need this to be performant over all other concerns? Development time and maintainability are typically your most expensive resource. What you describe SOUNDS like a relational database, so I would probably look at a SQL database as my first option. If speed really is your primary priority, you may do best with an in-memory substring search.
Is that answer helpful at all?
#NoSQL or SQL?
median crown
sharp phoenix
If you need to use full text search either pick a sql db like postgre if it's search indecies fit your needs. Or use a dedicated search db like Meilli or elastic.
median crown
80k is not a lot
80k is definitely not a lot for anything calling itself a database. It’s probably enough to make a primitive “flat file database” a bit clunky and inefficient and probably more than you want to keep in memory for a direct memory search as I originally mentioned… that said 80k does seem like quite a lot for any definition of “several” that I’ve heard before… lol
mental igloo
write calls should be slightly faster in mongo because it doesn't do as much relation checking etc, read calls should be orders of magnitude faster in sql
schema on write vs schema on read, basically
so if you do a lot more reading, you usually want relational dbs anyways
I would recomment to start out with either sqlite or postgres
postgres has good full text search abilities
use that as your main data store
and if you feel like the search is not good enough, have a meilisearch/elastic as a search service
elastic is pretty painful to deploy and maintain (imo), but I haven't tried meili yet so I can't say for sure if it's any better
noSQL is good when you have either NO CLUE what your data looks like (scraping HTML and putting blobs in the DB for example), or when you have a very specific purpose, like graph databases
median crown
@true kernel keep in mind, the biggest difference between SQL and NoSQL databases isn’t really performance, or even the SQL language, but the ACID guarantees:
- Atomicity - ensures all or none of a transaction (or statement outside of a transaction) is ever visible to any operation outside the transaction.
- Consistency - no operation will ever see data in a state that violates guarantees specified by the schema constraints
- Isolation - Even when multiple operations (or transactions) are being run concurrently, they will never see the database in a state that would not result from running them one at a time.
- Durability - once a change is made to the database state, no subsequent operation will see the data in a state with that change undone (unless and until the data is modified again).
Does that help?
mental igloo
To supplement my answer, the reason I do not recommend storing everything in the search db, even if what you want is essentially search, is that they do not provide the same guarantees a relational database does
Also mandatory don't use mongo because it's terrible at storing data (acid issues, retrocausal transactions...)
median crown
Also… while traditional database indices don’t generally work gracefully with full (non leading character) substring searches, it can be added where strategically necessary by adding a global substring table, and a join table for each field you want to substring search on. (Keep in mind the longer the minimum search string length, the more efficiently this can be done.)
mental igloo
Postgres docs should be a good introduction to full text search in general https://www.postgresql.org/docs/current/textsearch-intro.html
true kernel
NoSQL or SQL? (SOLVED)