From PHP to Xan to Rust: speeding up large CSV imports in Symfony

The goal of a price comparison website is obviously to compare prices across as many webshops as possible. So far Vindle has mainly integrated relatively smaller and more specialized webshops, offering one or a few product types, and each with no more than 40,000 total products.

Recently, however, we started integrating with Bol.com (one of the largest Dutch webshops). This was very exciting since Bol is a much more general webshop covering almost all product types, thus allowing an extra price to be compared for almost all products. But with a more general and larger scale webshop you also get many, many more products—roughly three orders of magnitude more, in fact. Bol.com offers over 73 million products, spread across 26 gzipped CSV feeds totaling roughly 27 GB.

As a price comparison website, there is something else that is very important: the prices shown need to be up to date. To achieve this, Vindle updates all product feeds once an hour, storing all the product feed entries in our database so that further operations can be done based on that stored data. Operations like updating the prices and availability of offers, or finding new offers for existing products.

This is where our journey starts. As you can imagine, suddenly increasing the scale of one merchant integration by roughly three orders of magnitude is going to require some optimizations. Now, since the new Bol.com integration is the main culprit behind this increase, our optimization efforts will be focused here. During this process, we will hopefully also learn some things that will help us optimize future feeds if needed (spoiler: we will—plenty)

Before we even get into parsing the CSV files there is an optimization we can already make, and one we made at the start. The indisputably cheapest and fastest way to parse CSVs is to not parse them at all. One nice thing Bol.com does, as I mentioned before, is split the 73 million products into 26 gzipped CSV files that are about 27 GB in total. The sizes of the individual files, however, range from around 1.5 MB to 4.8 GB, so there is quite a large size difference between them. But this still gives us two nice advantages: we only need to process the files we need, and we can include them in our hourly loop one at a time instead of all at once.

Where we started: PHP was fine

Vindle being a PHP/Symfony application, it made the most sense to just write the initial version in PHP.

The original implementation was about as exciting as you would expect: relying on fgetcsv(). A reasonably well-optimized standard PHP function for reading CSV.

Our first implementation was roughly like this:

merchant feed
    ↓
download and store the file
    ↓
gzip decompression stream
    ↓
PHP CSV reader
    ↓
filtering
    ↓
normalization
    ↓
database

Now normally in an article about optimizing a process of this kind, we would now take some time and talk about how obviously terrible this initial version was in hindsight. We would talk about how building something like this in PHP is super inefficient, the wasted I/O with all this disk writing and reading going on, and various other problems.

But we will not do this. In fact, I'm quite happy with this as an initial version. And here is why:

• It was quick and easy to implement, so we got off the ground fast
• It did exactly what it had to, generating the right data and keeping filtering statistics
• There were no major speed problems at all when running the initial few files
• We did not fall for premature optimization
• The parts of this that we kept in PHP ended up remaining basically the same to this day

So why then does this article exist?

Because we needed to start parsing more and larger Bol.com files to make proper use of their wide product coverage. And this version simply didn't allow for that.

The shortcomings of PHP

The initial PHP-only implementation had one primary bottleneck, and it might not be what you would initially expect: the filtering step.

See, during the filtering step we get rid of the vast majority of products for one of the following reasons:

• There is no valid price
• It does not ship to the Netherlands
• It is not in a new condition
• It has no or an invalid brand
• Or it does not fall under one of our configured allowed categories

Now this last one is the most important since it ends up filtering out most products. As I mentioned, the feed is split up in multiple files but each file contains multiple sub-categories. For example, the digital-reading file contains both digital books as well as e-readers. But since Vindle does not currently offer digital books we just get rid of all of them and only keep the explicitly allowed sub-categories, in this case e-readers.

Because of all this filtering, depending on the file we keep anywhere from about 9% down to only 0.0023% of the products. The most extreme example is a file containing ~7.2 million products, of which we keep about ~170.

Doing all this filtering and getting rid of the majority of the rows was quickly becoming expensive in PHP, in part because of the fgetcsv() function. Fortunately, there was quite a clean seam in the application: we could move this simple pre-processing to an external program and keep the more integrated work in PHP.

First optimization: Xan

Introducing Xan, a command-line CSV processing tool written in Rust. Xan was (almost) exactly the tool that we needed for the job: it allows for basic conditional filtering and selecting specific columns. And it works directly with compressed CSVs.

Symfony made it easy to keep the application logic where it was, while delegating the expensive pre-processing step to an external tool.

Equipped with Xan our pipeline became roughly:

download and store the file
    ↓
Xan
├── filter unwanted rows
└── select required columns
    ↓ (stream)
PHP
    ↓
normalization
    ↓
database

This created a significant speed-up without introducing much complexity. We simply introduced the pre-filtering using Xan and then streaming those filtered rows directly into PHP.

If you are reading this article because you're dealing with a comparable problem, I would highly recommend this sort of solution using Xan. This avoids having to write things in Rust and keeps everything quite contained.

But our pre-processing had one additional requirement. We don't only want the filtered rows. We also keep statistics about why rows were filtered.

For example, after processing a feed it is useful to know how many source rows were rejected because they had no valid offer, because they were not deliverable, because the brand was missing, or because they did not belong to a relevant product group.

Sadly, this means that this relatively simple solution using Xan was not enough.

The hidden cost of multiple passes

Now how does one solve this issue? It's simple, just use more Xan.

So, to circumvent this problem of not being able to retrieve the filtering statistics from Xan directly, we implemented an extra pre-pass that resolves them separately.

Simplified, the flow was:

feed
 ├── Xan pass 1 → exclusion statistics
 └── Xan pass 2 → filtered rows → PHP

This worked, but it also felt like we took one step forward and one step backward, since we were now running Xan twice per feed. Though this was still considerably faster than using plain PHP, this was not going to allow us to comfortably go through all the files we'd need to every hour. This extra pass cost us roughly 20–40 seconds per feed.

From here, a new idea was born: run both passes in parallel. The idea was simple: we centrally read and decompress the feed and then use tee to pipe the result into our two separate Xan processes.

Something like:

               ┌→ Xan statistics
gzip → tee ────┤
               └→ Xan filter → PHP

The main improvements here were:

• removing one disk read cycle
• removing the need to decompress twice

Parallelization doesn't remove work

This idea looked attractive on paper, but in practice it introduced a new problem: backpressure.

Our two Xan branches did not do equal work. The statistics branch only had to count results, while the filtering branch also had to filter rows, stream them into PHP, and wait for PHP to continue processing them. Because Unix pipes have bounded buffers, the slower branch would eventually fill its pipe. Once that happened, tee blocked, which blocked the decompressor, which in turn blocked the upstream producer.

So while the pipeline looked more parallel, its throughput was still determined by the slowest consumer.

On top of that, both branches still had to fully parse the CSV. So although the pipeline removed one disk read and one decompression pass, it still duplicated the expensive row-by-row parsing work.

There was also a bonus problem: implementing clean error handling for this type of split process is much more complicated.

So our supposedly more efficient streaming pipeline now had:

• two CSV parser processes;
• duplicated filtering work;
• additional pipes;
• difficult error handling;
• more process scheduling;
• more context switching;
• and a slowest-consumer bottleneck.

And if all of that was not enough, the performance was also lacking. In general, this implementation was not faster than the serial one. In fact it leaned more toward being a few percent slower, getting worse with larger feeds.

So at this point we realized that this type of pipe contraption would probably not get us to where we wanted. What we needed was much simpler.

One row, one decision

For each row, we already knew exactly what needed to happen:

read row
    ↓
check exclusion rules
    ↓
if excluded:
    update statistics
otherwise:
    write selected columns

The statistics and filtering were not fundamentally incompatible operations. In fact, initially in PHP they were done in the same pass; we were just forced to separate them by the limitations of command-line tools.

Basically, what we needed was:

• An external program more optimized for these types of operations than PHP
• The ability to collect statistics and filter at the same time so we only had to parse each CSV row once

This is when we started to experiment with Rust. In fairness, we considered Rust as an option from the very beginning but we wanted to see what was possible with off-the-shelf tools. Adding Rust to a project like this is not without its own downsides.

The goal here was now very narrow: write this hot loop of filtering and collecting statistics into a small native Rust program. That's all. The only other requirement was that it must allow streaming of input and output data through STDIN and STDOUT. Lastly, it must be as dumb as possible, depending on as little context and business logic as we can make it.

Streaming here is also of particular importance, since at this stage we'd made another realization. The current implementation had a non-deliberate dependency: empty disk space. When dealing with a 5 GB file our current implementation would have to first download that whole file to disk, so the machine doing this process would need 5+ GB of free disk space. Another thing is the fact that writing 5 GB to disk to then read it once and delete it also seemed wasteful. For these reasons we wanted the next iterations to directly stream the incoming download into our preprocessor instead of downloading the entire file to disk. We wanted to make the process diskless.

