Replace kong price table with yearn-prices

[matheus1lva]

Remove Kong's price table; source indexer prices from yearn-prices service

Description

Kong writes a per-block USD price for every token it touches into a Postgres
price table. That table is now the largest object in the database, and it sits
on the write path of every indexed block. This spec makes the quantified case
for deleting it: the indexer reads prices from the external, edge-cached
yearn-prices service (prices.yearn.dev) instead, and the two public GraphQL
resolvers that read the table (prices, tvls) stop returning price data
immediately rather than get re-pointed. This API behavior is not coupled to the
indexer cutover flag: the existing data is already unusable, so there is no
reason to preserve it during the service trial. No code is pre-approved. The
go/no-go call is in §3.

The indexer still needs a real price per block to compute TVL. Day granularity
is signed off: the service keys prices by UTC day
(floor(ts/86400)*86400 + 86399), and every indexer lookup resolves
block_number → block_time (getBlockTime, lib/blocks) and passes the unix
timestamp to the service, which normalizes to day-end. fetchPriceServiceUsd
(prices.ts:79) is the template, with two bugs to fix first (§4 Phase A2).

1. Baseline (measured 2026-07-02, Neon project kong / green-frog-37871507, aws-us-east-2, pg16, autoscale 0.25–4 CU, suspend_timeout=0)

Every number below comes from live pg_* catalog queries and 1% TABLESAMPLE
scans against production. Counts marked (est) are reltuples or sampled.

Storage

object	total	heap	index	share of DB
price	32 GB (34,065,620,992 B)	14 GB	17 GB	60%
output (hypertable chunks in _timescaledb_internal)	14 GB			26%
evmlog	7.5 GB	3.9 GB	3.7 GB	14%
snapshot	23 MB	13 MB	0.5 MB	—
thing	3.5 MB	2.4 MB	1.0 MB	—
DB logical total	53 GB			100%

• price holds 60% of the database.
• Its primary key price_pkey (chain_id, address, block_number) is 17 GB on its own, bigger than every other table and index in the DB combined.
• A second index price_chain_addr_time_idx shows 0 bytes (never built). Drop it regardless.
• Rows: ~133.3M (exact GROUP BY chain_id count). reltuples shows 120.0M, stale because the table has never been analyzed. Average row width 105 bytes (sampled pg_column_size).
• Neon synthetic storage for the project: 61.9 GB (logical plus 24h history retention).

Maintenance / bloat

