Partition Vision Result Databases for Predictable Queries
Choose time and production-aware partitions, indexes, retention operations, and query tests that keep large inspection histories usable.
A traceability query that once returned in two seconds now scans hundreds of millions of inspection rows, while a nightly deletion causes long locks during production.
This is a vendor-neutral engineering method. The worked example is hypothetical and must be replaced by measurements from the real product, line, and risk assessment.
What you will learn
Identify the physical, optical, data, or process limit behind partitioning high-rate vision result databases.
Convert the inspection need into measurable acceptance criteria.
Compare practical architectures and their trade-offs.
Commission the method using repeatable evidence.
Validate the final system under representative production variation.
Technical foundation
Partitioning narrows physical work
A useful key lets the database prune irrelevant partitions for common predicates and makes bounded retention possible.
Partitioning is not an index replacement
Part ID, batch, station, defect, and event lookups still need deliberate local or global access paths.
Lifecycle operations shape the design
Creating, attaching, detaching, archiving, and dropping partitions must be automated and tested without blocking production.
Related guides on this publication: Machine Vision Traceability: Images, Results, Recipes, and MES and Machine Vision PLC Integration: A Robust Handshake and Industrial Vision Dataset Design: Avoid Leakage and False Confidence.
Engineering workflow
1. Measure workload and volume
Evaluate: rows and bytes per second, retention, late arrivals, query windows, batch patterns, and concurrency.
Why it matters: partition size should follow real access and maintenance.
Measure or calculate: capture query statistics and daily growth over representative production. Preserve settings, sample identity, operating state, and the calculation method so alternatives remain comparable.
Trade-off: measurement takes an observation period. Common failure: choosing monthly partitions by habit.
2. Choose the partition key
Evaluate: event time, production date, batch, site, station, and immutable identity.
Why it matters: the key controls pruning and row movement.
Measure or calculate: test candidate keys against the top query predicates and arrival skew. Preserve settings, sample identity, operating state, and the calculation method so alternatives remain comparable.
Trade-off: multi-level schemes add objects and planning. Common failure: partitioning on a frequently corrected field.
3. Set boundary granularity
Evaluate: hour, day, week, month, subpartition, expected rows, and retention unit.
Why it matters: too few partitions stay large; too many add planning and maintenance overhead.
Measure or calculate: model partitions retained and rows per partition at peak rate. Preserve settings, sample identity, operating state, and the calculation method so alternatives remain comparable.
Trade-off: fine boundaries simplify deletion but increase object count. Common failure: using a boundary unrelated to retention.
4. Design indexes and constraints
Evaluate: part ID, event ID, batch, station, timestamp, defect, BRIN, B-tree, and uniqueness scope.
Why it matters: partition pruning does not accelerate every lookup.
Measure or calculate: run EXPLAIN and uniqueness tests across partitions. Preserve settings, sample identity, operating state, and the calculation method so alternatives remain comparable.
Trade-off: more indexes slow writes and consume storage. Common failure: duplicating every possible index.
5. Automate lifecycle operations
Evaluate: future partition creation, default partition, late rows, ANALYZE, vacuum, detach, archive, legal hold, and drop.
Why it matters: a missing partition can stop ingestion.
Measure or calculate: stage operations and alert before boundaries arrive. Preserve settings, sample identity, operating state, and the calculation method so alternatives remain comparable.
Trade-off: automation needs ownership and rollback. Common failure: creating tomorrow's partition after midnight.
6. Benchmark production behavior
Evaluate: ingest latency, query percentiles, lock waits, plan stability, failover, backup, restore, and retention.
Why it matters: synthetic single-user tests miss contention.
Measure or calculate: replay representative writes and investigations at retained scale. Preserve settings, sample identity, operating state, and the calculation method so alternatives remain comparable.
Trade-off: full-scale testing costs time and storage. Common failure: testing only an empty schema.
Worked example
Hypothetical sizing: A line stores 12 million result rows/day and retains 90 days.
total retained rows = 12,000,000 × 90 = 1.08 billion
Daily partitions hold about 12 million rows and align with daily retention. That is a starting assumption; query mix, late arrivals, index size, and measured planning time decide whether daily boundaries are appropriate.
Practical decision aid
| Design choice | Useful when | Risk to test |
|---|---|---|
| Daily time partition | queries and retention are time-bounded | many objects over long retention |
| Monthly time partition | volume is moderate | large deletion and index units |
| Batch partition | batches are large and queried directly | many small or overlapping batches |
| Time then station | site scale needs isolation | subpartition complexity |
| BRIN on ordered time | large append-correlated tables | lossy scans for weak correlation |
| B-tree on part ID | selective identity lookups | write and storage overhead |
Use the table to choose the next controlled experiment, not as a universal product recommendation. A component or algorithm is acceptable only when the complete inspection cell meets pre-agreed technical and operational criteria.
Common mistakes and how to prevent them
Partitioning without workload data. queries still scan broadly. Prevent it by profiling real predicates.
Assuming pruning always occurs. plans touch every partition. Prevent it by checking EXPLAIN with bound parameters.
Forgetting future partitions. inserts fail at boundary. Prevent it by precreating and alerting.
Ignoring late results. rows hit default or fail. Prevent it by defining a late-arrival route.
Over-indexing partitions. ingest and maintenance slow. Prevent it by keeping evidence-backed indexes.
Dropping without hold checks. required evidence disappears. Prevent it by gating retention operations.
Validate under production conditions
Load data at projected retained scale, not only peak daily rate. Replay writes, part lookups, batch reports, defect trends, late arrivals, corrections, retention holds, partition creation, detach and restore. Measure latency percentiles, rows and partitions scanned, locks, storage, vacuum behavior, replica lag, backup time, and failure recovery.
Use representative acceptable parts, confirmed defects, boundary samples, and nuisance variation. Repeat complete part presentations rather than processing one stored image many times. Include start-up, warm-up, maximum speed, changeover, maintenance, environmental limits, communication faults, and long-duration operation where relevant.
Define acceptance criteria before reviewing final results. Preserve raw counts and denominators for false accepts, false rejects, invalid acquisitions, timing overruns, and manually reviewed cases. After release, trend leading indicators and conduct labelled audits so deterioration is detected before a customer escape.
Key takeaways
Base partitions on measured queries and lifecycle operations.
Keep the partition key stable and present in common predicates.
Add selective indexes for non-partition lookups.
Automate boundaries, late data, and retention safely.
Benchmark with retained-scale data and concurrent workloads.
Follow this Hashnode blog for more practical industrial machine-vision engineering, and connect with Kivanc Ekici on LinkedIn. To explore relevant technology information, visit Algomedi.
Frequently asked questions
Should vision data always be partitioned by time?
Time is common for retention and investigations, but the measured workload should decide.
Does partitioning make every query faster?
No. It helps when pruning removes partitions; other predicates still need indexes or different access paths.
How large should a partition be?
There is no universal size; balance rows, index size, query windows, planning overhead, and maintenance duration.
What happens to late-arriving results?
Route them to a valid historical or controlled default partition and monitor the condition.
Can a partition be dropped for retention immediately?
Only after retention, hold, archive, reference, and recovery checks pass.