Investigation: The tick ingest pipeline design record

The ingest loop handles each tick on one thread, in arrival order. For each route of the tick, it reads the series, inserts two observations, and publishes to JetStream. Each insert is a commit. The publish waits for an acknowledgement. The rule that the republish follows the stored row ties the live view to the speed of the database.

The queue sits inside the NATS client. It has no age limit, and it serves the oldest tick first. When the loop falls behind, it spends its time on old ticks while newer ticks wait. This is the behaviour that requirement 1 forbids.

Measurements on one environment (release build, one server shared by five environments, a burst of 4,000 ticks) show the limit.

Configuration	Rows written each second
As built: synchronous commit on, one NOTIFY for each row	143
Synchronous commit off for the marketdata role	166
Synchronous commit off, and the NOTIFY function empty	210
Step 1 built: batches, no notification, core NATS	about 2,260

Three facts come from the samples.

• Exactly one database session of the marketdata service is active. The work is serial.
• With the commit sync removed, 41 percent of the samples of that session wait on a database-wide lock, 34 percent run on the CPU, and 24 percent wait for the service. The lock is the one that a transaction takes at commit after it calls NOTIFY. PostgreSQL takes it so that notifications enter the queue in commit order. The row trigger ores_marketdata_market_observations_notify_trg calls pg_notify for every row.
• The blockers of that lock were the marketdata sessions of two other environments on the same server. The lock is shared across databases, so ingests that share one server slow each other.

A commit sync is also slow on this disk. One fdatasync takes about 4.3 ms.

The write path has more costs for each row than the commit. Each row passes a before-insert trigger that runs an UPDATE on the same table and calls ores_iam_validate_tenant_fn. The table has seven indexes, and one of them is a partial unique index. Each statement runs through a tenant-aware connection that sets app.current_tenant_id when it takes the connection.

Two JetStream streams also store every tick. The stream synthetic_ticks covers the producer subjects, and the stream marketdata_ticks covers the republish subjects. Both keep their messages in files for 7 days, and both have no consumer. A producer waits for an acknowledgement of each tick, and the ingest waits for one more for each route. The streams give nothing that a client reads. In one environment the second stream holds 1,812 subjects after two days.

The demand is small today. One environment receives 13 ticks each second. Each tick reaches 4.6 routes on average, and each route stores two rows. That is about 120 rows each second against a ceiling of about 150. At 30,000 ticks each second the same fan-out is about 280,000 rows each second.

Hedgr is the new desktop client. Its plan, docs/plans/market-eventing.md in the Hedgr repository, shows how a real consumer reads the pipeline.

• Live values come from the party tick stream. Hedgr subscribes once for each session to marketdata.v1.ops.market_tick.<tenant>.<party>.>. This is the subject that the ingest loop publishes to after it stores a row. The tick stream is the only subscription that Hedgr makes in its own code. Hedgr has no subscription to any observation change event. That confirms that the database notification serves no client here.
• Hedgr filters on the client. The tick sink of Hedgr says that the party subscription delivers every tick of the party, and that it keeps the ticks of the watched series and drops the rest. Each tick costs a decode, two calls of the URI codec, a number parse, a lock and a map lookup, on the one NATS delivery thread. The section on the live subject makes the broker do the selection.
• The live tick has no coordinate. The backfill of Hedgr gives each point its term. The live tick carries a series URI, a time and a value, and the aggregator takes no term. A series with coordinates, such as the pillars of a curve, would mix its pillars in one live bucket stream. This was read in the code and not run against a live curve. FX spot has no coordinate and is not affected.
• Hedgr waits for the store without knowing it. The subject carries a tick only after the ingest has stored it. When the ingest fell behind, the chart showed ticks that were 70 minutes old, and nothing in Hedgr was at fault. The live path of this proposal removes that coupling.
• Hedgr measures freshness itself. It judges the age of a tick from the observation_time of the tick, and it draws that age beside the verdict. This is the right measure. It is also the acceptance measure for the pipeline: the age of a tick as the client sees it, at the 99th percentile.
• Hedgr already conflates. It buckets ticks into 1 second, 1 minute and 1 hour levels on the NATS thread, and it passes closed buckets to the frame loop through a fixed ring. A chart needs at most one point for each second. The class interval of 100 milliseconds therefore loses nothing that a chart shows. It cuts the fan-out from tens of thousands of ticks each second to at most ten for each datum. A consumer that wants less conflates for itself, as Hedgr does.
• The subject is scoped to a party. The live publish must still go to one subject for each route. Its cost is small compared with a commit, because core NATS carries it without an acknowledgement. The cost grows with the number of parties that watch a source.
• Hedgr seeds a chart with one history call. On open, on reconnect and on a manual refresh it asks for a span of time and merges the reply with its live ticks. See A chart reads a series with one history call and one live subscription.
• One difference needs a decision. The ring of Hedgr drops the newest bucket when it is full. Its source comment gives the reason: a tick is worth less than the progress of the dispatch thread, and the chart shows the points it did receive. The freshness rule says to overwrite the oldest. The two rules differ only when the frame loop stalls by more than 4,096 buckets, but they should agree.
• The reconnect gap. Hedgr loses the ticks between a drop and a reconnect, and it fills the gap with a history call from its newest tick. The recent window serves the recent part of that call from memory.

