sqlit performance study

Abstract

A measured investigation of sqlit identifies a severe autocomplete bottleneck and smaller sources of unnecessary startup and idle work. The strongest implemented result is a 98.1% reduction in completion work plus dropdown refresh with 5,000 stored routines: the median falls from 1,841.2 ms to 34.6 ms. An index replaces a quadratic scan without changing the observed suggestions. Empty-queue scheduling changes reduce explorer-focus idle CPU by 72.0%, and honoring a saved disabled-worker preference reduces launch-and-exit CPU by 16.1%.

The study contains 1349 recorded observations from 152 sequential jobs, plus diagnostic pilots. It exercises the actual Textual application in pseudo-terminals, five real database engines, a controlled network-delay relay, and alternative rendering and serialization strategies. Database experiments show approximately 98% less response payload when a 50,000-row query is explicitly bounded to the first 1,001 rows. Bulk rendering and threaded preparation finish sooner but produce longer event-loop stalls. A sampled, row-backed prototype is promising for CPU and result availability, with substantial feature-contract work remaining.

Default first-refresh startup improvement is not established by the confidence interval. The long-cell change also lacks a reliable end-to-end timing gain in the repeated trials. Results apply to the stated fixtures, software versions and machine; they are not an overall application speedup, a physical FPS measurement or a production-cloud benchmark.

1. Study design

The investigation separates time to first useful display, time to complete a result, consumed CPU, event-loop responsiveness, terminal output, database payload and process memory. These quantities answer different questions. An optimization that removes waiting can increase CPU or redraw traffic, while a process that consumes little CPU can still block input. Each experimental conclusion names its measurement boundary.

Experimental coverage

Controls and statistics

Workloads ran sequentially. Baseline/candidate order was randomized within trial blocks with a fixed seed, and the rendering-strategy order was randomized. Database scenarios were shuffled within each repetition. Source commits were checked before jobs. Docker servers were limited to one CPU and 512 MiB each, exposed only on random loopback ports, then stopped and removed. Only synthetic database contents and public lab credentials were used.

The principal statistic is the median. Error bars show a nonparametric 95% bootstrap interval for that median, using 2,000 resamples. Reported improvement intervals use 5,000 resamples; matched trial blocks are resampled together where available. The completion-engine source runs use independent resampling. Reduction is 100 × (median before − median after) / median before. Negative reduction means increased cost. Means, standard deviations, quartiles, minimum, maximum and descriptive p95 values are available in the interactive appendix and JSON.

These are exploratory intervals, without correction for examining many alternatives. A p95 computed from five or nine runs is a description of this small sample, not a service-level guarantee. No host cache was dropped, CPU governor changed, or visible desktop manipulated. A new Python process is “cold” only at the process level; the filesystem may remain warm. The empty-bytecode condition redirects Python’s bytecode lookup and does not emulate a cold disk.

2. Diagnostics and calibration

The existing startup profiler and import tracer identified the startup phases. The existing debug-event bus and UI-stall watchdog were exercised in dedicated diagnostic runs. A known 120 ms blocking sleep produced a roughly 118 ms excess-delay observation and a watchdog warning. This control matters because the blocking sleep consumes little CPU: CPU utilization alone would miss the freeze.

The new lab adds a five-millisecond asyncio heartbeat, per-workload consumed CPU, terminal-byte accounting, display-call timestamps, Chrome trace exports, repeatable result assertions and statistics. cProfile localized rendering work in the compositor, table and Rich formatting paths. An independent py-spy recording of the baseline completion workload collected 471 samples with no sampling errors and retained a Speedscope file. Diagnostic timings are excluded from the formal comparative summaries.

A heartbeat value is lateness beyond its requested five-millisecond sleep. It is not a measured frame presentation time. Likewise, an application display call can write a partial update and need not correspond to one monitor frame. The report therefore uses event-loop lateness and terminal output as responsiveness evidence, without relabeling them as physical FPS. The formal completion runs disable the native watchdog to keep diagnostics overhead separate; watchdog evidence comes from the dedicated control runs.

