This depends on your application. Choose Postgres Frequent Write operation why? Postgres has secondary index architecture. So re-balancing operation cost of insert & delete is cheaper than Clu...
B-Tree and B+Tree
BTree This is the common data structure when DB store index. Each node has key - value Each node is a page N = M - 1 N: the number of element in a node M: the number of children nodes L...
When you meet huge table
When you work with huge table [CASE I] Brute force Brute forcing your way to work on the table. If you tried to find a row in the table without indexing which is do multi-processing, multi-thread...
Indexing with query planner
Configuration Tested on DBMS: PostgreSQL 14.4 Table DDL create table users ( id integer generated always as identity constraint user_id_pk primary key, name varchar(1...
Bitmap Scan
Configuration DBMS : PostgreSQL 14.4 Table Schema create table users ( id integer generated always as identity constraint user_id_pk primary key, name varchar(1...
Clustered index vs non-clustered index
Primary key Organized, clustered table. we call it Index Organized Table (IOT) Clustered index that is sequential is not that bad. So, it’s ordered data and can use efficient caching mechanism. I...
Required terms of database
Pages The logical unit, that of database stores and reads the data. Different according to DBMS (ex. PostgreSQL has a 8KB page, MySQL does 16KB) DB handle data one or more pages in a single IO. ...