janko · November 1, 2020 17:26 · Nov 1, 2020 · Nov 1, 2020 · Nov 1, 2020 · Nov 1, 2020
diff --git a/results.md b/results.md
@@ -1,7 +1,7 @@
-| Strategy                    | Execution time | Memory allocated  |
-| :------                     | -------------: | ----------------: |
-| individual inserts          | 35.7 seconds   | 478 MB            |
-| individual prepared inserts | 23.8 seconds   | 634 MB            |
-| bulk insert                 | 8.4 seconds    | 162 MB            |
-| batched bulk insert         | 7.9 seconds    | 158 MB            |
-| database insert             | 2.0 seconds    | 0 MB              |
+| Strategy                    | Execution time | Objects allocated | Memory allocated  |
+| :------                     | -------------: | ----------------: | ----------------: |
+| individual inserts          | 35.7 seconds   | 610k              | 478 MB            |
+| individual prepared inserts | 23.8 seconds   | 480k              | 634 MB            |
+| bulk insert                 | 8.4 seconds    | 21k               | 162 MB            |
+| batched bulk insert         | 7.9 seconds    | 21k               | 158 MB            |
+| database insert             | 2.0 seconds    | 94                | 0 MB              |
diff --git a/script.rb b/script.rb
@@ -61,7 +61,8 @@
     end
   end
   allocated_mb = memory_report.total_allocated_memsize.to_f / (1024 * 1024)
-  puts "#{name} (%.1fs, allocated: %dMB)" % [execution_time, allocated_mb]
+  allocations  = memory_report.total_allocated
+  puts "#{name} (%.1fs, memory: %dMB, objects: %d)" % [execution_time, allocated_mb, allocations]
 end
 
 measure.("individual inserts") do
@@ -146,4 +147,4 @@
   drop_column :event           # this table will only hold approval logs now
   drop_column :target          # this was specific to publication logs
   set_column_not_null :user_id # only publication logs didn't have user id set
