DEV Community: Rishabh Bisht

How to Scale to a Billion Documents in Symfony - Part II

Rishabh Bisht — Fri, 27 Feb 2026 13:08:44 +0000

This article was written by Andreas Braun.

Building an Application

In the first part of this series, we designed a schema to store fuel prices in MongoDB and imported a large amount of data into the database. We also looked at how to efficiently aggregate this data using MongoDB's aggregation framework. With the database prepared, it is now time to build a Symfony application that allows us to interact with this data. In this part of the tutorial, we'll use the Doctrine MongoDB ODM to map our documents to PHP classes, and Symfony UX to build a modern frontend.

To recap, this is what we want our application to do:

Show a dashboard with some general information
Show a list of stations
Allow searching for a station by name, brand, or location
Show the details of a station, including:
- A map showing the station's location
- The latest prices for each fuel type
- A chart showing the price history for the last day

Setting Up the Application

First, we set up our Symfony application using the Symfony CLI. If you don't have it installed yet, you can find instructions on the Symfony website. Once the CLI is installed, create a new Symfony project:

symfony new --webapp fuel-price-app

This creates a new web application with the latest version of Symfony. Since we don't need the Doctrine ORM, we can remove a number of packages from composer.json:

composer remove doctrine/dbal doctrine/doctrine-bundle doctrine/doctrine-migrations-bundle doctrine/orm symfony/doctrine-messenger

Now, make sure that you have the MongoDB extension installed and enabled. If you don't have it installed yet, install it using pie:

pie install mongodb/mongodb-extension:^2.1

Now we're ready to install the Doctrine MongoDB ODM bundle. This bundle allows easy configuration of the Doctrine MongoDB ODM and provides a set of tools to work with MongoDB in Symfony applications. To install the bundle, run the following command in your project directory:

composer require doctrine/mongodb-odm-bundle

Using Symfony Flex, this command sets up everything you need to connect to MongoDB. You can check the default configuration in config/packages/doctrine_mongodb.yaml. To connect to your database, update the MONGODB_URL environment variable in your .env file.

Mapping Documents

The first document we'll map is the station data. Create a new Document\Station class and add fields for everything we need to store:

<?php

declare(strict_types=1);

namespace App\Document;

use App\Document\Partial\AbstractStation;
use App\Repository\StationRepository;
use Doctrine\Common\Collections\ArrayCollection;
use Doctrine\Common\Collections\Collection;
use Doctrine\ODM\MongoDB\Mapping\Annotations\Document;
use Doctrine\ODM\MongoDB\Mapping\Annotations\EmbedOne;
use Doctrine\ODM\MongoDB\Mapping\Annotations\Field;
use Doctrine\ODM\MongoDB\Mapping\Annotations\Id;
use Doctrine\ODM\MongoDB\Mapping\Annotations\Index;
use GeoJson\Geometry\Point;
use Symfony\Component\Uid\Uuid;
use Symfony\Component\Uid\UuidV4;

#[Document(repositoryClass: StationRepository::class)]
#[Index(keys: ['location' => '2dsphere'])]
class Station
{
    #[Id(type: 'uuid', strategy: 'none')]
    public readonly Uuid $id;

    #[Field]
    public string $name;

    #[Field]
    public string $brand;

    #[Field(type: 'point')]
    public Point $location;

    #[EmbedOne(targetDocument: Address::class)]
    public Address $address;

    public function __construct(string|UuidV4|null $id = null)
    {
        $this->id = $id instanceof UuidV4 ? $id : new UuidV4($id);
    }
}

Note that we're not yet mapping the latestPrice and latestPrices fields. We'll do that later when we have the DailyPrice document ready. While we're mostly relying on imported data, we still want fully functionable documents, so we make sure that we can create a new station both with and without an ID. We store the ID as a uuid type, which was recently added to the Doctrine MongoDB ODM. This allows us to use the Uuid class from Symfony, mapping to a MongoDB Binary UUID. The location is stored as a Point type, which I also created specifically for this project.

The Address class is a simple value object that we map as an embedded document. Since we're only handling German addresses, we can keep it simple and implement the Stringable interface for easy string conversion. Here's the complete Address class:

<?php

declare(strict_types=1);

namespace App\Document;

use Doctrine\ODM\MongoDB\Mapping\Annotations\EmbeddedDocument;
use Doctrine\ODM\MongoDB\Mapping\Annotations\Field;
use Doctrine\ODM\MongoDB\Mapping\Annotations\Index;
use Stringable;

#[EmbeddedDocument]
class Address implements Stringable
{
    public function __construct(
        #[Field]
        public string $street,
        #[Field]
        public string $houseNumber,
        #[Field]
        #[Index]
        public string $postCode,
        #[Field]
        public string $city,
    ) {
    }

    public function __toString(): string
    {
        return <<<EOT
$this->street $this->houseNumber
$this->postCode $this->city
EOT;
    }
}

The mapping also shows the indexes we'll want to create. For the stations themselves, we're creating a 2dsphere index on the location field to allow for geospatial queries. The address.postCode field is indexed to allow for quick lookups by postal code. We'll talk about more indexes later, but for now this will suffice.

Before we can continue with the DailyPrice document, we need to think about the station mapping. We want to store stations as a standalone document, but we also want to be able to embed station data into the DailyPrice document. Embedding certain station data allows us to query prices without having to join two collections. Since documents and embedded documents require different mapping information, we'll have to introduce an abstraction in the form of a mapped superclass:

use Doctrine\ODM\MongoDB\Mapping\Annotations\EmbedOne;
use Doctrine\ODM\MongoDB\Mapping\Annotations\Field;
use Doctrine\ODM\MongoDB\Mapping\Annotations\Index;
use Doctrine\ODM\MongoDB\Mapping\Annotations\MappedSuperclass;
use GeoJson\Geometry\Point;

#[MappedSuperclass]
#[Index(keys: ['location' => '2dsphere'])]
abstract class AbstractStation
{
    #[Field]
    public string $name;

    #[Field]
    public string $brand;

    #[Field(type: 'point')]
    public Point $location;

    #[EmbedOne(targetDocument: Address::class)]
    public Address $address;
}

We then create one station document that is used as a standalone document:

class Station extends AbstractStation
{
    #[Id(type: 'binaryUuid', strategy: 'none')]
    public readonly Uuid $id;

    public function __construct(string|UuidV4|null $id = null)
    {
        $this->id = $id instanceof UuidV4 ? $id : new UuidV4($id);
        $this->latestPrices = new ArrayCollection();
    }
}

The embedded station document is then mapped as follows:

#[EmbeddedDocument]
class EmbeddedStation extends AbstractStation
{
    #[ReferenceOne(name: '_id', storeAs: 'id', targetDocument: Station::class)]
    private Station $referencedStation;
}

In the embedded document, we include a reference to the Station collection, but we only store its identifier in the _id field. This is a common strategy to deal with denormalized data in MongoDB, as it allows us to resolve the reference using a $lookup operator or the ODM’s reference mechanisms when we need to access data that we haven’t duplicated into the embedded document. We'll be using the exact same strategy for the DailyPrice document, since it needs to be embedded into the Station document as well. So, let's map that next:

#[MappedSuperclass]
class AbstractDailyPrice
{
    #[Id]
    public readonly string $id;

    #[Field(type: Type::DATE_IMMUTABLE)]
    public readonly DateTimeImmutable $day;

    #[Field(enumType: Fuel::class)]
    public readonly Fuel $fuel;

    #[Field(nullable: true)]
    public readonly ?float $openingPrice;

    #[Field]
    public readonly float $closingPrice;

    #[EmbedOne(targetDocument: Price::class)]
    public readonly Price $lowestPrice;

    #[EmbedOne(targetDocument: Price::class)]
    public readonly Price $highestPrice;

    #[EmbedMany(targetDocument: Price::class)]
    public readonly Collection $prices;

    #[Field]
    public readonly float $weightedAveragePrice;
}

This is the base class for all DailyPrice documents, and it comes with a small embedded document:

#[EmbeddedDocument]
class Price
{
    #[Field(name: '_id', type: Type::OBJECTID)]
    public readonly string $id;