The existing rendering tests permit several seconds and the standard headless app path does not start the idle scheduler. Those tests remain useful correctness checks, but cannot by themselves establish smooth interactive performance. The PTY experiments run the ordinary scheduler and renderer. A CLI preparser issue also surfaced: an absolute value following a diagnostic path flag could be mistaken for a project directory. The branch repairs that parsing and adds path-argument regressions.

Clock and CPU definitions follow Python’s time API. Threaded work must also respect the Textual worker/UI boundary; moving a function to a thread is not evidence that its complete pipeline has become non-blocking.

3. Autocomplete: a confirmed quadratic bottleneck

Implemented and tested

The baseline computes a display name for each routine by scanning every routine again to discover same-name entries in other schemas or databases. With N routines, this creates approximately N² candidate comparisons before returning at most 50 suggestions. The work also occurs for unrelated SQL and, in the original ordering, even before the blank-input early return.

The change builds one mapping from lowercased routine name to its database/schema identities, then uses that mapping for disambiguation. Original spelling, qualified names and output ordering are preserved. Blank or string-literal input returns before constructing the catalog index. The index is local to the completion request, avoiding a persistent cross-connection invalidation problem.

For 5,000 routines, the standalone completion function falls from 2,337.5 ms to 5.6 ms. In the real application completion-and-dropdown path, the corresponding median is 1,841.2 ms to 34.6 ms, with a 95% reduction interval of approximately 97.0–98.2%. At 1,000 routines the UI path falls from 98.4 to 21.8 ms.

The large difference between the optimized function and the complete dropdown path is residual UI work, not a contradiction. The UI also formats names, computes context, mounts suggestion rows and refreshes. The existing 100 ms autocomplete debounce is outside the measured boundary. A 35 ms callback-and-refresh result does not establish a universal 16.7 ms frame budget; the largest low-overhead candidate heartbeat observation in this completion series still exceeds 50 ms.

Validation includes a deterministic operation-count regression that fails on repeated catalog rescanning, namespace and case checks, and the existing completion suite. The initial focused lane passed 347 tests. The formal engine samples retain hashes of their suggestion lists so that speed and observed output can be compared together. Further dropdown reuse or batched mounting is a separate opportunity, with no speedup claimed here.

4. Startup: remove unnecessary work before promising faster launch

Worker preference fix implemented

The CLI creates its process worker before constructing the Textual app, which preserves an existing platform safeguard around process spawning. Previously, the saved process_worker=false setting was applied only during mount, after that worker had already been created. The fix reads this preference before prewarming. Enabled configurations retain the early spawn path.

With the saved worker disabled, launch-and-exit CPU falls from 541.8 to 454.4 ms, a 16.1% reduction with a 95% interval of about 14.9–19.6%. First-refresh medians move from 410.7 to 393.0 ms, but the interval crosses zero. Default-policy startup likewise has no clearly established improvement. The supported claim is less unnecessary CPU when the worker is disabled.

Import diagnostics place a large share of startup before the first app construction, including Textual, Rich, the results widget and Arrow. Import timings are inclusive and must not be added as though every module were independent. The separate empty-bytecode condition reaches first refresh at approximately 1,194 ms, versus 393 ms for the compiled-cache disabled-worker condition. This supports checking bytecode generation in source-only or unusual packaging paths; it does not show that a normal installer is missing that optimization.

The next architectural startup experiment should defer the heavy results backend behind a lightweight empty-results state. It requires preserving focus, table selectors, commands and the first-query experience. The observed import budget is an opportunity ceiling, not a measured saving from an implementation. This study does not recommend deferring security checks or disabling the enabled worker’s platform safeguards merely to improve a launch number.

5. Idle CPU: schedule work when it exists

Implemented and tested

The baseline idle scheduler arranges another check every 150 ms even with an empty queue. The change leaves no check scheduled when there is no work, arms a timer when a job arrives, disarms it after cancellation or pause, and resumes pending work correctly. Elapsed-time decisions use a monotonic clock. Reentrant job requests and stop/start behavior are covered by lifecycle tests.

