EC2 Ubuntu下SQLite3写入时只读查询性能异常问题排查

Let’s break down your issues step by step—you don’t need to jump to PostgreSQL right away, and there are easy fixes to get your queries running fast.

1. The #1 Culprit: Missing Index on timestamp #

Your slow query times (10-12s for 297 rows, 90s for 1000+) are a classic sign of a full table scan. When you run SELECT timestamp, close FROM ... WHERE timestamp > ? without an index on timestamp, SQLite has to read every row in the table to filter results. That’s brutal as data grows.

Fix It: Add an Index #

Run this once per table (adjust for your dynamic %sfut table names):

# In your write script, during initialization (run once per table)
table_name = f"{bnf}fut"
c.execute(f'CREATE INDEX IF NOT EXISTS idx_{table_name}_timestamp ON {table_name}(timestamp)')

This will cut your query time from seconds to milliseconds—guaranteed.

Bonus: Fix Your Query Safety #

You’re using string formatting to build your query, which is risky (SQL injection) and error-prone. Use parameterized queries for the timestamp value (table names can’t be parameterized, so validate the bnf value first):

import re

# Validate table name to prevent injection
if not re.match(r'^[a-zA-Z0-9_]+$', bnf):
    raise ValueError("Invalid table name format")

table_name = f"{bnf}fut"
select_statement = f'SELECT timestamp, close FROM {table_name} WHERE timestamp > ?'
tdf = pd.read_sql(select_statement, conn_tick, params=(lasttimestamp,))

2. Locking Concerns: WAL Mode Already Fixes This #

You enabled PRAGMA journal_mode=wal, which is the right move—SQLite’s WAL mode allows concurrent reads and writes. Writes don’t block reads, and reads don’t block writes. Your isolation_level=None (autocommit) setting is also correct for your use case, since you’re writing tick data in small, independent chunks.

The "lockup" you suspect isn’t happening here—WAL eliminates that bottleneck. The slowdown is definitely the missing index.

3. Should You Switch to PostgreSQL? #

Not yet. SQLite is more than capable of handling tick data workloads as long as you optimize it properly. Only consider PostgreSQL if:

• Your dataset grows to millions of rows and even indexed queries start lagging
• You need distributed read/write scaling, multi-node deployments, or advanced SQL features (like complex window functions or row-level security)
• You have hundreds of concurrent writes per second (SQLite can handle ~1k writes/sec, but beyond that, PostgreSQL scales better)

Fix the index first—you’ll likely see enough performance gain to stick with SQLite.

4. WAL File Size & Checkpointing #

10MB WAL File Impact #

A 10MB WAL file is negligible. SQLite reads from both the main database and WAL to get the latest data, but small WAL files don’t add meaningful overhead. Performance only drops when WAL files grow to tens/hundreds of MB, as SQLite has to scan more unmerged data.

How to Manage Checkpoints #

Checkpoints merge committed WAL data into the main database and truncate the WAL file. Here’s how to control them:

• Auto-checkpoint adjustment: By default, SQLite triggers a checkpoint when the WAL reaches 1/3 the size of the main database. You can set a fixed page count (1 page = 4KB) to keep WAL smaller:

  # Trigger checkpoint after 1000 pages (~4MB) of writes
  c.execute('PRAGMA wal_autocheckpoint=1000')
  

• Manual checkpointing: Add a periodic manual checkpoint in your write script (e.g., every 5 minutes) to force merging:

  # FULL checkpoint waits for all read connections to close before merging
  c.execute('PRAGMA wal_checkpoint(FULL)')
  

• Extra safety: For Linux, enable PRAGMA checkpoint_fullfsync=ON to ensure data is fully written to disk during checkpoints (prevents corruption on sudden shutdowns).

内容的提问来源于stack exchange，提问作者Sid