Database Optimization - Obsidian Chess Studio

Obsidian Chess Studio uses SQLite databases (.db3 files) to store millions of chess games efficiently. This guide covers optimization techniques for maximum performance.

Database Architecture

Storage Format

Obsidian Chess Studio uses a custom SQLite schema optimized for chess data:

export async function query_games(
  db: string, 
  query: GameQuery
): Promise<QueryResponse<NormalizedGame[]>> {
  try {
    const timeControlCategory =
      (query as unknown as { time_control_category?: string | null })
        .time_control_category ?? null;
    return unwrap(
      await commands.getGames(db, {
        player1: query.player1,
        range1: normalizeRange(query.range1),
        player2: query.player2,
        range2: normalizeRange(query.range2),
        tournament_id: query.tournament_id,
        sides: query.sides,
        outcome: query.outcome,
        start_date: query.start_date,
        end_date: query.end_date,
        position: null,
        time_control_category: timeControlCategory,
        game_details_limit: query.game_details_limit,
        wanted_result: query.wanted_result ?? null,
        options: {
          skipCount: query.options?.skipCount ?? false,
          page: query.options?.page,
          pageSize: query.options?.pageSize,
          sort: query.options?.sort || "id",
          direction: query.options?.direction || "desc",
        },
      } as any),
    );
  } catch (error) {
    const message = error instanceof Error ? error.message : String(error);
    if (message.toLowerCase().includes("no such table")) {
      return { data: [], count: 0 };
    }
    throw error;
  }
}

Available Databases

Obsidian Chess Studio provides several pre-optimized databases:

Lumbra's Gigabase

9.5M+ games • 526K players • 2.6 GBComprehensive collection of high-quality games.

Caissabase 2024

5.4M+ games • 321K players • 1.2 GBCurated database of notable games.

Position Cache

Pre-calculated statistics • 628 MBAccelerates position searches across major databases.

MillionBase

3.4M+ games • 284K players • 744 MBCompact yet comprehensive database.

Indexing Strategies

Automatic Indexing

Obsidian Chess Studio automatically creates indexes when importing databases. For optimal performance, ensure databases are fully indexed before querying.

Index Types

The backend creates indexes on key fields:

Player Indexes: Fast player name lookups
Date Indexes: Efficient date range queries
ELO Indexes: Quick rating-based filtering
Position Indexes: Accelerated position searches
Tournament Indexes: Fast tournament game retrieval

export async function query_players(
  db: string, 
  query: PlayerQuery
): Promise<QueryResponse<Player[]>> {
  try {
    return unwrap(
      await commands.getPlayers(db, {
        options: {
          skipCount: query.options.skipCount || false,
          page: query.options.page,
          pageSize: query.options.pageSize,
          sort: query.options.sort,
          direction: query.options.direction,
        },
        name: query.name,
        range: normalizeRange(query.range),
      }),
    );
  } catch (error) {
    const message = error instanceof Error ? error.message : String(error);
    if (message.toLowerCase().includes("no such table")) {
      return { data: [], count: 0 };
    }
    throw error;
  }
}

Checking Index Status

async function getDatabase(name: string): Promise<DatabaseInfo> {
  const appDataDirPath = await appDataDir();
  const path = await resolve(appDataDirPath, "db", name);
  const res = await commands.getDbInfo(path);
  const source = await invoke<DatabaseSource | null>(
    "get_db_source", 
    { file: path }
  ).catch(() => null);
  
  if (res.status === "ok") {
    return {
      type: "success",
      ...res.data,
      file: path,
      ...(source ? { source } : {}),
    };
  }
  return {
    type: "error",
    filename: path,
    file: path,
    error: res.error,
    indexed: false,  // Not indexed if error
    ...(source ? { source } : {}),
  };
}

Query Optimization

Efficient Filtering

Range Normalization

Avoid unnecessary filters by normalizing ELO ranges:

function normalizeRange(
  range?: [number, number] | null
): [number, number] | undefined {
  // If range covers full spectrum (3000 points), omit filter
  if (!range || range[1] - range[0] === 3000) {
    return undefined;
  }
  return range;
}

Impact: Reduces query complexity by removing redundant filters.

Skip Count Optimization

For pagination, skip total count calculation when not needed:

{
  options: {
    skipCount: true,      // Skip expensive COUNT(*) query
    page: 1,
    pageSize: 50,
    sort: "averageElo",
    direction: "desc",
  }
}

Performance Gain: 2-3x faster for large result sets.

Limit Game Details

Restrict the number of full game records retrieved:

game_details_limit: 
  query.game_details_limit == null 
    ? null 
    : (String(query.game_details_limit) as unknown as bigint),

Best Practice: Set to 100-1000 for position searches to balance detail and performance.

Optimal Sort Fields

Choose appropriate sort fields based on use case:

Sort Field	Best For	Performance
`id`	Sequential browsing	⚡️ Fastest
`date`	Chronological order	⚡️ Fast
`averageElo`	Quality games	🔶 Moderate
`ply_count`	Game length	🔶 Moderate
`whiteElo`, `blackElo`	Player strength	🔶 Moderate

Position Search Optimization

Position searches are the most intensive database operation. Use optimization techniques to improve response times.

const payload = {
  position: {
    fen,
    type_: type,  // "exact" or "partial"
  },
  game_details_limit: String(gameDetailsLimitValue) as unknown as bigint,
  options: {
    skipCount: true,
    sort: (options.sort || "averageElo"),
    direction: (options.direction || "desc"),
  },
  ...(options.start_date ? { start_date: options.start_date } : {}),
  ...(options.end_date ? { end_date: options.end_date } : {}),
  ...(wantedResult ? { wanted_result: wantedResult } : {}),
};

Optimization Tips:

Use Exact Matching when possible (faster than partial/material matching)
Apply Date Filters to narrow search scope
Limit Game Details to 500-1000 games
Use Position Cache database for frequently searched positions

Cache Management

Position Cache Database

The Position Cache database stores pre-calculated statistics:

Download Position Cache

Download the 628 MB Position Cache from the Databases page.

Automatic Integration

The cache is automatically used for position searches across supported databases.

Performance Boost

Common positions load 10-50x faster with the position cache.

The Position Cache covers:

Lumbra’s Gigabase
Caissabase 2024
Ajedrez Data (Correspondence & OTB)
MillionBase

In-Memory Caching

Obsidian Chess Studio implements intelligent caching at multiple levels:

Query Result Cache

Recent query results are cached in memory using @tanstack/react-query:

const { data, error, isLoading } = useQuery({
  queryKey: ["default-dbs"],
  queryFn: async () => {
    return DATABASES as SuccessDatabaseInfo[];
  },
  staleTime: Infinity,  // Cache indefinitely
});

Opening Cache

Opening statistics are cached for the current session to avoid redundant calculations.

Database Info Cache

Database metadata (game count, player count, etc.) is cached to reduce file system access.

Clearing Cache

To clear cached data:

In-App: Settings → Advanced → Clear Cache
Manual: Delete AppData/cache directory
Selective: Use React Query Devtools to clear specific queries

Performance Tuning

Database File Optimization

VACUUM Command

Periodically vacuum databases to reclaim space and improve performance:

This operation temporarily requires up to 2x the database size in free disk space.

Benefits:

Removes fragmentation
Reclaims deleted space
Optimizes index structures
Improves query speed by 10-30%

File Location

Store databases on fast storage:

Storage Type	Random Read	Impact
NVMe SSD	< 0.1 ms	⚡️ Best
SATA SSD	< 0.5 ms	⚡️ Excellent
HDD	10-15 ms	🔶 Acceptable
Network	Varies	❌ Not recommended

Database Size Management

Manage database growth:

Archive Old Games

Export and remove games older than a certain date if not needed.

Split by Category

Create separate databases for different time controls or player levels.

Remove Duplicates

Use built-in duplicate detection before importing.

Query Performance Monitoring

Enable performance monitoring in Settings → Developer → Show Query Performance.

Key metrics to monitor:

Query Time: Target < 500ms for most queries
Result Set Size: Larger = slower; use pagination
Cache Hit Rate: Higher = better performance
Database Size: Impacts memory usage

Concurrent Query Management

// If multiple players are selected, issue one query per player
if (selectedPlayers.length > 0) {
  const searchLimit = 1000; // backend maximum per search
  const basePayload = {
    position: { fen, type_: type },
    game_details_limit: String(searchLimit) as unknown as bigint,
    options: {
      skipCount: true,
      sort: options.sort || "averageElo",
      direction: options.direction || "desc",
    },
  };

  const results = await Promise.all(
    payloads.map((p) => 
      commands.searchPosition(options.path!, p as any, tab)
    )
  );
  // Merge results...
}

Limit concurrent queries to 3-5 to avoid overwhelming the database engine.

Advanced Techniques

Opening Precaching

Pre-calculate opening statistics for faster analysis:

Select Reference Database

Go to Settings → Directories → Reference Database.

Start Precaching

Click “Start Precache” to begin calculating opening statistics.

Monitor Progress

The progress bar shows processed positions and any errors.

Completion

Once complete, opening lookups will be significantly faster.

const result = await commands.precacheOpenings(referenceDatabase);

Benefits:

5-10x faster opening explorer
Reduced database load
Smoother user experience

Batch Operations

When performing bulk operations:

Transaction Batching

Group multiple writes into single transactions for 10-100x speedup.

Async Processing

Use background workers for large imports to keep UI responsive.

Incremental Updates

Update only changed records rather than full re-imports.

Deferred Indexing

Create indexes after bulk inserts, not during.

ChessBase Online Integration

For cloud-based searches:

if (isChessbaseDatabasePath(options.path)) {
  const requestGeneration = ++chessbaseSearchGeneration;
  
  const res = await invokeWithAbort<ChessbasePositionSearchResult>(
    "chessbase_search_position",
    {
      fen,
      useMaterial: type === "partial",
      maxGames: gameDetailsLimitValue,
      color: options.color ?? "any",
      wantedResult: options.result ?? "any",
      startDate: options.start_date ?? null,
      endDate: options.end_date ?? null,
    },
    signal,
  );
}

ChessBase searches have a 30-second timeout and support cancellation for better performance.

Troubleshooting

Slow Query Performance

Symptoms: Queries taking > 5 secondsSolutions:

Check if database is fully indexed
Verify database file is on fast storage (SSD)
Reduce game_details_limit to 500-1000
Enable skipCount: true for pagination
Use Position Cache database
Run VACUUM to defragment

High Memory Usage

Symptoms: Application using excessive RAMSolutions:

Reduce concurrent query limit
Clear query cache: Settings → Clear Cache
Lower pageSize in query options
Restart application to clear memory
Close unused board tabs

Database Corruption

Symptoms: “corrupted database” error messagesSolutions:

// Backend automatically detects and removes corrupted databases
return list.filter((db) => {
  if (db.type !== "error") return true;
  const msg = (db.error ?? "").toLowerCase();
  return !msg.includes("corrupted database detected and removed");
});

Re-download the database from a trusted source
Check disk health (run chkdsk or fsck)
Ensure sufficient disk space during operations
Avoid force-closing during write operations

Missing Tables

Symptoms: “no such table” errors

catch (error) {
  const message = error instanceof Error ? error.message : String(error);
  if (message.toLowerCase().includes("no such table")) {
    return { data: [], count: 0 };  // Return empty result
  }
  throw error;
}

Database may be incompatible format
Re-import from PGN source
Download fresh copy from Databases page

Best Practices Summary

Use Position Cache

Download and use the Position Cache database for 10-50x faster position searches.

Optimize Queries

Use skipCount, normalize ranges, and limit game details for faster results.

Fast Storage

Store databases on SSD for best performance, especially for large databases.

Regular Maintenance

Periodically vacuum databases and clear cache to maintain optimal performance.

Monitor Performance

Enable performance monitoring to identify slow queries and bottlenecks.

Precache Openings

Use opening precaching for frequently analyzed opening positions.

Database Management

Learn about importing and managing databases

Position Search

Advanced position search techniques

Customization

Configure database and performance settings

Get Started

User Guide

Features

Advanced

​Database Architecture

​Storage Format

​Available Databases

Lumbra's Gigabase

Caissabase 2024

Position Cache

MillionBase

​Indexing Strategies

​Automatic Indexing

​Index Types

​Checking Index Status

​Query Optimization

​Efficient Filtering

​Position Search Optimization

​Cache Management

​Position Cache Database

​In-Memory Caching

​Clearing Cache

​Performance Tuning

​Database File Optimization

​Query Performance Monitoring

​Concurrent Query Management

​Advanced Techniques

​Opening Precaching

​Batch Operations

Transaction Batching

Async Processing

Incremental Updates

Deferred Indexing

​ChessBase Online Integration

​Troubleshooting

​Best Practices Summary

Use Position Cache

Optimize Queries

Fast Storage

Regular Maintenance

Monitor Performance

Precache Openings

​Related Resources

Database Management

Position Search

Customization

Build docs developers (and LLMs) love

Database Architecture

Storage Format

Available Databases

Indexing Strategies

Automatic Indexing

Index Types

Checking Index Status

Query Optimization

Efficient Filtering

Position Search Optimization

Cache Management

Position Cache Database

In-Memory Caching

Clearing Cache

Performance Tuning

Database File Optimization

Query Performance Monitoring

Concurrent Query Management

Advanced Techniques

Opening Precaching

Batch Operations

ChessBase Online Integration

Troubleshooting

Best Practices Summary

Related Resources