Tencent Cloud TDSQL-C (PostgreSQL Edition) How to solve the problem of seconds inventory deduction under high concurrency?

Introduction (pain point analysis)

As an architect or developer of an e-commerce platform, have you ever felt anxious about the second kill scenario when preparing for “618”, “Double 11” and other big promotions? When a large number of users come in at the same time, frantically clicking “Buy Now”, your system is facing a severe test:

• Inventory is oversold.Concurrent database read/write conflicts resulted in orders being successfully generated even though the actual inventory was sold out, resulting in capital loss and customer complaints.
• Database bottlenecks.Spike requests pour into the central database like a tsunami, causing the CPU and connection count to explode, response latency to soar, and even triggering an avalanche that brings the entire system to a standstill.
• Poor user experience.The page loads slowly, the buttons don't respond, the user can only see the “system is busy” message, and eventually leave in disappointment.

​One sentence summary.If you are struggling with inventory overselling, database bottlenecks and system stability in high concurrency spike scenarios, then this article will provide you with a complete, high-performance solution based on Tencent Cloud's TDSQL-C.

​Solution Architecture Diagram and Overview​

The figure below clearly shows the architecture of the Tencent Cloud TDSQL-C based spike solution.

Architecture diagram description: User requests are loaded through Tencent Cloud CDN accelerated static resources and then distributed through load balancing CLB. The business application layer is deployed on CVM and accesses Tencent Cloud Redis as a hot data cache. Most critically, the core transaction operation of inventory deduction is directly completed by the high-performance and highly compatible TDSQL-C (PostgreSQL version) database, ensuring absolute data consistency and high performance.

Workflow Overview.​

1. Traffic Access and Distribution.User requests are first accelerated by Tencent Cloud's CDN for static page loading, and then evenly distributed to back-end business server clusters through load balancing (CLB).
2. Read-write separation.When the business server reads non-core data such as product information, it prioritizes access to Tencent Cloud Redis cache, greatly reducing database pressure. For the core transaction of inventory deduction, the application directly connects to the TDSQL-C main instance.
3. Core Deduction.TDSQL-C utilizes PostgreSQL's powerful transaction processing capability and row-level locks to perform the process of “Query Inventory -> Determine if it is greater than 0 -> Deduct Inventory” at the database level with atomic operations, which can fundamentally eliminate overselling.
4. Results returned.After a successful deduction, the cache is updated and the successful result is returned to the user. The read-only instance is responsible for taking on read requests such as order queries to further share the pressure on the main database.

​Value Proposition.The solution through the “Redis cache hot data + TDSQL-C to protect the core transaction” architecture, not only to take advantage of the high performance of the cache, but also to ensure that the database in the very high concurrency of data consistency ability, a perfect solution to the core of the second kill pain points.

Core Products and Components​

core component	play a role	Key configuration/selection recommendations	Why choose it
​Tencent Cloud TDSQL-C (PostgreSQL Edition)​	​Core data layer for strong consistency transactions for inventory deductions.is the key to solving the oversold problem.	It is recommended to choose the Serverless version, which automatically expands and shrinks the capacity according to the actual computing resource usage, and can easily cope with the spike; the storage is billed according to the actual data volume, and the cost is optimal.	​Extreme performance.Compute and storage separation architecture, I/O performance is 2-3 times that of local SSDs, guaranteeing millisecond response. ​100% is PostgreSQL compatible.Smooth migration without modifying business code. ​Extreme High Availability.Multi-copy data redundancy, automatic failover, service availability up to 99.99%.
​Tencent Cloud Redis​	​Cache layer to cache hot product detail pages, spike status, etc.Takes the lion's share of read requests and protects the back-end database.	Choose memory-type specifications to ensure read and write speeds; set a reasonable expiration time; before the spike begins toCache Warmup。	​Ultra-high throughput.Supports hundreds of thousands of QPS, significantly reducing database load. ​Enrichment of data structures.Support for List, Set , etc., can be realized in seconds queue and other complex logic. ​Data persistence.Avoid data loss due to cache restart.
​Tencent Cloud Load Balancing (CLB)​	​Traffic portal that evenly distributes massive user requests to multiple back-end business servers.​	Configure it for Layer 4 (TCP) or Layer 7 (HTTP/HTTPS) listening and turn on health checking to automatically reject abnormal back-end servers.	​Ultra-high concurrency.A single cluster can support hundreds of millions of connections, easily coping with traffic floods. ​Highly available.Clustered deployment eliminates single points of failure. ​Flexible expansion and contraction.It can be automatically adjusted according to the flow rate.
​Cloud servers (CVM)/elastic scaling (AS)​	​The application server that runs the business logic.​	Use elastic scaling groups to save costs by automatically increasing the number of servers during the spike and decreasing it after the spike based on metrics such as CPU utilization or number of concurrent connections.	​Flexible configuration.A wide range of calculation specifications are available to choose from as required. ​Seamless integration with CLB.CVMs within a scaling group automatically register and deregister with the CLB.

​Summary of program benefits​

• ​⛓️ Put an end to overselling.Based on the strong transactional characteristics of TDSQL-C, it realizes accurate inventory deduction and avoids capital loss and customer complaints caused by overselling from the root.
• ⚡ Extreme performance.TDSQL-C's extreme I/O performance + Redis cache guarantees a stable and smooth system during the spike, and a silky smooth experience for users placing orders.
• 📈 Elastic Highly Available.Full-link high availability design (CLB, CVM scaling group, TDSQL-C multi-copy), no single point of failure in the system, and can be automatically scaled according to the traffic flow with controllable cost.
• 🛡️ Smooth Migration.TDSQL-C 100% is compatible with PostgreSQL, and existing services can be accessed with nearly zero modification, dramatically reducing the technical threshold and migration risk.

​ Application Scenarios and Applicable Customers​

• Core Scene.Instantaneous high concurrency scenarios such as seconds, rush, limited time specials, sweepstakes, etc. on e-commerce platforms.
• Applicable customer characteristics.​
  • The business has periodic or sudden traffic peaks, and there is a strong demand for system elastic expansion and contraction.
  • The data consistency requirements are so high that business vulnerabilities such as inventory overselling are unacceptable.
  • Currently using a PostgreSQL database and looking for a more robust and hassle-free cloud database solution.

​ Related links​