    #[Field(type: Type::DATE_IMMUTABLE)]
    public readonly DateTimeImmutable $date;

    #[Field()]
    public readonly float $price;

    #[Field()]
    public readonly ?float $previousPrice;
}

The DailyPrice document we embed in the Station document does not need to embed a station, so it can be empty:

#[EmbeddedDocument]
class EmbeddedDailyPrice extends AbstractDailyPrice
{
}

The actual DailyPrice document that we store in its own collection adds an embedded relationship to a station:

#[Index(keys: ['fuel' => 1, 'day' => -1, 'station._id' => 1], unique: true)]
#[Index(keys: ['station._id' => 1, 'day' => -1])]
class DailyPrice extends AbstractDailyPrice
{
    #[EmbedOne(targetDocument: EmbeddedStation::class)]
    public readonly EmbeddedStation $station;
}

That's the most important mappings completed. You can also see that we create two separate indexes: one for fuel, day, and station that we also require to be unique, and another for the station and day. These indexes will cover most of our use cases. When building indexes, I usually apply the ESR (Equality, Sort, Range) guideline. The first fields in an index should be those used to do equality checks (e.g. checking for a specific fuel type), followed by fields used for sorting, and lastly fields used for range queries. There may be reasons to slightly deviate from this guideline, but in most cases it works well. Compound indexes in MongoDB also come with prefix indexes, meaning that if you query for a subset of fields in an index, MongoDB can still use that index for the query. Our first index on fuel, day, and station._id can also be used to query for just fuel and day, or just for fuel. For queries that don't filter by fuel type, we can use the second index on station._id and day. Note that the order of the station ID and day are flipped here, as we may want to sort by date when querying for a station's prices, or might be looking for a range of days for a station. Speaking of sorting, you'll notice that the index order is reversed on the day field, and that data is sorted in descending order. We are more likely to be interested in newer price data than in older price data, so a reverse sort on the index makes our queries more efficient.

Showing Station Details

Now that we have our documents mapped, let’s show some data in our application. I’ll skip over the details as the controllers for the station list and details page are essentially CRUD controllers. If you are interested in those details, you can check out the full source code of the controller. In the templates, I created several Symfony UX components to have reusable cards showing important station information, like the name. One example would be the one for station details that includes a map of where to find this station. By using Symfony UX components, we can encapsulate the logic for generating the map and adding all the information inside a dedicated PHP class:

#[AsTwigComponent('Station:Map')]
class Map
{
    public AbstractStation $station;

    public function getMap(): UxMap
    {
        [$longitude, $latitude] = $this->station->location->getCoordinates();
        $location = new Point($latitude, $longitude);

        $map = new UxMap();
        $map
            ->center($location)
            ->zoom(13)
            ->addMarker(new Marker(
                position: $location,
                title: $this->station->name,
                infoWindow: new InfoWindow(
                    headerContent: $this->station->name,
                    content: nl2br((string) $this->station->address),
                ),
            ));

        return $map;
    }
}

We can then include a map for a station wherever we want by including our handy little component:

<twig:Station:Map station="{{ station }}" />

We’re now showing a list of stations and some details about that station, but we’re not displaying prices yet. So, let’s see how we can efficiently show prices for the station.

Showing Latest Prices

When we imported data, we stored the latest price for each fuel type in the Station document, as well as a list of the last 30 days. To remind ourselves, this is the latestPrice field in the Station document:

{
  "latestPrice": {
    "diesel": {
      "_id": {
        "$oid": "673f8d1f3de33b548fc21a9f"
      },
      "closingPrice": 1.599,
      "day": {
        "$date": "2024-11-19T00:00:00.000Z"
      },
      "fuel": "diesel",
      "highestPrice": {
        "_id": {
          "$oid": "673f29d86526447058420b3d"
        },
        "date": {
          "$date": "2024-11-19T11:30:38.000Z"
        },
        "price": 1.649
      },
      "lowestPrice": {
        "_id": {
          "$oid": "673f29df65264470584ab598"
        },
        "date": {
          "$date": "2024-11-19T21:07:55.000Z"
        },
        "price": 1.559
      },
      "openingPrice": 1.599,
      "prices": [],
      "weightedAveragePrice": 1.6
    },
    "e10": {},
    "e5": {}
  }
}

To map this data, we'll need to create a new DailyPriceReport class that represents a daily price report for all fuel types at a station:

#[EmbeddedDocument]
class LatestPrice
{
    #[EmbedOne(targetDocument: EmbeddedDailyPrice::class)]
    public readonly EmbeddedDailyPrice $diesel;

    #[EmbedOne(targetDocument: EmbeddedDailyPrice::class)]
    public readonly EmbeddedDailyPrice $e5;

    #[EmbedOne(targetDocument: EmbeddedDailyPrice::class)]
    public readonly EmbeddedDailyPrice $e10;
}

We can now embed this document in the Station class. We can build a similar embedded document with an EmbedMany relationship for the last 30 days of prices, which we'll call LatestPrices and map it here as well:

class Station extends AbstractStation
{
    #[EmbedOne(targetDocument: LatestPrice::class)]
    public ?LatestPrice $latestPrice = null;

    #[EmbedOne(targetDocument: LatestPrices::class)]
    public ?LatestPrices $latestPrices;
}

Once we have our documents mapped, we can create more UX components to display them. For example, this is the component to show the latest price data:

