El problema que el LIKE no resuelve

Tienes una pantalla con un grid de clientes. El usuario empieza a escribir “João Si” y espera ver “João Silva”, “João Silveira”, “João Simões”. Rápido. Mientras escribe.

La solución más inmediata es usar LIKE:

1
SELECT * FROM Clientes WHERE Nome LIKE '%João Si%'

Funciona. Para 100 registros. Para 500 mil registros en producción, con varios usuarios escribiendo al mismo tiempo, ese LIKE '%...%' es un full table scan — la base de datos lee cada fila de la tabla, una a una, sin usar índice. La query se vuelve lenta, la base sufre y el usuario abandona la pantalla.

El Full-Text Search (FTS) resuelve exactamente eso: un mecanismo de indexación y búsqueda textual especialmente diseñado para consultas sobre texto, con soporte para:

  • Búsqueda por palabras y frases exactas
  • Relevancia y ranking de resultados
  • Raíces de palabras (stemming): “corriendo” encuentra “correr”
  • Sinónimos y stopwords
  • Operadores lógicos: AND, OR, NOT, NEAR
  • Índices invertidos — la base no lee cada fila, lee el índice

En este artículo voy a implementar el escenario completo: un frontend Angular con búsqueda reactiva paginada que llama a una API REST C# que usa Full-Text Search en la base de datos — con ejemplos para SQL Server, PostgreSQL y Oracle.

📦 Código fuente: La implementación completa de este artículo está en el repositorio blog-zocateli-sample en GitHub. Consulta también los ejemplos Angular (search-ui) y los scripts SQL.


El escenario: Angular + API REST + Base de datos vía FTS

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
┌─────────────────────────────────────────────────────────────┐
│  Angular Grid (frontend)                                    │
│                                                             │
│  ┌─────────────────────────────────────────────┐            │
│  │     Escribe el nombre del cliente...        │            │
│  └─────────────────────────────────────────────┘            │
│       ↓ debounce 400ms + mínimo 3 caracteres                │
│  ┌──────────────────────────────────────────────────────┐   │
│  │  Nombre        | CPF         | Email                 │   │
│  │  João Silva    | 123.456.789 | [email protected]  │   │
│  │  João Silveira | 987.654.321 | [email protected]  │   │
│  └──────────────────────────────────────────────────────┘   │
│  Página 1 de 12  [< Anterior] [Siguiente >]                 │
└─────────────────────────────────────────────────────────────┘
              │ GET /api/clientes?q=João+Si&pagina=1&tamanho=20
              ▼
┌─────────────────────────────────────────────────────────────┐
│  ASP.NET Core API                                           │
│  ClientesController → ClienteService → IClienteRepository   │
└─────────────────────────────────────────────────────────────┘
              │ query Full-Text Search paginada
              ▼
┌──────────────┐   ┌──────────────┐   ┌──────────────┐
│  SQL Server  │   │  PostgreSQL  │   │    Oracle    │
│  CONTAINS()  │   │  to_tsquery  │   │  CONTAINS()  │
│  FT Index    │   │  GIN Index   │   │  CTX Domain  │
└──────────────┘   └──────────────┘   └──────────────┘

Frontend Angular: búsqueda reactiva con debounce

¿Por qué SPA?

El patrón de “búsqueda mientras se escribe” es nativo en frameworks SPA (Single-Page Applications) como Angular, React y Vue. En esos frameworks, toda la lógica de interacción se ejecuta en el navegador, y el modelo de programación reactiva — con RxJS en Angular — ofrece los operadores debounceTime, distinctUntilChanged y switchMap directamente, sin trucos.

En frameworks server-side (Razor Pages, Blazor Server, Thymeleaf, JSF…), el flujo de renderizado es diferente: cada interacción debe ir al servidor y devolver HTML actualizado. Para implementar el mismo comportamiento, dependes de JavaScript adicional (escrito a mano o vía HTMX, Turbo, Alpine.js, etc.) para capturar el evento de escritura, aplicar el debounce en el cliente y disparar la petición asíncrona — añadiendo una capa de complejidad que no existe en el modelo SPA. El resultado funciona, pero el desarrollo es más laborioso y el mantenimiento más fragmentado (lógica dividida entre el servidor y scripts JS sueltos).

Si tu proyecto ya usa un framework server-side, evalúa si vale la pena introducir una isla de interactividad SPA (Angular Element, React aislado) solo para este componente de búsqueda, o si un script JS ligero como HTMX es suficiente para el caso de uso.

Angular con RxJS hace elegante el patrón de “búsqueda mientras se escribe”. La idea es simple: no disparar una petición con cada tecla pulsada — eso sobrecargaría el servidor. Uso dos filtros:

  1. Mínimo de 3 caracteres — evitar búsquedas con texto muy corto
  2. Debounce de 400ms — esperar a que el usuario deje de escribir antes de enviar

Componente de búsqueda

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
// clientes-grid.component.ts
import { Component, OnInit, OnDestroy } from '@angular/core';
import { FormControl } from '@angular/forms';
import { Subject } from 'rxjs';
import {
  debounceTime,
  distinctUntilChanged,
  filter,
  switchMap,
  takeUntil
} from 'rxjs/operators';
import { ClienteService } from './cliente.service';
import { PagedResult, ClienteDto } from './models';

