Python Programming Questions

Goal: compare memory and wall-clock of three join methods on two large disk-resident tables.

A concrete illustrative scale, to make the deliverable tangible (placeholder numbers for the experiment's INPUT sizes, not a claimed result; the actual measured numbers only come from running it): joining a 5,000,000-row table to a 50,000,000-row table on a shared user_id column, on a machine with 8 GB of RAM available to the process. That size difference (10x) is deliberately chosen so a hash join (build an in-memory lookup table keyed by the join column from the SMALLER side, then scan the larger side and look up each row's match against it, rather than one built from the bigger side) has an obvious "small side" to build its lookup table from.

Metrics:

• Peak RAM (process) via psutil
• Total wall-clock and CPU time
• I/O throughput and disk usage
• Return correctness (row counts, spot checksums)

Fairness:

• Same machine, same storage medium, same join keys/indexes
• Fixed dataset snapshots and repeat runs per method
• Use warm/cold cache measurements (warm cache: the operating system already has the file's data sitting in memory from a previous read, so the next read is fast; cold cache: the OS has to actually go to disk, which is slower and closer to a fresh, worst-case run; measuring both matters because a method that looks fast on a warm cache can look very different the first time it touches a cold file)

Experiment plan:

• Prepare synthetic datasets with controlled sizes and cardinalities, using the illustrative 5M/50M scale above (or whatever scale matches the real workload) as a concrete starting point
• Method (1): pandas: read table A in chunks, build index on join key for B or stream smaller table and use dict-based hash join (build an in-memory Python dict keyed by the join column from the smaller table, then scan the larger table row by row and look each row's key up in that dict, an $O(n + m)$ approach instead of an $O(n \cdot m)$ nested comparison)
• Method (2): SQLite: load both tables into temp DB, create indexes, run JOIN
• Method (3): Dask (a library that mirrors the Pandas API but splits data into partitions and runs the same operations, including merge/join, across them in parallel, so a join too large for one machine's memory can still complete by processing partitions and spilling intermediate results to disk when needed): persist datasets on disk, execute dask.dataframe.merge with appropriate partitions

Presentation:

• Plot peak memory vs runtime per method (error bars from repeats)
• Table with throughput, disk I/O, and CPU
• Discuss trade-offs: simplicity, indexing cost, parallelism, disk spill behavior (disk spill: what happens when an operation's intermediate data no longer fits in RAM and the engine starts writing partial results to disk to keep going instead of crashing with an out-of-memory error, trading speed for the ability to finish at all)

Success criteria: lowest peak memory with acceptable runtime; reproducible scripts + configs

My decision

I would lean Go for this tool if the parsing is truly CPU-heavy, meaning the machine spends most of its time computing rather than waiting on disk, and the team wants one deployable binary. Go's compiled binary, static typing, and built-in concurrency fit unattended daily jobs well.

What matters beyond raw speed

• team familiarity and onboarding
• library support for the file formats you need
• packaging and deployment simplicity
• error handling and testability
• memory footprint and startup time
• how often the rules change

When I would pick Python instead
If the job mostly glues together existing Python libraries, or if the engineers need to change parsing rules every week, Python may be faster to evolve and easier to read.

Worked example
If the tool reads 500 large files every morning, I would favor Go when parsing and parallel file handling are the bottlenecks. If the same tool is edited often by a small team that values quick iteration over strict compilation, Python may win because maintenance cost matters more than raw throughput.

So I choose the language that minimizes total operating cost, not just the fastest loop.

How they work together:

• CPython uses reference counting: each object tracks references; when count hits zero it's reclaimed immediately.
• The cyclic GC (generational, meaning it groups objects by how long they've survived so far and scans the youngest group most often, since most garbage dies young and rarely needs a second look) complements this by detecting reference cycles that reference counting alone can't free.

Example needing GC:
Two objects referencing each other (cycle) with del avoided or handled by GC. Example:

class Node:
    def __init__(self):
        self.ref = None

a = Node()
b = Node()
a.ref = b
b.ref = a
# If no external refs remain, ref counts are non-zero due to cycle -> GC must collect

Actually watching the reclamation happen, verified on CPython 3.12, extending the example above with a __del__ and an explicit gc.collect():

import gc

class Node:
    def __init__(self, name):
        self.name = name
        self.ref = None
    def __del__(self):
        print(f'__del__ called for {self.name}')

a = Node('a')
b = Node('b')
a.ref = b
b.ref = a
del a, b            # neither refcount reaches zero; both still reference each other

gc.collect()         # the cyclic GC finds the unreachable pair and reclaims it

Output:

__del__ called for a
__del__ called for b

Both finalizers ran as part of gc.collect() reclaiming the cycle, confirming the cycle really was found and cleaned up, not just theoretically collectible.

Reference counting handles most deallocations quickly; the cyclic GC periodically finds unreachable cycles and reclaims them. Cycles involving objects with __del__ used to require special handling: on Python versions before 3.4, the cyclic GC could not safely decide what order to finalize such objects in, so it left them permanently uncollected in gc.garbage instead of guessing. Since Python 3.4 (PEP 442), __del__ is called safely as part of collecting the cycle, exactly as demonstrated above, and the objects are then reclaimed normally; citing the old "cycles with __del__ can't be collected" claim as current fact is a dated answer today.

Why vectorized ops are faster:

• Vectorized NumPy/Pandas operations run in optimized C/Fortran loops avoiding Python per-element overhead.
• They leverage contiguous memory, CPU cache, SIMD (Single Instruction, Multiple Data: a CPU feature that applies one instruction to several values in a single step, instead of one value at a time), and multi-threaded BLAS (Basic Linear Algebra Subprogram, a standard library of fast, hardware-tuned matrix/vector math routines that numpy calls into) for numeric work.
• Example: adding two arrays uses a single C loop vs Python loop calling millions of Python operations.

Worked example, verified on CPython 3.12 (showing what actually gets computed, not a timing benchmark: exact speed depends on hardware and array size, so run time.perf_counter() yourself to see the real gap on your own machine rather than trusting a hardcoded number here):

import numpy as np

a = np.array([1.0, 2.0, 3.0, 4.0])
b = np.array([10.0, 20.0, 30.0, 40.0])

def add_loop(a, b):
    return [x + y for x, y in zip(a, b)]

def add_vectorized(a, b):
    return a + b

print(add_loop(a, b))
# [11.0, 22.0, 33.0, 44.0]
print(add_vectorized(a, b))
# [11. 22. 33. 44.]

Both compute the identical values; add_loop does it via four separate Python-level additions, each one dispatched, type-checked, and reference-counted by the interpreter, while add_vectorized's a + b dispatches once into a single compiled C loop over the two contiguous memory buffers, which is the mechanism, not a specific timing number, that makes the vectorized version pull ahead as array size grows.

When vectorization can be slower:

• Small arrays: overhead of creating intermediate arrays and function call overhead can dominate; a simple Python loop or in-place updates may be faster.
• Complex element-wise logic with branching: vectorization may require multiple large temporaries or complex masking, increasing memory bandwidth and runtime.

Where vectorization stops winning, made concrete: a piecewise function like "if x < 0, return 0; elif x < 10, return x; else return x**2" vectorizes via np.where, but every branch has to be computed for every element before the mask selects which result to keep:

x = np.array([-3.0, 5.0, 15.0])
result = np.where(x < 0, 0.0, np.where(x < 10, x, x ** 2))
print(result)
# [  0.   5. 225.]

Even though only one branch is "true" for any given element, x ** 2 is computed for all three elements (including -3.0 and 5.0, whose squared values are simply discarded by the outer np.where), and each np.where call allocates a full temporary array the size of x. For a genuinely small array or a function with many branches, that wasted work and temporary-array allocation can cost more than a plain Python loop that only evaluates the one branch each element actually needs; this is the concrete shape of "vectorization can be slower."

Example: computing a piecewise function with many branches can be slower vectorized due to multiple masks and temporary arrays; using numba (a library that compiles a plain Python function to machine code the first time it runs, letting a genuinely branchy per-element loop run at near-C speed without vectorizing it at all) for a compiled loop may be faster and more memory-efficient.

Profiling plan for pandas.apply hotspot:

1. Confirm hotspot: run a coarse profiler (cProfile) to see time spent in apply.

• python -m cProfile -o prof.out script.py
• snakeviz prof.out (a browser-based visualizer for cProfile output) or pstats to inspect

2. Line-level: use line_profiler (pip install line_profiler) and add @profile to the function passed to apply or use kernprof:

• kernprof -l -v script.py
  This shows which lines inside the applied function are expensive.

A concrete pass with real, reproducible numbers (call counts, not timing, since exact seconds are hardware-dependent and not something to hardcode here), verified with pandas on CPython 3.12:

import cProfile, pstats, io
import pandas as pd

def slow_row_calc(row):
    total = 0.0
    for i in range(20):
        total += (row['x'] + i) ** 0.5
    return total

df = pd.DataFrame({'x': range(500)})

pr = cProfile.Profile()
pr.enable()
df['y'] = df.apply(slow_row_calc, axis=1)
pr.disable()

stats = pstats.Stats(pr)
print('total function calls:', stats.total_calls)
# key by function name instead of hardcoding a (filename, line, name) tuple: the
# filename/line depend on how this file happens to be invoked and are not stable
own_key = next(k for k in stats.stats if k[2] == 'slow_row_calc')
print('calls to slow_row_calc:', stats.stats[own_key][0])

On this run (pandas version-dependent in its exact figure, but always dramatically more than one call per row), this printed total function calls: 156327 and calls to slow_row_calc: 500. The second number is unsurprising, 500 rows means 500 calls to your own function, but the first is the real finding a coarse profile surfaces: .apply(..., axis=1) did not cost "500 function calls," it cost over 150,000, because pandas builds a full pandas Series object per row (with its own isinstance checks and internal bookkeeping) before your function even runs, per-row overhead invisible from just reading the code, and exactly the kind of thing a profiler exists to reveal instead of guessing at.

3. Interpret results: if most time in Python-level loops or element ops, apply is causing Python callbacks per row.

4. Optimizations:

• Replace apply with vectorized NumPy/Pandas ops or use groupby.transform.
• Use C-accelerated libraries (numexpr, which evaluates a whole array expression like a*b+c in one call without materializing each intermediate array) for heavy numeric expressions.
• If logic is complex, use numba (compiles a plain Python function to machine code the first time it runs) to JIT-compile the function and call it over NumPy arrays.
• If per-row but pure Python expensive work, consider transforming to C-extension or use multiprocessing/df.map_partitions with Dask (a library that mirrors the Pandas API but splits data into partitions and runs the same operations across them in parallel).

The vectorized replacement for the example above, confirmed to agree exactly with the .apply() version:

y_vectorized = sum((df['x'] + i) ** 0.5 for i in range(20))
import numpy as np
print(np.allclose(df['y'].to_numpy(), y_vectorized.to_numpy()))
# True

This replaces the 500 individual per-row Python-function calls (and everything pandas does to set each of them up) with 20 vectorized column-wide additions, one per term in the loop, each running as a single compiled pass over all 500 rows at once instead of 500 separate Python-level calls.

5. Validate: run profiler again to confirm reduced total call count, and add a regression test asserting the vectorized and original implementations agree (as np.allclose does above) for the critical dataset.

Result: move work from Python-level per-row to vectorized, compiled, or parallel implementations.