Using Clause (Part 2)

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 resources scanning through those 100,000 preceding rows without using them, causing the query to become progressively slower as users view the final pages.
FETCH FIRST N ROWS ONLY: This is an ANSI SQL standard clause, which has completely similar functionality to LIMIT N but is more standardized, helping code easily convert to other databases like Oracle or SQL Server.
- The standard syntax of this clause is fully equivalent to LIMIT, although it is longer, it reads very much like natural English and you can write this clause flexibly using synonyms:
  - FIRST or NEXT: You can write FETCH NEXT 5 ROWS ONLY (often used when accompanied by OFFSET).
  - ROW or ROWS: If retrieving 1 row, you can write FETCH FIRST 1 ROW ONLY.
  - Omitting the quantity: If you write FETCH FIRST ROWS ONLY (without a number), Postgres automatically understands that you only want to retrieve exactly the first row.
- Combining pagination with OFFSET
  - Just like LIMIT, FETCH FIRST can also be combined with OFFSET to implement the pagination feature.
  - According to the ANSI SQL standard, OFFSET will be written more explicitly as OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY.
- FETCH WITH TIES
  - This is an advanced feature of the ANSI standard clause that standard LIMIT cannot achieve (unless writing additional complex subqueries).
  - Suppose you want to retrieve the top 3 products with the highest prices, if you use LIMIT 3 as usual, you still get the correct result.
  - However, if the data has a special form where the first 4 rows all have the same price (or the 3rd and 4th products have equal prices), using the conventional method will only retrieve exactly 3 products, missing the products with the same price.
  - In this case, using the WITH TIES clause will resolve this issue, as it will retrieve the number of rows you requested, plus all rows that have values equal to the last row (tied).
  - The WITH TIES clause must be accompanied by ORDER BY, otherwise the data in the database will be in an unordered chaotic state and Postgres will not be able to determine the tied rows.

Concurrency Control

When working with systems that have multiple users accessing simultaneously, you need row-locking clauses to avoid race conditions:

FOR UPDATE: Locks the selected rows. Until the transaction ends, no one else is allowed to modify or delete these rows.
FOR SHARE: Locks the rows in "shared" mode. Other transactions can still read the data, but no one is allowed to change it until you finish your work.
SKIP LOCKED: Accompanies FOR UPDATE. If any row of data is currently locked by another process, Postgres will completely skip that row and proceed to read the next free row. This clause is the backbone for building high-performance message queue systems using a database.

Data Manipulation Extensions

ON CONFLICT (...) DO UPDATE/DO NOTHING: The clause that handles the upsert (insert + update) strategy. When you insert a new row into a table, if a conflict occurs duplicating the primary key or a unique constraint, you can command Postgres to automatically convert it into an UPDATE statement to update the new data, or DO NOTHING to skip it without reporting an error.
RETURNING: Normally when you run INSERT, UPDATE or DELETE, Postgres only returns a message indicating the number of affected rows. If you add the RETURNING col1, col2 clause, Postgres will additionally return the data of the rows that were just modified, just like a SELECT statement. You will save one query back to the table.

Optimization and Data Sampling Group

TABLESAMPLE SYSTEM/BERNOULLI: Used to take a random sample of data from a large table.
For example, using TABLESAMPLE BERNOULLI (1) will randomly sample 1% of the rows in the data table. This method runs much faster than using ORDER BY random() LIMIT on a large data set.

How It Works

When using ORDER BY random() LIMIT, the Postgres optimizer must perform as follows:
- Uses a sequential scan to read block by block, loading all data rows in the table into RAM.
- For each row, the CPU must run a mathematical function to assign that row a random number from 0 to 1.
- Postgres must resort those rows based on the random number just calculated and if it cannot fit within RAM, it will have to write temporary data to the disk to run the external merge sort algorithm.
- Finally, it retrieves the top rows and discards the remaining rows.
- Thus, the issue with this solution is having to sequentially scan all data in the table (incurring I/O costs) and sorting the data (incurring CPU costs).
TABLESAMPLE BERNOULLI will also use a sequential scan combined with independent probability for each row to distribute the random probability.
- Because it does not know the total number of rows, it must read the entire data to calculate the corresponding percentage.
- Since every row has a certain selection rate based on the percentage, when using this clause multiple times you will get different row count results and the gathered result will be a dataset evenly distributed from beginning to end.
- Although it utilizes a sequential scan, it skips the sorting step and loading data into RAM to process the LIMIT, so it is still much faster than ORDER BY random() LIMIT.
TABLESAMPLE SYSTEM does not count the number of rows to calculate the percentage, but calculates it based on the total number of blocks or pages of that table on the disk.
- Each data table is stored as a sequence of consecutive data blocks on the disk. Postgres always knows exactly the total number of blocks a table is occupying (this information is continuously updated and stored in the file management system of the operating system and the database).
- Consequently, there will be no need to sequentially scan the entire table or sort the data anymore, making this the method with the fastest response speed.
- When using TABLESAMPLE SYSTEM (1) to sample 1%, the specific operational mechanism is as follows:
  - Postgres will use a random number generation algorithm to select exactly 1% of the pages.
  - After that, it only needs to directly access the chosen blocks to retrieve all the internal rows.
  - After reading these blocks, it will stop and return the result, completely skipping the remaining blocks.
- Therefore, using SYSTEM will sample based on the percentage of the total blocks, unlike BERNOULLI which samples based on the percentage of the total rows and one page contains varying numbers of rows.
  - So each time you use this method, the row count result may also differ.
  - Because the retrieved rows are consecutive within the same page, the obtained results will usually be more clustered and less randomly distributed than the two methods above.

