The most efficient way to query a growing collection/table

Question

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.

Answer 1

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.