Melkij

Во-первых, индексы. В sql-дампе любой индекс - это несколько десятков байт запроса на создание индекса. В базе - это развесистая структура, которая может занимать порядочно места. Один индекс обычно небольшой - но у вас же он не один?
Во-вторых, как уже упомянули - бинарные файлы могут быть раздуты из-за delete и update запросов, если плохо настроен автовакуум. Касается как самих таблиц, так и индексов.
Третье: например табличка связей из тройки интов. В pg_dump данные пишутся через copy - один заголовок, следом все строки, значения разделены табуляцией. Итого 3 байта на строку данных оверхеда + строковое представление значения. В реальной таблице - у каждой строки есть заголовок из 23 байт. Плюс выравнивание данных. Итого минимум будет 36 байт на каждую строку. (описание структуры таблицы опять же отдельно хранится) Т.е. текстовый вид внезапно компактнее почти на всём диапазоне значений инта для этой таблички.

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

у меня есть таблица, в которой уже есть список уникальных меток

try this:

select label, latest_stamp from labelstable lateral (
    select timestamp as latest_stamp from tablename where tablename.label = labeltable.label
    order by timestamp desc limit 1
) l

Postgresql 9.3 или выше.
Loose index scan по самой таблице тоже можно сделать, он просто будет более многословным.

select json_agg(json_build_object('id_user', id_user, 'data', d)) from (
    select id_user, json_agg(json_build_object('data', val, 'type', type)) as d from (
        select id_user, type, val from tt group by id_user, type, val
    ) t group by id_user
) s;

select id_user, array_agg(data) from tablename group by id_user

Можно и сразу json собрать. С оглядкой на версию базы только.

select json_agg((
select json_build_object('id_user', id_user, 'data', json_agg(data)) from tablename group by id_user
));

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

Сама ошибка говорит о том, что в кодировке latin1 нет эквивалента для символа. Чтобы не иметь себе мозг многими проблемами - пересоздайте (да, именно пересоздайте, кодировка БД может выбираться только при создании БД) базу данных с кодировкой UTF8.

Если только поправить запрос, который у вас на той же схеме работает в mysql:

SELECT p.phone,
count(1) filter(where i.status=7 ) as saled,
count(1) filter(where i.status=3 ) as no_saled
FROM items as i
INNER JOIN phones as p
ON i.user_id=p.users
WHERE p.phone IN("7924445544", "8985545444")
GROUP BY p.phone;

Если же схема другая - то синтаксисом int[] обозначается массив из integer, такой штатных тип данных. В mysql такого нет. У вас именно массив чисел?

postgresql не делает явных различий между аггрегирующими и оконными функциями. Как оконную можно использовать любую аггрегирующую функцию (кроме указанных с списке (или своих реализованных как) Ordered-Set и Hypothetical-Set).

any built-in or user-defined normal aggregate function (but not ordered-set or hypothetical-set aggregates) can be used as a window function

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

select ... from tablename 
where arraycolumn && array['foo', 'bar']::text[]

insert into tablename (field1, field2, field3) 
select field1, field2, 'new values instead field3'
from tablename
/*where*/

Вам нужны tablespace: https://www.postgresql.org/docs/current/static/man...

У вас табличка ожидаемого результата не соответствует исходным данным.

Ну, например, так:

with origtable (name, math, physic, chemie) as (values ('Вася', 80, 52, 69), ('Коля', 50, 90, 95), ('Петя', 62,98, 42)) 
, orders as (
select name, 
row_number() over (order by math desc) as mathid,
row_number() over (order by physic desc) as phid,
row_number() over (order by chemie desc) as chid
 from origtable
)
select m.name as "математика", p.name as "физика", c.name as "химия" from orders as m                              
join orders as p on m.mathid=p.phid
join orders as c on m.mathid=c.chid
order by m.mathid;

математика | физика | химия 
------------+--------+-------
 Вася       | Петя   | Коля
 Петя       | Коля   | Вася
 Коля       | Вася   | Петя

Ожидал получить в выборке не более одной строки с каждым ref_id

GROUP BY ref_id, id

С чего бы это вы ожидали увидеть только один ref_id, если явно сказали затем группировать по id?
С учётом названия и того, что postgresql в принципе согласился запрос выполнить - id является первичным ключом. А группировка по первичному ключу ожидаемо результат не изменит.

select  distinct on (ref_id)  ref_id,  id,  created_at from tablename  order by ref_id, created_at desc;

Как разделить таблицу, горячие данные оставить на SSD, холодные - на HDD. Для этого во-первых партицирование для разделения таблицы на две. https://habrahabr.ru/post/273933/ (как обычно, внимание на комменты и pg_partman)
Затем, до миграции данных (или сразу при создании партиций), перенос архивных в другой tablespace www.postgresql.org/docs/current/static/sql-createt... stackoverflow.com/a/11228536 на HDD.
Затем миграция данных на партиции.
Вообще-то, это уже может быть вполне достаточно. 1-2млн строк * 365 дней это не запредельно много. Хотя не указан характер данных.

Прозрачный для приложения перенос таблиц на другую железку - FDW, foreign data wrapper. Чем актуальнее postgresql - тем лучше. Пилится штука весьма активно по части оптимального распределения запроса. Дружит ли уже с партицированием - честно, не в курсе.

Прозрачно отправить запрос на две базы и склеить - элементарно view с union all из локальной таблицы и FDW. Только это неинтересный вариант, зачем для запроса на горячие данные дёргать холодную часть базы?

Вдобавок, можете посмотреть в сторону postgresql-xl, greenplum. Первый года полтора назад был не вполне production-ready, сейчас не знаю, второй используется даже в банковской сфере, но как мне помнится катастрофически не годится для OLTP, только OLAP нагрузка.