dropindex concurrently i_t1_id_new;

-`pg_attrdef` stores all current defaults. When we change`DEFAULT` for an existing column, this catalog is updated to

store the new value:

nik=# alter table t1 alter column c2 set default -30;

nik=# select pg_get_expr(adbin, 't1'::regclass::oid) from pg_attrdef;

pg_get_expr

'-30'::integer

(1 row)

And the value stored in`pg_attribute` in`attmissingval` is that one that is used for the rows that existed before

column was created:

nik=# select attmissingval from pg_attribute where attrelid = 't1'::regclass::oid and attname = 'c2';

attmissingval

{-10}

(1 row)

Originally from:[tweet](https://twitter.com/samokhvalov/status/1729321940496924977),[LinkedIn post]().

Two key principles of helping others (it can be internal help to your teammates, or external consulting, doesn't

matter):

1. Rely on good sources.

2. Test everything.

Rely on high-quality sources that you trust. For example, my point of view, ranking the quality/trustworthiness level,

roughly:

- PG docs – 9/10

- PG source code – 10/10 (source of truth!)

- StackOverflow random answers – 5/10 or lower (excluding

[Erwin Brandstetter](https://stackoverflow.com/users/939860/erwin-brandstetter),[Laurenz Albe](https://stackoverflow.com/users/6464308/laurenz-albe),[Peter Eisentraut](https://stackoverflow.com/users/98530/peter-eisentraut) –

these

guys rock when they answer there, 8/10 or higher)

- the same for random blog posts

- both "Internals" books by[Suzuki](https://www.interdb.jp/pg/)

and[Rogov](https://postgrespro.com/blog/pgsql/5969637) – 8/10 or higher

- etc.

And (important!) always provide the link to your sources; you get two benefits from this:

- advertise good source and pay them back;

- share the responsibility to some extent (very helpful if you are not very experienced yet; everyone might make a

mistake).

Always doubt everything, don't trust language models, regardless of their nature.

If someone (including you or me) says something without verification via an experiment (test), it needs to be fixed –

using an experiment.

All decisions should be made based on data – the reliable data is gathered via testing.

Most database-related ideas are to be verified using database experiments.

Two types of database experiments:

1. Multi-session experiments – full-fledged benchmarks like those conducted using`pgbench`, JMeter,`sysbench`,

`pgreplay-go`, etc.

They aim to study the behavior of Postgres as a whole, all its components, and must be performed on dedicated

resources where nobody else is doing any work. Environment should match production (VM and disk type, PG version,

settings).

Examples of such experiments include load testing, stress testing, performance regression testing. Main tools are

those that aim to study macro-level query analysis:`pgss`, wait event analysis (aka active session history or

performance/query insights),`auto_explain`,`pgBadger`, etc.

More about this type of experiments:[Day 13: How to benchmark](0013_how_to_benchmark.md).

2. Single-session experiments – testing one or a sequence of SQL queries using a single session (sometimes, two), to

check query syntax, study individual query behavior, optimize particular query, etc.

These experiments can be conducted in shared environments, on weaker machines. However, to study query performance,

you need to have the same PG version, same or similar database, and matching planner settings (how to do it:

[Day 56: How to make the non-production Postgres planner behave like in production](0056_how_to_imitate_production_planner.md)).

In this case, it should be kept in mind that

timing metrics might be significantly off compared to production, and the main attention in the query optimization

process should be paid to execution plans and data volumes (actual rows, buffer operation counts provided by the

option`BUFFERS` in`EXPLAIN`).

Examples of such experiments: checking syntax and logical behavior of a sequence of SQL queries, query performance

analysis`EXPLAIN (ANALYZE, BUFFERS)` and testing optimization ideas, schema change testing, etc. It is very helpful

to be able to quickly clone large databases not paying extra money for storage (Neon, Amazon Aurora). And it's even

better if no extra money is paid for both storage and compute, this truly unlocks testing activities including

automated tests in CI/CD (DBLab Engine[@Database_Lab](https://twitter.com/Database_Lab)).

As you can see, the principles are extremely simple:

- read good papers, and

- don't blindly trust – test everything.

Originally from:[tweet](https://twitter.com/samokhvalov/status/1729900720189759801),[LinkedIn post]().

As of now (PG16, 2023), Postgres implements UUID versions from 1 to 5, based

on[RFC 4122](https://datatracker.ietf.org/doc/html/rfc4122).

- Docs:[UUID Data Type](https://postgresql.org/docs/current/datatype-uuid.html)

- Additional module[uuid-ossp](https://postgresql.org/docs/current/uuid-ossp.html)

A UUID value can be generated using`get_random_uuid()`, it generates UUID version 4

([source code for PG16](https://github.com/postgres/postgres/blob/03749325d16c4215ecd6d6a6fe117d93931d84aa/src/backend/utils/adt/uuid.c#L405-L423)):

nik=# select gen_random_uuid();

gen_random_uuid

c027497b-c510-413b-9092-8e6c99cf9596

(1 row)

nik=# select gen_random_uuid();

gen_random_uuid

08e63fed-f883-45d8-9896-8f087074bff5

(1 row)

In standard UUIDs, the version can be understood looking at the first character after the 2nd hyphen:

<pre>

08e63fed-f883-<ustyle="color:darkmagenta;font-weight:bolder">4</u> ... 👈 this means v4

</pre>

The values are coming in a "pseudorandom" order. This has certain negative impact on performance: in a B-tree index,

inserts happen in various locations, which, in general, affects write performance, as well as performance of Top-N

reads (selecting N latest rows).

There is a proposal to implement newer versions of UUID both in RFC and Postgres – v7 provides a time-based UUID that

includes a millisecond-precision timestamp, sequence number, and additional entropy in the form of random or fixed bits.

This kind of UUID not only ensures global uniqueness but also preserves the temporal aspect, which can be very

beneficial for performance.

-[Commitfest: UUID v7](https://commitfest.postgresql.org/45/4388/)

-[rfc4122bis proposal](https://datatracker.ietf.org/doc/draft-ietf-uuidrev-rfc4122bis/)

UUID values are 16-byte – the same as`timestamptz` or`timestamp` values.

Good materials explaining performance aspects:

-[The effect of Random UUID on database performance](https://twitter.com/hnasr/status/1695270411481796868) (video,~19

min) by[@hnasr](https://twitter.com/hnasr)

-[Identity Crisis: Sequence v. UUID as Primary Key](https://brandur.org/nanoglyphs/026-ids#ulids) by

Since Postgres doesn't support UUID v7 natively yet, there are two options to use them

- generate on client side

- implement a helper function in Postgres.

For the latter approach, here is[SQL function](https://gist.github.com/kjmph/5bd772b2c2df145aa645b837da7eca74)

(thanks[@DanielVerite](https://twitter.com/DanielVerite)):

create or replacefunctionuuid_generate_v7() returns uuid

as $$

select encode(

set_bit(

set_bit(

overlay(

uuid_send(gen_random_uuid())

placingsubstring(int8send(floor(extract(epochfrom clock_timestamp())*1000)::bigint)from3)

from1 for6

),

52,1

),

53,1

),

'hex')::uuid;

$$ language SQL volatile;

Examples:

nik=# select uuid_generate_v7();

uuid_generate_v7

018c1be3-e485-7252-b80f-76a71843466a

(1 row)

nik=# select uuid_generate_v7();

uuid_generate_v7

018c1be3-e767-76b9-93dc-23c0c48be6c7

(1 row)

nik=# select uuid_generate_v7();

uuid_generate_v7

018c1be3-e973-7704-82ad-5967b79cf5c4

(1 row)

After a few minutes:

nik=# select uuid_generate_v7();

uuid_generate_v7

018c1be8-5002-70ab-96c0-c96ad5afa151

(1 row)

A few notes:

1) If you use these value in the`ORDER BY` clause, the chronological order will persist.

2) For the first 3 values (that we generated during a few seconds) there is a common prefix,`018c1be3-e`, and with the

last value that was generated slightly later, there is common prefix`018c1be`.

3) Note`7` after the second hyphen in all values:

<pre>

018c1be3-e973-<ustyle="color:darkmagenta;font-weight:bolder">7</u>... 👈 this means v7

</pre>

4) The function returns a value of the UUID type, so it's still 16-byte (while text representation of it would take 36

characters including hyphens, meaning 40 bytes total with`VARLENA` header):

nik=# select pg_column_size(gen_random_uuid());

pg_column_size

(1 row)

nik=# select pg_column_size(uuid_generate_v7());

pg_column_size

(1 row)

- 0060[How to add a column](./0060_how_to_add_a_column.md)

- 0061[How to create an index, part 1](./0061_how_to_create_an_index_part_1.md)

- 0062[How to create an index, part 2](./0062_how_to_create_an_index_part_2.md)

- 0063[How to help others](./0063_how_to_help_others.md)

- 0064[How to use UUID](./0064_how_to_use_uuid.md)

- ...