[](https://github.com/rahulmoundekar/fulltext-search-api/actions/workflows/ci.yml)

A production-style Spring Boot REST API demonstrating PostgreSQL-native Full-Text Search, relevance ranking, fuzzy search, filtering, sorting, pagination, search suggestions, and database indexing.

This project demonstrates how to build a powerful product search API using PostgreSQL Full-Text Search instead of introducing a separate search engine such as Elasticsearch.

The API supports:

1. PostgreSQL Full-Text Search
2. Fuzzy Search fallback

When a normal Full-Text Search doesn't return results, the API can fall back to PostgreSQL trigram-based fuzzy matching.

                    Search Request
                           |
                           v
                    Query Validation
                           |
                           v
                      PostgreSQL
                     Full-Text Search
                           |
                  +--------+--------+
                  |                 |
              Results            No Results
                  |                 |
                  |                 v
                  |           Fuzzy Search
                  |             pg_trgm
                  |                 |
                  +--------+--------+
                           |
                           v
                     Search Ranking
                           |
                           v
                        Filters
                           |
                           v
                         Sorting
                           |
                           v
                       Pagination
                           |
                           v
                    REST API Response

• PostgreSQL Full-Text Search
• tsvector
• websearch_to_tsquery()
• ts_rank()
• PostgreSQL pg_trgm
• word_similarity()
• Fuzzy search fallback
• Search suggestions
• Relevance-based ranking
• Rating-based ranking
• Review-count based popularity ranking
• Brand filtering
• Category filtering
• Minimum price filtering
• Maximum price filtering
• Multiple sorting strategies
• Pagination
• Native PostgreSQL queries
• Spring Data JPA projections
• Flyway database migrations
• PostgreSQL GIN indexes
• Request validation
• Global exception handling
• Swagger/OpenAPI documentation

                         Client
                           |
                           v
                  ProductController
                           |
                           v
                   ProductService
                           |
             +-------------+-------------+
             |                           |
             v                           v
      Full-Text Search              Fuzzy Search
             |                           |
        tsvector                    pg_trgm
        tsquery                    word_similarity()
        ts_rank()                       |
             |                           |
             +-------------+-------------+
                           |
                           v
                    ProductRepository
                           |
                           v
                       PostgreSQL
                           |
                           v
                       products

full-text-search-api/
│
├── src/
│   ├── main/
│   │   ├── java/
│   │   │   └── com/rahul/api/
│   │   │       │
│   │   │       ├── config/
│   │   │       │   ├── OpenApiConfig.java
│   │   │       │   └── SearchConstants.java
│   │   │       │
│   │   │       ├── controller/
│   │   │       │   └── ProductController.java
│   │   │       │
│   │   │       ├── dto/
│   │   │       │   ├── ApiErrorResponse.java
│   │   │       │   ├── ProductRequest.java
│   │   │       │   ├── ProductResponse.java
│   │   │       │   ├── ProductSearchResponse.java
│   │   │       │   └── FuzzySearchResponse.java
│   │   │       │
│   │   │       ├── entity/
│   │   │       │   └── Product.java
│   │   │       │
│   │   │       ├── enums/
│   │   │       │   └── SearchSort.java
│   │   │       │
│   │   │       ├── exception/
│   │   │       │   ├── GlobalExceptionHandler.java
│   │   │       │   └── ProductNotFoundException.java
│   │   │       │
│   │   │       ├── repository/
│   │   │       │   ├── ProductRepository.java
│   │   │       │   └── projection/
│   │   │       │       ├── ProductSearchProjection.java
│   │   │       │       └── FuzzyProductProjection.java
│   │   │       │
│   │   │       └── service/
│   │   │           ├── ProductService.java
│   │   │           └── impl/
│   │   │               └── ProductServiceImpl.java
│   │   │
│   │   └── resources/
│   │       ├── db/
│   │       │   └── migration/
│   │       │       └── V1__create_products.sql
│   │       │
│   │       └── application.yml
│   │
├── pom.xml
├── README.md
└── .gitignore

The project uses PostgreSQL.

Example database:

CREATE DATABASE fulltext_search;

The project uses the PostgreSQL pg_trgm extension for fuzzy search.

CREATE EXTENSION IF NOT EXISTS pg_trgm;

Verify:

SELECT extname
FROM pg_extension
WHERE extname = 'pg_trgm';

Expected:

pg_trgm

The application works with product fields such as:

id
title
description
brand
category
price
rating
review_count
search_vector
created_at
updated_at

The search_vector column stores the PostgreSQL Full-Text Search representation of searchable product data.

PostgreSQL Full-Text Search is the primary search mechanism.

The searchable data is represented using:

tsvector

and the user query is converted into:

tsquery

The search condition is:

p.search_vector @@ websearch_to_tsquery(
    'english',
    :query
)

The @@ operator determines whether the document matches the search query.

The project uses:

websearch_to_tsquery()

instead of manually constructing a tsquery.

It provides search behavior closer to what users expect from a normal search box.

For example:

iphone pro

can be converted into a PostgreSQL search query without manually constructing PostgreSQL operators.

Matching products are ranked using:

ts_rank()

Example:

SELECT
    p.id,
    p.title,
    ts_rank(
        p.search_vector,
        websearch_to_tsquery('english', :query)
    ) AS text_score
FROM products p
WHERE p.search_vector @@
      websearch_to_tsquery('english', :query)
ORDER BY text_score DESC;

The API goes beyond simple text relevance.

The final ranking combines:

70% Text Relevance
20% Product Rating
10% Review Popularity

Formula:

(
    0.70 * ts_rank(
        p.search_vector,
        websearch_to_tsquery('english', :query)
    )
    +
    0.20 * COALESCE(p.rating, 0) / 5.0
    +
    0.10 * LEAST(
        LN(1 + COALESCE(p.review_count, 0)) / 10.0,
        1.0
    )
)

This allows the API to prioritize products that are both relevant and popular.

Consider two products:

Product A
Text relevance = 0.90
Rating = 4.8
Reviews = 1000

Product B
Text relevance = 0.92
Rating = 3.0
Reviews = 5

If we only use text relevance:

Product B > Product A

But with the combined ranking model:

Text relevance
+
Rating
+
Popularity

Product A can receive the better overall ranking.

This is closer to how real-world product search systems can be designed.

Users don't always type the correct spelling.

Example:

Correct:
iphone

User enters:

iphne

Full-Text Search may not return a useful result.

The API therefore provides a fuzzy-search fallback.

The project uses:

pg_trgm

which provides trigram-based text similarity.

A trigram represents a sequence of three characters.

This allows PostgreSQL to calculate similarity between strings.

The project uses:

word_similarity(
    LOWER(:query),
    LOWER(p.title)
)

Example:

SELECT
    title,
    word_similarity(
        LOWER('iphne'),
        LOWER(title)
    ) AS similarity
FROM products
ORDER BY similarity DESC;

This allows typo-tolerant matching.

The fuzzy search also uses:

LOWER(p.title) % LOWER(:query)

as a candidate filter.

The fuzzy condition is approximately:

WHERE
    LOWER(p.title) % LOWER(:query)

    AND word_similarity(
        LOWER(:query),
        LOWER(p.title)
    ) >= :threshold

The purpose is:

% operator
    ↓
Find suitable candidates

word_similarity()
    ↓
Calculate similarity

score
    ↓
Rank results

Search performance depends heavily on indexes.

CREATE INDEX IF NOT EXISTS idx_products_search_vector
ON products
USING GIN (search_vector);

The GIN index allows PostgreSQL to efficiently search the tsvector column.

CREATE INDEX IF NOT EXISTS idx_products_title_trgm
ON products
USING GIN (LOWER(title) gin_trgm_ops);

This index supports trigram-based searches.

The main search endpoint follows this strategy:

                    Query
                      |
                      v
             Full-Text Search
                      |
              +-------+-------+
              |               |
            Found          Not Found
              |               |
              |               v
              |          Fuzzy Search
              |               |
              +-------+-------+
                      |
                      v
                    Rank
                      |
                      v
                   Filter
                      |
                      v
                    Sort
                      |
                      v
                  Paginate

This gives us the benefits of Full-Text Search while still handling common spelling mistakes.

The search endpoint supports:

?brand=Apple

?category=Smartphone

?minPrice=50000

?maxPrice=150000

Filters are applied to both:

Full-Text Search

and:

Fuzzy Search

This ensures that the fallback does not unexpectedly return products outside the user's requested filters.

The API supports:

RELEVANCE
PRICE_ASC
PRICE_DESC
RATING
NEWEST

GET /api/v1/products/search?q=iphone&sort=RELEVANCE

GET /api/v1/products/search?q=iphone&sort=PRICE_ASC

GET /api/v1/products/search?q=iphone&sort=PRICE_DESC

GET /api/v1/products/search?q=iphone&sort=RATING

GET /api/v1/products/search?q=iphone&sort=NEWEST

The API uses Spring Data Pageable.

Example:

GET /api/v1/products/search?q=iphone&page=0&size=10

Example response:

{
  "content": [
    {
      "id": 1,
      "title": "Apple iPhone 17 Pro"
    }
  ],
  "totalElements": 9,
  "totalPages": 1,
  "size": 10,
  "number": 0
}

Fuzzy fallback also supports pagination using:

LIMIT :limit
OFFSET :offset

The API supports lightweight search suggestions.

Example:

GET /api/v1/products/suggestions?q=iph

Possible response:

[
  "Apple iPhone 17 Pro",
  "Apple iPhone 17 Pro Max"
]

Suggestions are useful for implementing autocomplete functionality in a frontend application.

POST /api/v1/products

Request:

{
  "title": "Apple iPhone 17 Pro",
  "description": "Apple flagship smartphone",
  "brand": "Apple",
  "category": "Smartphone",
  "price": 129999
}

GET /api/v1/products

GET /api/v1/products/{id}

Example:

GET /api/v1/products/1

PUT /api/v1/products/{id}

Example:

{
  "title": "Apple iPhone 17 Pro",
  "description": "Updated description",
  "brand": "Apple",
  "category": "Smartphone",
  "price": 125000
}

DELETE /api/v1/products/{id}

GET /api/v1/products/search

Required parameter:

q

Optional parameters:

brand
category
minPrice
maxPrice
sort
page
size

Example:

GET /api/v1/products/search?q=iphone

Example with filters:

GET /api/v1/products/search?q=iphone&brand=Apple&category=Smartphone

Example with price range:

GET /api/v1/products/search?q=iphone&minPrice=50000&maxPrice=150000

Example with sorting and pagination:

GET /api/v1/products/search?q=iphone&sort=PRICE_ASC&page=0&size=10

GET /api/v1/products/fuzzy-search

Example:

GET /api/v1/products/fuzzy-search?q=iphne

Possible response:

[
  {
    "id": 1,
    "title": "Apple iPhone 17 Pro",
    "brand": "Apple",
    "category": "Smartphone",
    "price": 129999,
    "textScore": 0.5
  }
]

iphone

Expected matching products:

Apple iPhone 17 Pro
Apple iPhone 17 Pro Max

iphne

The Full-Text Search may not return results.

The API can then perform fuzzy matching:

iphne
  |
  v
pg_trgm
  |
  v
iPhone

The API validates search queries.

For example:

GET /api/v1/products/search?q=

returns:

{
  "status": 400,
  "error": "Bad Request",
  "message": "Search query must not be empty"
}

Minimum and maximum query lengths are controlled centrally through SearchConstants.

The API uses a global exception handler.

@RestControllerAdvice

The following cases are handled:

• Invalid search query
• Invalid price range
• Product not found
• Request validation errors
• Unexpected server errors

Example:

{
  "timestamp": "2026-08-19T20:00:00",
  "status": 404,
  "error": "Not Found",
  "message": "Product not found with id: 999",
  "path": "/api/v1/products/999"
}

Database schema changes are managed using Flyway.

Migration files are located under:

src/main/resources/db/migration/

Example:

V1__create_products.sql

Flyway automatically executes pending migrations during application startup.

Example application.yml:

spring:
  datasource:
    url: jdbc:postgresql://localhost:5432/fulltext_search
    username: ${DB_USERNAME:postgres}
    password: ${DB_PASSWORD:postgres}

  jpa:
    hibernate:
      ddl-auto: validate

  flyway:
    enabled: true

Use environment variables for real credentials.

Do not commit production passwords to GitHub.

Install:

• Java 21
• Maven
• PostgreSQL

Verify Java:

java -version

Verify Maven:

mvn -version

CREATE DATABASE fulltext_search;

CREATE EXTENSION IF NOT EXISTS pg_trgm;

Set:

DB_USERNAME
DB_PASSWORD

or configure your local application.yml.

mvn spring-boot:run

Application:

http://localhost:8080

Swagger UI:

http://localhost:8080/swagger-ui/index.html

OpenAPI specification:

http://localhost:8080/v3/api-docs

Swagger provides an interactive way to execute and test the APIs.

• Full-Text Search
• tsvector
• tsquery
• websearch_to_tsquery
• ts_rank
• pg_trgm
• word_similarity
• % trigram operator
• GIN indexes
• Native SQL
• EXPLAIN ANALYZE

• REST controllers
• Service layer
• Repository layer
• Spring Data JPA
• Native queries
• Interface projections
• Pageable
• DTOs
• Validation
• Global exception handling

• Search filters
• Sorting
• Pagination
• Search fallback
• Relevance ranking
• Error responses

The project uses PostgreSQL indexes specifically designed for the search workload.

Full-Text Search:

GIN(search_vector)

Fuzzy search:

GIN(LOWER(title) gin_trgm_ops)

Use:

EXPLAIN ANALYZE

to verify query plans and index usage.

Example:

EXPLAIN ANALYZE
SELECT
    id,
    title
FROM products
WHERE search_vector @@
      websearch_to_tsquery('english', 'iphone');

A simple search might use:

WHERE LOWER(title) LIKE '%iphone%'

However, Full-Text Search provides features such as:

• Tokenization
• Normalization
• Ranking
• Language-aware search
• Search operators
• GIN indexing

Therefore:

LIKE

is useful for simple substring matching, while:

Full-Text Search

is better suited for richer text-search requirements.

This project intentionally uses PostgreSQL.

• Existing database infrastructure
• No additional search cluster
• ACID transactions
• Simple deployment
• Native SQL
• GIN indexes
• Full-Text Search
• Trigram fuzzy search

• Search volume is extremely large
• Advanced search capabilities are required
• Distributed search is required
• Complex relevance models are required
• Search analytics are extensive

The appropriate solution depends on the application's scale and requirements.

A concise explanation of this project:

I implemented a product search API using PostgreSQL Full-Text Search. Product data is indexed into a tsvector column and queries are generated using websearch_to_tsquery(). Results are ranked using ts_rank() and a custom score that combines text relevance, product rating and review popularity. If Full-Text Search doesn't find results, the API falls back to PostgreSQL pg_trgm fuzzy search using word_similarity(). I also implemented filtering, sorting, pagination, GIN indexes, native queries and Swagger documentation.

To provide efficient text searching and ranking directly inside PostgreSQL without requiring a separate search engine.

A normalized representation of searchable text used by PostgreSQL Full-Text Search.

A PostgreSQL representation of a search query used to match against a tsvector.

It checks whether a tsvector matches a tsquery.

It calculates the relevance score of a matching document.

It provides search syntax that is more natural for user-entered search strings.

A PostgreSQL extension providing trigram-based similarity operations and indexes.

To find text that is similar even when the user makes spelling mistakes.

GIN indexes efficiently support PostgreSQL Full-Text Search and trigram-based searches.

PostgreSQL-specific features such as:

ts_rank()
websearch_to_tsquery()
word_similarity()

are database-specific and are easier to express using native SQL.

Search queries often return calculated values such as:

textScore
score
titleHighlight
descriptionHighlight

Interface projections allow the application to retrieve only the required columns instead of loading complete entities.

The API first performs Full-Text Search.

If there are no results, it executes trigram-based fuzzy search and returns similar products.

Potential future enhancements include:

• Search title + description + brand
• PostgreSQL dictionaries
• Synonym support
• Search highlighting
• Advanced autocomplete
• Search analytics
• Popular search tracking
• Redis caching
• Search result caching
• Elasticsearch/OpenSearch migration for very large workloads
• Advanced ranking models
• Multi-language search
• Search typo correction
• Search history

This project focuses primarily on PostgreSQL search concepts.

Authentication and authorization can be integrated using Spring Security/JWT when required.

✅ Product CRUD
✅ PostgreSQL database
✅ Flyway migration
✅ Full-Text Search
✅ tsvector
✅ tsquery
✅ websearch_to_tsquery()
✅ ts_rank()
✅ Custom relevance score
✅ Brand filtering
✅ Category filtering
✅ Price filtering
✅ Pagination
✅ Sorting
✅ Fuzzy search
✅ pg_trgm
✅ word_similarity()
✅ Search suggestions
✅ GIN indexes
✅ Validation
✅ Global exception handling
✅ Swagger/OpenAPI
✅ GitHub README

After completing this project, you should understand how to implement a production-style search API using PostgreSQL without immediately introducing a dedicated search engine.

The most important concepts demonstrated are:

PostgreSQL Full-Text Search
        +
Relevance Ranking
        +
Fuzzy Search
        +
Database Indexing
        +
Filtering
        +
Sorting
        +
Pagination
        +
Spring Boot REST API

Rahul Moundekar

Java Backend Developer

Technologies:

Java
Spring Boot
Microservices
REST APIs
PostgreSQL
JPA
Docker
Cloud

This project is intended for learning, portfolio and interview demonstration purposes.