How to keep up a payment gateway database performance high for fast user experience and fast transactions

If database of a payment gateway is slow then user experience will also be slow and would lead to low transaction success rate, it will have cascading effect for example when a page is taking time in loading then user will press back/refresh button and then it would lead to a failed transaction. A loss to the payment gateway and merchant both.

As a developer you know that computing powers/processors have got very powerful but input/output has not got such powers and speed, and slow input output becomes reason of poor performance many a times.

Your database system can kill you when, may not deliver the performance you want, it can be a database for which hackers will thank you, or its stored procedures logic can break when you make certain mistakes.  There can be many mistakes, but common of them are:

1. Coupled database schema: When database schema is too coupled, there will be many foreign keys and other constraints, leading to complexity and hence to poor performance. An open advice in this matter can be KISS” -  Keep it simple stupid
   
   
2. Many transactions table: If you have many tables storing transactions, like for one application service request if your database is storing information in many tables, two or three tables should be enough
   
   
3. Loose security: When you do not have different users for different roles and privileges, and you are using one or two admin account for all database operations, you have kept welcome door open for hackers to exploit you. If your database has critically important information, then it is better to keep it under a firewall as an offline machine. One should also take care of SQL injection at application layer
   
   
4. Multiple read/write operations in a service call: If while serving one online request you are hitting your database multiple times, for example you are performing multiple read/writes to display a page or providing response to a service request. Try to keep minimum back and forth between database and application
   
   
5. No caching: if you are not using any caching mechanism to access (static) information from database, you can be overheating your database system
   
   
6. Low network bandwidth or low speed or low IOPS between application server and database server could be another reason to watch for. Check your limits and see how much bandwidth you are consuming, check what are the peaks.
   

Concluding piece of advice can be, “Your database is not yours, its database of your clients, do allow them to use it, and do not allow hackers to enjoy it”

Thanks for reading