H2数据库（v1.4.195）空表重复执行SELECT *的性能影响及优化问询

Let’s break down your questions one by one, based on how H2’s B-tree file storage works in v1.4.195:

1. Does H2 read file blocks to check if an empty table is empty? #

Yes, but it’s an extremely lightweight operation. When your table is empty, H2 still maintains small metadata blocks (for schema info) and the root node of the table’s B-tree index. Running select * from eventTable limit 10 offset 0 will read these tiny blocks initially.

That said, after the first query, H2 caches these metadata blocks in memory—so subsequent 2-second polls will almost always hit the cache instead of doing physical IO. For an empty table, the overhead here is negligible; the query will return an empty result instantly after the first cache warm-up.

2. Does H2 have an equivalent to Oracle’s High Water Mark (HWM)? #

No, H2 doesn’t use a High Water Mark system like Oracle. Oracle’s HWM tracks the highest data block ever used by a table, forcing full scans to read up to that mark even if data was deleted.

H2’s B-tree storage is dynamic: when rows are removed, unused B-tree nodes are marked as free and reused for future inserts. Queries only traverse existing, populated nodes—there’s no leftover "ghost" block range that gets scanned unnecessarily. For an empty table, this means your poll queries won’t waste cycles on unused storage.

3. Should you replace polling with INSERT triggers if performance is a concern? #

It depends on your use case, but triggers are often a more efficient alternative to frequent polling—here’s a breakdown:

Pros of using triggers: #

• No idle overhead: Triggers only run when an actual INSERT happens, eliminating the 2-second repeated queries when the table is empty.
• Real-time alerts: You get notified the moment an event is inserted, instead of waiting up to 2 seconds for the next poll cycle.

Cons to weigh: #

• Write-time latency: Triggers add a small amount of overhead to each INSERT operation. If your trigger logic is complex (like calling external services), it could slow down data writes. Keep trigger logic as lightweight as possible.
• Configuration work: You’ll need to create and maintain an AFTER INSERT trigger in H2. For example:

  CREATE TRIGGER event_monitor_trigger AFTER INSERT ON eventTable
  FOR EACH ROW CALL "com.yourteam.YourTriggerImplementation";
  

  Your implementation will need to follow H2’s Trigger interface, which adds a bit of code overhead.

Recommendation: #

If your current polling setup is causing measurable performance issues (unlikely for an empty table, but possible as the table grows or polling frequency increases), triggers are a strong replacement. If polling is working fine and you prefer the simplicity of not modifying write logic, you can stick with it—empty-table polls have almost no practical cost.

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