Slow Database Queries? Are Database Types to Blame?

Summary

Slow database queries can stem from schema choices as well as inefficient SQL. The example uses PostgreSQL with a json column holding large payloads, then selects a single url value for an app_uuid and orders the results. EXPLAIN ANALYZE with BUFFERS showed 7,106 shared buffer hits, representing about 60MB of JSON data loaded and discarded, while execution reached 1,141.296 milliseconds in the reported run. The solution split the JSON into separate text fields for data and url, bringing query time down from about 1,200 milliseconds to 10 milliseconds. The broader conclusion is that schemas should be revisited as application usage becomes better understood, but changes are not necessary when the design isn't causing a problem.

Takeaways

1. 1

   EXPLAIN (ANALYZE, BUFFERS) reports PostgreSQL I/O details, including whether data comes from cache or disk and the number of shared buffer hits.

2. 2

   The reported query used 7,106 8KB PostgreSQL blocks, or about 60MB of JSON data, despite returning only the requested url values.

3. 3

   The authors recommend auditing schema data types as application usage evolves, while avoiding changes solely for the sake of changing tables.