@Component({
  selector: 'app-clientes-grid',
  templateUrl: './clientes-grid.component.html'
})
export class ClientesGridComponent implements OnInit, OnDestroy {
  buscaControl = new FormControl('');
  clientes: ClienteDto[] = [];
  paginaAtual = 1;
  totalPaginas = 1;
  totalRegistros = 0;
  carregando = false;
  termoBusca = '';

  private destroy$ = new Subject<void>();

  constructor(private clienteService: ClienteService) {}

  ngOnInit(): void {
    this.buscaControl.valueChanges.pipe(
      debounceTime(400),                    // espera 400ms tras la última tecla
      distinctUntilChanged(),               // ignora si el valor no cambió
      filter(termo => !termo || termo.length === 0 || termo.length >= 3),
      takeUntil(this.destroy$)
    ).subscribe(termo => {
      this.termoBusca = termo ?? '';
      this.paginaAtual = 1;                 // vuelve a la primera página en cada nueva búsqueda
      this.buscar();
    });

    // Carga la primera página sin filtro al abrir la pantalla
    this.buscar();
  }

  buscar(): void {
    this.carregando = true;
    this.clienteService
      .listar(this.termoBusca, this.paginaAtual, 20)
      .pipe(takeUntil(this.destroy$))
      .subscribe({
        next: (resultado) => {
          this.clientes       = resultado.itens;
          this.totalPaginas   = resultado.totalPaginas;
          this.totalRegistros = resultado.totalRegistros;
          this.carregando     = false;
        },
        error: () => {
          this.carregando = false;
        }
      });
  }

  irParaPagina(pagina: number): void {
    if (pagina < 1 || pagina > this.totalPaginas) return;
    this.paginaAtual = pagina;
    this.buscar();
  }

  ngOnDestroy(): void {
    this.destroy$.next();
    this.destroy$.complete();
  }
}

Template HTML

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
<!-- clientes-grid.component.html -->
<div class="grid-container">
  <div class="search-bar">
    <input
      [formControl]="buscaControl"
      placeholder="🔍 Escribe el nombre del cliente..."
      class="input-busca"
    />
    <span *ngIf="carregando" class="spinner">Buscando...</span>
  </div>

  <table class="grid-tabela">
    <thead>
      <tr>
        <th>Nombre</th>
        <th>CPF/CNPJ</th>
        <th>Correo</th>
        <th>Ciudad</th>
      </tr>
    </thead>
    <tbody>
      <tr *ngFor="let cliente of clientes">
        <td>{{ cliente.nome }}</td>
        <td>{{ cliente.cpfCnpj }}</td>
        <td>{{ cliente.email }}</td>
        <td>{{ cliente.cidade }}</td>
      </tr>
      <tr *ngIf="!carregando && clientes.length === 0">
        <td colspan="4" class="sem-resultado">Ningún cliente encontrado.</td>
      </tr>
    </tbody>
  </table>

  <div class="paginacao">
    <span>{{ totalRegistros }} registros | Página {{ paginaAtual }} de {{ totalPaginas }}</span>
    <button (click)="irParaPagina(paginaAtual - 1)" [disabled]="paginaAtual <= 1">‹ Anterior</button>
    <button (click)="irParaPagina(paginaAtual + 1)" [disabled]="paginaAtual >= totalPaginas">Siguiente ›</button>
  </div>
</div>

Service Angular

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
// cliente.service.ts
import { Injectable } from '@angular/core';
import { HttpClient, HttpParams } from '@angular/common/http';
import { Observable } from 'rxjs';
import { PagedResult, ClienteDto } from './models';

@Injectable({ providedIn: 'root' })
export class ClienteService {
  private readonly apiUrl = '/api/clientes';

  constructor(private http: HttpClient) {}

  listar(
    q: string,
    pagina: number,
    tamanho: number
  ): Observable<PagedResult<ClienteDto>> {
    let params = new HttpParams()
      .set('pagina', pagina)
      .set('tamanho', tamanho);

    if (q?.trim()) {
      params = params.set('q', q.trim());
    }

    return this.http.get<PagedResult<ClienteDto>>(this.apiUrl, { params });
  }
}
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
// models.ts
export interface ClienteDto {
  id: number;
  nome: string;
  cpfCnpj: string;
  email: string;
  cidade: string;
}

export interface PagedResult<T> {
  itens: T[];
  totalRegistros: number;
  totalPaginas: number;
  paginaAtual: number;
  tamanhoPagina: number;
}

¿Por qué switchMap? El switchMap cancela la petición anterior cuando llega una nueva. Si el usuario escribe rápido y el servidor aún no respondió la primera petición, la segunda cancela la primera — sin riesgo de que los resultados lleguen desordenados.


API REST con ASP.NET Core

