Show HN: Bi-directional sync between Postgres and SQLite

I dig it, super cool idea!

What about timestamps, though? SQLite doesn't support them.. does it end up being restricted to the lowest common denominator between the two databases in terms of supported types/functionality?

Co-founder (Conrad) here. SQLite does indeed have a narrow set of types. Timestamps in Postgres are converted to text in SQLite in a format that is compatible with ISO8601 and SQLite's functions. Type conversions are documented here: https://docs.powersync.com/usage/sync-rules/types

I see you're using ISO8601 and including timezone data, which seems best for generalizing.

Somewhat related: if you were storing UNIX timestamps that only need the resolution of a day and will always be UTC, would you use an integer for the UNIX time or just a shortened ISO8601 without H:M:S data?

I've been trying to decide on this for a feature in a tool I'm making. In either case, SQLite has the ability to convert back and forth, so it'd really be the exported JSON containing the data whose readability would be affected more than anything.

Personally I prefer the shortened ISO8601 format (YYYY-MM-DD). While it's not the most efficient, efficiency doesn't often matter on that level. It's unambiguous, can be parsed by practically any library, and easily human-readable.

I'd avoid using unix timestamps for day-resolution, since it's very easy to make a mistake with the timezone when parsing, which you only notice when changing between a positive and negative timezone.

Julian day is another option, but support for it is not quite as universal. If efficiency is important, that's the format I'd use.

What’s the ambiguity issue with Unix time stamps? Unix time stamps are expressed as a UTC offset always so I’m not sure how time zones come into play / what parsing might involved.

You can do it in a consistent way, for example always store the timestamp at 00:00 UTC. But that's not obvious just by looking at the values, which is why it can be ambiguous.

And when parsing those values, you could do for example (JS) `new Date(ts*1000).getDay()`. Of course that's not correct - you need to use the UTC methods. But it's easy to miss, and may pass all your tests until you switch the timezone.

Those aren't massive issues - you just have to be careful with the parsing and serialization, and you need to be careful in any case. It just explains my personal preference.

Not sure I follow. What's the difference between `new Date(ts).getDay()` when ts is an ISO8601 string or a ms since UNIX epoch value. Pretty sure the result is the same and whether you need to use `getDay` or `getUTCDay` is a display issue that affects both variants.

I could see it being more convenient if you're just dumping the raw values without any transformation or you don't know what data type is stored in a column, but otherwise the difference seems largely one of taste and not so much relative error rates?

You're right that you'll have exactly the same issues if you use Date with a string.

However, with a string date you can bypass the Date class completely in many use cases.

My experience with this is anecdotal, but I've ran into many bugs due to conversion between day-precision values and Date objects. One example was a date picker library that returned values at 00:00 in the local timezone instead of UTC, which were then not correctly converted to UTC-based values before being persisted.

Once again, these are all preventable issues - just use UTC everywhere. I've just seen issues like that happen enough that I prefer never converting between dates and timestamps if I can avoid it.

My experience with this is anecdotal, but I've ran into many bugs due to conversion between day-precision values and Date objects. One example was a date picker library that returned values at 00:00 in the local timezone instead of UTC, which were then not correctly converted to UTC-based values before being persisted.

Oh man, that sounds ugly, and an invitation for disaster. Lots of off-by-ones.

In my project's case, I also need to do math with them for analytical purposes. It's a game collection manager, so it's tracking the day you bought, beat, and 100%d a game.

I derive how long it took to beat a game by subtracting the UNIX timestamp of the purchase date from the beaten date's timestamp, then divide by (60*60*24) to measure days. It's handy to show games in the backlog and sort by purchase date, too, so you can target the games that've been sitting the longest. There are plans to graph out a collection in terms of its path from new to beaten to completed in a Gantt-style chart or something else fun.

