Hey everyone! I'm looking for some guidance on choosing the right database for my project. Here's the situation: I have around 1,000 users, with some having up to 1 million products in their warehouses. We receive data from suppliers every 2-4 hours that requires quick updates to the database. Each product has several fields, including warehouse ID, type (like car parts or screws), price, quantity, last update, tags, labels, and more. Users need to filter dynamically across about 80% of these fields, including tags and labels. The key requirements are: 1) very fast insert/update capabilities for both bulk (1000+ records) and individual records, 2) quick filtering across numerous fields, and 3) no transactions needed as data can simply be overwritten. Given these needs, which database would you recommend? Should I consider OpenSearch, MongoDB, or something else? Thanks!
4 Answers
Honestly, I don't see a need for NoSQL given your scale. Any standard relational database can manage this just fine. NoSQL is really beneficial if you have data access patterns that are constrained, which it sounds like you don't. By the way, in my experience, MongoDB isn't the best choice for scaling under heavy loads either.
For your scale and needs, I believe PostgreSQL or another relational database is ideal. It can handle millions of records every hour with ease, and it's built for querying efficiently.
I’d suggest starting with PostgreSQL. There's nothing in your requirements that excludes it, and if it can’t handle your needs, then you could explore NoSQL options. My team migrated a lot of work from large-scale MongoDB clusters back to PostgreSQL after finding its limitations. It's often harder to predict resource constraints with MongoDB, and managing working set memory can be tricky. Plus, check out the JSONB features in PostgreSQL—it might surprise you!
You mentioned needing a non-relational datastore, but your use case seems pretty relational at its core. I'd recommend using PostgreSQL and setting up an ETL pipeline for your data. It should work well for your requirements.
Related Questions
How to Build a Custom GPT Journalist That Posts Directly to WordPress
Cloudflare Origin SSL Certificate Setup Guide
How To Effectively Monetize A Site With Ads