{"id":63,"date":"2025-12-29T13:50:24","date_gmt":"2025-12-29T13:50:24","guid":{"rendered":"https:\/\/navionyx.com\/blogs\/?p=63"},"modified":"2025-12-29T21:22:31","modified_gmt":"2025-12-29T21:22:31","slug":"how-we-scaled-navionyx-to-handle-100m-gps-records-using-postgresql-aws-ec2","status":"publish","type":"post","link":"https:\/\/navionyx.com\/blogs\/how-we-scaled-navionyx-to-handle-100m-gps-records-using-postgresql-aws-ec2\/","title":{"rendered":"\ud83d\ude80 How We Scaled Navionyx to Handle 100M+ GPS Records Using PostgreSQL &#038; AWS EC2"},"content":{"rendered":"<p data-start=\"475\" data-end=\"647\">When we started building <strong data-start=\"500\" data-end=\"512\">Navionyx<\/strong>, our GPS tracking platform, everything looked simple.<br data-start=\"566\" data-end=\"569\" \/>A few vehicles, a few thousand location points, and basic tracking dashboards.<\/p>\n<p data-start=\"649\" data-end=\"698\">Fast forward a few months \u2014 we were dealing with:<\/p>\n<ul data-start=\"700\" data-end=\"877\">\n<li data-start=\"700\" data-end=\"730\">\n<p data-start=\"702\" data-end=\"730\"><strong data-start=\"702\" data-end=\"730\">100+ million GPS records<\/strong><\/p>\n<\/li>\n<li data-start=\"731\" data-end=\"760\">\n<p data-start=\"733\" data-end=\"760\"><strong data-start=\"733\" data-end=\"760\">GBs of time-series data<\/strong><\/p>\n<\/li>\n<li data-start=\"761\" data-end=\"813\">\n<p data-start=\"763\" data-end=\"813\"><strong data-start=\"763\" data-end=\"813\">Thousands of devices sending data continuously<\/strong><\/p>\n<\/li>\n<li data-start=\"814\" data-end=\"877\">\n<p data-start=\"816\" data-end=\"877\">A real-time dashboard that users expected to load <em data-start=\"866\" data-end=\"877\">instantly<\/em><\/p>\n<\/li>\n<\/ul>\n<p data-start=\"879\" data-end=\"1067\">This is the story of how we redesigned our <strong data-start=\"922\" data-end=\"968\">database, architecture, and query strategy<\/strong> to scale reliably \u2014 without overengineering or throwing unnecessary infrastructure at the problem.<\/p>\n<hr data-start=\"1069\" data-end=\"1072\" \/>\n<h2 data-start=\"1074\" data-end=\"1113\">\ud83d\udccc The Real Challenge of GPS Systems<\/h2>\n<p data-start=\"1115\" data-end=\"1166\">GPS tracking systems are deceptively hard to scale.<\/p>\n<p data-start=\"1168\" data-end=\"1177\">They are:<\/p>\n<ul data-start=\"1178\" data-end=\"1322\">\n<li data-start=\"1178\" data-end=\"1227\">\n<p data-start=\"1180\" data-end=\"1227\"><strong data-start=\"1180\" data-end=\"1195\">Write-heavy<\/strong> \u2192 constant inserts from devices<\/p>\n<\/li>\n<li data-start=\"1228\" data-end=\"1276\">\n<p data-start=\"1230\" data-end=\"1276\"><strong data-start=\"1230\" data-end=\"1244\">Read-heavy<\/strong> \u2192 dashboards polling frequently<\/p>\n<\/li>\n<li data-start=\"1277\" data-end=\"1322\">\n<p data-start=\"1279\" data-end=\"1322\"><strong data-start=\"1279\" data-end=\"1301\">Time-series driven<\/strong> \u2192 data grows forever<\/p>\n<\/li>\n<\/ul>\n<p data-start=\"1324\" data-end=\"1356\">At scale, na\u00efve designs lead to:<\/p>\n<ul data-start=\"1357\" data-end=\"1454\">\n<li data-start=\"1357\" data-end=\"1382\">\n<p data-start=\"1359\" data-end=\"1382\">Slow <code data-start=\"1364\" data-end=\"1374\">ORDER BY<\/code> queries<\/p>\n<\/li>\n<li data-start=\"1383\" data-end=\"1401\">\n<p data-start=\"1385\" data-end=\"1401\">Full table scans<\/p>\n<\/li>\n<li data-start=\"1402\" data-end=\"1418\">\n<p data-start=\"1404\" data-end=\"1418\">EC2 CPU spikes<\/p>\n<\/li>\n<li data-start=\"1419\" data-end=\"1454\">\n<p data-start=\"1421\" data-end=\"1454\">Dashboards taking seconds to load<\/p>\n<\/li>\n<\/ul>\n<p data-start=\"1456\" data-end=\"1516\">We had to rethink how GPS data should be stored and queried.<\/p>\n<hr data-start=\"1518\" data-end=\"1521\" \/>\n<blockquote data-start=\"1587\" data-end=\"1840\">\n<p data-start=\"1589\" data-end=\"1840\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-54\" src=\"http:\/\/navionyx.com\/blogs\/wp-content\/uploads\/2025\/12\/Gemini_Generated_Image_2tq7np2tq7np2tq7.png\" alt=\"High-level GPS tracking system architecture showing vehicles sending location data to an AWS EC2 backend, PostgreSQL time-series database, and a real-time fleet dashboard.\" width=\"1408\" height=\"768\" \/><\/p>\n<\/blockquote>\n<hr data-start=\"1842\" data-end=\"1845\" \/>\n<h2 data-start=\"1847\" data-end=\"1903\">\ud83e\udde0 Designing GPS Data as Time-Series (Not Relational)<\/h2>\n<p data-start=\"1905\" data-end=\"1998\">One of the first decisions we made was <strong data-start=\"1944\" data-end=\"1997\">treating GPS data as append-only time-series data<\/strong>.<\/p>\n<p data-start=\"2000\" data-end=\"2015\">Key principles:<\/p>\n<ul data-start=\"2016\" data-end=\"2110\">\n<li data-start=\"2016\" data-end=\"2042\">\n<p data-start=\"2018\" data-end=\"2042\">No updates, only inserts<\/p>\n<\/li>\n<li data-start=\"2043\" data-end=\"2084\">\n<p data-start=\"2045\" data-end=\"2084\">Optimize for \u201clatest point per vehicle\u201d<\/p>\n<\/li>\n<li data-start=\"2085\" data-end=\"2110\">\n<p data-start=\"2087\" data-end=\"2110\">Avoid unnecessary joins<\/p>\n<\/li>\n<\/ul>\n<p data-start=\"2112\" data-end=\"2130\">Simplified schema:<\/p>\n<div class=\"contain-inline-size rounded-2xl corner-superellipse\/1.1 relative bg-token-sidebar-surface-primary\">\n<div class=\"sticky top-[calc(--spacing(9)+var(--header-height))] @w-xl\/main:top-9\">\n<div class=\"absolute end-0 bottom-0 flex h-9 items-center pe-2\">\n<div class=\"bg-token-bg-elevated-secondary text-token-text-secondary flex items-center gap-4 rounded-sm px-2 font-sans text-xs\"><\/div>\n<\/div>\n<\/div>\n<div class=\"\" dir=\"ltr\"><code class=\"whitespace-pre! language-sql\">vehicle_tracking_data (<br \/>\nid BIGSERIAL,<br \/>\ndevice_id TEXT,<br \/>\ngps_timestamp <span class=\"hljs-type\">TIMESTAMP<\/span>,<br \/>\nlatitude <span class=\"hljs-type\">DOUBLE PRECISION<\/span>,<br \/>\nlongitude <span class=\"hljs-type\">DOUBLE PRECISION<\/span>,<br \/>\nspeed <span class=\"hljs-type\">INTEGER<\/span>,<br \/>\nignition <span class=\"hljs-type\">BOOLEAN<\/span><br \/>\n)<br \/>\n<\/code><\/div>\n<\/div>\n<p data-start=\"2326\" data-end=\"2406\">This allowed PostgreSQL to do what it does best \u2014 fast writes and indexed reads.<\/p>\n<hr data-start=\"2408\" data-end=\"2411\" \/>\n<h2 data-start=\"2413\" data-end=\"2445\">\u26a1 PostgreSQL: The Unsung Hero<\/h2>\n<p data-start=\"2447\" data-end=\"2521\">PostgreSQL handled our scale surprisingly well \u2014 once we used it properly.<\/p>\n<p data-start=\"2523\" data-end=\"2549\">Key features we relied on:<\/p>\n<ul data-start=\"2550\" data-end=\"2664\">\n<li data-start=\"2550\" data-end=\"2569\">\n<p data-start=\"2552\" data-end=\"2569\">Composite indexes<\/p>\n<\/li>\n<li data-start=\"2570\" data-end=\"2590\">\n<p data-start=\"2572\" data-end=\"2590\">Descending indexes<\/p>\n<\/li>\n<li data-start=\"2591\" data-end=\"2611\">\n<p data-start=\"2593\" data-end=\"2611\">Table partitioning<\/p>\n<\/li>\n<li data-start=\"2612\" data-end=\"2632\">\n<p data-start=\"2614\" data-end=\"2632\">Materialized views<\/p>\n<\/li>\n<li data-start=\"2633\" data-end=\"2664\">\n<p data-start=\"2635\" data-end=\"2664\">Aggressive query optimization<\/p>\n<\/li>\n<\/ul>\n<p data-start=\"2666\" data-end=\"2709\">One of the most impactful indexes we added:<\/p>\n<div class=\"contain-inline-size rounded-2xl corner-superellipse\/1.1 relative bg-token-sidebar-surface-primary\">\n<div class=\"sticky top-[calc(--spacing(9)+var(--header-height))] @w-xl\/main:top-9\">\n<div class=\"absolute end-0 bottom-0 flex h-9 items-center pe-2\">\n<div class=\"bg-token-bg-elevated-secondary text-token-text-secondary flex items-center gap-4 rounded-sm px-2 font-sans text-xs\"><\/div>\n<\/div>\n<\/div>\n<div class=\"\" dir=\"ltr\"><code class=\"whitespace-pre! language-sql\"><span class=\"hljs-keyword\">CREATE<\/span> INDEX vtd_device_ts_idx<br \/>\n<span class=\"hljs-keyword\">ON<\/span> vehicle_tracking_data (device_id, gps_timestamp <span class=\"hljs-keyword\">DESC<\/span>);<br \/>\n<\/code><\/div>\n<\/div>\n<p data-start=\"2812\" data-end=\"2890\">This alone reduced our <strong data-start=\"2835\" data-end=\"2889\">latest GPS fetch time from seconds to milliseconds<\/strong>.<\/p>\n<hr data-start=\"2892\" data-end=\"2895\" \/>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-72\" src=\"http:\/\/navionyx.com\/blogs\/wp-content\/uploads\/2025\/12\/Gemini_Generated_Image_1cpgaw1cpgaw1cpg.png\" alt=\"postgresql elephant mascot\" width=\"1024\" height=\"1024\" \/><\/p>\n<hr data-start=\"3113\" data-end=\"3116\" \/>\n<h2 data-start=\"3118\" data-end=\"3170\">\ud83e\udde9 Partitioning: Making 100M Rows Feel Manageable<\/h2>\n<p data-start=\"3172\" data-end=\"3238\">Instead of one massive table, we partitioned GPS data <strong data-start=\"3226\" data-end=\"3237\">by date<\/strong>.<\/p>\n<p data-start=\"3240\" data-end=\"3287\">Why date-based partitioning works well for GPS:<\/p>\n<ul data-start=\"3288\" data-end=\"3398\">\n<li data-start=\"3288\" data-end=\"3324\">\n<p data-start=\"3290\" data-end=\"3324\">Queries usually target recent data<\/p>\n<\/li>\n<li data-start=\"3325\" data-end=\"3354\">\n<p data-start=\"3327\" data-end=\"3354\">Old data is rarely accessed<\/p>\n<\/li>\n<li data-start=\"3355\" data-end=\"3398\">\n<p data-start=\"3357\" data-end=\"3398\">Automatic partition pruning reduces scans<\/p>\n<\/li>\n<\/ul>\n<div class=\"contain-inline-size rounded-2xl corner-superellipse\/1.1 relative bg-token-sidebar-surface-primary\">\n<div class=\"sticky top-[calc(--spacing(9)+var(--header-height))] @w-xl\/main:top-9\">\n<div class=\"absolute end-0 bottom-0 flex h-9 items-center pe-2\">\n<div class=\"bg-token-bg-elevated-secondary text-token-text-secondary flex items-center gap-4 rounded-sm px-2 font-sans text-xs\"><\/div>\n<\/div>\n<\/div>\n<div class=\"\" dir=\"ltr\"><code class=\"whitespace-pre! language-sql\"><span class=\"hljs-keyword\">PARTITION<\/span> <span class=\"hljs-keyword\">BY<\/span> <span class=\"hljs-keyword\">RANGE<\/span> (gps_timestamp)<br \/>\n<\/code><\/div>\n<\/div>\n<p data-start=\"3447\" data-end=\"3455\">Results:<\/p>\n<ul data-start=\"3456\" data-end=\"3567\">\n<li data-start=\"3456\" data-end=\"3491\">\n<p data-start=\"3458\" data-end=\"3491\">Smaller index sizes per partition<\/p>\n<\/li>\n<li data-start=\"3492\" data-end=\"3522\">\n<p data-start=\"3494\" data-end=\"3522\">Faster deletes and archiving<\/p>\n<\/li>\n<li data-start=\"3523\" data-end=\"3567\">\n<p data-start=\"3525\" data-end=\"3567\">Predictable performance even as data grows<\/p>\n<\/li>\n<\/ul>\n<hr data-start=\"3569\" data-end=\"3572\" \/>\n<h3 data-start=\"3574\" data-end=\"3609\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-73\" src=\"http:\/\/navionyx.com\/blogs\/wp-content\/uploads\/2025\/12\/Gemini_Generated_Image_9cqs169cqs169cqs.png\" alt=\"postgresql time based partitions\" width=\"1024\" height=\"1024\" \/><\/h3>\n<hr data-start=\"3769\" data-end=\"3772\" \/>\n<h2 data-start=\"3774\" data-end=\"3804\">\ud83d\udcca The Dashboard Bottleneck<\/h2>\n<p data-start=\"3806\" data-end=\"3832\">Our fleet dashboard shows:<\/p>\n<ul data-start=\"3834\" data-end=\"3887\">\n<li data-start=\"3834\" data-end=\"3850\">\n<p data-start=\"3836\" data-end=\"3850\">Total vehicles<\/p>\n<\/li>\n<li data-start=\"3851\" data-end=\"3860\">\n<p data-start=\"3853\" data-end=\"3860\">Running<\/p>\n<\/li>\n<li data-start=\"3861\" data-end=\"3870\">\n<p data-start=\"3863\" data-end=\"3870\">Stopped<\/p>\n<\/li>\n<li data-start=\"3871\" data-end=\"3877\">\n<p data-start=\"3873\" data-end=\"3877\">Idle<\/p>\n<\/li>\n<li data-start=\"3878\" data-end=\"3887\">\n<p data-start=\"3880\" data-end=\"3887\">Offline<\/p>\n<\/li>\n<\/ul>\n<p data-start=\"3889\" data-end=\"3950\">Originally, each refresh recalculated this from raw GPS data.<\/p>\n<p data-start=\"3952\" data-end=\"3963\">That meant:<\/p>\n<ul data-start=\"3964\" data-end=\"4059\">\n<li data-start=\"3964\" data-end=\"3993\">\n<p data-start=\"3966\" data-end=\"3993\">Repeating expensive queries<\/p>\n<\/li>\n<li data-start=\"3994\" data-end=\"4018\">\n<p data-start=\"3996\" data-end=\"4018\">CPU-heavy aggregations<\/p>\n<\/li>\n<li data-start=\"4019\" data-end=\"4059\">\n<p data-start=\"4021\" data-end=\"4059\">Slower response times during peak load<\/p>\n<\/li>\n<\/ul>\n<p data-start=\"4061\" data-end=\"4090\">We needed a smarter approach.<\/p>\n<hr data-start=\"4092\" data-end=\"4095\" \/>\n<h2 data-start=\"4097\" data-end=\"4139\">\ud83d\udd25 Materialized Views: The Game Changer<\/h2>\n<p data-start=\"4141\" data-end=\"4216\">Instead of calculating vehicle states repeatedly, we <strong data-start=\"4194\" data-end=\"4215\">pre-computed them<\/strong>.<\/p>\n<p data-start=\"4218\" data-end=\"4261\">We introduced a <strong data-start=\"4234\" data-end=\"4255\">materialized view<\/strong> that:<\/p>\n<ul data-start=\"4262\" data-end=\"4353\">\n<li data-start=\"4262\" data-end=\"4303\">\n<p data-start=\"4264\" data-end=\"4303\">Stores the latest GPS point per vehicle<\/p>\n<\/li>\n<li data-start=\"4304\" data-end=\"4328\">\n<p data-start=\"4306\" data-end=\"4328\">Derives vehicle status<\/p>\n<\/li>\n<li data-start=\"4329\" data-end=\"4353\">\n<p data-start=\"4331\" data-end=\"4353\">Refreshes periodically<\/p>\n<\/li>\n<\/ul>\n<div class=\"contain-inline-size rounded-2xl corner-superellipse\/1.1 relative bg-token-sidebar-surface-primary\">\n<div class=\"sticky top-[calc(--spacing(9)+var(--header-height))] @w-xl\/main:top-9\">\n<div class=\"absolute end-0 bottom-0 flex h-9 items-center pe-2\">\n<div class=\"bg-token-bg-elevated-secondary text-token-text-secondary flex items-center gap-4 rounded-sm px-2 font-sans text-xs\"><\/div>\n<\/div>\n<\/div>\n<div class=\"\" dir=\"ltr\"><code class=\"whitespace-pre! language-sql\"><span class=\"hljs-keyword\">CREATE<\/span> MATERIALIZED <span class=\"hljs-keyword\">VIEW<\/span> vehicle_live_status <span class=\"hljs-keyword\">AS<\/span><br \/>\n<span class=\"hljs-keyword\">SELECT<\/span> <span class=\"hljs-keyword\">DISTINCT<\/span> <span class=\"hljs-keyword\">ON<\/span> (device_id)<br \/>\ndevice_id,<br \/>\ngps_timestamp,<br \/>\nspeed,<br \/>\nignition,<br \/>\n<span class=\"hljs-keyword\">CASE<\/span><br \/>\n<span class=\"hljs-keyword\">WHEN<\/span> gps_timestamp <span class=\"hljs-operator\">&lt;<\/span> now() <span class=\"hljs-operator\">-<\/span> <span class=\"hljs-type\">interval<\/span> <span class=\"hljs-string\">'10 minutes'<\/span> <span class=\"hljs-keyword\">THEN<\/span> <span class=\"hljs-string\">'offline'<\/span><br \/>\n<span class=\"hljs-keyword\">WHEN<\/span> speed <span class=\"hljs-operator\">&gt;<\/span> <span class=\"hljs-number\">5<\/span> <span class=\"hljs-keyword\">THEN<\/span> <span class=\"hljs-string\">'running'<\/span><br \/>\n<span class=\"hljs-keyword\">WHEN<\/span> ignition <span class=\"hljs-operator\">=<\/span> <span class=\"hljs-literal\">true<\/span> <span class=\"hljs-keyword\">THEN<\/span> <span class=\"hljs-string\">'idle'<\/span><br \/>\n<span class=\"hljs-keyword\">ELSE<\/span> <span class=\"hljs-string\">'stopped'<\/span><br \/>\n<span class=\"hljs-keyword\">END<\/span> <span class=\"hljs-keyword\">AS<\/span> status<br \/>\n<span class=\"hljs-keyword\">FROM<\/span> vehicle_tracking_data<br \/>\n<span class=\"hljs-keyword\">ORDER<\/span> <span class=\"hljs-keyword\">BY<\/span> device_id, gps_timestamp <span class=\"hljs-keyword\">DESC<\/span>;<br \/>\n<\/code><\/div>\n<\/div>\n<p data-start=\"4747\" data-end=\"4784\">Now dashboard queries became trivial:<\/p>\n<div class=\"contain-inline-size rounded-2xl corner-superellipse\/1.1 relative bg-token-sidebar-surface-primary\">\n<div class=\"sticky top-[calc(--spacing(9)+var(--header-height))] @w-xl\/main:top-9\">\n<div class=\"absolute end-0 bottom-0 flex h-9 items-center pe-2\">\n<div class=\"bg-token-bg-elevated-secondary text-token-text-secondary flex items-center gap-4 rounded-sm px-2 font-sans text-xs\"><\/div>\n<\/div>\n<\/div>\n<div class=\"\" dir=\"ltr\"><code class=\"whitespace-pre! language-sql\"><span class=\"hljs-keyword\">SELECT<\/span> status, <span class=\"hljs-built_in\">COUNT<\/span>(<span class=\"hljs-operator\">*<\/span>)<br \/>\n<span class=\"hljs-keyword\">FROM<\/span> vehicle_live_status<br \/>\n<span class=\"hljs-keyword\">GROUP<\/span> <span class=\"hljs-keyword\">BY<\/span> status;<br \/>\n<\/code><\/div>\n<\/div>\n<p data-start=\"4866\" data-end=\"4902\">\u26a1 <strong data-start=\"4868\" data-end=\"4902\">Instant results. Minimal load.<\/strong><\/p>\n<hr data-start=\"4904\" data-end=\"4907\" \/>\n<p data-start=\"5136\" data-end=\"5175\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-74\" src=\"http:\/\/navionyx.com\/blogs\/wp-content\/uploads\/2025\/12\/Screenshot-2025-12-29-at-1.28.15-PM.png\" alt=\"Navionyx app live screenshot\" width=\"1232\" height=\"692\" \/><\/p>\n<hr data-start=\"5177\" data-end=\"5180\" \/>\n<h2 data-start=\"5182\" data-end=\"5228\">\u2601\ufe0f AWS EC2: Scaling Without Overengineering<\/h2>\n<p data-start=\"5230\" data-end=\"5309\">We ran PostgreSQL on <strong data-start=\"5251\" data-end=\"5278\">dedicated EC2 instances<\/strong>, tuned for database workloads.<\/p>\n<p data-start=\"5311\" data-end=\"5325\">Key decisions:<\/p>\n<ul data-start=\"5326\" data-end=\"5437\">\n<li data-start=\"5326\" data-end=\"5349\">\n<p data-start=\"5328\" data-end=\"5349\">High-RAM EC2 instance<\/p>\n<\/li>\n<li data-start=\"5350\" data-end=\"5370\">\n<p data-start=\"5352\" data-end=\"5370\">SSD-backed storage<\/p>\n<\/li>\n<li data-start=\"5371\" data-end=\"5404\">\n<p data-start=\"5373\" data-end=\"5404\">Proper PostgreSQL memory tuning<\/p>\n<\/li>\n<li data-start=\"5405\" data-end=\"5437\">\n<p data-start=\"5407\" data-end=\"5437\">Connection pooling (PgBouncer)<\/p>\n<\/li>\n<\/ul>\n<p data-start=\"5439\" data-end=\"5483\">Instead of blindly scaling horizontally, we:<\/p>\n<ul data-start=\"5484\" data-end=\"5576\">\n<li data-start=\"5484\" data-end=\"5509\">\n<p data-start=\"5486\" data-end=\"5509\">Optimized queries first<\/p>\n<\/li>\n<li data-start=\"5510\" data-end=\"5536\">\n<p data-start=\"5512\" data-end=\"5536\">Reduced unnecessary load<\/p>\n<\/li>\n<li data-start=\"5537\" data-end=\"5576\">\n<p data-start=\"5539\" data-end=\"5576\">Scaled only when metrics justified it<\/p>\n<\/li>\n<\/ul>\n<hr data-start=\"5578\" data-end=\"5581\" \/>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-75\" src=\"http:\/\/navionyx.com\/blogs\/wp-content\/uploads\/2025\/12\/Gemini_Generated_Image_sz7b1ysz7b1ysz7b.png\" alt=\"AWS EC2 cloud architecture diagram\" width=\"1024\" height=\"1024\" \/><\/p>\n<p>&nbsp;<\/p>\n<hr data-start=\"5795\" data-end=\"5798\" \/>\n<h2 data-start=\"5800\" data-end=\"5842\">\ud83d\udce6 Handling GBs of GPS Data Efficiently<\/h2>\n<p data-start=\"5844\" data-end=\"5872\">Why this architecture works:<\/p>\n<ul data-start=\"5873\" data-end=\"6049\">\n<li data-start=\"5873\" data-end=\"5911\">\n<p data-start=\"5875\" data-end=\"5911\">Append-only writes keep inserts fast<\/p>\n<\/li>\n<li data-start=\"5912\" data-end=\"5962\">\n<p data-start=\"5914\" data-end=\"5962\">Indexes optimize the most common access patterns<\/p>\n<\/li>\n<li data-start=\"5963\" data-end=\"5998\">\n<p data-start=\"5965\" data-end=\"5998\">Partitioning keeps queries scoped<\/p>\n<\/li>\n<li data-start=\"5999\" data-end=\"6049\">\n<p data-start=\"6001\" data-end=\"6049\">Materialized views eliminate runtime computation<\/p>\n<\/li>\n<\/ul>\n<p data-start=\"6051\" data-end=\"6095\">PostgreSQL proved it can comfortably handle:<\/p>\n<ul data-start=\"6096\" data-end=\"6169\">\n<li data-start=\"6096\" data-end=\"6126\">\n<p data-start=\"6098\" data-end=\"6126\">Hundreds of millions of rows<\/p>\n<\/li>\n<li data-start=\"6127\" data-end=\"6146\">\n<p data-start=\"6129\" data-end=\"6146\">Continuous writes<\/p>\n<\/li>\n<li data-start=\"6147\" data-end=\"6169\">\n<p data-start=\"6149\" data-end=\"6169\">Real-time dashboards<\/p>\n<\/li>\n<\/ul>\n<hr data-start=\"6171\" data-end=\"6174\" \/>\n<h2 data-start=\"6176\" data-end=\"6195\">\ud83e\udde0 Key Takeaways<\/h2>\n<p data-start=\"6197\" data-end=\"6242\">If you\u2019re building a GPS or telemetry system:<\/p>\n<ul data-start=\"6244\" data-end=\"6475\">\n<li data-start=\"6244\" data-end=\"6297\">\n<p data-start=\"6246\" data-end=\"6297\"><strong data-start=\"6246\" data-end=\"6297\">Model data for access patterns, not just schema<\/strong><\/p>\n<\/li>\n<li data-start=\"6298\" data-end=\"6333\">\n<p data-start=\"6300\" data-end=\"6333\">Index for \u201clatest record\u201d queries<\/p>\n<\/li>\n<li data-start=\"6334\" data-end=\"6373\">\n<p data-start=\"6336\" data-end=\"6373\">Use materialized views for dashboards<\/p>\n<\/li>\n<li data-start=\"6374\" data-end=\"6422\">\n<p data-start=\"6376\" data-end=\"6422\">Partition early \u2014 not after performance breaks<\/p>\n<\/li>\n<li data-start=\"6423\" data-end=\"6475\">\n<p data-start=\"6425\" data-end=\"6475\">PostgreSQL is far more powerful than people assume<\/p>\n<\/li>\n<\/ul>\n<p data-start=\"6477\" data-end=\"6566\">With the right design, you don\u2019t need exotic databases \u2014 just <strong data-start=\"6539\" data-end=\"6565\">well-used fundamentals<\/strong>.<\/p>\n<hr data-start=\"6568\" data-end=\"6571\" \/>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-76\" src=\"http:\/\/navionyx.com\/blogs\/wp-content\/uploads\/2025\/12\/Gemini_Generated_Image_5njzmf5njzmf5njz.png\" alt=\"High-performance GPS tracking system\" width=\"1408\" height=\"768\" \/><\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>When we started building Navionyx, our GPS tracking platform, everything looked simple.A few vehicles, a few thousand location points, and basic tracking dashboards. Fast forward a few months \u2014 we were dealing with: 100+ million GPS records GBs of time-series data Thousands of devices sending data continuously A real-time dashboard that users expected to load [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":78,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[4],"class_list":["post-63","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-news","tag-blogs"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.2 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>\ud83d\ude80 How We Scaled Navionyx to Handle 100M+ GPS Records Using PostgreSQL &amp; AWS EC2<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/navionyx.com\/blogs\/how-we-scaled-navionyx-to-handle-100m-gps-records-using-postgresql-aws-ec2\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"\ud83d\ude80 How We Scaled Navionyx to Handle 100M+ GPS Records Using PostgreSQL &amp; AWS EC2\" \/>\n<meta property=\"og:description\" content=\"When we started building Navionyx, our GPS tracking platform, everything looked simple.A few vehicles, a few thousand location points, and basic tracking dashboards. Fast forward a few months \u2014 we were dealing with: 100+ million GPS records GBs of time-series data Thousands of devices sending data continuously A real-time dashboard that users expected to load [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/navionyx.com\/blogs\/how-we-scaled-navionyx-to-handle-100m-gps-records-using-postgresql-aws-ec2\/\" \/>\n<meta property=\"og:site_name\" content=\"Navionyx - News\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/navionyx\/\" \/>\n<meta property=\"article:published_time\" content=\"2025-12-29T13:50:24+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2025-12-29T21:22:31+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/navionyx.com\/blogs\/wp-content\/uploads\/2025\/12\/ChatGPT-Image-Dec-30-2025-02_50_48-AM.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"1536\" \/>\n\t<meta property=\"og:image:height\" content=\"1024\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"Ajay Tribhuwan\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Ajay Tribhuwan\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/navionyx.com\/blogs\/how-we-scaled-navionyx-to-handle-100m-gps-records-using-postgresql-aws-ec2\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/navionyx.com\/blogs\/how-we-scaled-navionyx-to-handle-100m-gps-records-using-postgresql-aws-ec2\/\"},\"author\":{\"name\":\"Ajay Tribhuwan\",\"@id\":\"https:\/\/navionyx.com\/blogs\/#\/schema\/person\/bf73eb55ae3cb4a95c153ea29739668f\"},\"headline\":\"\ud83d\ude80 How We Scaled Navionyx to Handle 100M+ GPS Records Using PostgreSQL &#038; AWS EC2\",\"datePublished\":\"2025-12-29T13:50:24+00:00\",\"dateModified\":\"2025-12-29T21:22:31+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/navionyx.com\/blogs\/how-we-scaled-navionyx-to-handle-100m-gps-records-using-postgresql-aws-ec2\/\"},\"wordCount\":548,\"image\":{\"@id\":\"https:\/\/navionyx.com\/blogs\/how-we-scaled-navionyx-to-handle-100m-gps-records-using-postgresql-aws-ec2\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/navionyx.com\/blogs\/wp-content\/uploads\/2025\/12\/ChatGPT-Image-Dec-30-2025-02_50_48-AM.jpg\",\"keywords\":[\"blogs\"],\"articleSection\":[\"news\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/navionyx.com\/blogs\/how-we-scaled-navionyx-to-handle-100m-gps-records-using-postgresql-aws-ec2\/\",\"url\":\"https:\/\/navionyx.com\/blogs\/how-we-scaled-navionyx-to-handle-100m-gps-records-using-postgresql-aws-ec2\/\",\"name\":\"\ud83d\ude80 How We Scaled Navionyx to Handle 100M+ GPS Records Using PostgreSQL & AWS EC2\",\"isPartOf\":{\"@id\":\"https:\/\/navionyx.com\/blogs\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/navionyx.com\/blogs\/how-we-scaled-navionyx-to-handle-100m-gps-records-using-postgresql-aws-ec2\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/navionyx.com\/blogs\/how-we-scaled-navionyx-to-handle-100m-gps-records-using-postgresql-aws-ec2\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/navionyx.com\/blogs\/wp-content\/uploads\/2025\/12\/ChatGPT-Image-Dec-30-2025-02_50_48-AM.jpg\",\"datePublished\":\"2025-12-29T13:50:24+00:00\",\"dateModified\":\"2025-12-29T21:22:31+00:00\",\"author\":{\"@id\":\"https:\/\/navionyx.com\/blogs\/#\/schema\/person\/bf73eb55ae3cb4a95c153ea29739668f\"},\"breadcrumb\":{\"@id\":\"https:\/\/navionyx.com\/blogs\/how-we-scaled-navionyx-to-handle-100m-gps-records-using-postgresql-aws-ec2\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/navionyx.com\/blogs\/how-we-scaled-navionyx-to-handle-100m-gps-records-using-postgresql-aws-ec2\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/navionyx.com\/blogs\/how-we-scaled-navionyx-to-handle-100m-gps-records-using-postgresql-aws-ec2\/#primaryimage\",\"url\":\"https:\/\/navionyx.com\/blogs\/wp-content\/uploads\/2025\/12\/ChatGPT-Image-Dec-30-2025-02_50_48-AM.jpg\",\"contentUrl\":\"https:\/\/navionyx.com\/blogs\/wp-content\/uploads\/2025\/12\/ChatGPT-Image-Dec-30-2025-02_50_48-AM.jpg\",\"width\":1536,\"height\":1024,\"caption\":\"How We Scaled Navionyx to Handle 100M+ GPS Records Using PostgreSQL & AWS EC2\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/navionyx.com\/blogs\/how-we-scaled-navionyx-to-handle-100m-gps-records-using-postgresql-aws-ec2\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/navionyx.com\/blogs\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"\ud83d\ude80 How We Scaled Navionyx to Handle 100M+ GPS Records Using PostgreSQL &#038; AWS EC2\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/navionyx.com\/blogs\/#website\",\"url\":\"https:\/\/navionyx.com\/blogs\/\",\"name\":\"Navionyx\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/navionyx.com\/blogs\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/navionyx.com\/blogs\/#\/schema\/person\/bf73eb55ae3cb4a95c153ea29739668f\",\"name\":\"Ajay Tribhuwan\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/0ba8e0f56aa90b4b694da4b4a9224c8ebfd4f90f7c15da0c41eaf211b90b347b?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0ba8e0f56aa90b4b694da4b4a9224c8ebfd4f90f7c15da0c41eaf211b90b347b?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0ba8e0f56aa90b4b694da4b4a9224c8ebfd4f90f7c15da0c41eaf211b90b347b?s=96&d=mm&r=g\",\"caption\":\"Ajay Tribhuwan\"}}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"\ud83d\ude80 How We Scaled Navionyx to Handle 100M+ GPS Records Using PostgreSQL & AWS EC2","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/navionyx.com\/blogs\/how-we-scaled-navionyx-to-handle-100m-gps-records-using-postgresql-aws-ec2\/","og_locale":"en_US","og_type":"article","og_title":"\ud83d\ude80 How We Scaled Navionyx to Handle 100M+ GPS Records Using PostgreSQL & AWS EC2","og_description":"When we started building Navionyx, our GPS tracking platform, everything looked simple.A few vehicles, a few thousand location points, and basic tracking dashboards. Fast forward a few months \u2014 we were dealing with: 100+ million GPS records GBs of time-series data Thousands of devices sending data continuously A real-time dashboard that users expected to load [&hellip;]","og_url":"https:\/\/navionyx.com\/blogs\/how-we-scaled-navionyx-to-handle-100m-gps-records-using-postgresql-aws-ec2\/","og_site_name":"Navionyx - News","article_publisher":"https:\/\/www.facebook.com\/navionyx\/","article_published_time":"2025-12-29T13:50:24+00:00","article_modified_time":"2025-12-29T21:22:31+00:00","og_image":[{"width":1536,"height":1024,"url":"https:\/\/navionyx.com\/blogs\/wp-content\/uploads\/2025\/12\/ChatGPT-Image-Dec-30-2025-02_50_48-AM.jpg","type":"image\/jpeg"}],"author":"Ajay Tribhuwan","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Ajay Tribhuwan","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/navionyx.com\/blogs\/how-we-scaled-navionyx-to-handle-100m-gps-records-using-postgresql-aws-ec2\/#article","isPartOf":{"@id":"https:\/\/navionyx.com\/blogs\/how-we-scaled-navionyx-to-handle-100m-gps-records-using-postgresql-aws-ec2\/"},"author":{"name":"Ajay Tribhuwan","@id":"https:\/\/navionyx.com\/blogs\/#\/schema\/person\/bf73eb55ae3cb4a95c153ea29739668f"},"headline":"\ud83d\ude80 How We Scaled Navionyx to Handle 100M+ GPS Records Using PostgreSQL &#038; AWS EC2","datePublished":"2025-12-29T13:50:24+00:00","dateModified":"2025-12-29T21:22:31+00:00","mainEntityOfPage":{"@id":"https:\/\/navionyx.com\/blogs\/how-we-scaled-navionyx-to-handle-100m-gps-records-using-postgresql-aws-ec2\/"},"wordCount":548,"image":{"@id":"https:\/\/navionyx.com\/blogs\/how-we-scaled-navionyx-to-handle-100m-gps-records-using-postgresql-aws-ec2\/#primaryimage"},"thumbnailUrl":"https:\/\/navionyx.com\/blogs\/wp-content\/uploads\/2025\/12\/ChatGPT-Image-Dec-30-2025-02_50_48-AM.jpg","keywords":["blogs"],"articleSection":["news"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/navionyx.com\/blogs\/how-we-scaled-navionyx-to-handle-100m-gps-records-using-postgresql-aws-ec2\/","url":"https:\/\/navionyx.com\/blogs\/how-we-scaled-navionyx-to-handle-100m-gps-records-using-postgresql-aws-ec2\/","name":"\ud83d\ude80 How We Scaled Navionyx to Handle 100M+ GPS Records Using PostgreSQL & AWS EC2","isPartOf":{"@id":"https:\/\/navionyx.com\/blogs\/#website"},"primaryImageOfPage":{"@id":"https:\/\/navionyx.com\/blogs\/how-we-scaled-navionyx-to-handle-100m-gps-records-using-postgresql-aws-ec2\/#primaryimage"},"image":{"@id":"https:\/\/navionyx.com\/blogs\/how-we-scaled-navionyx-to-handle-100m-gps-records-using-postgresql-aws-ec2\/#primaryimage"},"thumbnailUrl":"https:\/\/navionyx.com\/blogs\/wp-content\/uploads\/2025\/12\/ChatGPT-Image-Dec-30-2025-02_50_48-AM.jpg","datePublished":"2025-12-29T13:50:24+00:00","dateModified":"2025-12-29T21:22:31+00:00","author":{"@id":"https:\/\/navionyx.com\/blogs\/#\/schema\/person\/bf73eb55ae3cb4a95c153ea29739668f"},"breadcrumb":{"@id":"https:\/\/navionyx.com\/blogs\/how-we-scaled-navionyx-to-handle-100m-gps-records-using-postgresql-aws-ec2\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/navionyx.com\/blogs\/how-we-scaled-navionyx-to-handle-100m-gps-records-using-postgresql-aws-ec2\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/navionyx.com\/blogs\/how-we-scaled-navionyx-to-handle-100m-gps-records-using-postgresql-aws-ec2\/#primaryimage","url":"https:\/\/navionyx.com\/blogs\/wp-content\/uploads\/2025\/12\/ChatGPT-Image-Dec-30-2025-02_50_48-AM.jpg","contentUrl":"https:\/\/navionyx.com\/blogs\/wp-content\/uploads\/2025\/12\/ChatGPT-Image-Dec-30-2025-02_50_48-AM.jpg","width":1536,"height":1024,"caption":"How We Scaled Navionyx to Handle 100M+ GPS Records Using PostgreSQL & AWS EC2"},{"@type":"BreadcrumbList","@id":"https:\/\/navionyx.com\/blogs\/how-we-scaled-navionyx-to-handle-100m-gps-records-using-postgresql-aws-ec2\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/navionyx.com\/blogs\/"},{"@type":"ListItem","position":2,"name":"\ud83d\ude80 How We Scaled Navionyx to Handle 100M+ GPS Records Using PostgreSQL &#038; AWS EC2"}]},{"@type":"WebSite","@id":"https:\/\/navionyx.com\/blogs\/#website","url":"https:\/\/navionyx.com\/blogs\/","name":"Navionyx","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/navionyx.com\/blogs\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/navionyx.com\/blogs\/#\/schema\/person\/bf73eb55ae3cb4a95c153ea29739668f","name":"Ajay Tribhuwan","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/0ba8e0f56aa90b4b694da4b4a9224c8ebfd4f90f7c15da0c41eaf211b90b347b?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/0ba8e0f56aa90b4b694da4b4a9224c8ebfd4f90f7c15da0c41eaf211b90b347b?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0ba8e0f56aa90b4b694da4b4a9224c8ebfd4f90f7c15da0c41eaf211b90b347b?s=96&d=mm&r=g","caption":"Ajay Tribhuwan"}}]}},"_links":{"self":[{"href":"https:\/\/navionyx.com\/blogs\/wp-json\/wp\/v2\/posts\/63","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/navionyx.com\/blogs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/navionyx.com\/blogs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/navionyx.com\/blogs\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/navionyx.com\/blogs\/wp-json\/wp\/v2\/comments?post=63"}],"version-history":[{"count":5,"href":"https:\/\/navionyx.com\/blogs\/wp-json\/wp\/v2\/posts\/63\/revisions"}],"predecessor-version":[{"id":77,"href":"https:\/\/navionyx.com\/blogs\/wp-json\/wp\/v2\/posts\/63\/revisions\/77"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/navionyx.com\/blogs\/wp-json\/wp\/v2\/media\/78"}],"wp:attachment":[{"href":"https:\/\/navionyx.com\/blogs\/wp-json\/wp\/v2\/media?parent=63"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/navionyx.com\/blogs\/wp-json\/wp\/v2\/categories?post=63"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/navionyx.com\/blogs\/wp-json\/wp\/v2\/tags?post=63"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}