Relational or Distributed? A Scaling Study of Frequent Itemset Mining over Urban License Plate Detections for Smart City Platforms

1. Introduction

Smart city programmes increasingly instrument the road network with cameras whose primary purpose is enforcement, but whose by-product is a dense, structured record of vehicle movement [1,2]. Once each frame has been reduced to a tuple containing a timestamp, a camera identifier and a license plate, the camera network stops being a video problem and becomes a data problem, with the volume, velocity and variety that characterise big data workloads [3,4,5]. Automatic number plate recognition (ANPR) data of this kind already support travel-time estimation, queue estimation, origin–destination inference and trajectory reconstruction [6,7,8,9]. In a mid-sized Colombian city such as Pereira, a deployment of a few hundred cameras would produce tens of millions of detections per week, a volume at which the choice of storage and query engine stops being an implementation detail and starts determining which questions can be asked at all.

Among the questions a traffic authority can pose over such a stream, the most valuable are not the ones that count events but the ones that reveal structure. Knowing how many vehicles passed a given intersection last Tuesday is a report. Knowing which groups of cameras are frequently traversed by the same vehicles describes the corridors that traffic actually uses, which supports signal coordination and enforcement planning. The same reasoning applied the other way round, to the groups of vehicles that repeatedly pass the same cameras together, is the basis of automated convoy detection, a direct tool for traffic enforcement [10]. Formally, both questions are instances of frequent itemset mining [11,12]. In the first, each plate is a transaction and each camera an item, and a frequent itemset is a set of cameras that recurs with at least a specified support; in the second, the roles are exchanged, with camera and time windows as transactions and plates as items. This paper measures the first formulation. The second shares its cost structure, and because a busy camera window contains many more plates than a single plate has detections, its relational cost per transaction is expected to be even higher.

The engineering difficulty is that this workload sits exactly at the boundary between the two families of engine that a smart city platform typically has available [13,14]. On one side, a relational database management system materialises the detections in indexed tables, paying once for extraction, loading and index construction, and thereafter touching only the rows relevant to each query. On the other, a distributed processing engine reads columnar files directly and parallelises the computation across workers, investing nothing up front but rescanning the data on every query [15,16,17]. The received wisdom is that the distributed engine wins when data are large, but at what volume, for which query, and under which cluster topology this actually happens is rarely reported with enough detail to support a sizing decision.

This paper answers that question for the frequent itemset workload specifically. The contribution is threefold. First, a reproducible benchmark that measures five decision-making workloads on an indexed PostgreSQL instance and on a four-worker Spark cluster across eight nested data scales spanning almost three orders of magnitude. Second, a characterisation of the fixed coordination overhead that dominates the distributed engine below roughly one million rows, which explains why the naive expectation of distributed superiority fails at moderate volumes. Third, and centrally, a quantitative account of the frequent itemset workload as the only one in the suite whose engine preference is projected to invert with scale, together with an extrapolated crossover volume derived from the fitted growth exponents of the two curves.

One caveat frames every number that follows: the whole benchmark ran on a single machine, where the four Spark workers share the disk and memory bandwidth of one host. Independent I/O paths on real distributed hardware would improve the behaviour of the cluster, so the distributed engine is reported here under a handicap it would not face in a physical deployment. The remainder of the paper is organised as follows. Section 2 reviews related work, Section 3 describes the dataset, the two implementations and the experimental protocol, Section 4 presents the measurements, Section 5 discusses their architectural implications and limitations, and Section 6 concludes.

2. Related Work

2.1. License Plate Recognition Data in Smart Cities

ANPR cameras are deployed mainly for law enforcement and toll collection, but because they are often owned by the authorities that would use their data, they have become an inexpensive source of vehicle-level mobility information [8]. Published uses include travel-time and queue-length estimation [6], origin–destination estimation from reconstructed trajectories [7], trajectory reconstruction from sparse camera networks [9], the regularity of individual vehicle arrivals [18] and the detection of vehicles travelling in convoy [10]. Hadavi et al. analysed two weeks of data from 122 cameras in a Belgian police district and showed that camera-pair frequencies recover the main corridors also seen in GPS data [8]. These studies share a methodological focus: they describe what can be learned from the data, and treat the storage and processing infrastructure as given. Surveys of big data for traffic estimation note that the tooling question remains comparatively under-examined [14], and architectural proposals for smart city transport combine stream and batch layers without quantifying where the boundary between them should lie [13]. The present work addresses that gap for one specific and computationally demanding question.