With explorer focus, the five-second median CPU total falls from 21.1 to 5.9 ms. That is approximately 0.42% to 0.12% of one CPU core, or about 15 ms less consumed CPU per five seconds. The relative reduction is 72.0%, but the absolute magnitude is important. Editor-focus CPU falls from 39.9 to 23.7 ms while retaining cursor blinking.

This is a reduction in periodic application work, not a measured battery-life gain. Other wakeups, the terminal emulator and the rest of the desktop remain outside the process measurement. A cursor-blink-disabled pilot showed an additional preference-dependent opportunity, but was not adopted or promoted to a replicated claim. Keeping the existing visual behavior while eliminating empty polling is the better-supported change.

6. Rendering: throughput, stutter, memory and terminal traffic

The stock result path renders a small preview, then appends rows through idle work. The main comparison loads 50,000 six-column rows, while other fixtures exercise small results, 40-column tables, long text, Decimal values, scrolling and filtering. Full result availability, initial result refresh and event-loop lateness are retained separately. Original row counts and selected values are asserted.

Bulk rendering

Rejected as a general default

Building the whole table immediately reduces median full availability from 1,304.1 to 154.4 ms and reduces CPU by 54.1%. However, the median of each trial’s maximum heartbeat lateness rises from 22.1 to 94.7 ms. Finishing the batch sooner comes with a substantially longer single interruption.

Preview plus threaded preparation

Did not meet the responsiveness objective

The hybrid keeps an immediate preview, prepares a complete Arrow backend in a thread and replaces the table once. Full availability improves to 228.1 ms, but maximum lateness remains about 94.8 ms. The experiment demonstrates that moving preparation to a thread does not, by itself, remove the complete pipeline’s blocking behavior. Native conversion, GIL behavior and final mounting require separate attribution before selecting a more complex worker design. Python documents the relevant limitation of asyncio.to_thread.

More frequent timed batches

A measurable trade-off

Appending 500 rows per short timer reduces full availability to 524.7 ms while keeping the median maximum lateness near 21.2 ms. It also generates substantially more terminal output: 236,866.0 bytes versus 146,513.0. Its CPU point estimate increases. A foreground render scheduler therefore needs both a latency budget and redraw coalescing; shortening timers alone is not a lightweight solution.

Rows retained in Python, display work on demand

Strong prototype; incomplete production contract

The read-only prototype retains the existing Python rows and supplies visible values on demand, with widths sampled from 128 leading rows and the last row. It avoids converting the complete dataset into a second representation solely for display. Full availability falls to 67.1 ms and CPU to 62.2 ms: 94.9% and 79.7% reductions respectively in this fixture.

The prototype changes the column-width policy and does not implement all mutation/export contracts. Filtering and restoration still exercise stock application paths. It runs inside the existing Arrow-dependent app, so it does not demonstrate removal of that dependency or its import cost. The next development step is an explicit backend contract covering sort, copy, export, duplicate labels, mixed types, UUIDs, binary values, dates, decimals and cancellation. The measured gain justifies that work; it does not justify silently replacing the current backend.

Terminal bytes are the UTF-8 payload submitted to the terminal driver, excluding SSH framing, encryption, compression and retransmission. Scrolling still produces substantial output across backend strategies, so faster data preparation alone does not solve terminal transport cost. RSS is the process high-water value at a point in the same ordered workload sequence; it includes imports and earlier allocations and should not be read as the memory allocated by one operation.

Long-cell display bounds

Implemented; end-to-end speedup not established

The table’s formatter now honors the available width for oversized, single-line literal strings, using Rich’s cell-aware truncation. The backend value remains complete. Tests cover long ASCII, CJK, combining marks, emoji, markup-looking text and the existing styled-markup path. This gives the display representation a defined bound without truncating data used by the value viewer or other operations.

The repeated fresh-process workload does not establish a reliable whole-operation speedup: CPU medians are 90.6 and 86.3 ms, with a reduction interval spanning roughly −20% to +30%. Full-availability timing is similarly uncertain. The earlier pilot looked stronger, illustrating why the paper uses the repeated study for conclusions. This change should receive normal visual review, with no broad performance credit assigned from these timings.

