Timeout for account with many transactions

[obycode]

Describe the bug
The API is timing out when attempting to retrieve transactions for an account that has a very large number of transactions, even with a low limit. For example,

https://api.hiro.so/extended/v2/addresses/SP3SBQ9PZEMBNBAWTR7FRPE3XK0EFW9JWVX4G80S2/transactions?limit=30&offset=0

To Reproduce
Steps to reproduce the behavior:

1. curl -s "https://api.hiro.so/extended/v2/addresses/SP3SBQ9PZEMBNBAWTR7FRPE3XK0EFW9JWVX4G80S2/transactions?limit=30&offset=0"
2. See error:

{"statusCode":500,"code":"57014","error":"Internal Server Error","message":"canceling statement due to statement timeout"}

Expected behavior
Users should be able to retrieve transactions for any account in a reasonable time.

Note that this shows up in the explorer on the account pages, e.g. https://explorer.hiro.so/address/SP3XXK8BG5X7CRH7W07RRJK3JZJXJ799WX3Y0SMCR?chain=mainnet

[iamramtin]

Hey @obycode! I believe the root cause is in getAddressTransactions (multiple UNIONs + correlated subqueries leading to performance issues on the high-activity addresses). I have an optimisation approach in mind. Could you assign me this issue so I can work on a fix? Also, any advice on how best to replicate this dataset or a similar high-volume address locally to test performance improvements?

[obycode]

Sounds great, thanks @iamramtin. I'm just a user of the API, so I'll direct you to @rafaelcr.

[iamramtin]

@rafaelcr gentle ping, let me know if I can be assigned this :)

[rafaelcr]

hey @iamramtin absolutely, PRs are welcome! feel free to take this on

I believe the root cause is in getAddressTransactions (multiple UNIONs + correlated subqueries leading to performance issues on the high-activity addresses)

Yes, this definitely tracks. That function/query is very old i.e. it was written when the transactions table was way smaller than it is today. Perhaps also the slowness could come from any COUNT(*) performed to calculate the total transaction count for the given account.

any advice on how best to replicate this dataset or a similar high-volume address locally to test performance improvements?

Easiest is to go over to https://archive.hiro.so/mainnet/stacks-blockchain-api-pg/ and grab one of the archives so you can test with mainnet data and real accounts with lots of activity

[iamramtin]

Hey @rafaelcr

I've been digging into the timeout issue and have tried out a few optimisations to improve the performance. My benchmarks show that a medium activity address (SP21EK0KSQG7HEHBGCVRJGPGFMV8SCA2B85X01DK2) dropped from about 21.5 seconds to around 4.5 seconds on average, seeing roughly a 4x improvement. For the high-activity address (SP3SBQ9PZEMBNBAWTR7FRPE3XK0EFW9JWVX4G80S2), there's definitely some speedup but the query is still running past the 30s timeout locally, so it's not quite there yet

The main changes were moving canonical filtering earlier in the query, limiting each event table to prevent runaway queries, and using more efficient counting methods. The optimised version returns identical results to the original, so no breaking changes

I'm wondering how all this might play out in your production environment. Can you share a bit about the infrastructure and database layout you're running (hardware, Postgres config, indexing, etc)? Anything that may help figure out whether these optimisations are enough once they're off my local machine, or if we'd need to consider bigger structural changes in how the data's stored

[rafaelcr]

Thanks for the update @iamramtin

dropped from about 21.5 seconds to around 4.5 seconds on average, seeing roughly a 4x improvement

This is great progress!

I'm wondering how all this might play out in your production environment. Can you share a bit about the infrastructure and database layout you're running (hardware, Postgres config, indexing, etc)? Anything that may help figure out whether these optimisations are enough once they're off my local machine

We use the exact same schemas you see on this repo in our prod databases in postgres 15/17 deployments. I think the most valuable feedback we could use would be the output of an EXPLAIN ANALYZE query that shows the updated query plan based on your improvements. Perhaps there are still a few scans we should remove and/or optimize.

or if we'd need to consider bigger structural changes in how the data's stored

This is something I've definitely been considering in the past. Maybe we can add an intermediate caching table or expand the one we use for STX transactions (principal_stx_txs) to cover all types of tokens as well.

Could you send me your draft PR so we can discuss over your code? Also, we could perhaps set up a quick call to bounce off some ideas if you're interested. Let me know.

Thanks

[iamramtin]

Hey @rafaelcr I've opened a [PR](#2347) with the WIP implementation

I'm happy to chat and see if this is a valid approach for your prod data and if there are further optimizations that can still be made. Let me know your thoughts :)

---

I used the latest database archive from archive.hiro.so/mainnet/stacks-blockchain-api-pg and tested against the original problematic address SP3SBQ9PZEMBNBAWTR7FRPE3XK0EFW9JWVX4G80S2 that was timing out in production:

• Original endpoint: 156 seconds (2.6 minutes) - http://localhost:3999/extended/v2/addresses/SP3SBQ9PZEMBNBAWTR7FRPE3XK0EFW9JWVX4G80S2/transactions?limit=30&offset=0
• Optimized endpoint: 24 seconds - http://localhost:3999/extended/v2/addresses/SP3SBQ9PZEMBNBAWTR7FRPE3XK0EFW9JWVX4G80S2/transactions-v2?limit=30&offset=0
• Improvement: 6.5x faster execution time on the high volume address
• Test environment: M3 MacBook Air with latest database archive from archive.hiro.so/mainnet/stacks-blockchain-api-pg

Performance Results

I used a medium volume address SPDSMZGNMVKK48BHX45VKETX4VF8264NV0C9J692 for these benchmarks

Query optimization alone: 20.8s → 4.2s (4.9x improvement)
With targeted indexes: 4.2s → 2.4s (additional 1.8x improvement)
Total improvement: 8.7x speedup

Here's a preview of the EXPLAIN ANALYZE comparison:

Original Query (20.8s)

Execution Time: 20766.341 ms
InitPlan 13 (returns $37)
  ->  Aggregate  (actual time=10403.160..10403.162 rows=1 loops=1)
        ->  Nested Loop  (actual time=0.010..10398.568 rows=65654 loops=1)

Query Optimization Only (4.2s)

Execution Time: 4156.001 ms
InitPlan 13 (returns $49)
  ->  Aggregate  (actual time=22.230..22.231 rows=1 loops=1)
        ->  HashAggregate  (actual time=14.620..21.150 rows=65654 loops=1)

With Database Indexes (2.4s)

Execution Time: 2381.312 ms
SubPlan 2
  ->  Index Only Scan using idx_stx_events_subquery_optimized on stx_events
SubPlan 7  
  ->  Index Only Scan using idx_ft_events_subquery_optimized on ft_events

Technical Approach

There were two main bottlenecks addressed:

1. Late canonical filtering - moved canonical checks into each UNION ALL branch
2. Expensive count calculation - replaced separate count CTE with HashAggregate

The index strategy converts expensive bitmap scans into index-only scans for correlated subqueries.

Local testing environment:

• PostgreSQL 15 with 4GB effective_cache_size, 4MB work_mem
• Full production data archive

The PR includes complete EXPLAIN ANALYZE outputs and a production-ready migration script with rollback procedures.

[iamramtin]

Hey @rafaelcr, thanks for the sync on this.

To move forward, I suggest breaking this into separate action items. One approach could be:

1. Introduce a v2 of the endpoint, merge it behind a feature flag, and test it against live data.
2. Once validated, evaluate structural database changes (e.g. new indexes) to improve long-term performance.

Happy to collaborate on this and iterate together.