How To Dev

Introduction Besides the clauses mentioned in the previous article, PostgreSQL, as an exceptionally powerful database management system, supports many other specialized clauses as follows. Navigation and Pagination Group This is a basic yet crucial group that is very widely used. ORDER BY : Sorts the returned results in ascending ( ASC ) or descending ( DESC ) order. There is also ORDER BY col DESC NULLS LAST to push NULL values to the very bottom of the result table instead of the default top when sorting in descending order. LIMIT/OFFSET : Used for pagination. LIMIT N retrieves a maximum of N rows. OFFSET M skips the first M rows before retrieving. When you use OFFSET M , PostgreSQL must still scan and count all the first M rows to know where to begin, then it retrieves the next N rows for LIMIT . The consequence is that if OFFSET is small (for the first pages), the query still runs very fast. If OFFSET is large, such as using OFFSET 100000 , the database must expend resour...

Introduction When applying Pessimistic Locking, it means that the system is always defensive and assumes that "There will certainly be other people accessing to modify this row at the same time as me, so it is best to set up a barrier to lock this row right from the reading phase ( SELECT ) to reserve the spot." All types of Row-level Locks such as FOR SHARE, FOR KEY SHARE, FOR NO KEY UPDATE, FOR UPDATE in PostgreSQL are pure tools of the Pessimistic Locking mindset. Detail In the previous article, we explored Row-level locks and their characteristics. Now, I will guide you through its specific use cases when applied in a system like E-commerce. First, let us create the tables and seed data as follows. This is a simple schema to illustrate how to apply Pessimistic locking to solve problems, rather than being as comprehensive as a real-world production system. CREATE TABLE customers ( id INT PRIMARY KEY , name VARCHAR ( 100 ), phone VARCHAR ( 20 ), loyalty_p...