This blog post is adapted from a lightning talk by Ben Fritsch at Ruby on Ice 2019.
There can be a number of reasons why your application performs poorly, but perhaps none are as challenging as issues stemming from your database. If your database's response times tend to be high, it can cause a strain on your network and your users’ patience. The usual culprit for a slow database is an inefficient query being executed somewhere in your application logic. Usually, you can implement a fix in a number of common ways, by:
- reducing the amount of open locks (or more detail about database lock debugging in this other blog post by Heroku Engineer Richard Schneeman, The Curious Case of the Table-Locking UPDATE Query)
- defining indexes for faster
WHERElookups - rewriting the query to use more efficient statements
...But what if your problem isn't resolved by any of these actions?
Let's talk about a problem that can occur from the underlying database schema, and how to solve it.
The problem
Consider this PostgreSQL database schema:
CREATE TABLE table (
app_uuid uuid NOT NULL,
json_field json
)
Postgres lets you mark a column's data type as json. This, as opposed to simply unstructured text, allows for more flexible querying and data validation.
As part of our application’s behavior, we receive and store payloads that look like this:
{"data": "very large string", "url": "https://heroku.com"}
If I want to fetch the url values for a specific app_uuid, I would write a query like this:
SELECT (table.large_json_field ->> 'url'::text) AS url,
FROM table
WHERE ("app_uuid" = $app_uuid)
ORDER BY "created_at" DESC LIMIT 200;
The average execution time of this query was 10ms, although there were outliers reaching as high as 1200ms. This was becoming unacceptable, and I dug in to see exactly what was going on.
The investigation
If you need to look into slow queries, the EXPLAIN ANALYZE statement is a good place to start. It will provide you with some internal metrics about how your query is planned and executed. I learned that there is also the option to use EXPLAIN (ANALYZE, BUFFERS), which looks like this:
EXPLAIN (ANALYZE, BUFFERS)
SELECT (table.large_json_field ->> 'url'::text) AS url,
FROM table
WHERE ("app_uuid" = $app_uuid)
ORDER BY "created_at" DESC LIMIT 200;
BUFFERS provides stats on the I/O subsystem of Postgres, identifying whether information is being loaded from cache (memory) or directly from disk. It is much slower for Postgres to read data outside of the cache.
The result of that informative query was the following information:
-> Index Cond: (app_uuid = $app_uuid::uuid)
Buffers: shared hit=7106
Planning time: 0.187 ms
Execution time: 1141.296 ms
By default, Postgres has a block size of 8kb. According to the Postgres planner, the query to fetch a url key uses over 7,100 blocks, which means that we've loaded (and discarded) about 60MB of JSON data (7,106 * 8kb), just to fetch the URLs we wanted. Even though the query took less than a millisecond to plan, it takes over a second to execute!
The solution
The fix for this is simple and direct. Rather than relying on a single json column, I converted it into two separate text fields: one for data and one for url. This brought the query time down from 1200ms to 10ms as we were able to scope our query to the exact piece of information we needed.
We realized that in this use case, storing JSON was no longer a requirement for us. When we started building this feature about three or four years ago, a json data type was the best choice we had, given the information we had. Our system hasn't changed, but our understanding of how the system was being used did. We had been storing the same JSON structure for years, but we were essentially storing unstructured data in our database. As a result of our database design, reading information became expensive for queries which only required a small piece of information.
I encourage you to audit your database schema for columns with data types that are no longer necessary. As our application and knowledge of users' behaviors evolves, so too should our database structure. Of course, don't change tables just for the sake of changing something! We were able to continue operating with this problem for years without any tremendous strain on our systems. If it's not a problem, don't fix it.
Want to learn more about Postgres? Check out another article we wrote on dev.to: Postgres Is Underrated—It Handles More than You Think.