mvryo

In payment gateway systems, the transaction table often grows rapidly, leading to performance bottlenecks. One effective way to scale a transactional database is   sharding   — splitting the database into smaller, more manageable pieces based on a shard key. In this guide we will walk through the process of sharding an existing transaction table in PostgreSQL, explaining key considerations, risks, and two popular sharding approaches:   row-based   and   table-based . Key Considerations and Risks Before diving into the implementation of sharding, it’s important to understand the key considerations and risks associated with this approach: Considerations: Sharding Key : The selection of a shard key is crucial. A good shard key ensures even distribution of data, while also aligning with the most frequent query patterns. For payment transactions,  transaction_date  or  customer_id  can be used. Database Complexity : Sharding increases the complexi...

In distributed systems, generating unique and reliable transaction reference numbers can be a challenging task, especially in payment gateway setups where race conditions may occur. During my experience with such a system, I encountered issues with race conditions caused by database triggers. In this guide we will dive into the pitfalls of generating transaction reference numbers, the approaches you can take to avoid these issues, and the lessons I learned along the way. Understanding the Problem Transaction reference numbers are essential in any payment gateway. These unique identifiers ensure every transaction is traceable and auditable, helping with both internal processing and external reporting. Initially, we implemented a  trigger-based approach  that generated structured reference numbers using a combination of prefixes, dates, and sequential numbers. While this approach worked well in a low transaction volume environment, the story changed when we scaled out to a highe...

As developers, we often face decisions that affect how we interact with databases in our applications. One critical choice is whether to use an ORM (Object-Relational Mapping) tool or stick with traditional non-ORM methods like writing raw SQL queries or using query builders. Each approach has its advantages and disadvantages, and the decision depends on several factors, such as the complexity of the queries, project requirements, and the design of the database itself. In this guide, we’ll explore the key differences between ORM and non-ORM approaches, discuss when to use each, and highlight the pros and cons to help you make the right decision for your next project. What is an ORM? An ORM is a tool or library that allows developers to interact with a relational database using an object-oriented paradigm. It maps database tables to classes, rows to objects, and columns to attributes, allowing developers to work with data using their programming language’s syntax rather than writing SQL...