2.2. Frequent Itemset Mining

The problem was formalised by Agrawal and Srikant in the context of association rule discovery over retail baskets. Their Apriori algorithm exploits the downward closure property of support, generating candidate itemsets of increasing cardinality and pruning any candidate with an infrequent subset [11]. The cost of Apriori is dominated by repeated candidate generation and by one database pass per level, which becomes prohibitive when transactions are long or support thresholds low. Han et al. removed candidate generation altogether with FP-Growth, which compresses the transaction database into a prefix tree of frequent items, the FP-tree, and mines it recursively by projecting conditional pattern bases [12]. FP-Growth requires two passes over the data and scales substantially better; its parallel formulation, PFP [19], is the basis of the implementation in the MLlib package of Apache Spark [20]. A broad survey of the itemset mining family is given in [21], and the relation between itemsets and trajectory patterns is discussed in [22].

A third route exists that is rarely discussed in the mining literature because it belongs to the database tradition: expressing co-occurrence directly in relational algebra. A self join of the transaction table with itself, keyed on the transaction identifier and constrained to avoid duplicate pairs, enumerates every co-occurring pair of items and counts them in a single declarative statement. This route requires no specialised library and benefits from decades of query optimiser engineering, but it materialises a quadratic number of intermediate tuples per transaction and does not generalise gracefully beyond pairs. Understanding when this pragmatic option stops being viable is precisely the practical question faced by an engineering team that already operates a relational database.

2.3. Query Engines and Processing Regimes

The distinction between transactional and analytical processing regimes is long established. Online transaction processing is characterised by many short, selective operations over few rows and benefits from indexed access paths; online analytical processing aggregates over large row counts and prioritises efficient scanning over the latency of any individual operation [17]. Column-oriented storage was introduced specifically to serve the second regime [23], and the columnar file formats used by contemporary distributed engines are its direct descendants.

The distributed lineage begins with MapReduce [15] and its distributed file system [24], and continues with in-memory engines that retain fault tolerance while avoiding disk materialisation between stages [16,25], including a relational layer over the same runtime [26]. Whether parallel databases or MapReduce-style systems are the better substrate for analytical work was debated explicitly by Pavlo et al., whose benchmark found well-tuned parallel databases faster on most analytical tasks at the cost of longer loading times [27,28]; the trade-off between paying for loading once and rescanning on every query is the same one examined here. On the streaming side, structured streaming models an unbounded stream as an incrementally executed relational query [29], and the Kappa architecture argues for a single streaming substrate [30] in place of the separate batch and speed layers of the Lambda architecture [31]. Comparative benchmarking of these engines has focused predominantly on streaming latency and throughput [32], leaving the batch analytical regime, and mining workloads in particular, comparatively under-measured against a well-tuned relational baseline.

2.4. From Video to Detections

The event stream analysed here is the output of a computer vision pipeline whose components are mature (Figure 1a). Single-stage detectors process video at real-time rates [33], and the YOLOv8 family is a current practical choice for plate localisation [34]. Tracking-by-detection associates detections across frames to preserve identity, with the SORT family and its appearance-aware and low-confidence extensions being the standard options [35,36]. Optical character recognition then converts the plate crop into a string [37], and multi-camera vehicle datasets such as CityFlow [38] establish benchmark conditions for city-scale deployments. This work takes the existence of such a pipeline as given and concerns itself exclusively with what happens downstream, once detections have been persisted.

3. Materials and Methods

3.1. Synthetic Detection Dataset

Real detections at the volume required are not obtainable, both because no such archive was available and because license plate records carry privacy restrictions. The dataset was therefore produced by a microscopic traffic simulation over the real road network of Pereira, Risaralda, Colombia. The simulation is microscopic in the usual sense of the term [39]: each vehicle is an individual agent with its own origin, destination, route and speed, rather than an aggregate flow density. That granularity is what makes the dataset usable here, since each camera emits its own independent detection stream and the co-visitation structure emerges from individual trajectories rather than being imposed by the generator. The road graph is downloaded from OpenStreetMap through the OSMnx library restricted to drivable ways [40], and only its largest strongly connected component is retained so that every origin can reach every destination; each edge carries its speed limit, length and traversal time.

