Database

From my previous post, I realized that pattern matching operators (LIKE, ILIKE) do not utilize indexes. As I explored further, I came across the concept of collation and decided to take some notes in this post. Encoding Encoding maps human-readable characters to numbers so computers can understand them. Essentially, it assigns a unique number to each character. Common encodings include UTF-8 and ASCII. ASCII: Represents 256 unique characters. UTF-8: Represents 1,112,064 characters, covering almost all characters from any language. Most modern programming languages, such as Go, natively support UTF-8. Unlike ASCII, which uses 1 byte per character, UTF-8 uses up to 4 bytes. Strings in programming languages are typically represented as byte arrays. In ASCII, the number of bytes corresponds to the number of characters. However, this is not true for UTF-8. ...

Explain Explaining the query will help you to estimate how expensive your query is and which query plan will be used. 1 2 3 4 5 6 khanh=# explain select g from grades where g = 100; QUERY PLAN ------------------------------------------------------------------------------- Index Only Scan using g_index on grades (cost=0.42..97.55 rows=4636 width=4) Index Cond: (g = 100) (2 rows) So, this query will use Index-Only-Scan. The cost of the query is ranging from 0.42 to 97.55, 4636 is number of estimated return rows, width is size of return data in byte. ...

Check timezone of the current session 1 2 3 4 5 khanh=# show timezone; TimeZone ---------- UTC (1 row) Change timezone of a session 1 2 3 4 5 6 7 khanh=# set timezone='asia/ho_chi_minh'; SET khanh=# show timezone; TimeZone ------------------ Asia/Ho_Chi_Minh (1 row) you can get timezone names here: https://en.wikipedia.org/wiki/List_of_tz_database_time_zones timestamp and timestamptz timestamptz aka timestamp with time zone, the time when return to the client will be converted to the timezone has picked in the session. ...

B tree and B+ tree B tree is a self-balance tree. I can see that the B tree and AVL tree have a thing in common – it is all self-balance. But the difference is, each node in the AVL tree store exactly one value, and have at most two children. Each node of the B tree contains an array of at most N values and has at most N + 1 children. ...

1. Read phenomena When transaction A reads the data that might be changed by transaction B. 1.1 Dirty reads: Is when a transaction read uncommitted data from another transaction. Example: Transaction A Transaction B begin transaction; select age from employee where id = 1; // age = 24 begin transaction; update employee set age = 25 where id = 1; // age = 25 select age from employee where id = 1; // age = 25 rollback; select age from employee where id = 1; // age = 24 commit; 1.2 Non-repeatable reads When during a transaction, you retrieve a row two times, and the second time, you got a slightly different row. It is different from the dirty read in that this time, it read committed data. ...

Atomicity: Each transaction could have multiple steps in it, each step might be a query or an update to the data. Atomicity means that the transaction is considered a success if all steps are a success. If just one of those steps is failed, the whole transaction is considered to fail, and it has to roll back to the previous state. Consistency: In the database, it might have some rules or constraints, and the transaction when modified data have to follow those constraints. For example, let say the constrain for bank account balance is 0 or positive, so if a transaction tries to assign a negative number to a bank account, it violates the constrain and has to roll back. ...