In case you don't know the back story, in order for me to play with radios and label it "work," I've created a PostgreSQL database running on AWS Aurora. The db is fed from API calls to aprs.fi through Lambda functions on AWS. Some of the DDL & code is mine. Some is from Claude. Neither of us paid much attention to indexing when we were putting things together. I threw my own personal best practices out the window and just went for a "make it work, worry about everything else (except security) later," style of coding.

Yeah, we're starting to pay for that little decision.

There isn't that much data in the database. Only when I'm outside with the radio (Bridgecom Systems Maverick, the latest & greatest DMR handheld on the market) and have it set to receive GPS signals and to transmit APRS signals that anything can go in to the database. Further, in order to avoid limits, and costs, I'm only calling for data every three minutes. As of this writing, there are only 400+ individual points stored in the system so far. Yet, queries are already running a little slower. Nothing radical, but I can tell.

Time to spend some time and get a little tuning done.

A Query and an Explain Plan

I'm writing this series of blog posts for several reasons. The biggest is to focus on how well, for the most part, running PostgreSQL on AWS Aurora just isn't all that different from running it yourself. While there are a whole slew of reasons for answering the question "Why PostgreSQL?" one of my favorite is pretty simple: extensions. There are a whole slew of extensions available, and it's not hard to create your own (you can make 'em out of just SQL). However, with my background in insurance, and more specifically working for a company that prided themselves on engineering and testing as well as any kind of actuarial calculations, we frequently fell back on that old real estate saw: location, location, location. When we're talking global information systems in PostgreSQL, then we can lean hard on postGIS to get the job done. In the first post I showed how easy it was to get postGIS up and running inside Aurora. Now, I'd like to show a little more functionality, specific to postGIS, but, also, specifically running inside Aurora as if it was just on your own servers.

In my last post on this topic I showed how to set up Aurora to capture explain plans in PostgreSQL. Even with my rudimentary understanding of query tuning within PostgreSQL, there are issues, not uncommon to SQL Server, with the view, reports.locations_visited:

CREATE OR REPLACE VIEW reports.locations_visited AS
 SELECT DISTINCT ll.city,
    ll.state,
    ll.country,
    count(DISTINCT li.location_info_id) AS visit_count,
    min(li.lasttime) AS first_visit,
    max(li.lasttime) AS last_visit
   FROM reports.location_info li
     LEFT JOIN reports.location_lookup ll ON st_dwithin(ll.center_point, li.gis_point, (ll.radius_km * 1000::numeric)::double precision)
  WHERE li.loc_name = 'KC1KCE-8'::text AND ll.city IS NOT NULL
  GROUP BY ll.city, ll.state, ll.country
  ORDER BY (count(DISTINCT li.location_info_id)) DESC, ll.city;

If you're a PostgreSQL expert, I'm pretty sure you're already spotting some of the issues. Heck, I can see it pretty quickly myself. Remember, I relied on the AI to generate quite a few of these queries (not all). This is one of them. Spot it yet? How about, using DISTINCT and GROUP BY together. Won't aggregating the data, grouping it, make for a pretty "distinct" set of data? OK, but we're guessing here, right? First, we can test pretty quickly, will the results be the same? Running this query with and without the distinct, the data comes back:

Creek County Oklahoma United States 283 2026-02-13 00:21:21+00 2026-03-08 17:13:36+00
Sapulpa Oklahoma United States 42 2026-02-14 00:58:49+00 2026-03-07 19:58:55+00
Jenks Oklahoma United States 28 2026-02-18 23:13:48+00 2026-03-07 19:49:25+00
Tulsa Oklahoma United States 26 2026-02-22 00:13:37+00 2026-03-07 19:41:23+00
Hanau Hessen Deutschland 19 2026-03-01 11:16:46+00 2026-03-04 07:17:20+00
Bixby Oklahoma United States 1 2026-02-22 00:25:35+00 2026-02-22 00:25:35+00

But how do we see if there is any changes on performance in AWS Aurora PostgreSQL? Same as anywhere else in PostgreSQL. Sure, I could set up pg_stats_statements and query that. However, remember, to a very large extent, running Postgres on Aurora is the same as running it locally. I can easily see, and measure, two versions of the query to see the differences between like this:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT DISTINCT ll.city,
    ll.state,
    ll.country,
    count(DISTINCT li.location_info_id) AS visit_count,
    min(li.lasttime) AS first_visit,
    max(li.lasttime) AS last_visit
FROM reports.location_info li
LEFT JOIN reports.location_lookup ll
    ON ST_DWithin(ll.center_point, li.gis_point, (ll.radius_km * 1000)::double precision)
WHERE li.loc_name = 'KC1KCE-8'
  AND ll.city IS NOT NULL
GROUP BY ll.city, ll.state, ll.country
ORDER BY count(DISTINCT li.location_info_id) DESC, ll.city;

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT ll.city,
    ll.state,
    ll.country,
    count(DISTINCT li.location_info_id) AS visit_count,
    min(li.lasttime) AS first_visit,
    max(li.lasttime) AS last_visit
FROM reports.location_info li
LEFT JOIN reports.location_lookup ll
    ON ST_DWithin(ll.center_point, li.gis_point, (ll.radius_km * 1000)::double precision)
WHERE li.loc_name = 'KC1KCE-8'
  AND ll.city IS NOT NULL
GROUP BY ll.city, ll.state, ll.country
ORDER BY count(DISTINCT li.location_info_id) DESC, ll.city;

With the following output:

Unique (cost=1580753.15..1580755.36 rows=126 width=120) (actual time=106.086..106.091 rows=8 loops=1)
Buffers: shared hit=94
-> Sort (cost=1580753.15..1580753.47 rows=126 width=120) (actual time=106.085..106.086 rows=8 loops=1)
Sort Key: (count(DISTINCT li.location_info_id)) DESC, ll.city, ll.state, ll.country, (min(li.lasttime)), (max(li.lasttime))
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=94
-> GroupAggregate (cost=1580745.29..1580748.76 rows=126 width=120) (actual time=104.200..104.331 rows=8 loops=1)
Group Key: ll.city, ll.state, ll.country
Buffers: shared hit=85
-> Sort (cost=1580745.29..1580745.61 rows=126 width=112) (actual time=104.189..104.218 rows=525 loops=1)
Sort Key: ll.city, ll.state, ll.country, li.location_info_id
Sort Method: quicksort Memory: 60kB
Buffers: shared hit=85
-> Nested Loop (cost=0.00..1580740.89 rows=126 width=112) (actual time=83.790..100.618 rows=525 loops=1)
Join Filter: st_dwithin(ll.center_point, li.gis_point, ((ll.radius_km * '1000'::numeric))::double precision, true)
Rows Removed by Join Filter: 8606
Buffers: shared hit=85
-> Seq Scan on location_info li (cost=0.00..17.12 rows=410 width=48) (actual time=0.021..0.132 rows=397 loops=1)
Filter: (loc_name = 'KC1KCE-8'::text)
Buffers: shared hit=12
-> Materialize (cost=0.00..14.64 rows=308 width=142) (actual time=0.000..0.003 rows=23 loops=397)
Buffers: shared hit=1
-> Seq Scan on location_lookup ll (cost=0.00..13.10 rows=308 width=142) (actual time=0.005..0.017 rows=23 loops=1)
Filter: (city IS NOT NULL)
Buffers: shared hit=1
Planning:
Buffers: shared hit=262
Planning Time: 40.471 ms
Execution Time: 110.449 ms

Sort (cost=1580753.15..1580753.47 rows=126 width=120) (actual time=17.882..17.884 rows=8 loops=1)
Sort Key: (count(DISTINCT li.location_info_id)) DESC, ll.city
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=13
-> GroupAggregate (cost=1580745.29..1580748.76 rows=126 width=120) (actual time=17.731..17.872 rows=8 loops=1)
Group Key: ll.city, ll.state, ll.country
Buffers: shared hit=13
-> Sort (cost=1580745.29..1580745.61 rows=126 width=112) (actual time=17.723..17.756 rows=525 loops=1)
Sort Key: ll.city, ll.state, ll.country, li.location_info_id
Sort Method: quicksort Memory: 60kB
Buffers: shared hit=13
-> Nested Loop (cost=0.00..1580740.89 rows=126 width=112) (actual time=0.044..16.959 rows=525 loops=1)
Join Filter: st_dwithin(ll.center_point, li.gis_point, ((ll.radius_km * '1000'::numeric))::double precision, true)
Rows Removed by Join Filter: 8606
Buffers: shared hit=13
-> Seq Scan on location_info li (cost=0.00..17.12 rows=410 width=48) (actual time=0.014..0.127 rows=397 loops=1)
Filter: (loc_name = 'KC1KCE-8'::text)
Buffers: shared hit=12
-> Materialize (cost=0.00..14.64 rows=308 width=142) (actual time=0.000..0.002 rows=23 loops=397)
Buffers: shared hit=1
-> Seq Scan on location_lookup ll (cost=0.00..13.10 rows=308 width=142) (actual time=0.003..0.008 rows=23 loops=1)
Filter: (city IS NOT NULL)
Buffers: shared hit=1
Planning:
Buffers: shared hit=3
Planning Time: 0.806 ms
Execution Time: 17.944 ms

Sure, we can examine what's happening within the execution plan, and we will, but let's focus on just a few points first. We went from 262 buffers hit to 3. I don't know about you, but I'm already excited. Execution time went from 110ms to just shy of 18ms. An 83% increase, or, if you will, 6 times faster. However you want to frame it, just that small change in code made an enormous difference.

There's more.  I'm only, ever, going to be showing a single call sign, so filtering for 'KC1KCE-8' is never going to eliminate rows from our data set, so I can safely remove that. Also, the  clause 'll.City IS NOT NULL' causes our OUTER JOIN to be an INNER JOIN. That's not necessarily going to improve things, but why not make things more clear in the code. Resulting in this query, which, it so happens, shaved another millisecond off the execution time to be about 17ms instead of 18ms:

SELECT ll.city,
    ll.state,
    ll.country,
    count(DISTINCT li.location_info_id) AS visit_count,
    min(li.lasttime) AS first_visi
    max(li.lasttime) AS last_visit
FROM reports.location_info li
JOIN reports.location_lookup ll
    ON ST_DWithin(ll.center_point, li.gis_point, (ll.radius_km * 1000)::double precision)
WHERE ll.city IS NOT NULL
GROUP BY ll.city, ll.state, ll.country
ORDER BY count(DISTINCT li.location_info_id) DESC, ll.city;

Is that it?

Almost. One other point worth noting, there isn't currently an index on the reports.location_info table. Because of this, our JOIN criteria is scanning that table, 'Join Filter: st_dwithin(ll.center_point, li.gis_point, ((ll.radius_km * '1000'::numeric))::double precision, true).' With only 400 or so rows, no big deal. As that data set grows, we're absolutely going to need an index. I'm going to go ahead and create one now:

CREATE INDEX idx_location_info_gis_point
    ON reports.location_info USING GIST (gis_point);

I said it's probably not a big deal for performance, yet, but adding the index dropped the performance time another millisecond, even though the buffers hit went up to 28. Here's the new explain plan:

Sort (cost=4032.08..4032.39 rows=122 width=120) (actual time=10.776..10.779 rows=8 loops=1)
Sort Key: (count(DISTINCT li.location_info_id)) DESC, ll.city
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=149
-> GroupAggregate (cost=4024.50..4027.85 rows=122 width=120) (actual time=9.810..9.948 rows=8 loops=1)
Group Key: ll.city, ll.state, ll.country
Buffers: shared hit=149
-> Sort (cost=4024.50..4024.80 rows=122 width=112) (actual time=9.801..9.831 rows=525 loops=1)
Sort Key: ll.city, ll.state, ll.country, li.location_info_id
Sort Method: quicksort Memory: 60kB
Buffers: shared hit=149
-> Nested Loop (cost=0.27..4020.27 rows=122 width=112) (actual time=2.728..6.435 rows=525 loops=1)
Buffers: shared hit=149
-> Seq Scan on location_lookup ll (cost=0.00..13.10 rows=308 width=142) (actual time=0.018..0.027 rows=23 loops=1)
Filter: (city IS NOT NULL)
Buffers: shared hit=1
-> Index Scan using idx_location_info_gis_point on location_info li (cost=0.27..13.00 rows=1 width=48) (actual time=0.135..0.259 rows=23 loops=23)
Index Cond: (gis_point && _st_expand(ll.center_point, ((ll.radius_km * '1000'::numeric))::double precision))
Filter: st_dwithin(ll.center_point, gis_point, ((ll.radius_km * '1000'::numeric))::double precision, true)
Rows Removed by Filter: 15
Buffers: shared hit=148
Planning:
Buffers: shared hit=28
Planning Time: 10.842 ms
Execution Time: 15.845 ms

I'm still learning how to properly read these, so bear with my explanation. Instead of having to materialize data for the join and then do a sequence scan, the optimizer can now use the index. It means a few more reads, but overall better performance, especially as this scales in size.

I'm sure there are more tuning opportunities to be had, but I'm going to leave it for that at the moment.

Conclusion

I kind of keep harping on this, but it's an important point. If I was tuning this locally, no involvement with Aurora, the steps, tools and methods I'd use are the same. That's honestly the key point. If you know, or are learning, PostgreSQL, but now you have to migrate to Aurora, I've got good news. Your knowledge is going to transfer, mostly. Sure, there are distinct differences. However, to a very large degree, this is still just PostgreSQL. Poor code choices, as the AI made when it first created this query, will lead to poor performance on Aurora, same as locally. Getting good indexes in place will help performance, whether you're local or on Aurora.

If you've spotted any other potential improvements, let me know in the comments. Anything you'd like to see added to this APRS project, also let me know in the comments. I've got a few ideas for some additional changes, additions, and probably a fix or two (not everything is working correctly, see if you can spot it). That means I'll be back here with another post or two on this project.