Большая транзакция SQL: заканчивается память на PostgreSQL, но работает на SQL Server

Я решил перенести свое приложение для демона С# (используя dotConnect как поставщик ADO.NET) из SQL Server 2008 R2 в PostgreSQL 9.0.4 x64 (в Windows Server 2008 R2). Поэтому я немного изменил все запросы, чтобы сопоставить синтаксис PostgreSQL и... застрял в поведении, которое никогда не случалось с теми же запросами на SQL Server (даже не с низкой версией Express).

Скажем, база данных содержит две очень простые таблицы без какого-либо отношения друг к другу. Они выглядят примерно так: ID, Name, Model, ScanDate, Notes. У меня есть процесс преобразования, который считывает данные по TCP/IP, обрабатывает его, запускает транзакцию и помещает результаты в вышеупомянутые 2 таблицы с использованием ванильных INSERT. Таблицы изначально пусты; нет столбцов BLOB. В плохой день около 500 000 INSERT, все они завернуты в одну транзакцию (и не могут быть разделены на несколько транзакций, кстати). Нет SELECT, UPDATE или DELETE когда-либо созданных. Пример INSERT (идентификатор - bigserial - автоматически автоинкремент):

INSERT INTO logs."Incoming" ("Name", "Model", "ScanDate", "Notes")
VALUES('Ford', 'Focus', '2011-06-01 14:12:32', NULL)

SQL Server спокойно принимает нагрузку при сохранении разумного рабочего набора ~ 200 МБ. PostgreSQL, однако, занимает дополнительно 30 МБ каждую секунду, когда транзакция выполняется (!) И быстро исчерпывает системную память.

Я сделал свой RTFM и попытался возиться с postgresql.conf: установка "work_mem" до минимума 64 кБ (это немного замедлило работу в режиме RAM), уменьшив "shared_buffers" / "temp_buffers" до минимума (без разницы), но безрезультатно. Снижение уровня изоляции транзакций до Read Uncommitted не помогло. Нет индексов, кроме индекса ID BIGSERIAL (PK). SqlCommand.Prepare() не имеет значения. Никаких параллельных соединений никогда не было установлено: daemon использует базу данных исключительно.

Может показаться, что PostgreSQL не может справиться с умопомрачительно простым INSERT-fest, в то время как SQL Server может это сделать. Может быть, PostgreSQL snapshot-vs-SQL Server блокирует разницу в изоляции? Для меня это факт: ванильный SQL Server работает, в то время как ни ванильный, ни измененный PostgreSQL не делает.

Что я могу сделать, чтобы потребление памяти PostgreSQL оставалось плоским (как это, по-видимому, имеет место с SQL Server), в то время как транзакция на основе INSERT работает?

EDIT: я создал искусственный тестовый файл:

DDL

CREATE TABLE sometable
(
  "ID" bigserial NOT NULL,
  "Name" character varying(255) NOT NULL,
  "Model" character varying(255) NOT NULL,
  "ScanDate" date NOT NULL,
  CONSTRAINT "PK" PRIMARY KEY ("ID")
)
WITH (
  OIDS=FALSE
);

С# (требуется Devart.Data.dll и Devart.Data.PostgreSql.dll)

PgSqlConnection conn = new PgSqlConnection("Host=localhost; Port=5432; Database=testdb; UserId=postgres; Password=###########");
conn.Open();
PgSqlTransaction tx = conn.BeginTransaction(IsolationLevel.ReadCommitted);

