As the developed system matured we found new challenges in our push toward an event-driven architecture and OLAP powered features. We want to talk about these challenges, specifically the ones that we overcome by using PostgreSQL to - not only serve the OLAP data - but also to do the required transformations of the sourced data, in near real-time.

The next chapter​

After we sorted our troubles managing PostgreSQL logical replication slot consumption state, our CDC of postgres events started to work reliably. We even used it a few times to debug data issues in situations where the transactional data was overwritten. And we found no data inconsistency since, and it was time to focus on what came next.

Today almost all our transactional databases have our logical replication client attached to them and are streaming data to our data-event layer, our CDC. The data sourced from our transactional databases is then used for the most varied ends. This data source segregation also proved useful to ensure system reliability.

And we started to develop analytic features on top of it. These go from generalist data aggregations - where we sum up time - to more complex reporting, evolving heavier data transformations. The ability to spin off new databases - OLAP or not - and keep them up-to-date with transactional data brought speed and independence to teams across our engineering department.

We also have been extending its applications for other purposes such as decreasing the lag of business intelligence tooling, for instance.

Data pipelines​

In practice, we create subscriptions in our messaging service behind the CDC pipeline, and with each new subscription, the service attached to it can keep the respective database in sync with our transactional databases, creating independent ETL pipelines. This allows us to advance the development of non-core parts of the system independently. But, of course, also brings challenges when things on our transactional database change.

When we started to develop our transformations, we wanted to move data from our monolith transaction database, but in parallel to that other efforts have been made to decouple domains from our main service. Such as subscriptions or authentication. And… a new authorization layer.

As a result, some new projects needed to tap into data sourced from different transactional databases. From different sources of truth. That drove the need to source data from the different databases into our CDC and resulting ETL pipelines. All data ends up traveling in the same data backbone and can be accessed, and managed by mostly reusing the same go package.

PostgreSQL logical replication was just the very beginning of the journey, each blue box represents a different deployment of our replication consumption client, listening to a different replication stream, from a different database, and making that data generally available in the CDC pipeline.

Authorization system​

Our new authorization layer is a project using the CDC. That stands out unexpectedly but has more to it.

Toggl Track had a limited and hard-coded system of roles. It's more than that, but we can sum it up to whether you are an admin or not. Replacing the entire authorization layer was a major project, as most of these rules are enforced at the database layer. On top of that, we have been decoupling domains from our main service, which means that data to assess authorization rules was now coming from different sources of truth, such as the old monolithic database where entity settings are stored, our new subscription service, and the new authorization database where we were planning to store roles, permissions and their relations to users.

However, in addition to providing more role flexibility, we set out to deal with performance bottlenecks while resolving authorization rules for large organizations with tens of thousands of projects and users. Authorization alone was taking a considerable amount of response time for reporting features, for instance.

The solution that we came up with was computing a data object with all the needed information to solve authorization. But, as we didn't want to increase sign-in time, we needed to generate that object pre-session. While looking for ways to achieve this, our newly developed CDC pipeline couldn't have come at a better time.

This means that while projects are added to a workspace, the user sessions belonging to that workspace are being precomputed on our authorization OLAP database through the ETL process that is sourcing data available on the CDC from the different transactional databases.

Of course, you don't want to wait forever for your newly created project to be usable! These sessions have to be updated fast, near real-time. Doing these transformations fast, over a large and constant stream of data highlighted some... light DB tips.

Tips & Tricks​

Handling duplication​

While properly managing the state of our logical replication slot fully prevents data loss, there is still the possibility of data duplication, on plug-off events, for instance.

We embraced the duplication possibilities from the get-go, which turned out to be an advantage. Our CDC carries the LSN from the logical replication event that generated a given data event, and we use it as an identifier for simpler data aggregations, while for more complex ones we make use of updated timestamps to filter out unnecessary data changes, which prevents duplicated operations.

The updated_at is required in all our transactional tables, and its update is enforced through triggers.

Overall, the operations on our OLAP transformation end up atomic. Different data events resulting from the same transaction will become atomic in our data stream. And we strive for idempotence, either using the data identifier or the updated timestamps, processing the same event multiple times will need to generate the same result. Ideally processing events out of order should also produce the same result.

Bulk first​

Large transactions can easily generate thousands of data events. Processing these events individually in OLAP would be challenging. Bulking their ingestion and processing was a key factor in dealing with large volumes of data in near real-time.

INSERT INTO transformation (
  user_id,
  total_time,
  updated_at
)
SELECT
  (d->>'user_id')::int AS user_id,
  (d->>'total_time')::int AS total_time,
  (d->>'updated_at')::timestamp without time zone AS updated_at
FROM JSON_ARRAY_ELEMENTS($1::jsonb) d
ON CONFLICT ON CONSTRAINT transformation_pkey
DO UPDATE SET
  total_time = excluded.total_time,
  updated_at = excluded.updated_at
WHERE
  transformation.updated_at <
    excluded.updated_at;

Data ingestion will vary from transformation to transformation, but if we were to draw a baseline, it would look like this. We feed to postgres a single parameter, which is a json array string. We then use json array functions from postgres to turn that data into table records, and we always try to insert them first. Then, we make use of conflict constraints to decide if we should update the existing data or skip it. The where clause in the query above is an application example of the updated_at fields that we talked about above.

Dealing with low-level data models​

Our data events on the CDC pipeline contain only the information of the affected table row. The data event schema matches the table definition at the time that it was generated.

This low-level data structure is most of the time incomplete for what we need to do. Sometimes we need data from other tables, or relation keys that only exist in the parent entity, this is normal as our source is normalized transactional data.

We found that the optimal way to deal with missing data on individual data events is to keep a small version of the entities, that we need to relate to our OLAP database. It's something like a cache, updated by the ETL process itself. And through triggers, we use these updates to populate our final OLAP model.

Statement triggers​

Similarly to defaulting to bulking, we should be aware of what kind of triggers we define. If we define row-level triggers, the transformation query will run once for each row changed by our data update, while if we use statement triggers, we execute it only once.

Of course, this will also imply supporting it in the trigger function.

CREATE TRIGGER after_update AFTER UPDATE ON transformation
  REFERENCING NEW TABLE AS new_table OLD TABLE AS old_table
  FOR EACH STATEMENT EXECUTE FUNCTION olap.update_team_goals();

CREATE OR REPLACE FUNCTION olap.update_team_goals() RETURNS trigger LANGUAGE plpgsql AS $$
  BEGIN
    WITH changes AS (
 SELECT t.team_id, SUM(new_table.total_time - COALESCE(old_table.total_time, 0)) AS total_time
 FROM relations.user_teams t
 JOIN new_table ON t.user_id = new_table.user_id
 LEFT JOIN old_table ON new_table.user_id = old_table.user_id
 GROUP BY t.team_id
    )
    UPDATE olap.team_goals tg SET total_time = total_time + changes.total_time
    FROM changes WHERE tg.team_id = changes.team_id;


    RETURN NULL;
  END;
$$;

In the code above you can find a practical example of the process. We define a trigger over a transformation table, calling the olap.update_team_goals function, referencing new and old data tables. This call is for EACH STATEMENT.

If we look at the function, these reference tables have only the user_id and total_time, however, as the function name states, we want to update the team goals. As so, we must find out the team_id for the teams to which each user belongs, and that's done in the CTE by cross-checking the new data table with the relations.user_teams existing on our OLAP database and kept up to date by the ETL pipeline. We join it with the new and old tables - as we want to find the time difference added by the new change - and we use the results to update our final transformation in the update query below.

Inclusive Indexes​

In this way, we end up using smaller versions of the transactional entity on our OLAP database, and because of that many of our table searches are for a limited amount of data. By making use of inclusive indexes, we can save a trip to the table, at run time, to retrieve the bit of information that we are looking for because it will be part of the index.

This can cut execution time in half very easily.

CREATE SCHEMA relations;
CREATE TABLE relations.user_teams (
  user_id integer NOT NULL,
 team_id integer NOT NULL,
  CONSTRAINT user_teams_pkey PRIMARY KEY (user_id, team_id)
);

INSERT INTO relations.user_teams (user_id, team_id)
SELECT (random() * 10000)::int AS user_id, (random() * 1000)::int AS team_id
FROM generate_series(1, 1000000)
GROUP BY 1, 2;

SELECT user_id, COUNT(1) FROM relations.user_teams GROUP BY user_id ORDER BY 2 DESC LIMIT 1;

CREATE INDEX simple_user_index ON relations.user_teams (user_id);
EXPLAIN (ANALYSE, VERBOSE) SELECT team_id FROM relations.user_teams WHERE user_id = 1560;

