Search error while using Datatable with huge data in NodeJS

Question

I have around 5,00,000 records in my database. I'm using DataTables to build an admin panel to manage the records. I have Node.js as backed with MongoDB.

I have used this library - https://www.npmjs.com/package/datatables-query

So far on page load I have successfully loaded results a shown in the below Image.

Whenever I type something in the search box, I get 500 error as shown in the screenshot.

What could be the problem here?

Is DataTable a good option for showing grid with huge amount of data or is there any better option considering Node.js, Express and MongoDB combo?

Here is my server side code.

app.post('/getUsersData',function(req, res) {
  var Model = require('./models/user'),
    datatablesQuery = require('datatables-query'),
    params = req.body,
    query = datatablesQuery(Model);

  query.run(params).then(function (users) {
    var data = JSON.stringify(users);
    // var data = JSON.stringify(users);

    res.end(data);
  }, function (err) {
    res.status(500).json(err);
  });
});

I have a table in MongoDB named User with 3 columns

1) Name

2) Email

3) Password

$(document).ready(function() {
  var table = $('#datatable').DataTable({
    // dom: 'Bfrtip',
    processing: true,
    serverSide: true,
    order: [[1, 'asc']],
    "aoColumnDefs": [ { "sClass": "hide_me", "aTargets": [ 0 ], visible: false } ], // first column in visible columns array gets class "hide_me"
    ajax: {
        url: "/getUsersData",
        type: 'POST',
        dataSrc: "data"
    },
    columns: [
        { data : "_id"},
        { data : "name" },
        { data : "email" },
        { data : "password" },
   ],
   responsive: true
  });
});

Answer 1

I would probably create an index on the data and search that index rather than searching the data itself. If you do however create a full text index you need to have a combination off all your cols in your collection and mongo allows only 1 full text index per collection.

As for alternatives, you could look into AWS's Elastic Search (which works just fine with MongoDB) or Sphinx Index (based on PostreSQL)

Edit: I know this answer doesn't actually answer the question in the slightest but I fear that the 500 error is not a memory issue in the application but rather on the DB (mongo is not like SQL, so don't design your applications like you would in SQL).

Some reading material, if you plan on changing db structure

http://rachbelaid.com/postgres-full-text-search-is-good-enough/
https://about.gitlab.com/2016/03/18/fast-search-using-postgresql-trigram-indexes/