The question isn't which database is better—it's which database fits the use case. I operate both Supabase (managed) and local PostgreSQL (self-hosted) for different purposes.
This document describes the decision framework used to allocate workloads between these options.
Current Architecture
Supabase Projects (5)
| Project | Purpose | Tables | |---------|---------|--------| | StepTen Army | Agent coordination, shared knowledge | 15 | | ShoreAgents AI | Business operations platform | 40 | | BPOC | Recruitment and candidate management | 30 | | StepTen.io | Content CMS | 10 | | Pinky Commander | Legacy (deprecated) | 8 |
Self-Hosted PostgreSQL (1)
| Instance | Purpose | Tables | |----------|---------|--------| | shoreagents_brain | Semantic search, embeddings | 3 |
Supabase Advantages
Immediate API Layer
Supabase automatically generates REST and GraphQL APIs for every table. No Express server, no route definitions, no boilerplate:
`typescript
// Immediately functional after table creation
const { data, error } = await supabase
.from('content')
.select('*')
.eq('status', 'published')
.order('created_at', { ascending: false })
.limit(10);
`
For applications requiring standard CRUD operations, this saves significant development time.
Integrated Authentication
Supabase Auth handles: - User registration and login - Password hashing and verification - Session management - Refresh token handling - Social OAuth providers
Row Level Security policies can reference auth.uid() directly:
`sql
CREATE POLICY "Users see own data"
ON user_profiles FOR SELECT
USING (user_id = auth.uid());
`
Building equivalent functionality from scratch requires substantial effort.
Real-Time Subscriptions
Database changes stream via WebSockets without additional infrastructure:
`typescript
supabase
.channel('content-changes')
.on('postgres_changes',
{ event: '*', schema: 'public', table: 'content' },
(payload) => console.log('Change:', payload)
)
.subscribe();
`
Managed Operations
Supabase handles: - Automated backups - Point-in-time recovery - Scaling (on paid plans) - Security patches - Monitoring dashboards
Operational burden is minimal.
Self-Hosted PostgreSQL Advantages
Zero Network Latency
Local queries complete in single-digit milliseconds. Cloud round-trips add 50-100ms regardless of query efficiency.
For AI agent workflows executing thousands of queries, this difference compounds significantly.
No Egress Costs
Cloud databases charge for data transfer. High-volume embedding generation and retrieval can incur substantial costs. Local processing has no per-operation charges.
Complete Control
Self-hosted provides: - Custom postgresql.conf tuning - Any extension (not limited to Supabase's supported list) - Filesystem access for bulk operations - No dependency on external service availability
Offline Operation
Local databases function without internet connectivity. For development, testing, or air-gapped environments, this is essential.
Decision Framework
Use Supabase When:
Building user-facing applications The Auth + RLS + API combination provides a complete backend with minimal code. Time to production is dramatically faster.
Real-time features are required Supabase Realtime is production-ready. Building equivalent WebSocket infrastructure requires significant effort.
Team includes non-database-specialists The dashboard, visual schema editor, and SQL editor lower barriers to database interaction.
Operational capacity is limited Managed infrastructure means no 3 AM database emergencies.
Use Self-Hosted When:
Low latency is critical AI agents querying knowledge bases benefit from local millisecond responses.
High query volume with cost sensitivity Thousands of daily queries incur no per-operation costs locally.
Extensions beyond Supabase's supported list are needed Some specialized extensions require full PostgreSQL control.
Offline or air-gapped operation is required Internet-dependent services aren't an option.
Complete data sovereignty is required Data never leaves controlled infrastructure.
Hybrid Architecture
Both options can coexist. Current architecture:
User-Facing → Supabase
ShoreAgents platform, content CMS, recruitment system all use Supabase. Benefits: - Auth integration - Real-time updates for dashboards - Managed backups - Geographic distribution for client access
AI Agent Operations → Local PostgreSQL
Semantic search brain uses local PostgreSQL. Benefits: - Sub-10ms query latency - No per-query costs for embedding operations - Offline development capability - Full pgvector control
Data Flow
`typescript
async function processQuery(question: string): Promise`
Performance Comparison
Measured latencies for equivalent operations:
| Operation | Supabase | Local PostgreSQL | |-----------|----------|------------------| | Simple SELECT | 60-100ms | 2-5ms | | Vector search (1K vectors) | 90-130ms | 5-10ms | | Vector search (10K vectors) | 160-220ms | 15-25ms | | Single INSERT | 70-110ms | 3-6ms | | Bulk INSERT (1000 rows) | 500-800ms | 50-100ms |
Network latency dominates Supabase times. For user-facing applications with human response expectations, 100ms is acceptable. For AI operations with machine-speed requirements, local is preferable.
Cost Analysis
Supabase (5 projects)
| Project | Plan | Monthly | |---------|------|---------| | StepTen Army | Free | $0 | | BPOC | Free | $0 | | Pinky Commander | Free | $0 | | ShoreAgents AI | Pro | $25 | | StepTen.io | Pro | $25 | | Total | | $50 |
Included: backups, scaling, monitoring, support.
Self-Hosted
| Item | Cost | |------|------| | Mac Mini (one-time) | $600 | | Electricity | ~$5/month | | Backup storage | $100 one-time | | Ongoing | ~$5/month |
Excluded: personal time for maintenance.
Analysis
If all 6 databases were on Supabase Pro: ~$150/month. Local saves ~$100/month but requires management time. The hybrid approach optimizes both cost and operational burden.
Migration Considerations
Supabase → Self-Hosted
`bash
# Export from Supabase
pg_dump -h db.project.supabase.co -U postgres -d postgres > dump.sql
# Import to local
psql -d local_db < dump.sql
`
Note: RLS policies referencing Supabase Auth won't function without modification.
Self-Hosted → Supabase
`bash
# Export from local
pg_dump local_db > dump.sql
# Import to Supabase
psql -h db.project.supabase.co -U postgres -d postgres < dump.sql
`
Note: Verify extension compatibility before migration.
FAQ
Can Supabase handle AI embeddings?
Yes. Supabase supports pgvector. Performance is adequate for user-facing applications. For high-frequency internal operations, local provides better latency.
What about AWS RDS or Google Cloud SQL?
These provide managed PostgreSQL without Supabase's additional features (Auth, Realtime, Storage, Functions). Good middle ground when only the database is needed.
How do you handle backups for local PostgreSQL?
Daily pg_dump to external storage, weekly rotation. Less automated than Supabase but sufficient for current scale.
Which would you recommend for a new project?
Start with Supabase unless specific requirements mandate self-hosting. Development velocity advantage is significant. Migration to self-hosted remains possible if needed.
What's the main risk of the hybrid approach?
Complexity. Two database systems mean two sets of operational knowledge, two backup strategies, two failure modes. Justified when benefits outweigh complexity costs.
Infrastructure decisions should be driven by requirements, not preferences. Evaluate each workload against its actual needs.