Five hundred cameras are placed on the nodes of highest betweenness centrality [41], that is, the intersections through which the largest number of shortest paths pass, mirroring the criterion an urban planner would apply when siting enforcement cameras (Figure 2). Each vehicle receives a random origin and destination and follows the time-shortest path; it is assigned a type (car with probability $0.78$, motorcycle $0.18$, truck $0.04$), a colour, a uniform speed in $[25,60]$ km/h and a plate in the Colombian format, ABC123 for cars and trucks and ABC12D for motorcycles. A route completion probability distributed as $\mathrm{Beta}(8,2)$ models vehicles that abandon their planned trajectory, reproducing unanticipated turns.

The simulation advances in discrete steps of $dt=1$ s with between $2000$ and $3000$ concurrent vehicles. When a vehicle crosses a camera-equipped node, a detection event is emitted with ten fields: timestamp, camera identifier, plate, internal vehicle identifier, graph node and edge, speed, vehicle type, colour and heading. Output is persisted as Hive-partitioned Parquet by year/month/day/hour, which enables temporal predicate pushdown, using a fixed seed and checkpointing every $250000$ events for reproducibility. Table 1 summarises the window actually loaded for the benchmark; every evaluated scale is a nested temporal sub-window of this range, all starting at the same instant.

Three properties of this dataset matter for the mining workload. First, the mean number of detections per plate over the full window is $31.5$, so transactions are short but not trivially so. Second, because every scale is a window that opens at the same instant, short windows cut trips that are still in progress and therefore contain truncated, shorter transactions; the mean transaction length grows with the window until the window comfortably exceeds the typical trip duration. At the generation rate of Table 1, the smallest scale of 50 thousand rows corresponds to roughly nine minutes of simulated traffic and the 1 million row scale to about three hours. Third, the camera population is fixed at 500 items, so the itemset lattice does not grow with the data volume; only the transaction database does.

The dataset is synthetic, and its mobility is not calibrated against observed counts. Origins and destinations are drawn at random and plates do not recur across trips, so the frequent itemsets it yields reflect the shortest-path structure of the network around high-centrality intersections rather than the habitual behaviour of real drivers. This is adequate for the purpose of the study, which is to measure the computational cost of mining as a function of volume, because that cost depends on the number and length of transactions and not on their semantic content. It does mean that the mined itemsets themselves should not be read as findings about traffic in Pereira.

3.2. Problem Formulation

Let D be the set of detections, each a tuple $(p,c,t)$ of plate, camera and timestamp. The transaction database is obtained by grouping detections by plate,

$$\mathcal{T}=\left\{T_p=\{c:(p,c,t)\in D\}|p\in P\right\},$$

(1)

where P is the set of distinct plates. Cameras are the items and each plate contributes one transaction consisting of the set of cameras it was seen at, with multiplicity discarded (Figure 1b). The support of an itemset $X\subseteq C$ is its relative frequency among transactions,

$$\mathrm{supp}\left(X\right)={\displaystyle \frac{\left|\{T_p\in \mathcal{T}:X\subseteq T_p\}\right|}{\left|\mathcal{T}\right|}},$$

(2)

and the mining task is to return every X with $\mathrm{supp}\left(X\right)\ge \sigma$ for a threshold $\sigma$, fixed here at $\sigma =0.05$. A frequent itemset of cardinality two or more is, in this domain, a set of cameras traversed together by at least five percent of the observed vehicles.

3.3. The Two Implementations

The relational implementation expresses pairwise co-visitation directly, joining the detection table with itself on the plate and breaking the symmetry with a strict inequality on the camera identifier, as sketched in Algorithm 1. It is declarative, requires no library beyond the database, and exploits the index on $(\mathtt{plate},\mathtt{ts})$ to restrict the scan to the temporal window. Its cost, however, is governed by the number of intermediate tuples it materialises. For a plate observed $n_p$ times the join emits $\Theta \left(n_p^2\right)$ pairs, so the total intermediate volume is

$$\sum _{p\in P}\left({\displaystyle \genfrac{}{}{0pt}{}{n_p}{2}}\right)\approx {\displaystyle \frac{1}{2}}\left|P\right|\overline{n^2}={\displaystyle \frac{N}{2}}·{\displaystyle \frac{\overline{n^2}}{\overline{n}}},$$

