0

I'm asking this question for both SQL and NoSQL implementation as the principle is the same and I'm curious to hear peoples' thoughts (I'm a beginner who is curious about both, particularly MongoDB & Postgresql). I have a scenario where I need to query a growing collection/table and wonder whether I should split the data into two collection/tables instead of one. Essentially, as more members join, there will be a check (should I use a cron job?) that has to check a growing amount of data over time, and I wonder whether having another collection/table will reduce the amount of data that the cron job has to search through and hence improve performance.

I’ve made up an example below where there is a single ‘members’ collection/table that holds all member objects. Members can be of type PENDING or QUALIFIED where all new members are initially of type PENDING. When the date the new member has joined is roughly 3 months ago, I want the type field/column to change to QUALIFIED automatically.

The main question I have concerns the presumably decreasing performance as the number of members grows and the query will need to wade through more data due to the single collection/table.

Criteria:

  • Members become qualified in 3 months (for argument’s sake, I want to also say it needs a qualified column/field as there’s another requirement in my real use case)
  • Members can be of type A or B (column/field)
  • Need to be able to perform these searches (in an app):
    -Members of type A, unqualified
    -Members of type B, unqualified
    -Members of type A, qualified
    -Members of type B, qualified
    -Any member, qualified or not

My Current Setup:

NOSQL example member document inside a ‘members’ collection:

{
   _id: id,
   name: 'Joe',
   date: some_date,
   type: 'A', // or 'B'
   qualified: 'PENDING' // or 'QUALIFIED'
}

SQL example of a table would include the following columns:

ID Name Date Type Qualified
1 'Joe' date 'A' 'PENDING'

Concerns:

  • I’m worried about scalability: as more members join, the cron will need to search through a larger amount of already qualified users to determine which have exceeded the 3 month joining date

Questions:

  1. How should I go about making the qualified field/column automatically change if a member’s registration date has exceeded 3 months? (Would a cron job that runs once per day be suitable?)
  2. Should I split pending vs qualified members into 2 tables/collections or not? If not, is there a better way of doing this or is my current way ok and be ok for performance?
  3. If I used a separate collection/table for qualified vs pending users, will the search criteria require $lookups/joins? Presumably they would, and I wonder whether they would make it less performant.

Presumably if I use two collections/tables with qualified vs pending users, will the searches (in the criteria above, which should be pagination-friendly) become less efficient as they will require $lookups/joins? But on the other hand, will the cron that checks whether a member should become qualified or not be much more efficient since it would only query a smaller number of members? (If a member qualifies, it would set qualified to QUALIFIED and move the member to the qualified collection/table). I assume eventually over years and years that about 99% or more members would be qualified and 1% or less pending, yet it would have to query through them all if one collection/table is present, which makes me thing 2 collections/tables might be best?

I do not know which is the most efficient option. Any help, explanations, links to reading material, recommended books, etc would be greatly appreciated as a beginner (undecided about SQL vs NoSQL, possibly Postgresql vs MongoDB). Thank you.

1 Answer 1

2

This is really very simple in a relational database. All you need is a B-tree index on the qualified column. The speed of an index scan does not depend on the size of the table (or at least, not much), only on the size of the result set. So if you have more and more pending members, the query will get slower, but it won't get slower just because you have more members.

In PostgreSQL, you have the additional option of a partial index:

-- "qualified" had better be an ENUM or have a check constraint
CREATE INDEX ON members (/* relevant columns */) WHERE qualified = 'pending';

I cannot speak about MongoDB.

6
  • Interesting answer, thank you. If the number of new members joining remained at the same rate over a long time (and thus the result set remain around the same despite the increasing member size), how much slower would it eventually get? Would (or could) it eventually need to be split into separate tables? I have never heard of a B-tree index. Is there a book or something that you would recommend for a beginner to learn these important concepts? I also wonder whether I should store the qualified as a boolean (or 0/1) Oct 19, 2022 at 20:45
  • 1
    B-tree indexes are a standard concept and generally the default used in many database systems (for example, PostgreSQL and MongoDB). The answer here also applies to MongoDB: use an indexed field and consider using a partial index if you only commonly search on a subset of states. If you want to minimise I/O and complexity of application logic, I think it would be more typical to change the value of an existing record rather than moving it to a new table or collection.
    – Stennie
    Oct 20, 2022 at 0:21
  • 1
    The query will not become measurably slower as the table grows. The best book about B-tree indexes out there is certainly SQL Performance Explained, which covers not only PostgreSQL. Oct 20, 2022 at 4:12
  • Thank you both - very helpful information. I might buy that book Oct 20, 2022 at 21:31
  • 1
    NoSQL describes a broad set of database solutions that are not limited by the constraints of SQL. Document databases, graph databases, and specific products like MongoDB will all have different capabilities. For learning MongoDB I suggest the online docs (mongodb.com/docs) and free courses at MongoDB University (university.mongodb.com). Printed books lag behind the official docs, but the most comprehensive is probably O'Reilly's Definitive Guide, which was authored by MongoDB team members.
    – Stennie
    Oct 25, 2022 at 6:25

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.