Minimalist High-Load Search (Triad-based)

A high-performance search engine for spare parts catalogs, built with a Thin Backend + Fat Database architecture. This project demonstrates advanced sub-string search optimization techniques for datasets exceeding 1,000,000 records.

💡 Concept & Architecture

The primary challenge with standard LIKE %query% searches in relational databases is the inability to utilize standard indexes, leading to a "Full Table Scan," which is unacceptable under high load.

This project implements Triad-based Indexing (n-gram approach):

1. Data Layer: Every string is decomposed into 3-character segments (triads) upon insertion.
2. Hashing: Triads are converted into numeric hashes and stored in a dedicated indexed table.
3. Search: Instead of expensive text matching, the engine performs a lookup by the numeric hash of the first triad. This allows the database to use a B-Tree index, reducing search latency to milliseconds.
4. Backend: Helidon (Java) serves as an ultra-lightweight proxy, delegating all business logic and JSON response formatting to the PostgreSQL layer (PL/pgSQL).

🤖 AI-Assisted Development

This project was architected and implemented in collaboration with Google Gemini over a 3-day intensive sprint. It showcases a modern "AI + Human" workflow, where complex database optimizations and cloud-native configurations were co-developed through iterative technical dialogue.

🛠 Technology Stack

• Java 21 & Helidon MicroProfile 4.x/6.x: Minimalist RESTful API layer.
• PostgreSQL 17: Data processing, triggers, and core business logic.
• Docker & Docker Compose: Orchestration with integrated health checks.
• PL/pgSQL: "Database-as-a-Service" pattern implementation.

🚀 Key Features

🔹 Multi-Database Compatibility (PostgreSQL & Oracle)

The core logic is implemented using a "Database-as-a-Service" pattern. While this version uses PostgreSQL 17, the architectural design is fully compatible with Oracle DB. The PL/pgSQL logic can be seamlessly ported to Oracle PL/SQL, making this solution ideal for enterprise environments transitioning between different database engines.

🔹 In-Database Processing

All CRUD and search logic is encapsulated within a single entry point in the database. This minimizes network overhead between the application and the database by transmitting only the final JSONB result.

🔹 Automated Indexing

A specialized trigger, trigger_index_triads, automatically updates the search index in real-time during INSERT or UPDATE operations on the parts_catalog table.

🔹 Performance & Bulk Operations

The system natively supports bulk data insertion and updates via JSONB arrays in a single request, which is critical for high-throughput enterprise systems.

📈 Load Testing (1,000,000+ Records)

To demonstrate production-grade performance, a data population script is included:

1. Start the infrastructure:

   docker-compose up -d

2. Run the load test:

   chmod +x load_test.sh
   ./load_test.sh

The script appends 900,000 records to the initial 100,000. You will observe that search latency remains stable even with a million-row catalog.

🚦 Quick Start

1. Clone the repository.
2. Run the build and start command:

   docker-compose up --build

3. The UI will be available at: http://localhost:8080/search/ui (static frontend bundled in resources).

📄 API Reference

Method	Path	Description
GET	/search?q=PART-100	Search by part number (Triad index)
POST	/search	Single or Bulk data insertion
PUT	/search	Single or Bulk data update
DELETE	/search?q={id}	Delete record by ID

---

📄 License

This project is licensed under the MIT License - see the LICENSE file for details.

Developed in 3 days with ❤️, Java, and Google Gemini.