(3)

with $N=\left|D\right|$ and $\overline{n},\overline{n^2}$ the first and second moments of the per-plate detection count. This is the precise sense in which the self join is quadratic: it is quadratic in the detections per transaction, not in the global row count. Equation (3) therefore predicts a measured growth exponent above one only while $\overline{n^2}/\overline{n}$ is still growing with the window, which in this dataset happens while windows are short enough to truncate trips. Once the transaction length distribution stabilises, Equation (3) predicts linear growth, and any additional steepening must come from the physical cost of materialising and aggregating the intermediate tuples rather than from their number.

The distributed implementation, sketched in Algorithm 2, builds the transaction database of Equation (1) explicitly with a shuffle-based grouping and hands it to the FP-Growth implementation of MLlib [12,19,20]. It reads the Parquet files directly, with no prior loading or indexing of any kind, and relies on partition pruning for the temporal filter.

The two implementations are deliberately not equivalent in output, and the asymmetry favours the relational side. The self join solves only the pairwise case, enumerating co-visited camera pairs, whereas FP-Growth mines itemsets of arbitrary cardinality and additionally returns the frequent singletons. The relational engine is therefore given the strictly easier problem, and any erosion of its advantage with scale is a conservative estimate of the advantage of the distributed formulation. Where the outputs do overlap, on the support of pairs, their agreement is checked at every scale as described in Section 4.4.

3.4. Workload Suite

Frequent itemset mining is measured alongside four other workloads, listed in Table 2, chosen so that the suite covers the decision-making primitives an operational camera platform must serve: reports, distribution histograms, individual lookups and alerts. Their role in this study is diagnostic. Because they exercise the same two engines over the same data at the same scales but with radically different computational profiles, they separate the fixed cost of each engine from the data-dependent cost, which is what makes the behaviour of the mining workload interpretable. Each workload was implemented twice, in SQL and in PySpark, and the two implementations were verified to produce identical results at every scale.

3.5. Storage Configurations

The comparison contrasts two radically different storage philosophies, shown end to end in Figure 3. In PostgreSQL 16 the data are materialised in a relational table range-partitioned by month on the timestamp, over which three indexes are built, $(\mathtt{plate},\mathtt{ts})$, $(\mathtt{camera}\_\mathtt{id},\mathtt{ts})$ and $\left(\mathtt{ts}\right)$, tailored to the evaluated queries. Loading is performed by an ETL process that copies the Parquet into the table with COPY, dropping the indexes before the bulk load and recreating them at the end. In Apache Spark 3.5.8 there is no prior loading of any kind: the engine reads the hour-partitioned Parquet files directly and has no indexes.

This asymmetry is the interpretive key to the whole experiment. PostgreSQL pays once for the ETL and the indexing and thereafter answers each query touching only the pertinent rows; Spark invests nothing up front but rescans the relevant Parquet on every query. The reported times exclude the one-off ETL and index construction cost entirely, which is again a choice that favours the relational engine.

3.6. Execution Environment

The Spark cluster is brought up with Docker Compose on a single host of 24 threads and 80 GB of memory, on an internal network shared with PostgreSQL. It comprises one master and four workers of four cores and 12 GB each, totalling 16 cores and 48 GB of execution capacity, plus a client node acting as driver; each executor operates with a 10 GB heap and four cores, with spark.cores.max$=16$ and 400 shuffle partitions. No dataset was cached between queries. PostgreSQL 16 ran with shared_buffers$=$4 GB, work_mem$=$256 MB, effective_cache_size$=$12 GB and max_parallel_workers_per_gather$=$4. As an infrastructure constraint worth recording, the default WSL2 configuration caps memory at fifty percent of the host, which is insufficient to sustain the cluster; it had to be raised to 64 GB through the .wslconfig file to avoid termination by memory exhaustion.

Two consequences of this topology must be stated before the results are read. First, as anticipated in the introduction, the four workers share CPU, memory and disk bandwidth, so the measurements are a lower bound on the benefit of the distributed solution relative to a multi-machine cluster. Second, PostgreSQL runs on the same host and thus competes for the same resources, which is symmetric in principle but favours the engine with the smaller working set.

3.7. Measurement Protocol

