How We Accelerate Customer Support with an Online Knowledge Base

Imagine your site has hundreds of support articles, customers can't find answers, and support tickets keep duplicating. A help center with proper search and a Q&A section solves this pain — but only if implemented correctly, considering performance and usability. We've been doing such projects for years: designed and deployed documentation portals for dozens of companies — from startups to enterprise. One of the key issues is search speed: users won't wait more than a second. The solution is a full-text index on the database side.

Problems We Solve

The first problem is N+1 queries when loading a list of articles with categories. The second is slow LIKE-based search on article body. The third is lack of feedback: customers can't rate article usefulness, and the team doesn't know which materials need improvement. A typical scenario: a site has 15,000 articles, search uses LIKE '%query%', response time 5–10 seconds. After migration to PostgreSQL tsvector, response time drops to 10 milliseconds — that's up to 500 times faster.

How We Design the Database Schema

We build category hierarchy using self-reference parent_id. For each level, we store an icon and sort order. Articles are linked to a category, with a slug and numeric counters.

CREATE TABLE kb_categories ( id SERIAL PRIMARY KEY, parent_id INTEGER REFERENCES kb_categories(id), name VARCHAR(150) NOT NULL, slug VARCHAR(150) NOT NULL UNIQUE, icon VARCHAR(50), sort_order INTEGER NOT NULL DEFAULT 0 ); CREATE TABLE kb_articles ( id SERIAL PRIMARY KEY, category_id INTEGER REFERENCES kb_categories(id), title VARCHAR(255) NOT NULL, slug VARCHAR(255) NOT NULL UNIQUE, excerpt TEXT, body TEXT NOT NULL, body_search TSVECTOR GENERATED ALWAYS AS ( to_tsvector('russian', title || ' ' || body) ) STORED, helpful_yes INTEGER NOT NULL DEFAULT 0, helpful_no INTEGER NOT NULL DEFAULT 0, views_count INTEGER NOT NULL DEFAULT 0, is_published BOOLEAN NOT NULL DEFAULT true, sort_order INTEGER NOT NULL DEFAULT 0, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW() ); CREATE INDEX ON kb_articles USING gin(body_search); CREATE INDEX ON kb_articles(category_id, is_published, sort_order); CREATE TABLE faq_items ( id SERIAL PRIMARY KEY, category VARCHAR(100), question TEXT NOT NULL, answer TEXT NOT NULL, sort_order INTEGER NOT NULL DEFAULT 0 ); 

The body_search field is a generated column, eliminating data desynchronization. The GIN index delivers search speeds <10ms on 10K articles. We use a separate table for Q&A items — it simplifies ordering and categorization.

How to Implement Full-Text Search on PostgreSQL?

We use plainto_tsquery('russian', $query) and rank with ts_rank. A Laravel controller:

class KnowledgeBaseController extends Controller { // Full-text search public function search(Request $request): JsonResponse { $query = trim($request->input('q', '')); if (strlen($query) < 2) { return response()->json(['data' => [], 'query' => $query]); } $articles = KbArticle::published() ->whereRaw( "body_search @@ plainto_tsquery('russian', ?)", [$query] ) ->selectRaw("*, ts_rank(body_search, plainto_tsquery('russian', ?)) as rank", [$query]) ->orderByDesc('rank') ->limit(10) ->get(['id', 'title', 'slug', 'excerpt', 'category_id', 'rank']); return response()->json([ 'data' => KbArticleResource::collection($articles), 'query' => $query, ]); } // Article with view tracking public function show(string $slug): JsonResponse { $article = KbArticle::published() ->with('category') ->where('slug', $slug) ->firstOrFail(); // Increment views (async) dispatch(fn() => $article->increment('views_count'))->afterResponse(); // Related articles in same category $related = KbArticle::published() ->where('category_id', $article->category_id) ->where('id', '!=', $article->id) ->orderByDesc('views_count') ->limit(5) ->get(['id', 'title', 'slug']); return response()->json([ 'article' => KbArticleResource::make($article), 'related' => $related, ]); } // Rate article helpfulness public function helpful(Request $request, KbArticle $article): JsonResponse { $request->validate(['helpful' => 'required|boolean']); $session = $request->session()->getId(); $key = "helpful:{$article->id}:{$session}"; if (Cache::has($key)) { return response()->json(['already_voted' => true]); } Cache::put($key, true, now()->addDays(30)); if ($request->boolean('helpful')) { $article->increment('helpful_yes'); } else { $article->increment('helpful_no'); } return response()->json([ 'yes' => $article->fresh()->helpful_yes, 'no' => $article->fresh()->helpful_no, ]); } } 

The show method uses afterResponse() — views are counted without slowing down the response. Vote caching prevents abuse. The response includes related articles, increasing time on site.

React Components: FAQ Accordion and Search

We build the frontend with React or Vue — doesn't matter. Here's a React example.

FAQ Accordion

import { useState } from 'react'; interface FaqItem { id: number; question: string; answer: string; } function FaqAccordion({ items, category }: { items: FaqItem[]; category: string }) { const [openId, setOpenId] = useState<number | null>(null); return ( <section> <h2>{category}</h2> <dl> {items.map(item => ( <div key={item.id} className={`faq-item ${openId === item.id ? 'open' : ''}`}> <dt> <button onClick={() => setOpenId(openId === item.id ? null : item.id)} aria-expanded={openId === item.id} aria-controls={`faq-answer-${item.id}`} > {item.question} <span aria-hidden>{openId === item.id ? '−' : '+'}</span> </button> </dt> <dd id={`faq-answer-${item.id}`} hidden={openId !== item.id} > <div dangerouslySetInnerHTML={{ __html: item.answer }} /> </dd> </div> ))} </dl> </section> ); } 

Implemented following Accessibility guidelines: aria-expanded, aria-controls, hidden. The answer can contain HTML (images, links).

Search with Debounce

function KbSearch() { const [query, setQuery] = useState(''); const [results, setResults] = useState<KbArticle[]>([]); useEffect(() => { if (query.length < 2) { setResults([]); return; } const timer = setTimeout(async () => { const { data } = await api.get('/api/kb/search', { params: { q: query } }); setResults(data.data); }, 300); return () => clearTimeout(timer); }, [query]); return ( <div className="kb-search"> <input type="search" value={query} onChange={e => setQuery(e.target.value)} placeholder="Search the knowledge base..." aria-label="Search the knowledge base" /> {results.length > 0 && ( <ul className="kb-search__results" role="listbox"> {results.map(article => ( <li key={article.id} role="option"> <a href={`/help/${article.slug}`}> <strong>{article.title}</strong> <p>{article.excerpt}</p> </a> </li> ))} </ul> )} </div> ); } 

300ms debounce prevents excessive requests. Uses role="listbox" and role="option" for accessibility.

What If You Need to Find Answers Quickly?

Search with autocomplete is the minimum. Additionally, we can implement filtering by category, sorting by popularity or date. In complex cases, we integrate Elasticsearch, but for 95% of projects, PostgreSQL tsvector is sufficient.

How Long Does Development Take?

Total: 4 to 5 days for a standard knowledge base with Q&A section. Timeline may increase if integration with external systems or custom analytics is required. A typical project costs between $2,500 and $5,000, often saving companies thousands in support overhead annually.

Search Method Comparison

version: '3.8' services: postgres: image: postgres:16 environment: POSTGRES_DB: knowledge_base POSTGRES_USER: kb_user POSTGRES_PASSWORD: secret ports: - '5432:5432' volumes: - pgdata:/var/lib/postgresql/data volumes: pgdata: