Recent content in Homepage on Valerie Parham-Thompson: Database architecture for teams ready to launch and scale
In my previous blog post, I used analyze after defining extended statistics . But why do we run this statement? When you run analyze on a postgres table, the pg_statistic table (and therefore the pg_stats view) gets updated. Those statistics give insight into the size and shape of the data in the tables so the query planner can choose an optimal lookup path. It is executed in the background during…
Something you probably already know about Postgres: The planner needs to know things about the data to come up with a good access plan. For example, it needs to decide whether to use an index or a sequential scan. It’s looking at things like row count or distinct values when it comes up with this plan. The statistics it uses to do this are stored internally. One place is the pg_class table.
Retail site selection consultants charge thousands of dollars for trade area reports. The underlying work is a drive-time polygon overlaid with census demographics, with competitor locations plotted on top. All of it is reproducible with PostGIS, free government data, and open commercial data from Overture Maps. I’ll walk through a brief analysis using Woodcroft Shopping Center in Durham, NC…
A geofence is a virtual boundary defined around a real-world geographic area. When a tracked object (a phone, a truck, a piece of farm equipment) crosses that boundary, something triggers: a push notification, a dispatch alert, a compliance log entry, an irrigation valve. The geofence is defined as a polygon with geographic coordinates, typically a sequence of latitude-longitude pairs forming a…
Most database problems are fixable. A slow query gets an index. A hot table gets partitioned. A node runs out of memory and you add another. The feedback loop is tight enough that you can experiment your way to a better configuration without touching application code or migrating data. But a handful of decisions don’t work that way. They’re structural. They shape the schema, the…
The last couple of posts in this series described navigation algorithms: robot vacuums covering a living room floor, then self-driving taxis doing the same at city scale. In that second post, I mentioned that Waymo vehicles sweep lidar continuously to sense the road around them. That’s lidar as a real-time perception tool. There’s another side to it: aerial terrain surveys that produce…
Having a shared vocabulary across database, software, and infrastructure teams is critical when working together to tune latency issues. I’ve been in many incident rooms where the only report is “the application is slow” and had to unwind a series of questions: What do you mean by slow? Where do you see this? What parts are slow? If everyone in the room had read Enberg’s…
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.…
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…
Vacuum robots and self-driving taxi robots navigate physical spaces and avoid obstacles. That’s a super broad characterization. The more interesting question is why everything else about them is so different. My friend was telling me recently about riding in a Waymo in Los Angeles, and we started discussing how they gathered data to know where to go. I mentioned that I’d been writing a…
A developer asked me recently which open data sources he could use for real-time traffic in his application. The list is shorter than he expected, and the main reason is licensing. The value of apps and APIs that do dynamic routing is including how fast vehicles are moving on a given road segment right now, compared to how fast they normally move. This is what lets an app route around congestion…
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. Understanding HypoPG HypoPG is a PostgreSQL extension…
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 SQL Approach The traditional approach to generating test…
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…
Finding the nearest X is easy to ask for. Getting it right at scale is another matter. A lot of “find the nearest X” features (the coffee shop around the corner, the available driver closest to your pickup, the hospital within reach during an emergency) use a k-nearest neighbor (KNN) query under the hood. In PostGIS, that’s the <-> distance operator. On a small table it works…
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…
As YugabyteDB continues to evolve, its extensive API ecosystem offers powerful capabilities for database management and automation. However, with hundreds of API endpoints across overlapping categories, locating exactly the right API endpoint can be challenging. In this guide, I’ll walk you through several proven strategies for efficiently finding the API endpoints you need, along with…
Migrating to YugabyteDB offers significant advantages in terms of high availability, global distribution, and horizontal scalability—features essential for managing modern database workloads. However, data migration can be a complex process, particularly when transforming your schema definition. Differences in datatype support, query syntax, and core features across systems can complicate the…
I have been writing about shortest-path algorithms and A* heuristics in the context of road networks and pgRouting. But the same graph search concepts show up in a device that millions of people own and never think twice about: the robot vacuum. A robot vacuum has to solve three problems every time it runs. It needs to figure out where it is, decide where to clean next, and find its way back to…
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…
If you’ve ever installed PostGIS and opened the documentation, you’ve run into the type decision right away: geometry or geography ? They look similar, they both store spatial coordinates, and they share many function names. The difference matters more than it first appears. Choosing the wrong one leads to silently incorrect distance calculations. Why Two Types Exist PostGIS was…
One thing that can really wreck your performance in Cassandra and the similar YugabyteDB YCQL is large partitions due to an imbalanced key. Without the robust nodetool commands of Cassandra, it can be challenging to find these large partitions in YugabyteDB. dsbulk is a tool used for migrating data, and YugabyteDB has a fork that takes into consideration slight differences from Cassandra. That…
Today’s global and distributed applications often need to serve user requests from a single data source across different regions. While providing data scaling and protection against network outages, ensuring low-latency access to data is critical for providing a seamless user experience. YugabyteDB, a distributed SQL database, is designed to handle global data workloads efficiently. In this…
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. Understanding Tablet Impact Each tablet in your distributed database represents an…
A database transformation and migration project takes solid planning and testing. I’ve found that three common changes required when transforming a SQL Server database to YugabyteDB YSQL are related to syntax, performance, and stored procedures. These will get you started on your transformation project. Syntax Transforming a schema from MS SQL to YugabyteDB requires some minor syntax changes. This…
I was recently reviewing a database partitioning definition in YugabyteDB (the postgres “ysql” API), and realized the partition distribution might not be what the developer intended. What is database partitioning? Database partitioning is used to divide large tables into smaller tables (partitions). While the data is physically separate, the application can access the data logically as…
Postgres and YugabyteDB allow you to define partitions of parent tables. Partitions are useful in at least two ways: You can take advantage of partition pruning. The database doesn’t need to look at partitions it knows won’t meet the parameters of the query. You can easily archive data by disconnecting and/or dropping partitions instead of managing expensive delete queries.…
I had to create a 10 million row table for testing recently, and put together a query to generate random data for it. INSERT INTO my_table (id, mydatetime, string1, string2) SELECT (random() * 70 + 10)::int, TIMESTAMP '2024-01-01 00:00:00.000000' + interval '1 millisecond' * (random() * 86400 * 1000 * 365), (array['alligator','bear','cat','dog'])[(random() * 3 + 1)::int],…
Timing of seasonal demand depends on the industry, but a cyclical increase in traffic applies to all industries. Maybe your cycle is shorter than a full year. Or maybe it’s related to things like weather patterns or fashion. You know when your business gets the most traffic. Retail? Black Friday/Cyber Monday. Health and fitness? New Year’s Day. Pizza restaurant? Halloween and Thanksgiving,…
Math… the universal language. Timestamps, not so much. The way we decide to denote date and time differs across both computer languages and human languages. The format also differs across implementations of SQL. For example, Oracle and Postgres allow very different formats to be entered in the timestamp data type. Oracle allows a wide variety of punctuation in dates: hyphens, slashes, commas,…
In both MySQL and Postgres, expiring records after a set period of time takes a couple of timestamps and a little creativity. With Cassandra, or in this case the YugabyteDB ycql API, TTL (time to live) can be leveraged to handle this functionality, simplifying both the table definition and amount of work required by your code. Here’s a short test to demonstrate. Reminder that you can set up…
I was recently setting up a demo to show off query logging features. Two common extensions, pg_stat_statements and pg_stat_monitor, store data locally. In the case of a distributed database, it is helpful to combine the query runtimes on all nodes. YugabyteDB supports foreign data wrappers, so I decided to use this feature to combine query statistics from each of my three test nodes. The libraries…
I added a new database to my demo platform: Postgres. This code helps me provision Ansible Postgres on Mac for demo purposes or simple functional testing, and it is an extension of previous work I shared: https://valerieparhamthompson.com/posts/string-search/ . The script does a postgres install via Homebrew for Mac M1 and starts it up, then creates the database, user, etc. needed for the demo.…
I made slight changes to the Debezium UI example for local demo purposes, here: https://github.com/dataindataout/debezium-examples/tree/main/ui-demo Here’s a brief screen recording of how to configure the Debezium postgres connector in the UI using the values from the docker-compose setup.
A distributed database is designed to withstand outages to a good degree. However, you should also maintain backups in case of “oops” scenarios like a dropped table. The yb-admin tool can be used to manage snapshots. Here’s a brief walkthrough. Some caveats about using snapshots… They are stored on the same server, so this method doesn’t protect against file system…
Quick post to share my presentation last week at the YugabyteDB Friday Tech Talk. It was on fuzzy matching, and more generally string searches. Got to nerd out on two of my favorite topics: words (broadly, linguistics and specifically, names) and databases. Check it out! (Code for scenarios in my repo, here: https://github.com/dataindataout/xtest_ansible/tree/main/scenarios/fuzzy )…
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. https://info.yugabyte.com/hubfs/YFTT%20Slide%20Decks/2022_12_02_YFTT_Valerie%20Parham-Thompson_Audit%20Logging%20in%20YugabyteDB.pdf Audit logging is just one of the security…
I recently put together a platform to demo a handful of scenarios related to YugabyteDB cross-cluster replication. The code is here: https://github.com/dataindataout/xtest_ansible This works for Mac (Apple M1) and should work on later versions of Mac and Linux. Unsure if it will work on Windows. You will need a copy of YugabyteDB (2.16 or 2.17, depending on which branch of the demo code you use).…
Here’s a very quick way to set up YugabyteDB on your Mac for functional testing. It assumes you already have Homebrew installed. brew tap yugabyte/yugabytedb brew install yugabytedb In the future, you can upgrade the version by running this: brew upgrade yugabytedb Verify the installation and check the version: yugabyted version Set up local networking: sudo ifconfig lo0 alias 127.0.0.2 sudo…
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,…
Audit logging is essential for tracking the “who, what, when, and where” of database access and changes, supporting both security and compliance requirements. It helps organizations know who accessed or modified data, schemas, roles, or grants. YugabyteDB offers both session-level and object-level audit logging. The system builds upon standard PostgreSQL logging (including the pg-audit…
Memory configuration in YugabyteDB for YSQL workloads involves partitioning among the tserver, master, and postgres processes, each with default ratios. Adjusting these ratios based on workload characteristics helps avoid out-of-memory events. Monitoring memory usage is crucial, and tuning parameters like max_connections, work_mem, and temp_file_limit can optimize both performance and resource…
At DSS 2021, I provided a comprehensive orientation to monitoring YugabyteDB, focusing on how to interpret and leverage built-in metrics for operational visibility. Three key dimensions of database monitoring: Uptime (Alerting): Ensuring the system is running and healthy through critical alerts. Performance (Trending): Tracking historical performance to detect changes and optimize queries.…
The founder of YugabyteDB and I discussed Kroger’s multi-year journey modernizing its technology infrastructure using distributed SQL to support its large-scale retail operations with the Kroger VP of Customer Technology at DSS 2021. Key points: Kroger, the largest independent grocery chain in the US, operates over 2,800 stores under various banners. The company is focused on digitizing its…
Migrating Oracle workloads to Google Cloud’s Bare Metal Solution (BMS) offers benefits like reduced rewrites, familiar hardware, and simplified licensing. Challenges include server sizing, OS changes, and database upgrades. Careful planning and consolidation are key for large databases, and BMS is well-suited for organizations aiming to minimize downtime and risk during migration. Read more!
I recently did an upgrade of 200+ nodes of Cassandra across multiple environments sitting behind multiple applications using the cstar tool. I chose the cstar tool because, out of all automation options, it has topology awareness specific to Cassandra. I will share my experience with this upgrade, including observations and surprises, as well as a walk-through of the process using a Cassandra…
Partial indexes in PostgreSQL dramatically improve query performance for searches on specific email domains. Standard indexes may not help with wildcard searches, but a partial index targeting a particular domain can reduce query times by avoiding full table scans. Practical examples and execution plans illustrate the speedup, and partial indexes are best for data distributions that remain stable…
PostgreSQL offers multiple backup options, including logical backups with pg_dump and pg_dumpall, and physical backups with pg_basebackup. Regular backups and testing restore procedures are essential for disaster recovery. Continuous archiving and point-in-time recovery provide additional protection and flexibility for critical database environments. Read more!
Upgrading large Cassandra clusters is simplified with cstar, which discovers cluster topology and executes commands in a topology-aware manner. This reduces downtime and risk, and practical tips include using verbose output and ensuring all nodes are up before starting. cstar’s automation and resilience make it ideal for complex, large-scale operations. Read more!
Integrating Apache Spark with Cassandra requires attention to data modeling, query optimization, and cluster sizing. Best practices include designing efficient schemas, minimizing cross-node queries, and tuning Spark jobs for performance. This combination is powerful for analytics and data processing in distributed environments. Read more!