for (int ii = 0; ii < 300000; ii++)
{
    PgSqlCommand cmd = conn.CreateCommand();
    cmd.Transaction = tx;
    cmd.CommandType = CommandType.Text;
    cmd.CommandText = "INSERT INTO public.\"sometable\" (\"Name\", \"Model\", \"ScanDate\") VALUES(@name, @model, @scanDate) RETURNING \"ID\"";
    PgSqlParameter parm = cmd.CreateParameter();
    parm.ParameterName = "@name";
    parm.Value = "SomeName";
    cmd.Parameters.Add(parm);

    parm = cmd.CreateParameter();
    parm.ParameterName = "@model";
    parm.Value = "SomeModel";
    cmd.Parameters.Add(parm);

    parm = cmd.CreateParameter();
    parm.ParameterName = "@scanDate";
    parm.PgSqlType = PgSqlType.Date;
    parm.Value = new DateTime(2011, 6, 1, 14, 12, 13);
    cmd.Parameters.Add(parm);

    cmd.Prepare();

    long newID = (long)cmd.ExecuteScalar();
}

tx.Commit();

Это воссоздает захват памяти. ОДНАКО: если создается переменная 'cmd' и не приводят к единому плану запроса внутри PostgreSQL, как в SQL Server.

Проблема остается: если вы используете Fowler Active Record dp для вставки нескольких новых объектов, готовый общий доступ к экземплярам PgSqlCommand не является элегантным.

Есть ли способ/вариант для облегчения повторного использования плана запроса с несколькими запросами, имеющими идентичную структуру, но с разными значениями аргументов?

UPDATE

Я решил посмотреть на самый простой случай - где пакет SQL запускается непосредственно на СУБД, без ADO.NET(предложенный Jordani). Удивительно, но PostgreSQL не сравнивает входящие SQL-запросы и не повторно использует внутренние компилированные планы, даже если входящий запрос имеет одинаковые одинаковые аргументы! Например, следующая партия:

PostgreSQL (через pgAdmin → Выполнять запрос) - память hogs

BEGIN TRANSACTION;

INSERT INTO public."sometable" ("Name", "Model", "ScanDate") VALUES('somename', 'somemodel', '2011-06-01 14:12:19');
INSERT INTO public."sometable" ("Name", "Model", "ScanDate") VALUES('somename', 'somemodel', '2011-06-01 14:12:19');
-- the same INSERT is repeated 100.000 times

COMMIT;

SQL Server (через Management Studio → Execute) - поддерживает использование памяти

BEGIN TRANSACTION;

INSERT INTO [dbo].sometable ("Name", "Model", "ScanDate") VALUES('somename', 'somemodel', '2011-06-01 14:12:19');
INSERT INTO [dbo].sometable ("Name", "Model", "ScanDate") VALUES('somename', 'somemodel', '2011-06-01 14:12:19');
-- the same INSERT is repeated 100.000 times

COMMIT;

и файл журнала PostgreSQL (спасибо, Sayap!) содержит:

2011-06-05 16:06:29 EEST LOG:  duration: 0.000 ms  statement: set client_encoding to 'UNICODE'
2011-06-05 16:06:43 EEST LOG:  duration: 15039.000 ms  statement: BEGIN TRANSACTION;

INSERT INTO public."sometable" ("Name", "Model", "ScanDate") VALUES('somename', 'somemodel', '2011-06-01 14:12:19');
INSERT INTO public."sometable" ("Name", "Model", "ScanDate") VALUES('somename', 'somemodel', '2011-06-01 14:12:19');
-- 99998 lines of the same as above
COMMIT;

По-видимому, даже после передачи всего запроса на сервер как есть, сервер не может его оптимизировать.

Альтернативный драйвер ADO.NET

Как предположил Жордани, я попробовал драйвер NpgSql вместо dotConnect - с тем же (отсутствием) результатов. Однако источник Npgsql для метода .Prepare() содержит такие просветляющие строки:

planName = m_Connector.NextPlanName();
String portalName = m_Connector.NextPortalName();
parse = new NpgsqlParse(planName, GetParseCommandText(), new Int32[] { });
m_Connector.Parse(parse);

Новое содержимое в файле журнала:

