Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Show HN: Bi-directional sync between Postgres and SQLite

powersync.com

Post reply on HN
Print view View original

51–60 of 107 posts

Re: Show HN: Bi-directional sync between Postgres and SQLite

#51

Post by zlg_codes »

vlovich123 wrote: post #50
matharmin wrote: post #45

Earlier quoted context omitted.

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 negati…

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.

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?

Re: Show HN: Bi-directional sync between Postgres and SQLite

#52

Post by matharmin »

vlovich123 wrote: post #50
matharmin wrote: post #45

Earlier quoted context omitted.

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 negati…

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.

Re: Show HN: Bi-directional sync between Postgres and SQLite

#53

Post by jinjin2 »

maxfurman wrote: post #37
alfor wrote: post #30

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 a…

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.

Re: Show HN: Bi-directional sync between Postgres and SQLite

#56

Post by thruflo »

m9t wrote: post #54

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

Re: Show HN: Bi-directional sync between Postgres and SQLite

#57

Post by matharmin »

thruflo wrote: post #56
m9t wrote: post #54

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…

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!

Re: Show HN: Bi-directional sync between Postgres and SQLite

#58

Post by jddj »

bongodongobob wrote: post #36
alfor wrote: post #30

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 a…

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.

Re: Show HN: Bi-directional sync between Postgres and SQLite

#59

Post by vlovich123 »

zlg_codes wrote: post #51
vlovich123 wrote: post #50

Earlier quoted context omitted.

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.

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…

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.

Re: Show HN: Bi-directional sync between Postgres and SQLite

#60

Post by vlovich123 »

matharmin wrote: post #52
vlovich123 wrote: post #50

Earlier quoted context omitted.

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 a…

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?

Post reply on HN