Skip to main content
Applies to:
  • Plan -
  • Deployment -

​
Summary

Queries that include broad predicates like input IS NOT NULL can cause slow scans or 504 timeouts when querying project logs with bt sql or the SQL API. Narrow the predicate to a specific nested field or remove it. For large exports, use async SQL queries or cloud storage export instead of trying to increase synchronous timeouts.

​
What is happening

  • The logs dataset stores nested JSON. A broad predicate such as input IS NOT NULL does not narrow the scan enough on large log tables and can make the query expensive.
  • That scan can cause the server or CLI request to time out. In the CLI, timeout or non-JSON responses can sometimes surface as parsing errors.
  • Shell quoting issues (different shells like fish vs bash) can also change how the query is sent and cause parse failures that look unrelated.

​
Fix or suggestion

​
Option 1: remove or narrow “IS NOT NULL” (most common fix)

  1. Remove the broad check:
    • Bad: AND input IS NOT NULL
    • Better: remove the clause entirely.
  2. Or test a specific nested field:
    • Example: AND input.foo.bar IS NOT NULL
  3. Re-run the same query with a larger lookback once narrowed.
Example before → after (minimal):
  • Before: SELECT * FROM project_logs(… ) WHERE … AND input IS NOT NULL AND input.type = ‘inferenceTranscript’ LIMIT 50
  • After: SELECT * FROM project_logs(… ) WHERE … AND input.type = ‘inferenceTranscript’ LIMIT 50
  • Or: SELECT * FROM project_logs(… ) WHERE … AND input.transcript[0].id IS NOT NULL AND input.type = ‘inferenceTranscript’ LIMIT 50

​
Option 2: Run long or bulk queries asynchronously, or use cloud storage export

  1. Use the CLI async flag for long-running queries:
    • Example: bt sql —async “SELECT … FROM project_logs(…) WHERE … LIMIT 10000”
  2. Async API flow (if you prefer the API):
    • POST /btql/async → returns an id
    • GET /btql/async/ → check status
    • GET /btql/async//result?limit=[&cursor=] → paginate results
  3. Use cloud storage export for large recurring exports or warehouse pipelines. If S3 export is not enabled in your deployment, request it from your account administrator.
Notes on async behavior:
  • Async results are materialized and retained for 7 days.

​
How to confirm it worked

  • Re-run the previously timing-out query (with the same lookback). It should return results without a 504 or parsing error.
  • For async API runs, confirm the job status becomes succeeded, then paginate results via the result endpoint. For CLI async runs, confirm the command returns results successfully.

​
Notes

  • Use —verbose on the CLI to show HTTP response bodies and surface redirect or timeout errors when debugging.
  • Be careful with shell quoting. For long or multi-line SQL strings use a safe heredoc pattern to avoid shell interpolation differences:
    • Example: 
      cat query.sql | bt sql --non-interactive --json
  • To enable the server-side performance improvement in self-hosted deployments, set BRAINSTORE_ENABLE_PARQUET_SUMMARY_READS=true in your Brainstore extra env vars and deploy your CloudFormation/stack update.