Controller

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
// ClientesController.cs
[ApiController]
[Route("api/clientes")]
public class ClientesController(IClienteService service) : ControllerBase
{
    [HttpGet]
    public async Task<ActionResult<PagedResult<ClienteDto>>> Listar(
        [FromQuery] string? q,
        [FromQuery] int pagina = 1,
        [FromQuery] int tamanho = 20,
        CancellationToken ct = default)
    {
        if (tamanho > 100) tamanho = 100;  // límite máximo por seguridad
        if (pagina < 1)    pagina = 1;

        var resultado = await service.ListarAsync(q, pagina, tamanho, ct);
        return Ok(resultado);
    }
}

Modelos y DTOs

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
// PagedResult.cs
public record PagedResult<T>(
    IReadOnlyList<T> Itens,
    int TotalRegistros,
    int TotalPaginas,
    int PaginaAtual,
    int TamanhoPagina);

// ClienteDto.cs
public record ClienteDto(
    int Id,
    string Nome,
    string CpfCnpj,
    string Email,
    string Cidade);

// Cliente.cs (entidad)
public class Cliente
{
    public int    Id        { get; set; }
    public string Nome      { get; set; } = "";
    public string CpfCnpj  { get; set; } = "";
    public string Email     { get; set; } = "";
    public string Cidade    { get; set; } = "";
}

Service

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
// ClienteService.cs
public class ClienteService(IClienteRepository repo) : IClienteService
{
    public async Task<PagedResult<ClienteDto>> ListarAsync(
        string? q, int pagina, int tamanho, CancellationToken ct)
    {
        var (itens, total) = await repo.BuscarAsync(q, pagina, tamanho, ct);

        return new PagedResult<ClienteDto>(
            Itens:         itens.Select(c => new ClienteDto(c.Id, c.Nome, c.CpfCnpj, c.Email, c.Cidade)).ToList(),
            TotalRegistros: total,
            TotalPaginas:   (int)Math.Ceiling((double)total / tamanho),
            PaginaAtual:    pagina,
            TamanhoPagina:  tamanho);
    }
}

Qué es el Full-Text Index en SQL Server

SQL Server mantiene un catálogo de texto completo — una estructura de índice invertido separada de los índices B-Tree normales. Tokeniza el texto de las columnas indexadas, aplica stopwords (palabras ignoradas: “de”, “do”, “a”…) y stemming (reductor de raíces) por idioma, y almacena los tokens con sus posiciones en los documentos.

La búsqueda usa los predicados CONTAINS y FREETEXT:

  • CONTAINS(coluna, '"João Silva"') — búsqueda literal o con prefijo
  • CONTAINS(coluna, '"João*"') — prefijo (João, Joãozinho, etc.)
  • FREETEXT(coluna, 'João Silva') — lenguaje natural, más tolerante

Requisito previo: habilitar el Full-Text Search en SQL Server

El Full-Text Search es un recurso opcional de SQL Server — debe estar instalado en la instancia antes de cualquier CREATE FULLTEXT INDEX. En instalaciones estándar de SQL Server Developer, Express with Advanced Services o Enterprise ya viene activo; en instalaciones mínimas puede estar ausente.

Verifica si está activo:

1
2
-- Devuelve 1 si el recurso está instalado, 0 si no lo está
SELECT FULLTEXTSERVICEPROPERTY('IsFullTextInstalled') AS FTInstalled;

Si devuelve 0, necesitas añadir la feature vía SQL Server Setup:

  1. Abre el SQL Server Installation Center (setup.exe del medio de instalación)
  2. Elige “Add features to an existing instance”
  3. Selecciona “Full-Text and Semantic Extractions for Search”
  4. Completa el asistente — no es necesario reiniciar la instancia

Además, el servicio auxiliar SQL Full-text Filter Daemon Launcher (MSSQLFDLauncher) debe estar en ejecución. Verifícalo en SQL Server Configuration Manager o vía PowerShell:

1
Get-Service -Name 'MSSQLFDLauncher*'

Referencia completa: Introducción al Full-Text Search — Microsoft Learn

Setup del Full-Text Index

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
-- 1. Crear el catálogo (una vez por base de datos)
CREATE FULLTEXT CATALOG catalogo_clientes AS DEFAULT;

-- 2. Crear el índice FT en la tabla Clientes
--    Requiere que la tabla tenga una columna clave única (generalmente la PK)
CREATE FULLTEXT INDEX ON Clientes
(
    Nome          LANGUAGE 'Brazilian',
    Email         LANGUAGE 'Brazilian',
    Cidade        LANGUAGE 'Brazilian'
)
KEY INDEX PK_Clientes
ON catalogo_clientes
WITH STOPLIST = SYSTEM;

-- 3. Verificar el progreso de la indexación (puede tardar la primera vez)
SELECT name, is_enabled, crawl_status_description
FROM sys.fulltext_indexes fi
JOIN sys.tables t ON fi.object_id = t.object_id;

