Compare SQLite and PostgreSQL insert performance

SQLiteVsPostgreSQL.lpr

program SQLiteVsPostgresql;
//Required packages : mormot2, multithreadprocslaz
{$I mormot.defines.inc}
{$ModeSwitch nestedprocvars}
uses
 {$I mormot.uses.inc}
  sysutils,
  MTProcs,
  mormot.core.perf,
  mormot.core.base,
  mormot.orm.core,
  mormot.orm.sql,
  mormot.orm.sqlite3,
  mormot.rest.core,
  mormot.rest.sqlite3,
  mormot.db.sql,
  mormot.db.sql.postgres,
  mormot.db.raw.postgres,
  mormot.db.raw.sqlite3,
  mormot.db.raw.sqlite3.static;

{.$DEFINE EnableTransaction}
{$DEFINE Parallel}
const
  PQHost: PChar = '127.0.0.1';
  PQPort: PChar = '5432';
  PQDBName: PChar = 'mormot_postgres_test';
  PQUserName: PChar = 'root';
  PQPassword: PChar = '1';

  InsertDirectCount = 1 * 1000;
  InsertOrmCount = 1 * 1000;
  MultiConnection = 50;
  PerConnection = InsertDirectCount div MultiConnection;

type
  TOrmOITbl = class(TOrm)
  private
    FValue: RawJson;
  published
    property Value: RawJson read FValue write FValue;
  end;


  procedure DummyNoticeProcessor(arg: pointer; message: PUtf8Char); cdecl;
  begin
  end;

  procedure PostgreSQLDirectInsert;
  var
    Connection: PPGconn;
    R: PPGresult;
    Texts: array of String;
    Values: array of PChar;
    Lengths: array of Integer;
    Formats: array of Integer;
    I: Integer;
    timer: TLocalPrecisionTimer;
  begin
    //WriteLn('PostgreSQL :');
    Connection := PQ.SetDBLogin(Pointer(PQHost), Pointer(PQPort), nil, nil, Pointer(PQDBName), Pointer(PQUserName), Pointer(PQPassword));
    if PQ.Status(Connection) = CONNECTION_BAD then
    begin
      Writeln(#9'Failed to connect to pq database');
      Exit;
    end;
    //WriteLn(#9'Connection Successful');
    PQ.SetNoticeProcessor(Connection, @DummyNoticeProcessor, nil); //Minimize speed drop for logging

    PQ.Exec(Connection, 'DROP TABLE IF EXISTS "public"."IDTbl";');
    PQ.Exec(Connection, 'VACUUM');
    PQ.Exec(Connection, 'CREATE TABLE "public"."IDTbl" ( "ID" int4 NOT NULL, "Value" json, CONSTRAINT "IDTbl_pkey" PRIMARY KEY ( "ID" ) );');
    //WriteLn(#9'Database Cleared.');

    R := PQ.Prepare(Connection, 'InsertDirect', 'INSERT INTO "public"."IDTbl" ("ID", "Value") VALUES ($1, $2);', 2, nil);
    PQ.Check(Connection, R, nil, False);
    //WriteLn(#9'Statement Prepared.');

    SetLength(Texts, 2 * InsertDirectCount);
    for I := 1 to InsertDirectCount do
    begin
      Texts[(I - 1) * 2] := IntToStr(I);
      Texts[(I - 1) * 2 + 1] := '{"X": ' + IntToStr(I) + '}';
    end;
    //WriteLn(#9'Data is ready');

    SetLength(Values, 2);
    SetLength(Lengths, 2);
    SetLength(Formats, 2);
    Formats[0] := 0;
    Formats[1] := 0;
    timer := TLocalPrecisionTimer.CreateAndStart;
    {$IFDEF EnableTransaction}
    PQ.Exec(Connection, 'BEGIN TRANSACTION;');
    {$ENDIF}
    for I := 1 to InsertDirectCount do
    begin
      Values[0] := Pointer(Texts[(I - 1) * 2]);
      Values[1] := Pointer(Texts[(I - 1) * 2 + 1]);
      Lengths[0] := Length(Texts[(I - 1) * 2]);
      Lengths[1] := Length(Texts[(I - 1) * 2 + 1]);
      PQ.ExecPrepared(Connection, 'InsertDirect', 2, Pointer(Values), Pointer(Lengths), Pointer(Formats), PGFMT_TEXT);
    end;
    {$IFDEF EnableTransaction}
    PQ.Exec(Connection, 'COMMIT;');
    {$ENDIF}
    WriteLn('PostgreSQL :', #9'Records Per Second : ', timer.PerSec(InsertDirectCount));
    Writeln('---------------------------------------------------------');
    PQ.Finish(Connection);
  end;

  procedure SQLiteDirectInsert;
  var
    Connection: TSqlite3DB;
    dummy: PUtf8Char;
    Stmt: TSqlite3Statement;
    Texts: array of String;
    timer: TLocalPrecisionTimer;
    I: Integer;
  begin
    //WriteLn('SQLite :');
    DeleteFile('sqlite.db');
    sqlite3.open('sqlite.db', Connection);
    //WriteLn(#9'Connection Successful');

    sqlite3.exec(Connection, 'DROP TABLE IF EXISTS "IDTbl";', nil, nil, dummy);
    sqlite3.exec(Connection, 'VACUUM;', nil, nil, dummy);
    sqlite3.exec(Connection, 'CREATE TABLE "IDTbl" ( "ID" INTEGER NOT NULL, "Value" TEXT, PRIMARY KEY ( "ID" ) );', nil, nil, dummy);

    //sqlite3.exec(Connection, 'PRAGMA locking_mode = NORMAL', nil, nil, dummy);
    //sqlite3.exec(Connection, 'PRAGMA journal_mode = WAL', nil, nil, dummy);
    //sqlite3.exec(Connection, 'PRAGMA page_size = 65536', nil, nil, dummy);
    //sqlite3.exec(Connection, 'PRAGMA synchronous = NORMAL', nil, nil, dummy);

    sqlite3.exec(Connection, 'PRAGMA locking_mode = EXCLUSIVE', nil, nil, dummy);
    sqlite3.exec(Connection, 'PRAGMA journal_mode = OFF', nil, nil, dummy);
    //sqlite3.exec(Connection, 'PRAGMA page_size = 65536', nil, nil, dummy);
    sqlite3.exec(Connection, 'PRAGMA synchronous = OFF', nil, nil, dummy);

    //WriteLn(#9'Database Cleared.');

    sqlite3.prepare_v2(Connection, 'INSERT INTO "IDTbl" (ID, Value) VALUES (?, ?);', -1, Stmt, dummy);
    //WriteLn(#9'Statement Prepared.');

    SetLength(Texts, 2 * InsertDirectCount);
    for I := 1 to InsertDirectCount do
    begin
      Texts[(I - 1) * 2] := IntToStr(I);
      Texts[(I - 1) * 2 + 1] := '{"X": ' + IntToStr(I) + '}';
    end;
    //WriteLn(#9'Data is ready');

    timer := TLocalPrecisionTimer.CreateAndStart;
    {$IFDEF EnableTransaction}
    sqlite3.exec(Connection, 'BEGIN TRANSACTION;', nil, nil, dummy);
    {$ENDIF}
    for I := 1 to InsertDirectCount do
    begin
      sqlite3.bind_int(Stmt, 1, I);
      sqlite3.bind_text(stmt, 2, Pointer(Texts[(I - 1) * 2 + 1]), -1, TSqlDestroyPtr(SQLITE_STATIC));
      sqlite3.step(Stmt);
      sqlite3.reset(Stmt);
    end;
    sqlite3.finalize(Stmt);
    {$IFDEF EnableTransaction}
    sqlite3.exec(Connection, 'COMMIT;', nil, nil, dummy);
    {$ENDIF}
    WriteLn('SQLite :', #9'Records Per Second : ', timer.PerSec(InsertDirectCount));
    Writeln('---------------------------------------------------------');

    sqlite3.close(Connection);
  end;

  procedure PostgreSQLORMInsert;
  var
    PQProps: TSqlDBPostgresConnectionProperties;
    Model: TOrmModel;
    Server: TRestServerDB;
    Texts: array of TOrmOITbl;
    timer: TLocalPrecisionTimer;
    I: Integer;

    procedure Execute(Index: PtrInt; Data: Pointer; Item: TMultiThreadProcItem);
    begin
      Server.Add(Texts[Index], True);
    end;

  begin
    //WriteLn('PostgreSQL ORM :');
    PQProps := TSqlDBPostgresConnectionProperties.Create('127.0.0.1', 'mormot_postgres_test', 'root', '1');
    Model := TOrmModel.Create([TOrmOITbl]);
    VirtualTableExternalRegisterAll(Model, PQProps);
    Server := TRestServerDB.Create(Model, ':memory:');
    //WriteLn(#9'Connection Successful');

    Server.DB.LockingMode := lmExclusive;
    Server.DB.Synchronous := smOff;
    Server.DB.Execute('DROP TABLE IF EXISTS "public"."OITbl";');
    Server.DB.Execute('VACUUM;');
    Server.CreateMissingTables();
    //WriteLn(#9'Database Cleared.');

    SetLength(Texts, InsertOrmCount);
    for I := 1 to InsertOrmCount do
    begin
      Texts[I] := TOrmOITbl.Create();
      with Texts[I] do
      begin
        Value := '{"X": ' + IntToStr(I) + '}';
      end;
    end;
    //WriteLn(#9'Data is ready');

    timer := TLocalPrecisionTimer.CreateAndStart;
    {$IFDEF Parallel}
    ProcThreadPool.DoParallelNested(Execute, 1, InsertOrmCount, nil, MultiConnection);
    {$ELSE}
    for I := 1 to InsertOrmCount do
    begin
      Server.Add(Texts[I], True);
    end;
    {$ENDIF}
    WriteLn('PostgreSQL ORM :', #9'Records Per Second : ', timer.PerSec(InsertDirectCount));
    Writeln('---------------------------------------------------------');

    Server.Free;
    Model.Free;
    PQProps.Free;
  end;

  procedure SQLiteORMInsert;
  var
    Model: TOrmModel;
    Server: TRestServerDB;
    Texts: array of TOrmOITbl;
    timer: TLocalPrecisionTimer;
    I: Integer;
    mode: TSqlLockingMode;
    sync: TSqlSynchronousMode;

    procedure Execute(Index: PtrInt; Data: Pointer; Item: TMultiThreadProcItem);
    begin
      Server.Add(Texts[Index], True);
    end;

  begin
    Model := TOrmModel.Create([TOrmOITbl]);
    Server := TRestServerDB.Create(Model, 'sqlite.db');

    for mode := lmNormal to lmExclusive do
      for sync := smOff to smFull do
      begin
        //WriteLn('SQLite ORM ', mode, ' ', sync, ' :');
        Server.DB.LockingMode := mode;
        Server.DB.Synchronous := sync;
        Server.DB.Execute('DROP TABLE IF EXISTS "OITbl";');
        Server.DB.Execute('VACUUM;');
        //Server.DB.Execute('PRAGMA journal_mode = OFF;');
        Server.CreateMissingTables();
        //WriteLn(#9'Database Cleared.');

        SetLength(Texts, InsertOrmCount);
        for I := 1 to InsertOrmCount do
        begin
          Texts[I] := TOrmOITbl.Create();
          with Texts[I] do
          begin
            Value := '{"X": ' + IntToStr(I) + '}';
          end;
        end;
        //WriteLn(#9'Data is ready');

        timer := TLocalPrecisionTimer.CreateAndStart;
    {$IFDEF Parallel}
        ProcThreadPool.DoParallelNested(Execute, 1, InsertOrmCount, nil, MultiConnection);
    {$ELSE}
        for I := 1 to InsertOrmCount do
        begin
          Server.Add(Texts[I], True);
        end;
    {$ENDIF}

        WriteLn('SQLite ORM ', mode, ' ', sync, ' :', #9'Records Per Second : ', timer.PerSec(InsertDirectCount));
        Writeln('---------------------------------------------------------');
      end;
    Server.Free;
    Model.Free;
  end;

{$IFDEF Parallel}
  procedure PostgreSQLMultiConnectionDirectInsert;
  var
    Connections: array of PPGconn;
    R: PPGresult;
    Texts: array of String;
    I: Integer;
    timer: TLocalPrecisionTimer;

    procedure Execute(Index: PtrInt; Data: Pointer; Item: TMultiThreadProcItem);
    var
      Values: array of PChar;
      Lengths: array of Integer;
      Formats: array of Integer;
      I: Integer;
    begin
      SetLength(Values, 2);
      SetLength(Lengths, 2);
      SetLength(Formats, 2);
      Formats[0] := 0;
      Formats[1] := 0;
      {$IFDEF EnableTransaction}
      PQ.Exec(Connections[Index], 'BEGIN TRANSACTION;');
    {$ENDIF}
      for I := Index * PerConnection to (index + 1) * PerConnection - 1 do
      begin
        Values[0] := Pointer(Texts[I * 2]);
        Values[1] := Pointer(Texts[I * 2 + 1]);
        Lengths[0] := Length(Texts[I * 2]);
        Lengths[1] := Length(Texts[I * 2 + 1]);
        PQ.ExecPrepared(Connections[Index], 'InsertDirect', 2, Pointer(Values), Pointer(Lengths), Pointer(Formats), PGFMT_TEXT);
      end;
    {$IFDEF EnableTransaction}
      PQ.Exec(Connections[Index], 'COMMIT;');
    {$ENDIF}
    end;

  begin
    //WriteLn('PostgreSQL Multi-Connection:');
    SetLength(Connections, MultiConnection);
    for I := 0 to MultiConnection - 1 do
    begin
      Connections[I] := PQ.SetDBLogin(Pointer(PQHost), Pointer(PQPort), nil, nil, Pointer(PQDBName), Pointer(PQUserName), Pointer(PQPassword));
      if PQ.Status(Connections[I]) = CONNECTION_BAD then
      begin
        Writeln(#9'Failed to connect to pq database');
        Exit;
      end;
    end;
    PQ.SetNoticeProcessor(Connections[0], @DummyNoticeProcessor, nil); //Minimize speed drop for logging

    PQ.Exec(Connections[0], 'DROP TABLE IF EXISTS "public"."IDTbl";');
    PQ.Exec(Connections[0], 'VACUUM');
    PQ.Exec(Connections[0], 'CREATE TABLE "public"."IDTbl" ( "ID" int4 NOT NULL, "Value" json, CONSTRAINT "IDTbl_pkey" PRIMARY KEY ( "ID" ) );');
    //WriteLn(#9'Database Cleared.');

    for I := 0 to MultiConnection - 1 do
    begin
      R := PQ.Prepare(Connections[I], 'InsertDirect', 'INSERT INTO "public"."IDTbl" ("ID", "Value") VALUES ($1, $2);', 2, nil);
      PQ.Check(Connections[I], R, nil, False);
    end;
    //WriteLn(#9'Statement Prepared.');

    SetLength(Texts, 2 * InsertDirectCount);
    for I := 1 to InsertDirectCount do
    begin
      Texts[(I - 1) * 2] := IntToStr(I);
      Texts[(I - 1) * 2 + 1] := '{"X": ' + IntToStr(I) + '}';
    end;
    //WriteLn(#9'Data is ready');


    timer := TLocalPrecisionTimer.CreateAndStart;

    ProcThreadPool.DoParallelNested(Execute, 0, MultiConnection - 1, nil, MultiConnection);

    WriteLn('PostgreSQL Multi-Connection:', #9'Records Per Second : ', timer.PerSec(InsertDirectCount));
    Writeln('---------------------------------------------------------');
    for I := 0 to MultiConnection - 1 do
      PQ.Finish(Connections[I]);
  end;

{$ENDIF}
begin
  PostgresLibraryInitialize;
  PostgreSQLDirectInsert;
  SQLiteDirectInsert;
  PostgreSQLORMInsert;
  SQLiteORMInsert;
  {$IFDEF Parallel}
  PostgreSQLMultiConnectionDirectInsert;
  {$ENDIF}
end.

Benchmark output in my PC -> OS: Linux , CPU: i9 9940X, RAM: 32G , SSD: M2 Samsung 970 EVO Plus

PostgreSQL : Records Per Second : 894

SQLite : Records Per Second : 11483

PostgreSQL ORM : Records Per Second : 602

SQLite ORM lmNormal smOff : Records Per Second : 406

SQLite ORM lmNormal smNormal : Records Per Second : 181

SQLite ORM lmNormal smFull : Records Per Second : 152

SQLite ORM lmExclusive smOff : Records Per Second : 1268

SQLite ORM lmExclusive smNormal : Records Per Second : 245

SQLite ORM lmExclusive smFull : Records Per Second : 199

PostgreSQL Multi-Connection: Records Per Second : 12940

Please see my answer in the forum.

I guess that on your HW configuration, you could read 1 million inserts per second in ORM BATCH mode (at least on Linux, certainly something less on Windows).
And high number in PostgreSQL in ORM BATCH mode, even without multi-connection.

Forum post : https://synopse.info/forum/viewtopic.php?id=6233