In my previous post on routing, I used Dijkstra’s algorithm without much discussion of alternatives. The Dijkstra algorithm works for network routing, and for many problems it is the right choice. But pgRouting also ships with pgr_aStar, an implementation of the A* algorithm that can find the same shortest path while exploring fewer edges. The difference comes down to one thing: a heuristic that tells the algorithm which direction to look.
In my previous post on pgRouting, I showed how to run shortest-path queries directly inside PostgreSQL. That approach works well when your road data is already in Postgres and your network is moderate-sized. But what happens when you need live traffic data, global coverage, or routing at thousands of queries per second? That is where external routing APIs and dedicated routing engines come in.
I have evaluated five routing services that cover the spectrum from fully managed APIs to self-hosted open-source engines. Each one makes different trade-offs around cost, speed, customization, and data freshness. Here is what I found.
If your application needs to answer “what is the fastest route between two points,” you might reach for an external routing API like Mapbox Directions. But if your spatial data is already stored in PostgreSQL, the Postgres extension pgRouting lets you run graph-based routing queries right where the data is.
PostGIS gives you spatial data types, indexes, and operations like distance calculations, intersections, and buffers, but it has no concept of navigating a network from one point to another. pgRouting fills that gap by adding graph traversal and shortest-path algorithms on top of PostGIS. I recommend it for teams that already use PostGIS and want to keep routing logic close to the data.
pg_stat_statements is an extension that tracks execution statistics for every normalized SQL statement.
Database performance problems are often mysterious. Queries slow down, CPU usage spikes, or users complain about latency, but pinpointing the cause requires visibility into what your database is actually doing. pg_stat_statements is PostgreSQL’s answer to this challenge.
pg_stat_statements is an extension that tracks execution statistics for every normalized (fingerprinted) SQL statement. Instead of logging millions of nearly-identical queries, it groups similar statements together (with constants replaced by placeholders), aggregating their execution metrics into a single fingerprint. This approach provides comprehensive query-level insights with minimal performance overhead and storage cost.
Using HypoPG to test hypothetical indexes for query optimization in YugabyteDB
Query optimization is a critical aspect of database performance tuning. While YugabyteDB’s YSQL API provides powerful tools for analyzing query performance through EXPLAIN plans, sometimes we need to experiment with different indexing strategies without the overhead of actually creating the indexes. This is where HypoPG comes in handy.
HypoPG is a PostgreSQL extension that allows you to create hypothetical indexes and see how they would affect your query plans without actually creating the indexes. This is particularly useful when:
Modern approaches to generating test data with Python Faker
In 2018, I wrote about using SQL functions to generate random test data in MySQL. While that approach served its purpose, the landscape of test data generation has evolved significantly. Today, I want to share my experience with using the Faker library, which has become my go-to tool for creating realistic test datasets.
The traditional approach to generating test data relied heavily on SQL functions like RAND() and string manipulation. This method worked but had limitations:
Writing an interactive command-line tool as a learning tool for YugabyteDB REST APIs
I’ve had the chance to share my database expertise in a variety of venues: speaking at meetups and conferences, leading hands-on workshops, mentoring new technologists, and of course writing.
I had been brewing a new idea for sharing content when a great opportunity landed in my lap.
The idea was: share what I know about managing a specific database product in code. Instead of creating a runbook for how to set up replication, I would write code that sets up replication. The key part is that it would have to be well-organized, commented, and documented to be useful to learners. Making it interactive would help users understand the options and parameters as they chose the commands and added flags. Even the error statements would give them insight into how it all works.
Modern distributed databases split large tables into tablets to enable parallel processing and efficient data distribution. Finding the right tablet size impacts everything from query performance to operational overhead. Let’s explore how to approach tablet sizing systematically to achieve optimal performance.
Each tablet in your distributed database represents an independent unit of data distribution. When you create tablets, you influence system behavior at multiple levels. The database uses tablets to parallelize operations, manage resources, and handle data growth. Your tablet strategy directly affects query response times, write throughput, and overall system health.
YugabyteDB Friday Tech Talk on fuzzy matching for string searches
Gearing up for my next YFTT presentation next month. It will be on fuzzy matching, a chance to show out some neat string search features.
Meanwhile, here’s the deck for my last YFTT. The topic was audit logging.
Audit logging is just one of the security features available in YugabyteDB. You can use it to tell you the “who, what, when, where” of actions on your systems. The logs can be then sent to a log analysis system for archiving and correlation with other logs.
Techniques for matching similar strings in YugabyteDB
Fuzzy string matching in YugabyteDB can be done with wildcard lookups, phonetic algorithms (Soundex, Metaphone), and trigram similarity. I’ll show a demo of practical examples using artist names, highlighting the performance differences between wildcard searches and phonetic indices. A combination of indexed double metaphone and trigram methods works best for both speed and precision. Also, while YugabyteDB supports PostgreSQL-style extensions, some indexing optimizations behave differently due to its distributed storage layer.