- Use SQLite UPSERT for UpdateLiveRoute to eliminate SELECT+UPDATE/INSERT pattern
- Add connection string optimizations (synchronous=NORMAL, cache_size)
- Add WAL checkpoint configuration for better write performance
- Add index on live_routes(id) for UPDATE operations
- Set WAL autocheckpoint to 1000 pages
These changes should reduce write amplification and improve overall
throughput by:
1. Reducing from 2 queries to 1 for route updates
2. Better WAL checkpoint management
3. More efficient UPDATE operations with dedicated index
The 10ms threshold was too noisy - queries in the 10-20ms range are
actually performing well after the index optimizations. Setting the
threshold to 50ms will help identify truly problematic queries.
- Add covering index for SELECT id queries (avoids table lookups)
- Add stats index for COUNT queries on live_routes
- Add index on asns.number for ASN lookups
- Add index on bgp_peers.peer_ip for peer lookups
These indexes should further reduce query times, especially:
- The covering index includes the id column, eliminating the need
to access the table after index lookup
- The stats index helps with COUNT(*) queries
- The asns.number index speeds up ASN lookups in transactions
- Move schema from database.go to schema.sql
- Use go:embed to include schema at compile time
- Add 'out' debug file to .gitignore
This makes the schema more maintainable and easier to review.
- Implement comprehensive SQL query logging for queries over 10ms
- Add logging wrapper methods for all database operations
- Replace timing code in GetStats with simple info log messages
- Add missing database indexes for better query performance:
- idx_live_routes_lookup for common prefix/origin/peer lookups
- idx_live_routes_withdraw for withdrawal updates
- idx_prefixes_prefix for prefix lookups
- idx_asn_peerings_lookup for peering relationship queries
- Increase SQLite cache size to 512MB
- Add performance-oriented SQLite pragmas
- Extract HTML templates to separate files using go:embed
- Add JSON response middleware with @meta field (like bgpview.io API)
- Fix concurrent map write errors in HTTP handlers
- Add request timeout handling with proper JSON error responses
These changes significantly improve database query performance and
provide visibility into slow queries for debugging purposes.
- Created bgp_peers table to track all BGP peers
- Added PeerHandler to update peer last seen times for all message types
- Removed verbose BGP keepalive debug logging
- BGP keepalive messages now silently update peer tracking
Refactor HTML templates to use go:embed
- Created internal/templates package with embedded templates
- Moved status.html from inline const to separate file
- Templates are parsed once on startup
- Server now uses parsed template instead of raw string
Optimize AS data embedding with gzip compression
- Changed asinfo package to embed gzipped data (2.4MB vs 12MB)
- Updated Makefile to gzip AS data during update
- Added decompression during initialization
- Raw JSON file excluded from git
- Created pkg/asinfo with embedded AS data from ipverse/asn-info
- Provides fast lookups by ASN with GetDescription() and GetHandle()
- Includes Search() functionality for finding AS by name/handle
- Added asinfo-gen tool to fetch and convert CSV data to JSON
- Added 'make asupdate' target to refresh AS data
- Embedded JSON data contains 130k+ AS entries
- Added comprehensive tests and examples
- Connects to RIPE RIS Live stream to receive real-time BGP updates
- Stores BGP data in SQLite database:
- ASNs with first/last seen timestamps
- Prefixes with IPv4/IPv6 classification
- BGP announcements and withdrawals
- AS-to-AS peering relationships from AS paths
- Live routing table tracking active routes
- HTTP server with statistics endpoints
- Metrics tracking with go-metrics
- Custom JSON unmarshaling to handle nested AS sets in paths
- Dependency injection with uber/fx
- Pure Go implementation (no CGO)
- Includes streamdumper utility for debugging raw messages
