1

I am building a project using node.js that is integrated with 4 other systems that keeps sending data from sensors every 1 second. I am trying to have like a timeline so I need to save that data, but I don't feel it's correct to hit a couple of insert statements every one second.

what is the best way to save data that is that redundant. I was thinking about having some log files and then insert data in bulk. Any suggestions?

Thank you.

Talha Awan
  • 4,573
  • 4
  • 25
  • 40
Hasan Al-Natour
  • 1,936
  • 2
  • 14
  • 22
  • You should look into mongo db or some similar nosql db to store data that frequently with relative ease – gaganshera Apr 16 '17 at 11:38
  • @gaganshera That is a stupid advise, considering PostgreSQL significantly outperforms those for multi-row insert operations, not to mention the data integrity that PostgreSQL brings with this by default. – vitaly-t Apr 16 '17 at 14:58

1 Answers1

2

This would make it a premature optimization. I've bench-marked PostgreSQL under Node.js many times. And at any given moment inserting several records per second will take under 10ms, i.e. less than 1% of your app's load, if you do it every second.

The only worthwhile optimization you should do from start - use multi-row insert, even if you insert only 2 rows at a time. The reasons for this are as follows:

  • Node.js IO is a valuable resource, so the fewer round trips you do the better
  • Multi-row inserts are tremendously faster than separate insert queries
  • Separate inserts typically require a transaction, and a single multi-row insert doesn't.

You can find a good example here: Multi-row insert with pg-promise.

Community
  • 1
  • 1
vitaly-t
  • 24,279
  • 15
  • 116
  • 138
  • what about if the data are in different tables , i can still do multi-row insertion ? – Hasan Al-Natour Apr 17 '17 at 13:27
  • @HasanAl-Natour each table would need its own insert query, obviously ;) – vitaly-t Apr 17 '17 at 19:24
  • Am think of queuing insertions using RabbitmQ and let another instance of the application or a worker do the insert using a promise , what do you think ? – Hasan Al-Natour Apr 18 '17 at 12:05
  • @HasanAl-Natour The choice is all yours, and I already expressed in my answer what I think about all that. You are trying to solve a fictional problem. – vitaly-t Apr 18 '17 at 12:52