The republish subject ended in one token: the whole canonical URI of the datum, percent-encoded. A wildcard could then select a whole party and nothing smaller. The Hedgr client subscribed to the whole party and discarded the ticks of series that it did not watch. Each session decoded every tick of its party. At tens of thousands of ticks each second, that costs a delivery thread for each session, and it costs the broker the same traffic for each session. It broke requirement 8.

The change touched every consumer of the stream. Hedgr was the only client, and the Qt client no longer existed, so the cost of the change was at its lowest.

• The second row for each tick. The ingest stored each tick twice, once at its own time and once under the previous business day, so that a fresh environment could value a report at the previous close. The second row was the newest value under another date, and it doubled the writes. Decision of the owner, 2026-10-21: the ingest stores only the real tick. A close is a question for the reader, which asks for the latest tick at or before the end of the day.

• Indexes. The table has seven indexes and a trigger that closes the previous current point. Each index costs a write. A measurement with a batch decides which of them the hot path needs.

The first pass of step 0 times batched inserts into scratch tables of one environment, on one connection. The rows are made inside the database, so the figures leave out the client and the network. They are upper bounds. The script and the raw output are in the evidence folder of the investigation task.

Rows in a transaction	Observation table, as built	Same table, no trigger	Append-only table
1	231 each second	273	226
100	6,024	18,225	22,890
1,000	7,815	28,716	111,028
10,000	8,301	32,751	186,824
Bytes for each row, with indexes	1,199	942	162

Four facts follow.

• One row for each commit is commit-bound. The three shapes give 226 to 273 rows each second, and that is the 230 syncs each second of the disk.
• Batching alone does not help the present table. It rises from 231 to 8,301 rows each second and stops there. The trigger and its two lookups of the tenant table for each row set the ceiling. Removing the trigger gives four times more, and the seven indexes then set a new ceiling of about 33,000.
• The append-only table is 22 times faster than the present one. One index and no trigger give 187,000 rows each second on one connection, at 162 bytes for each row.
• COPY from a client file loaded 300,000 rows into the table without a trigger in 7.3 seconds, which is 41,000 each second, and into the append-only table in 1.6 seconds, which is 188,000 each second.

The demand of 276,000 rows each second needs 34 connections of the present table, at perfect scaling, and a disk that writes 330 megabytes each second. It needs two connections of the append-only table, and a disk that writes 45 megabytes each second. With one row for each source it needs less than one connection.

These figures have limits. The scratch tables were empty at the start, so the indexes were small and warm. The server was shared with four other environments. Row-level security and the tenant-aware connection were not in the test. The second pass below removes the first and the third limit and states what stays.

Tick processing is an old problem, and the industry has settled on a small number of shapes. This section lists them. The sources are linked where this session read them. Claims that come from a vendor are marked as such.

It follows practice in these points.

• It has a receive stage, a fan-out to live consumers, and a store writer, which is the tickerplant, the real-time store and the history writer in the kdb+ shape.
• A lane that conflates for its live consumers is a chained tickerplant.
• Writes to the store are batched with an explicit commit interval, as in QuestDB, ClickHouse and TimescaleDB.
• The recent window is the real-time database of a kdb+ system, held in each lane. It answers recent reads from memory, and the history store serves only older data.
• The store has no change signal for each row, because none of these systems has one.

