Skip to content
Ideony

ADR 0004: PostgreSQL 18 + PostGIS over MongoDB

ADR 0004: PostgreSQL 18 + PostGIS over MongoDB

Section titled “ADR 0004: PostgreSQL 18 + PostGIS over MongoDB”
  • Status: Accepted
  • Date: 2026-04-14
  • Deciders: Ideony team

Context

Section titled “Context”

Ideony’s SOS dispatch requires proximity-based candidate search (professionals within a radius, sorted by distance). The data model is relational: bookings reference users, professionals, categories, and reviews with FK constraints. The application also needs soft delete to prevent FK cascade violations.

Decision

Section titled “Decision”

Use PostgreSQL 18 + PostGIS 3.5 for the primary database, accessed via Prisma 7. PostGIS provides native ST_DWithin and ST_Distance functions for geographic queries. Redis 8.6 handles caching (search results 30 s, profiles 5 min) with noeviction policy.

Consequences

Section titled “Consequences”
  • PostGIS radius queries replace application-side Haversine math, with index support.
  • Relational integrity via FK constraints with soft delete on User prevents cascade violations.
  • Prisma 7 provides type-safe query builder with PostGIS raw query escape hatch.
  • PostgreSQL JSON columns available for flexible metadata without schema migration.
  • PostGIS adds an OS-level dependency to the Docker image.
  • Prisma raw queries required for geographic functions; these bypass type safety.
  • Schema migrations require careful ordering around FK references.

Alternatives considered

Section titled “Alternatives considered”
  • MongoDB + GeoJSON — rejected: geospatial queries less mature than PostGIS; joins across collections require $lookup aggregations; weaker referential integrity.
  • MySQL — rejected: no PostGIS equivalent; geospatial support is limited.