Optimizing Bulk Inserts with UPSERT and Conflict Resolution in SQLite

Efficiently Managing Unique Key Conflicts During High-Volume Data Insertion

Issue Overview: Redundant Inserts Due to Primary Key Conflicts

The core challenge involves inserting 114,414+ records from a source table (tt or Project_List) into two target tables (t0 and t1, or their equivalents like PL_ProjType_Search). These target tables enforce uniqueness through composite primary keys (pid, pn and pid, type). The naive approach of inserting all records with INSERT OR REPLACE works but results in redundant writes because SQLite replaces existing rows for duplicate keys. While this leverages SQLite’s fast write capabilities, it becomes inefficient at scale due to unnecessary disk I/O and index maintenance.

The goal is to insert only the latest record for each unique key combination (as determined by the maximum idate or InsertDate), thereby minimizing writes. Initial attempts using GROUP BY with MAX(idate) introduced performance bottlenecks because aggregating large datasets without proper indexing forced full table scans. A breakthrough came with the use of INSERT ... ON CONFLICT DO NOTHING, which avoided redundant inserts but risked leaving outdated idate values if not combined with proper ordering and conflict resolution logic.

Root Causes of Performance Bottlenecks and Data Integrity Risks

1. Unoptimized Aggregation Queries
   The GROUP BY clause in INSERT OR REPLACE queries (e.g., SELECT pid, type, MAX(idate) FROM tt GROUP BY pid, type) requires SQLite to scan the entire source table to compute aggregates. Without indexes on (pid, type, idate) or (pid, pn, idate), this becomes a full-table scan followed by a temporary sort, which is computationally expensive for large datasets.

2. Overhead of Index Maintenance
   Creating indexes on every column involved in GROUP BY or WHERE clauses (as initially attempted) increases write latency. Each index added to the source table (Project_List) forces SQLite to update all relevant indexes during every INSERT, slowing down bulk operations.

3. Misuse of INSERT OR REPLACE
   While INSERT OR REPLACE simplifies handling duplicates by replacing existing rows, it performs a DELETE followed by an INSERT under the hood. This triggers index rebuilds and foreign key checks (if enabled), which is less efficient than directly updating existing rows when applicable.

4. Unstable Ordering in Conflict Resolution
   Relying on implicit row order (e.g., INSERT INTO t0 SELECT ... FROM tt) without an explicit ORDER BY clause risks inserting older idate values first. Subsequent rows with the same key but newer idate would trigger replacements, leading to multiple writes for the same key.

5. Incomplete Conflict Handling with DO NOTHING
   Using ON CONFLICT DO NOTHING without ensuring the selected row has the latest idate can leave outdated data in the target table. For example, if an older idate is inserted first, newer entries for the same key are ignored, violating the requirement to retain the most recent record.

Step-by-Step Solutions for Efficient, Correct Data Insertion

Step 1: Optimize Source Table Indexes for Aggregation

Create composite indexes that allow SQLite to resolve GROUP BY and MAX() queries without scanning the entire table. For the tt or Project_List table:

-- Index for t0 (pid, pn, idate)
CREATE INDEX idx_tt_pid_pn_idate ON tt (pid, pn, idate);

-- Index for t1 (pid, type, idate)
CREATE INDEX idx_tt_pid_type_idate ON tt (pid, type, idate);

These indexes enable the database to:

• Quickly locate all rows for a given pid and pn (or type).
• Identify the maximum idate within each group using an index scan instead of a full-table scan.

Verification:
Run EXPLAIN QUERY PLAN on the SELECT portion of the insert statement to confirm index usage:

EXPLAIN QUERY PLAN
SELECT pid, pn, MAX(idate) FROM tt GROUP BY pid, pn;

Output should include USING INDEX idx_tt_pid_pn_idate.

Step 2: Use Conditional UPSERTs to Update Only When Necessary

Replace INSERT OR REPLACE with INSERT ... ON CONFLICT DO UPDATE to avoid deleting and reinserting rows. This approach updates only the idate when a newer value exists:

INSERT INTO t0 (pid, pn, idate)
SELECT pid, pn, idate FROM tt
WHERE true  -- Optional: Add filtering logic here
ORDER BY idate DESC  -- Ensure newer rows are processed first
ON CONFLICT (pid, pn) DO UPDATE SET
  idate = excluded.idate
WHERE excluded.idate > t0.idate;

Breakdown:

• ORDER BY idate DESC: Processes newer records first. If a conflict occurs, the newer idate is already in the target table, and subsequent older entries for the same key are ignored.
• DO UPDATE SET idate = excluded.idate WHERE ...: Updates the idate only if the incoming value is newer, minimizing writes.

Performance Note:
The ORDER BY clause adds a sorting step, but with an index on (pid, pn, idate DESC), sorting becomes a no-op because the index already provides the required order.

Step 3: Implement Batched Inserts with Transaction Control

For extremely large datasets (100k+ rows), wrap inserts in transactions to reduce disk I/O and journaling overhead:

BEGIN TRANSACTION;
INSERT INTO t0 ...
COMMIT;

Adjustment for Incremental Loads:
If the source table (tt) is continuously updated, use a WHERE clause to filter records inserted since the last run:

INSERT INTO t0 ...
SELECT pid, pn, idate FROM tt
WHERE idate > (SELECT MAX(idate) FROM t0);

Step 4: Benchmark and Compare Approaches

Use SQLite’s PRAGMA temp_store = MEMORY; and PRAGMA journal_mode = WAL; to optimize temporary storage and write-ahead logging. Compare execution times for:

1. Naive INSERT OR REPLACE
   Baseline measurement for full-row replacements.

2. GROUP BY with Indexes
   Verify if indexes reduce aggregation time.

3. ON CONFLICT DO UPDATE with Ordering
   Measure the impact of conditional updates versus replacements.

Example Benchmark Results:

Step 5: Monitor Index Impact and Adjust as Needed

Use SQLite’s sqlite_stat1 table to analyze index usage:

ANALYZE;
SELECT * FROM sqlite_stat1 WHERE tbl = 'tt';

If an index is unused (e.g., idx_tt_pid_type_idate has stat = 114414 1), consider dropping it to reduce write overhead.

Final Recommendations:

• For Static Data: Use CREATE VIEW t0 AS SELECT pid, pn, MAX(idate)... to eliminate physical writes entirely.
• For Dynamic Data: Combine INSERT ... ON CONFLICT DO UPDATE with composite indexes on (pid, [pn/type], idate DESC).
• Avoid Over-Indexing: Create only indexes that directly accelerate critical GROUP BY and ORDER BY clauses.

By addressing the root causes—inefficient aggregation, redundant writes, and unstable ordering—this approach reduces insertion time by 98% (from 27.55s to 0.23s in the example) while ensuring the target tables always reflect the latest data.