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

In my tool[postgres_dba](https://github.com/NikolayS/postgres_dba/), the setup instructions have this:

printf"%s %s %s %s\n"\\set dba\'\\\\i$(pwd)/start.psql\'>>~/.psqlrc

This provides a way to call`start.psql` by just typing`:dba` in psql, and this line works in both bash and zsh (which

is good because macOS switched to zsh as the default shell a few years ago, while bash is usually the default shell on

Linux distros).

One can easily add their own instructions to`.psqlrc`, defining various handy shortcuts. For example:

\set pid'select pg_backend_pid() as pid;'

\set prim'select not pg_is_in_recovery() as is_primary;'

\set a'select state, count(*) from pg_stat_activity where pid <> pg_backend_pid() group by 1 order by 1;'

👉 This adds simple shortcuts:

1) Current PID:

nik=# :pid

pid

(1 row)

```

2) Is this not a primary?

3) Simple activity summary

The value of a currentlyset psql variable can be passedin an SQL contextas a string using this interesting syntax:

It is important not to forget to use the option`-X` (` --no-psqlrc`)in scripting, so nothing that you putin`.psqlrc`

would affect the logic of your scripts.

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

Adding`CHECK` constraints can be helpful to:

- maintain better data quality

- define a`NOT NULL` constraint without downtime in PG12+

(more[here](0060_how_to_add_a_column.md?ref_type=heads#not-null))

To add a`CHECK` constraint without downtime, we need:

1. Quickly define a constraint with the flag`NOT VALID`

2. In a separate transaction, "validate" the constraint for existing rows.

Example:

altertable t

addconstraint c_id_is_positive

check (id>0) not valid;

This requires a very brief`AccessExclusiveLock` lock, so on loaded systems, the command has to be executed with low

`lock_timeout` and retries (read:

[Zero-downtime Postgres schema migrations need this: lock_timeout and retries](https://postgres.ai/blog/20210923-zero-downtime-postgres-schema-migrations-lock-timeout-and-retries)).

🖋️**Important:** Once the constraint with`NOT VALID` is in place, the new writes are checked (while old rows have not

been yet verified and some of them might violate the constraint):

nik=# insert into t select -1;

ERROR: new row for relation"t" violatescheckconstraint"c_id_is_positive"

DETAIL: Failing row contains (-1).

To complete the process, we need to validate the old rows:

altertable t

validateconstraint c_id_is_positive;

This scans whole table, so for a large table, it takes long time – but this query only

acquires`ShareUpdateExclusiveLock` on the table, not blocking the sessions that run DML queries. However, a lock

acquisition attempt is going to be blocked if there is`autovacuum` running in the transaction ID wraparound prevention

mode and processing the table, or if there is another session that builds an index on this table or performs

another`ALTER` – so we need to make sure none of these heavier operations are happening before we run our`ALTER`, to

avoid excessive wait time.

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

Adding a foreign key (FK) is straightforward:

altertable messages

addconstraint fk_messages_users

foreign key (user_id)

references users(id);

However, this operation requires locks on both tables involved:

1.`ShareRowExclusiveLock`,`RowShareLock`, and`AccessShareLock` on the referenced table, in this example it's

`users` (plus`AccessShareLock` on its primary key, PK). This blocks any data modifications to`users` (`UPDATE`,

`DELETE`,`INSERT`), as well as DDL.

2.`ShareRowExclusiveLock` and`AccessShareLock` to the referencing table, in this example`messages`

(plus,`AccessShareLock` to its PK). Again, this blocks writes to this table, and DDL.

And to ensure that the existing data doesn't violate the constraint, full table scans are needed – so the more data the

tables have, the longer this implicit scan is going to take. During which, the locks are going to block all writes and

DDL to the table.

To avoid downtime, we need to create the FK in three steps:

1. Quickly define a constraint with the flag`NOT VALID`.

2. For the existing data, if needed, fix rows that would break the FK.

3. In a separate transaction,`validate` the constraint for existing rows.

Example:

altertable messages

addconstraint fk_messages_users

foreign key (user_id)

references users(id)

not valid;

This requires a very brief`ShareRowExclusiveLock` and`AccessShareLock` on both tables, so on loaded systems, it is

still recommended to execute this with low`lock_timeout` and retries (read:

[Zero-downtime database schema migrations](https://postgres.ai/blog/20210923-zero-downtime-postgres-schema-migrations-lock-timeout-and-retries)),

to avoid lock queue blocking writes to the tables.

🖋️**Important:** once the constraint with`NOT VALID` is in place, new writes are checked (while old rows have not

been yet verified and some of them might violate the constraint):

nik=# \d messages

Table"public.messages"

Column | Type | Collation | Nullable | Default

id |bigint | |not null |

user_id |bigint | | |

Indexes:

"messages_pkey"PRIMARY KEY, btree (id)

Foreign-key constraints:

"fk_messages_users"FOREIGN KEY (user_id)REFERENCES users(id) NOT VALID

nik=# insert into messages(id, user_id) select 1, -1;

ERROR: insertorupdateon table`messages` violatesforeign keyconstraint"fk_messages_users"

DETAIL: Key (user_id)=(-1) is not presentin table`users`.

Now, with the FK created with`NOT VALID`, we know that Postgres already checks all the new data against the new

constraint, but for the old data, some rows might still be violating it. Before the next step, it makes sense to ensure

there are no old rows violating our new FK. It can be done using this query:

select id

from messages

user_id notin (

select idfrom users

);

This query scans the whole`messages` table, so it will take significant time. It is worth ensuring that`users` is

accessed via PK here (depends on the data volumes and planner settings).

The rows identified by this query will block the next step, so they need to either be deleted or changed to avoid the FK

violation.

To complete the process, we need to`validate` the old rows in a separate transaction:

altertable messages

validateconstraint fk_messages_users;

If the tables are large, this`ALTER` is going to take significant time. However, it only acquires

`ShareUpdateExclusiveLock` and`AccessShareLock` on the referencing table (`messages` in this example).

Therefore, it doesn't block`UPDATE` /`DELETE` /`INSERT`, but it conflicts with DDL and`VACUUM` runs. On the

referenced table (`users` here),`AccessShareLock` and`RowShareLock` are acquired.

As usual, if`autovacuum` processes this table in the transaction ID wraparound prevention mode, it won't yield – so

before running this, make sure there is no`autovacuum` running in this mode or DDL in progress.

- 0065[UUID v7 and partitioning (TimescaleDB)](./0065_uuid_v7_and_partitioning_timescaledb.md)

- 0066[How many tuples can be inserted in a page](./0066_how_many_tuples_can_be_inserted_in_a_page.md)

- 0067[Autovacuum "queue" and progress](./0067_autovacuum_queue_and_progress.md)

- 0068[psql shortcuts](./0068_psql_shortcuts.md)

- 0069[How to add a CHECK constraint without downtime](./0069_howd_tod_addd_ad_checkd_constraintd_withoutd_downtime.md)

- ...