Repositorio — SQL Server

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
// ClienteRepositorioSqlServer.cs
public class ClienteRepositorioSqlServer(AppDbContext db) : IClienteRepository
{
    public async Task<(IReadOnlyList<Cliente> itens, int total)> BuscarAsync(
        string? q, int pagina, int tamanho, CancellationToken ct)
    {
        // Sin filtro: paginación simple
        if (string.IsNullOrWhiteSpace(q))
            return await BuscarSemFiltroAsync(pagina, tamanho, ct);

        var termo = FormatarTermoSqlServer(q);
        var offset = (pagina - 1) * tamanho;

        // CONTAINS con prefijo — soporta búsqueda parcial al inicio de las palabras
        var sql = """
            SELECT Id, Nome, CpfCnpj, Email, Cidade
            FROM Clientes
            WHERE CONTAINS((Nome, Email, Cidade), {0})
            ORDER BY Nome
            OFFSET {1} ROWS FETCH NEXT {2} ROWS ONLY
            """;

        var countSql = """
            SELECT COUNT(*)
            FROM Clientes
            WHERE CONTAINS((Nome, Email, Cidade), {0})
            """;

        var itens = await db.Clientes
            .FromSqlRaw(sql, termo, offset, tamanho)
            .AsNoTracking()
            .ToListAsync(ct);

        var total = await db.Database
            .SqlQueryRaw<int>(countSql, termo)
            .FirstAsync(ct);

        return (itens, total);
    }

    private static string FormatarTermoSqlServer(string q)
    {
        // Divide en palabras y usa el prefijo "*" para búsqueda parcial:
        // "João Si" → '"João*" AND "Si*"'
        var palavras = q.Trim()
            .Split(' ', StringSplitOptions.RemoveEmptyEntries)
            .Select(p => $'"{EscaparSqlServer(p)}*"');

        return string.Join(" AND ", palavras);
    }

    private static string EscaparSqlServer(string palavra)
        // Previene la inyección en el predicado CONTAINS
        => palavra.Replace("\"", "\"\"").Replace("'", "''");

    private async Task<(IReadOnlyList<Cliente>, int)> BuscarSemFiltroAsync(
        int pagina, int tamanho, CancellationToken ct)
    {
        var offset = (pagina - 1) * tamanho;
        var itens = await db.Clientes
            .AsNoTracking()
            .OrderBy(c => c.Nome)
            .Skip(offset)
            .Take(tamanho)
            .ToListAsync(ct);

        var total = await db.Clientes.CountAsync(ct);
        return (itens, total);
    }
}

Ejemplo de resultado

Para el término “João Si”, SQL Server ejecuta internamente:

1
2
3
4
5
6
7
8
9
-- Equivalente a lo que ejecuta el FT engine:
SELECT Id, Nome, CpfCnpj, Email, Cidade
FROM Clientes
WHERE CONTAINS((Nome, Email, Cidade), '"João*" AND "Si*"')
ORDER BY Nome
OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY;

-- Devuelve: João Silva, João Silveira, João Simões, João Simplício...
-- Usando el índice invertido — no hace full table scan

Ranking de relevancia con SQL Server

Cuando el orden por relevancia importa, usa CONTAINSTABLE o FREETEXTTABLE, que devuelven una columna RANK:

1
2
3
4
5
6
7
8
var rankSql = """
    SELECT c.Id, c.Nome, c.CpfCnpj, c.Email, c.Cidade
    FROM Clientes c
    INNER JOIN CONTAINSTABLE(Clientes, (Nome, Email, Cidade), {0}) AS ft
        ON c.Id = ft.[KEY]
    ORDER BY ft.[RANK] DESC, c.Nome
    OFFSET {1} ROWS FETCH NEXT {2} ROWS ONLY
    """;

PostgreSQL: Full-Text Search con tsvector

Cómo funciona el FTS en PostgreSQL

PostgreSQL tiene soporte nativo a FTS sin extensiones adicionales. El enfoque usa:

  • tsvector — representación normalizada del texto indexado (tokens + posiciones)
  • tsquery — expresión de búsqueda compilada
  • to_tsvector(idioma, texto) — convierte texto a tsvector
  • to_tsquery(idioma, query) — convierte una búsqueda a tsquery
  • websearch_to_tsquery — más tolerante, acepta la entrada del usuario directamente
  • Índice GIN — índice invertido eficiente para tsvector

Requisito previo: habilitar FTS en PostgreSQL

PostgreSQL incluye soporte nativo a Full-Text Search desde la versión 8.3 — no hay instalación de componente adicional ni licencia separada. El recurso está disponible en cualquier instalación estándar, incluso RDS, Azure Database for PostgreSQL y Cloud SQL.

Verifica qué configuraciones de idioma están disponibles:

1
2
3
4
5
-- Lista los diccionarios de búsqueda textual instalados
SELECT cfgname, cfgparser::regproc
FROM pg_ts_config
ORDER BY cfgname;
-- Debe contener 'portuguese' para uso en portugués

Para texto en portugués con acentos, se recomienda habilitar la extensión unaccent, que normaliza los caracteres acentuados durante la indexación (“João” y “Joao” pasan a ser equivalentes):

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
-- Extensión incluida en PostgreSQL contrib — sin dependencia externa
CREATE EXTENSION IF NOT EXISTS unaccent;

