Amazon Aurora PostgreSQL Database + Geographic Information Primer

this series will dive deep on

Amazon DynamoDB as a write-hot append event layer for activity feed, user action key-based reads, ephemeral pins and audit logs. This event layer then fans out to geospatial layers, allowing event capture without stressing over SQL interactions impacting potentially lifesaving searches

Amazon Aurora DSQL powering social graphing of users, connections, matches, follows, trust scores, requests & 11 written languages. Aurora DSQL enables an access pattern of relational joins with multi-region active-active writes with strong consistency

and this post's topic

Amazon Aurora MySQL PostgreSQL for range queries over geography columns, geospatial indexes, math (ST_DWithin) to facilitate correlating users to resources within 8 kilometers and providing context location during an emergency for first responders or forensics investigation

is distributed, fault tolerant, self-healing and separated from compute so your data layer isn't slowed or competing with user interface, log, & event layers

Geometry operates in a flat cartesian plane — distances measured in whatever unit your coordinate reference system uses, often degrees for lat/lon data. Geography treats coordinates as points on the earth's curved surface and measures distance in meters. Open source Geographic Information Systems are combined into PostgreSQL databases via an PostGIS extension to add geographic objects to this object-relational database, enabling Augmented Reality features in our application

Ðekawɔwɔ  PostGIS schema is small on purpose. towns_geo holds one row per township, initially seeded with municipalities of Volta region in the eastern part of Republic of Ghana. keyed by the same Universally Unique Identifier (UUID) in Aurora DSQL for each town in its own towns table — only the geometry duplicated:

resources_geo was created for storing local clinics, water points, and workforce training centers.  pins_geo allows us durable, user-consented location shares that follow the identical pattern — one geometry column, one GiST index, one foreign UUID that ties back to the row of records in Aurora DSQL. activity_geo is different: it's a materialized geo-mirror of the DynamoDB activity feed, populated by a DynamoDB Streams Lambda called geo-mirror so when we do a radius query to find what's nearby our users, our geo-mirror never has to touch the write-hot event path

Sharing designs centered around a shared UUID is a good transition point to understanding foreign-key use in Aurora PostgreSQL. PostGIS running on standard Aurora PostgreSQL supports foreign keys just fine, we simply didn't need one, because the UUID crossing between engines is enforced by convention in application code rather than a database-level constraint 

a different architecture could use a hard foreign key here for referencing location against town locations, and PostgreSQL would let us write a relational schema: town_id UUID REFERENCES towns_geo(town_id) on resources_geo, service_areas, or pins_geo. We chose not to as the row sql "references" lives in a different database engine entirely (Aurora DSQL), and no foreign key clause can span two separate database clusters. The constraint conceptually exists; it's just enforced by our Lambda handlers instead of by REFERENCES

both queries stay sub-linear; whether the region holds a thousand rows or ten million, the database engine can instantly rule out most data and only touch the relevant "pages" to keep our queries incredibly fast regardless of table size. In PostgreSQL, fixed units of size are called pages, sometimes referred to as blocks. The default size of a PostgreSQL page is a chunk of 8 KB, where it may hold dozens of rows or it may contain a portion of a single row that spills across multiple pages

Both queries hit GiST (Generalized Search Tree) indexes to achieve this; an index type PostGIS relies on for spatial lookups. GiST works by storing bounding boxes — small fixed-size rectangles that enclose each geometry — organized into a tree structure similar to R-tree bounding box indexes commonly used in geographic survey point indexing. This pattern enables ultra-fast range, intersection, and nearest-neighbor searches. Queries hit the index first as a coarse filter, eliminating the vast majority of rows by checking bounding box intersection, then a secondary filter runs the exact spatial predicate on the surviving candidates. Without a GiST index, every ST_DWithin call checking for nearby geographic points would scan the entire table row by row to answer the question, "is object A within Distance X of Object B?" In Ðekawɔwɔ  default map view, we show support within 8 km, as an example use. key thing to understand about these geographic patterns is traversal of a hierarchy of nested bounding boxes touching only the relevant PostgreSQL pages

Amazon Aurora's storage architecture differs from self-managed PostgreSQL. Amazon Web Services Availability Zones (AZ) consist of physically separated data centers in redundant facilities inside a larger geographic AWS Region. Data is replicated 6 ways across 3 Availability Zones, each AZ receiving 2 copies. Write quorums are conducted and require acknowledgement from 4 of 6 copies before the write is considered durable. Reads need 3 of 6 from the read quorum for consistency. Aurora can lose an entire data center, and 2 copies along with it, and still serve reads and writes without downtime. Storage is segmented into 10 GB protection group chunks with the ability to scale up to 256 TiB. Each protection group is replicated independently. When a segment becomes unhealthy, Aurora rebuilds remaining copies without involving the databases

Ðekawɔwɔ  PostGIS cluster runs in us-west-2 Oregon region. In Oregon USA, our cluster spreads across three Availability Zones. 6 replicated copies of app users data from Ho Ghana reside oceans apart. I did my work from the Sacramento Mountains of New Mexico. Were I to hop in a car it would be roughly a 24 hour drive to us-west-2. When I drop a location pin on our app, or query for nearby medical clinics, the latency is low double-digit milliseconds. David works from Accra, Ghana, and every query he runs crosses the Atlantic and half of North America to reach the same cluster — 80 to 120 milliseconds each way measured against us-west-2 endpoints

Amazon Aurora Global Database allows for a secondary cluster in eu-west-1, Ireland — still not Ghana, but close enough on the map and on the undersea cable routes to cut Ghana-origin latency roughly in half

A primary cluster in one region (us-west-2 Oregon) replicates via storage-layer streaming to secondary clusters in (eu-west-1 Ireland) in up to 11 total regions. For us, primary in us-west-2 Oregon, secondary in eu-west-1 Ireland. Replication lag between is typically under a second. RPO (recovery point objective — data loss blast radius in a disaster) is measured in seconds rather than minutes or hours. If us-west-2 fails outright, we can promote the eu-west-1 secondary to become the new primary. If Oregon goes down, Ireland can take over with near-zero data loss

Amazon Aurora Serverless compute layer scales in Aurora Capacity Units — ACUs — a billing and sizing unit roughly 2 GiB of memory with proportional CPU and networking based on load. Set a minimum and a maximum ACU value, and Aurora adjusts capacity in fractional increments with no cold start penalty during normal scaling events. Serverless patterns are unlocked when you set the minimum to 0 ACUs — the database pauses during inactivity and resumes on the first incoming connection, typically within a few seconds. This is why I love the serverless lens. For a weekend hackathon project that may sit idle for days between demo sessions, scale-to-zero means we pay nothing when nobody is using the geospatial engine allowing us to innovate fearlessly as garage inventors

a Ðekawɔwɔ  nearby Lambda handles this directly. PostGIS cluster's auto-pause means the first call after idle time throws DatabaseResumingException for several seconds while the cluster wakes. nearby is handled as a non-critical stat, so PostgreSQL retries once or twice to catch a fast resume, then degrades to a 200 response with empty data and a warming: true flag rather than blocking the home screen on a cold database. scale-to-zero is free until the moment someone hits it cold, and the app is written to handle the moment instead of erroring

Amazon Aurora cloning uses copy-on-write protocol, creating an identical copy of a database in minutes regardless of size. No data is physically duplicated until one side modifies a PostgreSQL page. Contrast this against a snapshot restore. Snapshot restores pull all data from Amazon S3 into a new cluster volume — where a process that can take an hour for a 2 TB dataset. We used cloning during development to spin up test copies of the geospatial database instantly, validate schema changes, then throw the clone away. under the hood, this is faster because instead of copying 2 TB of files, the clone database points to the same stored PostgreSQL pages. had we already been in production, this would have meant zero risk to the primary data

Our PostGIS lives on Aurora PostgreSQL and not on Aurora DSQL out of compatibility: Aurora DSQL does not support PostgreSQL extensions. Missing PostgreSQL extension support matched against purpose-driven database design enforced a natural constraint of needing to use two relational engines — one engine for spatial math, another for the social graph. Each database does what it's purpose-built for rather than compromising one engine into doing both poorly.

Next post we'll dive into Aurora DSQL as a Social Graph Engine

Judges of the H0: Hack The Zero event appeared to all deeply care about databases and the contest prizes rewarded innovation with data modeling. We took proving our app could scale to millions as the challenge the judges would be most impressed by. We used consider AWS services as well as Vercel hosting & v0 design flow which you can learn about in our other posts related to the #H0Hackathon. Part of our "definition of done" was that we left the hackathon with tangible perspective we could use to become pros at the architectures highlighted in the event 

https://aws.amazon.com/blogs/database/vibe-code-with-aws-databases-using-vercel-v0/

As far as what services and databases we chose, that was determined by the type of data we were serving, where and when it was needed with what urgency and resiliency. Evaluated continually against the Well Architected Framework leaning on AWS-MCP

demo:

other posts related to this project:
Vercel x Amazon Web Services (AWS) Databases & Vercel V0 
v0 MCP × Kiro CLI | Vercel x AWS | agent 2 agent design fun 

Thanks For Reading ^.^