DenchClaw Performance: Keep Your CRM Fast

DenchClaw is fast by default. DuckDB — the embedded analytical database that powers it — is optimized for the kind of columnar queries your CRM runs: filtering by field values, aggregating across objects, joining entries to their related records. For a typical CRM workload (up to 100,000 entries, dozens of objects), DenchClaw is snappy on any modern laptop.

But as your workspace grows — more entries, more objects, more complex views, more concurrent users — there are patterns that keep it fast and mistakes that slow it down. This guide covers both.

Baseline Performance Expectations#

DuckDB benchmarks on typical CRM queries:

These are rough benchmarks on a modern MacBook. If your queries take significantly longer, there's usually a fixable cause.

Common Performance Bottlenecks#

1. The EAV Schema and PIVOT Views#

DenchClaw uses an EAV (Entity-Attribute-Value) schema internally. Each field value is a separate row in entry_fields. For a contact with 20 fields, that's 20 rows.

The auto-generated PIVOT views (v_people, v_companies, etc.) aggregate these into readable flat tables. The PIVOT itself involves an aggregation — for large objects, this can be the bottleneck.

Fix: Query the PIVOT view rather than joining entry_fields directly. The PIVOT view is designed for your query patterns and is faster for most read operations.

-- Fast: use the PIVOT view
SELECT * FROM v_people WHERE "Status" = 'Lead' LIMIT 100;
 
-- Slower: joining entry_fields directly (unless you have a specific reason)
SELECT e.id FROM entries e
JOIN entry_fields ef ON e.id = ef.entry_id
JOIN fields f ON ef.field_id = f.id
WHERE f.name = 'Status' AND ef.value = 'Lead';

2. Pagination for Large Result Sets#

Never load all entries into the UI at once for large objects. Always paginate.

In your Dench App queries:

// Bad: loads everything
const allPeople = await dench.db.query("SELECT * FROM v_people");
 
// Good: paginated
const page = await dench.db.query(`
  SELECT * FROM v_people 
  ORDER BY entry_id DESC 
  LIMIT 50 OFFSET ${currentPage * 50}
`);

The DenchClaw web UI handles pagination automatically for table and kanban views. For custom Dench Apps, implement pagination yourself.

3. Full-Text Search vs. Field Filters#

Full-text search across all fields is expensive. If you know which field you're searching, filter on it specifically:

-- Slower: LIKE search across all values
SELECT DISTINCT entry_id FROM entry_fields 
WHERE value LIKE '%stripe%';
 
-- Faster: filter on a specific PIVOT view column
SELECT * FROM v_people WHERE "Company" ILIKE '%stripe%';

For search-heavy workflows, consider adding an index to frequently searched fields (see DuckDB indexing below).

4. Unoptimized Report Queries#

Report queries that aggregate across large datasets without proper filtering are the most common source of slowness.

Before running an aggregate:

-- Add a date filter to limit the dataset
SELECT stage, COUNT(*), SUM(value::decimal)
FROM v_deals
WHERE "Close Date"::date >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY stage;

Filtering before aggregating is almost always faster than aggregating everything and filtering after.

DuckDB Query Optimization#

Analyzing Slow Queries#

DuckDB includes EXPLAIN ANALYZE for understanding query performance:

EXPLAIN ANALYZE 
SELECT * FROM v_people 
WHERE "Industry" = 'Fintech' AND "Employee Count"::int > 100;

This shows the query plan and actual execution times. Look for:

• Sequential scans on large tables where you expected an index scan
• Large intermediate result sets in the middle of joins
• String comparisons on numeric values (caused by casting issues)

DuckDB Indexes#

DuckDB creates indexes automatically for some query patterns, but you can also create them explicitly:

-- Connect to your DuckDB file
duckdb ~/.openclaw-dench/workspace/workspace.duckdb
 
-- Create an index on the value column for a specific field
CREATE INDEX idx_status ON entry_fields(value) 
WHERE field_id = (SELECT id FROM fields WHERE name = 'Status');
 
-- Index for the entry_id + field_id combination (common join pattern)
CREATE INDEX idx_entry_field ON entry_fields(entry_id, field_id);

When to add indexes:

• Queries on this field run more than a few times per minute
• The object has more than 10,000 entries
• The query is taking more than 100ms in practice

When not to bother:

• Small objects (under 5,000 entries) — DuckDB's column scanning is fast enough
• Fields rarely used in filters
• The query is already fast enough for your needs

Analyze Table Statistics#

DuckDB uses statistics to optimize query plans. Keep them current:

ANALYZE;
-- Or analyze a specific table:
ANALYZE entries;
ANALYZE entry_fields;

Run ANALYZE after major imports or if query plans seem suboptimal.

Memory and Storage Management#

Compacting the DuckDB File#

DuckDB files can grow from frequent inserts and deletes. Compact periodically:

VACUUM;
CHECKPOINT;

This recovers space from deleted rows and compacts the file. Safe to run anytime while DenchClaw is not under heavy load. For a workspace with moderate activity, run monthly:

"Schedule a monthly database maintenance task: run VACUUM and CHECKPOINT on the workspace DuckDB file on the first Sunday of each month at 3am."

Memory Limits#

By default, DuckDB uses up to 75% of available RAM for query operations. On a laptop with 8GB RAM, that's 6GB. For most CRM workloads, this is more than enough.

If you're running other memory-intensive applications simultaneously (video editing, large IDE), you can limit DuckDB's memory usage:

SET memory_limit='2GB';

Set this in a startup script or configure it in the DenchClaw .env:

DUCKDB_MEMORY_LIMIT=2GB

UI Responsiveness#

Optimize View Configurations#

Views with complex filters or sort conditions on large objects can be slow to render. Tips:

• Limit default rows: Configure views to show 50-100 rows by default, not "all"
• Avoid computed sorts on large fields: Sorting by a number field is faster than sorting by a text field with numeric-looking content
• Use specific filters: A view that filters to "Status = Active" is faster than one with no filter

Widget App Refresh Rates#

Dench App widgets with aggressive refresh rates (every 10 seconds) on complex queries will noticeably impact performance. Set refresh rates appropriate to data freshness needs:

• Live transaction data: 60 seconds minimum
• Pipeline summaries: 5 minutes is usually fine
• Monthly reports: No auto-refresh — update on demand

# In .dench.yaml
widget:
  refreshMs: 300000  # 5 minutes, not 10 seconds

Profile the Agent's Query Time#

If agent responses feel slow, it's usually the AI model API call, not DuckDB. To confirm:

1. Run the query directly in DuckDB CLI — if it's fast, DuckDB isn't the bottleneck
2. Check your AI model's response time — for complex instructions, the model takes 2-5 seconds
3. For recurring queries, cache results in the app's dench.store rather than requerying

Scaling to Larger Datasets#

For DenchClaw workspaces with over 100,000 entries:

1. Partition large objects: Instead of one people object with 200k entries, consider people_enterprise, people_smb, and people_consumer — smaller objects with more targeted views
2. Archive historical data: Move entries older than 2 years to an archive_* object; keep the active object lean
3. Summary tables: For analytics, pre-compute daily/weekly summaries rather than aggregating from raw entry data each time

-- Create a summary table that the agent updates daily
CREATE TABLE pipeline_summary AS
SELECT 
  DATE_TRUNC('week', CURRENT_DATE) as week,
  ef_stage.value as stage,
  COUNT(*) as deal_count,
  SUM(ef_value.value::decimal) as total_value
FROM v_deals
GROUP BY 1, 2;

Frequently Asked Questions#

My queries were fast before but have gotten slower. What should I do?#

First, run ANALYZE; on the database — stale statistics are a common cause of degraded query plans. Second, check if the object size has grown dramatically (query SELECT COUNT(*) FROM entries grouped by object). Third, run VACUUM; to compact the database file. These three steps resolve 90% of unexpected slowdowns.

Does DenchClaw support database sharding for very large datasets?#

No. DuckDB is a single-file database — there's no sharding. For very large datasets (millions of entries), the architectural answer is object partitioning (splitting large objects into smaller ones) and pre-aggregation tables. DuckDB handles 10M+ rows in analytical queries well; the bottleneck is usually the EAV schema, not DuckDB itself.

How does performance compare to HubSpot or Salesforce for large datasets?#

For read-heavy analytical queries, DuckDB running locally often outperforms cloud CRM APIs because there's no network latency. For write-heavy transactional workloads, cloud databases with proper indexing may be faster. DenchClaw's strength is analytical queries on your complete dataset — the kind of queries that take seconds in HubSpot's API take milliseconds in local DuckDB.

Can I run DenchClaw on a Raspberry Pi or low-power server?#

Yes. DuckDB runs on ARM architecture and low-power hardware. A Raspberry Pi 4 (4GB RAM) handles DenchClaw's CRM workload for personal use. For team deployments with concurrent users, a Pi is marginal — a modest VPS ($6-12/month) is more reliable.

Ready to try DenchClaw? Install in one command: npx denchclaw. Full setup guide →