The protocol is a systematic sweep over a scalability ladder. Each scale is defined as a temporal window $[\mathit{start},\mathit{end})$ which, at the rate of $\approx 8.06$ M rows per day, yields the target volume; the evaluated scales are s50K, s100K, s150K, s200K, s500K, s1M, s10M and s15M, all beginning at the same instant so that each is nested in the next. For PostgreSQL each cell is executed three times and the median reported, preceded by a warm-up run; for Spark a single repetition is executed, a decision justified by the low variance of its overhead floor, below two percent between repetitions in prior tests, and which allows the sweep to complete in about three hours instead of twelve.

Only the execution of the workload is timed, that is, the filter, the computation and the collection of the result, using time.perf_counter; the start-up of the Spark session and the opening of the PostgreSQL connection are left outside the clock. The complete design spans eight scales, five workloads and two engines, producing eighty measurement cells from 160 timed runs. Raw results are persisted as CSV, from which a single script regenerates every figure and table without Spark or Docker. Growth exponents are obtained by ordinary least squares on $\log t$ against $\log N$; over a segment with only two scales the fit reduces to the slope between the two points.

4. Results

Table 3 consolidates the eighty measurement cells and Figure 4 presents the corresponding curves. The first observation is blunt: over the entire evaluated range and in all five workloads, indexed PostgreSQL responds faster than Spark. No crossover is observed below 15 M rows in any workload.

4.1. The Fixed Overhead Floor of the Distributed Engine

Spark exhibits a fixed overhead floor. In W1, W2 and W4 its response time is essentially constant between 50 K and 1 M rows, with medians of 124, 126 and 125 s respectively and spreads of at most 27 s across a twentyfold change in data volume. In W3 and W5, which require an additional shuffle pass, the floor sits higher, at medians of 243 and 248 s. Within this regime the size of the data is irrelevant against the fixed cost of planning the job, launching tasks on the four workers and rescanning the Parquet. The data only begin to matter from the s10M scale onwards. PostgreSQL, by contrast, starts from sub-millisecond times and grows smoothly. Figure 5 contrasts the two behaviours on the lightest and the heaviest workload.

The extreme case is W3, the trajectory of a single plate. PostgreSQL resolves an index seek on $(\mathtt{plate},\mathtt{ts})$ in a median of $0.90$ ms at any scale, while Spark requires a full scan taking a median of 242 s, a difference of roughly $2.7\times {10}^5$ that remains constant across the ladder. Reading up to 10.8 GB to return a handful of rows is pure waste, and this is a consequence of the distributed execution model rather than a configuration defect.

4.2. Frequent Itemset Mining

W5 is the only workload whose engine preference is in motion. Table 4 and Figure 6 give its detail. The relational self join goes from $0.796$ s at s50K to $654.0$ s at s15M, growing by a factor of 821 while the data grow by a factor of 300. Distributed FP-Growth, in contrast, stays pinned to its floor between 231 and 261 s over the first six scales and only then begins to climb, reaching $1074.4$ s at s15M.

The consequence is a monotone erosion of the relational advantage, quantified in Figure 7 as the ratio of Spark time to PostgreSQL time. That ratio falls from $328\times$ at s50K to $134\times$, $78\times$, $60\times$, $21\times$ and $10\times$, and then to $1.9\times$ at s10M and $1.6\times$ at s15M. Over a 300-fold increase in volume the relative advantage of the relational engine contracts by a factor of two hundred.

The ratio erodes in the other workloads too, from $7362\times$ to $107\times$ in W1, from $10344\times$ to $173\times$ in W2 and from $1404\times$ to $3.0\times$ in W4, while staying flat near $2\times {10}^5$ in W3. Those erosions, however, are artifacts of the flat overhead floor rather than evidence about marginal cost: at s15M the distributed engine is still on its floor in W1, W2 and W4, at 121, 108 and 112 s, so its curve has not yet begun to respond to the data at all. W5 is the only workload in the suite where the distributed engine has demonstrably left its floor within the measured range, rising to 684 s and $1074$ s at the last two scales, and where the ratio nevertheless keeps falling. It is therefore the only workload for which both engines’ marginal costs are simultaneously observable and the comparison of growth rates is meaningful.

4.3. Growth Exponents and Extrapolated Crossover

