How To Dev

Introduction Used to group rows of data with the same values in specified columns into a single row, with the main purpose being accompanied by Aggregate Functions such as SUM, AVG, COUNT, MAX, MIN . Note that all columns appearing in the SELECT clause that are not part of an aggregate function like SUM, COUNT must be declared in the GROUP BY clause. How it works When you execute a GROUP BY command, the Postgres Optimizer typically chooses one of the following two algorithms depending on the data volume and work_mem : HashAggregate : Uses a hashing algorithm Postgres scans through the data table once, hashes the value of the GROUP BY column for each row into a bucket in a Hash Table located in RAM and computes the aggregate function into that bucket if it already exists. Used when data is unsorted and the hash table can fit entirely within work_mem , which is usually the fastest method with a time complexity of O(N) since it only requires reading the table once after creating t...

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...

Introduction Table Partitioning is a feature that physically divides a large table into smaller tables (called partitions), but logically, your application still sees it as a single table. When creating a table partitioning (parent table), that table is actually just a virtual table containing metadata, it has no corresponding heap file on the hard disk and its storage size is zero. On the other hand, each partition (child table) created is an independent physical table. PostgreSQL will allocate a separate heap file for each child table. Advantages Query Performance: When querying data, PostgreSQL will activate the Partition Pruning mechanism to skip irrelevant partitions and only scan the necessary partition, reading only the exact heap file of that partition on the disk. Data Lifecycle Management: Data retention is extremely efficient because you do not have to operate on a single massive table, which would cause table locks and generate many WAL files that slow down the system For i...