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

Right now on HN frontpage there is an article by knock.app

on[zero downtime Postgres upgrades](https://news.ycombinator.com/item?id=38616181).

Later this week,[Alexander Sosna](https://twitter.com/xxorde) (GitLab) is

presenting[a talk](https://postgresql.eu/events/pgconfeu2023/schedule/session/4791-how-we-execute-postgresql-major-upgrades-at-gitlab-with-zero-downtime/)

explaining how GitLab's large clusters were upgraded under heavy load without any downtime – I highly recommend looking

that that work

There are many details behind the proper zero downtime upgrade, many challenges to solve, and here I present only a

high-level plan. It works well for very large (dozens of TiB) clusters, with many replicas, and working under high TPS

(dozens of 10k). The process described here involves logical replication (with the "physical-to-logical" trick) and

PgBouncer's PAUSE/RESUME (assuming that PgBouncer is used).

The detailed material will require several separate howtos to be written.

The process consists of 2 steps:

1)**UPGRADE:** New cluster is created, running on new Postgres major version.

2)**SWITCHOVER:** step by step switchover of the traffic.

There is a "standard" approach when a new cluster is first created from scratch (`initdb`) and then a brand new logical

replica is created based on it (`copy_data = false` when creating logical subscription). However, this way of logical

replica provisioning takes a lot of time can be very problematic to execute under heavy load.

An alternative is to take a physical replica and convert it to logical. This is relatively easy to do knowing the

logical slot's LSN position and using`recovery_target_lsn`. This recipe is called "physical2logical" conversion, and it

allows for the creation of a logical replica based on physical replica (e.g. created based on a cloud snapshot in

minutes), reliably and quickly.

However, combining the physical2logical conversion with`pg_upgrade` should be done with care.

Details can be found[here](https://postgresql.org/message-id/flat/20230217075433.u5mjly4d5cr4hcfe%40jrouhaud).

Steps:

1. Create a new cluster, which will be a secondary cluster using cascaded physical replication (Patroni supports it).

2. Ensure that the new cluster's leader is not significantly lagging behind the old cluster's primary.

3. Stop new cluster's node in proper order (making sure stopped replicas are fully in sync with their leader).

4. On the old cluster's primary, create a logical slot and publication`FOR ALL TABLES` (this doesn't require

table-level locks, thus we don't need low`lock_timeout` and retries) and remember the slot's position.

5. Reconfigure new cluster's leader, setting`recovery_target_lsn` to the remembered slot's LSN, and disabling

`restore_command`.

6. Start new cluster's leader and replicas, let them reach the desired LSN. Then the leader can be promoted.

7. Stop new cluster's nodes again, in proper order.

8. Run`pg_upgrade --link` on the new cluster's leader.

9. Use`rsync --hard-links --size-only` on the new cluster's replicas – this is disputable step (see

details[here](https://postgresql.org/message-id/flat/CAM527d8heqkjG5VrvjU3Xjsqxg41ufUyabD9QZccdAxnpbRH-Q%40mail.gmail.com)),

but this is what most people use for in-place (w/o logical replication) upgrades with`pg_upgrade --link`, and there

is no another fast alternative invented yet.

10. Configure new cluster's leader (now primary already) to use logical replication – create subscription,

with`copy_data = false` and let it catch up with the working old cluster.

During all these steps the old cluster is up and running, and new cluster is invisible to users. This gives you a huge

benefit of testing the whole process right in production (after proper testing in lower environments).

First, it makes sense to switch over the read-only (RO) traffic. If the application allows, it makes sense to first

redirect only part of the RO traffic to new replicas. This would require an advanced replication lag detection in the

load balancing code

(see:

[Day 17: How to determine the replication lag - Hybrid case: logical & physical](./0017_how_to_determine_the_replication_lag.md#hybrid-case-logical-physical)).

When it is time to redirect the RW traffic, to achieve zero downtime, one can use PgBouncer's PAUSE/RESUME. If there are

multiple PgBouncer nodes (running on separate hosts/ports, or involving`SO_REUSEPORT`), it is important to implement a

graceful approach for PAUSE acquisition. Once all PAUSEs are acquired, the traffic can be redirected, and it is time to

issue RESUME. Before that it is important to make sure that all writes are fully propagated to the new primary.

It is important to have measures developed to protect the old cluster from writes coming from application or users –

e.g. adjusting`pg_hba.conf` or shutting the cluster down. However, for advanced rollback capabilities, it makes sense to

implement "reverse" logical replication, in the same manner as "forward" one, setting it up during switchover. In this

case, the writes to the old cluster are allowed – but only from logical replication. The reverse replication allows for

rollback even some time after the whole process is finished, this makes the whole process fully reversible from any

point.

As already mentioned, there are many aspects to address, this is only a high-level plan. If you can attend this talk, do

it[here](https://postgresql.eu/events/pgconfeu2023/schedule/session/4791-how-we-execute-postgresql-major-upgrades-at-gitlab-with-zero-downtime/).

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

Assume we have a 10 TiB table created many years ago, partitioned or not, like this one:

createtablet (

id int8primary key,-- of course, not int4

created_attimestamptz default now(),

...

);

and we need to quickly understand how the table grew year over year, assuming that no rows were deleted (or only a

negligible amount). So, we just need to count rows for every year.

A straightforward approach would be:

date_trunc('year', created_at)as year,

count(*)

from t

group by year

order by year;

However, for a 10 TiB table, we'd need to wait many hours, if not days, for this analysis to complete.

Here is fast, but not a precise way to get the row counts for each year (assuming the table has up-to-date stats; if in

doubt, run`ANALYZE` on it first):

do $$

declare

table_fqntext :='public.t';

year_startint :=2000;

year_endint := extract(yearfrom now())::int;

yearint;

explain_json json;

for yearin year_start..year_end loop

execute format(

$e$

explain (format json)select*

from %s

where created_at

between'%s-01-01'and'%s-12-31'

$e$,

table_fqn,

year,

year

) into explain_json;

raise info'Year: %, Estimated rows: %',

year,

explain_json->0->'Plan'->>'Plan Rows';

end loop;

end $$;

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

Sometimes we need to reindex many indexes – or all of them – and want to do it faster.

For example, this makes sense after upgrading from pre-14 Postgres to version 14+, when we want to rebuild all B-tree

indexes to benefit from optimizations that reduce bloat growth rates.

We might decide to use a single session and higher value of

[max_parallel_maintenance_workers](https://postgresqlco.nf/doc/en/param/max_parallel_maintenance_workers/), processing

one index at a time. But if we have powerful resources (a lot of vCPUs and fast disks), then the maximal value of

`max_parallel_maintenance_workers` may not be enough to move as fast as we can (changing

`max_parallel_maintenance_workers` doesn't require a restart, but we cannot use more than`max_worker_processes`

workers, and changing that requires a restart). In this case, it may make sense to process multiple indexes in parallel,

using`REINDEX INDEX CONCURRENTLY`.

But in this case, indexes need to be processed in proper order. The problem is that if you attempt to rebuild two

indexes belonging to the same table in parallel, a deadlock will be detected, and one of the sessions will fail:

nik=# reindex index concurrently t1_hash_record_idx3;

ERROR: deadlock detected

DETAIL: Process40 waits for ShareLockon virtual transaction4/2506; blocked by process1313.

Process1313 waits for ShareUpdateExclusiveLockon relation16634 of database16401; blocked by process40.

HINT: See server log for query details.

To address this, we can use this approach:

1. Decide how many reindexing sessions you want to use, taking into account`max_parallel_maintenance_workers` and the

planned resource utilization / saturation risks (CPU and disk IO).

2. Assuming we want to use N reindexing sessions, build the full list of indexes, with the table names they belong to,

and "assign" each table to a particular reindexing session. See the query below that does it.

3. Using this "assignment", divide the whole list of indexes to N separate lists, so all the indexes for a particular

table are present only in a single list – and now we can just run N sessions using these N lists.

For the step 2, here is a query that can help:

\set NUMBER_OF_SESSIONS10

format('%I.%I',n.nspname,c.relname)as table_fqn,

format('%I.%I',n.nspname,i.relname)as index_fqn,

mod(

hashtext(format('%I.%I',n.nspname,c.relname)) &2147483647,

:NUMBER_OF_SESSIONS

)as session_id

pg_index idx

join pg_class conidx.indrelid=c.oid

join pg_class ionidx.indexrelid=i.oid

join pg_namespace nonc.relnamespace=n.oid

n.nspname notin ('pg_catalog','pg_toast','information_schema')

table_fqn, index_fqn;

- 0074[How to flush caches (OS page cache and Postgres buffer pool)](./0074_how_to_flush_caches.md)

- 0075[How to find redundant indexes](./0076_how_to_find_redundent_indexes)

- 0076[How to find unused indexes](./0076_how_to_find_redundent_indexes.md)

- 0077[Postgres major upgrade without any downtime for a very large cluster running under heavy load](./0077_zero_downtime_major_upgrade.md)

- 0078[How to estimate the YoY growth of a very large table using row creation timestamps and the planner statistics](./0078_estimate_yoy_table_growth.md)

- 0079[How to rebuild many indexes using many backends avoiding deadlocks](./0079_rebuild_indexes_without_deadlocks.md)

- ...