It departs from practice in two points, and both follow from the requirements.

• No replay log. A tickerplant logs every tick so that a restart can replay it. This pipeline keeps no log, because a replayed tick is a stale tick. A restart resumes at the newest tick.
• Age-based shedding. A tickerplant keeps every tick and lets a slow subscriber fall behind. This pipeline drops old ticks and keeps the newest.

• The LMAX Disruptor (Thompson, Farley and others, 2011). A ring buffer with slots allocated once, sequence numbers, one writer for each ring, and a consumer that takes every available event as a batch. It avoids locks and queue contention. This pipeline takes four ideas from it: the bounded ring, the pre-allocated slots, the single writer, and batching without a timer. It does not take the library, the busy-spin wait strategies or the graph of consumers. The cost here is milliseconds for a commit and not nanoseconds for a hand-off, so a plain mutex and a condition variable are enough for the receive side.
• The single writer principle (Martin Thompson, "Mechanical Sympathy"). One thread owns each piece of mutable state. The lane follows it: one writer owns the ring and the connection of its lane.
• Reactive libraries. Reactive Streams libraries name the same drop-the-old idea onBackpressureLatest. A consumer that cannot keep up gets the newest item and not the oldest.

• A durable JetStream consumer as the buffer for quotes. It makes the backlog visible, and it loses nothing at a restart. It also replays old ticks in order, and requirement 1 forbids that. It fits data that must not be lost, and the durable tier uses it. It does not fit a perishable tick.
• Back-pressure that blocks the producer, for quotes. It protects every tick and makes the newest tick wait for the oldest. It breaks requirement 1. The durable tier uses it.
• A JetStream stream with one message for each subject, for late joiners. It holds the last value. The window in each lane holds it already, and it also holds the recent run that a chart needs. A second copy would need its own upkeep.
• A GPU. The work waits for commits and locks and does little arithmetic. A GPU has nothing to speed up on this path.
• A column store for the history now. A column store takes more than a million rows each second. It is a large change, and a batched write to the present store may be enough. Measure first. The decision belongs to steps 0 and 3 below.

Risk	What would show it	What changes if it is true
PostgreSQL cannot sustain the batch rate. The target is about 29,000 rows each second, one for each source tick, and about 116,000 in an active market. Step 0 shows that the present table cannot, at 8,300 each second for each connection, and that an append-only table can, at 187,000.	The next pass of step 0, from the application. It writes batches of 5,000 rows into the hypertable with the notification trigger removed, and it times the rate for each batch size.	Move the history to a column store sooner. Cut the fan-out first: one row for each source, and no second row for each tick.
One thread decodes every tick.	The busy share of the receive thread at 30,000 ticks each second.	Run several ingest instances, as the section on scale describes.
One series is much hotter than the others.	The busy share of each lane.	Split the lane key below the series, and give up whole-series operations for that class.
The volume of rows exceeds what the store holds.	The bytes for each row and the rows each day, measured in step 0.	Store bars only for the high-rate classes, and keep raw ticks for a short time.
The trigger and the indexes cap the rows each second of one connection.	The step 0 table with and without the trigger and with fewer indexes.	The append-only tick table of the store section.
The tick streams of JetStream cap the rate of the producers.	The rate of a feed with and without the acknowledgement.	Core publish, and no stream, for perishable subjects.
The windows use too much memory.	The memory of a lane against the number of watched datums.	Bound the window by bytes, and keep a bucketed window.
The live subject change breaks a consumer.	A search for every subscriber of the republish subject. Hedgr is the only client.	Migrate the client in the same wave, and keep no old subject.

When the pipeline replaces the loop, these notes change: the ingest loop note, which states that a republish follows the stored row; the note on adding an asset class, which gains the rules above; the PostgreSQL architecture note, which states that a time-series table sets the notification knob to false; the notes on the producer subject and the market tick, which state the live subject; and the subscription note. No client of the observation change event exists. Hedgr has none, and the Qt client is decommissioned.

Each step ends with a measurement. The test tool is the tick injector, which sends a burst of ticks at a chosen rate and times the drain.

