How To Dev

Introduction In PostgreSQL, Tuple comparison (row/tuple comparison) and Multi-column IN (IN condition on multiple columns) are two extremely powerful features that help write concise, clearer SQL queries and significantly optimize performance compared to manually chaining multiple AND/OR conditions. Tuple comparison Tuple comparison allows grouping multiple columns or values into a tuple (using parentheses (...)) and comparing these two tuples directly with each other using operators like =, <>, <, >, <=, >= Used for Keyset Pagination with more than 1 column and sorting applied Lexicographical order PostgreSQL compares elements from left to right, similar to dictionary sorting, starting by comparing the first pair of elements. If they differ, the result of the entire comparison is decided immediately without evaluating subsequent columns If they are equal, it proceeds to compare the next pair of elements, continuing this process until the end. General Example (a, ...

Introduction How to handle data changes Postgres uses the MVCC mechanism, meaning when data changes occur, Postgres still retains old data versions and INSERT s new data DELETE : when deleting rows, Postgres only adds a flag to that row called Dead Tuple indicating that this row is no longer used This means that row still exists on disk and is not completely deleted, so it will need cleaning up When querying, it will be automatically ignored and will not return results INSERT : when adding a row Postgres will search for empty space in existing pages to INSERT new rows Or create a new page, if empty pages are exhausted Or override data of Dead Tuple rows according to the Free Space Map UPDATE : when an UPDATE is needed, it will DELETE the old row and INSERT a row with new data TRUNCATE is an operation belonging to the DDL (Data Definition Language) group, not DML like DELETE . Instead of marking Dead Tuple for each row, Postgres will completely delete the table's data fi...