A separate headless visual fixture shows literal markup and Unicode values at their display bounds, with all 48 original values checked in the backend. Its rows are injected for display verification; its displayed query time is not a database measurement.

7. Database transport: bound work where it happens

Each server contains a 50,000-row fixture with an integer key and 256-character payload. Tests use the real sqlit adapter, then compare a client-side 1,000-row cap, an explicit LIMIT 1001 plus that cap, and a provider-specific streaming cursor. A separate relay adds either zero or 20 ms delivery delay in each direction, while counting transmitted protocol payload. It pipelines data instead of sleeping once per SQL call or serially throttling every chunk.

A 1,000-row cap still receives approximately 13.79 MB in PostgreSQL and 13.44 MB in MySQL and MariaDB for the unbounded SQL fixture. The explicitly limited query receives approximately 274 kB and 267 kB respectively, a roughly 98% reduction. All bounded variants verify the same first 1,000 rows and a true truncation indication. With 40 ms injected round-trip latency, the bounded query also removes substantial CPU and elapsed work.

This result concerns arbitrary unbounded SQL followed by a Python fetch cap. Generated table-preview queries already use dialect-specific SQL limits. A generic textual rewrite of every submitted statement would be unsafe for syntax and semantics: multi-statement batches, existing LIMIT clauses, locking reads, side-effecting functions and DML-returned rows need their own handling. The immediate recommendation is a clear bounded-preview execution contract, not an unconditional rewrite of user SQL.

Streaming is provider-specific

PostgreSQL’s regular cursor ordinarily transfers the complete result, while a named server-side cursor supports controlled fetching. The named-cursor experiment uses a transaction, closes the cursor, rolls back and restores the connection before a health query. These lifecycle requirements are part of the approach, as described in the Psycopg documentation.

PyMySQL’s SSCursor returns initial rows without buffering everything, but its close operation exhausts the unread result. The lab measures this cleanup and confirms that the connection can execute SELECT 1 afterward. The full payload is still transferred for MySQL/MariaDB. This matches the documented cursor behavior. Streaming timings in the figure include that extra health query, so their boundary includes more work than the ordinary limited-query column.

Bandwidth figures count relay payload, including protocol messages; they exclude TCP/IP headers and packet retransmissions. The byte intervals collapse for this deterministic fixture because repeated executions produce the same payload size. That does not imply identical savings for arbitrary schemas or real cloud services.

8. Metadata and connection round trips

The existing PostgreSQL and MySQL-family column-inspection paths query primary-key information and column information separately. Scanning 20 fixture tables therefore requires 40 metadata queries. The lab compares that path with one query per table and a single query returning all 80 columns. Ordering, type names and primary-key flags must match the existing adapter results.

At 40 ms injected round-trip latency, the 20-table scan takes about 1.66–1.72 seconds on the baseline paths. One query per table removes approximately half the latency; a single batch reduces the scan to about 44–52 ms, approximately 97% faster. Loopback improvements are smaller and more dependent on query planning. This is strong evidence for reducing round trips where multiple metadata requests are genuinely required.

The default application loads much metadata lazily, so the experiment is not evidence that every connection currently performs this 20-table scan. A provider-specific bulk metadata capability should be invoked by actual demand and preserve schema/database identity, permissions, ordering and cache invalidation. It should not eagerly fetch an entire organization’s catalog simply because one batch can do so efficiently.

For a short query at the injected latency, retaining a connection reduces median operation time from approximately 170 to 41 ms in PostgreSQL, 186 to 42 ms in MySQL, and 227 to 41 ms in MariaDB. The current cancellable-query implementation intentionally creates a dedicated connection, allowing cancellation to close it. This makes connection establishment a real opportunity and a real lifecycle constraint.

