Melkij

Очевидно psql на скрине, соответственно postgresql.

Приглашение командной строки -# говорит о том, что это уже не начало запроса, а вы что-то напечатали уже ранее. И psql считает, что вы пишете запрос далее, затем отправляет его весь целиком. Команды самого psql при этом обрабатываются так же.
О том же самом говорит LINE 2.

По таблицам - обратите внимание на регистр. from couriers и from Сouriers - это обращение к таблице couriers. А регистрозависимое имя синтаксически отличается по стандарту.

Могу ответить про postgresql.

без ее блокировки

Именно в такой постановке вопроса - никак.

С короткой блокировкой (доли секунд) - другое дело. Принципиально зависит от конкретного alter table и версии базы
Простой alter table foo add column col; - ставите statement_timeout to '100ms' и вносите как есть.
alter table foo add column col default const - после pg11 см. выше, до - интересное приключение
Если default не константа - сначала внесите как default null, затем обновляете частями. как в процессе до pg11.

Переписать в коррелированный запрос:

update my_table set all_child_qty = (select sum(child.qty) from my_table child where child.my_path LIKE (my_table.my_path || '%'));

или update my_table set ... from (подзапрос) where ...

select table_name from information_schema.columns where column_name = ? and table_schema = ?;

Или то же самое через родной pg_attribute.

Для поиска самих link_id

select link_id from tablename
group by link_id
having count(*) = sum(flag  = 1)

Либо

select id from links
where exists(select null from tablename where flag  = 1 and link_id = links.id) -- возможно это условие вам по задаче не нужно
and not exists(select null from tablename where flag != 1 and link_id = links.id)

Вычитывать сами строки можно попробовать через

select ...
from tablename t
where t.flag = 1
and not exists(select null from tablename sq where sq.link_id = t.link_id and sq.flag != 1)

При том, какой из вариантов эффективнее - критично зависит от распределения данных. При большой числе различающихся link_id с малым числом строк на каждый link_id и малым ожидаемом числе подходящих строк под задачу может быть эффективнее именно

select ... from tablename t where t.link_id in (
select q.link_id from tablename q
group by q.link_id
having count(*) = sum(flag  = 1)
)

При большом числе строк на каждый link_id и малом числе разных link_id может оказаться разумнее аналогично сначала достать link_id удовлетворяющие условию и по ним уже доставать данные.

Третий запрос по ожиданию лучше отзовётся на случай малого числа flag = 1 строк в таблице.

Самый быстрый на чтение вариант, впрочем, всё равно не выполнять обработку этого фильтра налету, а каким-то образом его материализовать и хранить предварительно рассчитанным.

Каждая проверка - целостный срез данных? То есть может быть такое, что для фразы 5 последняя проверка была в 2020-07-05, а для фразы 6 надо отматывать в 2020-07-03?

В общем-то, едим слона по частям:

извлечь суммарную информацию: сколько позиций фраз выросло, сколько позиций просело, сколько позиций осталось неизменными

select count(*) filter(where r1.position < r2.position) as pos_down,
count(*) filter(where r1.position = r2.position) as pos_same,
count(*) filter(where r1.position > r2.position) as pos_up from ...

Укус второй: надо откуда-то получить r1 и r2 соответственно строки таблицы за сравниваемые срезы. Это

tablename r1 full join tablename r2 on r1.phrase_id = r2.phrase_id where r1.... and r2....

Доедаем слона:
нужно дописать условие фильтрации и достать для r1 данные одного среза, для r2 - другого. Тут у вас не вполне конкретизировано что есть "предыдущая проверка". Например, достанем из таблицы tasks предыдущий task_id:

where r2.task_id = 123 and r1.task_id in (select id from tasks where id < 123 order by id desc limit 1)

Cnt число?
Select cnt * interval '1 day'

Увидел 3 ответа, ну думаю, элементарно, ответили уже. Но зачем так сложно?
group by field_name is null
всё. Группировка по выражению, зачем его делать строкой в подзапросе или числом? bool самого по себе более чем достаточно.

select count(*) filter (where status = 'active'),
count(*) filter (where status = 'offline') ...

наиболее оптимально

не делать count вовсе.

select source, 
count( distinct user_id) filter(where watch > 0) as watch_cnt,
count( distinct user_id) filter(where download > 0) as download_cnt
from help group by source

?

select distinct on (price_id), price_id,value  from tablename order by price_id, time_action desc

Если на каждый price_id много строк - то вы неверно думаете что запрос замедляет.

select price_id, value from prices as t1 lateral (select value from test t2 where t1.id=t2.price_id order by time_action desc limit 1) on true;

Если таблицы prices нет - то сооружать loose index scan вручную.
Всё разумеется с подходящим индексом.

ORDER BY ID

хм.

Покажите explain (analyze,buffers) запросов. И покажите планы запросов с "ORDER BY ID + 0". И ещё

select * from pg_stats where tablename = 'table' and attname = 'column'

Я предположу что у вас вовсе не in медленный, а or ломает оценку планировщика, но в этом конкретном случае это оказывается неожиданным благом.

Не надо использовать большой offset. Всё. В любой СУБД.
https://use-the-index-luke.com/no-offset
https://use-the-index-luke.com/sql/partial-results...

Вопросы оптимизации запросов не имеют смысла без указания используемой СУБД.

select ..., p.event_id is not null from events as e left join participations as p on p.user_id = ? and e.id = p.event_id

либо

select ..., exists(select null from participations as p on p.user_id = ? and e.id = p.event_id) from events as e