A Stock Screener Is a Database Problem

Jan 24, 2019

Published January 24, 2019

Eight thousand listed equities fit in memory. That fact causes many stock screeners to be designed badly.

A developer loads a table, loops through every row, evaluates several conditions, sorts the survivors, and returns the first page. The query completes in 20 milliseconds on a laptop. The implementation appears finished.

It is not finished.

The same code becomes expensive when several thousand users change filters at the same time, when each screen contains twelve predicates, when every result needs a composite rank, and when the service must refresh immediately after a market data update. A 20 millisecond loop becomes CPU saturation, long request queues, stale caches, and p95 latency measured in seconds.

A fast stock screener is not primarily a database problem. It is a data layout and query execution problem.

The useful architecture has four parts:

1. Store screening fields as contiguous columns rather than Python objects.
2. Evaluate ad hoc numeric conditions with vectorized operations.
3. Precompute packed bitmap indexes for common predicates.
4. Rank only the reduced candidate set, not the entire universe.

This design is simple enough to run inside one process. It also scales cleanly into a distributed service because the read path is immutable and deterministic.

The row loop is the wrong abstraction

Consider a common value and quality screen:

• Market capitalization of at least $2 billion
• Positive price-to-earnings ratio no greater than 18
• Average daily dollar volume of at least $10 million
• Return on assets of at least 8 percent

The direct Python implementation is readable:

def screen_rows(rows):
    matches = []

    for row in rows:
        if (
            row["market_cap"] >= 2_000_000_000
            and 0 < row["pe"] <= 18
            and row["dollar_volume_20d"] >= 10_000_000
            and row["roa"] >= 0.08
        ):
            matches.append(row["security_id"])

    return matches

The problem is not big-O notation. This is still O(n), and an O(n) scan over a small universe is acceptable. The problem is the execution model.

Each iteration performs Python bytecode dispatch, dictionary lookups, dynamic type checks, branch evaluation, and list operations. The CPU receives a stream of small dependent tasks. The data is scattered across objects. Cache locality is poor.

A columnar representation changes the work. Each field becomes one contiguous array. NumPy evaluates the predicates in compiled loops over homogeneous memory.

from dataclasses import dataclass

import numpy as np


@dataclass(frozen=True)
class ScreeningColumns:
    security_id: np.ndarray
    market_cap: np.ndarray
    pe: np.ndarray
    dollar_volume_20d: np.ndarray
    roa: np.ndarray


def screen_columns(columns):
    mask = (
        (columns.market_cap >= 2_000_000_000)
        & (columns.pe > 0)
        & (columns.pe <= 18)
        & (columns.dollar_volume_20d >= 10_000_000)
        & (columns.roa >= 0.08)
    )

    return columns.security_id[mask]

This code is also O(n). It is materially faster because the constant factors are different. The arrays are compact, the loops run in native code, and the processor can use vector instructions and predictable memory access.

For a stock screener, asymptotic analysis alone is not enough. Data representation determines whether the CPU spends its time evaluating financial predicates or interpreting the host language.

Separate filtering from ranking

Many screeners combine two different operations into one query:

• Filtering decides which securities are eligible.
• Ranking decides which eligible securities should appear first.

They should not share the same execution strategy.

Filtering is mostly boolean algebra. Ranking is numeric selection. A service that sorts the entire universe before applying restrictive filters performs unnecessary work.

Assume 100,000 instruments and a filter that admits 1,800 candidates. The expensive version computes a score for all 100,000 rows and performs a full sort. The efficient version intersects filters first, computes scores for 1,800 rows, and selects the best 50.

For top K results, a full O(n log n) sort is unnecessary. NumPy’s argpartition performs partial selection and then sorts only the selected tail.

import numpy as np


def top_k_candidates(candidate_mask, score, k=50):
    candidate_index = np.flatnonzero(candidate_mask)

    if len(candidate_index) <= k:
        return candidate_index[np.argsort(score[candidate_index])[::-1]]

    candidate_scores = score[candidate_index]
    local_top = np.argpartition(candidate_scores, -k)[-k:]
    top_index = candidate_index[local_top]

    return top_index[np.argsort(score[top_index])[::-1]]

The distinction matters under load. Filtering should reduce cardinality as early as possible. Ranking should touch only the resulting candidates.

This is the same principle used in query optimizers: apply selective predicates before expensive operators.

