Comments 19
При использовании линкованных серверов MS SQL для работы с PostgreSQL столкнулись с любопытной проблемой. Если через линкованный сервер в PostgreSQL вставлять большое количество записей (100 тысяч и больше) одним запросом в стиле 'insert into ... select ... from ...', то часть записей до адресата не доходит. И ошибок при этом не возникает. Если делить вставку на блоки, то все записи вставляются. При использовании линкованных серверов для связи между разными экземплярами MS SQL таких проблем не возникает при любых объемах.
Эта конструкция работает очень медленно и не стабильно. Для продуктивного использования подходит только через RPC. Для больших объемов данных приходится доставать левой ногой правое ухо. Передавать с MS SQL данные можно в JSON параметрах процедур или через FDW на стороне PostgreSQL. Обратно - заливая их bcp в глобальную временную таблицу. Вариант через Питон и sp_execute_external_script проще, но менее производительный.
В итоге перешли на интеграцию сервисами и Кафкой.
Используем линкованные сервера в связке с PostgreSQL на ежедневной основе в качестве основного механизма репликации данных с MS SQL уже два года, полет нормальный. Записей достаточно много (десятки миллионов). Механизм очень удобный, особенно при необходимости внесения изменений в большое количество записей одновременно на MS SQL и PostgreSQL. update работает стабильно, insert приходится разбивать.
Вы меня очень удивили. Давно это было, так что пришлось проверять заново. MS SQL и PostgreSQL взял на одинаковых 32-х ядерных виртуалках с 256 ГБ RAM. Взял всего миллион строк, а не десятки миллионов, как у Вас.
DECLARE
@sql_str nvarchar(max),
@proxy_account sysname='ssrs_proxy',
@proxy_password sysname='************',
@start_time datetime,
@ms_sql_time_ms int
SELECT @sql_str='
DROP TABLE IF EXISTS ##test_'+CONVERT(nvarchar(max),@@SPID)+'
CREATE TABLE ##test_'+CONVERT(nvarchar(max),@@SPID)+' (
ID int NOT NULL,
FromId varchar(9) NOT NULL,
ToId varchar(9) NOT NULL,
Std float(53) NOT NULL,
Forecast float(53) NOT NULL
)'
EXEC (@sql_str)
SELECT @sql_str='
DROP TABLE IF EXISTS _test0;
CREATE TABLE _test0 AS
SELECT G.n AS ID, (G.n+1)::text AS FromId,
(G.n+10)::text AS ToId,
G.n::double precision/3 AS Std,
G.n::double precision/7 AS Forecast
FROM generate_series(1,999999) G(n);'
EXEC (@sql_str) AT ELIS_DELTA
SELECT @sql_str='
INSERT INTO ##test_'+CONVERT(nvarchar(max),@@SPID)
+' (Id, FromId, ToId, Std, Forecast)
SELECT Id, FromId, ToId, Std, Forecast
FROM ELIS_DELTA.elis_delta.[public]._test0'
SELECT @start_time=CURRENT_TIMESTAMP
EXEC (@sql_str)
SELECT @ms_sql_time_ms=DATEDIFF(ms,@start_time,CURRENT_TIMESTAMP)
SELECT @sql_str='
COPY (
SELECT Id, FromId, ToId, Std, Forecast
FROM _test0 )
TO PROGRAM $pgm$ EXIT_STATUS=0; tmp_file=$'+'(mktemp /tmp/pgsql_bcp_to_mssql.XXXXXXXXX); '
+'cat > $tmp_file; /opt/mssql-tools/bin/bcp ''##test_'+CONVERT(nvarchar(max),@@SPID)
+''' in $tmp_file -S '+REPLACE(@@SERVERNAME,'\','\\')+' -U '+@proxy_account+' -P '''+@proxy_password
+''' -c -b 10000000 -a 65535 || EXIT_STATUS=$?; '
+'rm $tmp_file; exit $EXIT_STATUS $pgm$ NULL $nil$$nil$;'
SELECT @start_time=CURRENT_TIMESTAMP
EXEC (@sql_str) AT ELIS_DELTA
SELECT @ms_sql_time_ms AS MSSQL,
DATEDIFF(ms,@start_time,CURRENT_TIMESTAMP) AS PGSQL,
CONVERT(float,@ms_sql_time_ms)
/DATEDIFF(ms,@start_time,CURRENT_TIMESTAMP) AS Ratio
-- 23917 3213 7.443821973233738
Разница почти в 7.5 раз, даже не смотря на издержки преобразования вывода в текстовый формат BCP и закачки его через bash.
Как Вы с этим живете на десятках миллионах записей, если даже выборка миллиона весьма коротких строк заняла 24 секунды на довольно мощных серверах?
Тут надо отметить, что вариант подключения не продовский, а для начинающих. Большие данные Postrgre и MS SQL дата инженеры не юзают, по крайней мере у нас. Но кстати как вариант хорошее подспорье для улучшения и разработки своего драйвера. Если сделать норм - будет мощно!
Респект за код!
Для наших целей в целом подходит. Репликация 1 раз в час, среднее время выполнения представленного ниже запроса не больше одной минуты.
update a set ... from [Postgres].[db1].[public].[table1] a -- 1 000 000 записей
inner join [db2].[dbo].[table2] b -- 1 500 000 записей
on a.field = b.field and ...
не быстро конечно, но в нашем случае очень даже удобно )
не больше одной минуты
Вам не кажется, что минута потенциального ожидания в блокировке других процессов - это очень много?
-- 1 000 000 записей
Записей достаточно много (десятки миллионов)
Чему из этого верить?
не быстро конечно, но в нашем случае очень даже удобно )
Догадайтесь с трех раз, что скажет лид разработчику, если тот так будет защищать свое решение в PR )
Postgres вроде бы не блокирует записи при обновлении (мы пока с блокировками по вине репликации не сталкивались) . Десятки миллионов находятся в таблицах куда идет вставка записей, но по вставке обычно за раз тысячи записей или десятки тысяч (зависит от обновления записей в источнике). Что касается защиты, то тут вопрос баланса (как с нормализацией, монолитом и т.д.). В данном случае чем сложнее решение, тем больше вероятность различных внештатных ситуаций. Линкованные сервера для нас привычны. Больше десятка SQL серверов, сотни различных БД, терабайты данных, транзакционная и снимочная репликации между всеми серверами, плюс линкованные сервера. Все стараемся применять по месту. Но мы свой подход никому не навязываем )
Postgres вроде бы не блокирует записи при обновлении
Вы заблуждаетесь. При конкурентном обновлении записей в таблице даже deadlock можно схлопотать.
по вставке обычно за раз тысячи записей или десятки тысяч
Это действительно очень мало. У нас есть топики, по которым несколько сотен миллионов "записей" за сутки пролетает.
Больше десятка SQL серверов, сотни различных БД, терабайты данных, транзакционная и снимочная репликации между всеми серверами
Исходя из этого Вам все равно потребуется уходить от PostgreSQL ODBC + MS SQL Linked Server при росте объемов данных. Причем очень скоро.
Да, согласен. Статья для начинающих, не для коммерческого прода, вначале указал это. Тут статья подготовлена для CRUD приложухи с распред базой, во многих универах требуют. Так что статья больше студенческая, нежели для профи.
Да, согласен. Но статья студенческая, для прода не походит. Во многих универах требуются, вначале статьи указывал. Сам был студентом, стучался в свое время, но вот решил поделиться опытом.
Минутка занудства: Postgres или PostgreSQL, но не "postgre". Уважайте продукт.
А вы вычитывали статью перед публикацией? А то в глаза бросаются явные ошибки
Как сделать связанный сервер для распределенной базы данных. (MSSQL + Postgre)