The boring Rust program

There are a few reasons we chose Rust here, as opposed to another low-level language. But one of the reasons is that Xan is written in Rust and uses a well-optimized CSV library called simd-csv. In fact, simd-csv was designed around Xan's requirements, so we can use it as our foundation and build our relatively simple filtering and statistics collection on top of it.

Its original contract was essentially:

stdin
  ↓
Rust
├── parse CSV
├── apply source filters
├── count exclusions
└── project required columns
  ↓
stdout: filtered CSV

stderr: statistics

The existing PHP ingestion logic could continue consuming the resulting CSV exactly as it did before. There, it could perform the operations that are more integrated with our business logic. That compatibility was very useful: we were replacing one expensive stage of the pipeline rather than redesigning everything around it.

The processing loop itself is super simple:

for each record {
    statistics.total_rows += 1;

    if let Some(reason) = exclusion_reason(record) {
        statistics.exclude(reason);
        continue;
    }

    statistics.retained_rows += 1;
    write_projected_record(record);
}

Yet this achieved the same thing as two passes through Xan and a bunch of piping, at a faster rate. In other words, we had roughly regained the speed of the original single-pass Xan setup, while also getting the filtering statistics that had previously required an additional pass.

Symfony remained in charge

What I like about this architecture is that Symfony never stopped being the core of the application.

Rust only took over the narrow part of the pipeline that consisted of reading a lot of data and performing cheap, deterministic checks. Everything that depends on application context still lives in Symfony: managing and downloading feeds, credentials, domain logic, normalization, persistence and the rest of the actual ingestion workflow.

That boundary ended up being quite useful. We did not need to redesign the application around Rust; Symfony could simply orchestrate another specialized component, while the existing PHP code continued handling the parts it was already well suited for.

We could have continued moving all of that functionality into Rust. This would probably have been even faster in the end, assuming we were not limited by internet speed. But we chose not to.

Why? Because it was simply not worth it. The potential improvement would not have outweighed the extra work and maintenance debt this would have created, especially the latter.

The Rust program was implemented as a simple CLI. We chose that over FFI because FFI would have been more work. In fact, running this as a separate operating-system process gave us isolation, and a wonderfully boring interface.

There are some interesting complications around managing those streams safely from PHP, but they deserve an article of their own.

The next frontier

After we got the Rust preprocessor up and running we ran into a new bottleneck.

The setup with the Rust process was now roughly like this:

compressed stream
    ↓
gzip -dc
    ↓
Rust
    ↓
PHP

Compared to the previous iterations, this was already a much cleaner architecture: no more downloading the whole file to disk, no more multiple passes and no more parallel processes.

But now the processing of the CSV became fast enough to expose a new problem.

Gzip. It was often consuming an entire CPU core while the other parts of this pipeline were using much less.

In practice this meant the Rust code was waiting for more data and therefore even our PHP code was waiting. The external decompressing process was now determining how quickly data would arrive.

This is one of those things that shows how performance optimization often goes:

A is slow
↓
optimize A
↓
B is now slow
↓
optimize B
↓
C becomes visible

So it's not that the decompression suddenly became slower, the opposite happened. The rest of the system was now fast enough that our current decompression method could not keep up.

Moving decompression into Rust

One nice thing about gzip decompression is that there are a ton of libraries for it. So now it was just about finding the best one for this job.

I won't go into the benchmarking process here, but eventually we arrived at the Rust flate2 crate, and found that it performed substantially better for this workload. Something I only realized later is that Xan uses the same library for compressed input handling.

We deliberately hadn't included gzip decompression in the first Rust implementation. gzip -dc already worked, and until it started limiting throughput there was no reason to replace it. But at this point it made a lot of sense to also move the decompression to Rust, instead of a separate process.

Internally, the interesting part is very small. The gzip decoder implements Rust's Read interface, and our feed processor was already generic over anything implementing Read.

That left us with the final hot path:

Symfony/PHP
└── Bol.com
    ↓
compressed network stream
    ↓
Rust
├── gzip decompression
├── CSV parsing
├── filtering
├── statistics
└── column projection
    ↓
filtered CSV
    ↓
Symfony/PHP
├── validation
├── normalization
├── affiliate logic
└── persistence

No feed-sized temporary source file is required for the normal ingestion path.

No second CSV parsing pass is required for statistics.

And PHP only receives the rows that survived the cheap source-level filtering.

What changed in practice?

The result of these optimizations has been a material improvement to our hourly ingestion cycle, ultimately allowing us to go through all the Bol.com feeds we need without causing any problems or delays.

