10 posts tagged with "Database"

Indexes speed up reads by charging you on every write, and the charge is larger than most people picture. Measured on PostgreSQL 16 with the same 200,000 rows: a table with no indexes finished inserting in 287.8 ms and occupied 10.2 MB; a table with four indexes took 1,722.3 ms and occupied 27 MB. That is 6× slower and 2.6× bigger — just to carry four structures that no query may ever touch. The right question is not "should this column have an index?" but "does the read time saved cover the write time paid?"

GROUP BY collapses rows; a window function keeps every row while still computing the group total. Measured: on the same 200,000-row table, GROUP BY returns 200 rows and the window returns 200,000 rows. It is also faster than the old approach — replacing a self-join with a window took the query from 79.2 ms down to 22.7 ms. But there is one default that quietly corrupts numbers and almost nobody reads it: OVER (ORDER BY ...) uses a RANGE frame, so every peer row receives the same running total. If you want a row-by-row cumulative sum, you must spell out ROWS.

Isolation level quyết định một giao dịch nhìn thấy gì khi có giao dịch khác chạy song song. Ở mức mặc định của hầu hết database là READ COMMITTED, hai phiên cùng đọc số dư 1000 rồi cùng ghi 1100 sẽ cho kết quả cuối là 1100 — một lần cộng biến mất, không có lỗi nào được ném. Nâng lên REPEATABLE READ thì phiên thứ hai nhận ERROR: could not serialize access due to concurrent update: câu trả lời sai âm thầm biến thành một lỗi rõ ràng mà ứng dụng phải thử lại. Toàn bộ số liệu dưới đây đo bằng hai session psql song song trên PostgreSQL 16.11.

An index is a structure sorted by the column's values. Wrap a function around the column — date_part('year', created_at), LOWER(email), CAST(...) — and the thing you are comparing is no longer the sorted value, so the database cannot use the index and falls back to scanning the whole table. A condition that can use an index is called sargable. Measured on PostgreSQL 16 with 200,000 rows: the function-wrapped version runs in 29.6 ms with a Seq Scan, the range-rewritten version runs in 5.4 ms with a Bitmap Index Scan — about 5.4× faster, returning the same 37,518 rows.

LEFT JOIN keeps every row from the left table and fills in NULL where nothing matches. But WHERE runs after the join, so any condition placed on a right-hand column also removes those NULL rows — and the LEFT JOIN becomes an INNER JOIN with no warning whatsoever. Measured on PostgreSQL 16: a plain LEFT JOIN gives 4 rows, adding WHERE leaves 2 rows, but moving that same condition into ON gives 3 rows — which is the correct answer.