{# @var App\Document\EmbeddedDailyPrice #}
{% props dailyPrice = null %}

{% if dailyPrice is not null %}
<dt>{{ dailyPrice.fuel.displayValue }} <span class="text-muted">({{ dailyPrice.latestPrice.date|date }})</span></dt>
<dd>
    <twig:Price price="{{ dailyPrice.latestPrice.price }}" />
    (Average: <twig:Price price="{{ dailyPrice.weightedAveragePrice }}" />)
</dd>
{% endif %}

I want to point out one small detail: accessing the latest price in a DailyPrice object. While the latest price is also stored as the closingPrice, we don't record the time or any other detail for that. So, in our templates, we'd always have to call dailyPrice.prices.last() to access this. With PHP 8.4 and property accessors, we can also create a virtual property that returns the latest price:

public ?Price $latestPrice {
    get {
        return $this->prices->last() ?: null;
    }
}

This way, our template doesn't need to know where the latest price comes from, and if we later change our schema to store all price information in the closingPrice field, we can change the implementation of this property without affecting the template code. It's these small details that sometimes make code easier to understand and maintain.

Our application now serves a paginated list of stations, and we can click on a station to see it on a map and see its latest prices. If you remember, we also wanted to show a chart with the price history. In the next part of this tutorial, we'll do exactly that. We'll be using Symfony UX's Chart.js integration to show charts on the station page, and we'll also embed charts from Atlas Charts.

Visualizing Data with Charts

Welcome to the third part of this series. In the previous page, we ended up with a list of stations and a details page that shows the latest prices for each fuel type. In this part, we want to add some charts to better visualize the price data that we have. To do this, we can start by using Symfony UX's Chart.js integration. First, we install the package as well as an adapter for Chart.js to handle dates:

composer require symfony/ux-chartjs
./bin/console importmap:require chartjs-adapter-date-fns

Once it's installed, we can create a small component that creates and renders our chart. The template only contains a call to render_chart(), all the logic is contained in the component class:

#[AsTwigComponent('Chart:Station:DayPriceOverview')]
class DayPriceOverview
{
    public LatestPrice $latestPrice;

    public function __construct(protected readonly ChartBuilderInterface $chartBuilder)
    {
    }

    public function getChart(): Chart
    {
        $chart = $this->chartBuilder->createChart(Chart::TYPE_LINE);

        $chart->setData([
            'datasets' => $this->createDatasets(),
        ]);

        $chart->setOptions([
            'parsing' => false,
            'datasets' => [
                'line' => ['stepped' => 'before'],
            ],
            'scales' => [
                'x' => ['type' => 'time'],
            ],
        ]);

        return $chart;
    }

    private function createDatasets(): array
    {
        $datasets = [];

        foreach (Fuel::cases() as $fuel) {
            $fuelType = $fuel->value;
            if (! isset($this->latestPrice->$fuelType)) {
                continue;
            }

            $datasets[] = $this->createDataset($this->latestPrice->$fuelType, $fuel);
        }

        return $datasets;
    }

    private function createDataset(EmbeddedDailyPrice $dailyPrice, Fuel $fuel): array
    {
        return [
            'label' => $fuel->value,
            'data' => array_map(
                static fn (Price $price) => [
                    'x' => $price->date->getTimestamp() * 1000,
                    'y' => $price->price,
                ],
                $dailyPrice->prices->toArray(),
            ),
        ];
    }
}

The logic basically revolves around massaging the data so that Chart.js can display it without having to parse it. We use millisecond timestamps for the X axis, and prices for the Y axis, showing each fuel type as its own dataset. We can now render this chart in our template. We can do pretty much the same for the price history chart, where we want to show the lowest, average, and highest prices for each of the last 30 days. We already have all the necessary data in the latestPrices station field, so we can create an equivalent component for that data. With that, we're already showing a good chunk of information about the station's prices. And, since we have pre-aggregated this data, the view page is lightning fast and requires a single query to the station collection. If we had to join to a collection storing all prices, loading this data would take significantly longer.

This approach works great for the data that we have denormalized into the Station document, and we can also leverage it for other data that we can fetch from the database. Since we might have to aggregate the DailyPrice collection directly for other charts, we should also consider caching data for charts. This is especially important for charts that show historical data, as we don't want to run expensive aggregation queries every time a user visits a page. There is another alternative for this so we don't have to do this ourselves: MongoDB Atlas Charts. Atlas Charts allows us to create charts based on our MongoDB data, all without having to write any code. We can then embed single charts or an entire dashboard into our Symfony application using the Atlas Charts Embed API. Atlas Charts automatically caches chart data, so we don't have to worry too much about the performance of our charts. A full deep dive into Charts would be a bit too much for this blog post. Feel free to check out Atlas Charts yourself if you want to go further!

We're now in a good place: we have a working application that allows us to view stations, their latest prices, and some charts to visualize the data. The data we have was entirely imported from a CSV dump, and we haven't yet considered adding new data to the database. In the next part of this series, we'll look at how we can maintain consistency in this denormalized database and how MongoDB's query framework makes this easier.

How to Scale to a Billion Documents in Symfony

Rishabh Bisht — Wed, 14 Jan 2026 11:45:32 +0000

This article was written by Andreas Braun. Check out part 2 of this article.

If you regularly drive a car, you will no doubt be familiar with fuel prices. They vary wildly from country to country, change frequently depending on global oil prices, and are a constant discussion topic in politics. Have you sometimes felt that prices rise and fall somewhat arbitrarily? Speaking of price changes, how often do prices change where you live? How much could you save if you travelled to the next city, or even country to refuel your car?

While these questions may seem silly to some, to people in Germany, they come up quite often. You'd be surprised to hear that fuel prices in Germany change multiple times per day, and they vary wildly between mornings and evenings, and even between different cities in Germany. In 2012, the German government passed a law that enabled the Federal Cartel Office to require petrol stations to submit their fuel prices to a central database, which can then be consumed by various websites and apps to help people find the cheapest petrol station in their area. While the data isn't considered "open data" and access is only granted to people or companies that maintain a service to inform consumers, there is one such service that provides full access to all data. Since I was always interested in working with big amounts of data to see various trends, I decided to use this dataset to build a small application that shows some trends. This also provides a good opportunity to show how to work with such large amounts of data in MongoDB.

When we’re done, we’ll want to show a map of where a station is, its data, and the price history for the last day for which we have prices (which would be the current day once the application receives live data):

In this first part, we're going to inspect the data we have, how we can import it into MongoDB, and how we can design a schema that allows us to efficiently work with the data. In the second part, we will then build a Symfony application to display the data.

A first look at the data

Unfortunately, there is no open data solution to get access to this, and the data is only provided to registered applications that provide an information service to users. However, CSV files were available through a git repository for a while, which is what I used here. Cloning the repository gives us a first indication of what a massive dataset we're dealing with here:

du -hcs *
 78G    prices
8.0K    README.md
9.8G    stations
 87G    total

78 GB of price data, and almost 10 GB of station data. That's quite the dataset we've got here! Within the two datasets, the files are conveniently organized by year and month, with one file per day:

ls -1 prices/2025/06
2025-06-01-prices.csv
2025-06-02-prices.csv
[...]

ls -1 stations/2025/06
2025-06-01-stations.csv
2025-06-02-stations.csv
[...]

This is good news for us, as it allows us to slowly start working with some data while we build our schema, then import more and more data as we try to stretch the performance limits. But what does 78 GB of price data even mean? First, let's see how many stations we have:

wc -l stations/2025/06/2025-06-24-stations.csv 
   17592 stations/2025/06/2025-06-24-stations.csv

Ok, this is not too bad—we have just under 18,000 stations in the last file. Let's see how many price records we have for that day:

wc -l prices/2025/06/2025-06-24-prices.csv  
  453714 prices/2025/06/2025-06-24-prices.csv

Wow, more than 450,000 price records for a single day, which works out to an average of 25.8 records per station per day! This is going to be a challenge, but let's not get ahead ourselves. First, let's take a more detailed look at the data to see what information we have, and then talk about how to efficiently store such data in MongoDB.

Analyzing data

Let's start by looking at the station data. The first line of the CSV file contains the column names, so we can easily see what data is available:

uuid,name,brand,street,house_number,post_code,city,latitude,longitude,first_active,openingtimes_json
0e18d0d3-ed38-4e7f-a18e-507a78ad901d,OIL! Tankstelle München,OIL!,Eversbuschstraße 33,,80999,München,48.1807,11.4609,1970-01-01 01:00:00+01,"{""openingTimes"":[{""applicable_days"":192,""periods"":[{""startp"":""07:00"",""endp"":""20:00""}]},{""applicable_days"":63,""periods"":[{""startp"":""06:00"",""endp"":""22:00""}]}]}"

This looks quite promising, and just from this first look, we can already make some assumptions about the dataset:

The identifier is a UUID.
There's a name and a brand for the station, the latter of which will be quite interesting when comparing stations.
The address data is split into street, house number, post code, and city. However, the first record already shows the house number in the street field, so the data might need some cleaning if we want to use this data.
There are coordinates to place the petrol station on a map. We can use these coordinates in a geospatial index to find stations near a given location and better compare prices.
The first_active is supposed to indicate when the station was first active, but given that the first record lists the Unix epoch timestamp, this field might not provide data for all stations. We might just skip this as a result.
There's a JSON field containing the opening hours of the station. This data doesn't look like it will be useful for querying for open stations, so we will likely want to ignore this data as well.

Now, let's look at the price data. After all, this is what we came here for. Here's an example record:

date,station_uuid,diesel,e5,e10,dieselchange,e5change,e10change
2025-06-24 00:00:32+02,cbc2ab85-28ad-47e7-a102-3a0990f5d615,1.559,1.689,1.629,1,1,1

From each record, we can obtain the following data:

The date and time of the report
The station that reported the price
Prices for three types of fuel (Germany uses two different types of petrol with 5% and 10% ethanol)
Indicators whether any price changed

Looking at more data in this file, we can spot a couple of odd rows:

2025-06-24 00:01:33+02,8336b8d7-01fc-4f79-bdd3-6870c8268035,1.669,0.000,0.000,1,0,0
2025-06-24 00:01:33+02,87666c5e-eead-4ead-9b8c-eb8ab410fddb,1.599,1.679,1.619,0,0,1

Both rows indicate that some prices changed and others didn't, but there's a small difference: One row reports a price despite no change being recorded, and the other doesn’t. I'm not sure if this is significant—it could mean that the first station doesn't sell that fuel type. Either way, we'll want to exclude such data from our import. It is also interesting that the schema isn't properly normalized: Typically, the data would be stored in individual rows for each fuel type. This would prevent those odd rows from above:

date,station_uuid,fuel,price
2025-06-24 00:00:32+02,cbc2ab85-28ad-47e7-a102-3a0990f5d615,diesel,1.559
2025-06-24 00:00:32+02,cbc2ab85-28ad-47e7-a102-3a0990f5d615,e5,1.689
2025-06-24 00:00:32+02,cbc2ab85-28ad-47e7-a102-3a0990f5d615,e10,1.629
2025-06-24 00:01:33+02,8336b8d7-01fc-4f79-bdd3-6870c8268035,diesel,1.669
2025-06-24 00:01:33+02,87666c5e-eead-4ead-9b8c-eb8ab410fddb,e10,1.619

This was most likely a deliberate choice to make the schema more efficient: Storing three additional flags (even when used as a byte) and two additional float fields per row is definitely more efficient than storing the date and UUID fields three times. So, while my university professor would probably dock some points in an exam for this, it is a valid choice. To confirm my suspicion, let's just count the number of lines in the entire price dataset:

wc -l prices/**/*.csv
 993187497 total

Yes. Almost a billion price records in total! With around 50% of those records changing all three prices, we're probably looking at more than two billion individual prices in the dataset. No wonder they started optimizing the database. Now that we know what we're dealing with, let's try to design a schema for this.

Designing the schema

When we draw the schema up as an entity relationship (ER) diagram, it looks relatively straightforward:

In a relational database, we could start modeling tables based on this diagram, but as we saw with the prices, the sheer amount of data might force us to make some compromises. We're also using MongoDB, so a relational schema isn't going to be the way to go.

Station data is going to be easy, with the station document containing all data found in the stations CSV file. We can store the address as an embedded document, as well as the location as a GeoJSON point:

{
  "_id": {
    "$binary": {
      "base64": "DhjQ0+04Tn+hjlB6eK2QHQ==",
      "subType": "04"
    }
  },
  "name": "Oil! Tankstelle München",
  "brand": "OIL!",
  "address": {
    "street": "Eversbuschstraße 33",
    "houseNumber": "",
    "postCode": "80999",
    "city": "München"
  },
  "location": {
    "type": "Point",
    "coordinates": [
      11.4609,
      48.1807
    ]
  }
}

The ID might look a bit strange here, but this is actually the same UUID as in the CSV file. We can store it as a BSON binary with subtype 4, which stores a UUID according to RFC 4122. The $binary notation is the extended JSON representation of the BSON binary type, which is used to represent these BSON types in JSON.

Storing prices requires a little more thinking. If we wanted to store prices in a normalized way, we would store a document for each price report and reference the station and fuel type:

{
  "_id": {
    "$oid": "673f8a8b3de33b548f9228d0"
  },
  "fuel": "e5",
  "station": {
    "$binary": {
      "base64": "DhjQ0+04Tn+hjlB6eK2QHQ==",
      "subType": "04"
    }
  },
  "date": {
    "$date": "2025-06-24T05:08:10.000Z"
  },
  "price": 1.839
}

While this would certainly work and make my university professor quite happy, it isn't a very efficient way of storing prices for multiple reasons:

The metadata for each price report is quite large compared to the actual information we want to store.
With roughly 150 million price reports per year, this metadata would require a lot of storage space, which is not very efficient.
With the collection growing to billions of documents, indexes will grow quite large as well, further complicating our storage requirements.
Showing aggregate data requires lots of reads on this large collection, making the process quite slow.

Thanks to MongoDB's document model, we don't have to live with these downsides. Instead, we can leverage schema patterns to store data more efficiently.

The bucket pattern allows us to group data into larger documents, which can be stored and queried more efficiently. MongoDB's time series collections apply this pattern automatically, but we can't use it because we'll be using the $merge aggregation pipeline stage, which doesn't work with time series collections. An important consideration when using the bucket pattern is the granularity of the data we want to store. This is where it's useful to think about how you will be using the data.

Our first use case will be to look for a given fuel station, then seeing their latest prices and maybe the price history for that day. We might also be interested in showing aggregated prices over a longer period of time. With this use case, it makes sense to create buckets for each day, containing all prices for that particular day. Our bucket will thus be identified by a station, a fuel type, and a date. Prices will be stored in a list that we'll keep sorted in ascending order. This is what that document will look like:

{
  "_id": {
    "$oid": "673f8a8b3de33b548f9228d0"
  },
  "fuel": "e5",
  "station": {
    "$binary": {
      "base64": "DhjQ0+04Tn+hjlB6eK2QHQ==",
      "subType": "04"
    }
  },
  "day": {
    "$date": "2025-06-24T00:00:00.000Z"
  },
  "prices": [
    {
      "date": {
        "$date": "2025-06-24T05:08:10.000Z"
      },
      "price": 1.839
    }
  ]
}

This document certainly covers the use case above, but there's a small problem. To find a station, we need to either know the station's ID (e.g., by looking it up in the station collection), or we need to join to the station collection to find the station we're looking for. As you can imagine, this will slow down queries quite a bit. Again, we can make use of MongoDB's document model to work around this problem: Instead of storing a reference to the station, we can embed all station data in the bucket document. This way, we can create all necessary indexes and efficiently query the data. This is quite common in MongoDB, especially when working with data that doesn't change very often.

Keep in mind that this is a trade-off: Our documents will occupy more storage space, and if station data changes, we will need to update all its bucket documents. In our case, this is a fair trade-off as the data is not likely to change often, and the performance benefits of more efficient queries outweigh the increased storage requirements. Note that we don't necessarily have to duplicate all data when embedding. The amount of data to be duplicated will heavily depend on the use case, how often the data changes, and how often it will be needed by the application. This is our price document with embedded station data:

{
  "_id": {
    "$oid": "673f8a8b3de33b548f9228d0"
  },
  "fuel": "e5",
  "station": {
    "_id": {
      "$binary": {
        "base64": "DhjQ0+04Tn+hjlB6eK2QHQ==",
        "subType": "04"
      }
    },
    "name": "Oil! Tankstelle München",
    "brand": "OIL!",
    "address": {
      "street": "Eversbuschstraße 33",
      "houseNumber": "",
      "postCode": "80999",
      "city": "München"
    },
    "location": {
      "type": "Point",
      "coordinates": [
        11.4609,
        48.1807
      ]
    }
  },
  "day": {
    "$date": "2025-06-24T00:00:00.000Z"
  },
  "prices": [
    {
      "date": {
        "$date": "2025-06-24T05:08:10.000Z"
      },
      "price": 1.839
    }
  ]
}

With this schema, we can now efficiently query prices, but if we want to show prices over a larger period of time, there's still some aggregation to do. Let's say that on the detail page for a station, we want to show the lowest, average, and highest price for a given fuel type over the last 30 days. We could certainly do this by querying the right documents, then aggregating the prices using the aggregation framework. However, once a day has ended, this data is not going to change anymore. It would make sense to pre-aggregate this data and store it in the bucket. So, let's add these aggregated values to our price document:

{
  "_id": {
    "$oid": "673f8a8b3de33b548f9228d0"
  },
  "fuel": "e5",
  "station": {
    "_id": {
      "$binary": {
        "base64": "DhjQ0+04Tn+hjlB6eK2QHQ==",
        "subType": "04"
      }
    },
    "name": "Oil! Tankstelle München",
    "brand": "OIL!",
    "address": {
      "street": "Eversbuschstraße 33",
      "houseNumber": "",
      "postCode": "80999",
      "city": "München"
    },
    "location": {
      "type": "Point",
      "coordinates": [
        11.4609,
        48.1807
      ]
    }
  },
  "day": {
    "$date": "2025-06-24T00:00:00.000Z"
  },
  "prices": [
    {
      "date": {
        "$date": "2025-06-24T05:08:10.000Z"
      },
      "price": 1.839
    }
  ],
  "lowestPrice": {
    "date": {
      "$date": "2025-06-24T05:08:10.000Z"
    },
    "price": 1.839
  },
  "highestPrice": {
    "date": {
      "$date": "2025-06-24T05:08:10.000Z"
    },
    "price": 1.839
  },
  "averagePrice": 1.839
}

You'll note that we're not just storing the highest and lowest price values, but also when that price was reported. This will allow us to look for patterns in the data and figure out if there's a trend when a station has the lowest price of the day. This is sufficient for now, so let's move on to the next step: importing the data and aggregating it into the schema above.

Importing data

Despite price records storing station data, we will keep all station data in a separate collection. This will serve as a canonical record of all stations, and we can use it to look up station data when we need it. We now have a few options for importing data. If I only wanted to import data for a single day, I would leverage MongoDB Atlas' federated databases to read the CSV files directly from Azure DevOps and import them into MongoDB. However, since I want to import lots of historical data, there are more efficient ways to do this. Again, it's important to know the data that we have: We want to store buckets for each day, and the CSV files contain one file per day. This means that it will be efficient to load a single CSV file, pre-aggregate that data into the format that we need, then continue with the next file. Instead of using mongoimport, I chose to write a small Symfony Console command that reads the CSV file into a collection, separating the fuel types and converting data into the right format. After that, the command aggregates the data into buckets and adds them to the DailyPrice collection. MongoDB's aggregation framework is perfect for this task, so it's about time we look at some code.

From a list of prices, we can group them into buckets and find the highest and lowest prices of the day, but to compute the average price, we would need to know the first price of the day. Since stations don't report prices at midnight, we'll need to know the last price of the previous day. So, we'll start out by storing the last price of the day and will shift that to the next document in a separate step. We also need to denormalize station data, so this is one of the few places where we'll use a join. Except for storing the opening price, we can achieve all of this in a single query and store the result in our DailyPrice collection. Here's the aggregation pipeline I built:

return new Pipeline(
    Stage::set(day: Expression::dateTrunc(Expression::dateFieldPath('date'), unit: 'day')),
    self::groupPriceReportsByStationDayFuel(),
    self::reshapeGroupedPriceReports(),
    self::addExtremeValues(),
    self::lookupStation(),
);

Aggregation pipeline builder

Ah, yes, simple and elegant. Of course, I'm hiding all the interesting bits so far. This is the aggregation pipeline builder introduced in version 1.21 of the MongoDB driver. While the Doctrine ODM has had one for quite some time, the builder provided by the driver itself allows us to write our code in a functional style. This is also beneficial for testing. Just by looking at this, we can tell that the pipeline does the following in order:

Groups price reports by station, day, and fuel type
Reshapes the buckets into the document we want
Adds the extreme values (lowest and highest prices)
Looks up station data

Since the first $set stage of the pipeline is the simplest part, let’s take a more detailed look at it:

Stage::set(
    // Truncate the date to the specified unit and store it in a new day field
    day: Expression::dateTrunc(
        // We truncate the date field, which contains the exact time of the price report
        Expression::dateFieldPath('date'),
        // Truncating to the day effectively sets the day to midnight
        unit: 'day',
    ),
),

Behind the scenes, the driver transforms this code to the following pipeline stage:

{
  "$set": {
    "day": {
      "$dateTrunc": {
        "date": "$date",
        "unit": "day"
      }
    }
  }
}

The big advantage of using the builder like this is that we can test each part of the aggregation pipeline in isolation in unit tests to make sure it does what we expect, as we do with other pieces of code. It also makes it much more readable, as we don't have to parse through a huge aggregation pipeline to understand what it does. Now, we can start grouping prices. Of course, we could have done so when reading the CSV, but this would've required us to load all prices into memory. Since we're dealing with large amounts of data, this is not a good idea. Using the aggregation framework allows us to push this workload to the database, where we could even use a sharded cluster to distribute the workload across multiple nodes. So, what does the grouping stage look like?

return Stage::group(
    _id: object(
        station: Expression::fieldPath('station'),
        day: Expression::fieldPath('day'),
        fuel: Expression::fieldPath('fuel'),
    ),
    prices: Accumulator::push(object(
        _id: Expression::fieldPath('_id'),
        date: Expression::fieldPath('date'),
        price: Expression::fieldPath('price'),
    )),
);

The _id field will create a group for each station, day, and fuel. For the prices field, we use the $push accumulator and push all prices into a list. Note that while the _id field is an object, we'll change this further down the pipeline to use a simpler ID that can be indexed more easily. Now, let's build the document that we actually want to store. This can be done with the $replaceWith stage:

return Stage::replaceWith(object(
    day: Expression::fieldPath('_id.day'),
    station: Expression::fieldPath('_id.station'),
    fuel: Expression::fieldPath('_id.fuel'),
    prices: Expression::sortArray(
        Expression::arrayFieldPath('prices'),
        object(date: 1),
    ),
    pricesByPrice: Expression::sortArray(
        Expression::arrayFieldPath('prices'),
        object(price: 1),
    ),
));

We extract the day, station, and fuel from the previous _id field, and we create two price lists here: one sorted by date, the other sorted by price. We can use the first list to find the closing price of the day, and the second list to find the lowest and highest prices of the day in the next stage. We can then also remove the price list sorted by price:

return Stage::set(
    closingPrice: Expression::getField(
        'price',
        Expression::last(Expression::arrayFieldPath('prices')),
    ),
    lowestPrice: Expression::first(Expression::arrayFieldPath('pricesByPrice')),
    highestPrice: Expression::last(Expression::arrayFieldPath('pricesByPrice')),
    pricesByPrice: Expression::variable('REMOVE'),
);

This is where things start getting a bit more complex. The $first and $last operators are pretty self-explanatory, but to only get the closing price of the day, we use the $getField operator to extract the value of the price field from the price document returned by $last. Last but not least, we use the $$REMOVE variable to unset the pricesByPrice field, which saves us from using the $unset pipeline stage later on. Next up, finding the station. This is where aggregation pipelines really shine, as we can use the $lookup stage after grouping and filtering a lot of data to reduce the numbers of documents that need to be processed. In SQL, this would require some nested queries, which can become very difficult to read and understand, but in MongoDB, it is just another stage in the pipeline:

return new Pipeline(
    Stage::lookup(
        as: 'station',
        from: 'Station',
        localField: 'station',
        foreignField: '_id',
        pipeline: new Pipeline(
            Stage::project(
                _id: true,
                name: true,
                brand: true,
                location: true,
                address: true,
            ),
        ),
    ),
    Stage::set(
        station: Expression::first(Expression::arrayFieldPath('station')),
    ),
    Stage::match(station: Query::ne(null)),
);

But what's this? We're returning an entire pipeline from this factory to be used in a pipeline? Yes! When working with aggregation pipelines in the past, I noticed that there are some common tasks that require more than a single stage. Since the $lookup stage returns an array of documents even if only a single document is matched, we use a $set stage directly afterwards to extract the first (and, in our case, only) document from the array. The aggregation pipeline builder knows how to handle this and contcatenates nested pipelines so you never need to worry about this. For good measure, we also drop all documents where we couldn't find a station. We don't expect any such documents to be there, but it's better to be safe than sorry. Also, note that we're using a $project stage within $lookup. This is to ensure we're only copying the relevant station data over. As you'll see below, we're storing some extra data in the station document, which we don't want to duplicate.

I previously mentioned that writing aggregation pipelines like these not only makes them more readable, but also allows us to test them in isolation. Here's one unit test I wrote to test the addExtremeValues stage:

public function testAddExtremeValues(): void
{
    $price1 = ['_id' => 1, 'price' => 2, 'date' => new UTCDateTime(new DateTimeImmutable('2025-06-27T00:00:00+00:00'))];
    $price2 = ['_id' => 2, 'price' => 1, 'date' => new UTCDateTime(new DateTimeImmutable('2025-06-27T01:00:00+00:00'))];
    $price3 = ['_id' => 3, 'price' => 3, 'date' => new UTCDateTime(new DateTimeImmutable('2025-06-27T02:00:00+00:00'))];

    $document = [
        'prices' => [
            $price1,
            $price2,
            $price3,
        ],
        'pricesByPrice' => [
            $price2,
            $price1,
            $price3,
        ],
    ];

    $pipeline = new Pipeline(
        Stage::documents([$document]),
        PriceReport::addExtremeValues(),
    );

    $result = iterator_to_array(
        $this
            ->getTestDatabase()
            ->aggregate($pipeline, ['typeMap' => self::TYPEMAP]),
    );

    self::assertCount(1, $result);
    $result = $result[0];
    self::assertEquals(
        (object) [
            'prices' => [
                $price1,
                $price2,
                $price3,
            ],
            'closingPrice' => 3,
            'lowestPrice' => $price2,
            'highestPrice' => $price3,
        ],
        $result,
    );
}

We can leverage the $documents stage to create a pipeline that starts with a set of documents of our choosing, then insert the pipeline we want to test. This way, we don't have to worry about setting up a collection with test data and removing it again. The downside is that MongoDB won't be able to optimize the pipeline for index use, but since we're only testing small workloads, this is not a problem for us.

Initial import pipeline

Now that we have our pipeline, we can run it against the temporary import collection and store the results in the DailyPrice collection. This is easy enough, as we can use the $merge stage to write the results to that collection:

return new Pipeline(
    Stage::set(day: Expression::dateTrunc(Expression::dateFieldPath('date'), unit: 'day')),
    self::groupPriceReportsByStationDayFuel(),
    self::reshapeGroupedPriceReports(),
    self::addExtremeValues(),
    self::lookupStation(),
    Stage::merge('DailyPrice'),
);

The $merge stage is extremely powerful, as it allows us to specify the behavior when a record with the given identifier already exists. In our case, we're always inserting new data, but we could also handle the case of partial data being there (e.g., when a bucket already exists) and make sure to merge the price lists and run the above aggregation steps on the merged list. With the full import pipeline completed, we can now show why the aggregation builder is so useful. Without it, this is what the entire pipeline looks like:

[
    [
        '$set' => [
            'day' => [
                '$dateTrunc' => [
                    'date' => '$date',
                    'unit' => 'day',
                ],
            ],
        ],
    ],
    [
        '$group' => [
            '_id' => [
                'station' => '$station',
                'day' => '$day',
                'fuel' => '$fuel',
            ],
            'prices' => [
                '$push' => [
                    '_id' => '$_id',
                    'date' => '$date',
                    'price' => '$price',
                ],
            ],
        ],
    ],
    [
        '$replaceWith' => [
            'day' => '$_id.day',
            'station' => '$_id.station',
            'fuel' => '$_id.fuel',
            'prices' => [
                '$sortArray' => [
                    'input' => '$prices',
                    'sortBy' => [
                        'date' => 1,
                    ],
                ],
            ],
            'pricesByPrice' => [
                '$sortArray' => [
                    'input' => '$prices',
                    'sortBy' => [
                        'price' => 1,
                    ],
                ],
            ],
        ],
    ],
    [
        '$set' => [
            'closingPrice' => [
                '$getField' => [
                    'field' => 'price',
                    'input' => [
                        '$last' => '$prices',
                    ],
                ],
            ],
            'lowestPrice' => [
                '$first' => '$pricesByPrice',
            ],
            'highestPrice' => [
                '$last' => '$pricesByPrice',
            ],
            'pricesByPrice' => '$$REMOVE',
        ],
    ],
    [
        '$lookup' => [
            'as' => 'station',
            'from' => 'Station',
            'localField' => 'station',
            'foreignField' => '_id',
            'pipeline' => [
                [
                    '$project' => [
                        '_id' => true,
                        'name' => true,
                        'brand' => true,
                        'location' => true,
                        'address' => true,
                    ],
                ],
            ],
        ],
    ],
    [
        '$set' => [
            'station' => [
                '$first' => '$station',
            ],
        ],
    ],
    [
        '$match' => [
            'station' => [
                '$ne' => null,
            ],
        ],
    ],
    [
        '$merge' => [
            'into' => 'DailyPrice',
        ],
    ],
]

Sure, we could extract some parts into their own methods like we did before, but this still requires us to know about all stages, their syntax, and the types they accept. With the aggregation builder, we can leverage our IDE to provide auto-completion and type hints, making aggregation pipelines much easier to write and maintain.

Post-import aggregation

Of course, getting the data into MongoDB was just the first step. We're still missing the opening price of the day, which we couldn't compute previously. With that, we can also compute the weighted average price of the day. I will spare you all the dirty details, but the aggregation framework allows us to use the $setWindowFields stage to access a field from a different document in the pipeline:

return Stage::setWindowFields(
    partitionBy: object(
        station: Expression::fieldPath('station._id'),
        fuel: Expression::fieldPath('fuel'),
    ),
    sortBy: object(day: 1),
    output: object(
        openingPrice: Accumulator::shift(
            output: Expression::fieldPath('closingPrice'),
            by: -1,
            default: Expression::variable('REMOVE'),
        ),
    ),
);

In this case, I want to partition by the station and fuel, then sort by the day. I then want to add an openingPrice field using the $shift accumulator with a shift of -1, which means that I want to access the previous document in the partition. If there is no previous document, we are again using the special $$REMOVE variable to completely remove the field from the document. It may only save us 15 bytes, but when we're talking about around 18 million buckets per year, these 15 bytes still add up to around 270 MB of storage space. Speaking of saving storage space, another pattern is shortening field names. For example, instead of using openingPrice, we could use o as a key. I chose not to do so in this tutorial, but this is a valid solution to reduce document size. Using the Doctrine MongoDB ODM, we could still map these fields to more readable properties in our document classes, so we wouldn't even have to worry about this in the application code.

Running the import

With all the aggregation pipeline written and tested on a small scale, it's now time to make our database server do some work. For maximum performance, I decided to run the import on a local MongoDB instance. This allows me to bypass the network latency and leverage the fast SSD storage on my machine. Here's the output of the import command, where I imported data for the years 2022 and 2023:

Import took 178m 51.6s: 582,944,678 documents inserted, 0 updated.
Aggregating imported price reports for further processing...Done in 122m 26.0s.
Adding opening price for each day and merge into prices collection...Done in 169m 49.2s.
Recomputing daily aggregates...Done in 43m 27.9s.
Setting last price report for stations...Done in 185m 42.0s.

Data aggregation completed in 11h 40m 16.8s.

Almost 12 hours. That's quite a long time, but considering that we've imported more than half a billion documents, this is actually not too bad. The first step is impressive: We read just under 583 million price reports from the CSV files and inserted them into the import collection in under three hours. That works out to an average of more than 54,000 documents per second. From previous testing, I know that the bottleneck here is disk I/O when reading the CSV files due to "endpoint protection software" installed on the machine. The actual write command to MongoDB was able to handle around 300,000 documents per second. That's quite the impressive performance, if you ask me!

The next steps then took up the remainder of the time, although I will admit that I didn't optimize the pipeline for performance. This isn't something I'll be doing on the regular, and I'm sure that with some better index use, I would've been able to improve this. Since I simplified the station schema in the beginning and didn't explain the full pipeline for importing the stations, here's what we store in the database for a station:

{
  "_id": {
    "$binary": {
      "base64": "DhjQ0+04Tn+hjlB6eK2QHQ==",
      "subType": "04"
    }
  },
  "name": "Oil! Tankstelle München",
  "brand": "OIL!",
  "address": {
    "street": "Eversbuschstraße 33",
    "houseNumber": "",
    "postCode": "80999",
    "city": "München"
  },
  "location": {
    "type": "Point",
    "coordinates": [
      11.4609,
      48.1807
    ]
  },
  "latestPrice": {
    "diesel": {},
    "e10": {},
    "e5": {}
  },
  "latestPrices": {
    "diesel": [],
    "e10": [],
    "e5": []
  }
}

The latestPrice field contains the latest DailyPrice document for each fuel type, while the latestPrices field contains the DailyPrice documents for the last 30 days. Why, you might ask? Well, in order to show station details, we now only need to find the one station among 17,000 stations that we're interested in, and we have all the data we need to show the latest price, the price history for the day, and even a chart with the lowest, highest, and average prices for the last 30 days, all without having to query our huge DailyPrice collection. This is a great example of how MongoDB's document model allows us to optimize our data for the queries we want to run. And yes, we can still update this data pretty easily thanks to the power of MongoDB's query language, but let's save that for now.

Each DailyPrice document embedded into a station is exactly the same document we store in the DailyPrice collection. This allows us to reuse our Doctrine document classes when we build our application.

I also decided to store a DailyAggregate document for each day, which contains some aggregates for each fuel type and day. We can use this to compare the price at a particular station against averages across all stations:

{
  "_id": {
    "$oid": "673fd3903de33b548f44d4a9"
  },
  "day": {
    "$date": "2024-06-29T00:00:00.000Z"
  },
  "fuel": "e5",
  "numChanges": 22.3579504275982,
  "lowestPrice": 1.678,
  "highestPrice": 3,
  "weightedAveragePrice": 1.8388233077257572,
  "percentiles": {
    "p50": 1.8306483516483516,
    "p90": 1.869,
    "p95": 1.883,
    "p99": 2.312
  }
}

Now that we have our data in MongoDB, let's take a look at the size of our collections and how much data we actually stored:

The Station collection contains around 17,000 stations, totalling just over 1 GB of data.
The DailyPrice collection contains around 28 million documents (two full years worth of data), with 24 GB of data and a whopping 2.5 GB of indexes.
These 28 million bucket documents contain around 600 million individual prices, so we've successfully reduced the number of documents by a factor of 20.
Our DailyAggregates are just over 2,000 documents, coming in at 180 KB.

In the next part, we'll start building our Symfony application to work with this data. We'll use the Doctrine MongoDB ODM to map our documents to PHP classes, and we'll use Symfony UX to build a modern frontend that allows us to interact with the data. The goal is to show how we can efficiently handle this amount of data in a Symfony application, while still providing a good user experience.

Building an Archaeology Matcher: A (Literal) Deep Dive Into Multimodal Vector Search

Rishabh Bisht — Fri, 12 Dec 2025 14:17:25 +0000

The MongoDB team built a visual similarity search app for SymfonyCon. This article lays out how to build the app using Voyage AI multimodal embedding, MongoDB Atlas, and SymfonyAI.

This article was written by Pauline Vos.

What you’ll learn

What vector search and embedding models are.
How to use MongoDB to store embeddings and create vector search indexes.
How Symfony AI can help enhance your applications with AI.

SymfonyCon 2025 is behind us, and MongoDB's PHP team is still basking in the afterglow of so many great talks and conversations at our booth. After six years, SymfonyCon returned to my home town: Amsterdam—a major European city with a rich history—and this year marks its 750-year jubilee. What better way to represent ourselves than to show off a bit of that history and our powerful vector search capabilities?

First, some context

(Or watch the video.)

If you're ever in Amsterdam, I recommend making a stop at the Rokin metro station. One of the escalators serves as a sort of history museum: It displays a curation of archaeological finds collected during the construction of metro line 52.

Not planning to come over anytime soon? Lucky for you, many of the ~140,000 recorded objects were cataloged and published on Below the Surface. I've adored this website since its launch in 2017, as it lets users explore a timeline of objects ranging from the early 2000s to over a hundred thousand years ago. Not only that, it offers all the data in a downloadable CSV file to use freely—and don't we love some good data at MongoDB?

The Symfony app we built

So, how to wrangle all this data? Using MongoDB Atlas Vector Search, Voyage AI's multimodal capabilities, and Symfony AI, I built a little Symfony app that allows you to submit a description or image of an object, and finds the most similar object from the depths of Amsterdam's history! Pretty neat, right?

Go on, try it out (we do not store your image).

Scan if you're not on your phone

You can also explore the repo on GitHub.

How the app works

A common use of AI is similarity search. This technique uses an embedding model to take data and turn it into a vector embedding—a multi-dimensional array that records many different dimensions of said data. For instance, given one image of a parrot and one of a bicycle, a vector may record features like:


Animal.	Vehicle.
Has 2 wings.	Has 2 wheels.
Has beak.	Has basket.
Flies.	Rolls.
Jungle.	Urban.
Mostly red.	Mostly brown.

The number of data points recorded correlates to the number of dimensions the vector has, which is often several thousand.

Using a similarity algorithm, we can now compare vectors to see which are mathematically most similar. This is called a vector search. Given a vector store containing embeddings for the parrot and the bicycle, we can generate another embedding for, say, an image of a duck. When we perform a vector search using the duck's vector embedding, it's statistically most likely the top search result will be a parrot.

That's vector search in a nutshell! Different types of data may be used for this type of search. For instance, a blog enhanced with semantic search may embed text. A reverse image search feature will use image embeddings. In our demo use case, we use multiple data types (text from the CSV file, and images from the website), which is called "multimodal embedding."

Not all embedding models support every data type. Also, different models may have different "opinions" of what constitutes similarity. This can be further affected by the specific purpose a model is trained for. All are important considerations when choosing the right model for your data and use case!

How it was built using MongoDB, Symfony, and Voyage AI

There are two parts to this app: indexing the data, and letting users perform a vector search.

Indexing

SymfonyAI's Store component offers an Indexer that handles indexing. It will:

Load data from a given source (Loader).
Generate vector embeddings (Vectorizer).
Store the embeddings (Store).

1. Load data

First, we can create an implementation of LoaderInterface to load all the artifacts' text and image data from the CSV dataset and the website respectively, to prepare for embedding. For the embedding input, I decided on:

A concatenated string made up of all the text columns in the CSV (describing the object's function, material, date range, and more).
The object's image(s), which is one or two depending on the object.

Creating this sort of multimodal embedding will improve the similarity search by having both visual data and descriptions to go on for comparing the artifacts. Were we to only embed the images, for example, we wouldn't have data points for things like country of origin or date range.

2. Create vector embeddings

Now we've gathered our inputs for our embedding model (voyage-multimodal-3), we can pass them to Vectorizer. To do this, we define a Voyage Platform service, which we can inject into Vectorizer along with the model we want to use:

# config/packages/ai.yaml
ai:
    platform:
        voyage:
            api_key: '%env(VOYAGE_API_KEY)%'
    vectorizer:
        voyage:
            platform: 'ai.platform.voyage'
            model: 'voyage-multimodal-3'

The Vectorizer will handle sending all the inputs to Voyage AI (in batches), and return the resulting vector embeddings for storage.

3. Store the embeddings

Now that we have our vectors, we can store them in MongoDB.

Note: Before doing this, we have to create vector search indexes in our database. Since we're using Doctrine ODM entities with the VectorSearchIndex attribute, that's a matter of running bin/console doctrine:mongodb:schema:create.

While SymfonyAI comes with a MongoDB Store, for this app, I made our own Doctrine ODM implementation of StoreInterface. I hope to contribute one to the Store component soon to make it easier to use Doctrine entities with SymfonyAI.

When we wire up the Indexer with these three services, it'll pass the data through each of them to complete the indexing process.

# config/packages/ai.yaml
ai:

    # ...

    indexer:
        artifacts:
            loader: 'App\Service\ArtefactLoader'
            vectorizer: 'ai.vectorizer.voyage'
            store: 'App\Store\DoctrineODMStore'

This configuration now enables us to run bin/console ai:store:index artifacts, by the end of which all our artifacts will have multimodal embeddings stored in MongoDB Atlas.

Performing the vector search

With our embedding data and search indexes set up, we're ready to start finding matches.

1. Submit photo and/or prompt

In the UI, the user enters a description of what they're trying to find, takes a photo of an object, or both. For instance, a user may take a photo of a toothbrush and enter "medieval" in the text field to see if people in the Middle Ages used similar objects.

2. Generate image description

If there's an image, we use one of OpenAI's GPT models to generate a description of it to embed alongside the image data. We'll need to define another Platform service for OpenAI, which we can inject into our query handler.

# config/packages/ai.yaml
ai:
    platform:
        voyage:
            api_key: '%env(VOYAGE_API_KEY)%'
        openai:
            api_key: '%env(OPENAI_API_KEY)%'

# config/services.yaml
    App\Query\MatchQueryHandler:
        arguments:
            $openAi: '@ai.platform.openai'

You may wonder why I chose to generate a description of an image. After all, don't we already have an embedding model recording data from that same image? The main reason is that we can send instructions to GPT to only focus on the object in the photo and ignore any background, which would only serve as irrelevant noise in our similarity search. We can also ask it to focus specifically on features recorded in the CSV dataset (things like function and finish).

This can add more weight to data points that we're especially interested in comparing, and may improve our results. It's definitely trial and error, so I recommend playing around with different messages to see how they change results.

For our app, we're sending the following to GPT:

$messages = new MessageBag(
    Message::forSystem('You are an image analyzer bot that helps identify objects in images.'),
    Message::ofUser(
        'Describe the object in the foreground, ignoring the background or the person holding it. Try to focus primarily on the function. Color, shape, material, and finish of the object may also be included.',
        $image
    ),
);

3. Create vector embedding

Armed with our text description and image data, we can generate another multimodal embedding by sending it all off to Voyage AI. The resulting vector can then be used as a search query for a vector search!

4. Perform vector search

For our final trick, we use the Doctrine ODM aggregation builder to execute a vector search aggregation:

$builder = $this->documentManager->getRepository(Artefact::class)
    ->createAggregationBuilder()
    ->hydrate(MatchCandidate::class);

$builder
    ->vectorSearch()
    ->limit(10)
    ->numCandidates(200)
    ->index('default')
    ->path('embeddingVector')
    ->queryVector($vectorized->vector->getData())
    ->project()
    ->field('_id')->expression(0)
    ->field('artefact')->expression('$$ROOT')
    ->field('score')->meta('vectorSearchScore');

$matches = $builder
    ->getAggregation()
    ->execute();

This aggregation will return the 10 best matches it could find, including their scores. By default, they're ordered by score (descending), so the first match will be the most similar.

Et voilà! Pass them on to a template, and the user is presented with a cool object from Amsterdam's history and some other close matches. For example, this picture of the ring my stepdad made me when I was a kid yields the following results:

Next steps

Want to see more? Try out the app! You can also build it yourself using SymfonyAI and MongoDB.

Key takeaways

Generate vector embeddings and store them in MongoDB Atlas to implement similarity search.
Combine different types of data in multimodal embeddings to enhance search results.
Symfony AI can help you tie your store and platform together to easily index and embed your data.

Building With Symfony: Scaling Surfshark’s Backend

Rishabh Bisht — Mon, 28 Jul 2025 13:00:00 +0000

Curious about how leading companies leverage Symfony to build and scale their platforms? In the new Building With Symfony article series, we dive into the tech stacks of innovative organizations and share first-hand insights from their engineering teams. Discover the key tools, architecture choices, and best practices that help them deliver reliable, high-performance applications at scale.

This article was contributed by Vaidas Lažauskas, the Head of Backend Engineering at Surfshark.

Launched in 2018, Surfshark is a prominent cybersecurity company that provides a range of online privacy and security solutions. Best known for its VPN service, Surfshark has a team of over 500 employees spread globally and has been honored with more than 30 international awards. The company's mission is to build the most beloved security products for everyone. Surfshark has over 35 million application downloads, supported by more than 3,200 servers in 100 countries.

Architectural overview

Surfshark processes over 5,000 API requests per second! It leverages a robust backend that utilizes the Symfony framework with MongoDB as its primary database. Their system is designed to handle operations at a massive scale—the largest MongoDB collection has over 45 million documents! The back end is fully structured around microservices architecture. In fact, Surfshark has never used a monolith; from the very beginning, all services have been built as microservices and all the infrastructure is deployed on AWS.

The Symfony + MongoDB stack

Surfshark’s backend is built with PHP and the Symfony framework. Since the very beginning (over seven years ago), they've integrated MongoDB using Doctrine ODM, making full use of its query builder for application development. All new services are built on this stack, with plans underway to retire MySQL entirely. The Surfshark team has also contributed pull requests to Doctrine ODM in the past and actively supports the Symfony community. Surfshark is committed to staying up-to-date with their dependencies, upgrading major versions annually and keeping libraries fresh every week. They've recently migrated from MongoDB 7 to 8, with a smooth, zero-downtime upgrade that required no application-level changes—a testament to the robustness of their integration and the power of MongoDB Atlas.

“We use Compass. It’s a really great tool for entry level developers for index management and analyzing collections.”

Development at Surfshark is fully Dockerized using Docker Compose, with Mutagen syncing local files into the Docker environment for a seamless workflow. Developers predominantly use PHPStorm and work with MongoDB running in Docker containers during local development. For database management and analytics, the team enjoys a variety of tools:

MongoDB Compass: Excellent for entry-level developers and for index/collection analytics
MongoDB Shell: Preferred for flexible scripting and rapid prototyping

Overall thoughts on working with Symfony and MongoDB

When Vaidas joined Surfshark, the backend relied heavily on MySQL—with multiple services built on top of it. However, as the platform scaled and handled thousands of requests per second, it experienced interruptions and errors under load. To solve this, Vaidas introduced MongoDB, and all new services immediately started using it as the primary database. Today, about 90% of Surfshark’s data is housed in MongoDB.

“The main thing every new developer notices is that there is no migration schema madness. We still have MySQL for some legacy services, so we need to run migrations, using Percona toolkit, whenever we have to add a new field to the schema. We get thousands of requests per second. Just renaming the table will cause us to lose user requests because of errors that might appear while migrating. The new devs are amazed that there is no fixed schema with MongoDB. You make a change and just push to production.”

They also appreciate that schema and index changes can be performed without interrupting service, which is crucial for a platform serving thousands of requests per second.

“Creating an index is also seamless. You pass the new index and it just works without any interruption.”

Expiring data like auth tokens is easy and robust. They found MongoDB’s TTL handling superior to other solutions they have used, as the data is truly physically removed on schedule.

“TTL index is also a great feature. Many other databases have this feature but it works so much better in MongoDB because the data is always physically deleted right away.”

Surfshark’s story shows how Symfony and MongoDB can work together to power a fast-growing, reliable backend. By focusing on flexibility, easy scaling, and solid developer tools, they’ve built a system ready for millions of users. If you’d like to learn more about modern backend architecture or see how other companies use Symfony in production, stay tuned for the next articles in this series. Curious about integrating Symfony and MongoDB in your own projects? Check out our Symfony MongoDB integration to get started!

Best Database for C++ Development

Rishabh Bisht — Thu, 12 Jan 2023 19:32:46 +0000

C++, TIOBE’s most popular language for 2022, is a powerful & high-performance programming language that is widely used for a variety of different purposes. One area where C++ is particularly useful is in the development of database-driven applications.

Are you wondering what is the best database for C++ application you are building? In this article, we will take a look at the databases used by C++ developers. The source of the data is Stack Overflow survey 2022 raw data, filtered for C++ developers. There were over 70,000 developers who responded to this survey, and 16,024 of those had experience working with C++.

So, which database do C++ developers use?

MySQL (44.2%) and PostgreSQL (34.2%)
MySQL and PostgreSQL are open-source relational database management systems, which organize the data into tables with rows and columns. C++ developers can interact with these databases using libraries such as MySQL Connector/C++ and libpqxx (PostgreSQL C++ library).

SQLite (37.4%)
SQLite is a small, lightweight, and self-contained SQL database engine that can be embedded in applications and used to store and retrieve data. C++ developers can use SQLite C++ library such as sqlite3pp, SQLiteCpp to work with SQLite.

MongoDB (26.9%)
MongoDB is the most popular NoSQL database used by C++ developers based on the survey results.It is a document based database, designed to handle large amounts of unstructured data and can handle a very high volume of read and write operations with a wide variety of use cases. C++ developers can interact with MongoDB by using the MongoDB C++ Driver.

Which database do C++ developers want to use?

Stackoverflow survey also reports data on the database that C++ developers want to use. Here we can see PostgreSQL and MongoDB climbing up the ladder, but overall the result is similar.

PostgreSQL (30.0 %)
SQLite (25.1%)
MongoDB (23.96%)
MySQL (23.66%)