-- Variante del diccionario portuguese que ignora los acentos:
CREATE TEXT SEARCH CONFIGURATION portuguese_unaccent (COPY = portuguese);
ALTER TEXT SEARCH CONFIGURATION portuguese_unaccent
  ALTER MAPPING FOR hword, hword_part, word
  WITH unaccent, portuguese_stem;

-- Usa 'portuguese_unaccent' en lugar de 'portuguese' en las funciones to_tsvector / to_tsquery

Referencia completa: Full-Text Search — PostgreSQL Docs

Setup del índice FTS

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
-- 1. Añadir una columna tsvector para indexación (alternativa: calcular on-the-fly)
ALTER TABLE clientes ADD COLUMN busca_fts TSVECTOR;

-- 2. Poblar la columna con los textos concatenados (pesos diferentes por campo)
UPDATE clientes SET busca_fts =
    setweight(to_tsvector('portuguese', coalesce(nome, '')),   'A') ||
    setweight(to_tsvector('portuguese', coalesce(email, '')),  'B') ||
    setweight(to_tsvector('portuguese', coalesce(cidade, '')), 'C');

-- 3. Mantener la columna actualizada automáticamente vía trigger
CREATE OR REPLACE FUNCTION atualizar_busca_fts()
RETURNS TRIGGER AS $$
BEGIN
    NEW.busca_fts :=
        setweight(to_tsvector('portuguese', coalesce(NEW.nome,   '')), 'A') ||
        setweight(to_tsvector('portuguese', coalesce(NEW.email,  '')), 'B') ||
        setweight(to_tsvector('portuguese', coalesce(NEW.cidade, '')), 'C');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER tg_clientes_busca_fts
BEFORE INSERT OR UPDATE ON clientes
FOR EACH ROW EXECUTE FUNCTION atualizar_busca_fts();

-- 4. Crear un índice GIN (optimizado para tsvector)
CREATE INDEX idx_clientes_busca_fts ON clientes USING GIN (busca_fts);

setweight y los pesos A, B, C, D: permiten dar prioridad a los campos. Los resultados donde el término aparece en el nombre (A) reciben un ranking más alto que donde aparece en la ciudad (C). El ranking se calcula con la función ts_rank.

Repositorio — PostgreSQL

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
// ClienteRepositorioPostgres.cs
public class ClienteRepositorioPostgres(AppDbContext db) : IClienteRepository
{
    public async Task<(IReadOnlyList<Cliente> itens, int total)> BuscarAsync(
        string? q, int pagina, int tamanho, CancellationToken ct)
    {
        if (string.IsNullOrWhiteSpace(q))
            return await BuscarSemFiltroAsync(pagina, tamanho, ct);

        var offset = (pagina - 1) * tamanho;

        // websearch_to_tsquery: acepta entrada humana directamente — más seguro
        // Convierte "João Si" en 'João:* & Si:*' internamente
        // La cláusula :* indica prefijo (búsqueda parcial)
        var sql = """
            SELECT id, nome, cpf_cnpj, email, cidade
            FROM clientes
            WHERE busca_fts @@ websearch_to_tsquery('portuguese', {0})
            ORDER BY ts_rank(busca_fts, websearch_to_tsquery('portuguese', {0})) DESC,
                     nome
            LIMIT {1} OFFSET {2}
            """;

        var countSql = """
            SELECT COUNT(*)
            FROM clientes
            WHERE busca_fts @@ websearch_to_tsquery('portuguese', {0})
            """;

        var itens = await db.Clientes
            .FromSqlRaw(sql, q.Trim(), tamanho, offset)
            .AsNoTracking()
            .ToListAsync(ct);

        var total = await db.Database
            .SqlQueryRaw<int>(countSql, q.Trim())
            .FirstAsync(ct);

        return (itens, total);
    }

    private async Task<(IReadOnlyList<Cliente>, int)> BuscarSemFiltroAsync(
        int pagina, int tamanho, CancellationToken ct)
    {
        var offset = (pagina - 1) * tamanho;
        var itens = await db.Clientes
            .AsNoTracking()
            .OrderBy(c => c.Nome)
            .Skip(offset)
            .Take(tamanho)
            .ToListAsync(ct);

        var total = await db.Clientes.CountAsync(ct);
        return (itens, total);
    }
}

Búsqueda con prefijo en PostgreSQL

websearch_to_tsquery no soporta prefijos de forma nativa. Para la búsqueda mientras se escribe, donde cada palabra debe tratarse como prefijo, usa to_tsquery con :*:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
private static string FormatarTermoPostgres(string q)
{
    // "João Si" → "'João':* & 'Si':*"
    var palavras = q.Trim()
        .Split(' ', StringSplitOptions.RemoveEmptyEntries)
        .Select(p => $"'{EscaparPostgres(p)}':*");

    return string.Join(" & ", palavras);
}