-end
+end
diff --git a/results.md b/results.md
@@ -0,0 +1,7 @@
+| Strategy                    | Execution time | Memory allocated  |
+| :------                     | -------------: | ----------------: |
+| individual inserts          | 35.7 seconds   | 478 MB            |
+| individual prepared inserts | 23.8 seconds   | 634 MB            |
+| bulk insert                 | 8.4 seconds    | 162 MB            |
+| batched bulk insert         | 7.9 seconds    | 158 MB            |
+| database insert             | 2.0 seconds    | 0 MB              |
diff --git a/Gemfile b/Gemfile
@@ -0,0 +1,6 @@
+source "https://rubygems.org"
+
+gem "sequel"
+gem "pg"
+gem "sequel_pg"
+gem "memory_profiler"
diff --git a/Gemfile.lock b/Gemfile.lock
@@ -0,0 +1,21 @@
+GEM
+  remote: https://rubygems.org/
+  specs:
+    memory_profiler (0.9.14)
+    pg (1.2.3)
+    sequel (5.37.0)
+    sequel_pg (1.14.0)
+      pg (>= 0.18.0, != 1.2.0)
+      sequel (>= 4.38.0)
+
+PLATFORMS
+  ruby
+
+DEPENDENCIES
+  memory_profiler
+  pg
+  sequel
+  sequel_pg
+
+BUNDLED WITH
+   2.1.4
diff --git a/script.rb b/script.rb
@@ -0,0 +1,149 @@
+require "bundler/setup"
+require "sequel"
+require "benchmark"
+require "memory_profiler"
+
+system "createdb dataset-insert"
+DB = Sequel.postgres("dataset-insert")
+
+at_exit do
+  DB.disconnect
+  system "dropdb dataset-insert"
+end
+
+DB.create_table(:playlists) { primary_key :id }
+DB.create_table(:users)     { primary_key :id }
+
+DB.create_table :activity_logs do
+  primary_key :id
+  foreign_key :playlist_id, :playlists, null: false
+  foreign_key :user_id, :users
+  String :event, null: false
+  String :action, null: false
+  String :message
+  String :target
+  Time :created_at, null: false, default: Sequel::CURRENT_TIMESTAMP
+end
+
+DB[:playlists].multi_insert [{ id: 1 }, { id: 2 }]
+DB[:users].multi_insert [{ id: 1 }]
+
+# 100,000 approval logs
+DB[:activity_logs].multi_insert Array.new(100_000, {
+  playlist_id: 1,
+  user_id:     1,
+  event:       "approval",
+  action:      "approve",
+  message:     "Looks good!",
+})
+
+# 100,000 publication logs
+DB[:activity_logs].multi_insert Array.new(100_000, {
+  playlist_id: 2,
+  event:       "publication",
+  action:      "published",
+  target:      "Video Wall 1",
+})
+
+DB.create_table :publication_logs do
+  primary_key :id
+  foreign_key :playlist_id, :playlists, null: false
+  String :action, null: false
+  String :target
+  Time :created_at, null: false, default: Sequel::CURRENT_TIMESTAMP
+end
+
+measure = -> (name, &block) do
+  execution_time = nil
+  memory_report = MemoryProfiler.report do
+    execution_time = Benchmark.realtime do
+      DB.transaction(rollback: :always, &block)
+    end
+  end
+  allocated_mb = memory_report.total_allocated_memsize.to_f / (1024 * 1024)
+  puts "#{name} (%.1fs, allocated: %dMB)" % [execution_time, allocated_mb]
+end
+
+measure.("individual inserts") do
+  # select records we want to move
+  publication_logs = DB[:activity_logs].where(event: "publication")
+
+  # insert each record individually into the new table
+  publication_logs.each do |log|
+    DB[:publication_logs].insert(
+      playlist_id: log[:playlist_id],
+      action:      log[:action],
+      target:      log[:target],
+      created_at:  log[:created_at],
+    )
+  end
+
+  # delete records from the old table
+  publication_logs.delete
+end
+
+measure.("individual prepared inserts") do
+  # select records we want to move
+  publication_logs = DB[:activity_logs].where(event: "publication")
+
+  prepared_insert = DB[:publication_logs].prepare :insert, :insert_publication_data,
+    playlist_id: :$playlist_id, action: :$action, target: :$target, created_at: :$created_at
+
+  # insert each record individually into the new table
+  publication_logs.each do |log|
+    prepared_insert.call(
+      playlist_id: log[:playlist_id],
+      action:      log[:action],
+      target:      log[:target],
+      created_at:  log[:created_at],
+    )
+  end
+
+  # delete records from the old table
+  publication_logs.delete
+end
+
+measure.("bulk insert") do
+  # select records we want to move
+  publication_logs = DB[:activity_logs].where(event: "publication")
+
+  # insert each record individually into the new table
+  DB[:publication_logs].import [:playlist_id, :action, :target, :created_at],
+    publication_logs.map { |log| log.fetch_values(:playlist_id, :action, :target, :created_at) }
+
+  # delete records from the old table
+  publication_logs.delete
+end
+
+measure.("batched bulk insert") do
+  # select records we want to move
+  publication_logs = DB[:activity_logs].where(event: "publication")
+
+  # insert each record individually into the new table
+  publication_logs.each_slice(1000) do |logs|
+    DB[:publication_logs].import [:playlist_id, :action, :target, :created_at],
+      logs.map { |log| log.fetch_values(:playlist_id, :action, :target, :created_at) }
+  end
+
+  # delete records from the old table
+  publication_logs.delete
+end
+
+measure.("database insert") do
+  # select records we want to move
+  publication_logs = DB[:activity_logs].where(event: "publication")
+
+  DB[:publication_logs].import [:playlist_id, :action, :target, :created_at],
+    publication_logs.select(:playlist_id, :action, :target, :created_at)
+
+  # delete records from the old table
+  publication_logs.delete
+end
+
+DB[:activity_logs].where(event: "publication").delete
+
+DB.alter_table :activity_logs do
+  drop_column :event           # this table will only hold approval logs now
+  drop_column :target          # this was specific to publication logs
+  set_column_not_null :user_id # only publication logs didn't have user id set
+end
No results found