A bitmap index turns filters into bitwise operations

Vectorized scans are sufficient for many systems. They still evaluate every predicate against every row on every request.

That becomes wasteful when users repeatedly ask variants of the same common questions:

• Is the company profitable?
• Is the security sufficiently liquid?
• Is market capitalization above a standard threshold?
• Is the price-to-earnings ratio positive?
• Is the instrument in a particular exchange, country, sector, or asset class?

These predicates can be represented as bitmaps.

For one million instruments, a boolean condition needs one million bits, or 125,000 bytes before container overhead. Intersecting five conditions becomes a few bitwise AND operations over compact contiguous memory.

The implementation below uses NumPy’s packed bytes. It builds an immutable index for several common predicates and returns matching row positions.

import numpy as np


class PackedScreeningIndex:
    def __init__(self, columns):
        self.size = len(columns.security_id)
        self.security_id = columns.security_id

        masks = {
            "large_cap": columns.market_cap >= 2_000_000_000,
            "positive_pe": columns.pe > 0,
            "pe_at_most_18": columns.pe <= 18,
            "liquid": columns.dollar_volume_20d >= 10_000_000,
            "roa_at_least_8pct": columns.roa >= 0.08,
        }

        self.bitmaps = {
            name: np.packbits(mask.astype(np.uint8))
            for name, mask in masks.items()
        }

    def query(self, predicate_names):
        if not predicate_names:
            return self.security_id.copy()

        packed = self.bitmaps[predicate_names[0]].copy()

        for name in predicate_names[1:]:
            packed &= self.bitmaps[name]

        mask = np.unpackbits(packed)[: self.size].astype(bool)
        return self.security_id[mask]

The query path performs no Python loop over securities. It loops only over the requested predicates. The heavy operation is a native bitwise intersection over packed memory.

This example uses fixed thresholds. A production stock screener needs arbitrary values such as P/E below 14.5 or market capitalization above $7.3 billion. There are three practical approaches:

1. Use bitmaps for categorical fields and common threshold presets, then run exact vectorized predicates on the reduced candidate set.
2. Build cumulative bitmap ranges for selected numeric breakpoints.
3. Keep sorted numeric indexes and use binary search to produce candidate row sets.

The first approach is usually the best starting point. It captures most of the benefit without turning every numeric field into a complex index-maintenance problem.

Order predicates by selectivity and cost

Boolean expressions are mathematically commutative. Their execution cost is not.

Suppose a request contains these filters:

• United States listings
• Market capitalization above $2 billion
• Positive free cash flow
• A custom earnings stability calculation

The custom calculation may require several arrays and floating-point operations. It should not run across the full universe if the first three predicates reduce the candidates by 95 percent.

A basic planner can attach two estimates to each predicate:

• Selectivity: expected fraction of rows that survive
• Cost: expected CPU work per input row

Cheap, selective predicates should run first. Expensive predicates should run after candidate reduction.

The planner does not need a full SQL optimizer. Historical hit counts are enough. Track the number of rows before and after each predicate and maintain an exponentially weighted estimate by field and operator.

A useful ordering score is:

estimated_cost_per_row / expected_fraction_removed

Lower values run first. Bitmap predicates generally dominate because their per-row cost is small and their intersections are compact.

The main operational benefit is predictability. A screen containing one expensive custom factor no longer forces the service to compute that factor for every instrument.

Build immutable screening snapshots

A screening service should not mutate arrays while requests are reading them.

Use immutable snapshots with explicit version identifiers. A snapshot should contain:

• Security identifiers in stable row order
• Column arrays for all filterable fields
• Packed bitmap indexes
• Ranking inputs
• A schema version
• An effective timestamp

Build the next snapshot outside the request path. Validate it. Then replace one process-level reference atomically.

The request path becomes:

snapshot = current_snapshot
plan = compile_query(request, snapshot.schema_version)
candidates = execute_filters(plan, snapshot)
results = rank(candidates, request.sort, snapshot)

Every operation in one request reads the same snapshot. No locks are required for the arrays because they never change after publication.

This also makes cache keys precise. A correct result cache includes at least:

snapshot_version + normalized_filter_expression + ranking_definition + page

Caching only the raw URL is insufficient if two nodes serve different data versions.

Immutable snapshots also simplify rollback. If a newly built snapshot fails validation after publication, the service can restore the previous reference without reconstructing state.