private static string EscaparPostgres(string p)
    => p.Replace("'", "''").Replace("\\", "\\\\");
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
// Uso con to_tsquery para prefijo:
var sql = """
    SELECT id, nome, cpf_cnpj, email, cidade
    FROM clientes
    WHERE busca_fts @@ to_tsquery('portuguese', {0})
    ORDER BY ts_rank(busca_fts, to_tsquery('portuguese', {0})) DESC, nome
    LIMIT {1} OFFSET {2}
    """;

// Pasa la query formateada:
var termo = FormatarTermoPostgres(q);

Alternativa: FTS vía LINQ con EF.Functions (Npgsql)

El provider Npgsql para PostgreSQL ofrece soporte nativo a Full-Text Search directamente en LINQ, sin necesidad de FromSqlRaw. Para queries más simples — sin ranking personalizado ni prefijo — este enfoque es más limpio y mantiene el tipado fuerte:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
// Búsqueda FTS vía LINQ puro — sin SQL raw
// Requiere: Npgsql.EntityFrameworkCore.PostgreSQL
var itens = await db.Clientes
    .Where(c => EF.Functions.ToTsVector("portuguese", c.Nome + " " + c.Email + " " + c.Cidade)
                    .Matches(EF.Functions.WebSearchToTsQuery("portuguese", q)))
    .OrderBy(c => c.Nome)
    .Skip(offset)
    .Take(tamanho)
    .AsNoTracking()
    .ToListAsync(ct);

⚠️ Atención: El enfoque vía LINQ con EF.Functions funciona bien para búsquedas simples, pero no soporta prefijo (:* / búsqueda mientras se escribe) y ofrece menos control sobre el ranking. Para búsqueda reactiva con autocompletado — como el escenario de este artículo — FromSqlRaw con to_tsquery y :* sigue siendo la mejor opción. Para entender cómo EF Core abstrae el acceso a datos y cuándo usar LINQ vs SQL raw, consulta EF Core 8 Fluent API: Mapeo, ORM y Desacoplamiento.


Oracle: Full-Text Search con Oracle Text (CTX)

Cómo funciona Oracle Text

Oracle usa Oracle Text (también llamado Oracle Context o CTX) para Full-Text Search. Los índices se crean con CTXSYS.CONTEXT y las búsquedas usan el predicado CONTAINS(), que devuelve un score de relevancia:

  • CONTAINS(coluna, 'João') — búsqueda simple
  • CONTAINS(coluna, 'João%') — prefijo (wildcard derecho)
  • CONTAINS(coluna, 'João AND Silva') — operador AND
  • CONTAINS(coluna, 'FUZZY(Joao, 60, 6, N)') — búsqueda fuzzy (tolera errores ortográficos)

Requisito previo: habilitar Oracle Text

Oracle Text (antes llamado Oracle ConText Cartridge o InterMedia Text) está incluido en todas las ediciones de Oracle Database — Standard Edition 2, Enterprise Edition y Oracle Database Free (XE). Debe estar instalado en el schema CTXSYS y el usuario de la aplicación debe tener los privilegios correctos.

Verifica si el componente está instalado:

1
2
3
4
5
-- Requiere acceso a DBA_REGISTRY (DBA o SELECT_CATALOG_ROLE)
SELECT comp_name, version, status
FROM   dba_registry
WHERE  comp_name = 'Oracle Text';
-- STATUS debe ser 'VALID'; si está ausente, el componente no está instalado

Si no está instalado, ejecuta como SYS AS SYSDBA:

1
2
3
4
5
6
7
8
9
-- Instala Oracle Text en la base de datos (se ejecuta una sola vez)
-- ctxsys = usuario dueño de Oracle Text
-- SYSAUX  = tablespace por defecto para el schema CTXSYS
-- TEMP    = tablespace temporal
-- NOLOCK  = no bloquea la cuenta CTXSYS tras la instalación
@?/ctx/admin/catctx.sql ctxsys SYSAUX TEMP NOLOCK

-- A continuación, compila los objetos inválidos (si es necesario)
@?/rdbms/admin/utlrp.sql

Concede los privilegios necesarios al usuario de la aplicación:

1
2
3
4
5
6
-- Opción 1: role CTXAPP (cubre CREATE INDEX, CTX_DDL, CTX_QUERY)
GRANT CTXAPP TO meu_usuario;

-- Opción 2: privilegios granulares (producción con principio de menor privilegio)
GRANT EXECUTE ON CTXSYS.CTX_DDL   TO meu_usuario;
GRANT EXECUTE ON CTXSYS.CTX_QUERY TO meu_usuario;

Verifica que el servicio de indexación de Oracle Text esté activo:

1
2
-- El proceso extproc / extjob es necesario para el sincronizador de índices
SELECT program, status FROM v$process WHERE program LIKE '%extproc%';

Referencia completa: Oracle Text Application Developer’s Guide · Oracle Text Reference

