PostgreSQL - Study Notes 3

 

     ctid

 

 

 

 

What data type should you use for your primary keys? Integers or UUIDs?

In 98% of use cases, you should favor integer types. When we are over integer types, said, my opinion is you should use big integers.

There are two drawbacks to use UUID as a primary key. One is the size. The other is random insertion, which will cause the btree index to fracture and rebalance. For the second drawback, you can use UUID V7 which has a timestamp at the beginning to avoid it. One benifit of UUIDs is you can generate these IDs without coordination of the database.

Use a public ID alongside the bigint identity primary key to avoid increment attacking. You can use a Nano ID generator for this public ID.

 

select pg_column_size(1::bigint) bigint_size, pg_column_size(gen_random_uuid()::uuid) uuid_size;
 bigint_size | uuid_size 
-------------+-----------
           8 |        16

 

 

 

 

Index helps on the first unbounded range, but not on the second unbounded range. That is because of the index selectivity. See https://www.cnblogs.com/zhangzhihui/articles/18590043.

 

 

 

 

 

 

To use the composite index, you have to start with the leftmost column.

 

 

 

 

Left to right, no skipping, stops at the first range.

 

Combining multiple indexes:

 

 

 

 

 

 

Covering index:

 

 

Note the 'Only'. 

 

 

Note the 'Only'.

 

Partial index:

 

 

 

 

 

Index ordering:

 

 

 

 

 

 

 

 

 

 

 

By default, NULLs are treated as larger than any other values. But we can change that both in query and index construction.

Ordering nulls:

          

 

 

 

 

 

 

Functional indexes:

 

 

 

 

 

Duplicate indexes:

 

 

 

Hash indexes:

A hash index is only useful for strict equality lookups.

 

 

 

 

Explain structure: read the query plan inside out.

 

 

 

 

 

 

 

Explain analyze: