I recently wrote about a project I created on AWS Aurora PostgreSQL where I'm capturing APRS data from a radio. I focused on the ease of use, getting a database, some Lambda Functions, and a few schedulers working together with a web page. It was easy. However, I'd like to focus on a slightly different area now, performance.

Sure, I could think of this as just a hobby or demonstration database and who cares about performance. However, I'd like to illustrate what can be done with PostgreSQL and postGIS running in Aurora to see query behaviors and improve performance.

Monitoring on AWS

Yes, of course, my immediate answer to monitoring PostgreSQL on AWS Aurora is to use Redgate Monitor. However, let's save that article for another day. Instead, let's explore what we have in the way of native monitoring in AWS. Right on the console for Aurora, you can see the Monitoring tab:

This is pretty fundamental stuff. CPU Utilization, Commit Throughput, DBLoad (Active sessions), etc. It gives you the basics of what you need to know about a database. It doesn't show much in the way of query information though. For that, we need to go where there are actually a few more details, CloudWatch. There, in addition to all that information above, in more detail, we can see Top Waits and Top Queries:

Unless I onboard "Database Insights Advanced" to this database, this is about all I get. However, for our purposes, this can be enough. I can see which queries are getting called and their average latency. Would I like more from my monitoring? Of course. That's why you probably would enable the advanced Insights (or better still, put Redgate Monitor to work). That does come with a cost though. For the moment, let's keep clear of added costs.

So, is that it? That's my tooling available for monitoring (again, before we get into paid behaviors on AWS)?

Of course not. Look, this is PostgreSQL. What if, go with me here, we set up auto_explain and included nesting? Ah, then you'd see this:

2026-02-24 12:20:34 UTC:153.66.235.55(41278):gis_admin@aprs_reporter:[36757]:LOG:  duration: 26.943 ms  plan:
	Query Text: WITH distinct_coords AS (
	        SELECT 
	            li.lat,
	            li.lon,
	            COUNT(*) as point_count
	        FROM reports.location_info li
	        WHERE li.loc_name = 'KC1KCE-8'
	        GROUP BY li.lat, li.lon
	    )
	    SELECT 
	        dc.lat,
	        dc.lon,
	        dc.point_count
	    FROM distinct_coords dc
	    WHERE NOT EXISTS (
	        SELECT 1 
	        FROM reports.location_lookup ll
	        WHERE ST_DWithin(
	            ll.center_point,
	            ST_SetSRID(ST_MakePoint(dc.lon, dc.lat), 4326),
	            ll.radius_km * 1000
	        )
	    )
	    ORDER BY dc.point_count DESC
	    LIMIT p_limit
	Limit  (cost=203989.26..203989.39 rows=52 width=23) (actual time=26.155..26.157 rows=0 loops=1)
	  Buffers: shared hit=119
	  ->  Sort  (cost=203989.26..203989.39 rows=52 width=23) (actual time=26.154..26.155 rows=0 loops=1)
	        Sort Key: (count(*)) DESC
	        Sort Method: quicksort  Memory: 25kB
	        Buffers: shared hit=119
	        ->  Nested Loop Anti Join  (cost=15.08..203987.77 rows=52 width=23) (actual time=26.117..26.118 rows=0 loops=1)
	              Join Filter: st_dwithin(ll.center_point, (st_setsrid(st_makepoint((li.lon)::double precision, (li.lat)::double precision), 4326))::geography, ((ll.radius_km * '1000'::numeric))::double precision, true)
	              Rows Removed by Join Filter: 155
	              Buffers: shared hit=116
	              ->  HashAggregate  (cost=15.08..15.60 rows=52 width=23) (actual time=0.188..0.208 rows=78 loops=1)
	                    Group Key: li.lat, li.lon
	                    Batches: 1  Memory Usage: 40kB
	                    Buffers: shared hit=9
	                    ->  Seq Scan on location_info li  (cost=0.00..12.80 rows=304 width=15) (actual time=0.013..0.073 rows=294 loops=1)
	                          Filter: (loc_name = 'KC1KCE-8'::text)
	                          Buffers: shared hit=9
	              ->  Materialize  (cost=0.00..14.65 rows=310 width=46) (actual time=0.000..0.000 rows=3 loops=78)
	                    Buffers: shared hit=1
	                    ->  Seq Scan on location_lookup ll  (cost=0.00..13.10 rows=310 width=46) (actual time=0.003..0.008 rows=12 loops=1)
	                          Buffers: shared hit=1
	Settings: max_parallel_workers = '16', maintenance_io_concurrency = '1', effective_cache_size = '174362872kB', search_path = '"$user", public, topology, tiger'

Quite a bit to unpack there, however, let's hold off a second on that and focus on this. We can rely on the tools that PostgreSQL gives us to examine performance within our databases in addition to the tools provided by AWS.

Getting Explain Plans in Aurora

Let me be up front on this. My skills for query tuning in PostgreSQL are very rudimentary at this point. Unlike SQL Server, I haven't written multiple books and spent 30 years tuning Postgres. However, I'm working up my fundamentals and we can apply them here. Getting explain plans is one of the fundamentals we're talking about. Just like in SQL Server, it's the window into how the optimizer is resolving your query. Sure, with PostgreSQL it's even easier than SQL Server to run the debugger and see exactly what's happening under the hood, no plans needed. However, I suspect, many of you, like me, just will not be, ever, breaking down the optimizer code line-by-line to figure out where to put an index. We are going to rely on these plans, so, in order to get them, we've got a bit of work to do.

The plan up above came from the logs, put there through the use of auto_explain. Why would I do this? Well, here's a query to use function:

SELECT * FROM reports.get_ungeocoded_locations(1000);

I can run this with EXPLAIN (gets a plan) or EXPLAIN ANALYZE (gets a plan plus runtime metrics by executing the query) and the results will look something like this:

QUERY PLAN |
--------------------------------------------------------------------------------+
Function Scan on get_ungeocoded_locations (cost=0.25..10.25 rows=1000 width=72)|

Where's all that cool 'Seq Scan on location_lookup' stuff? We can see the query as listed up above. It's doing a little more than simply a function scan. This is because Postgres treats functions & procedures as a black box when it comes to plans. Yes, like SQL Server, there is a plan for the query inside the function, but, similar to SQL Server, it's not shown in the query calling it. Now, unlike SQL Server, there's not a way, let's be clear, from the EXPLAIN command, to see that function. Either, you have to pluck the query out of the function and then run it through EXPLAIN separately, or, you can do what I did and put auto_explain to work. That's slightly tricky in AWS Aurora, so let's go over it.

If you were hosting PostgreSQL locally, you would enable auto_explain pretty simply:

LOAD 'auto_explain';
SET auto_explain.log_min_duration = 20;
SET auto_explain.log_analyze = ON;
SET auto_explain.log_nested_statements = ON;

That would load auto_explain and set a number of variables, including, turning on nested statements, which is what we need in order to see inside the function execution. This only loads it up for the session running. To make it run permanently on the cluster you have to edit the configuration file and restart the cluster.

So, how do we do this in AWS Aurora PostgreSQL? Carefully.

First, if you accepted the defaults on the install, as I explained that I did in the first post in this series, you're going to have make some changes to your Aurora cluster. Instead of the default Parameter Group, you'll need to create a custom group. It's silly simple. Give a name, description, and then, most importantly, pick the right type, version and that it's a cluster definition:

Simple. Once done, you have to edit the configuration of your cluster and change the parameter group from the default to this one. It's easy and quick. However, things here get a little tricky. You have to reboot the cluster to see this parameter group working. However, the edits we're going to make, also require a cluster reboot. So, I did multiple reboots to my cluster so that I could see each item in action and validate that they worked, one change a time. You could wait to reboot until we're done with the next steps.

With the parameter group created, you can open it up and begin to edit parameters. This is the AWS Aurora equivalent of editing the config file. Here are the parameters we're interested in. I filtered them by simply typing in auto_explain:

Let's start the bottom. I've added 'auto_explain' to my shared_preload_libraries. That makes it possible to just put auto_explain to work. This change does require a reboot. The rest of the parameters are dynamic, meaning as you save them to the parameter group, they'll become active, or change behaviors, or whatever it is you're trying to do. So you can that I've enabled log_analyze, changing it to a value of 1, on as opposed to 0 or off. I'm also capturing settings and buffers. I've set the log_min_duration to 20ms to try to cut down on the noise (this queries are running pretty darned fast, prior to tuning, because my data set is so small). On a real system this number would absolutely be higher. Finally, we get to the meat of, I enabled log_nested_statements so that I can see the internal behaviors of the function as we show up above.

This is really easy to do... once you know how. Searches, especially through AI, tend to point you towards running CREATE EXTENSION, which isn't even correct on the core behavior of PostgreSQL, let alone on Aurora. However, once done, well, you can see it, I can pull out my explain plan and start tuning queries. Pretty sure we need an index to better support this Claude generated code. Also, I'm pretty sure we can restructure that code for better performance as well.

Conclusion

Let's be fair and honest, AWS Aurora PostgreSQL is a fork of Postgres. That means it's "compatible" with standard PostgreSQL behavior, not necessarily a perfect copy. However, as you've seen so far, to a very large degree, we're working inside PostgreSQL. There's just differences such as using a Parameter Group to do things we'd likely do in the config file. Those differences don't make working within PostgreSQL on Aurora more difficult though. Also, while there are differences, when you get into the standard operations like EXPLAIN, auto_explain, and others, it's just the same as working with PostgreSQL pretty much anywhere else.

To view this action, go here. Let me know if you'd like to see some different information or behaviors. If you want to see the code, go here.