Setup del índice CTX

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- 1. Crear preferencias de idioma (una vez)
BEGIN
  CTX_DDL.CREATE_PREFERENCE('pref_clientes', 'BASIC_LEXER');
  CTX_DDL.SET_ATTRIBUTE('pref_clientes', 'BASE_LETTER', 'YES');  -- ignora acentos
  CTX_DDL.SET_ATTRIBUTE('pref_clientes', 'MIXED_CASE', 'NO');    -- case insensitive

  CTX_DDL.CREATE_PREFERENCE('wordlist_clientes', 'BASIC_WORDLIST');
  CTX_DDL.SET_ATTRIBUTE('wordlist_clientes', 'PREFIX_INDEX', 'YES');  -- búsqueda por prefijo
  CTX_DDL.SET_ATTRIBUTE('wordlist_clientes', 'PREFIX_MIN_LENGTH', '2');
  CTX_DDL.SET_ATTRIBUTE('wordlist_clientes', 'PREFIX_MAX_LENGTH', '10');
END;
/

-- 2. Crear un índice CONTEXT en la columna Nome
--    Para múltiples columnas, crea una columna calculada o usa MULTI_COLUMN_DATASTORE
CREATE INDEX idx_clientes_fts ON clientes(nome)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS ('LEXER pref_clientes WORDLIST wordlist_clientes');

-- 3. Sincronizar el índice tras inserciones/updates (puede programarse)
EXEC CTX_DDL.SYNC_INDEX('idx_clientes_fts');

-- Para autosincronización (Oracle 12c+):
-- PARAMETERS ('... SYNC (ON COMMIT)')

Repositorio — Oracle

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
// ClienteRepositorioOracle.cs
public class ClienteRepositorioOracle(AppDbContext db) : IClienteRepository
{
    public async Task<(IReadOnlyList<Cliente> itens, int total)> BuscarAsync(
        string? q, int pagina, int tamanho, CancellationToken ct)
    {
        if (string.IsNullOrWhiteSpace(q))
            return await BuscarSemFiltroAsync(pagina, tamanho, ct);

        var termo = FormatarTermoOracle(q);
        var offset = (pagina - 1) * tamanho;

        // CONTAINS devuelve un score 0-100; > 0 significa que encontró
        // SCORE(1) es el score de la primera llamada CONTAINS en la query
        var sql = $"""
            SELECT Id, Nome, CpfCnpj, Email, Cidade
            FROM (
                SELECT c.Id, c.Nome, c.CpfCnpj, c.Email, c.Cidade,
                       SCORE(1) AS relevancia,
                       ROW_NUMBER() OVER (ORDER BY SCORE(1) DESC, c.Nome) AS rn
                FROM Clientes c
                WHERE CONTAINS(c.Nome, :termo, 1) > 0
            )
            WHERE rn > :offset AND rn <= :limite
            ORDER BY rn
            """;

        var countSql = """
            SELECT COUNT(*)
            FROM Clientes
            WHERE CONTAINS(Nome, :termo, 1) > 0
            """;

        var itens = await db.Clientes
            .FromSqlRaw(sql, new OracleParameter("termo", termo),
                             new OracleParameter("offset", offset),
                             new OracleParameter("limite", offset + tamanho))
            .AsNoTracking()
            .ToListAsync(ct);

        var total = await db.Database
            .SqlQueryRaw<int>(countSql, new OracleParameter("termo", termo))
            .FirstAsync(ct);

        return (itens, total);
    }

    private static string FormatarTermoOracle(string q)
    {
        // "João Si" → "João% AND Si%"
        // El % en Oracle Text es un wildcard de prefijo (similar a LIKE 'Si%')
        var palavras = q.Trim()
            .Split(' ', StringSplitOptions.RemoveEmptyEntries)
            .Select(p => $"{EscaparOracle(p)}%");

        return string.Join(" AND ", palavras);
    }

    private static string EscaparOracle(string p)
        => p.Replace("'", "''").Replace("%", "\\%").Replace("_", "\\_");

    private async Task<(IReadOnlyList<Cliente>, int)> BuscarSemFiltroAsync(
        int pagina, int tamanho, CancellationToken ct)
    {
        var offset = (pagina - 1) * tamanho;
        var itens = await db.Clientes
            .AsNoTracking()
            .OrderBy(c => c.Nome)
            .Skip(offset)
            .Take(tamanho)
            .ToListAsync(ct);

        var total = await db.Clientes.CountAsync(ct);
        return (itens, total);
    }
}

Registrando los repositorios por base de datos

En Program.cs, selecciona el repositorio según la base de datos configurada:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
// Program.cs
var banco = builder.Configuration["DatabaseProvider"]; // "sqlserver" | "postgres" | "oracle"

builder.Services.AddScoped<IClienteService, ClienteService>();

builder.Services.AddScoped<IClienteRepository>(sp =>
{
    var db = sp.GetRequiredService<AppDbContext>();
    return banco switch
    {
        "postgres" => new ClienteRepositorioPostgres(db),
        "oracle"   => new ClienteRepositorioOracle(db),
        _          => new ClienteRepositorioSqlServer(db)   // default: SQL Server
    };
});

// DbContext — selecciona el provider
builder.Services.AddDbContext<AppDbContext>(opt =>
{
    if (banco == "postgres")
        opt.UseNpgsql(builder.Configuration.GetConnectionString("Postgres"));
    else if (banco == "oracle")
        opt.UseOracle(builder.Configuration.GetConnectionString("Oracle"));
    else
        opt.UseSqlServer(builder.Configuration.GetConnectionString("SqlServer"));
});

