terrier

Какой-то у вас не такой enum. enum здового человека такой:

CREATE TYPE entity_enum_type as ENUM ('post', 'image', 'video');

Ну и соответсвенно в таблице enum_type будет типа entity_enum_type .
https://www.postgresql.org/docs/9.6/static/datatyp...
Вот такой enum занимает на диске 4 байта на значение, нагляден и весьма эффективен.

Вот так вот более читаемо

CREATE FUNCTION is_tags_equal(article1 text, article2 text)
RETURNS boolean AS
$$
SELECT NOT EXISTS 
	(
		(SELECT tag FROM articles_tags WHERE article = article1 
			EXCEPT 
		SELECT tag FROM articles_tags WHERE article = article2)
		UNION ALL
		(SELECT tag FROM articles_tags WHERE article = article2 
			EXCEPT 
		SELECT tag FROM articles_tags WHERE article = article1)
	)
$$
LANGUAGE sql;

SELECT article FROM articles WHERE is_tags_equal(article, 'ArticleName') AND article != 'ArticleName';

Ну, да это будет работать, но сам по себе способ весьма брутальный. Более гибко было бы переносить архивные таблицы на удаленный сервер и если нужен доступ к архиву или восстановление из архива обращаться к нему через что-нибудь типа pl/proxy

Recheck Cond: (user_id = 114)
Rows Removed by Index Recheck: 153189
Heap Blocks: exact=39222 lossy=26507

У вас делается повторная проверка этого самого условия и она действительно отфильтровывает значительное количество строк. Long story short - вам не хватает work_mem, повысьте.

Посмотрите в pg_stat_activity - никто на локах не зависает? Запросы по часу не исполняются? Соединений получается точно пять?

Ну, собственно загрузить полный список имен в таблицу ( пусть from_excel ) и вперед

with updated as
( update table_name surname='surname' WHERE name='name'  returning name )
select name from from_excel where name not in ( select name from updated );

Prepared statements

https://www.postgresql.org/docs/current/static/sql...

онлайн 500-1000 человек

300-400 запросов в секунду к mysql

На сколько либо нормальном железе 1 mysql-сервера хватит за глаза, redis точно излишний. Могу смело предположить, что у вас там характер нагрузки - короткие более-менее одинаковые запросы и все горячие данные быстро поднимутся в кэш базы ( у вас же не 100 гигабайт горячих данных, правда? ).
Переход на постгрес на таких масштабах заметного прироста именно в производительности скорее всего не даст, тем более понятно, что это затраты. Хотя помним, что у постгреса много других преимуществ.

pg_dump -t table_name

Ваш триггер на инсерт вызывает инсерт ( который вызывает инсерт ).
Вам нужно во-первых объявить его FOR EACH ROW,
во-вторых можифицировать new
new.guid = < ваше выражение>

Выглядит как задача для Logstash

Postgres:

Вопрос в том, будет ли эксклюзивно заблокирована строка из post на время выполнения всех этих операций

Собственно, да, всегда берется FOR UPDATE row-level lock, когда на строку делают delete, reference не влияет на это.
Посмотреть на локи можно примерно так.
Из одной консольки psql делаем

BEGIN;
delete from post where id = 1;

- в одной транзакции делаем delete ( понимаем, что он хватает лок на все, что удаляет )
Из другой консольки

BEGIN;
select * from post where id = 1 for update;

- эта транзакция пытается схватить лок на ту же строку, не может и ждет.

А вот что произойдет при роллбеке/коммите - это зависит от уровня изоляции транзакций.

проверять TG_OP или делать дополнительный триггер

Да, в общем-то и так и так нормально, смотрите как будет более читаемо

если триггер выполняется на большем кол-ве строк операция now() будет вызываться на каждой из них?

Да, собственно, как и написано в объявлении триггера "FOR EACH ROW", то бишь для каждой измененной строки. Ну, конкретно по поводу now() я бы сильно не волновался, это же время на начало транзакции, оно не меняется, то есть, скорее всего просто достается число из ближнего кэша. А вот если бы там была длительная операция вместо now(), вы бы ее в триггер не засунули бы, верно?

Впрочем, если вам принципиально проставлять время один раз для одного SQL-запроса, то можно объявить триггер "FOR EACH STATEMENT", он будет выполняться один раз для каждого запроса. Но в нем, понятное дело недоступны таблицы OLD и NEW, то есть каким-то отдельным запросом надо время проставлять