2011-06-05 15:25:26 EEST LOG:  duration: 0.000 ms  statement: BEGIN; SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
2011-06-05 15:25:26 EEST LOG:  duration: 1.000 ms  parse npgsqlplan1: INSERT INTO public."sometable" ("Name", "Model", "ScanDate") VALUES($1::varchar(255), $2::varchar(255), $3::date) RETURNING "ID"
2011-06-05 15:25:26 EEST LOG:  duration: 0.000 ms  bind npgsqlplan1: INSERT INTO public."sometable" ("Name", "Model", "ScanDate") VALUES($1::varchar(255), $2::varchar(255), $3::date) RETURNING "ID"
2011-06-05 15:25:26 EEST DETAIL:  parameters: $1 = 'SomeName', $2 = 'SomeModel', $3 = '2011-06-01'
2011-06-05 15:25:26 EEST LOG:  duration: 1.000 ms  execute npgsqlplan1: INSERT INTO public."sometable" ("Name", "Model", "ScanDate") VALUES($1::varchar(255), $2::varchar(255), $3::date) RETURNING "ID"
2011-06-05 15:25:26 EEST DETAIL:  parameters: $1 = 'SomeName', $2 = 'SomeModel', $3 = '2011-06-01'
2011-06-05 15:25:26 EEST LOG:  duration: 0.000 ms  parse npgsqlplan2: INSERT INTO public."sometable" ("Name", "Model", "ScanDate") VALUES($1::varchar(255), $2::varchar(255), $3::date) RETURNING "ID"
2011-06-05 15:25:26 EEST LOG:  duration: 0.000 ms  bind npgsqlplan2: INSERT INTO public."sometable" ("Name", "Model", "ScanDate") VALUES($1::varchar(255), $2::varchar(255), $3::date) RETURNING "ID"
2011-06-05 15:25:26 EEST DETAIL:  parameters: $1 = 'SomeName', $2 = 'SomeModel', $3 = '2011-06-01'
2011-06-05 15:25:26 EEST LOG:  duration: 0.000 ms  execute npgsqlplan2: INSERT INTO public."sometable" ("Name", "Model", "ScanDate") VALUES($1::varchar(255), $2::varchar(255), $3::date) RETURNING "ID"
2011-06-05 15:25:26 EEST DETAIL:  parameters: $1 = 'SomeName', $2 = 'SomeModel', $3 = '2011-06-01'
2011-06-05 15:25:26 EEST LOG:  duration: 0.000 ms  parse npgsqlplan3: INSERT INTO public."sometable" ("Name", "Model", "ScanDate") VALUES($1::varchar(255), $2::varchar(255), $3::date) RETURNING "ID"

Неэффективность совершенно очевидна в этом отрывке журнала...

Выводы (такие как они)

Замечание Фрэнк о WAL - это еще одно пробуждение: что-то еще, чтобы настроить, что SQL Server скрывается от типичного разработчика MS.

NHibernate (даже в своем простом использовании) повторно использует подготовленные SqlCommands правильно... если только он использовался с самого начала...

очевидно, что существует разница в архитектуре между SQL Server и PostgreSQL, а код, специально построенный для SQL Server (и, таким образом, блаженно не осознающий возможность "невозможного повторного использования идентичного-sql" ) не будет эффективно работать PostgreSQL без основного рефакторинга. Рефакторинг 130 + старых классов ActiveRecord для повторного использования подготовленных объектов SqlCommand в беспорядочном многопоточном промежуточном программном обеспечении не является делом "just-replace-dbo-with-public".

К сожалению, для моей сверхурочной работы правильный ответ Eevar:)

Спасибо всем, кто разбил!

Ответы

    cmd = conn.CreateCommand(); 
    parm1 = cmd.CreateParameter();
    parm1.ParameterName = "@name";
    parm2 = cmd.CreateParameter();
    parm2.ParameterName = "@model";
    parm3 = cmd.CreateParameter(); 
    parm3.ParameterName = "@scanDate"; 

// NOTE:
// For optimal performance, make sure you always set the parameter
// type and the maximum size - this is especially important for non-fixed
// types such as NVARCHAR or NTEXT;