Memory is cheaper than repeated interpretation

A stock screener is an unusually favorable workload for in-memory columnar execution.

One million float64 values occupy approximately 8 MB. Ten numeric columns occupy approximately 80 MB. A packed bitmap for one million rows occupies approximately 125 KB. Two hundred bitmap predicates occupy approximately 25 MB, excluding small object overhead.

The same information stored as one million Python dictionaries can consume several times more memory because every row carries hash tables, pointers, boxed numbers, and allocator overhead.

The columnar design is faster and often smaller.

Memory mapping is useful when snapshots exceed comfortable process memory or when multiple worker processes need the same immutable arrays. NumPy can read .npy arrays with mmap_mode="r", allowing the operating system’s page cache to share physical pages across workers.

import numpy as np


market_cap = np.load("market_cap.npy", mmap_mode="r")
pe = np.load("pe.npy", mmap_mode="r")
roa = np.load("roa.npy", mmap_mode="r")

The request process should still avoid random access across many unrelated files. Group fields by access pattern, keep hot columns resident, and measure page faults under production-like concurrency.

Benchmark the execution model, not a toy endpoint

Average latency hides queueing behavior. Measure p50, p95, and p99 across realistic query mixes.

A useful benchmark should include:

• Several universe sizes
• Repeated queries after warm-up
• Common and rare predicates
• Candidate ranking
• Serialization of the first result page
• Concurrent requests
• Snapshot replacement during reads

The synthetic benchmark below isolates predicate evaluation. It compares a Python row scan, a NumPy boolean mask, and a packed bitmap intersection. The bitmap build cost is excluded because indexes are constructed with the immutable snapshot, not per request.

import time

import numpy as np


def make_data(size):
    random = np.random.RandomState(7)

    return {
        "market_cap": random.lognormal(21.0, 1.35, size),
        "pe": random.normal(19.0, 12.0, size),
        "dollar_volume": random.lognormal(16.0, 1.8, size),
        "roa": random.normal(0.06, 0.09, size),
    }


def row_scan(data):
    market_cap = data["market_cap"]
    pe = data["pe"]
    dollar_volume = data["dollar_volume"]
    roa = data["roa"]
    matches = []

    for i in range(len(market_cap)):
        if (
            market_cap[i] >= 2_000_000_000
            and 0 < pe[i] <= 18
            and dollar_volume[i] >= 10_000_000
            and roa[i] >= 0.08
        ):
            matches.append(i)

    return matches


def vector_scan(data):
    mask = (
        (data["market_cap"] >= 2_000_000_000)
        & (data["pe"] > 0)
        & (data["pe"] <= 18)
        & (data["dollar_volume"] >= 10_000_000)
        & (data["roa"] >= 0.08)
    )

    return np.flatnonzero(mask)


def build_bitmap_index(data):
    masks = {
        "large_cap": data["market_cap"] >= 2_000_000_000,
        "positive_pe": data["pe"] > 0,
        "pe_at_most_18": data["pe"] <= 18,
        "liquid": data["dollar_volume"] >= 10_000_000,
        "profitable": data["roa"] >= 0.08,
    }

    return {
        name: np.packbits(mask.astype(np.uint8))
        for name, mask in masks.items()
    }


def bitmap_scan(index, size):
    packed = index["large_cap"].copy()

    for name in ("positive_pe", "pe_at_most_18", "liquid", "profitable"):
        packed &= index[name]

    return np.flatnonzero(np.unpackbits(packed)[:size])


def p95_milliseconds(function, repetitions=12):
    samples = []

    for _ in range(repetitions):
        started = time.perf_counter()
        function()
        samples.append((time.perf_counter() - started) * 1_000)

    return float(np.percentile(samples, 95))


def run():
    for size in (5_000, 25_000, 100_000, 500_000, 1_000_000):
        data = make_data(size)
        index = build_bitmap_index(data)

        row_ms = p95_milliseconds(lambda: row_scan(data))
        vector_ms = p95_milliseconds(lambda: vector_scan(data))
        bitmap_ms = p95_milliseconds(lambda: bitmap_scan(index, size))

        print(size, row_ms, vector_ms, bitmap_ms)


if __name__ == "__main__":
    run()

One representative run produced the following p95 query times:

Absolute numbers vary by processor, NumPy build, memory speed, and thermal state. The important result is the slope. Python interpretation grows into the request budget. Contiguous native operations remain small enough to leave time for ranking, serialization, network transit, and queueing.

