Skip to content
algo150Open the app
Frontend system design ยท about 40 minutes

Data table over 100,000 rows

Design a data table for an admin product, for example a list of orders. The data has about 100,000 rows and 20 columns. Users sort by a column, filter by text and by column values, and look through many rows fast. They can show, hide, reorder and resize columns, and their column setup is remembered. They select rows for bulk actions. The table must work with a keyboard and a screen reader, and a filtered view must be shareable as a link.

Outline it in the app

Ask first

  • Does the browser get all the data, or does the server sort, filter and page it?
  • Do users want pages or continuous scrolling? Must they jump to a place or see the total?
  • Are the cells read-only, or can users edit them in the table?
  • Do rows change while the user looks at them, and how fresh must they be?
  • Which column types exist: text, number, date, status, actions?
  • Is the column setup saved per user on the server, or only in this browser?

The outline

Write your own outline for each part first, then open it to compare with the points a strong answer covers.

Requirements
  • Functional: sort, filter, page or scroll, choose and order columns, select rows, run bulk actions
  • A sort or a filter shows its result in about a second, with visible progress when it takes longer
  • Scrolling is smooth, and the DOM holds only the rows near the viewport
  • Sort, filters and page are in the URL; the column setup is remembered
  • Headers, cells and row actions can be reached with the keyboard, and the sort state is announced
  • The table body has loading, empty (no rows match) and error states
Architecture
  • With 100,000 rows the server sorts, filters and pages; the client sends the table state and renders one window of rows
  • A table controller (a hook) owns the table state and builds the query; the view components only render
  • A query cache, keyed by sort, filters and page, keeps earlier pages, so going back is instant
  • Either pages, which give jumps and totals, or virtual scrolling that loads blocks of rows as the user scrolls
  • Columns are data: id, header, accessor, cell renderer, sortable, width; the table renders from this list
  • If the data must stay in the browser, sorting and filtering move to a Web Worker, and the rows are virtualized
Data model
  • Table state: sort (column and direction), filters per column, search text, page and page size (or a cursor)
  • Column state: order, visibility and widths; saved per user and kept apart from the URL state
  • Rows are stored by id; the visible order is a list of ids for the current query
  • Selection is a set of row ids, plus an 'all rows that match the filter' mode that the server resolves; it is never a list of row positions
  • Query result: rows, the total count, and the query it belongs to, so a stale response can be ignored
Interface
  • GET /orders?sort=-createdAt&status=paid&q=smith&page=3&pageSize=50 returns { rows, total }
  • DataTable props: columns, rows, total, state, onStateChange, loading, error, getRowId, renderEmpty
  • Column definition: { id, header, accessor, cell?, sortable?, width?, align? }
  • The table is controlled: the parent owns sort, filters and page, so it can keep them in the URL
  • Bulk action: POST /orders/bulk with { action, ids } or { action, filter } for all matching rows; the response has a result per row
  • PUT /users/me/table-settings/orders { order, hidden, widths } saves the column setup
Optimizations
  • Virtualize rows, and columns if there are many: render the visible window and a few extra rows; a fixed row height keeps it simple
  • The text filter is debounced and cancels the request before it; the old rows stay on screen, dimmed, while new rows load
  • The database has indexes for the sortable and filterable columns, and the sort has a tie-breaker on id, so pages do not overlap
  • Row and cell components are memoized and column definitions are stable, so selecting one row does not render every row again
  • Accessibility: a real table with th elements and scope, aria-sort on the sorted header, and a button inside each sortable header
  • With virtual rows, aria-rowcount and aria-rowindex tell assistive technology the real size and position
  • A sticky header, and column widths that stay fixed during loading, so the layout does not jump

Pitfalls

  • Rendering all 100,000 rows in the DOM
  • Sorting only the current page on the client while the server holds the rest of the data
  • A table made of div elements with no table or grid semantics
  • Selection stored as row positions, which breaks after a sort or a filter
  • A sort with no tie-breaker, so rows repeat or disappear between pages

Follow-ups an interviewer asks

  • Sorting on the client or on the server: where is the limit, and why?
  • Pages or virtual scrolling for this table? What does each one cost?
  • The user selects all 100,000 rows and presses Delete. How does that work?
  • How does a screen reader user know which column is sorted?
  • Rows have different heights. What changes in the virtualization?
  • A later release adds a new column. What happens to a saved column setup?