npm init -y
npm install express pg
npm install -D typescript @types/express @types/pg tsx

-- Create the articles table
CREATE TABLE articles (
  id SERIAL PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  body TEXT NOT NULL,
  author VARCHAR(100) NOT NULL,
  category VARCHAR(50),
  published_at TIMESTAMP DEFAULT NOW(),
  created_at TIMESTAMP DEFAULT NOW()
);
 
-- Add a generated tsvector column with weighted fields
ALTER TABLE articles
ADD COLUMN search_vector tsvector
GENERATED ALWAYS AS (
  setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
  setweight(to_tsvector('english', coalesce(body, '')), 'D')
) STORED;
 
-- Create a GIN index on the search vector
CREATE INDEX idx_articles_search ON articles USING GIN(search_vector);

-- tsvector normalizes text into sorted lexemes with positions
SELECT to_tsvector('english', 'The quick brown foxes jumped over lazy dogs');
-- Result: 'brown':3 'dog':9 'fox':4 'jump':5 'lazi':8 'quick':2
 
-- tsquery creates a search expression
SELECT to_tsquery('english', 'quick & fox');
-- Result: 'quick' & 'fox'
 
-- plainto_tsquery is simpler - treats spaces as AND
SELECT plainto_tsquery('english', 'quick fox');
-- Result: 'quick' & 'fox'
 
-- websearch_to_tsquery supports Google-like syntax
SELECT websearch_to_tsquery('english', '"quick fox" OR lazy -brown');
-- Result: 'quick' <-> 'fox' | 'lazi' & !'brown'

INSERT INTO articles (title, body, author, category) VALUES
('Getting Started with PostgreSQL', 'PostgreSQL is a powerful open-source relational database. This guide covers installation, basic configuration, and your first queries. We explore data types, table creation, and essential SQL commands for beginners.', 'Sadam Hussain', 'database'),
('Advanced PostgreSQL Performance Tuning', 'Learn how to optimize PostgreSQL for high-traffic applications. Topics include query planning with EXPLAIN ANALYZE, index strategies, connection pooling with PgBouncer, and vacuum configuration for large tables.', 'Sadam Hussain', 'database'),
('Building REST APIs with Node.js and Express', 'A comprehensive guide to building production-ready REST APIs. Covers routing, middleware, error handling, validation with Zod, and connecting to PostgreSQL using the pg library.', 'Sadam Hussain', 'backend'),
('React Server Components Explained', 'Server Components change how we think about React architecture. They run on the server, have zero bundle size impact, and can directly access databases and file systems without API routes.', 'Sadam Hussain', 'frontend'),
('Docker for Full-Stack Developers', 'Containerize your entire stack with Docker. This tutorial walks through Dockerfiles, multi-stage builds, docker-compose for local development, and deploying containers to production.', 'Sadam Hussain', 'devops');

-- Basic search
SELECT title, ts_rank(search_vector, query) AS rank
FROM articles, plainto_tsquery('english', 'postgresql performance') AS query
WHERE search_vector @@ query
ORDER BY rank DESC;
 
-- Search with headline snippets (highlighted matches)
SELECT
  title,
  ts_rank(search_vector, query) AS rank,
  ts_headline('english', body, query,
    'StartSel=<mark>, StopSel=</mark>, MaxWords=35, MinWords=15'
  ) AS snippet
FROM articles, websearch_to_tsquery('english', 'postgresql') AS query
WHERE search_vector @@ query
ORDER BY rank DESC;
 
-- Boosted ranking with normalization
SELECT
  title,
  ts_rank_cd(search_vector, query, 32) AS rank
FROM articles, plainto_tsquery('english', 'react server') AS query
WHERE search_vector @@ query
ORDER BY rank DESC;

-- Enable the extension
CREATE EXTENSION IF NOT EXISTS pg_trgm;
 
-- Create trigram indexes
CREATE INDEX idx_articles_title_trgm ON articles USING GIN(title gin_trgm_ops);
 
-- Similarity search (handles typos)
SELECT title, similarity(title, 'postgre perfomance') AS sim
FROM articles
WHERE title % 'postgre perfomance'
ORDER BY sim DESC;
 