CREATE INDEX inclusive_user_index ON relations.user_teams (user_id) INCLUDE (team_id);
EXPLAIN (ANALYSE, VERBOSE) SELECT team_id FROM relations.user_teams WHERE user_id = 1560;

In the runnable sample above we create our acquainted relations.user_teams table, with a user_id and a team_id columns, making them our composite primary key. We then generate some sample data, and we search for the user with the most teams - to get the most meaningful results. We create a simple index over our user_id, our search condition. Run an explain, and create an inclusive index, where we include the team_id. Note that team_id is not searchable and will not be used as an index condition, but it's usable as part of the index search.

Looking at the results:

Index Scan using simple_user_index on relations.user_teams  (cost=0.42..4.29 rows=95 width=4) (actual time=12.193..12.220 rows=128 loops=1)
  Output: team_id
  Index Cond: (user_teams.user_id = 1560)
Planning Time: 0.116 ms
Execution Time: 12.253 ms

In the first explain, we see our simple index being applied. However, in the second:

Index Only Scan using inclusive_user_index on relations.user_teams  (cost=0.42..3.19 rows=95 width=4) (actual time=0.077..0.089 rows=128 loops=1)
  Output: team_id
  Index Cond: (user_teams.user_id = 1560)
  Heap Fetches: 0
Planning Time: 0.129 ms
Execution Time: 0.112 ms

We end up with an execution time of about 1/10th of the original time. We have "Heap Fetches: 0", which means, we never used the table data to fetch results from.

Generated columns​

Another trick has to do with unstructured data on source transactional databases. We can find ourselves in need of retrieving data from a JSON structure to build our transformation on. That data may be easily accessible through the application layer, but in OLAP may be challenging to access due to the atomicity of the sourced data events.

If we create generated columns from such data, sure, it will take a little longer to write operations, but we can index it. This is especially important if we are not searching for a specific value within the JSON field where GIN indexes are useful.

CREATE SCHEMA olap;
CREATE TABLE olap.unstructured (
 dump jsonb NOT NULL
);

INSERT INTO olap.unstructured (dump)
SELECT JSONB_BUILD_OBJECT('user_id', (random() * 10000)::int, 'total_time', (random() * 1000)::int, 'goal', (random() * 1000)::int + 1) FROM generate_series(1, 100000000);

CREATE INDEX unstructured_gin_index ON olap.unstructured USING GIN (dump);

EXPLAIN (ANALYSE, VERBOSE) SELECT COUNT(1) FROM olap.unstructured
WHERE (dump->>'total_time')::int >= (dump->>'goal')::int;

ALTER TABLE olap.unstructured ADD COLUMN goal_completed boolean
GENERATED ALWAYS AS ((dump->>'total_time')::int >= (dump->>'goal')::int) STORED;
CREATE INDEX goal_completed_index ON olap.unstructured (goal_completed);

EXPLAIN (ANALYSE, VERBOSE) SELECT COUNT(1) FROM olap.unstructured WHERE goal_completed;

