matharmin

If it's pulsing, get it checked out. Pulsatile tinnitus is often a symptom of a bigger underlying issue.

It's right at the start of the post - the git remote including credentials was exposed via the .git directory

Thanks, that gives a good overview

Is this Java/JVM-specific? How are changes captured on the client?

From reading through the docs it seems like the CDC work is done in the JDBC driver (SyncLite Logger), but I had to read quite far to see that, and it's difficult to put together how it works and in what types of applications this can be used. For example, can I capture changes to a SQLite or DuckDB database in an Android or iOS application?

I'm interested to see the approaches used, since there is very little out there for capturing changes in embedded databases.

That depends on where you live. In my area, labor is cheap and the costs of panels, batteries and inverters came down significantly over the last 5 years. You can now break even on electricity costs for a residential pitched-roof installation in under 5 years. This is without any subsidies, without selling anything back to the grid.

Utility-scale solar installations also make a lot of sense, but around here transmission capacity for that is still a massive issue. You can install more transmission capacity, but it's not cheap.

The article is from 2016, and that point doesn't hold anymore. If you take the Functional Source License (from the current fair.io site), there is no restriction on the number of users or company size - it typically only prevents competing use cases.

The article is from 2016, and quite a bit has changed since then - both in the ecosystem (many previously "open source" companies moving away from that model), and in the new licenses available to be used.

I really like the Functional Source License (FSL) (the main license under the Fair Source brand), and we've adopted it at my company. It's not open source, and shouldn't be compared with that. But you do get a lot of the same advantages as you'd get from an open-source product, such as being able to inspect the source code to get an understanding of how the system works.

Yeah, I've dealt with some of those edge cases on AWS and GCP.

Some examples:

1. I've seen a delay of hours without any messages being sent on the replication protocol, likely due to a large transaction in the WAL not committed when running out of disk space.

2. `PgError.58P02: could not create file \"pg_replslot/<slot_name>/state.tmp\": File exists`

3. `replication slot "..." is active for PID ...`, with some system process holding on to the replication slot.

4. `can no longer get changes from replication slot "<slot_name>". ... This slot has never previously reserved WAL, or it has been invalidated`.

All of these require manual intervention on the server to resolve.

And that's not even taking into account HA/failover, these are just issues with logical replication on a single node. It's still a great feature though, and often worth having to deal with these issues now and then.

1. Do you use logical or physical replication for high availability? It seems like logical replication requires a lot of manual intervention, so I'd assume physical replication? 2. If that's the case, does that mean customers can't use logical replication for other use cases?

I'm asking because logical replication seems to become more and more common as a solution to automatically replicate data to external systems (another Postgres instance, Kafka, data warehousing, or offline sync systems in my case), but many cloud providers appear to not support it. Many others do (including AWS, GCP), so I'd also be interested in how they handle high availability.

You get concurrency in SQLite by using multiple connections - and typically a dedicated thread per connection.

When using async wrappers, a good solution is connection pooling like you mentioned - exactly the same concept as used by client->server database drivers. So you can have 5 or 10 read connections serving those 100 connections, with a statement/transaction queue to manage spikes in load. It's probably not worth having more connections than CPUs, but it depends a little on whether your queries are limited by I/O or CPU, and whether you have other delays in your transactions (each transaction requires exclusive use of one connection while it's running).

SQLite maintains an in-memory cache of recently-accessed pages of data. However, this gets cleared on all other connections whenever you write to the database, so is not that efficient when you have high write loads. But the OS filesystem cache will still make a massive difference here - in many cases your connections will just read from the filesystem cache, which is much faster than the underlying storage.

Open connections don't block checkpointing in SQLite. The main case I'm aware of that does block it, is always having one or more active transactions. I believe that's quite rare in practice unless you have really high and continuous load, but if you do then the WAL2 branch may be for you.

I feel connection pooling is much more rare in SQLite libraries than it should be. I'm maintaining one implementation (sqlite_async for Dart), but feel like this should be the standard for all languages with async/await support.

Postgres is usually completely happy enough with UUIDv4. Overall architecture (such as allowing distributed id generation, if relevant) is more important than squeezing out that last bit of performance, especially for the majority of web applications who don't work with 10 million+ rows.