Detail

Please create the tables as follows

CREATE TABLE categories (
    category_id SERIAL PRIMARY KEY,
    category_name VARCHAR(100) NOT NULL
);

CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    category_id INT REFERENCES categories(category_id),
    product_name VARCHAR(255) NOT NULL,
    price NUMERIC(12, 2) NOT NULL,
    rating NUMERIC(3, 2), 
    stock_quantity INT NOT NULL DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE
);

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(50) DEFAULT 'Pending',
    total_amount NUMERIC(12, 2) DEFAULT 0.00
);

CREATE TABLE payment_requests (
    idempotency_key UUID PRIMARY KEY, 
    order_id INT NOT NULL,
    amount NUMERIC(12,2) NOT NULL,
    status VARCHAR(50) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE product_sales_stats (
    product_id INT PRIMARY KEY REFERENCES products(product_id),
    total_sold INT DEFAULT 0,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

The payment_requests table is used to store the list of requests waiting for payment.
The product_sales_stats table is used to store statistical data on the quantity sold.

ORDER BY & FETCH

-- Query 1
SELECT product_id, product_name, rating, price
FROM products
ORDER BY rating DESC;

-- Query 2
SELECT product_id, product_name, rating, price
FROM products
ORDER BY rating DESC NULLS LAST;

-- Query 3
SELECT product_id, product_name, price
FROM products
ORDER BY price DESC
FETCH FIRST 3 ROWS ONLY;

-- Query 4
SELECT product_id, product_name, price
FROM products
ORDER BY price DESC
OFFSET 3 ROWS
FETCH NEXT 5 ROWS ONLY;

-- Query 5
SELECT product_id, product_name, rating
FROM products
WHERE category_id = 1
ORDER BY rating DESC NULLS LAST
FETCH FIRST 1 ROWS WITH TIES;

Query 1: Displays a list of products with ratings from high to low, used with a conventional ORDER BY DESC where rows with NULL values will be sorted at the very top.
Query 2: Similar to Query 1, but used with DESC NULLS LAST where rows with NULL values will be sorted at the very end.
Query 3: Used in pagination, only retrieving the first 3 products, utilizing the FETCH FIRST syntax.
Query 4: Skips the first 3 products, retrieving the next 5 products (OFFSET + FETCH NEXT).
Query 5: Retrieves the top products with the highest rating, if there are multiple products with equal scores at the final position, it retrieves them all without omission, so even when using FIRST 1 ROWS, it can retrieve 2 rows with equal scores.

SKIP LOCKED

BEGIN;
SELECT idempotency_key, order_id 
FROM payment_requests
WHERE status = 'PROCESSING'
ORDER BY created_at ASC
LIMIT 1
FOR UPDATE SKIP LOCKED;

The payment_requests table stores requests waiting for payment, normally workers will be used to process each request to avoid hanging the system.
When a worker wants to process a request, it will start a transaction and use FOR UPDATE SKIP LOCKED to fetch a row with the status PROCESSING to handle.
When the system has multiple workers, they will do the same and automatically skip rows that have already been blocked, even though all have the status PROCESSING.

ON CONFLICT & RETURNING

-- Statement 1
INSERT INTO product_sales_stats (product_id, total_sold, last_updated)
VALUES (1, 5, CURRENT_TIMESTAMP)
ON CONFLICT (product_id) 
DO UPDATE SET 
    total_sold = product_sales_stats.total_sold + EXCLUDED.total_sold,
    last_updated = EXCLUDED.last_updated
RETURNING product_id, total_sold AS new_total_sold;

-- Statement 2
INSERT INTO payment_requests (idempotency_key, order_id, amount, status)
VALUES ('43d35134-7379-4404-a42c-7c31e7b6c15f', 2, 200000, 'PROCESSING')
ON CONFLICT (idempotency_key) DO NOTHING
RETURNING status;

Statement 1: Used for an UPSERT (INSERT+UPDATE) to update the sold quantity of a product.
- If the product is not yet in the product_sales_stats table, it performs an INSERT, if it already exists, it aggregates the quantity.
- If you execute this statement twice, you will see the quantity increase instead of inserting a new row.
- Combined with RETURNING to immediately see the information just inserted without needing an additional SELECT.
Statement 2: In a distributed system, an Idempotency Key is usually utilized uniformly across the system to distinguish between different requests.

Therefore, use ON CONFLICT DO NOTHING to avoid inserting a new row if the idempotency_key is duplicated.
Avoids having to process a single request multiple times.

TABLESAMPLE

-- Query 1
SELECT product_id, product_name, price
FROM products
WHERE is_active = TRUE
ORDER BY random()
LIMIT 3;

-- Query 2
SELECT product_id, product_name, price 
FROM products TABLESAMPLE BERNOULLI(10);

-- Query 3
SELECT product_id, product_name, price 
FROM products TABLESAMPLE SYSTEM(10);

Query 1: This is the conventional way to get random data in a table. It can fetch the exact desired number of rows but is only suitable for small tables because it must sequentially scan the entire table to perform SORT and LIMIT.
Query 2: Randomly samples at a rate of 10% of the rows across the entire table, still using a sequential scan but without needing SORT and LIMIT so the performance is better, the number of rows fetched each execution will not be fixed.
Query 3: Randomly samples at a rate of 10% of the blocks or pages of that table, offering the best performance because it does not require a sequential scan or sorting, but the number of rows fetched is not fixed and will usually be higher than the two cases above.