• price has never been autovacuumed or analyzed (autovacuum_count=0, last_autovacuum=NULL, last_analyze=NULL), with ~1.09M dead tuples at last check (~0.8% of ~133M rows — small; pg_stat's 34% figure came from its unreliable n_live_tup=2.1M, itself a product of the missing analyze).
• The default autovacuum_vacuum_scale_factor=0.2 sets the trigger at ~0.2 × reltuples ≈ 24M dead tuples, so autovacuum will not fire for a long time and the planner runs on stale stats.

Row composition (why the table exists)

Sampled price_source distribution (1% sample, scaled — shares are reliable, absolute rows approximate), ~133M rows:

source	approx rows	share
ydaemon	~128.9M	~99%
na (no price found, zero)	~1.5M	~1.2%
lens	~163k	0.1%
yprice via waveydb	~36k	—
defillama	~18k	—
priceservice	~17k	—
tvl	~7k	—

99% of the table is persisted yDaemon spot prices, written through on every
indexed block. yDaemon serves only current prices, so a row stamped with an old
block_number holds whatever spot price was live when Kong happened to index
that block, not the historical price for that block's time. These per-block
values are not re-fetchable from yDaemon, and they are the "unusable data" the
prices resolver returns today (§ Read paths). The price service replaces them
at correct day granularity, which is signed off.

Distribution

• Rows per chain (exact counts, sum ≈ 133.3M): mainnet 85.9M (64%), optimism 17.7M, base 10.2M, arbitrum 6.8M, fantom 4.9M, polygon 4.0M, katana 1.5M, berachain 0.9M, sonic 0.6M, gnosis 0.5M, other 0.1M.
• Distinct tokens: mainnet 1,130; ~1,820 total across 11 chains.
• block_time range: 2015-07-30 to now (deep historical backfill).

Write rate (steady state, tip only)

Rows whose block_time falls in the last 14 days: 75,000 to 80,000 rows/day
(≈0.9 upserts/s sustained, bursty). Each upsert maintains the 17 GB PK btree.
This excludes backfill and replay writes of historical block_times, which come
in unbounded bursts.

Read paths

• Indexer (fetchDbPriceUsd, prices.ts:139) does a point lookup into price_pkey. For historical (non-latest) calls it runs first (prices.ts:40), so every price resolution during a backfill or replay probes the 17 GB index; on a miss it computes via lens or service and writes the row back (load.price). For latest calls, yDaemon runs first and the result always gets written (prices.ts:36). That write is the ~77k/day steady load.
• GraphQL prices resolver (packages/web/app/api/gql/resolvers/prices.ts) requires address and has a fixed LIMIT 50000; prices(chainId) without an address throws. It therefore cannot stream a whole chain, but it still serves capped per-block spot snapshots that are not historical prices.
• GraphQL tvls resolver (…/resolvers/tvls.ts:56) runs LEFT JOIN price ON chain_id, asset_address, block_number to attach a price_usd column to each daily TVL bucket. This was added as a diagnostic and is the exact per-block join this ticket exists to kill.

Measured DB-work attribution (30-day window)

pg_stat_statements and pg_statio_user_tables, cumulative since the compute
started 2026-06-09 (stats never reset), attribute DB work to price directly:

metric	price queries	whole DB	share
query exec time	17.3M ms	11.5B ms	0.2%
disk block reads	34.8M	35.3B	0.1%
calls	4.9M (~163k/day)	584.7M	0.8%
rows served	4.9M (~0.5 GB at 105 B)	1.39B	0.4%

• price's own cache-hit ratio is poor: 51% (36.9M hits vs 34.8M disk reads) against a DB-wide 83.5%. That confirms the index-over-RAM mechanism per access, but the absolute volume is small.
• The I/O load lives elsewhere: evmlog (2.7B heap disk reads) and the output chunks dominate.
• Caveat: the window covers normal operation. A deep backfill or replay (bursts of per-block probes and writes) raises price's share while it runs; that load is episodic.

Bill split (Neon Console, June 2026, org-wide)

line	quantity	amount	share
Compute	4,004 CU·h @ $0.222	$888.97	58%
Public network transfer	6,520 GB (500 free, then $0.10/GB)	$602.02	39%
Storage, root branches	54.1 GB-month @ $0.35	$18.93	1.2%
Storage, snapshots	193.0 GB-month @ $0.09	$17.37	1.1%
Instant restore	6.2 GB-month @ $0.20	$1.25	—
Subtotal		$1,528.54

• The bill is org-wide (4 projects). kong dominates both big lines: ~95% of root-branch storage (62 of ~65 GB synthetic) and ~85% of CPU seconds (project counters: kong 490k of 580k org-wide).
• Storage — the thing this ticket deletes 60% of — is ~$36/month (2.4% of the bill). Compute and public transfer are the money, and §1's counters put price at 0.2% of compute and ~0.5 GB of the ~6,500 GB monthly transfer.
• The 6.5 TB/month public egress ($602) is its own investigation (clients connect over the public internet; price contributes ~nothing to it). Out of scope here, but that is where the transfer dollars are.

---

2. Why removing it helps (mechanism, checked against counters)

Storage (certain, but small in dollars). Direct: DB logical 53 GB drops to
~21 GB (−60%); Neon synthetic storage ~62 GB drops to ~28 GB. At June rates that
is ~$10–11/month on root-branch storage plus up to ~$9 on snapshots — against a
$1,529 bill (§1 bill split).

Write path. ~77k upserts/day maintain a 17 GB btree (larger than the 4 CU
compute's ~16 GB RAM) that autovacuum and analyze have never touched (§1).
Removal deletes that standing write load and the operational liability of an
unmaintained 133M-row table.

Compute (CU), corrected by measurement. The counters (§1) put price at
~0.2% of Kong's query time and ~0.1% of its disk reads in steady state. The
earlier theory that the index crowds the LFC and drags the whole DB's hit ratio
does not hold in this window: price is accessed too rarely to occupy much cache.
Kong's steady-state CU saving is small. It may be offset by edge-cache misses
that query the separate price-service Neon database; no cross-service cost
attribution exists yet. This migration intentionally has no historical
catch-up, backfill, or replay (§4–5).

Egress. Measured price-query output in the window is ~0.5 GB/month, a
negligible steady drain. The resolver is already address-required and capped at
50,000 rows, so it cannot emit a whole chain or a 9 GB response. At the sampled
105-byte row width its upper-bound table-row payload is roughly 5.3 MB per
request before GraphQL/HTTP encoding. Nulling it removes the remaining capped
egress and unusable data, but this is not a material egress-cost or
single-request abuse mitigation.

---

3. Savings projection + go/no-go

Method. Storage delta measured from catalogs. CU and egress attribution
measured from pg_stat_statements + pg_statio_user_tables over the 30-day
window (§1). Dollars from the June console bill split (§1) times those shares.

Projection, gross Kong-bill reduction per month (June rates, $1,529 bill):

• Storage: −32 GB logical (−60%), ~−34 GB synthetic. Certain — and worth ~$10–20/month (root-branch $10–11, snapshots up to ~$9).
• CU: ~0.2% of Kong's DB work ≈ ~$1–2/month. evmlog and output
  dominate I/O; the $889 compute line is theirs, not price's. This may be
  offset by price-service cost on edge misses.
• Egress: ~0.5 GB of ~6,500 GB/month ≈ ~$0.05. The prices resolver's
  existing address requirement and 50,000-row cap mean there is no additional
  multi-gigabyte tail-risk saving to count.
• Gross Kong saving: ~$12–22/month, ~1% of the bill. Net organisation
  saving is unknown until the incremental price-service cost is measured.

Verification. Deploy PRICE_SOURCE=service to ingest (§4),
then compare the following week's Kong CU·h and data transfer to the prior
week. In the same window, measure price-service Worker requests, edge-hit rate,
Neon compute, and outbound RPC. Rollback is a configuration deployment/restart
to PRICE_SOURCE=table; it requires no code revert, but is not instantaneous.
That rollback restores the indexer path only. The deliberately emptied GraphQL
fields remain empty/null.

Go / no-go. The ticket asked for an honest answer, so: as a cost project,
NO-GO — the gross Kong saving is ~1% of the bill and the net saving is
unmeasured. The premise (price table drives CU and egress) is contradicted by
measurement: price is 0.2% of query time, 0.1% of disk reads, ~0.01% of
transfer. As a risk/hygiene project, GO is defensible: it deletes an
unmaintained 133M-row / 17 GB-index liability and shrinks the DB 60% (faster
restores/branches). The public resolver is already bounded, so it is not part
of the go rationale. If the goal is the bill,
the levers are the $889 compute line (evmlog/output I/O) and the $602 public
transfer line (6.5 TB/month of client egress) — both out of scope here and both
worth their own tickets.

---

4. Migration + cutover plan

Phase 1

• Create a branch on neon just for this experiment
• graphql resolvers should return empty or null - prices and tvls.
• add env var USE_PRICE_SERVICE

This will control:

1. fetchPriceServiceUsd not fallbacking to any source but going directly to the price service;
2. skip every mq.add(mq.job.load.price, …)

• run indexer with USE_PRICE_SERVICE on for a while on the new neon branch

Phase 2 — verify

• Record the time taken from start to finish of indexer
• Choose a set of known tokens
  • BTC
  • ETH
  • Any curve vault
  • YVUSD
  • YBOLD
• Pull live kong data - through rest api - for all of the above
• Run script comparing properties between the two
  • It is expected to have a slight difference intraday - but the overall tvl and anything that gets influeced by price - should not deviate too much

Phase 3 — decommission the table

Given the tests assured the difference in price isn't too big and acceptable to follow up:

• Merge code changes
• Deploy with env var OFF and Stop indexer
• Turn env var to true
• Fanout abi
• Monitor logs for no errors - and check outputted data quality
• Create a new neondb snapshot
• Drop the price table
• Remove any reference on packages/db of it
• Monitor for any errors on sentry

---

5. Risk + rollback

risk	mitigation
Service outage, rate-limit, or a missing token-day leaves the indexer without a price	Past-day cache hits are immutable at the service edge, but coverage and outage behavior must be proven before cutover. Use the explicit current-day fallback policy; for missing past-day prices, retry/defer or mark the result explicitly unknown. Do not silently materialize tvl=0. Alert on service errors, fallback use, and unknown-price rate by chain/token/day.
HTTP and RPC call volume. fetchErc20PriceUsd(…, blockNumber) is called inside hooks; service edge caching alone does not reduce Kong's HTTP count.	Pass known block times into the client. Reuse Kong's existing prices.ts cache, with a normalized (chain, token, UTC day) key for past-day service results, so later hook calls for that token/day do not make another HTTP request. Instrument HTTP calls, RPCs, and errors during the service trial, then set an alert threshold. No backfill/replay is included, so this plan must not create a burst of historical calls.
Consumers of the prices/tvls price fields get empty/null	Intended. The data was per-block spot snapshots (unusable). Ship the unconditional resolver changes, watch for complaints, and direct anyone real to the official price API (prices.yearn.dev). Full schema sunset is a follow-up ticket.
Data loss on DROP TABLE	Gated on the agreed offsite backup pipeline (§4 Phase C; BE-8 is only an input) plus a pg_dump before the drop; the down-migration restores the schema. The dump is the only copy of the original per-block values; the service re-derives them at day granularity only (signed off).

Rollback.

• If identified any issues within the first fanout for test after deployed with USE_PRICE_SERVICEset to true, its easy to rollback
• If later a significant issue araises we can go back to the neon snapshot and fanout again so indexer will catchup

6. Technical notes

• Breaking API change: prices returns empty and tvls.price_usd returns null. Announce it; the underlying data was unusable per-block spot snapshots. Point real consumers at prices.yearn.dev.
• Two service-client bugs must be fixed before cutover: Gnosis chain name (100: 'xdai' → 'gnosis') and the hard-coded source=defillama filter (prices.ts:71,90).
• Reuse the existing cache.wrap. In service mode for past-day results, its key
  must use normalized
  (chain, token, UTC day) instead of blockNumber; the current/latest path
  retains its existing short-lived cache behavior. Block time must still be
  passed into the client to avoid a distinct getBlockTime RPC for every
  historical block (§5).
• fetchPriceServiceUsd (prices.ts:79) is the only service lookup path needed
  for this migration. Keep its token-oriented caller boundary, pass the known
  block time when available, and reuse the existing cache with a token/day key
  for past-day service results. batchHistorical and rangeHistorical are not
  part of this plan because no historical backfill/replay is being performed.
  The lookup uses the corrected PRICE_SERVICE_CHAIN_NAMES map and the
  service's default source priority.
• After the whole table gets decomissioned, ie: DROP TABLE, postgres (in this scenario neon) takes care automatically of reclaiming the storage taken previously by the table. In other words, thats the rm -rf done by postgres.

[murderteeth]

• we do not want to contunie supporting the prices resolver on the gql api. lets have the price resolver return null or [] for now. at a later date we can sunset. right now the prices resolver returns unusable data, so anyone "using" it has bad data anyway.

• same in resolvers/tvls.ts, drop the LEFT JOIN on price and just let price be null on the results. it was meant as a diagnostic and was a bad idea for the very problem we have now! lets null it and see what breaks or who complains and direct them to the official price api.

• gnosis price-service lookups are likely broken if the spec reuses the current map. kong maps chain 100 to xdai in PRICE_SERVICE_CHAIN_NAMES (packages/ingest/prices.ts:71), while the yearn-prices docs list gnosis as the supported chain name, not xdai. The spec says to reuse PRICE_SERVICE_CHAIN_NAMES, so Gnosis requests would likely fail validation

• unless we have a good reason, we should not be filtering prices to defillama only, ie /api/prices/batchHistorical?source=defillama&coins=... -> /api/prices/batchHistorical?coins=...

• "Optional follow-up: a background fanout replays" -- no replays, lets make a note to either sunset or reimplement the replay feature using stride table manipulation, https://linear.app/yearn-webops/issue/BE-150/sunset-or-reimplement-the-replay-feature

• "Reclaim storage (Neon reclaims on drop plus the history-retention window)." -- to be sure, instead of assuming neon takes care of it, lets stop fanout and run something like VACUUM (FULL, VERBOSE, ANALYZE) public.some_table;

• "Backfill wall-time... Acceptable: edge hits are cheap" -- no, even with edge caching on the price-side thats potentially a lot of http calls. fetchPriceServiceUsd has the right signature, by token, but the spec still needs to address the issue of too many http calls

• before deleting the price table, lets iterate on creating a backup pipeline for the database. we have an old spec here https://linear.app/yearn-webops/issue/BE-8/implement-serverless-offsite-backup-for-neon-database-via-gcp-cloud, but lets chat before implementing as we have more options now

[matheus1lva]

I took out "reclaim" which doesnt make sense given our idea is to drop the table entirely... i guess when i asked to rewrite it brought it back. The only table that has orphans on disk is the price table itself, the other ones are relative "healthy"

[matheus1lva]

@murderteeth updated.

[murderteeth]

One remaining item: please explicitly include and document the storage-reclamation procedure in Phase 3, including whether VACUUM (FULL, VERBOSE, ANALYZE) is required and where it should run.

[matheus1lva]

Last part added

+ After the whole table gets decomissioned, ie: DROP TABLE, postgres (in this scenario neon) takes care automatically of reclaiming the storage taken previously by the table. In other words, thats the rm -rf done by postgres.

[matheus1lva]

@murderteeth #442

Results can be found in https://github.com/yearn/kong/pull/442/changes#diff-65e1aadd1f2125219955ed8367d4a63bb4fd9d1c7c0856a65423eaeb3859b030