Murder rates are now around half of what it was 30 years ago, while at the same time security around homes went up significantly (and most new developments are security estates). The increase in murder rate over the last 3 years is not significant on that scale.

In my experience around Cape Town, it's both: High security (or more commonly electric fencing in affluent neighbourhoods) on a single house means the house is safe (or the owners are paranoid). High security on every house in the neighborhood means high crime rate in the neighborhood.

It also don't think crime stats went up much over the last decades in general (may be different in specific neighborhoods), but there is definitely more fear of safety that leads to more security.

It's fairly new, but at a couple hundred apps. Lots of indie developers, as well as a couple of larger companies.

My team publishes a cross-platform library/SDK with support for Flutter, React Native, Web and KMP.

Right now, we're seeing a lot of interest in Flutter for new cross-platform apps. I personally find it a really pleasant development experience.

We're also seeing a lot of interest in React Native. In our case possibly less than Flutter only because there is more competing libraries available for React Native.

We're also seeing interest in KMP, but much less than Flutter or React Native - I'd guess it's probably 5% or less of our users. It may have a good future, but it's not there yet in terms of current usage.

This is a great list to start with. Postgres logical replication is great for capturing changes, but the documentation is really lacking, and gives the impression that it was really just designed for Postgres -> Postgres replication, with a database administrator present to handle any edge cases.

Some others I've run into as part of developing a consumer for PowerSync:

1. LSN is strictly increasing for transaction commits, but not for row operations inside transactions. Specifically, when you have multiple concurrent transactions, they'll have overlapping LSN ranges for operations inside them.

2. When you restart replication, it always restarts at the beginning of a transaction. Combined with the above, there's not much of a point in trying to track progress within transactions.