But yeah, I just thought to ask because in JSON I and others can read `2023-12-01`, but not `1701417600`. SQLite and Python can do conversions for the calculations, and Python even has the timedelta module that might make my comparisons easier to do or slightly more accurate.

Edit: asterisks

when you do that, it will give you different days depending on your timezone. 1701388800 is 2023-12-01 in london, but still 2023-11-30 on new york.

Sure, but that’s also true for an iso8601 string unless you’re extracting things directly. But also you’re assuming that your iso8601 string is in local time which it may not be (eg string generated in London but accessed in New York). Localizing things to be time zone aware in a way that meets user expectations is hard because it’s a squishy domain specific UX problem, not a technical one so arguing about it like a technical issue feels like a wrong approach.

I think he's referring to behavior that some software has when it encounters raw UNIX timestamps without TZ information. I recall seeing a change like that in Python's datetime module as of late, so that even if UNIX timestamps are UTC by default, it's more explicit.

In the case of my project, the goal is to have UTC-only timestamps/dates, and since the resolution I'm using it for is a calendar day, time zone doesn't matter. I will still need to be explicit about it in my code when converting.

Or maybe edge cases like historical timezone changes, leap seconds?

By definition unix timestamps are UTC. Python's datetime module has a lot of footguns because naive timestamps can represent UTC, local time, or even a non-standard application-specific time. Python's aware timestamps just carry the tzinfo information along with the naive timestamp so that you can get to UTC from the abstract naive timestamp. It's a really overdesigned footgun of Python's datetime module more than anything, but again not unix timestamps which are always an offset from 1970 midnight UTC.

Cool, thanks for the clarification!

