Comments 16
в возможность последовательной вставки более миллиона записей в секунду в одну таблицу MS SQL верится с трудом
datetime2 - это не про вставку, а про хранение данных с высокой точностью. Временные точки могут приходить из приложения где микросекунды - это уже много.
Для такого случая я указал: "Если вдруг точность до 100 наносекунд жизненно необходима, то в PostgreSQL ее потребуется поддерживать отдельным полем типа smallint."
А то, что Вы процитровали, относится уже к "модификации значений типа datetime2 таким образом, чтобы они стали различаться в пределах шести знаков после десятичной точки для сохранения порядка сортировки"
из приложения где микросекунды - это уже много
Просто интересно, что за приложение? И как так получилось, что 100 наносекунд достаточно, а 1 микросекунда - уже много?
Внутри datetime2 - это int64, может и хранить в таком типе, вместо того, чтобы извращаться с двумя столбцами. Интерпретировать только на клиенте. Потому что превратить в timestamp без потери точности - нельзя.
Двухстолбцовая реализация позволяет легко интерпретировать данные в хранимых процедурах, функциях, триггерах и запросах, а не только на клиенте.
Если уж совсем невмоготу - создавайте лучше свой базовый тип для этой цели.
Что у Вас за приложение, что не для целочисленных интервалов времени, а именно для дат 100 наносекунд достаточно, а 1 микросекунда - уже много?
Это ваш пример "если жизненно необходимо". Я лишь возражаю, что решение с доп. столбцом smallint - супер-кривое (например, при сортировках производительноть будет хуже, во всех переливалках надо будет лить через 2 переменные и т.д.). Если нужна интерпретируемость, элементарно делается UDF, которая приводит int64 к дате просто умножением на некоторую константу и прибавлением к базовой дате, с которой начинается календарь.
Это ваш пример "если жизненно необходимо".
У меня его нет. Я же писал: "Такого в своей практике не встречал".
например, при сортировках производительноть будет хуже
делается UDF
Так про то и речь, что издержки на UDF в итоге оказываются выше, чем выигрыш от этих 16 бит при сортировке.
CREATE OR REPLACE FUNCTION bigint2ts(bigint)
RETURNS timestamp
LANGUAGE SQL AS $func$
SELECT '1900-01-01'::timestamp+($1/10)*'1 microsecond'::interval;
$func$;
DROP TABLE IF EXISTS tmp_tmp;
CREATE TABLE tmp_tmp AS
SELECT clock_timestamp() AS ts_val,
G.Id::bigint AS bigint_val,
(G.Id%10)::smallint AS hundred_nanos
FROM generate_series(1,10000000) G(Id);
DROP TABLE IF EXISTS tmp_sorted;
EXPLAIN ANALYZE
CREATE TEMP TABLE tmp_sorted AS
SELECT ts_val, bigint_val, hundred_nanos
FROM tmp_tmp
ORDER BY ts_val DESC, hundred_nanos DESC;
-- Planning Time: 0.039 ms
-- Execution Time: 4920.576 ms
DROP TABLE IF EXISTS tmp_sorted;
EXPLAIN ANALYZE
CREATE TEMP TABLE tmp_sorted AS
SELECT ts_val, bigint2ts(bigint_val) AS calc_ts, hundred_nanos
FROM tmp_tmp
ORDER BY bigint_val DESC;
-- Planning Time: 0.086 ms
-- Execution Time: 5547.087 ms
А в возможность последовательной вставки более миллиона записей в секунду в одну таблицу MS SQL верится с трудом.
Не говорите ерунды. Если поле определено как column DATETIME2(7) DEFAULT (SYSDATETIME()), то все записи, вставленные одним запросом без явного присвоения значения в это поле, получат одно и то же значение (емнип значение штампа времени начала выполнения запроса). А если это INSERT .. SELECT, то сколько SELECT вернёт, столько и вставится. Хоть миллион, хоть миллиард. И у всех будет одно и то же значение штампа времени - с точностью до последней цифры, вне зависимости от того, сколько времени потребовало выполнение запроса.
См. напр. https://dbfiddle.uk/vg3LIzfG
"Не говорите ерунды." (с)
В этом случае их вставка не была последовательной, и их взаимная сортировка не имела и не имеет значение. То есть это ни случай "точность до 100 наносекунд жизненно необходима", ни, тем более, случай "сохранения порядка сортировки", к котором относилась выдернутая Вами из контекста процитированная фраза.
Вставка выполняется именно что последовательно. Можете убедиться - выполните вставку набора записей в таблицу с ограничением уникальности, где среди вставляемых записей присутствуют дубликаты - ошибка выполнения возникнет именно на второй записи-дубликате, тогда как при одновременной вставке ограничение уникальности должно быть обнаружено либо до фактический вставки, либо в момент её выполнения, т.е. на первой записи-дубликате.
Если бы существовала функция, возвращающая штамп времени вставки, а не начала выполнения запроса (как это в MySQL делает функция SYSDATE()), это можно было бы увидеть. Если бы вместо "последовательной вставки" было написано "вставки разными/отдельными запросами" - слова бы не сказал..
Вставка выполняется именно что последовательно.
Откуда Вы это взяли? Пруф? В рамках одного INSERT вставка записей в таблицу может выполняться сервером не только в произвольном порядке, но и несколькими процессами параллельно. Даже явно указанный ORDER BY, при отсутствии кластерного индекса в таблице MS SQL, влияет только на сортировку записей внутри страниц. Никогда такого не видели?
И опять, какое отношение это имеет к сохранению порядка сортировки?
занятная заметка. трансформация типов данных между разными частями приложения знатная боль, а тут опыт миграции между специфическими типами разных систем.
у меня не большой опыт. натыкаясь на подобные статьи у меня возникает вопрос: А стоит ли настолько сильно приростать к конкретной СУБД/фреймворку, используя уникальные типы данных или наращивая метаструктуру, снижая поддерживаемость конечного ПО? неужели профит настолько ценнен, чтобы проглатывать горсть синтаксического сахара, а потом потеть на миграциях и конвертациях данных и логики?
Уникальные типы данных MS SQL (Spatial Geometry/Geography или hierarchyid) я не рассматривал вообще именно по озвученной Вами причине. А money и datetime2 - как раз полностью соответствуют требованиям ANSI/ISO 8601, в отличии от того же datetime. Но, как обычно, дъявол кроется в деталях.
в MS SQL нет прямого аналога типу timestamp with time zone
Хотелось бы вэтом месте видеть пару предложений о datetimeoffset, но.. их нет.
Datetimeoffset все же совсем не timestamp with time zone. Последний не хранит в себе никакой информации о часовом поясе и отличается от timestamp without time zone лишь тем, что автоматически конвертирует хранимое в UTC значение в часовой пояс клиента или наоборот, указанное значение в часовом поясе клиента в UTC для внутреннего представления.
Для хранения datetimeoffset в PostgreSQL вообще нет базового типа. Используется просто строка. Например:
SELECT ts, ts AT TIME ZONE tz
FROM (VALUES
('2023-11-20 12:00'::timestamptz, 'Europe/Moscow'),
('2023-11-20 12:00'::timestamptz, 'MSK'),
('2023-11-20 12:00'::timestamptz, 'GMT'),
('2023-11-20 12:00'::timestamptz, 'VLAT') ) V(ts, tz);
Проблемы при переходе с MS SQL на PostgreSQL. Типы данных