How To Dev

Introduction These are Query Tuning techniques based on common errors encountered when querying data and the process you can apply: Avoid Using SELECT * When you use queries like this SELECT * FROM users WHERE id = 100 SELECT username FROM users WHERE id = 100 If there is no Index , Postgres will have to use Heap Scan to load the corresponding entire data page into RAM At this point, whether you use SELECT * or SELECT name , Postgres handles it the same way But the difference starts here, if you only SELECT the necessary columns, Postgres only needs to process those columns without spending additional CPU cost to process all columns as with SELECT * If any of the requested columns are too large and stored in the TOAST Table , it incurs additional I/O and data decompression costs At the same time, if you deploy a Web Server to respond with this data to FE , larger amounts of data will consume more network bandwidth, RAM to parse JSON and take longer to process Solu...

Introduction When initializing a B-Tree Index , it also allows the option to choose the data sorting direction, including ASC (default) and DESC This feature is uniquely supported for B-Tree Index only, you cannot use it for other Index types ( GIN, GiST, BRIN,... ) The reason is due to the tree structure ordering keys, which can be read forward or backward to retrieve sorted results Other Index types do not support storing sorted data, they are used to serve specific data types and problems, sorting data order ORDER BY is not in their core design. NULL value In Postgres, NULL is understood as an Unknown value and is always considered greater than any normal value. Therefore, by default: When you create an ASC Index (Ascending): NULL values are at the END ( NULLS LAST ). When you create a DESC Index (Descending): Since NULL is the largest, when sorting from high to low, NULL will be at the BEGINNING ( NULLS FIRST ). Use case Includes forms such as CREATE INDEX idx_column_desc...