A useful next experiment is an exclusive-lease connection pool that discards cancelled or uncertain connections and explicitly resets reusable session state. Reset work may add round trips and reduce the available gain. Transactions, temporary objects, session settings, credential changes and cross-database routing must be validated before integration. The numbers above measure retained-connection opportunity; they are not measurements of a completed safe pool.

9. Local databases, process isolation and IPC

SQLite and DuckDB experiments compare reused connections, fresh connections, the application’s cancellable-query object and the actual process-worker client. They also compare 1,000-row and 50,000-row transfers. Fixtures live in temporary files. DuckDB uses read-only connections so that the forced cross-process experiment does not depend on unsupported concurrent writers.

Process creation and communication can dominate a trivial local query. A warm worker avoids repeated interpreter initialization, but the current worker still creates a dedicated query connection. Parent CPU measurements exclude the worker’s CPU; the worker’s reported operation time is retained separately. The cold-worker condition includes startup, execution and shutdown. In normal UI use, DuckDB advertises that the process-worker route is unsupported, so its forced comparison is an architectural experiment rather than a measurement of that adapter’s default behavior.

These results favor reusing an already-needed worker and avoiding an unwanted worker, while retaining cancellation and crash isolation where required. They do not support globally removing isolation. For long queries, the startup cost may be small relative to execution; for a rapid sequence of local previews, it can be the dominant term.

The serialization experiment intentionally distinguishes two contracts. A Python-row round trip includes serialization and reconstruction of Python values. A prepared-Arrow round trip starts and ends with a columnar table. Arrow’s buffer-sharing and IPC properties can be valuable in a pipeline that stays columnar, but they do not eliminate the conversions required by an otherwise row-oriented pipeline. The full Python-to-Arrow-to-Python variant is measured separately. The Arrow IPC documentation describes the underlying format and zero-copy opportunities.

Compression is data-dependent. Both repeated text and varied deterministic hexadecimal text are exercised. Repeated strings are allocated independently to match driver-returned values, preventing pickle from receiving an artificial advantage through object-identity memoization. LZ4 and Zstd results are available with byte sizes and round-trip times. A blanket serializer or compressor replacement is not supported; a sustained columnar path needs its own end-to-end experiment.

10. Recommended actions

The highest-confidence changes are small and localized. The larger opportunities are supported by actual prototypes or real-server comparisons, but retain explicit implementation gates. Savings from unrelated rows in this table must not be added into an “overall percentage.”

Approaches to avoid adopting from these results

Do not replace incremental rendering with a synchronous bulk build solely because completion time improves. Do not assume a thread removes native or mounting stalls. Do not switch all MySQL reads to SSCursor expecting a bandwidth cap. Do not remove cancellation isolation to reproduce a microbenchmark. Do not choose an IPC format using a comparison that excludes its required conversions. A width-cache intervention and disabling cursor blink were explored in pilots, but did not supply the same quality of evidence as the implemented fixes.

11. Validation and limits

All 152 formal jobs completed. The study asserts fixture result counts, selected cell values, truncation, metadata equivalence, successful post-stream connection use and serialization equality. Each owned Docker server was stopped and removed; cleanup logs retain exit and OOM state. Dedicated diagnostics provide the startup/import logs, native watchdog output, structured events, cProfile output and an independent sampling profile.

The primary before/after estimates are below. Values are milliseconds, including CPU milliseconds where named. An interval crossing zero means that this sample does not establish the direction of the change. The saved-worker condition and the default-worker condition are intentionally separate.

External validity

This is one Linux laptop and one locked software environment. CPU frequency, thermal state and background activity were not pinned; load averages and environment metadata are retained. No physical terminal-emulator GPU timing, monitor presentation trace, battery energy or network packet loss was measured. Therefore, “60 FPS,” battery-life percentages and universal startup claims would go beyond the evidence.

The network-delay relay models fixed one-way latency, not a complete mobile or WAN network. It adds no loss, jitter, constrained bandwidth or TLS handshake, and its local database servers use small synthetic schemas. Paid cloud cold starts, OAuth/token refresh, SSH tunnels, enterprise catalog permissions, Oracle, SQL Server, Databricks, Exasol, Snowflake and other providers are outside the measured server matrix. No existing private connection was used to fill those gaps.

