[MUSIC]
>> Hi. My name is Jonathan Denney,
and I'm the co-founder and CTO at ConvertFlow.
In this talk, I'm going to break down lessons
learned scaling ConvertFlow on Postgres
to eight plus billion events. Who is this talk for?
It's for CTOs and engineers at
startups who want to learn not just from our wins at
ConvertFlow but also our mistakes on our journey through choosing
a database as well as scaling up
our application and optimizing performance at scale.
I'm going to break down each stage of
our journey from zero to one million events,
from 1-100 million events,
and then to billions of events.
For each stage, I'm distilling our learnings
into lessons that you will walk
away with for you to use in your journey
from choosing a database technology to scaling it up.
What is ConvertFlow?
ConvertFlow is a no-code platform for
e-commerce marketing teams to create
personalized and launch visitor conversion campaigns
on their websites without waiting on developers.
This means to say SaaS application with
thousands of paying users logging in to use
a dashboard where customers create
interactive contents and experiments for their websites,
which they are going to want to get insightful reports on.
Now this content our customers
create for their websites gets viewed
by as many as 40 million unique visitors per month at this point.
Which means a lot of events for our application to
process and for our customers to report on.
We launched ConvertFlow out of Techstars Chicago in 2016,
which is a three month long startup accelerator
with 10 companies per cohort.
Now, since then ConvertFlow has been installed in more than
20,000 websites serving brands such as Talkspace,
Nectar Mattresses, and Volkswagen as customers.
ConvertFlow has also reached 8.7 billion shopping events.
It's reached millions in revenue and this is
all while staying lean and bootstrapping our growth.
Best part, we've done all this while running on Postgres.
While I've got lots to teach you
on lessons that I've been learning those early years
of bootstrapping a Saas company
with limited time and limited capital,
my goal here today is to offer a high level overview of
those lessons that relate to scaling our database.
Let's get into it. Here's a story of how we scaled
our database from zero tracked events to just one million events.
Techstars Summer 2016, we hadn't
raised a seed round like
many of the other companies in our cohort.
It was just my co-founder Ethan and I just hacking away on our MVP,
launching quickly to validate demand
for our product, and to generate some early revenue.
Now, at this stage,
the common saying "premature optimization
is the root of all evil" really applies.
The highest leverage problems to be solving are
not yet the technical problems that SaaS company will
face post-traction, despite
what developers like myself tend to want to believe.
At this point, life or death of the company,
it means choosing the right market,
the right customer, solving the right problem for them,
and solving it in a way that
customers are actually going to pay for.
This meant for our apps stack,
we needed to optimize for convention over configuration.
Naturally, we chose Ruby on Rails to get started.
At this point the Rails ecosystem was very developed.
It was best practice to launch on
Heroku because they made it so easy.
What was Heroku's flagship database offering?
Postgres. Postgres was the perfect fit for us at this stage.
It works great with Rails,
which is the framework that we're most familiar
with and is easy to set up on Heroku.
Now in our eyes we thought we were just following
bootstrap or best practices, choosing Rails,
the popular framework for start ups at the time, Heroku,
the cloud provider that was most popular for Rails at the time,
and Postgres, which is Heroku's primary database offering.
But the truth is, our time was almost entirely
devoted to talking the phone with potential customers,
manually building solutions for their problems,
finding the patterns in their demands,
and then rendering all those learnings into a SaaS product that
marketers and agencies would pay us their hard-earned dollars for.
With a handful of customers who are running their reports in
the dashboard and less than a million events in our database,
it meant queries were nearly instant.
Scaling up our database at this early stage was very easy.
We would get notified by Heroku that we're
nearing our storage limit and we would scale up.
Optimizing queries against our events table was
more a matter of creating the primary indexes that we
needed to quickly query reports for
specific customers and their individual ConvertFlow campaigns.
What are the lessons from this stage?
Lesson Number 1, create
the right product before worrying about scale.
Once we found some early product market fit
and start to see our app scaling up a bit,
we were generating some early revenue,
only then did we need to devote
more time to optimizing our application's database for scale.
As you'll see throughout this talk,
the tech stack decisions that we made were subject to change,
and many of them did.
But we were able to make those decisions
as a result of real problems that arise
using real data from live customer deployments
rather than spending time solving for hypotheticals.
Now, this is especially important at
the early stage when most of your time and your attention
should be invested with working with your customer to solve
their problems and not an over-engineering your stack.
Lesson Number 2, because the objective at this stage is
to learn which product to build as quickly as possible,
the tech stack and database you choose
should be easy to deploy live.
It should be easy to reach some initial scale with.
It should not cost an arm and a leg and
ideally be open source in
case you need to migrate [inaudible] later on.
Now for our database, the answer was Postgres.
However, if at this stage you're more familiar with
another database technology that meets your criteria,
possibly due to your past work experience,
then by all means, use your preferred database.
Though if you're not sure,
I suggest using Postgres because it's
easy for early stage companies to set up,
it's easy to deploy in
today's cloud infrastructures, it's open source,
it's got a great community and what's great is
it powers lots of companies operating at a massive scale.
You can stick with it long term.
Onto the next stage, scaling from
one million events to 100 million events.
At a million events processed,
we were confident that we had
the stack that we needed to grow 100X,
well, we were wrong.
As we landed a few larger brands with
traffic totaling around one million monthly unique visitors,
our customer facing reports started to slow to a crawl.
Customers started complaining about
time balance when viewing the reports,
and so performance notifications were filling up
our inboxes and simply scaling our Heroku,
Postgres database was just not solving the problem.
Now, in hindsight, it wasn't
working because as our events database grew,
the amount of data that we needed to bring into memory for
each query was far too large for a single node database.
Of course, we didn't understand this.
At this point, we had a burning problem that we
needed to solve ASAP.
Our app had become unacceptably slow for customers.
We were landing larger websites with larger workloads each week,
and that meant each day
things were just growing more and more chaotic,
so we started looking for other solutions.
Now, shopping for an analytics database
is really intimidating for an early stage startup.
Each vendor we found online looked far too enterprise
for startups surviving on accelerator pre-seed capital.
Using the cloud credits offered as perks with Techstars
possibly meant configuring far too much on AWS,
Azure or Google Cloud than we had time for,
with Demo Day approaching really soon.
We quickly figured out that
our Google Cloud credits applied for Firebase.
In an attempt to put out the timeouts
that we were seeing with our app,
we started writing events to Firebase alongside of Postgres.
Now this is because we found that with Firebase,
the queries were a little bit faster than we had time,
and it didn't cause the rest of the app to come to a halt
each time one of our larger customers would write a report.
While this was less than
ideal solution, by writing and carrying events to Firebase,
we temporarily put out
the fires and we survived to the end of Techstars,
and we had a successful Demo Day.
Now onto the next stage,
how we scaled from 100 million events to a billion events.
At 100 million events,
we thought our hacked together stack of
Postgres plus Firebase was ready to scale 10X or more.
Once again, we were wrong.
One day I got this notification from
Google Cloud that we were running out of credits.
I started poking around
their dashboard to estimate what our costs are looking like,
and let's just say the number made my heart drop once I saw it.
Our Firebase solution had patched the problem,
but we blew through our Google Cloud credits.
Now we were facing a massive bill and
unsustainable margins unless we found a long term solution,
enter Citus or Hyperscale (Citus),
as it's now call on Azure's platform.
As we were facing imminent start up death by
a Google Cloud bill that was coming
next month because we ran out of our credits,
I came across this article by Heap Analytics about how they scaled
their Postgres database significantly
using a Postgres extension called Citus.
It made it really easy for them to shard
their analytics tables by a customer tenant ID column,
which resulted in much faster queries.
Now, speaking about Heap Analytics,
I believe Matt Dupree at Heap is giving
a talk at Citus Con right now as well,
so make sure to check them out.
Once we saw they were using Citus
and because we were already using Postgres,
we were ready to drop Firebase in an instant.
I reached out to the Citus Cloud team,
and within a couple of weeks,
with the help of Citus,
we had migrated our Postgres database off of Heroku,
we had sharded our analytics tables by website ID,
and we had upgraded from
a single node database to a cluster of nodes
consisting of a coordinator node and
multiple worker nodes, so all as a result.
Our queries were about 2-4 times as fast
as they were on our hacked together Firebase solution.
Even more critical at the time,
Citus Cloud's managed Postgres service
was a far more predictable rate
that we could actually afford
while maintaining great margins for a SaaS company,
which we had decided to contain to
bootstrap to profitability at this point in time.
>> Time for our lesson's recap.
Lesson number 3, expect parts
of your stack and your vendors to change with scale.
Going from a million to billion events
was a really challenging time for us,
but we were grateful to have a chance to
solve problems that resulted from early product market fit.
Well, we made some incorrect tech stock decisions
along the way, without a doubt.
Because there was a strong demand from our customers and lots
of really clear indicators driving our decision making,
we were able to adjust our stack based on real data and
migrate to a vendor that was
the right fit for us, and that didn't break the bank.
Lesson Number 4, we learned that scaling
analytical workloads benefits from
sharding tables by customer tenant ID column,
and distributing the workload across multiple database nodes.
Now doing that is typically very hard for early stage startups,
but Citus makes it really easy when running on Postgres.
On to the next page. As we cross
a billion events with our stack simplified,
and our database optimizations now focused only on Postgres,
we learned there was some other helpful optimizations
we can make to keep the reports fast,
and so we can scale to billions of events.
Lesson Number 5, the first low hanging fruit was to adopt
a rollup strategy for
our most commonly accessed reports in our dashboard.
By incrementing lifetime statistics for campaign use,
conversions and conversion rate reports,
we could just fetch
a pre calculated report to visualize the totals for
the customer's campaigns in a single page
without having to make dozens of
separate queries to the events table.
Now we're taking this even further by
incrementing daily statistics for each report.
Visualizing a chart with stats for
30 days is reduced to adding up statistics from
just 30 rollup table rows rather than
querying millions of individual events.
Lesson Number 6.
We started clearing out old stale data from hot storage.
With the lifetime and total statistics
being recorded with our roll up reports,
we realized with the more granular reports,
customers needed to see statistics for recent events,
maybe up to a year or so.
We realize we didn't need to keep
historical event data in hot storage forever,
so we started periodically clearing out event data,
no longer being frequently accessed.
What we found has not only helped us optimize our margins,
because our storage needs were no longer growing exponentially,
but our reports also became slightly faster,
because we were working with less data in hot storage.
Lesson Number 7, the other major
optimization we found was mitigating index bloat.
Now, because Postgres versions
introduce improvements to indexes over the years,
we found that re indexing
our tables greatly reduce the storage impact,
making maintenance operations easier
and improving our margins a little bit.
Now, when using Citus,
re indexing distributed tables happens a lot faster because
each table shard reindexes independently.
Now a caveat to look for when
reindexing production database tables.
When performing a reindex,
make sure to reindex the table CONCURRENTLY.
We learned this the hard way,
the first time we went to reindex
the table without the CONCURRENT part
In the query added, which locked up one of
our production tables for a short period that felt like an eternity.
As we scale to billions events,
it's not been without challenges.
Which brings us to lesson Number 8.
We eventually found that our events table ID primary key,
was going to hit the 2.1 billion max
value limit of Postgres's integer column,
which Rails used by default for ID primary keys
at the time that we created our tables.
Now, we worked it with Colton Shepard at Citus,
who's also presenting at Citus Con,
now as a customer,
so which is how we strategize
a minimal downtime migration to the big integer
column type for our analytical tables primary keys.
Now, how we did this within a 30 minute maintenance window
is nicely documented on the Azure Community site for reference.
Just search Citus ConvertFlow
and the article should come right up.
To offer a high-level insight,
how we converted the ID column to
big integer for billions of rows.
We created a new big integer ID column.
We indexed it and then we backfilled
values for the new big integer column with the same values
in the original ID column.
Then we switched over to the new column.
The cut over time for this only took
about 20 minutes during which we held
all incoming event requests in
a big job queue to be processed after.
To dive deeper into this,
Robert Treat has a presentation of
this year's Citus Con that
dives deeper into big integer conversions.
Make sure you check that one out as well.
Now while we are making adjustments for this new stage of scale,
Citus Cloud had been acquired by Microsoft and it created
a new offering on Azure called Hyperscale (Citus).
In 2021, once again we worked with
the Citus team to migrate our Postgres database
off of Citus Cloud's original AWS-based offering to Azure.
This was painless and done in just about 10 minutes.
They did this by streaming changes to a replica database on Azure
and then having a switch over to
the Azure connection stream in
a maintenance window that we had scheduled.
Once we switched over to Azure,
we had all the same features that we had
with Citus Cloud's original managed Postgres service,
along with the full support of the Azure platform.
Here we are. So far,
Postgres and Citus have supported us as we
process 8.7 billion shopping events at the time of this talk,
and with the new Hyperscale (Citus) offering in Azure,
we can continue to scale our database
horizontally by adding more worker nodes
as our customer base grows,
meaning Postgres will support us for years to come.
Let's recap all the top lessons for you to take away today.
Lesson Number 1, in the beginning,
don't worry about scale just yet.
Focus on creating the right product.
Your tech stack can change.
You can migrate vendors in the future,
but if you don't get early traction,
there won't be a startup to solve
scale challenges for. Lesson Number 2.
We found that Postgres is
a great database for start ups to start with and to scale it.
It's easy to work with,
it's easy to deploy.
It's got a great community, it's open source,
and it's helped lots of companies reach a massive scale.
Lesson Number 3, expect
parts of your stack and your vendors to change with scale.
As real data comes in,
you have a crystal clear idea of what
is and what is not a hypothetical problem,
so that way you don't prematurely optimize too much.
Lesson Number 4,
when scaling applications with analytics on Postgres,
sharding tables by customer ID and
distributing the data across multiple database nodes,
is key for maintaining high performance at scale.
The Citus Postgres extension makes this very easy,
so I highly recommend that. Lesson Number 5.
To performance optimize frequently access reports,
use roll up tables and incremented counts to
pre calculate statistics that display in your dashboard.
Lesson Number 6,
clear out old stale data that is
infrequently accessed from hot storage. Lesson Number 7.
Monitor your index bloat and reindex tables to reduce it.
Also, make sure to reindex your tables CONCURRENTLY,
otherwise, expect your table to lock up like ours did.
Then the last lesson here, lesson Number 8,
audit your tables for integer value limitations and
migrate them to big integer before reaching the maximum value.
At this point, there's documented cases in
this kind of big integer conversion online,
and you can find ours on the Azure Community site
if you search ConvertFlow Citus together.
That's it. I hope these lessons serve you
well as an early stage CTO engineer.
Whether you are growing your applications on Postgres or not.
With that being said, if you're interested
in building and scaling SaaS applications,
come join us at ConvertFlow, we're hiring.
If you have any questions, just drop
a line in the Citus Con Discord.
You can also reach me at
jonathan@convertflow.com. Thank you so much.