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

 

 

 

 

 

Can the following queries use the bday index?

1. SELECT * FROM users WHERE birthday < '1989-02-14';

2. SELECT * FROM users WHERE birthday > '1989-02-14';

3. SELECT * FROM users WHERE birthday BETWEEN '1989-01-01' AND '1989-12-31';

 

The short answer is: Yes, absolutely. All three of these queries are "indexable" because of how B-Tree indexes are structured.

A B-Tree (Balanced Tree) stores data in a sorted, hierarchical structure. Think of it like a physical phone book or a sorted library catalog. Because the values are sorted, Postgres doesn't have to look at every row; it can jump straight to a specific point and read the values in order from there.

---

1. WHERE birthday < '1989-02-14'

• Can it use the index? Yes.

• How: This is a Range Scan. Postgres finds the entry for Feb 14, 1989, and then follows the "leaf" pointers in the index to the left (backwards) to collect every date that comes before it.

2. WHERE birthday > '1989-02-14'

• Can it use the index? Yes.

• How: Similar to the first query, Postgres finds the starting point and then follows the index pointers to the right (forwards) to collect all later dates.

3. WHERE birthday BETWEEN '1989-01-01' AND '1989-12-31'

• Can it use the index? Yes.

• How: BETWEEN is just shorthand for birthday >= '1989-01-01' AND birthday <= '1989-12-31'. The B-Tree finds the start of the range, finds the end, and grabs everything in the middle.

---

The "But" (Why Postgres might ignore your index)

Even though the index can be used, the Postgres Query Planner might choose not to use it if:

1. High Selectivity: If your query returns a massive percentage of the table (e.g., 90% of your users were born before 1989), Postgres might decide it’s faster to just do a Sequential Scan (read the whole table) rather than jumping back and forth between the index and the actual data storage.

2. Small Table: If the table only has a few hundred rows, reading the index is actually more "work" than just reading the table once.

 

 

 

 

 

 

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: