backend-optimization

name	backend-optimization
description	Optimizing backend choice and configuration for ENC processing and maritime routing. Use when choosing between PostGIS and GeoPackage, tuning performance, or planning production deployment.

PostGIS vs GeoPackage Performance Tuning

Optimizing backend choice and configuration for ENC processing and maritime routing based on comprehensive performance benchmarks.

Purpose

Backend selection significantly impacts performance. This skill provides data-driven guidance on choosing and optimizing PostGIS or GeoPackage based on use case, scale, and requirements.

Performance Summary

⚠️ For current performance metrics, see /docs/reference/technical-specs.md - This document contains comprehensive benchmarks updated regularly with actual test results.

Key findings from latest benchmarks (SF Bay to LA route, 47 S-57 ENCs, ~387 NM):

• PostGIS is 2.0-2.4× faster overall than GeoPackage
• PostGIS dominates graph weighting: 2.0-4.2× faster (the critical bottleneck)
• Base graph creation: GeoPackage 2× faster for initial creation
• Storage ratio: PostGIS ~0.9× vs GeoPackage (slightly more compact)

Reference for specific metrics:

• Graph Creation Performance: /docs/reference/technical-specs.md - Section "Graph Creation Performance by Spacing" (0.1-0.5 NM)
• Fine Graph Performance: /docs/reference/technical-specs.md - Section "Fine Graph Creation Performance by Spacing"
• Storage Requirements: /docs/reference/technical-specs.md - Section "Storage Requirements"

Decision Matrix

Use PostGIS When:

• Scale: >1000 ENCs or >500K graph nodes
• Performance: Speed is critical (production deployments)
• Concurrency: Multiple users or processes accessing data
• Features: Need transactional updates (S57Updater)
• Infrastructure: Have server infrastructure available

Best for: Production systems, large-scale analysis, server-based deployments

Use GeoPackage When:

• Scale: <500 ENCs or <500K graph nodes
• Portability: Need single-file, portable database
• Offline: No server access or internet connectivity
• Simplicity: Want zero-configuration setup
• Sharing: Need to transfer complete database easily

Best for: Offline applications, portable analysis, single-user scenarios, small-medium datasets

Use SpatiaLite When:

• Scale: <100 ENCs, testing/prototyping only
• Simplicity: Absolutely minimal setup required

Not recommended for production due to limited spatial index performance.

Performance Breakdown by Pipeline Step

Step 1: Base Graph Creation (0.3 NM Grid)

• GeoPackage advantage: 2× faster (96-99s vs 193-202s)
• Reason: File-based operations faster for initial creation
• Optimization: Use --skip-base to reuse existing base graph

Step 2: Fine Graph Refinement

• Depends on mode:
  • FINE 0.2nm: GeoPackage slightly faster (12-28s vs 12-28s)
  • FINE 0.1nm: PostGIS faster (101s vs 36s for PostGIS)
  • H3 Hexagonal: PostGIS 41% faster (468s vs 276s)
• Optimization: For H3 mode, use PostGIS

Step 3: Graph Weighting (CRITICAL BOTTLENECK)

• PostGIS dominates: 2.0-4.2× faster than GeoPackage
• Reason: Database-side spatial operations drastically reduce enrichment time
• Breakdown:
  • FINE 0.2nm: PostGIS 161s vs GeoPackage 684s (4.2× faster)
  • FINE 0.1nm: PostGIS 762s vs GeoPackage 2,703s (3.5× faster)
  • H3: PostGIS 4,916s vs GeoPackage 9,586s (2.0× faster)
• Impact: 37-89% of total pipeline time
• Optimization: Use PostGIS for any graph >500K nodes

Step 4: Pathfinding Execution

• PostGIS advantage: 1.2-1.3× faster graph loading
• Actual routing: <1 second (negligible difference)
• Optimization: Use PostGIS for faster loading, but difference is minor

Configuration Optimizations

PostGIS Tuning

-- Increase work memory for spatial operations
SET work_mem = '256MB';

-- Create spatial indexes
CREATE INDEX idx_geom ON your_table USING GIST (geom);

-- Analyze for query planner
ANALYZE your_table;

-- Vacuum after large updates
VACUUM ANALYZE;

GeoPackage Tuning

import sqlite3

# Increase cache size
conn = sqlite3.connect("maritime.gpkg")
conn.execute("PRAGMA cache_size = -64000")  # 64 MB cache

# Use WAL mode for concurrency
conn.execute("PRAGMA journal_mode = WAL")

# Disable sync for faster writes (careful: data loss risk)
conn.execute("PRAGMA synchronous = OFF")  # Use only for non-critical data

conn.close()

GDAL Cache for Both Backends

from osgeo import gdal

# Increase GDAL cache (helps both backends)
gdal.SetCacheMax(512 * 1024 * 1024)  # 512 MB

Scaling Characteristics

PostGIS Scaling

• Near-linear scaling for weighting step (best in class)
• 4× more nodes → 3.6× total time (FINE 0.1nm vs 0.2nm)
• Recommended for: Graphs >500K nodes

GeoPackage Scaling

• Superlinear scaling for weighting step (file I/O bottleneck)
• 4× more nodes → 4.7× weighting time
• Recommended limit: <500K nodes

Production Deployment Recommendations

Small-Scale Production (<500 ENCs, <500K nodes)

Backend: GeoPackage
Graph Mode: FINE 0.1nm or FINE 0.2nm
Expected Time: 14-52 minutes
Advantages: Simple deployment, portable, no server

Medium-Scale Production (500-2000 ENCs, 500K-2M nodes)

Backend: PostGIS ⭐ STRONGLY RECOMMENDED
Graph Mode: FINE 0.1nm
Expected Time: 21-30 minutes (50-100 ENCs)
Advantages: 2-3× faster, transactional updates, concurrent access

Large-Scale Production (>2000 ENCs, >2M nodes)

Backend: PostGIS (REQUIRED)
Graph Mode: FINE 0.1nm or BASE 0.3nm
Expected Time: Varies by scale
Advantages: Only viable option, optimized for large datasets

Research/Multi-Resolution

Backend: PostGIS
Graph Mode: H3 Hexagonal
Expected Time: 107-180 minutes
Advantages: Flexible multi-resolution analysis

Cost-Benefit Analysis

PostGIS

Pros:

• 2.0-2.4× faster overall
• 2.0-4.2× faster weighting (critical bottleneck)
• Transactional updates
• Concurrent access
• Production-proven scalability

Cons:

• Requires server setup
• More complex deployment
• Less portable

Total Cost: Setup time + faster processing = Better for repeated use

GeoPackage

Pros:

• Zero-configuration
• Single-file portability
• Easy sharing/transfer
• Works offline

Cons:

• 2.0-2.4× slower overall
• Weighting bottleneck (37-89% of time)
• No concurrent access
• File size grows large

Total Cost: No setup + slower processing = Better for one-time use or small datasets

Migration Strategy

GeoPackage to PostGIS

# Export GeoPackage to SQL
ogr2ogr -f "PostgreSQL" \
    PG:"dbname=maritime_db host=localhost user=maritime_user" \
    maritime.gpkg \
    -lco SCHEMA=public

# Or use GDAL VectorTranslate in Python
from osgeo import gdal

gdal.VectorTranslate(
    "PG:dbname=maritime_db host=localhost",
    "maritime.gpkg",
    format="PostgreSQL",
    layerCreationOptions=["SCHEMA=public"]
)

PostGIS to GeoPackage

# Export PostGIS to GeoPackage
ogr2ogr -f GPKG maritime.gpkg \
    PG:"dbname=maritime_db host=localhost" \
    -progress

Common Issues

Issue: GeoPackage Weighting Too Slow

Symptom: Weighting step takes >1 hour for moderate graphs

Solution: Switch to PostGIS - 2.0-4.2× faster

Issue: PostGIS Connection Overhead

Symptom: Base graph creation slower than GeoPackage

Solution: Expected behavior. Use --skip-base to reuse base graph, or accept one-time cost

Issue: Out of Memory During Weighting

Symptom: Process killed during weighting step

Solution:

# Increase GDAL cache
gdal.SetCacheMax(512 * 1024 * 1024)

# Or reduce graph size (use coarser resolution)
# FINE 0.2nm instead of FINE 0.1nm

Related Skills

• postgis-setup: PostGIS Setup (setting up PostGIS backend)
• environment-setup: Environment Setup (installing both backends)
• integration-tests: Integration Tests (testing both backends)

Cross-References

📊 Performance Data (Primary Source):

• /docs/reference/technical-specs.md: ⭐ Authoritative source for all performance benchmarks - Updated regularly with latest test results
  • Graph Creation Performance by Spacing (0.1-0.5 NM)
  • Fine Graph Creation Performance by Spacing
  • Storage Requirements by Backend
  • Platform Benchmarks (Linux/Windows)

📋 Backend Selection & Setup:

• /docs/user-guides/database-backend-guide.md: Quick reference comparison table and backend selection guidance
• Project Knowledge: /dev/rules/CLAUDE.md (Database Backend Patterns, Performance Characteristics sections)
• Development Workflow: /dev/rules/WORKFLOW.md
• README.md: High-level overview and SVG performance charts