-- Combine full-text search with fuzzy fallback
SELECT title, rank, sim FROM (
  -- Primary: full-text search
  SELECT title,
    ts_rank(search_vector, query) AS rank,
    1.0 AS sim,
    1 AS source
  FROM articles, plainto_tsquery('english', 'postgresql') AS query
  WHERE search_vector @@ query
 
  UNION ALL
 
  -- Fallback: trigram similarity (only if full-text returns nothing)
  SELECT title,
    0 AS rank,
    similarity(title, 'postgresql') AS sim,
    2 AS source
  FROM articles
  WHERE title % 'postgresql'
) combined
ORDER BY source, rank DESC, sim DESC
LIMIT 10;

// src/server.ts
import express, { Request, Response } from "express";
import { Pool } from "pg";
 
const app = express();
app.use(express.json());
 
const pool = new Pool({
  host: "localhost",
  port: 5432,
  database: "searchdb",
  user: "postgres",
  password: "password",
});
 
interface SearchQuery {
  q: string;
  category?: string;
  page?: string;
  limit?: string;
}
 
app.get("/api/search", async (req: Request<{}, {}, {}, SearchQuery>, res: Response) => {
  const { q, category, page = "1", limit = "10" } = req.query;
 
  if (!q || q.trim().length === 0) {
    return res.status(400).json({ error: "Search query is required" });
  }
 
  const pageNum = Math.max(1, parseInt(page));
  const limitNum = Math.min(50, Math.max(1, parseInt(limit)));
  const offset = (pageNum - 1) * limitNum;
 
  try {
    const params: (string | number)[] = [q, limitNum, offset];
    let categoryFilter = "";
 
    if (category) {
      categoryFilter = "AND a.category = $4";
      params.push(category);
    }
 
    const result = await pool.query(
      `
      WITH search_results AS (
        SELECT
          a.id,
          a.title,
          a.author,
          a.category,
          a.published_at,
          ts_rank(a.search_vector, websearch_to_tsquery('english', $1)) AS rank,
          ts_headline('english', a.body, websearch_to_tsquery('english', $1),
            'StartSel=<mark>, StopSel=</mark>, MaxWords=35, MinWords=15'
          ) AS snippet
        FROM articles a
        WHERE a.search_vector @@ websearch_to_tsquery('english', $1)
        ${categoryFilter}
      )
      SELECT
        *,
        COUNT(*) OVER() AS total_count
      FROM search_results
      ORDER BY rank DESC
      LIMIT $2 OFFSET $3
      `,
      params
    );
 
    const totalCount = result.rows[0]?.total_count ?? 0;
 
    res.json({
      query: q,
      results: result.rows.map(({ total_count, ...row }) => row),
      pagination: {
        page: pageNum,
        limit: limitNum,
        total: parseInt(totalCount),
        pages: Math.ceil(parseInt(totalCount) / limitNum),
      },
    });
  } catch (error) {
    console.error("Search error:", error);
    res.status(500).json({ error: "Search failed" });
  }
});
 
app.get("/api/suggest", async (req: Request<{}, {}, {}, { q: string }>, res: Response) => {
  const { q } = req.query;
 
  if (!q || q.length < 2) {
    return res.json({ suggestions: [] });
  }
 
  try {
    const result = await pool.query(
      `
      SELECT DISTINCT title,
        similarity(title, $1) AS sim
      FROM articles
      WHERE title % $1
      ORDER BY sim DESC
      LIMIT 5
      `,
      [q]
    );
 
    res.json({
      suggestions: result.rows.map((row) => ({
        title: row.title,
        relevance: parseFloat(row.sim.toFixed(3)),
      })),
    });
  } catch (error) {
    console.error("Suggest error:", error);
    res.status(500).json({ error: "Suggestion failed" });
  }
});
 
const PORT = process.env.PORT || 3001;
app.listen(PORT, () => {
  console.log(`Search API running on port ${PORT}`);
});

-- Partial index for only published articles
CREATE INDEX idx_published_search ON articles USING GIN(search_vector)
WHERE published_at IS NOT NULL;
 
-- Composite index for filtered searches
CREATE INDEX idx_category_search ON articles USING GIN(search_vector)
WHERE category IS NOT NULL;
 
-- Monitor index usage
SELECT
  indexrelname AS index_name,
  idx_scan AS times_used,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY idx_scan DESC;
 
-- Analyze query performance
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT title, ts_rank(search_vector, query) AS rank
FROM articles, plainto_tsquery('english', 'postgresql') AS query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 10;