The logarithmic latency scale below makes all three execution models visible without hiding the lower two near zero.

import React from 'react';
import { AxisBottom, AxisLeft } from '@visx/axis';
import { curveMonotoneX } from '@visx/curve';
import { GridRows } from '@visx/grid';
import { Group } from '@visx/group';
import { ParentSize } from '@visx/responsive';
import { scaleLog } from '@visx/scale';
import { LinePath } from '@visx/shape';

const observations = [
  { rows: 5000, row: 0.79, vector: 0.027, bitmap: 0.021 },
  { rows: 25000, row: 3.89, vector: 0.058, bitmap: 0.051 },
  { rows: 100000, row: 17.10, vector: 0.231, bitmap: 0.177 },
  { rows: 500000, row: 83.00, vector: 1.20, bitmap: 0.887 },
  { rows: 1000000, row: 160.80, vector: 2.36, bitmap: 1.71 },
];

const series = [
  { key: 'row', label: 'Python row scan', stroke: '#8b1e1e' },
  { key: 'vector', label: 'NumPy column scan', stroke: '#1f4e79' },
  { key: 'bitmap', label: 'Packed bitmap', stroke: '#2f6b3b' },
];

function formatRows(value) {
  if (value >= 1000000) return `${value / 1000000}m`;
  if (value >= 1000) return `${value / 1000}k`;
  return String(value);
}

function formatLatency(value) {
  if (value < 0.1) return `${Math.round(value * 1000)}us`;
  if (value < 10) return `${value.toFixed(1)}ms`;
  return `${Math.round(value)}ms`;
}

function InnerScreeningLatencyChart({ width = 760 }) {
  const chartWidth = Math.max(width, 640);
  const height = 420;
  const margin = { top: 34, right: 36, bottom: 58, left: 72 };
  const innerWidth = chartWidth - margin.left - margin.right;
  const innerHeight = height - margin.top - margin.bottom;

  const xScale = scaleLog({
    domain: [5000, 1000000],
    range: [0, innerWidth],
  });

  const yScale = scaleLog({
    domain: [0.01, 300],
    range: [innerHeight, 0],
  });

  return (
    <svg
      width={chartWidth}
      height={height}
      role="img"
      aria-label="P95 stock screener latency by instrument count"
    >
      <rect width={chartWidth} height={height} fill="#ffffff" />

      <text x={margin.left} y={20} fontSize={15} fontWeight={600}>
        P95 screening latency by execution model
      </text>

      <Group left={margin.left} top={margin.top}>
        <GridRows
          scale={yScale}
          width={innerWidth}
          stroke="#d8dde3"
          strokeDasharray="3,3"
          numTicks={6}
        />

        {series.map((item) => (
          <Group key={item.key}>
            <LinePath
              data={observations}
              x={(datum) => xScale(datum.rows)}
              y={(datum) => yScale(datum[item.key])}
              curve={curveMonotoneX}
              stroke={item.stroke}
              strokeWidth={2.25}
            />

            {observations.map((datum) => (
              <circle
                key={`${item.key}-${datum.rows}`}
                cx={xScale(datum.rows)}
                cy={yScale(datum[item.key])}
                r={3.25}
                fill="#ffffff"
                stroke={item.stroke}
                strokeWidth={2}
              />
            ))}
          </Group>
        ))}

        <AxisLeft
          scale={yScale}
          numTicks={6}
          tickFormat={formatLatency}
          stroke="#4a5560"
          tickStroke="#4a5560"
          tickLabelProps={() => ({
            fill: '#30363d',
            fontSize: 11,
            textAnchor: 'end',
            dx: '-0.35em',
            dy: '0.3em',
          })}
          label="P95 query latency"
          labelProps={{
            fill: '#30363d',
            fontSize: 12,
            textAnchor: 'middle',
          }}
        />

        <AxisBottom
          top={innerHeight}
          scale={xScale}
          tickValues={[5000, 25000, 100000, 500000, 1000000]}
          tickFormat={formatRows}
          stroke="#4a5560"
          tickStroke="#4a5560"
          tickLabelProps={() => ({
            fill: '#30363d',
            fontSize: 11,
            textAnchor: 'middle',
            dy: '0.6em',
          })}
          label="Instrument rows"
          labelProps={{
            fill: '#30363d',
            fontSize: 12,
            textAnchor: 'middle',
          }}
        />

        <Group left={12} top={10}>
          {series.map((item, index) => (
            <Group key={item.key} top={index * 21}>
              <line
                x1={0}
                x2={20}
                y1={0}
                y2={0}
                stroke={item.stroke}
                strokeWidth={2.25}
              />
              <text x={28} y={4} fontSize={11} fill="#30363d">
                {item.label}
              </text>
            </Group>
          ))}
        </Group>
      </Group>
    </svg>
  );
}

