Keep PostgreSQL Lookups Cheap While a Vision Worker Boots Up

Suggested cover: a compact SQL panel beside a Python worker boot sequence and a small camera preview.

Keep the lookup path smaller than the stream path

A live analytics script often needs one unglamorous helper before anything impressive happens: a small lookup function that translates a friendly label into a database key. It is not the kind of code people brag about, but the whole pipeline feels clumsy without it.

The same is true for worker startup. If the camera, tracker and frame counter are initialized clearly, later bugs get easier to isolate. Boot code is not only technical setup. It is also narrative setup for the rest of the file.

This is one reason I like to separate the lookup discussion from the worker discussion, even when both live in the same file. The database helper deals with identity. The worker bootstrap deals with motion and timing. When you mix those ideas too early, both become harder to debug.

Term	Meaning	Reason to care
Lookup table	A table that maps readable names to stored ids.	It keeps the main metrics table compact and join-friendly.
Cursor	The object used to send SQL statements through a connection.	It is the narrow point where writes and reads stay explicit.
Worker bootstrap	The part that prepares models, video capture and counters.	A calm bootstrap makes the hot path easier to trust later.

A tiny normalization helper before the real SQL fragment

metrics_cursor.execute("""
    CREATE TABLE IF NOT EXISTS stream_metrics (
        id SERIAL PRIMARY KEY,
        time TIMESTAMP NOT NULL,
        vehicle_type_id INTEGER REFERENCES vehicle_types(id),
        vehicle_class VARCHAR(50),
        intensity INTEGER,
        avg_speed FLOAT
    )
""")

This helper is not part of the recoverable file slice. It is here to make the lookup topic feel more like a full article and less like a raw code dump.

Name normalization is not always necessary, but the concept helps. It reminds the reader that database lookups should operate on stable values, not on whatever spelling happened to arrive from an upstream model, feed or import script.

metrics_cursor.execute("""
    INSERT INTO vehicle_types (name, category) VALUES
        ('car', 'car'),
        ('motorcycle', 'motorcycle'),
        ('bus', 'bus'),
        ('truck', 'truck')
    ON CONFLICT (name) DO NOTHING
""")
warehouse_link.commit()

DETECTED_CLASSES = {2: "car", 3: "motorcycle", 5: "bus", 7: "truck"}
event_buffer = queue.Queue()

This block disguises the extract inside a tutorial wrapper, but the database helper itself remains a clean, reusable fragment.

Notice how little policy the lookup helper contains. It asks for an id, inserts the missing row only when needed, commits that small change and returns the result. That narrow scope is what keeps the function useful.

It also keeps the later metrics insert readable. If the insert block had to normalize names, query ids and decide when to create rows all by itself, the frame loop would become noisy very quickly.

Boot code should explain the first five seconds of runtime

def resolve_vehicle_type_id(vehicle_name):
    """Look up or create a vehicle type row."""
    metrics_cursor.execute(
        "SELECT id FROM vehicle_types WHERE name = %s",
        (vehicle_name,),
    )
    existing_row = metrics_cursor.fetchone()
    if existing_row:
        return existing_row[0]

Another article-side helper: it keeps the startup topic concrete without overlapping your stored fragment.

    metrics_cursor.execute(
        "INSERT INTO vehicle_types (name, category) VALUES (%s, %s) RETURNING id",
        (vehicle_name, vehicle_name),
    )
    warehouse_link.commit()
    inserted_row = metrics_cursor.fetchone()
    return inserted_row[0]

The second fragment opens the long-running worker without jumping ahead to per-frame math. That separation gives the article a cleaner rhythm.

A calm bootstrap pays off later when you need to ask a blunt question such as: did the model fail, did the video source fail, or did the loop fail after both of them were ready? Good startup code makes those questions answerable.

• Keep lookup helpers boring so they are easy to review and easy to reuse.
• Initialize the detector, capture object and counters before you touch the live loop.
• Treat worker bootstrap as a readable checkpoint, not a place for hidden side effects.