Supabase - PostgreSQL with Bells and Whistles

Look, Firebase is fine until you need to do anything beyond basic CRUD. Try joining data or running complex queries and you'll hate life. Supabase showed up in 2020 because developers were tired of Firebase's NoSQL limitations but still wanted the convenience of not managing servers.

It's basically PostgreSQL with a REST API auto-generated from your schema, plus real-time features and auth that doesn't suck. If your team knows SQL (and they should), you'll feel at home. If they don't, stick with Firebase because Supabase won't hold your hand through database design.

It's Just PostgreSQL (But That's Good)

Every Supabase project is a real PostgreSQL database. Not some gimped version - you get full superuser access and can SSH in if needed. This means:

• ACID transactions actually work (unlike Firebase's eventual consistency bullshit)
• Complex joins across tables without jumping through hoops
• JSON columns for when you need NoSQL-style data but still want real queries
• Full-text search that's actually fast and relevant
• Vector search with pgvector for AI stuff that actually works
• Row Level Security so you can sleep at night knowing users can't see each other's data

Real-Time That Actually Works (Most of the Time)

The real-time features use PostgreSQL's logical replication to stream changes over WebSockets. It's pretty solid but can get flaky under heavy load.

The good: Subscribe to table changes and get notified when rows are inserted, updated, or deleted. No polling bullshit. Presence tracking for "who's online" features works well.

The bad: Connection drops happen, especially on mobile. The broadcast messaging for chat apps is decent but you'll want a fallback. Don't expect it to scale to thousands of concurrent connections on the free tier - you'll hit limits fast.

Auth That Doesn't Make You Want to Scream

Supabase auth is solid. Email/password, social logins, phone auth, MFA - it all works. The JWT tokens play nice with session management.

The killer feature is Row Level Security. You write SQL policies that PostgreSQL enforces at the database level. No matter how your data gets accessed - API, direct SQL, whatever - the security rules apply.

The gotcha: RLS error messages are cryptic as hell. You'll spend hours debugging insufficient_privilege errors when you missed one policy. But once you get it working, it's bulletproof. Way better than application-level security that can be bypassed.

The Reality Check (August 2025)

Big names like 1Password and Mozilla use Supabase, which is reassuring. But let's talk about what you'll actually experience:

Free tier: 2 projects, 500MB database, 1GB file storage. Sounds generous until your storage fills up in week 2 of your MVP. Then it's $25/month for Pro which gives you 8GB database + 100GB file storage. Pro also bumps the max upload size from 50MB to 500GB (yes, half a terabyte), though you'll hit bandwidth costs before you actually need files that big. I learned this the hard way when our demo app hit the storage limit during a client presentation.

Connection limits: Free tier now gives you 200 concurrent connections (up from 60 in 2024), while Pro gets 500. Still sounds like a lot until your Next.js app with poor connection pooling eats through them. You'll learn about pgbouncer the hard way. When we launched on Product Hunt, our site went down in 20 minutes because every API route was opening a new connection.

Real-time scaling: Works great until 100-200 concurrent users, then you need Pro. Don't expect miracles on shared infrastructure. We had a collaborative doc editor that worked beautifully in testing with 10 users, then turned into a laggy mess when 150 people joined during launch.

Launch Week 15 in July 2025 brought significant improvements: unified logging across all services (no more jumping between tabs to debug), OpenTelemetry support for custom monitoring tools, advanced observability metrics previously only available in Grafana, organization-wide MFA enforcement, 97% faster Edge Function cold starts (sub-100ms), and cheaper cached egress at $0.03/GB (down from $0.09/GB). The AI debugging assistant can now analyze your logs and suggest fixes. These are the kinds of practical improvements that actually help in production.

The Stack That Powers Your Headaches

Supabase's architecture is PostgreSQL surrounded by a bunch of microservices. When it works, it's smooth. When it doesn't, debugging across these services is a nightmare.

What You're Actually Dealing With

PostgreSQL: Real PostgreSQL with useful extensions like pgvector and PostGIS. You get superuser access, which is great until you accidentally break something and realize you're flying solo.

Kong Gateway: Handles routing and rate limiting. It's solid but when it goes down, everything goes down. The rate limits on free tier will bite you during load testing.

PostgREST: Auto-generates REST APIs from your schema. It's clever until you need complex queries that don't map well to REST endpoints. Then you're writing custom functions or using the RPC endpoint.

Realtime Server: Elixir-based server that handles WebSocket connections. Works well but connection drops are common on mobile. The presence features are neat for collaborative apps.

GoTrue Auth: Handles authentication with JWT tokens. Pretty standard stuff. Email confirmation can be flaky - users will complain about not getting emails.

Storage Service: S3-compatible file storage with RLS policies. The image transformations work but are slow. You'll want a CDN for production. 2025 added Analytics Buckets with Apache Iceberg support for data warehouse use cases - actually useful if you're doing analytical queries on large datasets.

Edge Functions (Cool But Limited)

Edge Functions run on Deno and are pretty neat for serverless logic. The reality:

Good stuff:

• TypeScript works out of the box - no build step nonsense
• Direct database access with your connection string
• Standard Web APIs - no vendor lock-in weirdness

The pain points (mostly improved in 2025):

• Cold starts dropped to sub-100ms (97% faster than 2024) with Deno 2.1 upgrade
• Memory limits are tight - you'll hit them processing larger files
• Local development setup is still finicky despite CLI improvements
• Debugging got better with unified logging and AI assistant analysis
• Persistent storage now available - mount S3-compatible storage for file operations between invocations

They're great for webhooks, image processing, and simple API endpoints. Don't try to rebuild your entire backend with them.

Security (RLS Will Save and Haunt You)

Row Level Security is Supabase's main security feature. It's brilliant and infuriating:

Why it's brilliant: Security rules live in the database, not your app code. Bypass the API? Doesn't matter - PostgreSQL enforces the rules. SQL injection? Policies still apply.

Why you'll hate it: Error messages are cryptic as hell. insufficient_privilege tells you absolutely nothing. You'll waste hours debugging why auth.uid() returns null in your policy.

-- This looks simple
CREATE POLICY \"Users see own data\" ON profiles
    FOR SELECT USING (auth.uid() = user_id);
    
-- Until auth.uid() returns null and you don't know why
-- Common errors you'll see:
-- ERROR: column \"user_id\" does not exist
-- ERROR: function auth.uid() does not exist
-- ERROR: insufficient_privilege (with zero helpful context)

Spent 4 hours debugging this exact error last month. The issue? My JWT was expired, but the error message just said insufficient_privilege. Had to dig through the logs to realize auth.uid() was returning null because the token was stale.

Pro tip: Test your policies in the SQL editor with real JWTs. The dashboard's policy simulator lies sometimes. And remember: policies are allowlists, not denylists. No policy = no access.

Performance (It's PostgreSQL, What Did You Expect?)

Database performance: It's PostgreSQL, so it's fast for reads if you index properly. Writes are ACID-compliant but slower than NoSQL. On a $25/month Pro instance, expect decent performance for most apps.

API performance: The auto-generated REST API adds 50-100ms overhead compared to direct SQL. For complex queries, you'll end up writing custom functions anyway.

Real-time performance: Works well for 100-200 concurrent connections. Beyond that, expect connection drops and scaling issues. The presence features can get laggy with many users.

The gotcha: Connection limits will bite you. Free tier caps at 200 concurrent connections (up from 60), Pro gets 500. Your Next.js app with poor pooling will still exhaust this fast. Learn pgbouncer or suffer.

Vector Search (The AI Hype Train)

pgvector support is decent if you need vector search. The marketing makes it sound revolutionary, but it's just PostgreSQL with an extension.

What actually works:

• Store embeddings from OpenAI, Hugging Face, whatever
• Similarity search with cosine similarity or L2 distance
• Hybrid queries mixing vector similarity with regular SQL filters

The reality:

• Indexing large vector collections is slow and memory-hungry
• Query performance degrades with collection size - you'll need partitioning strategies
• RAG applications work but are basic - you'll need more sophisticated chunking and ranking

It's fine for small-to-medium vector collections. For serious AI workloads, you'll want specialized vector databases like Pinecone or Weaviate.

Understanding the architecture is one thing. Dealing with the inevitable production fires is another. Here are the exact questions you'll be frantically googling when your Supabase setup decides to shit the bed at 2 AM.