learnkdb
Back to Performance & Troubleshooting

Attributes · Performance & Troubleshooting · Practice Question

If You Append New Data to an Existing Partitioned Table, How Do You Maintain the Attributes?

Sign in to track your progress

Question

After appending new rows to an on-disk HDB partition, the p# attribute on sym is lost. How do you maintain attributes correctly after appending to an existing table?

Solution

/ Starting state: existing partition with p# on sym
attr (select sym from trade where date=2025.01.01)`sym    / returns `p

/ Step 1: Append new rows
newRows: ([] time:enlist 09:35:00; sym:enlist`AAPL; price:enlist 151.2; size:enlist 100)
`:/data/hdb/2025.01.01/trade/ upsert .Q.en[`:/data/hdb;] newRows

/ Attribute is now lost
attr (select sym from trade where date=2025.01.01)`sym    / returns `

/ Step 2: Re-sort on disk by sym
`sym xasc `:/data/hdb/2025.01.01/trade/

/ Step 3: Re-apply the parted attribute
@[`:/data/hdb/2025.01.01/trade/; `sym; `p#]

/ Step 4: Reload so the attribute is visible
\l /data/hdb

/ Confirm
attr (select sym from trade where date=2025.01.01)`sym    / returns `p

Explanation

p# requires all occurrences of each symbol to be contiguous. When you append rows, the new rows are added to the end of each column file — so an AAPL row appended after the GOOG block breaks the contiguity. KX's documentation states that only the sorted attribute is preserved when appending to a column on disk, so the append removes p# regardless.

The mandatory post-upsert sequence for HDB partitions:

  1. xasc on disk — `sym xasc `:/path/to/partition/ re-sorts all the table's column files by sym, so all AAPL rows are together, all GOOG rows are together, and so on, and writes them back. (It leaves the sorted attribute on sym; step 2 replaces it with p#.)

  2. Re-apply p# — after sorting, the contiguity guarantee is restored and p# can be applied to the on-disk path.

  3. Reload — the HDB's in-memory view of the partition must be refreshed with \l for the updated attribute to take effect. Queries against the reloaded partition will now use the block index.

For in-memory tables in the RDB, the situation is different. The RDB uses g# on sym, which is maintained on append — no post-append action is required. This is one of the key reasons g# is used in the RDB rather than p#.

Rule of thumb: for any on-disk HDB append, always plan for a sort + attribute reapplication + reload cycle. In production systems, this is typically handled by the EOD sort process and automated by monitoring that checks attr after every partition update.