This time we create an olap.unstructured table with a single JSON field (don't do this in real life). We then generate some sample data, basically JSON objects with 3 fields: random user IDs, total times, and goals. We create a GIN index, which we will see that it's useless for our case, and then run an explain on a query that is counting the number of users that fulfilled their goal. After that, we create a generated column with that same condition and index it.

EXPLAIN (ANALYSE, VERBOSE) SELECT COUNT(1) FROM olap.unstructured
WHERE (dump->>'total_time')::int >= (dump->>'goal')::int;
—--------------------------------------------------------------------------------------------------
Finalize Aggregate  (cost=2292731.42..2292731.43 rows=1 width=8) (actual time=74036.280..74047.599 rows=1 loops=1)
  Output: count(1)
  ->  Gather  (cost=2292731.00..2292731.41 rows=4 width=8) (actual time=74036.072..74047.584 rows=5 loops=1)
        Output: (PARTIAL count(1))...
        ->  Partial Aggregate  (cost=2291731.00..2291731.01 rows=1 width=8) (actual time=74029.976..74029.977 rows=1 loops=5)
              Output: PARTIAL count(1)...
              ->  Parallel Seq Scan on olap.unstructured  (cost=0.00..2270481.00 rows=8500000 width=0) (actual time=0.477..73068.822 rows=10190650 loops=5)
                    Filter: (((unstructured.dump ->> 'total_time'::text))::integer >= ((unstructured.dump ->> 'goal'::text))::integer)
                    Rows Removed by Filter: 10209350...
Planning Time: 10.810 ms
Execution Time: 74048.038 ms

Looking at the query plan for the first query we see that we actually end up with a sequential scan, and quite a bit of execution time. This happens because in this case, we are not searching for a specific value within the json object, where GIN indexes would help. Instead, that index is not used here because we are comparing properties within the json object itself.

EXPLAIN (ANALYSE, VERBOSE) SELECT COUNT(1) FROM olap.unstructured WHERE goal_completed;
—--------------------------------------------------------------------------------------------------
Finalize Aggregate  (cost=692971.65..692971.66 rows=1 width=8) (actual time=3173.567..3195.634 rows=1 loops=1)
  Output: count(1)
  ->  Gather  (cost=692971.23..692971.64 rows=4 width=8) (actual time=3173.488..3195.625 rows=5 loops=1)
        Output: (PARTIAL count(1))...
        ->  Partial Aggregate  (cost=691971.23..691971.24 rows=1 width=8) (actual time=3165.396..3165.397 rows=1 loops=5)
              Output: PARTIAL count(1)...
              ->  Parallel Index Only Scan using goal_completed_index on olap.unstructured  (cost=0.57..660276.85 rows=12677752 width=0) (actual time=0.161..2484.704 rows=10190650 loops=5)
                    Output: goal_completed
                    Index Cond: (unstructured.goal_completed = true)
                    Heap Fetches: 0...
Planning Time: 0.234 ms
Execution Time: 3195.690 ms

Our second query shows a considerable improvement in execution time because it makes use of the index, created over the generated field. Bonus: Note that there are 0 heap fetches. We are using COUNT(1), and because our operation doesn't need any more data, it can use the index alone. If we were to push execution to go back to the table, such as by using COUNT(*) that wouldn't be the case; that would degrade response time.

JSONB​

On our authorization transformation, we were looking to generate a session object in JSON. That meant that more than transversing the data model, this ETL transformation was about aggregating data, in particular, aggregating data hierarchically in JSON format.

We have large organizations with tens of thousands of projects, for our transformation that means generating and aggregating tens of thousands of small JSON objects. Aware of the advantages of using JSONB for storage, we didn't think twice about executing our smaller operations using JSONB functions, which meant many small parsing operations on every iteration degrading session update response time.

If, instead, we cast the result, we convert it only once, resulting in a sweat performance improvement.

EXPLAIN (ANALYSE, VERBOSE) SELECT JSONB_BUILD_OBJECT('obj', obj) FROM (
 SELECT JSONB_OBJECT_AGG(user_id, tracked_obj) AS obj FROM (
  SELECT user_id, JSONB_OBJECT_AGG(team_id,  JSONB_BUILD_OBJECT('total_time', (random() * 1000)::int)) AS tracked_obj FROM (
   SELECT (random() * 10000)::int AS user_id, (random() * 10000)::int AS team_id
   FROM generate_series(1, 1000000)
  ) gen
  GROUP BY user_id
 ) agg
) foo;
—--------------------------------------------------------------------------------------------------
Subquery Scan on foo  (cost=50005.01..50005.03 rows=1 width=32) (actual time=6325.727..6368.489 rows=1 loops=1)
  Output: jsonb_build_object('obj', foo.obj)
  ->  Aggregate  (cost=50005.01..50005.02 rows=1 width=32) (actual time=5503.496..5503.499 rows=1 loops=1)
        Output: jsonb_object_agg((..., (jsonb_object_agg(..., jsonb_build_object('total_time',...)
        ->  HashAggregate  (... (actual time=3535.802..4119.971 rows=10001 loops=1)...
Planning Time: 0.174 ms
Execution Time: 6412.477 ms

Postgres is doing exactly what we told it to do, using jsonb object aggregate methods. If instead:

EXPLAIN (ANALYSE, VERBOSE) SELECT JSONB_BUILD_OBJECT('obj', obj) FROM (
 SELECT JSON_OBJECT_AGG(user_id, tracked_obj) AS obj FROM (
  SELECT user_id, JSON_OBJECT_AGG(team_id, JSON_BUILD_OBJECT('total_time', (random() * 1000)::int)) AS tracked_obj FROM (
   SELECT (random() * 10000)::int AS user_id, (random() * 10000)::int AS team_id
   FROM generate_series(1, 1000000)
  ) gen
  GROUP BY user_id
 ) agg
) foo;
—--------------------------------------------------------------------------------------------------
Subquery Scan on foo  (cost=50005.01..50005.03 rows=1 width=32) (actual time=3295.757..3345.740 rows=1 loops=1)
  Output: jsonb_build_object('obj', foo.obj)
  ->  Aggregate  (cost=50005.01..50005.02 rows=1 width=32) (actual time=1846.187..1846.190 rows=1 loops=1)
        Output: json_object_agg((..., (json_object_agg(..., json_build_object('total_time',...)
        ->  HashAggregate  (... (actual time=1765.143..1776.300 rows=10001 loops=1)...
Planning Time: 0.104 ms
Execution Time: 3355.383 ms

We run a single parsing operation at the end of it. Improving execution time and resource usage.

Conclusion​

This is how far we got in our journey leveraging postgres logical replication, and we have no plans to stop building on top of it. Leveraging this technology opens development avenues otherwise more difficult to pursue. We hope that our mistakes and discoveries can help you achieve great things too.

This post is based on the content of our A journey into PostgreSQL logical replication: the next chapter session, presented at PGConf.NYC 2024 earlier this month.

Capital Sin​

Our V8 API represented a major step for our backend infrastructure. Indeed, V8 marked the transition from our former Ruby on Rails backend to the current Go dominance. Practically speaking, it means that a team of Ruby engineers embraced a new language quite young back in 2012. Moving from an interpreted to a strongly typed language can be challenging, in particular, speaking of quite opinionated languages like Ruby and Go. We moved from an extreme like Ruby, probably one of the most implicit languages that can look like magic for newbies, to Go, which is one of the languages that most advocate for explicitness.

Go delivered on promises, allowing us to scale our infrastructure up to these days. Performance is not the only aspect to take into account when dealing with a growing codebase, though. In terms of structure and code design, V8 API was… arguably sub-optimal, to the point the team quickly started implementing an improved version 9 a couple of years later.

Over the following ten years, V8 and V9 coexisted for a number of reasons, making the deprecation of the old version harder and harder. V8 became deeply integrated not only with our internal systems but also with numerous external applications used by our customers.

Every new feature and change we worked on during this period required updates both to V8 and V9 endpoints while keeping the V8 interface unaffected. Such a persistent dependency is a massive burden for engineers that doesn’t affect only velocity but also the happiness of dealing with changes.

Complexity of Dependencies​

Successfully deprecating an old API version involves much more than technical work. It requires ongoing communication and collaboration with both internal stakeholders and external customers.

Within Toggl, our product and engineering teams had to identify all the internal services relying on the V8 API. This was no small feat, given the API's extensive use in various features and backend processes. We had to ensure that all these services were compatible with V9 before deprecating V8, which required significant refactoring and rigorous testing.

The external dependencies were even more challenging. Many of our customers had built their own tools and workflows around the V8 API. These integrations were critical to their operations, meaning that any disruption could have serious consequences for their businesses. We needed to communicate effectively with these customers, provide ample notice of the impending changes, and offer support to help them migrate to the new API.

It took over 3 years to fully deprecate V8 since we decided to seriously get it done.

We issued multiple deprecation announcements to keep our customers informed about the timeline for retiring the API. These announcements were critical in ensuring that everyone had sufficient time to plan and execute their migrations. Despite our best efforts, we still encountered situations where customers were caught off guard, underscoring the importance of clear and repeated communication. We pushed the deadline for deprecation so many times I lost the count. Not only the public ones but also when we managed to restrict the API usage to a small number of premium customers, we still iterated over and over to be able to fully drop it.

Our support and customer success teams played a crucial role in this transition. They worked closely with customers to understand their specific use cases, troubleshoot issues, and provide guidance on migrating to V9. This back-and-forth communication was essential in addressing concerns and ensuring a smooth transition.

You can understand how time-consuming and expensive it can be to involve all these stakeholders to deprecate such a piece of infrastructure. From a business perspective, it is really difficult to sustain such an effort for a long time, and the risk is to keep an outdated piece of infrastructure running for years, as it happened in our case. Simple choices like creating a new API version could chase you for years. We strongly suggest you mind your steps and, hopefully, learn from our mistakes.

Probably, one of the biggest mistakes we made during these 10 years was not to declare the previous V8 deprecated as soon as V9 was ready. As previously mentioned, there are a number of reasons why it didn’t happen, including V9 exhibiting worse performance. Still, eventually, the unclear versioning strategy led to more systems integrating and relying on the stable V8. Communication was not clear both internally and externally. We had multiple internal clients still using V8 when we set the deprecation goal, including our browser extension. The discovery about the browser extension has been really painful, and we fell from it when we thought at least most of the internal clients were deprecated. It also significantly pushed the ETA because dropping the old installed versions of the extensions was not trivial at all. While looking at external communication, our public docs only listed V8 up to three years ago, making another case for really bad communication and explaining why the network of dependencies kept growing.

Theory and Best Practices​

API versioning is a widely discussed topic. Navigating the web, you will find hundreds of posts describing benefits and best practices.

Important companies like Stripe and Shopify wrote about API versioning strategies as well.

API versioning is a double-edged sword. On one hand, it allows for evolution and improvement of services. On the other, it can lead to a proliferation of versions that become increasingly difficult to maintain. Supporting multiple versions simultaneously is technically referred as Version Sprawl.

Way too often, resources fail to mention how the approach to API versioning can differ significantly depending on whether you're building for B2C or B2B markets. When building a B2C product, you need fast iteration cycles, generally low impact of breaking changes, and emphasis on user experience and feature delivery. On the other side, building a B2B product comes with longer deprecation cycles, a higher impact of breaking changes due to deep integrations, and a greater need for backward compatibility. These are really different sets of requirements that must affect your versioning strategy.

We consider ourselves a product-led company, fully devoted to user feedback, experimentation, and fast iteration. Technical debt is the number one enemy to meet these requirements. Indeed, the inevitable growing technical debt in a scaling company slows down delivery cycles and overall the entire machine, making experimentation and improvements harder to deliver. API versioning can really easily turn into technical debt if your versioning strategy is not clear from the beginning.

All of this is also a side effect of the product culture in a B2C company that comes with additional friction for an engineering team to prioritize maintenance and toils like public documentation. But there is no way around it. Companies like Stripe have a clear incentive to maintain clear and up-to-date documentation because integrations are the main source of revenue for the company. In a company like Toggl that at the same time both never historically monetized API usage and has an active ecosystem of third-party integrations, the incentives for prioritization around these topics are mostly left to engineering maintenance and personal initiative.

The recipe for these occurrences is a mix of strategic planning and automation. First of all, abandon the idea of getting rid of technical debt all at once. It will require a number of iterations and smoothing to get to the point where you will actually be able to get rid of it. It was a key lesson for us and the only way we eventually managed to deprecate the old API. In terms of automation, instead, whatever requires a manual process to be maintained and such a process is not directly aligned with business goals will end up being outdated. For this reason, our existing public documentation is auto-generated automatically on each code release. The amount of manual work required for maintenance is really limited, and for the past three years seldom we suffered any type of inconsistency. Unfortunately, it doesn’t mean our communication is now perfect; we are still missing a consistent changelog to inform third parties about API changes, but we are working on it.

Another aspect conflicting with best practices in the day-to-day is that although you can try to avoid breaking changes as much as you can, those are inevitable. As part of the infinite game, your company will be evolving over time, and sooner or later, the evolution will require some breaking change. The general approach to avoid being blocked by breaking changes is to branch. In the case of API versioning, you can either create a new version or new endpoint(s), migrate dependencies to the new branch, and eventually deprecate the old one. In this case, you temporarily maintain multiple functioning versions of the same system. The transition period is inevitable, but at least it allows the delivery of the new behavior to meet users’ expectations.

When branching becomes systematic, though, and not just a temporary measure to deliver new functionalities, you might end up trying to maintain multiple API versions for longer periods of time. Maintaining multiple versions at the same time can easily transform into a nightmare. The primary reason is when your underlying data model, like your database schema, changes throughout different versions. In that case, the options are either to maintain different running schema versions, introducing serious complexity and maintenance overhead your team probably doesn’t want to deal with, or on the other side to manage the backward compatibility via business logic. The latter is definitely easier and more manageable, but over time, it will be more and more difficult to ensure consistency with older versions.

In our case, the V9 API grew, introducing major changes to the data model. For instance, the Organization layer was later introduced, as well as a number of features and user roles. These changes made the maintenance of V8 a growing burden. Eventually, the old version will end up lacking compatibility or, worse, will become a dangerous entry point. First of all, security holes could arise when the old version has access to domains that are not covered by the new version. In the best-case scenario, the mismatch will be the source of inconsistencies.

When V10?​

If you think the question is a joke, it was actually discussed a number of times within the backend team. Up to some point, there was quite certainty that we would have started the implementation of a new V10 API sooner or later.

In recent years, the opportunity was seriously discussed in particular during two circumstances. The first one was a major refactoring we made of V9 API to standardize the code architecture in all our domain packages. We won’t share details about this here, because it would require another blog post that maybe will come at some point, but enough context is to say that we spent almost 2 years refactoring our API code. Potentially, just writing a new API version would have been a reasonable approach, but we decided that the effort was as massive and took the safe bet. In retrospect, luckily, we didn’t pick the new version option just for the sake of building a shiny new thing because we would potentially still today be struggling to deprecate V9, or maybe both V8 and V9 because deprecating two versions would have required even further effort we would have never prioritized.

The second circumstance was after the refactoring when we actually wondered which was our versioning strategy if we had one at all. The conclusion from all these discussions was that, as of today, we consider our API version-less. We have the v9 in the path because of backward compatibility, but we don’t plan for a new version any time soon. We decided to embrace flexibility, and we think a version-less approach is the most efficient one to minimize maintenance and allow us to deliver user feedback and requirements quickly.

We embraced breaking changes as well, although we try to minimize them in every possible way. We had a number of projects that required some breaking changes to implement new functionalities or systems. Branching, keeping the existing backward compatibility running for some time, and announcing a deprecation notice proved to be effective. For instance, as part of our Shared Auth release we talked about in our previous blog post, we announced the deprecation of a number of endpoints that we kept running for some time, offering backward compatibility and eventually deprecated a few months later. In such cases, we also clearly communicated brownout periods to force customers relying on the service to react to interruptions. Of course communication went beyond a simple post, but we actively monitored usage and informed the organizations affected when the deadlines were approaching.

Hopefully, we learned the lesson: using a clear and structured communication strategy while not being blocked from releasing new stuff is a balance we think will allow us to keep the speed of delivery and innovation going forward.

Key Lessons​

We discussed a number of problems and challenges we faced with our API versioning. To summarize the lessons (and rants), here are some quick tips to avoid the pitfalls we encountered:

1. Understand your business needs: take into account your target customers and invest in your infrastructure accordingly. If your main source of revenue is third-party integrations, you will have to invest more into your infrastructure compared to how much you read in this post. Otherwise, our experience could save you a lot of time.
2. Plan for deprecation from the start: be aware about the lifetime of your system and build deprecation strategies into your design from day one. If you think deprecation is a problem for your older version, you might regret it for longer than your younger self expected.
3. Communicate in advance: schedule a deprecation period, communicate clearly to customers using more than one channel, like a blog post, and reach out via email.
4. Define brownout periods: often customers will not answer, but will eventually fire up when the service becomes unstable. Make sure the brownout windows are clearly communicated and long enough to give enough time to customers to react.
5. Offer Migration Support: Provide tools and documentation to ease the transition. For instance sharing public Swagger spec for our V9 helped out customers building their clients.
6. Consider Version-less APIs: Explore strategies that allow for evolution without explicit versioning. Don’t feel like a bad engineer if you don’t version your API. Long-term maintainability and evolutionary architectures are the most important aspects for companies, and no AI agent will be able to replace you in making these decisions anytime soon.

Our experience with the transition has been a valuable learning opportunity. As we move forward, we're committed to implementing these best practices to ensure smoother API evolution in the future, as well as maintaining and improving our communication with third-party developers.

Historically, we operated as fully separate and independent products, as well as separate teams. There are multiple reasons for this choice, but this approach allowed us to stay flexible and experiment independently for a long time, at different scales.

In January 2023, we started planning for a significant leap forward in enhancing user experience by rolling out a unified authentication system, internally named Shared Auth. Not only a significant leap, but Shared Auth was also a giant project that, since the beginning, we were aware would have required complex coordination as well as the involvement of multiple teams.

Our Goal​

The primary goal of the project was to get rid of the burden of managing multiple accounts for different Toggl products, providing a single access experience to all users. We decided to start from Track and Plan, our biggest products, and later allow the other products to integrate into Shared Auth.

A fundamental requirement was also to ensure zero-downtime, because at our scale it is really expensive to put the entire platform in maintenance mode and block users access. No mistakes were allowed then, extreme coordination was required to go live and offer the new functionality to all the users, taking into account all the edge cases that could arise from such a migration.

In order to allow users to log in and sign up with different methods, manage their password, and potentially their account, all of this through the same interface, we had to centralize all of our users into the same place.

Hence, the main technical challenge and goal was to aggregate users from different systems and databases into a single service to be consumed by all Toggl products. This goal had to be met taking into account synchronization required across different products using different cloud providers and different technology stacks.

Architecture​

When designing an authentication system, there are a few questions to be addressed before talking about any technology of choice. First of all, how is the session generated? How is the session stored, if it is at all? Which cryptography method to ensure confidentiality and integrity? In our case, how is the session transmitted to authenticate against multiple backend services?

Our products are served as subdomains of the root domain. For instance Track is served at track.toggl.com, while Plan is served at plan.toggl.com. This is valid for both the frontend applications and the backend, that is reached on the same subdomain on a specific API path. This implied that a valid session must be available domain wide at toggl.com, to be available to all of our applications.

On the browser, we soon figured out that a secure cookie was the best way to store a domain-wide valid session. The Secure attribute technically protects only the cookie's confidentiality, although an active network attacker can overwrite Secure cookies from an insecure channel, disrupting their integrity. We didn’t consider this a problem for a few reasons: our traffic is always encrypted, we set the cookie as HTTP-only so that JavaScript has no access, as well as up to date browsers do not allow to set the Secure directive for insecure sites (not HTTPS-protected).

On our native apps, we relied on OAuth on its PKCE variant to prevent CSRF and authorization code interception attacks. The OAuth setup returns both an access token with a one-hour lifetime as well as a refresh token with a 1-month lifetime. The refresh token can be used to obtain a new pair of access and refresh tokens (meaning that we have implemented rotating refresh tokens for extra security). This setup guarantees that you can continue using the Toggl native apps without needing to log in again as long as you interact at least once a month with them.

Hence, we defined that our shared authentication platform would have been served on accounts.toggl.com, which is exactly where users provide their credentials, and the secure cookie is set to be valid domain-wide.

But what does the cookie contain? We explored the countless existing solutions but eventually decided to use the most widely adopted JWT (JSON Web Token) standard as the format for session credentials so that Toggl products can validate them without having to make synchronous requests to the authentication service every time. Paired with JWT, we also employed JWK (JSON Web Key Set) for sharing keys so that individual Toggl products could validate session credentials. JWK is a set of keys containing the public keys used to verify any JWT issued by the server, and in our case, the signing is done with the Ed25519 algorithm, which provides a number of useful properties.

And yes, no need to point to tweets claiming how bad JWT is. Love and respect for cryptographers, we are aware of the risks and were careful about properly implementing the token verification, the session storage, and invalidation; indeed, we don’t store any personal data, such as name or email, within JWT.

After picking up the design elements of the authentication system, we were ready to decide the technologies to implement the solution.

On the front end side, we decided to use Remix, the protagonist of one of our recent blog posts. Remix is defined as a full stack web framework, specifically an SSR framework for React, that got a lot of traction and popularity after wide adoption from Shopify.

On the backend side, there were not many doubts about using Go for the backend service and Postgres for the database, our backend stack in the greatest majority of our services. We also leveraged PubSub, another widely used component in our architecture, for inter-backend communication, which we employed for communicating user changes and revoked session events.

Development and Migration​

The development and migration to Shared Auth involved meticulous coordination across multiple teams and products. Representatives from each Toggl team, including Track and Plan, collaborated closely to design, develop, and deliver the solution. Overall, more than 10 teams were involved, and almost 20 stakeholders took part in the project.

The setup described above was quite unusual for our company, but required by the size of the project. We named this group of stakeholders the Auth Taskforce. Stakeholders joined from engineering, product, design and management departments of both Track and Plan products, composing a fully cross-functional team able to manage the entire lifecycle of the project.

A dedicated Slack channel was created for the project in order to facilitate alignment and focused discussions. The team also had weekly meeting with rotating hosts, to foster ownership and comprehensive understanding among team members.

Each team representative was responsible for the definition of the testing flows required to validate the correctness of the solution. The testing flows included signup and logout via 4 different authentication methods within 10 different applications, as well as account edit and closure functionalities between both Track and Plan products. This extensive testing ensured that all potential issues were identified and addressed before the full rollout. For instance, representatives from native clients verified that the OAuth flow was working correctly; they could sign up, log out, close their accounts, and so on. On top of that, each representative also actively took part in testing general flows cross-platform.

The task force started setting up a dedicated staging environment for the new shared auth system. Track and Plan backends were also provisioned in this environment, incrementally adapting to the new system. In the same way, all clients were able to target this dedicated environment and test the new flows.

Challenges​

As mentioned above we decided to start with the 2 main Toggl products per usage metrics. Both products have different technology stacks, frontend and backend wise. They were operating as separate companies up-to shortly before starting with this project which carried the additional challenge of a lack of cooperation culture between these products teams.

In addition to their backends, both had native and web app clients that needed to be adapted to work with the new model. We also defined some constraints for the delivery:

• We had to avoid logging out everyone at once on the release date (to reduce disruption to end users) but allowed a transition period where old session credentials would still be recognized as valid
• We didn’t have to affect profile and session endpoints functionality for some months after roll-out, transparently proxying their requests to the new system. This was to reduce disruption to third-party scripts and integration that depended on the old endpoints

Another source of edge cases was users with matching emails in both products. Indeed, we wanted to migrate the source of truth for user accounts from the individual Track and Plan databases to the new unified authentication system in the most transparent way. We had to take into account the around 20,000 users sharing both products. The tricky challenge is that both Track and Plan allow users to start using the product without verifying the email. Hence, we didn’t know beforehand if users with matching emails represented the same person.

We introduced a solution we named “Merge Conflict Resolution”, a mechanism where each time one of those users tried to log in to the new system, they would be presented with a screen telling them a new unified auth system was put in place and to please use a link that we had sent to their email in order for them to set a new password. On top of making users aware of this change, it had two other benefits: first, we could verify the user’s ownership of the email; second, we could let the user choose if they wanted to merge the two accounts in the first place. Alternatively, by clicking the link in the email, users also had the option to change the email of the account they were in case it wasn’t valid, or they couldn’t access it, or they simply didn’t want the two accounts connected.

Release​

Zero Downtime Strategy​

The requirements for implementing a zero downtime release were:

• Implement transparent proxying of endpoints in both Track and Plan backend so that they would resolve the request by contacting the new endpoints in Shared Auth
• Allow a transition period where the old session credential would still be recognized

In the new system, using JWT tokens, the ID of each user and the products they have access to are encoded in the token. In this way, when either Track or Plan backends needed to validate the new session credentials, they only had to check whether:

• The product was included in the list encoded in the token
• The user ID was known; otherwise, make a one-time request to Shared Auth
• The JWT was properly signed with the public keys, based on the JWK ****mechanism.

Those JWK public keys are cached by Track and Plan, then only one request to Shared Auth once every 30 minutes is required to check whether they changed.

We also ensured the independence of Track and Plan, allowing them to process the authentication for the vast majority of requests without having to make synchronous calls to the Shared Auth system. It is really important to notice that, in this way, we take out that component as a critical one that could drive the uptime for all Toggl products down if temporarily unavailable.

Given that Track and Plan already had a local concept of a user, when we changed the source of truth for these users to the new authentication system, we just reused these local records as a cache of the new source of truth that was updated by listening to a PubSub queue. This way, there was a surgical insertion of the interaction needed between those pre-existing backends and the new system, where the rest of their codebases could continue working seamlessly, consuming from the local record without worrying about the existence of a new system.

Scheduled Steps​

The task force defined 9 required steps to handle the migration:

1. Transition Time: all stakeholders agreed on a specific time for changing the source of truth to Toggl Accounts
2. Set transition time: the transition time was specified in the new and old system to determine when the new behavior should start
3. Deploy Toggl Accounts: no new signups or interactions with existing accounts will be possible until the time defined in Step 1
4. Deploy Toggl Track: clean up any existent references to previous accounts from the database, and also set the Transition Time, after which requests will start being proxied
5. Clean up the Toggl Accounts production database: manually delete all users created during the testing phase
6. Wait for the Transition Time: no new signups accepted after the previous step, migrating any accounts, or having any role until the time defined
7. Monitoring: multiple resources, including Grafana and Google Cloud dashboards and Sentry, for errors
8. Frontend release: greenlight for Track and Plan frontend to release their changes
9. Monitoring (again): the same resources above, making sure users are able to access the new system, as well as the Merge Conflict Resolution mechanism was properly working

Outcome​

The transition to Shared Auth was successfully executed with minimal disruption.

Given the amount of testing that went into this project and that we had a representative from each team, we could cover well the vast majority of scenarios. The fact that we allowed a transition period both for old session credentials as well as for old endpoints reduced the surface area for surprises.

On launch day, we saw some problems with the old native apps when trying to authenticate with Google/Apple because they were pointing to the old Track endpoint, which in turn was transparently proxying to Shared Auth. For this situation we decided just to turn off the automatic proxying to Shared Auth, allowing Track to be the source of truth again for those auth mechanisms. This mechanism made sense because Track was (up to that point) the only Toggl product allowing Google/Apple access, so it had enough data in its database to resolve those requests.

Before the rollout we had also put in place a process in the new auth mechanism that would inform Track about any Google/Apple ID that was registered in the unified system. A couple of days later, we fixed the problem, and Track old endpoint returned to just proxying the requests to the new system, as intended.

On launch day we also saw a race condition that affected a small portion of users signing up through invitations. For these users, we didn’t have to create a personal Track workspace because they were already joining an existing one. Taking into account how we were processing events in Track from PubSub, we could create a local record with the personal Track workspace before checking whether the user was signing up due to an invitation. So, for some days, some users got a personal workspace, which created mild confusion until we fixed the race condition and cleaned up the workspaces created due to this problem.

Nine months after the launch of Shared Auth, we cleaned up the deprecated endpoints as planned.

Flash Forward & Conclusions​

Almost one year later, we successfully integrated Work, our new expense management and reporting platform that relied on Shared Auth from the get-go, as well as Hire, which recently migrated all their users to use the unified system.

Following the successful implementation of Shared Auth, we continue to work on integrating our products. This project marks a significant step towards interoperability and sets a strong foundation for future enhancements.

This was only possible thanks to the effort put forward by the involved stakeholders who represented all teams within the company. Thank you for your wide contributions delivering this project.

Why do developers test their applications? One reason is that developers must safeguard the applications from their own changes.

Professional developers test their code. Martin, The Clean Coder (2011).

Testing shows the presence, not the absence, of bugs. Dijkstra.

The quotes highlight the importance and essence of testing, acknowledging that tests can reveal issues but not guarantee that your program is correct.

Tests are an invaluable investment that enables developers to confidently assess and enhance their code, increasing productivity. Remember that tests should be understandable, easy to maintain, and flexible to evaluate - like the application code.

Testing Strategies​

Patience, young grasshopper. Before diving into the Testcontainers project, let's understand the Testing Strategies and how Integration Tests fit into them.

The Ice Cream Cone​

The Ice Cream Cone focuses a lot on manual testing and, in general, on testing an environment that mimics as closely as possible the production environment. Although dev and prod parity is definitely not bad, this approach is also the opposite of testing in isolation, making it much more difficult to test details and edge cases we are still interested in covering. As the application grows, more QAs are required to think about the scenarios and perform the tests manually. Can you see how this is error-prone and nonscalable?

The Pyramid​

The not-so-cool Test Pyramid. I still remember the first time I saw the Tests Pyramid. My very first thought was: “Holy Moly, it is so cool!” However, as Stephen H Fishman mentioned in this article, “Testing is Good. Pyramids are Bad. Ice Cream Cones are the Worst”. So, I've changed my mind a bit. Although the pyramid strategy is better than the previous model discussed, it is unsuitable for many modern applications. At the base of the pyramid are the unit tests, which are considered low-cost. They are cheaper to develop and faster to execute. Something implicit in the pyramid is that as the cost increases, confidence also increases, and confidence is an essential aspect of any test suite.

The pyramid is based on the assumption that broad-stack tests are expensive, slow, and brittle compared to more focused tests, such as unit tests. While this is usually true, there are exceptions. If my high-level tests are fast, reliable, and cheap to modify - then lower-level tests aren't needed. Fowler, martinfowler.com (2016).

Let's analyze Martin Fowler's note. Our test suite can provide a high confidence level at a lower cost since the broad-stack tests are cheaper, such as the unit tests. Moreover, there's no need to have multiple tests validating the same scenarios only to watch them fail for the same reasons. Don't be repetitive. To be clear, I am not discarding the unit tests. It's all about balance.

Integration Tests = confidence + +, mocks - -​

Integration tests determine if independently developed units of software work correctly when they are connected to each other. Fowler, martinfowler.com (2018).

Applications with Sonar gates requiring 80-90% code coverage do not guarantee high-quality software; they ensure elevated coverage. There is a big difference between quality (strongly related to confidence) and coverage.

When tests are written just to satisfy a certain coverage rate, they lose their value. As Kent C. Dodds said in his awesome post about integration tests, “When you strive for 100% all the time, you find yourself spending time testing things that really don't need to be tested”.

Take a look at the following Go code.

func (s service) Get(ctx context.Context, id int64) (model.User, error) {
  return s.repository.find(ctx, id)
}

Let's consider a unit test for this function where the repository would be mocked. As there are no business rules to be validated, even though the coverage is increased, the function may still contain a bug that wouldn't be caught. Maintaining tests without a specific existing purpose can be annoying and unproductive.

After discussing with other Togglers, we agreed that integration tests are more effective in preventing developers from making errors.

Honeycomb and Trophy​

Spotify proposed the honeycomb test strategy that emphasizes integration tests while still including unit and e2e tests, but in a different proportion. It's all about balance, remember? The trophy approach includes static analysis, but it also gives priority to integration tests.

Testcontainers​

Testcontainers is the correct spelling (yes, lowercase c) and a singular word. Testcontainers is not about “testing containers” but “testing with containers”. The central concept of the framework is to manage and run integration tests using Docker-based test dependencies through a straightforward API.

All of this is self-contained and orchestrated within the test context. Simple and fast as unit tests, with no mocks! As a developer, executing integration tests through my favorite IDE, with no extra steps, is delightful.

Richard North started this open-sourced project in 2015, recognizing the potential for success when Docker was becoming increasingly consolidated across the software community. The first version was released for Java and that is currently the most feature-rich one. Nowadays, Testcontainers supports many other languages: Go, .NET, Node.js, Python, Rust, Haskell, Ruby, Clojure, and Elixir. He created the solution primarily to solve his own pain.

We've achieved great outcomes by using the Testcontainers in one of our modules. This approach has allowed us to eliminate the need for READMEs, scripts, make commands (which include a particular execution order), and other extra steps to run integration tests. This is particularly helpful for new employees unfamiliar with the project.

It also allowed us to remove the additional steps to manage the containers' lifecycle from our pipeline.

We could improve the module's test execution performance by ~35% compared to the old suite in our pipeline. The old suite has many dependencies, some of which are optional for the module. These steps consume a lot of time. Below are the numbers of a POC.

Sample tests running in the old suite

Sample tests running in the Testcontainers suite

To run the tests using Testcontainers in your pipeline, you only need to have Docker installed, which is already included in GitHub Actions workers by default.

How does Testcontainers integrate with Docker?​

This is a succinct representation of the Docker architecture. The Docker CLI uses the API REST to manage the objects. Whenever a Docker command is executed, it indirectly invokes this API. Testcontainers directly invokes Docker API, making it simple and flexible to communicate with Docker through code, considering an HTTP client is available in many different languages. The Testcontainers uses the docker client available in the Go packages to do this. Simple and direct!

Testcontainers and Go​

The primary language employed in the development of Toggl Track is Go. The Go library offers some modules, with Postgres being the most popular. The modules aim to simplify usage across various technologies, abstracting away many configurations. There are a bunch of other interesting modules besides Postgres, such as MySQL, Redis, Elasticsearch, Kafka, and k3s. Among these, k3s is particularly noteworthy as it is a lightweight Kubernetes cluster, which could prove helpful for teams working with operators or similar projects. You can launch any other containers using the Generic Container implementation if you have a generated image. Certain containers require initialization, such as database migrations. One way to perform migrations is to retrieve the host and port and execute them through your migration library. If this is not possible, you can use the WithInitScripts function. If your tests require multiple dependencies, guess what? You can also use docker-compose with test Containers.

Show me the code!​

Here, you can find a simple demo application that uses Postgres to perform some tests. If you have come this far, you know you won't need much to run the tests.

Let's go over the crucial points of the linked application.

• There is a simple CRUD around the User entity.

type User struct {
	bun.BaseModel `bun:"table:users,alias:u"`
	ID *int64  `bun:"id,pk" json:"id"`
	Name string  `bun:"name" json:"name"`
	Surname string  `bun:"surname" json:"surname"`
	Age int64  `bun:"age" json:"age"`
}

I chose Bun just to demonstrate how easy it is to set up migrations using your favorite library.

• There is also a simple repository and service. Nothing new so far.

type service struct {
	repository Repository
}

...

func (s service) Create(ctx context.Context, user model.User) (*int64, error) {
	return s.repository.save(ctx, user)
}
      
func (s service) Get(ctx context.Context, id int64) (model.User, error) {
	return s.repository.find(ctx, id)
}
      
func (s service) List(ctx context.Context) ([]model.User, error) {
	return s.repository.findAll(ctx)
}

• The goal is to test the service layer integrated with the repository. And now, this is the Postgres configuration:

type PostgresDatabase struct {
	instance *postgres.PostgresContainer
}
   
func NewPostgresDatabase(t *testing.T, ctx context.Context) *PostgresDatabase {
	pgContainer, err := postgres.RunContainer(ctx,
		testcontainers.WithImage("postgres:12"),
		postgres.WithDatabase("test"),
		postgres.WithUsername("postgres"),
		postgres.WithPassword("postgres"),
		testcontainers.WithWaitStrategy(
			wait.ForLog("database system is ready to accept connections").
				WithOccurrence(2).
				WithStartupTimeout(5*time.Second), 
				),
   	    )
	require.NoError(t, err)
	return &PostgresDatabase{
		instance: pgContainer,
	}
}
   
func (db *PostgresDatabase) DSN(t *testing.T, ctx context.Context) string {
	dsn, err := db.instance.ConnectionString(ctx, "sslmode=disable")
	require.NoError(t, err)
	return dsn
}
   
func (db *PostgresDatabase) Close(t *testing.T, ctx context.Context) {
	require.NoError(t, db.instance.Terminate(ctx))
}

The code is pure Go and has parameters similar to Docker, making it easier to understand. First, we define the image tag, the database name, user, and password. The Wait Strategy indicates if the container is ready to receive requests. There is also the option to run the database migrations informing the SQL files through the WithInitScripts function. Note that we have been using the Postgres module.

The wrapper PostgresDatabase was created only to facilitate the use in the suites.

• Finally, here is the suite test.

func TestSuite(t *testing.T) {
	suite.Run(t, new(IntegrationTestSuite))
}
      
type IntegrationTestSuite struct {
	suite.Suite
	db        *bun.DB
	container *integration.PostgresDatabase
}
      
func (s *IntegrationTestSuite) SetupSuite() {
	ctx, cancel := context.WithTimeout(context.Background(), time.Minute)
	defer cancel()
      
	s.setupDatabase(ctx)
}
      
func (s *IntegrationTestSuite) TearDownSuite() {
	ctx, cancel := context.WithTimeout(context.Background(), time.Minute)
	defer cancel() 	
	
	s.container.Close(s.T(), ctx)
}
      
func (s *IntegrationTestSuite) setupDatabase(ctx context.Context) {
	s.container = integration.NewPostgresDatabase(s.T(), ctx)
	sqldb := sql.OpenDB(pgdriver.NewConnector(pgdriver.WithDSN(s.container.DSN(s.T(), ctx))))
	s.db = bun.NewDB(sqldb, pgdialect.New())
	s.migrate(ctx)
}
      
func (s *IntegrationTestSuite) migrate(ctx context.Context) {
	err := s.initDatabase(ctx)
	require.NoError(s.T(), err)
      
	migrations := &migrate.Migrations{}
	err = migrations.Discover(os.DirFS("../../migrations"))
	require.NoError(s.T(), err)
      
	migrator := migrate.NewMigrator(s.db, migrations)
	_, err = migrator.Migrate(ctx)
	require.NoError(s.T(), err)
}
      
func (s *IntegrationTestSuite) initDatabase(ctx context.Context) error {
	type hack struct {
		bun.BaseModel `bun:"table:bun_migrations"`
		*migrate.Migration
	}
	_, err := s.db.NewCreateTable().Model((*hack)(nil)).Table("bun_migrations").Exec(ctx)
	require.NoError(s.T(), err)
	return nil
}

So, the database was initialized, including the migrations executed using the Bun. These migrations would represent the ones used in production. This is the main idea: get as close as possible to the production environment.

• The tests are straightforward, like this example. Notice that the database instance came from the integration test suite.

func (s *IntegrationTestSuite) TestCreate() {
	userService := NewService(NewRepository(s.db))
      
	s.Run("service should create the user successfully", func() {
		// Given
		user := model.User{
			Name:    "John",
			Surname: "Smith",
			Age:     45,
		}
      
		// When
		id, err := userService.Create(context.Background(), user)
      
		// Then
		s.NoError(err)
		s.NotNil(id)
	})
}

Alternatives​

Dear reader, you may consider exploring other options besides Testcontainers. Let's look at some alternatives.

1. If a project's only external dependency is the database, it is worth using an in-memory database to run the tests. It's important to note that it is also a mock. Ultimately, the tests do not deal with the actual database instance. They may not always reflect the database's real behavior, which can lead to false positives. A few years back, I worked on a project that involved using MySQL and an in-memory database to conduct tests. I implemented a change, and the developed tests worked fine with the newly inserted index. However, I was bombarded with errors when the code was deployed in the development environment. It turned out that MySQL didn't support the type of index I had used, although it was allowed in the in-memory database used to perform the tests, leading me to a false positive.

2. It is possible to run the tests mostly with docker. Absolutely, it is. On the other hand, it can also generate other problems. Once upon a time, a developer was running integration tests when they suddenly stopped working. The developer tried restarting everything, thinking the problem might be with their machine. However, after two hours of troubleshooting, they discovered that a new container was required to execute the test due to a new dependency that another team had inserted in the test suite.

Conclusion​

Initially, I planned to split this into two sections: Upsides and Downsides. But truth be told, I couldn't round up enough downsides to warrant a separate section. So, if you've hit a snag with Testcontainers, let's chat! I'm all ears (or eyes). Alright, let's highlight the characteristics of the library:

• Simple to implement tests, evaluate, and give maintenance
• Easy to run them locally or in the CI/CD execution
• Waiting Strategies
• Random port allocation in containers to prevent conflicts
• Fast feedback
• Productivity
• Flexibility
• Self-contained tests
• Isolation

That is all, folks!

The Incident​

On 2024-02-26, we noticed a surge in traffic affecting our authentication service, the shared service allowing users to login and signup to our products. The amount of traffic was quite relevant, up to 8000 times the level of traffic we are used to sustain.

A DDoS attack was launched against our service, leading to a substantial service interruption. This incident marked one of the longest outages in our history, for this reason we decided to public share more details about it. The outage impacted user access functionalities while not entirely inhibiting platform access for already logged-in users.

It barely took us ten minutes to identify the root cause of this vulnerability as an unintentional misconfiguration of our cloud networking protection service, a critical component of our defense, not properly configured in the new service.

Our Response​

Our team responded to the attack with urgency, deploying several strategies to mitigate its impact. We adjusted our infrastructure scalability, implemented geographic blocking to manage traffic, and engaged closely with our cloud provider, Google Cloud, to restore the necessary protective measures. These actions were crucial in managing the attack and eventually restoring normal service levels.

However, the incident highlighted areas for improvement, particularly in our observability and mastering of the technologies in use. Indeed, compounding the lack of networking protection was our initial lack of awareness and observability of the specific behaviors and limitations of our authentication service. In particular, our authentication frontend, a Remix based server-side rendered application. Remix is a wonderful web framework we are adopting for a number of applications, and we will keep investing in it.

It turns out that our authentication frontend service could not properly handle the rate-limiting strategy from our API because of enforcing a further rate-limiting mechanism built within our API client. We could not identify such a behavior while load testing under reasonable traffic conditions, still definitely lower than the huge traffic we experienced. The outcome is that despite our efforts to scale the related service horizontally to face the surge of traffic and actually mitigate the attack, bringing traffic back to a reasonable amount, due to the conflicting mechanisms, we failed to restore the service functionality for a few hours.

As mentioned above, it took us long to identify the rate-limiting problem because we were missing proper observability in our service, including proper logging and tracing, that would have allowed us to trace the lifecycle of the requests and understand the unexpected behavior of the authentication service. We officially employed Remix at scale for the first time last August when we released our Shared Login last year. This aspect is clearly providing us with actionable feedback in terms of the approach to adopting and mastering new technologies, as well as enforcing well-established practices across the stack.

Moving Forward​

Reflecting on this incident, we recognize the importance of continuous learning and adaptation. Here are the key steps we are implementing to fortify our platform:

• Enhanced Communication: We are improving our internal communication processes to ensure that critical information is shared promptly and effectively across all relevant teams

• Comprehensive Service Acceptance Criteria: We will enforce adherence to a rigorous set of service acceptance criteria for all backend and frontend services, which will include enhanced observability measures. This approach will enable us to detect and address potential issues more proactively

• Improved Infrastructure and Testing: Our commitment extends to upgrading our infrastructure to better handle increased traffic volumes and potential threats. This includes expanding our testing strategies to cover a wider range of scenarios, ensuring our platform remains resilient under various conditions

• Status Page Overhaul: Transparency with our users is paramount. We commit to improving our status page to offer a more detailed and real-time overview of our services' status. This will provide our users with timely information regarding service interruptions and maintenance updates

The recent attack was a stark reminder of the evolving challenges in maintaining a secure and reliable platform. While it was a difficult time, it has catalyzed our efforts to strengthen our systems, processes, and team readiness.

At Toggl, we are committed to learning from this incident and emerging stronger, ensuring that we continue to provide the reliable service our users expect.

We strive daily to provide better reporting capabilities to our users, reporting over more data, with more insights, transformations, well... more of everything. However, all of these reports were still running on top of our normalized data structure on our transactional database.

Long story short, we long noticed the need to move, hydrate, and transform our transactional data into something with report-friendly structures, and that became harder and harder to ignore.

Moving data around​

We started to look into what could be done and what solutions were out there, but we ended up deciding to implement an in-house CDC pipeline, on which ETL processes would run, to build and keep up-to-date our new datasets. This, with a very important requirement in mind: keep our reporting features as close to real-time as they can be.

A periodic extraction wouldn't do it, while a CDC pipeline will provide us with a reliable stream of data changes happening on our transactional database - in a close to real-time fashion - and these change events could then be used to keep our OLAP transformations up-to-date. Along with other use cases.

The end goal is to reduce as much as possible run-time computing to transform data in order to deliver a given report. And make that data easy enough to be retrieved over different dimensions. Which often means sacrificing storage for computing.

Our OLAP journey​

As so, our Postgres logical replication journey began. We chose to leverage Postgres functionalities to achieve our requirements, and in the end, our OLAP databases are also Postgres databases that are responsible not only for keeping our new datasets but also for doing much of the transformation's heavy lifting. Modularization was put in place to ensure that we can use different technologies if we choose to.

We are big Golang fans, so we build our logical replication client application - that will sit on top of Postgres logical replication features - in Golang, using pglogrepl, and end up with an implementation very very similar to other open-source snippets that you can find all over google. A very simplistic one too. But given our particular use case, we ended up extending that simplistic implementation with supporting features around uploading data-changing logical replication events to an event messaging service.

To make our logical replication client work, it needs a replication slot, on the Postgres side, the same as any other client, including Postgres itself. That slot will be responsible for keeping track of our client event consumption, and that is done by using LSN offsets.

LSNs, or Log Sequence Numbers, are incremental numbers that point to a given location in the WAL file. And that’s the “sequence” that we need to keep track of to commit our consumption progress to Postgres in order to not only receive the data again but also to allow Postgres to flush old WAL files, which should no longer be needed because we already consumed them (assuming that you have no other logical replication endeavors going on).

Here is an example of how these offsets look like and a sample of queries that could generate such offset patterns:

In the first one, we have a transaction start, an insert, a couple of updates, and a commit - and by the way, this was one of the most common operation patterns that we had in our database till a few months ago. Then we have another example, and finally, how does it look like with a single insert. Notice the transaction's begin and end offsets, in contrast with what was writen before, that we cared only about data-changing events in the beginning of our journey.

Learning curve​

That's an important bit of information regarding our journey there in the last sentence. We cared only for events that changed data, in order to fulfill our goal which was to keep our reporting dataset up-to-date. We cared for inserts, and updates. Also deletes, and truncates, but not too much about these, as we make these operations forbidden on our production databases. At least for non-superusers.

It made sense at the time because we knew that for a given data-changing event to make it into the Postgres logical replication stream, the change had to be already committed so... we didn’t see any use in processing any other events like transaction begins and ends. Looking back, that was a mistake.

To go easy on our eyes, from this point forward, I'm just going to simplify LSN notation from the hexadecimal representation to just a numeric sequence prefixed with LSN. What's important here is for you to notice the value incrementation itself, or lack of it, not the representation.

The examples shown to you till now have incremental LSN offsets. Meaning, we can see that the value of LSN offsets follows our operation order. This doesn't always happen, but it's something very easy to assume that it does. We fell for that.

Without concurrency, our Postgres data-changing queries will always produce incremental LSN offsets across the board. It makes sense, we are appending them to the log.

In the first column, we have the operations being executed, the transaction to which they belong, and the LSN values attributed to the individual operations. In the second column, we can see how these operations would be written to the log. We can visualize a WAL file being generated over such conditions as in the following image.

Notice in the first transaction, the BEGIN and END events there in the vertical, then abstracted in the others, because, well, we were not caring for them, but nonetheless they are there, and are streamed by Postgres - its just that - they were completely ignored by our logical replication client.

With concurrency, however, this is what LSN offsets look like:

We can see that the first transaction started, Postgres logged the begin, the insert, then another transaction started, Postgres logged the first operations of it, then another one came in, same happened, and as the remaining changes reached the log offsets were given to them, but these incremental offsets don’t respect transaction boundaries. At the end of it, we get fragmented LSN incremental sequences, if we group them by transaction. Note that in this example, the first transaction to start is the last one to end.

Here is the visual representation of how a WAL file for a Postgres database operating under the right concurrency conditions may look like:

In sum, PostgreSQL will log concurrent transactions as their individual changes reach the log, and that’s the point where LSN offsets are attributed to individual events.

As we were intentionally disregarding transactions, all we had to work with were data-changing events and their offsets.

The operations that were being executed in one order, the first column - if they were being applied concurrently - pg will be logging the operations as they reach the log, second column. Logical replication would stream that data in a different order and with nonconsecutive incrementation within the transaction itself. Note again that the first transaction to start is the last one to be streamed.

We were dead set on caring only for data-changing events, we thought that it would simplify our process, and we didn’t yet realize that we needed to change that. So, we attempted to live with this reality that hit us once we first deployed to production. We would keep track of the LSN 1, 2, 3, whatever, commit that to Postgres, and eventually either end up ignoring data on our own, losing lower offsets, or receiving duplicated data for higher offsets.

We made bad assumptions, and the first one was that LSNs would always be incremental across the board, but as we just saw, when logging happens under concurrency conditions that’s not the case. The second was that we cared only for offsets coming in data-changing events. Since logical replication stream changes are already committed, we assumed that using data-changing events would be enough.

Attenuating circumstances​

There were attenuating circumstances to our bad assumptions. One of which was that at the time, for the first usage of our newly developed OLAP - ETL-based - system, we didn't need to care for operation order. We were summing up time. So we didn’t look deep enough into how we were receiving the data, just if we were receiving all the data. And that always happened in close-to-ideal sceneries. For these aggregations didn't matter if we sum up 10 hours and subtract 5 or the other way around. And a side note is that deletes for us were actually negative updates. Because of soft-deletion.

Well, apart from that bad excuse of how we ended up scratching our heads about why we were getting inconsistent data on our OLAP transformations in production. We were also not generating the right conditions to test for concurrency issues, and that’s mostly why we didn’t have enough information to figure this out faster. We also managed to mitigate the problem so well that we were now only seeing inconsistent data in production, and very sporadically, which masked the problem and didn’t help debug the issue. Eventually, we managed to trace down sporadic data inconsistencies to very specific conditions that would match our replication client redeploys with periods of high QPS, where the probability of ending up with non-incremental offsets between transactions would be greater.

Of course, no user was exposed to this data till we got 100% confident that our extraction and transformations were reliable.

Enlightenment​

Eventually, we figured out that we were doing something horribly wrong. The first clue was that - well - Postgres itself can use logical replication for replicas, and, replicas can also restart and/or recover without any data issues. The second one was to look at Postgres source code and understand the process behind pgoutput, which is the logical replication decoding plugin that we use. And third and final breakthrough was getting in touch with Postgres developers in an act of desperation through the mailing list, and slowly - navigating replies - put together the picture of how the replication offset tracking process works and how is expected to be used.

What did we do wrong?​

Incremental LSN offsets are not ensured, not across transactions, nor should they be expected. By doing so, we went into a spiral of "ways not to track replication offsets.” no matter how good our mitigation process was, it was doomed to fail, specifically due to reconnections.

Given these two transactions, if we were to commit and use offset 5, we would then discard the first event of the next transaction artificially, and I say artificially because Postgres will still send it - as we are going to see next - but if we filter out events on our side using the last offset that we received, we will discard it.

Another wrong related to this was to commit operation offsets, instead of transaction end offsets. We were not caring for transaction events, so, when our client exited, we would commit the last data-changing event offset consumed, which would make Postgres stream the entire transaction again, and we would either duplicate that data or - if we were to artificially escape it - risking discarding needed data in other situations.

To be clear: no filtering on the client side is needed. We only assumed that was because we were fooled by Postgres resending the last transaction all over again as we were not committing transaction ends, only the last operation.

Postgres will only mark a given transaction as consumed by the logical replication client if the replication committed LSN is greater or equal to its transaction end.

Hard facts​

After the realization of what we were doing wrong. We come to draw hard facts that we could rely on to work towards a proper solution.

• Logical replication works over TCP, which means that we will never receive the next event without acknowledging the last one; this ensures us that whatever offset comes next, is not "out-of-order" in the sense that it will be abiding by Postgres logical replication rules. We could also discard any networking interference in the process.
• The only event offset that we can rely on being incrementally sequential in between transactions streamed by logical replication is the commit or transaction end offset. Postgres will only log one transaction end at a time.
• pgoutput logical replication stream will stream transactions sorted by commit offsets.
• Not using transaction end offset to commit our consumption progress to the replication slot will trigger Postgres to resent the entire data for the current transaction again upon reconnection.
• Events for a given transaction are always streamed together, regardless of the log position of events that compose it not being written sequentially in the log. This means that Postgres logical replication will only stream the next transaction once it is done with the current one, and again TCP ensures that we can rely on this.
• Regardless of the consumption commit offset for the last transaction end having a higher numeric value than some of the next transaction events, PostgreSQL will always stream the next transaction in its entirety for the same reason that we were receiving duplicated events when not committing to Postgres an offset bigger or equal to the transaction end event.

New approach​

We changed our approach to always commit the transaction end event offset to the Postgres replication slot and only these offsets. For that, we make sure that we process the transaction data-changing events, respecting the transactional integrity that they entail. Meaning, that we make sure that we upload all transaction data-changing events to our messaging service and only then advance our consumption offset.

At this point, we were confident that our logical replication client was working as expected. However, our tail doesn't end here.

Messaging services have limited message sizes, and as we started treating transaction data as a whole, we started to hit them. That is because Postgresql transactions can very well have millions of data-changing events. By moving forward only when the COMMIT event is received and trying to enforce transaction integrity on data uploaded, we were vulnerable to OOM incidents. No matter how generously we would add memory to our service. On the other side, splitting these huge transactions into multiple bulk event uploads to respect max message size limitations would create conditions for duplication, either on plug-off events or general erroring.

While the proper use of LSN offsets fully prevents data loss, it doesn’t prevent data duplication. At the end of the day, respecting transactional integrity while managing capacity constraints such as message sizes for our messaging service was not free of charge, and we ended up having to split transactions when their size was significant enough. Due to that, under the wrong circumstances we may end up uploading only part of the transaction data, and that would mean that our consumption offset would not move forward because we didn’t consume the whole transaction. Upon retry, we would resent the data already updated, causing data duplication. Another instance when data duplication can happen is if, after delivery of the transaction data, we are unable to commit our consumption offset to Postgres for some reason.

We embraced the data duplication possibility on our transformations to avoid thickening the consumption layer on top of Postgres (our Golang logical replication client) with statefulness features.

We are proud to announce that if today you go to any Toggl Track client application, create an account, create your first project, and track time to it, the total time for that project that you will see across our client applications will be the result of this process. One in a few use cases that are already in production, in between other applications that we are working on.

Thanks for reading, we expect that sharing such adventures is helpful for others. Hoping that the next time that you need to work with Postgres logical replication, you don't get stuck around realizing these problems where you won’t find explicit answers in the documentation. And, unfortunately, we can even be misled by implementations that are out there that simply don't care for data consistency or the process as a whole, as we found in some open-source projects. Some went as far as suggesting in the documentation to just wait long enough for all the data to be consumed, which, of course, is impossible to do with a live application.

This post is based on the content of our A journey into PostgreSQL logical replication session, presented at PGConf.EU 2023 last December.

In recent years, our company has made significant progress in technology and scalability, which has been instrumental in driving our growth. Additionally, we want to show we established an environment that is highly engaging and challenging for engineers.

As seasoned developers know, the journey through the tech landscape is as challenging as it is rewarding. Our blog aims to capture this journey, offering a window into the dynamic world of software engineering.

What to Expect​

Our posts will span a variety of topics, including but not limited to:

• Technology: deep dives into the latest tech and tools we employ. Expect explorations into Golang, Postgres, React, TypeScript, Kotlin, and more, reflecting our team's broad expertise.

• Engineering Challenges: Real-world problem-solving stories. Learn how we tackle complex issues, from scaling infrastructure to optimizing performance.

• Best Practices and Innovations: Insights into our methodologies and the innovative solutions we devise.

Hiring​

We are Hiring! Visit Working at Toggl and discover our open positions.