Primary Key Generator for Database Overflow

Solution Overview

Problem

Database applications face primary key overflow issues due to rapid insert and delete operations, leading to insufficient capacity in integer-based primary key columns, which can be mitigated by using larger keys but at the cost of performance and memory efficiency.

Innovation Solution

Implement a system to reclaim and reuse primary key values by identifying gaps in the primary key sequence, allowing for the use of smaller, more memory-efficient primary keys without risking overflow, and managing these reclaimed values through a key generator and caching system.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Reliability

If 8-byte integer primary keys are used to avoid overflow, then the capacity and lifespan of the database application is extended, but the memory consumption and performance overhead increase

Engineering Contradiction:

Improveprimary key capacityVSAvoidmemory consumption

Core Design Contradiction:

ReliabilityVSWeight of stationary object

Solution Approach 1:

The patent implements a primary key reclamation mechanism that identifies and recovers unused primary key values from deleted records. A reclaimer component scans the database table to find gaps in the primary key sequence (where records have been deleted), collects these reclaimed keys, and makes them available for reuse. This allows the system to maintain a compact 4-byte primary key column while effectively extending the usable key space by recycling previously allocated keys, thereby avoiding overflow without requiring larger key sizes.

Inventive Principle:

Principle #34Discarding and recovering

2Weight of stationary object

If 4-byte integer primary keys are used, then memory efficiency and performance are improved, but the primary key column overflows after approximately 298 days in high-volume OLTP applications

Engineering Contradiction:

Improvememory consumptionVSAvoiddatabase lifespan

Core Design Contradiction:

Weight of stationary objectVSDuration of action of stationary object

Solution Approach 1:

The patent implements a primary key reclamation mechanism that identifies and recovers unused primary key values from deleted records. A reclaimer component scans the database table to find gaps in the primary key sequence (where records have been deleted), collects these reclaimed keys, and makes them available for reuse. This allows the system to maintain a compact 4-byte primary key column while effectively extending the usable key space by recycling previously allocated keys, thereby avoiding overflow without requiring larger key sizes.

Inventive Principle:

Principle #34Discarding and recovering

Solution Approach 2:

The system dynamically changes the effective primary key space by introducing a reclamation mechanism. Instead of statically allocating a fixed range of 4-byte integers, the system actively manages and expands the usable key space by identifying gaps and reusing deleted keys. The reclaimer periodically scans the table and updates the available key range, effectively adapting the primary key capacity to the actual data lifecycle patterns.

Inventive Principle:

Principle #35Parameter changes

3Reliability

If auto increment algorithm advances values indefinitely, then unique primary key values are guaranteed, but the primary key column reaches overflow in high-volume insert applications

Engineering Contradiction:

Improveunique key guaranteeVSAvoidprimary key capacity

Core Design Contradiction:

ReliabilityVSQuantity of substance

Solution Approach 1:

The patent implements a primary key reclamation mechanism that identifies and recovers unused primary key values from deleted records. A reclaimer component scans the database table to find gaps in the primary key sequence (where records have been deleted), collects these reclaimed keys, and makes them available for reuse. This allows the system to maintain a compact 4-byte primary key column while effectively extending the usable key space by recycling previously allocated keys, thereby avoiding overflow without requiring larger key sizes.

Inventive Principle:

Principle #34Discarding and recovering

Data Source

PatentUS10146836B2Primary key generator

Publication Date: 2018.12.04 DILLON SOFTWARE SERVICES LLC

AI summary

Systems and methods for reclaiming and reusing primary key values are provided. According to one embodiment, multiple unused primary key values of a database application are reclaimed by a key generator by locating a gap in a sequence of a primary key column of the database application. Reuse of the reclaimed unused primary key values is facilitated by the key generator by returning a reclaimed unused primary key value responsive to a request from the database system for a new primary key value for the primary key column.