Fitting a power law $t\sim N^b$ on the log–log curves quantifies the two growth regimes (Table 5). For the relational self join the exponent is $b=1.16$ over the full ladder, $b=1.21$ over $[1\mathrm{M},15\mathrm{M}]$ and $b=1.43$ between the last two scales. This is the highest relational exponent in the suite: the same fit over the full ladder gives $1.06$ for W4, $0.70$ for W1, $0.66$ for W2 and $0.02$ for the indexed lookup of W3. For distributed FP-Growth the exponent over $[1\mathrm{M},15\mathrm{M}]$ is only $b=0.50$, but this figure is contaminated by the overhead floor at s1M and understates the marginal cost; between the last two scales, where the floor no longer dominates, the exponent is $b=1.12$, that is, close to linear.

The super-linear and increasing relational exponent has two plausible sources, and the present data do not fully separate them. At the bottom of the ladder, where windows last minutes and truncate most trips, the mean transaction length grows with the window and Equation (3) accounts for super-linear growth directly. At the top of the ladder, windows of 30 to 45 hours are much longer than any trip, the transaction length distribution should be close to stationary, and Equation (3) by itself predicts linear growth. The steepening to $1.43$ between s10M and s15M is therefore more plausibly a physical effect of the join than a combinatorial one: on the order of ${10}^8$ intermediate pairs must be hashed and aggregated, which can exceed the 256 MB of work_mem available to each hash operation and force spilling to disk, on a host whose memory is shared with the Spark workers. Either way the effect is real for the hardware measured, but its origin matters for extrapolation, and Section 5 returns to it.

Extrapolating the two fits from the last measured point, where the ratio is $r_{15\mathrm{M}}=1.64$, gives the crossover volume at which the curves meet:

$$N_{\times }=N_{15\mathrm{M}}·r_{15\mathrm{M}}^{1/(b_{\mathrm{pg}}-b_{\mathrm{sp}})}.$$

(4)

Using the $[10\mathrm{M},15\mathrm{M}]$ exponents, $b_{\mathrm{pg}}-b_{\mathrm{sp}}=0.31$ and $N_{\times }\approx 7.3\times {10}^7$ rows; using the wider $[1\mathrm{M},15\mathrm{M}]$ window, where the difference is $0.71$, $N_{\times }\approx 3.0\times {10}^7$. The crossover therefore lies between roughly thirty and seventy-three million detections, that is, between four and nine days of operation for this five-hundred-camera deployment. The spread between the two estimates reflects genuine sensitivity to the fitting window, and the narrower window rests on two points per engine, so the bracket should be read as an order-of-magnitude estimate rather than a precise threshold.

4.4. Cross-Validation of the Mined Patterns

Because the two implementations mine the same patterns by entirely different means, their agreement is a useful correctness check on the two code paths. The check is deliberately narrow in scope: at each of the eight scales, the support of the top-ranked pair returned by the relational self join was compared against the frequency of the top-ranked two-item itemset returned by FP-Growth. The two agree exactly at all eight scales, with counts of 404, 704, $1007$, $1273$, $2749$, $5490$, $52849$ and $79051$ shared plates respectively. This establishes that the SQL and the PySpark code paths compute the same quantity, so the timing comparison is one of cost rather than of semantics. It does not verify the complete result set: only the leading itemset of each run was retained, and extending the check to the full top-k lists is left for a future sweep.

5. Discussion

Three findings follow from these measurements, and their joint reading is an architectural recommendation rather than an engine preference.

The first is that a distributed engine is structurally unsuited to small and medium query volumes, and this has little to do with tuning. Below roughly one million rows the response time is independent of data size and dominated by job planning, task distribution and Parquet rescanning. In this regime the relational engine wins by factors ranging from tens to hundreds of thousands. Any architecture that routes interactive or operational queries through a distributed batch engine pays a two-minute floor for answers a relational index would return in milliseconds. This is consistent with the earlier finding that well-tuned parallel databases outperform MapReduce-style systems on most analytical tasks once their loading cost has been paid [27].

The second is that the dominance of the relational engine over the rest of the suite stems from its storage model. It invests once in ETL and index construction, and thereafter every query touches only the pertinent rows; the distributed engine rescans on every query. For the operational queries typical of a city the size of Pereira, such as reports by camera, re-identification alerts and plate lookups over windows of hours or a few days, a partitioned and indexed relational schema answers in milliseconds to tens of seconds, orders of magnitude below the distributed alternative.