Comparativa entre las tres bases de datos

CaracterísticaSQL ServerPostgreSQLOracle
MecanismoFull-Text Catalog + FT Indextsvector + GIN IndexOracle Text (CTX)
Predicado de búsquedaCONTAINS(), FREETEXT()@@ (operador tsvector/tsquery)CONTAINS()
Búsqueda por prefijo"termo*"'termo':*termo%
Ranking nativoCONTAINSTABLE RANKts_rank()SCORE()
StemmingPor idioma (LANGUAGE)Por idioma (dictionary)Por lexer
AcentuaciónDepende de la collationExtensión unaccentBASE_LETTER = YES
Actualización del índiceAutomática (background)Trigger en la columnaManual/SYNC u ON COMMIT
SetupCREATE FULLTEXT INDEXCREATE INDEX ... USING GINCTX_DDL.CREATE_PREFERENCE + CREATE INDEX INDEXTYPE CTXSYS.CONTEXT
Licencia necesariaIncluida en SQL ServerNativa, sin costo extraOracle Text (incluido)

Buenas Prácticas y Precauciones

1. Protégete contra la inyección en el predicado FTS

Los predicados CONTAINS y to_tsquery no son inmunes a la inyección — tienen su propia sintaxis, que puede manipularse. Siempre:

  • Usa parámetros ({0}, :param) cuando el provider lo soporte
  • Escapa los caracteres especiales antes de interpolar strings
  • Limita el tamaño máximo del término de búsqueda
1
2
3
4
5
6
// Validación en el controller — rechaza términos muy largos o con caracteres de control
if (q is { Length: > 100 })
    return BadRequest("Término de búsqueda demasiado largo.");

if (q?.Any(c => char.IsControl(c)) == true)
    return BadRequest("Término de búsqueda inválido.");

2. Latencia del índice en Oracle

Oracle Text no indexa registros nuevos automáticamente por defecto. Configura la sincronización:

1
2
3
4
5
6
7
-- Opcional en el CREATE INDEX: actualiza el índice en cada commit
CREATE INDEX idx_clientes_fts ON clientes(nome)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS ('... SYNC (ON COMMIT)');

-- O prográmalo vía DBMS_JOB / DBMS_SCHEDULER para sync periódico:
EXEC CTX_DDL.SYNC_INDEX('idx_clientes_fts', '2M');  -- 2 minutos de ventana de memoria

3. Stopwords y términos cortos

FTS ignora las stopwords (“de”, “do”, “da”, “e”, “o”, “a”…). Una búsqueda de solo una stopword devuelve cero resultados, lo que puede confundir al usuario. Trátalo:

1
2
3
4
// En el service: si el resultado es cero y el término solo tiene stopwords,
// devuelve un mensaje específico o recurre a LIKE
if (total == 0 && TermoContemApenasStopwords(q))
    return await ListarSemFiltroAsync(pagina, tamanho, ct);

4. Caché de resultados frecuentes

Las búsquedas con términos populares (“José”, “Maria”) pueden cachearse en Redis con un TTL corto:

1
2
3
4
5
6
7
var cacheKey = $"clientes:fts:{q}:p{pagina}:t{tamanho}";
var cached = await cache.GetAsync<PagedResult<ClienteDto>>(cacheKey);
if (cached is not null) return cached;

var resultado = await repo.BuscarAsync(q, pagina, tamanho, ct);
await cache.SetAsync(cacheKey, resultado, TimeSpan.FromSeconds(30));
return resultado;

5. Mínimo de caracteres — también en la API

Incluso con el filtro en Angular, la API debe validar:

1
2
if (q is { Length: > 0 and < 3 })
    return BadRequest("El término de búsqueda debe tener al menos 3 caracteres.");

Conclusión

El Full-Text Search no es una funcionalidad de nicho — es el camino correcto para cualquier búsqueda textual en tablas con volumen relevante. La diferencia entre un LIKE '%termo%' y un CONTAINS() o @@ puede ser la diferencia entre una query de 8 segundos y una de 40 milisegundos.

El flujo completo que presenté:

  1. Angular con debounceTime y distinctUntilChanged — evita una avalancha de peticiones
  2. Mínimo de 3 caracteres — tanto en el frontend como en la API
  3. ASP.NET Core con CancellationToken — cancela consultas si el cliente se desconecta
  4. Full-Text Index en la base de datos — índice invertido, no full scan
  5. Búsqueda por prefijo ("termo*" / 'termo':* / termo%) — comportamiento natural de “búsqueda mientras se escribe”
  6. Paginación — porque incluso FTS con miles de resultados necesita paginación

El patrón se repite independientemente de la base de datos: crea el índice invertido específico de la base, usa el predicado correcto y pasa el término formateado. EF Core con FromSqlRaw es el punto de integración — lo bastante flexible para usar el recurso nativo de cada base sin renunciar al tipado fuerte de C#.


Lee También


Referencias

SQL Server

PostgreSQL

Oracle

EF Core y Angular