3. Some hosting providers such as AWS RDS flushes the WAL every 5 minutes, causing an increase of 64MB each time. Make sure to acknowledge progress on "Primary keepalive messages", otherwise the WAL for an idle database will grow at over 750MB/hour. (Unlike what the article mentions, you don't need heartbeat messages as long as you acknowledge the primary keepalive messages.)

4. If you have a table without an unique index, you can replicate using REPLICA IDENTITY FULL or REPLICA IDENTITY NOTHING. But if you have duplicate rows in the source table, there will be no way to distinguish them with logical replication, and you can end up with inconsistent state.

DDL changes are also tricky. As the post mentions, you can detect columns added or removed using just the logical replication protocol. But there are other cases not covered:

1. You only get notifications of new tables once data has been inserted.

2. You get no notifications of dropped tables.

3. If a column is added with a default value, that default value is present on new rows, but you don't get any updates on existing rows.

Catering for initial snapshots is also not trivial:

1. Create a new logical replication slot using the logical replication protocol, with the "EXPORT_SNAPSHOT" option to create a "snapshot" of the state.

2. On a separate connection, start a repeatable read transaction, with `SET TRANSACTION SNAPSHOT '<snapshot>'` using the above snapshot.

3. Do the initial snapshot using this transaction.

4. Once the snapshot is complete, continue using logical replication.

Care must be taken that all data types match between the initial snapshot and logical replication. Also check things like columns matching up between the two (e.g. generated columns included in the initial snapshot, but not logical replication).

There are also a couple of cases where replication slots will break. I've seen a couple of cases triggered by the source database running out of disk space, despite the rest of the database having recovered in the meantime. Some examples:

1. I've seen a delay of hours without any messages being sent on the replication protocol, likely due to a large transaction in the WAL not committed when running out of disk space.

2. `PgError.58P02: could not create file \"pg_replslot/<slot_name>/state.tmp\": File exists`

2. `replication slot "..." is active for PID ...`, with some system process holding on to the replication slot.

3. `can no longer get changes from replication slot "<slot_name>". ... This slot has never previously reserved WAL, or it has been invalidated`.

Each one of those can be handled, typically by restarting postgres and/or deleting the replication slot. But there are many different edge cases that are difficult to know about and test for, before suddenly running into it in a production system. So generally it's very difficult to create a system that "just works", and can automatically recover without manual intervention in cases like those.

These are decent GPUs for anything other than heavy gaming. I'm driving two 4k screens with it, and even for some light gaming (such as factorio) it's completely fine.

These days graphics cards do (optional) frame generation, which gives a much higher framerate than what the game can natively do. Still not sure whether that will get you to 1khz, but it would be closer at least.

If you just need one or two of the commands, just using those queries directly is easy.

If you want to support all of them, including handling all the different Postgres versions, it becomes quite a lot to implement.

It's actually not exactly 50K, and likely varies across Chrome/V8 versions, operating system and/or machine architecture. On my browser the threshold is around 22500 elements. For a quick test, see [1].

The difference in V8 appears to be based on whether or not the array is stored in "large object space" [2], which seems to be used when the object is somewhere between 64kb and 128kb [3] (once again can depend on lots of factors). I wonder how many other optimizations depend on that?

[1]: http://www.lonniebest.com/BadShiftPerformance/ [2]: https://issues.chromium.org/issues/42202676 [3]: https://github.com/danbev/learning-v8/blob/master/notes/heap...

Updated in 2022, but I don't see anything on <Suspense> for example. That doesn't necessarily mean the info is now wrong, but it's definitely not complete anymore.

What I find interesting is that this approach means the query plan is chosen when "compiling" the statement, not when running it. However, there are still some cases where SQLite will recompile the statement when running, such as when the schema changed.

This sounds like the "Closest pair of points problem", which should have fast solutions - O(n log(n)) comparisons instead of O(n^2), which makes a huge difference when you have 10 million vectors. You might not find an off-the-shelf solution, but there are some good explanations of the approach at least.

Just to expand on this - ElectricSQL and PowerSync (and some others like Evolu) use a simple but quite effective approach to get reactive queries:

1. Listen for updates to the table(s) used by the query, using SQLite's update hook or similar approach.

2. When a table has changed, rerun the query.

There is no incremental updating of row results, no smart detection of whether the updated rows have an effect on the query. But it works with practically any query, including joins, aggregates, and even views and CTEs in the case of PowerSync and Evolu. And in most cases, SQLite is fast enough that performance is not an issue.

The JSON duality views is quite a neat idea.

You can get something similar in SQLite with normal views and triggers using JSON1 functions, but it would be a lot of effort to create those triggers manually.

The sqlite_async lib uses WAL mode by default.

From some further testing: 1. With a batch size of 10k, there is a negligible difference in write performance on my machine (with SQLite taking around 10% longer). 2. Reads are still slower, with the majority of the time spent in decoding. There are probably faster ways to convert List<double> into a Uint8List - probably using the typed_data library. I haven't tested this yet.

With larger batch sizes, memory usage becomes a problem with the current sqlite_async batch implementation. There can probably be improved in the library, but a workaround is to split the writes into smaller batches.

Overall, you should be able to get very similar performance between Isar and SQLite, but there is something to say for Isar providing better performance for the default/obvious way of doing things in this case.

Author of the sqlite_async package here.

There isn't anything obviously wrong with how the lib is used - the writes are properly batched in a single transaction, but the performance is much slower than expected.

From a quick look, garbage collection appears to be a big overhead - it could be related to how data is passed between isolates. In this case, it may actually be faster to do writes in smaller batches, since the current approach may use a lot of memory for the batch. I'll have to test further to see whether that helps, or whether there are other optimizations for this case.

When it works it's that simple. When it doesn't - perhaps because of some incompatibility between the native model and a recent Expo update, it's hours of debugging through the different layers to figure out what's going on. That's my experience starting a React Native project in 2023.

Flutter has issues with not using native UI, but I've found the package system with native modules to be way simpler and less issues. Unfortunately you're still stuck with Coacoapods though.

If you don't need any native plugins other than those provided by Expo Go, then yes.

If you do need other libraries, or add native code yourself, the package system becomes a nightmare. It is NPM packages on top of Cocoapods on top of a XCode/Swift/Objective-C compile process. Only slightly better on Android.

Want to make a change to a library? Good luck getting linking working on your local setup.

Cordova had exactly the same issues. I'm not sure if Ionic Capacitor is better, but this is one area where Flutter is miles ahead.