Now, let me welcome Kevin, no, yes, Kevin, sorry who we'll talk about electric SQL,
query driven sync in tanstack DB, let's welcome him with a round of applause.
Hello everyone, I'm Kevin, I'm finding engineer at electric SQL,
and a part-time post-doctoral researcher at the very university that Brussel.
Today I will be talking about query driven sync in tanstack DB.
So many applications still use a traditional web architecture,
so you have the application, the API and database,
and the API in the database, live on the backend,
and the application makes HTTP requests to the API,
in order to read and write data,
the API then reads and writes to the database,
the database returns some data to the API,
and the API returns the data to the application.
Now, the fontent of the application typically uses a reactive UI library,
such that anytime the local state changes,
this is automatically reflected in the UI.
The problem is that this reactivity stops at the network boundary,
so whenever the local state changes the UI is automatically updated,
but the local state does not automatically remain in sync with the backend state.
So if you want the local state to remain in sync with the backend state,
you typically end up implementing some long-pulling mechanism,
or a custom mechanism on top of web sockets or SSE.
So many people in this room are local first experts,
and probably have a better architecture in mind,
so you're probably thinking about the local first architecture of course.
So let's copy all the data to the front end.
So we still have our application and a database,
but now we also have a sync engine.
So the idea is that the application will keep a local cache of the data,
and initially of course that local cache will be empty.
So we need to populate that local cache,
and so we will start out by doing an initial sync phase.
So we do an initial sync and the sync engine will then query the database
to load the data, the database will return a partial data set,
and the sync engine will then return that data to the fontent,
where it will be used to populate the cache.
And so once we have loaded all the data in the local cache,
we also have a background sync mechanism,
which ensures that the local cache remains in sync with the backend.
And so now that we have our local cache,
the application always reads and writes from and to the cache,
so all the interactions feel instant.
So this leads to great user experience.
Now this local first architecture also has a couple of downsides.
The first one is that we now have a sync engine,
so we have to rewrite the fontent and the backend around this sync engine.
As I explained, we need an initial sync phase to populate the cache,
and this initial sync can be quite slow if our data sets are big.
Also, if the local cache grows and becomes very big,
then querying that local cache can become slow,
and the UI becomes laggy.
So if you want to avoid that,
we need to index the local cache,
we need to do some optimized querying and so on.
So we end up basically building our own client site database,
and this is truly a rabbit hole.
So what is it that we actually want?
We want our application to have great UX and great DX.
So great UX means that the application needs to be fast,
there should be no loading spinners and so on.
So we already know that in order to achieve great UX,
the data must be locally available.
On the other hand, we also want to achieve great DX
such that the developer simply needs to declare
which data they need for their view,
rather than how to fetch that data.
So we don't want developers to have to write networking code,
caching code, thinking code,
conflict resolution code, and so on.
So the main challenge here consists in how are we going to sync data
just in time, so it's available when the user needs it,
without requiring the developer to be a magician.
And this is exactly what led us to develop a text like DB.
So 10-StackDB is a reactive client-site store.
It has backend-agnostic collections
and incremental queries over those collections.
So you can think of 10-StackDB as a client-site database.
So it's a reactive client-site store,
meaning that whenever the data in the store changes,
you'll UI automatically reflect it,
so it's integrated to view, react, and so on.
It has backend-agnostic collections,
so the collections hold your data locally,
and it's backend-agnostic, meaning you can populate it from any data source.
So you can even populate it from your existing REST API,
a GraphQL API, SyncEngines, whatever.
It has incremental queries over those collections,
so you write queries like that are very SQL-like.
And whenever the results of the query change,
it's incrementally computed, so it uses IBM,
so in terms of view maintenance to do that.
So let's take a look at an example app,
so we all know this example app,
it's an issue tracker like GitHub or Linear,
the typical example app, so we have projects,
projects can have issues,
can have comments.
So if we already have such an app,
we probably have a frontend that looks like this,
so we can have function-get projects,
get issues or get comments,
which basically just makes a get request
to our existing REST API, right?
In this case, we are just loading all the projects issues
or comments, because we saw before,
in a local first world,
it's nice to have all of that data locally.
So we also want to be able to,
of course, create projects issues and so on,
so this is just a create project very easy,
we'd make a post request to our existing API,
and the project is now created.
So, up till here, there's nothing special,
but it's like 99% of all apps do this kind of things,
you already have this code.
So if you want to incrementally start adopting
the first thing you're going to do is to define
some data collections.
So we can define a collection with create collection here,
and it's going to hold all our projects.
And we already have an existing REST API,
so we want to basically populate the collection
from that REST API.
And to do that, we use this query collection option,
so it's going to be a query collection,
and the query collection is basically using a ten-step query,
so I'm sure many of you know that,
it's just a way to query your existing REST API.
So you have to provide a query function here,
which is a get-projects function from before,
so it's just a regular get request,
and we have to provide these three handles.
So it only insert on update on the lead,
and basically whenever somebody locally insert
or updates or deletes a record in a collection,
it's going to call this handler.
And so this handler is just going to call
create projects here,
which makes a post-lickers to the backend.
So this query collection is going to load
the data from our existing REST API,
and it's going to periodically pull it
to keep the collection in sync with the backend.
So you can define some more collections,
the issues collection, and the comments collection,
and basically what I want you to show here,
what I want to show you here,
is that the collections wrap your existing API,
nothing special, no backend changes needed, very easy.
Now that we have these collections,
we can define some views over them.
So imagine we want a view listing all the issues
in a certain project.
Well, we can write a live query,
so the query basically reads from the issues collection.
It filters out because it only wants the issues
on a certain project, and only in a certain status, for instance,
and it orders them in a reverse chronological order.
We define two dependencies here,
so whenever a project idea of status changes,
this query is going to be a recomputed.
And I find that even we have the query results,
it's bound to this live variable,
and so whenever this query results change,
the view is here and not.
So you can also write a issues detailed view,
so here we show the issue details,
and the comments on it.
Again, we write a live query,
we read from the issues collection,
we filter to only the issue we're interested in,
and here interestingly, we join it,
we left join it with the comments,
but only the comments on this issue, right?
And then we order them in chronological order.
So again, anytime the issue already changes,
the query is going to be completed.
So what I want you to understand here,
is that we simply write a SQL-like declarative query,
and we can do quite complex stuff,
like filters, turns, ordering,
and all of this is done efficiently for you.
So internally, it tends like the B keeps on indexing,
optimizes your queries, and so on.
So you don't have to worry about that.
So one more thing, we may want to write,
for instance, the comment on an issue,
so how do we do that?
We have an ad comments function here,
which is just going to call insert on the collection,
and insert that record.
As I said before,
each collection defines these handlers.
So when we insert here locally,
it's going to, in the background,
asynchronously call this handler,
which is going to create it on the backend.
So this diagram,
exemplifies this, your writes are to the local cache,
your UI reflects it immediately.
In the background, we make an API call,
which is going to write into the database,
you get a response, and the write is confirmed.
At that point in time, the front and nose,
that write went through everything's fine, it's confirmed.
So we saw these kind of queries,
which are quite complex,
and I thought you don't have to worry about it,
and they will be efficient.
So how can those be efficient, right?
So we certainly don't want to recompute the query
every time the underlying collection changes,
because computing a query with joins,
etc., is very expensive.
So instead, we use a technique called
incremental view maintenance.
So instead of recomputing,
we basically keep track of the previous query results,
and we also know the delta,
so the change for instance,
and insert or delete or whatever.
Based on that, we can incrementally compute
the changes to the result of the query.
So to give you an intuition here,
imagine you have an order query like this one,
and you have the previous result,
then you already know they are ordered, right?
And then if you have a change,
like insert a comment in this list of order comments,
well, you can do it very efficiently,
because you can use binary search to figure out
where to put it inside,
and that's basically in logarithmic time,
and that's very more efficient and sorting,
which is in and look.
So the key here is that
incremental view maintenance allows
of sub query milliseconds query execution
even on large datasets.
Then there is one final problem here,
that remains,
and that's the initial load times.
So we have these collections from before,
and as I told you,
we need to operate them with initial data.
The data can be big,
and so the user is stuck on the loading screen
while the data is loading.
But maybe the user just opens the app
to scheme a few issues, right?
So it's a bit silly that we are waiting to load all the data,
if they just want to see a few issues.
So in some cases,
you don't want to load everything in front,
and you just want to load it lazily when the user needs it.
So PENSEGDB has three different sync modes
for the collections,
eager modes on demand and progressive.
So eager mode is a default,
if you don't specify any,
it's going to be eager,
and it loads all data up from.
So that's best used for small datasets.
The on demand mode is best for loud datasets
because it's going to load the data
only when it's needed by query.
So if a query needs a data,
it's going to check what data does it need,
and it's going to load only that data.
And then progressive mode is kind of a combination of both.
So it starts out in the on demand mode,
such that you are not stuck on a loading screen,
and then in the background,
it does a full initial sync.
So when the initial sync is completed,
it switches back into either mode.
So that's best for medium datasets,
where basically you don't want to wait
for all the data to load because it's too big,
but you can still fit it into memory,
so you do it in the background.
So on demand mode is basically what we call
period-driven sync,
because the query expresses what data is needed,
and the system fetches it and the query needs it.
So where would you use period-driven sync?
Well imagine you have a list of issues,
which is very big, right?
So typically you're going to paginate that list of issues.
So you can just change your query a bit to be paginated.
So you say, okay, I want only limited amount of results,
namely the size of the page,
and I want to start reading from the offset
where the page starts.
So that's basically the only change you need to the query,
and then in the collection,
you don't need to forget to put it on demand mode.
So because the collection is on demand mode,
it starts out empty with no data,
and then when the user opens the app and goes to the first page,
it will at runtime load that page.
Then the user can scroll down,
click through to the second page,
it's going to load the second page and so on.
So I hear you that you're here like, okay,
but now we are going to load data at runtime,
so we are going to be again stuck on a loading screen, right?
But you can avoid it if you are smart about when to preload the data,
but if you use, for instance, the Amazon app and your browsing for items,
it looks like an infinite feat of data.
You can keep scrolling and you never see a loading screen.
And so of course, Amazon doesn't load everything at once,
I'm in a phone, it just loads the first page,
and when you're almost at the end of the page,
it's going to pre-fetch the next page, right?
So by the time you're at the end of the first page,
the next page is already loaded.
And you can also do that with TENSECDB,
so you can basically preload a live query
so that you can get this infinite scroll experience without spinners.
One technical detail is that I told you that TENSECDB is back in TENSEC,
so how can TENSECDB then load data from your back,
and if it doesn't know how your back and looks like, right?
So every back-end integration needs to implement a certain interface,
and the key here is this load subset function that needs to be implemented.
So this load subset function, TENSECDB can call it
with an optional workloads or a bike loss and limit.
And it's basically TENSECDB saying, hey, please load this data
for me, so nothing special.
Up till now, we've been using our existing rest APIs,
so the TENSEC query integration.
And so the TENSEC query integration looks like this,
it's highly simplified, but the gist of it is here.
So load subset is going to fetch the query function from the collection.
It's going to call it with the option,
so a warehouse, potentially an older bike loss and a limit.
And then it's going to wait for the query function to load the data,
and when it has a new state, it possesses it in the collection,
so it updates the collection.
So we are basically passing these options through to the query function,
because TENSEC query itself is a gnostic to the API of your application.
So what does that mean as a developer?
Well, you wrote this live query in on the management,
and you have this workloads and this limit,
how inside your collection, inside your query function,
you have to handle the workloads and the limits here.
So you use the workloads to compute the project ID,
you use the limit to compute the page number,
and you make the right API call.
And so of course, this starts to become a bit annoying,
if you have to translate these complex queries at your right.
So this may be a good time to get rid of your traditional rest API,
which I think engine instead.
So what we envision here is that your application is still going to do local rights,
and in the background propagate and through your existing rest API,
so the right path still goes through your API,
but the read path goes through the sink engine,
so the data loading and the sink engine.
So once you have the sink engine,
the only change you need to do is to swap your query collection
for a electric collection.
And that's much easier because you just say,
hey, here's the URL of the electric API,
and I'm interested in the issues table.
So no more translating queries into rest API calls.
Okay.
Now it also becomes really real time,
so before that we're using a dense query,
so it was pulling your beckons.
Now you're using electric, so updates flow in real time to the front end,
and no other changes are needed to the UI components or the live queries.
I don't know how much time I've left.
One minute, perfect.
So a colleague of mine, Samuel is some of you may know him,
has developed his great demo app,
so it's a collaborative cat app.
So on the left, you had projects and projects have files
and files can be part.
You had an AI chatbot, so he asked a chatbot to build a new part.
Now he's updating that part,
and you see that the user on the right is basically
following in real time the changes,
so everything's things automatically.
He's asking the AI to make an extrude here.
There's just that.
And you see that there's even a presence marker
that shows what's in is doing.
So all of this is built on top of dense.
Electric, SQL, and YGS.
So to conclude,
I'll then stack the B enables for local first steps.
You have great UX and DX.
So great UX is achieved by the creative and sync mechanism.
In combination with some smart preloading,
meaning that the data is ready when the user needs it.
Our IBM engine internally enables the queries
to be very efficient, so your apps are fast and snappy.
It also achieves great DX, so as a developer,
you just write declarative queries,
known as working caching of syncing code.
Good, needed.
And most importantly, it integrates with your existing stack,
because we have modular backend integration.
So you just pick the right integration for your stack.
So yeah, this concludes my presentation.
Thank you for your attention.
I will take any questions.
Thank you for your presentation.
Very interesting.
We have five minutes for questions.
Please raise your hand.
Do we have a question on the internet?
So, a question there.
Thanks for your great talk.
Do you envision that it's possible to have the sync server
be blind that it's engine encrypted over the sync server,
but the database and clients can decrypt of course?
Yeah, I mean, the sync server here,
just as many tools before, it just propagates updates.
So I mean, it's not doing anything fancy,
like conflict resolution where you need to know the data.
So it can be agnostic to what the data actually is.
It just needs to know to whom it needs to sync it to.
Yeah.
Hello. Can you hear me?
Yeah.
Yeah. So how fancy can pan stack the big queries be?
And did you have to deal with the problem of joy and algorithm,
where you have to, when sometimes, if you do the wrong journey decision,
it's slow.
Yeah.
So your first question was how fast can this increase?
How complex?
Well, it's a sequel life, right?
So you can have filters, joins, aggregation, ordering.
It's quite complete.
And your second question was about the joins, right?
Yeah.
So we try to optimize the joins.
We use, we compute them incrementally,
based on this incremental view maintenance algorithms.
So they are extremely fast, but of course,
if you write a very bad join, that's going to be optimized,
then we cannot do anything about it.
Good answer.
Thank you.
Yeah.
You showed the example with RESTPI.
I wanted to know more regarding integrating with GraphQL.
Yeah.
So as I showed around the end,
it tends to be modular, so it's also open source, of course.
So you can write your own integration.
So currently, you have the tends to create integration.
We have electric integration.
We have several other sync engines.
We don't have GraphQL integration yet.
So you could write your own integration if you want,
you just need to implement a right interface.
So the one I showed was a bit simplified.
There are some other methods you need to implement, but it's very,
very achievable.
We have had many people just contributing their own integration,
so it's definitely feasible.
I'm curious, what is the relationship between electric SQL and
Tens Actib, both on a technical and an organizational basis?
Yeah.
So on a technical basis, electric SQL is just a sync engine
backend for Tens Actib.
On a more philosophical and business aspect,
it's on a philosophical aspect.
I mean, we just wanted to build this software,
because we believe all the sync engines and all the applications
that want to have sync needs something like this,
and we think like if you want to push the standards forward,
you need a common base where everyone can contribute to,
and at once, yeah.
And then on a business side, it's like,
well, let's do your best and hope to be the sync engine people
to use and listen to people, and we actually are the main
main trainers of Tens Actib.
Like, I'm almost full time just working on Tens Actib.
Yeah.
So Tens Actib have any server-side components, then or no?
We are working on server-side components, yes.
But there are some technical complexities around it,
so it's taking a bit longer than expected.
Great session of Q&A.
Let's thank Kevin again.
Thank you.