1. Prove the write ceiling. Build a prototype of the batch writer and time it. No product code changes. Find how sqlgen::insert writes a vector: the library splits it into batches of 50,000 rows, and the form of the statement is not yet verified, so the prototype may need COPY from libpq. Acceptance: a table of rows each second for each batch size, with and without the trigger that closes the previous point, with the append-only table, and with the policy of one tenant for each batch. It also states the bytes for each row. The result decides whether the later steps need a column store.
2. Remove the per-row costs. Step 0 shows that batching the present table stops at 8,300 rows each second, so this step moves ticks to the append-only table. Drop the notification trigger of the observation table, add a series cache and a batch writer, and stop waiting for acknowledgements on the live publish and on the producers. Remove the two tick streams for perishable subjects. Add the lag gauge. Acceptance: the burst drain rate reaches the rate that step 0 measured, and the lag gauge shows the lag of a loaded system. The ten-fold gain is a target and not a result until step 0 confirms it.
3. Lanes, the ring, the age gate, the overload states and the recent window. Add the history call, which the window and the store answer in one reply, and split the live subject into tokens. Move Hedgr to both: a subscription for each watched series, and a history call. Acceptance: a burst of ten times the sustained rate leaves no backlog after the burst ends. The lag of the live path stays below the age limit at all times. A chart opens from the window in under 100 ms and does not read the store for the span that the window covers.
4. Scale out, and open the policy table. Several lanes, each with its own connection. The policy table with its default row, the durable tier, the taps that replace the CRM if, and a rollout by source: a setting moves one source at a time to the new pipeline. Tune the indexes and chunks. Decide on a column store for history. Acceptance: 30,000 ticks each second for ten minutes, with the lag of the store below 1 second at the 99th percentile.

• Does a stream that can carry a tick that the store dropped cause a problem for any consumer? This note accepts it. The stream is the live view, and the table is the record of what the pipeline wrote.
• What is the age limit for each asset class? Interest rate curves tick slowly, and FX spot ticks fast.
• Should the CRM bridge read from the conflated live path and not from the store path?
• Is the second row for each tick worth its cost at scale?
• Should the ring of Hedgr overwrite its oldest bucket when it is full, to follow the freshness rule?
• How large is the recent window? The starting bound is 600 ticks or 10 minutes for a watched datum. Hedgr keeps 2 hours of 1 second points for a watched series, so a longer window or a bucketed window may be needed.
• How does the lane learn which datums a consumer watches? A subscription, a request to open a chart, or a setting for each source are the choices.
• Which tokens does the live subject need beyond the instrument type and the series? A venue or a currency token would let a client select more finely, and each token adds a level to every subscription.
• Does any client need to learn that a series changed? If so, the signal belongs on the live tick stream, at most once each second for each series. A database notification is not the way.
• Which classes need bars and which need a reducer? The answer comes from the first equity and option feeds. The default row serves FX and rates.
• How does a client learn that a curve published as one consistent set? The tick has no field for it today, so a curve may need a set marker on the tick.
• Does any series need a lane key below the series? A very large surface in one series may overload one lane. The busy share of each lane answers it.
• Does the reader of the store need the bitemporal observation table for ticks? No. The readers of snapshots, evolution, slices and the ORE export read the tick table and the observation table side by side, and take the latest value of each datum. They need no view.
• How long does the store keep raw ticks, and which classes keep bars only? The answer is a policy row for each class.
• Who provisions the writer role with BYPASSRLS, and does the project accept a role that skips the policy for writes? The section on tenant and party isolation gives the design and the measurements.
• Should the session functions of the policies change for every table? The section on tenant and party isolation shows a seven-fold cost on an indexed read.
• The deployment runs TimescaleDB under the Timescale licence, which compresses chunks and schedules the retention and compression policies. See The tick store is bounded.
• How many lanes does one server need, and how many connections can the database give them?

• How does the lane learn that a datum is watched? If the subscription tells it, the window fills from the subscription on, and a tick that is in neither the reply nor the buffer shrinks to the ticks of one batch.
• Does the first build keep the ceiling of 25,000 ticks? The measurement of reply size and of read time decides.
• Can a tick fall between the store and the window, as the two tiers section describes? The measurement of the batch delay against the first reply decides whether the call must wait for the commit of the batch in flight.
• The live subject names a series by the percent-encoded series URI, and this call names it by id. The client reads both from the series catalogue. Should the live subject carry the id instead, so that one name serves both calls?
• Should the reply carry the source_id? This page says no. A client that audits a tick reads the store.