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

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.

Comments

[Harsh]

Really impressive breakdown of scaling with MongoDB in Symfony! The part about using Doctrine MongoDB ODM to map documents to PHP classes is super clean. One question — when dealing with a billion documents, how do you handle index management over time? Do indexes start to affect write performance significantly at that scale, or does MongoDB handle it gracefully?