I did performance testing using Jmeter in QA and the number of records in QA DB were 30-40 times less than number of records in Prod DB.
For above reason, the performance in QA was very good but when application released in Prod, the performance was very bad.
How should the perf testing be done in Non-Prod that can simulate Prod conditions (in terms of DB size here)? What is the strategy/process?
The best option would be restoring the PROD database to QA environment, or even better have a full replica of PROD (this is known as STAGING environment), otherwise your test will not be accurate.
Also the ratio of scaling up the system is never 1x1, to wit if you have response time 1 second for database with 1 million of rows it doesn't necessarily mean that for 2 millions rows database the response time will be 2 seconds
If you cannot get a PROD replica you should run your tests on PROD directly in "dead" time like overnight or during the weekend.
If you cannot afford even that - you should be able to still perform certain activities like inspecting query plans and coming up with optimisation suggestions as if you make the query 2x times faster the same ration will be applied on production. You should be able to detect deadlocks, missing or inefficient indices, etc.
You should also be able to perform integration, interoperability and soak testing, but unfortunately that would be it when it comes to performance testing on scaled down environments.
As you said, you can't simulate real load if database is significantly smaller.
You should use similar, and if you can't, try to increase the load using other options, as more concurrent users, more transactions per second to match as close to real production load