export default function ScreeningLatencyChartShell() {
  return (
    <ParentSize>
      {({ width }) => <InnerScreeningLatencyChart width={width} />}
    </ParentSize>
  );
}

Tail latency is a capacity problem

A 20 millisecond query is not automatically safe. At 500 requests per second, it represents ten CPU-seconds of work per wall-clock second before ranking, JSON encoding, logging, and network handling.

The service will queue unless it has enough cores and no other bottleneck. Once utilization approaches saturation, p95 and p99 latency rise sharply even when median execution time remains stable.

This is why a 10x reduction in per-query CPU time is more valuable than a 10x reduction in an already small database lookup. It increases capacity and reduces queueing variance.

Measure CPU time per request, not only wall-clock time. A request that waits 300 milliseconds and executes for 3 milliseconds has a capacity or scheduling problem. A request that consumes 300 milliseconds of CPU has an algorithm or data layout problem.

Do not cache arbitrary result sets forever

Screening queries have a large combinatorial space. A cache keyed by every possible filter expression can grow without bound and deliver a low hit rate.

Cache at three levels instead:

1. Snapshot data: Immutable arrays and indexes remain resident for all requests.
2. Compiled query plans: Normalize equivalent expressions and cache predicate ordering and field bindings.
3. Popular result pages: Cache only high-frequency queries with short expiration and explicit snapshot versions.

Canonicalization is essential. These requests are equivalent:

market_cap >= 2000000000 AND pe <= 18
pe <= 18 AND market_cap >= 2000000000

Sort commutative predicates, normalize numeric units, remove redundant clauses, and serialize the expression deterministically before hashing it.

Do not cache across snapshot versions unless the product explicitly allows stale results. Financial users notice when a detail page and a screener disagree.

Common failure modes

Building indexes during requests

An index is useful only when its construction cost is amortized. Build packed bitmaps when publishing the snapshot. Do not call packbits inside the request handler.

Packing every possible threshold

Numeric values are not categories. Thousands of cumulative threshold bitmaps can consume memory and complicate updates. Index common breakpoints and use exact vectorized filtering for the residual condition.

Sorting before filtering

A global sort wastes CPU and memory bandwidth. Filter first, calculate scores for candidates, then use partial selection.

Using unstable row identifiers

Bitmap positions refer to snapshot row positions, not permanent security identities. Keep a stable security_id column and never expose row offsets outside the snapshot implementation.

Ignoring null semantics

Missing fundamentals should not silently pass a comparison. Define null behavior for every operator. For example, pe <= 18 should normally reject missing P/E values, while pe is null should be an explicit predicate.

Benchmarking one warm query

Measure cold process startup, warm steady state, concurrent access, snapshot swaps, and realistic result serialization. One isolated timing from an interactive shell is not a capacity plan.

A practical stock screener architecture

A compact production design can remain deliberately boring:

• A batch job builds versioned column arrays and common bitmap indexes.
• Validation checks dimensions, field ranges, uniqueness, and aggregate counts.
• The completed snapshot is written to immutable storage.
• API workers memory-map or load the snapshot.
• A lightweight compiler converts request filters into ordered predicates.
• Bitmap intersections reduce the candidate set.
• NumPy evaluates exact residual conditions.
• argpartition selects top K results.
• The service serializes only requested columns for the current page.
• Metrics record candidate counts, CPU time, wall time, cache status, and snapshot version.

This architecture does not require a specialized distributed database for a typical equity universe. It requires disciplined separation between snapshot construction and query execution.

The main optimization is not a clever algorithm. It is refusing to pay Python object overhead, repeated predicate work, and global sorting costs on every request.

A fast Python stock screener is possible because the workload is mostly boolean algebra over stable columns. Store the data in the form the processor can evaluate efficiently, precompute the predicates users request repeatedly, and reserve expensive calculations for the small candidate set that survives.