PostgreSQL vs Firestore: A Practical Decision Framework

I've built production systems on both PostgreSQL and Firestore. I Love Hwarang runs on PostgreSQL. Number Strike Baseball runs on Firestore. Flashcards Alarm uses Firestore. The choice wasn't random — each project had characteristics that made one database clearly better than the other.

I've also migrated between the two, and that experience shaped my framework more than building greenfield. At FSMH, we started with Firestore for speed-to-market but hit a wall when the fundraising analytics required complex aggregations across campaigns, donors, and time periods. The migration to PostgreSQL took six weeks and involved rewriting every data access layer. The lesson was painful but clear: choosing the wrong database isn't fatal, but the migration cost is real — plan for your year-two queries, not just your launch-day queries.

“I've shipped products with both. Here's the decision framework I actually use, based on data relationships, query patterns, and team size.”

Use Firestore when your data is document-shaped and your access patterns are simple. Flashcard decks, game sessions, user profiles — these are self-contained documents that rarely need joins. Firestore gives you real-time sync, offline support, and automatic scaling without managing servers. For a mobile app with a small team, this is often the right call.

Firestore's security rules deserve special attention. They're powerful but can become an unmanageable maze. For Number Strike Baseball, our security rules file grew to 400+ lines covering game state validation, user profile access control, and anti-cheat measures. Testing security rules requires the Firestore emulator, and debugging rule denials in production often means reading cryptic permission-denied errors with no stack trace. I recommend keeping security rules as thin as possible and moving complex validation logic to Cloud Functions instead.

Use PostgreSQL when your data has relationships that matter. I Love Hwarang has campaigns, donors, transactions, receipts, and admin accounts — all with meaningful relationships. A donor's history spans multiple campaigns. A campaign's analytics require aggregating transaction data. These queries are natural in SQL and painful in a document database.

PostgreSQL's operational overhead is the trade-off you pay for its power. You're managing connections, vacuuming tables, monitoring query performance, and planning indexes. For I Love Hwarang, I use Supabase as a managed PostgreSQL provider, which handles backups, replication, and scaling. But even with a managed service, I still spend time optimizing slow queries, managing migrations, and monitoring connection pool utilization during traffic spikes. This operational cost is worth it for complex data, but it's real and ongoing.

The real-time factor deserves its own analysis. Firestore's real-time listeners are magical for applications that need live updates — multiplayer games, collaborative tools, chat. PostgreSQL can do real-time with LISTEN/NOTIFY or Change Data Capture, but it requires additional infrastructure. If real-time is your core feature, Firestore's built-in support saves weeks of engineering.

Offline-first is a dimension that often gets overlooked in database selection. Firestore's offline persistence is built-in and transparent — the SDK caches data locally, serves reads from cache when offline, and syncs writes when connectivity returns. Building equivalent offline support with PostgreSQL requires a local database (SQLite), a sync engine, and conflict resolution logic. For Flashcards Alarm, where users study during morning commutes with spotty cell service, Firestore's offline support was a non-negotiable requirement that would have taken months to replicate with PostgreSQL.

Cost models differ dramatically at scale. Firestore charges per read, write, and delete. PostgreSQL charges per server hour. For read-heavy applications with predictable traffic, PostgreSQL is significantly cheaper. For write-heavy applications with spiky traffic, Firestore's per-operation pricing and auto-scaling can be more cost-effective.

Developer experience is the often-unquantified factor. Firestore's SDK is excellent — real-time listeners, offline caching, and type-safe queries all work out of the box. Setting up a Firestore project takes 10 minutes. PostgreSQL requires choosing an ORM (or going raw SQL), configuring connection pooling, setting up migrations, and managing schema changes across environments. For a solo developer or small team shipping fast, Firestore's lower setup cost is a genuine competitive advantage. For a larger team with dedicated DevOps, PostgreSQL's ecosystem of mature tooling (pgAdmin, pg_stat_statements, EXPLAIN ANALYZE) provides deeper insight and control.

My decision framework in one sentence: if I need joins, I use PostgreSQL; if I need real-time sync or offline-first, I use Firestore; if I need both, I use both with a sync layer between them. The tools serve the requirements, not the other way around.

One pattern I've found increasingly useful is the hybrid approach: Firestore for user-facing real-time features and PostgreSQL for backend analytics and reporting. In a hypothetical e-commerce app, product browsing and cart management live in Firestore for real-time updates and offline access, while order history, revenue analytics, and inventory management live in PostgreSQL for complex queries and joins. A Cloud Function sync layer keeps both systems consistent. This adds architectural complexity, but for products that genuinely need both real-time sync and relational queries, it's often simpler than forcing either database to do what it wasn't designed for.