The third finding is the substantive one. Frequent itemset mining reveals the regime where the balance begins to tip. Its relational cost is governed by the volume of intermediate pairs and by the cost of aggregating them, which is why its exponent is the highest in the suite and keeps rising across the measured range. Distributed FP-Growth, by contrast, grows close to linearly once its floor is paid, and two curves with different exponents must eventually cross. The relational engine reached this point while solving only the pairwise case and without being charged for its ETL and indexing; a fair accounting on either axis would move the crossover earlier.

The practical reading is therefore a hybrid architecture consistent with the Lambda and Kappa patterns [13,29,30,31]. An indexed relational engine should serve as the serving layer for interactive queries, near-real-time alerts and point lookups, where its indexed access is unbeatable. A distributed engine should be reserved for massive batch analytics, co-visitation mining over months of data and model retraining, where its parallelism and direct columnar reads repay the fixed coordination cost. The exponents reported here turn that qualitative recommendation into a first-order sizing rule: for this deployment, co-visitation mining should migrate to the distributed engine somewhere between four and nine days of accumulated history. For a municipal operator the rule is simple to apply, since the relevant quantity is the number of detections in the mining window, which follows directly from the number of cameras and the traffic they observe.

These findings are bounded by several limitations. The cluster runs on a single host and is therefore starved of the I/O bandwidth a physical multi-machine deployment would provide, so every figure reported for it is a lower bound. Spark was run at a single repetition; although the low variance of its overhead floor justifies this, a future sweep should use three repetitions to give it error bars symmetric with those of PostgreSQL. The ladder has no scales between 1 M and 10 M rows, the range was capped at 15 M rows and the crossover was not reached, so the narrower exponent estimate rests on two points and confirming the crossover requires extending the ladder into the 30–75 M range. The comparison couples engine and algorithm, since the self join runs only on PostgreSQL and FP-Growth only on Spark; a Spark SQL self join [26] would separate the two effects. The source of the late steepening of the relational curve was not isolated: query plans and spill statistics at the largest scales would show whether it is combinatorial or a memory effect, and if it is the latter, more memory per operation would delay the crossover. The PostgreSQL indexes were designed to fit the evaluated workloads, and a less favourable schema would bring the curves closer together. Finally, the dataset is synthetic and uncalibrated, with random origins and destinations and no recurring trips, so the transaction length distribution of a real deployment may differ; since the relational cost depends on that distribution through Equation (3), real data with longer or more variable transactions would move the crossover.

6. Conclusions

This work measured frequent itemset mining over urban camera detections on two engines that embody opposite storage philosophies, across eight nested scales from fifty thousand to fifteen million rows, using a synthetic dataset of one hundred million detections generated by microscopic traffic simulation over the real road network of Pereira.

Indexed PostgreSQL was faster than a four-worker Spark cluster in all five workloads over the entire evaluated range, with no crossover below fifteen million rows. The distributed engine exhibits a fixed overhead floor of about two minutes for scan-and-aggregate workloads and four minutes for shuffle-heavy ones, below which data volume is irrelevant to its response time, making it unsuited to interactive and operational queries.

Frequent itemset mining is nevertheless the workload where the balance demonstrably tips. The relational advantage erodes from a factor of 328 to a factor of $1.6$ across the ladder, and the fitted growth exponents place the crossover between thirty and seventy-three million detections. The relational engine reached that point while solving only the pairwise subproblem and without being charged for its ETL and indexing. The support of the top-ranked itemset agrees exactly at all eight scales, which confirms that the two code paths compute the same quantity.

The contribution is therefore a sizing rule rather than a recommendation for either engine: the analytical layer of a smart city camera platform should be hybrid, with an indexed relational engine serving operational queries and a distributed engine reserved for combinatorial mining beyond a volume threshold that this work estimates empirically. Future work should extend the ladder past fifty million rows on physically distributed hardware to confirm the crossover directly, add a Spark SQL self join to separate engine from algorithm, replicate the measurement with real or calibrated detections, and extend the comparison to itemsets of cardinality greater than two on the relational side, where the self-join formulation becomes impractical.

Finally, the exchanged formulation, with camera and time windows as transactions and plates as items, should be benchmarked directly, since automated convoy detection is the enforcement application where the cost of the relational self join is expected to grow fastest.