The principal UI dataset order is fixed, so later RSS observations include earlier allocations. Synthetic values cannot represent every driver object or LOB lifecycle. Repeated UI completion invokes the real application method and dropdown with supplied SQL text; it does not include physical keystroke transport or the existing debounce delay. Continuous typing during large-result ingestion, aggressive resize and all production cancellation races remain follow-up workloads.

Only the measured source changes are implemented in the branch. Experimental variants remain laboratory code. The study does not establish an installed release, a merge to main or a deployment. The baseline and measured candidate commits remain the reproducible references even if main changes later.

12. Explore the measurements

This table is generated from the recorded samples. Select an experiment family and metric, filter by provider, variant or scenario, and download the current view. Confidence intervals describe the median. The full JSON also retains means, standard deviations and quartiles.

13. Reproduction and diagnostic tools

The lab scripts are stored in labs/performance/. Create separate worktrees at the referenced baseline and candidate commits and install the frozen dependencies. The controller needs only the standard library; plotting uses a separate Matplotlib environment so that plotting dependencies cannot change the measured app’s import graph.

uv sync --frozen --group dev --extra postgres --extra mysql --extra duckdb

python labs/performance/study.py \
  --baseline /absolute/baseline \
  --candidate /absolute/candidate \
  --python /absolute/candidate/.venv/bin/python \
  --output /absolute/evidence \
  --docker --dry-run

# Remove --dry-run to execute. --quick is a smoke run only.
# Without Docker: --phases startup,cpu,completion,render,idle

python labs/performance/summarize.py \
  --input /absolute/evidence --output docs/performance

# In a separate environment with Matplotlib:
python labs/performance/build_report.py --data docs/performance/data.json

The network helper only accepts disposable local containers. Individual probes can enable --diagnostics --profile, and output both Chrome trace JSON and the native debug/watchdog logs. py-spy record --format speedscope supplies a complementary sampling profile. Download the selected diagnostic recordings and regression logs. Tests should use isolated sqlit configuration; the old personal-connection watchdog integration test is not a substitute for a disposable fixture.

Offline trace viewer

Open a generated ui.trace.json file to inspect workload spans and heartbeat lateness locally. No file is uploaded. This viewer is a companion diagnostic tool; it does not convert application display calls into physical FPS.

The lab’s trace uses logical lanes: workload spans and heartbeat observations.

References and evidence

1. sqlit source. Baseline 9db49c230c7c and measured candidate 0640c96464dc. Key source areas: completion engine; CLI prewarm; idle scheduler; result formatting; cursor adapters; cancellable queries and process worker.
2. Study data. 1349 normalized observations, descriptive statistics and effect intervals, lossless raw chunks and hashes, regression validation, and source/data provenance. The measurement-file hashes match laboratory snapshot 03e68b73999e.
3. Python Software Foundation. Time access and conversions, Python 3.13 documentation. Clock definitions and process CPU semantics. Accessed 10 September 2026.
4. Python Software Foundation. Coroutines and tasks: asyncio.to_thread. Threading and GIL limitations. Accessed 10 September 2026.
5. Textualize. Textual workers. Background work and UI interaction contracts. Installed Textual version measured: 8.2.8.
6. Psycopg project. Server-side cursors and transaction lifecycle. Installed psycopg2-binary version: 2.9.11. Accessed 10 September 2026.
7. PyMySQL project. Cursor objects: SSCursor and close. Installed PyMySQL version: 1.1.2. Accessed 10 September 2026.
8. Apache Arrow. Streaming, serialization and IPC and concat_tables. The installed implementation measured here is PyArrow 21.0.0; documentation describes the representation and buffer-sharing contracts.

This HTML embeds its figures and statistical explorer and requires no external JavaScript, font or image service. The SVG figures, CSV, statistics JSON and raw chunks remain separate downloadable artifacts. Measured outcomes, experimental interventions and proposed follow-up work are intentionally identified throughout.