|Test|Before|After|Improvement| |---|---|---|---| |Mobile feed – PHP → Xan (1.73M rows)|357.3 s|17.7–18.5 s|~95% less time / ~20× faster| |Computer feed – external gzip → Rust gzip|24.10 s|9.48 s|60.7% less time / 2.54× faster| |Computer – full production snapshot|60.6 s|39.2 s|35.3% faster| |Daily-care – full production snapshot|64.3 s|53.6 s|16.6% faster|

The first two rows are controlled processing benchmarks, while the last two are production snapshot observations on live inputs.

The main gains here are not in isolated microbenchmarks, but in the end-to-end ingestion process. Across representative feeds and production snapshots, the improvements ranged from meaningful to dramatic, and together they gave us enough headroom to process the Bol.com feeds we needed comfortably within our hourly update cycle.

And there is another practical benefit, enabled in part by making this process diskless. The larger feeds, which previously were unattractive to support, are now much less problematic because we no longer have to worry about their disk footprint. And even though we still have to download and parse every byte of them, the speed improvements have enabled us to support them hourly.

What we learned from the process

Should we start rewriting all CSV processing in Rust? No.

In fact, I think that for most Symfony developers, moving processing to an external system or writing a process in another language for a speed-up should not be done lightly, and definitely not straight away. It also creates its own overhead.

The progression is the main takeaway here:

• PHP was the right implementation at the beginning, and large parts of that code still remain
• Xan was the right move when we realized pre-processing was becoming expensive
• Rust became attractive when our processing requirements became sufficiently application-specific that general-purpose tools were not effectively able to do what we needed.

I think this progression also maps quite well to a rough decision tree for dealing with performance problems in PHP:

PHP performance problem
        ↓
Isolate the hot path
        ↓
Can PHP handle it with reasonable optimization?
      ↓                         ↓
    yes                         no
    ↓                           ↓
Stay in PHP             Is there a good external tool?
                              ↓              ↓
                            yes             no
                            ↓               ↓
                     Use the tool      Write a targeted low-level component

Removing disk I/O does not automatically make a pipeline faster

Streaming in this process had some important benefits, mainly that we no longer needed to fully download these large files. But if the machine is CPU-bound, replacing disk access with more simultaneous CPU work can actually make throughput worse.

Measure the pipeline rather than assuming that “streaming” or “parallel” means “faster.”

Parallel work is still work

Our tee experiment looked elegant:

one decompression
two parallel consumers

But both consumers still had to parse every row, parallelizing duplicate work doesn't magically make it less expensive. On a small VPS with limited CPU capacity, that distinction matters a lot.

Backpressure is useful

In a streaming architecture, a blocking or waiting step can seem like something is going wrong. But in reality it is what makes the architecture work, by keeping it bounded. If any part of the chain slows down, the section before it just waits once its buffer fills up. Memory usage remains controlled because each stage naturally slows the stage before it when needed.

Optimize the operation, not the language

The biggest structural improvement came from turning:

parse for statistics
+
parse for filtering

into:

parse once
→ statistics + filtering

Rust made implementing that efficient loop straightforward, but changing the algorithm mattered just as much as changing the runtime.

The best language boundary can be a pipe

We initially considered whether integrating Rust with PHP should be done through FFI. However, for this workload and type of problem that would have added unnecessary complexity. Since we're not calling a tiny Rust function thousands of times, we have a single long-running native process parsing a large stream of data.

Because of this, running as a subprocess gives us a clean boundary, natural streaming, crash isolation and almost no coupling between the two ecosystems.

Sometimes stdin, stdout and an exit code are all the integration layer you need.

Where it leaves us

The final architecture is more complicated than the original implementation in PHP.

But the complexity is narrowly contained.

Symfony still behaves like a Symfony application.

Rust behaves like a fast Unix-style preprocessing tool.

And the interface between them consists primarily of streams.

The result is a feed ingestion pipeline that can deal much more comfortably with the scale we're starting to encounter, without requiring the rest of the application to care how those source rows were reduced.

And multiple new possibilities have opened up to us. The Rust component could eventually become a reusable core for other heavy data-processing operations. Other feeds could move onto the same preprocessing infrastructure if it's shown that it is worthwhile. And we have the option to eventually implement FFI functions if they're justified.

But for now, the most useful result is also the simplest one:

We found one expensive loop in a Symfony application, moved exactly that loop somewhere better suited to running it, and left everything else alone.