Optimizing 600k+ Rows in Atlassian Forge SQL: Performance Under 16MB and 5s

Optimizing SQL Queries in Atlassian Forge In this article, we’ll work with a large dataset exceeding 600,000 records and walk through how to optimize SQL queries to stay within the strict Forge SQL limitations—particularly the 16 MB memory quota and the 5-second read timeout. I’ll demonstrate how to write efficient SQL using indexes, pagination strategies, and EXPLAIN plans to achieve sub-second execution times and low memory consumption, even with large OFFSET values. We’ll walk through a real-world example of optimizing complex SQL queries in Atlassian Forge. You’ll see how query performance can drastically improve when working with large datasets inside the Forge runtime. To measure and improve query performance, we’ll use EXPLAIN and EXPLAIN ANALYZE—powerful tools supported by TiDB that help us understand execution plans, memory usage, and performance bottlenecks. EXPLAIN generates a query plan without executing the query. It shows the order of operations, join types, scan strategies, and whether indexes will be used. EXPLAIN ANALYZE goes one step further—it executes the query and displays actual execution times, memory consumption, disk usage, and other runtime details. This is especially useful for diagnosing performance problems or validating optimizations.

Apr 13, 2025 - 13:14

Optimizing 600k+ Rows in Atlassian Forge SQL: Performance Under 16MB and 5s

Optimizing SQL Queries in Atlassian Forge

In this article, we’ll work with a large dataset exceeding 600,000 records and walk through how to optimize SQL queries to stay within the strict Forge SQL limitations—particularly the 16 MB memory quota and the 5-second read timeout. I’ll demonstrate how to write efficient SQL using indexes, pagination strategies, and EXPLAIN plans to achieve sub-second execution times and low memory consumption, even with large OFFSET values.

We’ll walk through a real-world example of optimizing complex SQL queries in Atlassian Forge. You’ll see how query performance can drastically improve when working with large datasets inside the Forge runtime.

To measure and improve query performance, we’ll use EXPLAIN and EXPLAIN ANALYZE—powerful tools supported by TiDB that help us understand execution plans, memory usage, and performance bottlenecks.

EXPLAIN generates a query plan without executing the query. It shows the order of operations, join types, scan strategies, and whether indexes will be used.
EXPLAIN ANALYZE goes one step further—it executes the query and displays actual execution times, memory consumption, disk usage, and other runtime details. This is especially useful for diagnosing performance problems or validating optimizations.