SQLite has a number of functions to work with time but the underlying storage format is just going to be--depending on what precision/range of timestamp you want--a float or an integer... to the extent, of course, that SQLite has types at all (as it frankly doesn't, thereby making this question kind of moot).

SQLite has five types, documented here: https://www.sqlite.org/datatype3.html

    null, integer, real, text, blob

It has a historically cavalier attitude to enforcing them (which I believe it inherited from TCL) but that changed in November 2021 with the release of strict table mode in version 3.37.0: https://www.sqlite.org/stricttables.html

JSON is a sixth type in practice due to the way `text` acts differently depending on whether it is fed directly from one JSON function to another.

See https://sqlite.org/json1.html#value_arguments

The JSON1 stuff in SQLite is incredible. Very easy to work with for the most part.

Ah! I mean, only two years old, but OK ;P. Regardless: my point stands that you can trivially store and work with timestamps in SQLite as they are simply a real or integer or text; like, the idea that you need a special timestamp data type or you can't work with such values in an engine that only two years ago got support for checking the types at all and encourages you to just write code is highly strange.

It has a historically cavalier attitude to enforcing them (which I believe it inherited from TCL)

Richard Hipp (inventor, principal dev of SQLite) called it “manifest typing”[0], indeed likely inspired by Tcl[1], of which he is a Core Team member emeritus, and of which SQLite was initially born, as a loadable Tcl package.

[0] https://www.sqlite.org/different.html

[1] https://wiki.tcl-lang.org/page/Everything%20is%20a%20string?...

I am looking for something like this. We develop IoT devices, thousand of linux computer running behind customer routers and have a central server with web admin, all in python.

I wish i could have "syncronized objects", something were a change in a device would be reflected back to the server, web interface even better and in the other direction too, a way to see the whole system as one thing, not a hodge podge of api, calls, update and so on.

Is there an elegant way to do this?

I'm reminded of RealmDB, which I think Mongo bought at some point

We have been using realm in production for a while and it seems to have steadily improved since moving to MongoDB (surprisingly for an acquisition). It is definitely best of class when it come to doing live sync between a backend and a local database.

Realm itself is amazing, fast and intuitive, and the bidirectional sync to MongoDB just works. The only downside is the messy and confusing web admin interface.

Out of curiosity, what kind of use case is yours?

Yeah they did acquire Realm

This is an interesting use case and sounds like it could be a fit.

What stack is the software that runs on the Linux devices?

Also feel free to shoot me an email if you'd like to discuss the use case privately (email in bio) or chat on Discord https://discord.gg/powersync

on the linux devices we have a web admin (made with node-red) that control the linux machine, network, config, updates.

Then the web admin run docker container, we have a python backend on the device with fastapi as the base and another with vue.js for the frontend (touchscreen)

Then the server is fastapi/mongodb/vue.js.

We have a continuous websocket from the python program to server and another one from web dashboard to our server admin.

Lot's of moving parts, lot's of problems, not a lot of cohesion and structure.

Thanks for the additional info. As another commenter mentioned, since you are using MongoDB, it may be worth looking into MongoDB Realm / Atlas Device Sync.

It's called RMM and there are tons of products that do exactly this. Most will be geared towards MSPs, but that's likely right up your alley as you probably want to silo customers into their own sites.

Yeah, during the IoT hype there was a (supposed? I never tried it) solution to this from every cloud provider.

I think Amazon and Google both deprecated it since then, not sure how Azure's is going.

Turso might be a fit

https://turso.tech/

Are you open to investors?

The company behind PowerSync is profitable https://www.powersync.com/company and is not currently actively seeking investment

Can I have a tshirt?

Yes!

Love to see this.

I couldn’t tell whether I could integrate my SQLite app with my own Postgres database? E.g. is it free to self host, and the paid services are a convenience? Or the cloud hostage is mandatory?

You can use any Postgres database as long as it supports enabling logical replication and PowerSync can connect to it. Details here: https://docs.powersync.com/usage/installation/database-setup

The PowerSync Service is currently a hosted cloud service. A self-hostable version is coming soon.

Ah thanks. Out of curiosity, will the self-hostable version be a paid product, or free to use? (No judgement, just want to understand the business model).

The initial self-hostable version will be available as part of the paid service, but there is also a free open-source self-hostable version planned for next year:

The PowerSync Service will move to an open-core model in 2024: An open-source version will be available (self-hosted) as well as a non-open-source version with premium features (available as both self-hosted and hosted cloud service)

The client SDKs are currently open-source (Apache 2.0 license)

So that means the pricing of powersync cloud solution will be just a bit higher or similar to cost of self-host, just with extra features, correct?

I'm asking in terms of ease of hosting & restrictions, whether it would be super hard like supabase self hosting + a lack of features or like appwrite where self hosting is very easy and you get most things in self hosted version as well?

To clarify, there will be two self-hostable versions:

- A paid self-hosted version, full-featured: This will be released in the next couple of months.

- An free open-source self-hosted version, with core features: This will be released within 2024.

We will aim to make the open-source version easy to self-host and want to make sure that it has a valuable set of core features.

Since the company seems to be answering questions I’ll give a few that come up for me. Congrats on the launch btw.

In the given design, am I understanding correctly that this means that a local commit could be seen as “committed” by the user but then later the server rejects it because of conflicts right? I guess it’s all application defined in that you could build your application in such a way as to show enqueued but uncommitted changes differently from the checkpoint, but it does mean that you could lose data if the application author doesn’t handle that well in their local code or the application server yeah? Not a critique because I think that’s true even for anything handrolled, but just making sure I understand the model.

Also, I’m a little unclear how it’s tied to postgres and what the powersync service is doing. The docs say that you fetch a JWT from your application to talk to the service but then it says that all writes are mediated by your own application code. So presumably the powersync service channel is for synchronization of Postgres -> local SQLite. Is that right? And the replication logic in powersync - is that essentially accomplishing horizontal sharding of the database for reads? Also, for the replication piece is the SQLite bit actually important or could you actually support arbitrary backends and SQLite is just convenient? Eg could you support browser LocalStorage instead of WASM SQLite or is there some piece of functionality of SQLite you’re relying on?

Finally, do you have any support for lazy local hydration / eviction? Eg if I have a Google docs like application, is it synchronizing my entire account at all times or does it support pulling a document and evicting LRU documents when some local storage limit is exceeded?

In the given design, am I understanding correctly that this means that a local commit could be seen as “committed” by the user but then later the server rejects it because of conflicts right?

it does mean that you could lose data if the application author doesn’t handle that well in their local code or the application server yeah?

Yes, this is correct and the backend developer needs to make sure that conflicts are handled appropriately (I went into a bit more detail on this in my other comment in reply to langarus which you may have seen)

So presumably the powersync service channel is for synchronization of Postgres -> local SQLite. Is that right?

Yes, this is correct.

And the replication logic in powersync - is that essentially accomplishing horizontal sharding of the database for reads?

Assuming I understood this question correctly — yes, the PowerSync Service handles the complexities of dynamic partial replication of the database to different users. In our announcement blog post we wrote a bit more about the trade-offs and design considerations: https://www.powersync.com/blog/introducing-powersync-v1-0-po... (see section "A scalable dynamic partial replication system")

Also, for the replication piece is the SQLite bit actually important or could you actually support arbitrary backends and SQLite is just convenient?

We do currently use a few different features of SQLite, but something that we are considering is making the client-side more database agnostic and potentially supporting more local database options (details TBD).

Finally, do you have any support for lazy local hydration / eviction? Eg if I have a Google docs like application, is it synchronizing my entire account at all times or does it support pulling a document and evicting LRU documents when some local storage limit is exceeded?

It is possible to accomplish some of this kind of functionality using PowerSync's Sync Rules. It should be possible to design the Sync Rules such that flags are set (e.g. based on LRU) which would trigger certain rows to be synced/un-synced.

I think my co-founder (matharmin) may also want to weigh in with more detail on some of the answers. We are based in different timezones so we may reply with more information in a few hours.

Yeah I figured that given the static nature of declaring the sync rules ahead of time, dynamic control over what is synchronized may be tricky. You mention using a flag column, but wouldn’t that mean that you’re generating writes for reads to update that flag/timestamp? Or can you choose to have extra local columns that aren’t aren’t part of the replication data?

Note that dynamic control from the client-side over what is synced is currently supported to an extent via token parameters from the client, and this will potentially be expanded in the future.

You mention using a flag column, but wouldn’t that mean that you’re generating writes for reads to update that flag/timestamp?

There may be other ways to solve this, but the solution that came to mind was:

- Set a "last accessed at" timestamp on the client when the user opens a specific item. This would sync to Postgres.

- Have a recurring task on the server-side that updates items in Postgres based on "last accessed at" and sets a flag that causes an item to be de-synced for that user once the elapsed time exceeds some threshold

Persistent local-only columns are not currently supported. Local-only tables are currently supported.

Also, for the replication piece is the SQLite bit actually important or could you actually support arbitrary backends and SQLite is just convenient? Eg could you support browser LocalStorage instead of WASM SQLite or is there some piece of functionality of SQLite you’re relying on?

SQLite is supported practically everywhere, so that's our first choice. We also modelled our sync rules on SQLite's type system and functions to a large extent.

However, there is nothing technical tying is to SQLite. The sync protocol works purely with JSON, and doesn't impose any restrictions on how the data is stored or queried.

For the JourneyApps platform where PowerSync was originally used, we actually have an implementation on top of IndexedDB for browsers, which ends up being more lightweight. It needs more work before we can expose it as a general library, so we just started with SQLite for now.

It is amazing how often I sit with crappy or no connectivity, even in today's day and age, and so if it were easier for more apps to be designed to work offline first that would truly be amazing.

Exciting development PowerSync team, good luck and Godspeed!

Yeah it's a shocker that even major apps like Apple's Weather aren't local first. Lots of room for improvement.

Why would you want a local first weather app? It's not very useful to have yesterday's weather or even from one hour ago.

Great question and that's what you'd think. I spend a lot of time in the outdoors though, and on day 3 of a camping trip into an area with no signal, I'd rather have a stale prediction for the day 4 weather than no prediction.

Got it, thanks.

Looks a lot like a light version of ElectricSQL (https://github.com/electric-sql/electric). Why should I use PowerSync instead?

A detailed comparison can be found here: https://www.powersync.com/blog/electricsql-vs-powersync

Hi! I'm not familiar with either engine, but I'm confused with the text in the comparison (see below). Couldn't ElectricSQL be used within the backend to enforce policies or am I missing something?

Writes are made directly to your Postgres database via the Electric sync service (bypassing your backend application),

ElectricSQL's architecture is documented here:

https://electric-sql.com/docs/reference/architecture#primary...

https://electric-sql.com/docs/reference/architecture#data-fl...

The docs explain how the Electric sync service publishes a logical replication stream that the Postgres database subscribes to. The Electric sync service is responsible for validating permissions based on the DDLX configuration.

Yup, this is correct.

We have had some requests / discussions around adding hooks to the sync service that will support custom logic on the write path (as per https://github.com/electric-sql/electric/discussions/565). This seems like a good idea but they don't exist yet.

Let me throw SymmetricDS into the ring: https://symmetricds.org/

In my opinion it is quite underestimated giving its various cross platform and cross product sync capabilities. My first experiences with it are quite promising.

Thanks for sharing. Sometimes I feel had it not been for discussion and community at HN, I wouldn't find such stuff.

You stole my words! Thank you again for sharing this option. I just knew about big brands OracleGate , Qlik, IBM IIDR etc but this is interesting. I hope somebody shares the experience with the Pro version and how was the support experience.?

Thanks for the link, I haven't come across SymmetricDS before. The list of supported databases is impressive.

It appears to be primarily aimed at server-to-server replication. It does support Android and iOS, but I don't see much documentation on that. Do you know if it works in practice to implement offline-first applications?

Sorry for being a noob but is this "tech" similar to the rails offline video linked or am I misunderstanding? I don't get why you would need a service instead of building it into the webapp/site?

https://www.youtube.com/watch?v=Gj8ov0cOuA0

I just scanned through the video, but it seems to focus on persisting client-side changes locally when offline, which only covers one part of the bigger problem.

Difficulties quickly come in if you want to: 1. Persist large amounts of data on the client. 2. Keep the data in sync incrementally (too much data to re-download every time). 3. Keep the data up-to-date in realtime (streaming changes). 4. Keep the data consistent, especially across multiple tables / types.

Many "offline" solutions are actually closer to caching rather than offline-first, which introduce all the issues associated with cache invalidation.

Also, if you're just working with a single table, you might get by with just using updated_at timestamps and soft deletes to be able to get incremental changes from the server. Even then you need to be careful with consistency issues, e.g. making sure timestamps are always in order and without duplicates. And if you start adding more tables, the complexity quickly increases.

thank you for pointing that out, yes. I just saw the video uses indexedDB (probably suboptimal although mdn says its ok for storing complex data on the client) and also its just for 'bridging' no internet access when doing input on the device. thank you for taking the time to answer, I see a bit clearer now.

IndexedDB is fine - just use some wrapped that makes it easier to work with. Our current SQLite implementation actually stores the underlying data inside IndexedDB - mostly due to a lack of better options (but that's busy changing with OPFS).

The difficult parts are mostly related to keeping the local data in sync with the server, whether that uses SQLite, IndexedDB or some other database.

This looks interesting! How does this compare to ElectricSQL[1]? Next to not using CRDTs.

[1] https://electric-sql.com/

Hey, James here from Electric. Congratulations to the PowerSync team :)

As a sibling comment says, PowerSync actually wrote up a comparison here https://www.powersync.com/blog/electricsql-vs-powersync

Aside from Electric being open source and PowerSync a proprietary service, the primary difference is in the programming model on the write path.

Electric provides finality of local writes. So once a non-malicious write is accepted locally, it is final and won’t be rejected by the server. This simplifies the programming model and means you don’t have to code for rollbacks.

PowerSync is a server authoritative system. Local writes are tentative and can be rejected at the server. You run an API on the write path and write logic to handle conflicts and rollbacks.

The different approaches come with different trade offs, both operationally and in terms of the programming model.

On the topic, if interesting, we have a list of alternative projects here: https://electric-sql.com/docs/reference/alternatives

Thanks for chiming in! ElectricSQL is great - we see it come up a lot in discussions since on the surface it solves the same problems (syncing between Postgres and SQLite), despite the details being very different. I love seeing all the innovation in the offline-first space!

Hey there's a comparison of the two here https://powersync.com/blog/electricsql-vs-powersync

Thank you - it’s great to have an alternative to electric-sql.

I actually need a local first, disconnected framework. What many people don’t realize is that even in the US, there are many parts without any network connectivity like our parks and preserves. Rangers still need to make queries in those areas.

You can try Watermelon DB. It is local first disconnected framework. And it has a sync framework as well, but you have to create your own backend (using any DB) for syncing. Stable product and works great

https://watermelondb.dev/docs/Sync/Intro

Hi everyone, I worked with watermelonDB and recently switched my entire project to powersync. Watermelon has its limitations for an offline applicationFirst, if you really want to make use of the application completely offline, you will have to build a synchronizer like CRDT by hand and manage the request queues. With powersync all of this is managed by them, and with a simple code I can choose which information from the database I will sync for each user. In my first tests with WatermelonDB, synchronization proved to be unfeasible due to the amount of synchronized data. In short, Powersync has proven to be a wonderful tool that has allowed my company to move forward with offline services.

You can find more alternatives here: https://localfirstweb.dev/

Sorry, unfortunately I am a bit lost on what this does (on my phone, so couldn't see the demos on the website). I'm an android developer, but I am not sure what this would do. Do you have an example of an app/backend at which you can explain where Powersync helps?

The goal is basically to help you build offline-first apps. In the offline-first paradigm, app code works directly with a client-side embedded database, which automatically syncs with a backend database in the background. This is in contrast to cloud-first apps which primarily use a cloud datastore via APIs.

Working with a local database (e.g. SQLite) means that apps feel instant to use because of low latency, and remain functional when the user’s network connection is unreliable or offline. Also, offline-first typically offers built-in real-time collaboration by automatically syncing data in the background.

There's 1-minute demo video here of a Flutter "To-Do List" app showing some of these concepts: https://www.youtube.com/watch?v=VTx5ViRe3HY (this demo uses a Supabase Postgres backend for simplicity)

Oh that's very interesting. I guess it's easier to deal with "only" sqlite than to have a store, keep track of mutations, and then upload/download the mutations when the device is reconnected.

For sure, working only with a local SQLite database does simplify state management in many cases. For the most part you don't need an additional state management system/library.

Meta had an interesting article a few years back about how they rewrote the Messenger app to use SQLite: https://engineering.fb.com/2020/03/02/data-infrastructure/me...

How do you handle primary keys? Wouldn't auto-incrementing integers lead to duplicate keys since clients are not aware of each other?

For the To Do app example, let's say two clients start with an empty database and each creates a new todo:

Client A creates [id:1, todo:"Buy milk"]

Client B creates [id:1, todo:"Buy cheese"]

Hi ricg thanks for the great question! We have a docs page that speaks to this https://docs.powersync.com/usage/sync-rules/client-id:

"PowerSync does not perform any validation that IDs are unique. Duplicate IDs on a client could occur in any of these scenarios:

- A non-unique column is used for the ID.

- Multiple table partitions are used, with the same ID present in different partitions.

- Multiple data queries returning the same record. This is typically not an issue if the queries return the same values (same transformations used in each query)."

To expand on what prawnstar said - it's exactly because of this that we recommend using uuids as the primary key (uuid v4 specifically). While you can use integer ids, you need some workarounds such as pre-generating sets of unique ids per device.

Cool! Reminds me of Fractal Database, https://github.com/fractalnetworksco/fractal-database

PoC Django ORM replication engine that supports master-master replication in a decentralized context. With a Django based solution you can sync between any DB Django supports so MySQL <-> Postrgres <-> SQLite is easy peasy.

Disclaimer: I am the author and haven't written the README yet, but cool to see similar stuff nonetheless.

Cool to learn about your project, thanks for sharing!

Do you expose the CDC log over Django? Or how do you listen for changes.

By leveraging Django's db transaction machinery in concert with Django's post save signals we send full representations to a user configurable replication target.

We provide a Matrix (protocol) replication target implementation but system's architecture was designed to be transport and database agnostic.

Our goal is to make it easy for developers to build offline-first decentralized applications for private / permissioned networks.

Well done on the launch! I have been using PowerSync for several months already ( although our own app hasn't quite reached production yet, due to other reasons).

Very happy with PowerSync. The docs are detailed and well thought out, and the technology is solid. One can see it builds on a decade of sync experience from the Journey team.

Thanks for the feedback! Along with the docs we also have an active Discord that anyone is welcome to join for any queries, requests, discussions etc https://discord.gg/powersync

Possible integration with drizzle orm ?

Let me define schema with drizzle and use its generated objects to interact with db. Postgres driver for server SQLite driver for client

It is on our near-term roadmap to support ORMs for the client-side SQLite database. Our goal with ORMs is to fit in with existing popular libraries on each platform (e.g. Flutter, React Native, etc.), rather than creating a PowerSync-specific ORM over all platforms. Drizzle is one of the integrations that we're looking at, along with Prisma and others.

This seems really interesting and I do have some questions:

How similar is this to MeteorJs minimongo collections?

Does this provide an architecture where a client subscribes to a collection and the server keeps track of which data the client has and only sends minimal update messages?

Is this ideal for intense real-time applications (ex:Chat)?

Is self-hosted a future option?

How similar is this to MeteorJs minimongo collections?

Does this provide an architecture where a client subscribes to a collection and the server keeps track of which data the client has and only sends minimal update messages?

It is similar in that PowerSync also supports local queries and real-time/streaming updates. However, I believe minimongo / MeteorJS uses an in-memory database, while PowerSync fully persists the data, allowing full offline capabilities.

The PowerSync client does subscribe to incremental changes, similar to MeteorJS (although the client keeps track of the state, not the server).

Is this ideal for intense real-time applications (ex:Chat)?

Yes, PowerSync will work well for that. One caveat is that PowerSync may have slightly more latency than some other real-time systems (in the order of tens to hundreds of milliseconds), in favor of consistency and offline persistence of data. This means PowerSync will not be suitable for time-sensitive games as an example.

Is self-hosted a future option?

Yes - see other comments for details on self-hosting and our open-source plans.

Great concept! What if the user is offline, makes updates to the local/embedded DB, but when it gets online there's an issue in syncing that prevents updating the postgress with the SQLite changes?

Is SQLite reverted to postgress and thus the changes lost?

The client does not update its state to the authoritative state of the server as long as there's pending writes present in the client's upload queue. If there is an error with uploading a write to the server (either a network error or the server returning an HTTP error response), the write will remain in the upload queue and the upload will be retried.

If the server can automatically resolve an error or conflict, it would return a 2xx response and the client's state will be updated to match the server's authoritative state.

If the conflict/error cannot automatically be resolved, there are a few options:

- The server can return a 2xx response but record information about the conflict that allows a user to resolve it (e.g. prompt a user to manually resolve the conflict — either the end-user or administrator for example)

- The server can save details in a dead letter queue