I haven't had time to try it, but I thought running PG on a copy-on-write filesystem with a specific "clone template" incantation would get you instant clones? Probably doable in a docker container?
https://boringsql.com/posts/instant-database-clones/
We do similar, although lean into our strict "every table as a sequence" and "all FKs are deferred" conventions and only issue DELETEs for tables that actually were inserted by the test
tracking the table inserted to isn't reliable without some kind of trigger based registry as it requires all db interactions to go through some kind of orm or something which we don't do because it's a bad thing to do. Sometimes CTE's that modify stuff are 1000x faster than the alternative and it's hard to track what is doing modifications vs not. We do track at the psycopg2 level whether a query has INSERT in it somewhere, which is a pretty good heuristic.
But lets say we just always cleared all the tables:
5ms per test * 6000 tests == 30s, across 15 test processes it is 2s of overhead to the test run. Meh. You are better off auditing your test setup functions that get reused (create_test_user etc) for how many queries they do, you might find that your overall test setup spends 20% of it's runtime creating users. When I did this I found that 50% of our test runtime was processing stack traces in logging statements (to show where in the code it was being logged from), modifying it to only put tracebacks on INFO and above cut our total testing time by almost 50%.
Using sequences is clever if you always increment a sequence for every insert, but again my analysis says that unless your table clearing is much slower than mine it's hardly worth it to check, in fact checking the value of every sequence can't take much less than 5ms which is how long it takes to clear every table if empty but is less general.
I get why folks use tmux/herdr, but I already use i3wm/Hyprland for window/workspace management, and want to have "shared first class windows" instead of dual binds of "super-based binds for i3 windows ... oh wait control-a based binds for tmux panes".
Has anyone got a tool/setup that is tmux-like but the remote terminals/panes are all local/native windows?
ITerm2 has a really great tmux integration where you get native windows or tabs for tmux windows and the panes are really easy to interact with. Of course it’s macOS only but I use it on my MBP and ssh to attach to my tmux sessions from a secondary Mac (getting the same experience remotely) or my phone (without all the niceties, just normal tmux, but still usable)
Ditto, iTerm2 tmux integration is a game-changer. People at work had this whole custom-built solution to help run stuff remotely, and I couldn't even understand what that was for because I'm so used to just iTerm2+tmux.
Something like this would be great. It seems what one really need is just persistent terminal session. So this thing probably can be built on top of zmc with a few scripts.
okey just did some investigation. ~/.config/herdr/herdr.sock JSON-RPC API so we can avoid herdr UI. In fact herdr's TUI is using it too.
So basically you can write your own tool to make it work (vibe it with AI if you want)
I'm admittedly an ORM apologist [1], but a few of his points articulated as "deal breakers" aren't that bad imo:
- "the pernicious use of foreign keys [...] links between classes are [...] foreign keys" ==> that just sounds like schema normalization, which is usually a good thing?
- "bending over backwards [...] to generate SQL that runs efficiently" ==> the huge majority of ORM-driven queries are "select * from table where id in ..."; for the queries that are more complicated than that, then yes use SQL! That's allowed!
Folks who dislike ORMs seem to have this false dichotomy that "the ORM _must_ be used for all queries", which is a self-imposed/unpractical restriction.
- "dual schema dangers" ==> he's exactly right that database should own the schema definition, but then just codegen the entities from the db schema? That's your singular source of truth, no drift. You can do this with Hibernate, ActiveRecord, Joist, many ORMs.
- "Identities" ==> ironically I think ORMs (that use the unit of work pattern) actually have net-better DX here b/c you can hook up a graph of entities with just references.
I.e. hook up a book to its author w/o knowing their ids yet, which explicitly avoids the annoyance he mentions of doing a partial commit/going to the db to figure out "what value should I INSERT into in the book.author_id column?" (but my author is new) in the middle of your business logic that just wants to "create books".
- transactions ==> agreed that "transactions via annotations" ala JPA/Hibernate are terrible, but afaiu all "internet scale" apps these days do reads outside of transactions, and just use op-locking during the singular flush/commit step to the db.
Disclaimer I am sure I won't change anyone's minds :-)
Edit: in the HN comments, we're debating "the best way to generate SQL", which is fine, but imo it overlooks the biggest value for ORMs: enforcing business invariants.
I.e. yes a simple INSERT is trivial is write, "why have the ORM to that!", but are you going to enforce the same business logic in the 10 places you do `INSERT authors` in your codebase? And if the answer is "I write an single `insertAuthor` abstraction to enforce this" then you're half-way to writing an adhoc half-specified, bug-riddled version of what a reactive ORM like Joist will do for you. [2] :-)
I have seen many ORM enjoyers argue the point about “you can just use SQL!” but I have never once seen an ORM enjoyer allow it, much less do it themselves in an actual codebase. They will time and time again prefer you write 100 lines of Typescript/Python for what could be achieved with 15 lines of SQL.
To make matters worse, most of the time I've successfully argued a project to just use SQL instead of an ORM, what has happened is that people over time built a home rolled ORM in the development language.
The problem is that "ORM" does a lot of heavy lifting as a term and can mean different things to different people. Like yes, obviously, one needs some sort of SQL -> data structure transition on the boundary (using "object" overfits to OOP!). But that can be extremely light weight. Let people write SQL, have a thin layer to pull the results back out into the appropriate data structures, and move on.
Every good ORM lets you write SQL. Mine for example has a getByQuery and getByWhere as standard methods. An ORM isn't just writing queries for you it's also handling type casting from lang primitives to SQL and back. In 99% of crud rest apis there should be no need to write your own SQL though.
And then the 100 lines of JS/Py ends up being way slower than the manual SQL, plus the autogen'd SQL part of it is slow, plus you can't even get the SQL query to profile without running the actual thing with prints.
Even the 'worst' of the ORMs (according to the people in these threads) makes this very easy:
users = User.find_by_sql(<<~SQL)
SELECT users.*,
COUNT(posts.id) AS posts_count
FROM users
LEFT JOIN posts ON posts.user_id = users.id
GROUP BY users.id
HAVING COUNT(posts.id) > 10
SQL
users.first.posts_count
# => 17
I’m not sure why you thought I meant code clarity and not performance? It’s clear in all cases the correct SQL query will be more performant.
Confused at what you’re evening trying to say here. Are you suggesting that 100 lines of application layer code is easier to understand than 15 lines of SQL?
1. Because you referred to lines of code as the way to suggest SQL is obvious better, not performance
2. No, my point was that talking about code clarity was a distraction because to talk about lines of code as a determinant of performance is clearly wrong.
3. Tangentially, yes, if some behavior takes 100 lines of general purpose code to express, I would rather read it in the general purpose language than in SQL even if the SQL was fewer lines. It's hard to imagine why this would ever be the case though.
Profile your code sometime; I assure you, with a properly indexed query, the actual query time is insignificant compared to everything else, unless your app is Rust, C, Nim, etc.
The overwhelming majority of OLTP queries I see running on massive prod systems execute in < 1 msec. More time is spent in network RTT than execution, let alone the ORM parsing the result.
> "bending over backwards [...] to generate SQL that runs efficiently" ==> the huge majority of ORM-driven queries are "select * from table where id in ..."; for the queries that are more complicated than that, then yes use SQL! That's allowed!
This is exactly why I hate ORMs. As I always put it "ORMs make the easy stuff slightly easier, and they make the harder stuff way harder".
If you're just using an OEM for the "select * from table where ID in ...", then you're saving practically nothing by using an ORM - just learn to write SQL, because as you put it, you're going to have to use it anyway for places where it falls over. There are lighter weight options that do basic stuff like transaction management and binding result sets to object properties that are much less of a PITA than ORMs.
In practice I've seen people try to use the ORM features first for places that need complicated SQL (which is a reasonable assumption), only to waste a boatload of time before concluding the ORM makes stuff harder.
> There are lighter weight options that do basic stuff like transaction management and binding result sets to object properties that are much less of a PITA than ORMs.
Query builders like these are my personal favorite from a productivity perspective! The point of a query builder is to dynamically build SQL statements that have many subtle variations (do we want to filter by EmailID or PhoneID here? What about a subquery? Did the caller want all results, or just results where $field=X?). They're basically one level above string templating for SQL generation, and often have niceties around ser/de and transaction management as you mentioned.
Because they are primarily about query generation, it feels _very_ natural to pop off the hood and write raw queries directly when necessary. You can usually use the transaction management and ser/de parts with raw queries, too.
Knex has its own set of problems. Again, SQL is a very powerful, well-known language and there are simpler tools that make it possible to break up and reuse queries.
Years ago I was working on a project that used knex, then I serendipitously discovered slonik through this blog post, https://gajus.medium.com/stop-using-knex-js-and-earn-30-bf41... (slonik has subsequently had lots of development since then). I decided to rewrite the entire persistence layer from knex to slonik over a long weekend and I'm so happy I did. I liked slonik so much that it was the only time I personally contributed to a programmer through GitHub Sponsors.
Disclaimer I just edited this into my OP comment, but "generating boilerplate INSERTs" is not the main reason I use ORMs -- it's business rule enforcement.
I.e. regardless of how easy it is to write `INSERT authors (...) VALUES (...)`, with an appropriately cute/ergonomic query builder to bind the variables/POJOs ... where does your business logic actually go?
Whenever you insert an author, are you always enforcing the same validation logic? Whenever you update a book, are you always updating the derived fields that need updated?
Getting the business rules right is "the actual hard stuff" imo, and nothing I've seen a query builder help with; it's always left as an exercise to the reader to reinvent their "business logic wrapped around POJOs" adhoc in their codebase.
This is an even worse argument for ORMs. Practically every system I've ever built had data access objects that were responsible for persisting and retrieving data. It's trivially easy to write the business rules plain out in whatever language I'm coding in - why would I want to unnecessarily wrap that in some opaque "rando-QL-invented-by-the-ORM-authors" than just specify it directly in code where I'm saving the object(s).
The number of comments implying that ORMs are required for basic software engineering concepts like proper encapsulation and DRY is baffling.
But this gets to the heart of what I was saying. I'll grant you that ORMs save a little bit of boiler plate up front (but not much - ORMs have plenty of their own boiler plate, just instead of a universally understood language like SQL they have it in their own custom config JSON/yaml/XML), but that is where I spend a teeny fraction of my time coding. Writing "boilerplate" SQL for a decently large project (say 50-100 object types) takes me maybe an extra day in coding time. I have wasted multiples of that time trying to track down a single weird ORM bug, or poorly performing query. Plus, spending that time up front to write my queries is always the least stressful time of the project. What is most stressful is when my site is finally getting a big traffic push, but then something causes the DB to crater and the leaky abstraction of the ORM makes it ten times harder to debug.
> Folks who dislike ORMs seem to have this false dichotomy that "the ORM _must_ be used for all queries", which is a self-imposed/unpractical restriction
my experience is the exact opposite. People who love and advocate the merits of ORM insist that everything be executed through ORM because it introduces too much complexity for them to blend handwritten SQL with the ORM generated queries
I've written/worked on several ORMs from scratch. ORMs are the industry standard. When I see posts like this I simply can't take them seriously. All they are saying is "I won't be a team player" and "I don't actually understand the subject matter". The reality is at a certain scale there's an entire orm team that optimizes everything. But even when there's no team involved there's no way you can write anything more optimized because I'm already at the computational limit of how far something can be optimized.
There's no (good) ORM that doesn't let you simply put your own query in.
I don’t understand this comment because in no way did I express that I’m not the team player. Seems like this is something of a sacred cow for you. Or maybe it’s a language barrier thing, but all I was trying to do was say that as a member of the data platform team, when I recommend handwritten SQL to address specific limitations of an orm, that is the response that I got. Hope this helps.
My reply was talking in general terms about the original post.
You wrote the exact opposite of my opinion here which is why I replied to your specifically:
> People who love and advocate the merits of ORM insist that everything be executed through ORM because it introduces too much complexity for them to blend handwritten SQL with the ORM generated queries
I believe strongly that good ORMs expose the ability to put your own queries in. But I can't possibly boil down all the reasons for this in one HN comment.
An ORM is not a query writer. It's a way to map SQL primitives to run time primitives in a static deterministic way backed by a suite of unit tests.
If you have a special query you wanna run that has 10 joins, 2 sub queries, and a derived view that's totally fine. No one says you can't. However remember that statistically 99.9% of all queries are not that.
But the speed is irrelevant as long as it's good enough. Notice Laravel's Eloquent at the bottom of the list yet thousands of projects are being built with it regularly.
How can I possibly condense 24 years of deep knowledge in one comment for you?
The tldr is if you're ever concatenating strings in order to build a query you're just doing what the entire job of orm is but rolling your own and chances are you'll end up with a bunch of bugs in how you handle well.... Everything.
I think your tone is a bit combative. You can certainly provide the cliff notes but if you want me to believe you’re at working at computational limits whilst talking to me about string concatenation in web dev backend languages I think the burden of proof is on you.
the amount of vitriol my comment generated was unexpected. i was sharing that my experience was the opposite of the comment I was replying to. So many people have read things into it that simply do not make sense to me, including this one. It wasn’t a flex, it was a statement of experience that was simply a different experience than the post I was replying to asserted as truth. As a senior member of the data team, I interact with developer teams regularly and suggest manual handwritten sql for particular performance edge cases, and I met with the response I mentioned. It’s not me not being the team player, it’s the development team using the ORM that has decided that the level of effort to maintain handwritten and ORM sequel is too much for their team to handle
> All they are saying is "I won't be a team player" and "I don't actually understand the subject matter".
I get the first part, but not the second.
Preferring to use SQL rather than an ORM + SQL is all about understanding the subject matter, which is the data as it exists in the database.
> The tldr is if you're ever concatenating strings in order to build a query you're just doing what the entire job of orm is but rolling your own and chances are you'll end up with a bunch of bugs in how you handle well.... Everything.
Yeah, so basically don't do this, except when you have to, like concatenating placeholders for a variable size IN query.
There's some classes of applications where it's hard to write all the queries because there's all sorts of mix and match stuff happening. Those are pretty much doomed to poor performance if the tables are large, so I would rather not play on those teams. On the bright side, the limit of a small table gets bigger every ram generation, and table scans on nvme aren't so painful either.
We're pointing out the same thing. Someone that uses an ORM knows when they shouldn't use them and I tend to trust that more than someone who simply refuses to use them and ends up recreating an ORM by accident.
> Someone that uses an ORM knows when they shouldn't use them
That's not been my experience. But admittedly, I've usually been brought in when the slow query is killing the database. Then I look at the query that nobody with any subject matter knowledge would have written, come up with an alternate query that will give either the same result or something close enough. Sometimes I have to then dig in and figure out how to make that happen, because the ORM user doesn't always know how to make direct queries.
But it sure did make the easy things easier, as the other poster said.
People focus on the query writing aspect of ORMs too much. That's not that primary reason you use an ORM. It's primary purpose is to hydrate objects in the runtime. If I pull a datetime from SQL there's a lot of value in having a single piece of code handle that datetime the same way across the entire stack. I can unit test that handling once across the entire code base. Very few ORMs are aware of how the data is indexed and yes a lot of people will write code that generates a complex WHERE clause against columns that aren't indexed. But that's an understanding problem. I expect someone who uses an ORM to understand SQL well. Including indexes and fixed length tables. Obviously you are encountering code made by people who don't understand this but the problem isn't the ORM. They would have made that mistake with or without an ORM.
> I expect someone who uses an ORM to understand SQL well.
From experience, I don't. ORMs are usually sold as 'learn this instead of learning SQL'. For many, the ORM creates the tables, alters the tables, and queries the tables; they don't see SQL and they don't know SQL. When that works, it works, but when it falls apart, they have to debug the SQL and the abstraction layer. I'd rather have fewer unnecessary abstraction layers.
> If I pull a datetime from SQL there's a lot of value in having a single piece of code handle that datetime the same way across the entire stack.
There's value there, datetimes are very complex, but the rest of the stuff it comes with obscures the value IMHO.
> Obviously you are encountering code made by people who don't understand this but the problem isn't the ORM. They would have made that mistake with or without an ORM.
It's hard to write the kind of complex queries I've seen by hand, and I like to imagine if you out how to do that, you'll also know why it's slow and not need my help... But the ORM is part of the problem, because when you've written bad queries by hand, and I give you a better query (or sequence of queries), it's easy to apply. When you've done it with an ORM, you may not even know where the query is made.
You can always make the ORM Model based on a view. Sometimes a background job compiling a simple result set table is the appropriate answer.
Almost all ORMs boil down their queries down to a single query handler so it's actually super easy to find the query.
My ORM for example:
*Read paths*
- Models/Factory/Getters/GetAllRecords.php:28 - table(...) when indexField is set.
- Models/Factory/Getters/GetAllRecords.php:31 - allRecords(...).
- Models/Factory/Getters/GetAllRecordsByWhere.php:95 - table(...) when indexField is set.
- Models/Factory/Getters/GetAllRecordsByWhere.php:98 - allRecords(...).
- Models/Factory/Getters/GetRecordByWhere.php:20 - oneRecord(...).
- Models/Factory/Getters/GetByQuery.php:9 - oneRecord(...).
- Models/Factory/Getters/GetAllByQuery.php:9 - allRecords(...).
- Models/Factory/Getters/GetTableByQuery.php:9 - table(...).
- Models/Versioning.php:122 - revision table(...).
- Models/Versioning.php:124 - revision allRecords(...).
*Write paths*
- Models/Events/Save.php:41 - insert on save() for phantom records.
- Models/Events/Save.php:53 - update on save() for existing dirty records.
- Models/Events/Delete.php:18 - delete by primary key.
- Models/Events/Destroy.php:24 - insert history row before destroy for versioned models.
- Models/Versioning.php:180 - insert history row after versioned save.
Error/retry path
- Models/Events/HandleException.php:35 - direct $connection->exec(...) for auto-creating missing tables.
- Models/Events/HandleException.php:43 - direct $connection->query(...) to rerun the failed query after table creation.
All of those eventually bottom out in IO/Database/StorageType.php:119 for non-result queries via PDO exec, or IO/Database/StorageType.php:149 for result queries via PDO query.
I used to profile all my queries in those two methods but with tools like NewRelic there's no need to slow the code down with profiling cruft.
Fair point, both "pro ORM" and "anti ORM" camps are prone to extreme stances.
I definitely don't agree with the "all queries must be executed through the ORM", and think that dogmatic stance has done a lot of damage to the ORM brand. :-/
They don't consider the ORM the second class citizen it actually is: an optional simplified alternative to normal queries, that can be used for the easy cases.
> the huge majority of ORM-driven queries are "select * from table where id in ..."; for the queries that are more complicated than that, then yes use SQL! That's allowed!
The issue is, your lowest value queries are always this type, then you get the 10-20 in any code base that are 100x more complex, and they are the ones your end users care about the most.
You end up with a 80/20 principal in the wrong way, it's great at producing queries that represent 20% of the value of your app, and awful for the 80% that define the core value of it.
The second issue is, if these queries are just "select * from table where id in ...", WTF bother with a library to abstract that away in the first place? It's trivially easy to handle this as SQL
> Folks who dislike ORMs seem to have this false dichotomy that "the ORM _must_ be used for all queries", which is a self-imposed/unpractical restriction.
I've always heard a major selling point of ORMs is "You don't have to write the actual SQL anymore"
Because of that, I tend to not trust people who use ORMs to even know how to write queries by hand in the first place
You're right, that has been another "pro ORM" pitch that has gone awry and, taken to the extreme, is wrong imo.
My nuanced articulation is "you don't have to write the _boilerplate_ SQL for the 90% of just-do-some-CRUD endpoints in your enterprise SaaS application, but you 100% need to 'know SQL' for the last 5-10% of ~reporting/analytics queries that the ORM is going to mess up".
That you somehow have to adapt the results into the same format the ORM uses. And has to adapt the parameters into taking data from the ORM. Or has to split your entire functionality from the ORM so you can actually access the database directly without one part of your code interfering with the others.
The ORMs I've tried tend to produce some pretty specific table structures that are a pain in the ass to work with outside of the ORM, imo
One of the sticking points I've found in the past is if I create a new table outside of the ORM, it doesn't know how to use it. Then if I try to add it to the ORM's model it doesn't use the existing table, it creates a conflict. Annoying stuff like that
> the huge majority of ORM-driven queries are "select * from table where id in ..."
From my experience, you are mistaken on that. Those queries mostly come with some joins, either necessary or not to represent the object, and that often could be avoided if the data wasn't mapped into some standard object.
The main problem of mixing sql and orm together is that most orms don't provide a way to do raw queries in a type safe manner that plays well with non-raw-sql queries.
You're already using a new runtime with tsgo -- it's golang at build time -- but still running Node in prod, so the same could work here. :-)
Agreed I would not want all Typescript users forced to use /this/ runtime, but if the TS team shipped tsc as "oh now it's uses a special fast JS runtime" (just like tsgo is a different runtime) I'd love to at least have the option of using the same special fast runtime in my own still-written-in-TS apps.
Seems I've either struck or a nerve, or miscommunicated, given the insta down votes.
tsgo isn't loading a full js jit compiler and a significant subset of the Node standard library. I'd be equally upset to see a Python compiler installing Pypy as a performance improvement to compile code for CPython faster.
if there was a thingy that compiled JS to a 10mb native executable with shared heap multithreading, im sure we'd use it. however, no one has invented such a thing. until this pr.
"beyond a junior level" -- I doubt this will change your mind, but this post is from the author of Tachyons, a "pre-Tailwinds" competitor that didn't get the same traction:
And the tldr is that he downloaded and read the CSS for several major websites at the time (post is from 2016) and they were all hodge-podge of terribleness.
Maybe all the devs writing that CSS were junior, but imo it's more than CSS just doesn't have the abstractions to match the level of OCD/bespokeness that designers spec into every Figma -- move this box by _this_ much / _that_ much / etc.
Per "how to handle dynamic queries", it's admittedly pretty different b/c we're an ORM (https://joist-orm.io/) that "fetches entities" instead of adhoc SQL queries, but our pattern for "variable number of filters/joins" looks like:
const { date, name, status } = args.filter;
await em.find(Employee, { date, name, employer: { status } });
Where the "shape" of the query is static, but `em.find` will drop/prune any filters/joins that are set to `undefined`.
So you get this nice "declarative / static structure" that gets "dynamically pruned to only what's applicable for the current query", instead of trying to jump through "how do I string together knex .orWhere clauses for this?" hoops.
I thought Dagger had/has a lot of potential to be "AWS-CDK for CI pipelines".
I.e. declaratively setup a web of CI / deployment tasks, based on docker, with a code-first DSL, instead of the morass of copy-pasted (and yes orbs) CircleCI yaml files we have strewn about our internals repos.
But their DSL for defining your pipelines is ... golang? Like who would pick golang as "a friendly language for setting up configs".
The underlying tech is technically language-agnostic, just as aws-cdk's is (you can share cdk constructs across TypeScript/Python), but it's rooted in golang as the originating/first-class language, so imo will never hit aws-cdk levels of ergonomics.
That technical nit aside, I love the idea; ran a few examples of it a year or so ago and was really impressed with the speed; just couldn't wrap my around "how can I make this look like cdk".
Still looks like "a circa-2000s Java builder API" and doesn't look like pleasant / declarative / idiomatic TypeScript, which is what aws-cdk pulled off.
Genuinely impressively (imo), aws-cdk intermixes "it's declarative" (you're setting up your desired state) but also "it's code" (you can use all the usual abstractions) in a way that is pretty great & unique.
Could you share an example of aws-cdk code that you think Dagger should take inspiration from? Dagger and aws-cdk work very differently under the hood, so it's difficult to make an apples-to-apples comparison. If there's a way to make Dagger more TS-native without sacrificing other important properties of Dagger, I'm interested. Thanks.
Hello! Yeah, I totally get Dagger is more "hey client please create a DAG via RPC calls", but just making something up in 30 seconds, like this is what I had in mind:
Like I'm still building a DAG, but by creating objects with "kinda POJOs" (doesn't have to be literally POJOs) and then stitching them together, like the outputs of 1 construct (the build) can be used as inputs to the other constructs (tests & container).
Can you description the deployment setup, somewhere in the docs/maybe with a diagram?
I get this is a backend library, which is great, but like does it use postgres replication slots? Per the inherited queries, do they all live on 1 machine, and we just assume that machine needs to be sufficiently beefy to serve all currently-live queries?
Do all of my (backend) live-queries live/run on that one beefy machine? What's the life cycle for live-queries? Like how can I deploy new ones / kill old ones / as I'm making deployments / business logic changes that might change the queries?
This is all really hard ofc, so apologies for all the questions, just trying to understand -- thanks!
Great questions — happy to clarify how deployment and lifecycle work today.
Let me begin by answering: what exactly is this engine? It's simply a computation + cache layer that lives in the same process as the calling code, not a server on its own.
Think of a LinkedQL instance (new PGClient()) and its concept of a "Live Query" engine as simply a query client (e.g. new pg.Client()) with an in-memory compute + cache layer.
---
1. Deployment model (current state)
The Live Query engine runs as part of your application process — the same place you’d normally run a Postgres/MySQL client.
For Postgres, yes: it uses one logical replication slot per LinkedQL engine instance. The live query engine instantiates on top of that slot and uses internal "windows" to dedupe overlapping queries, so 500 queries that are only variations of "SELECT * FROM users" still map to one main window; and 500 of such "windows" still run over the same replication slot.
As hinted at above, yes; each LinkedQL instance (new PGClient()) runs on the same machine as the running app (just as you'd have it with new pg.Client()) – and maps to a single Live Query engine under the hood.
That engine uses a single replication slot. You specify the slot name like:
new PGClient({ ..., walSlotName: 'custom_slot_name' }); // default is: "linkedql_default_slot" – as per https://linked-ql.netlify.app/docs/setup#postgresql
A second LinkedQL instance would require another slot name:
new PGClient({ ..., walSlotName: 'custom_slot_name_2' });
We’re working toward multi-instance coordination (multiple engines sharing the same replication stream + load balancing live queries). That’s planned, but not started yet.
---
3. Lifecycle of live queries
The Live Query engine runs on-demand and not indefinitely. It begins to exist when at least one client subscribes ({ live: true }) and effectively cleans up and disappears the moment the last subscriber disconnects (result.abort()). Calling client.disconnect() also ends all subscriptions and does clean up.
---
4. Deployments / code changes
Deploying new code doesn’t require “migrating” live queries.
When you restart the application:
• the Live Query starts on a clean slate with the first subscribing query (client.query('...', { live: true })).
• if you have provided a persistent replication slot name (the default being ephemeral), LinkedQL moves the position to the slot's current position and runs from there.
In other words: nothing persists across deploys; everything starts clean as your app starts.
---
5. Diagram / docs
A deployment diagram is a good idea — I’ll add one to the docs.
---
Well, I hope that helps — and no worries about the questions. This space is hard, and happy to explain anything in more detail.
Would you clarify what a "transaction" in this instance would mean?
LinkedQL definitely optimizes at multiple levels between a change happening on your database and the live result your application sees. The most significant of these being its concept of query windows and query inheritance which ensure multiple overlapping queries converge on a single "actual" query window under the hood.
Database transactions. Sometimes, when you require exceptionally high throughout and performance, it can be a viable strategy to batch multiple operations into the same transactions in order to reduce roundtrips, io and network latency.
Of course, it comes at the cost of some stability. However I was just curious if such an abstraction could support such use cases. Thank you for the link to the paper!
And of course achieving that "exceptionally high throughput and performance" is the ultimate goal for a system of this nature.
Now, yes — LinkedQL reasons explicitly in terms of transactions, end-to-end, as covered in the paper.
The key structural distinction is that LinkedQL does not have the concept of its own transactions, "since it doesn’t initiate writes". Instead, it acts as an event-processing pipeline that sits downstream of your database — with a strict "transaction-through rule" enforced across the pipeline.
What that transactional guarantee means in practice is this:
Incoming database transactions (via WAL/binlog) are treated as "atomic" units. All events produced by a single database transaction are received, processed, and propagated through the pipeline with their transactional grouping preserved, all the way to the output stream.
→ LinkedQL receives the resulting batch of mutation events from that transaction
→ processes that batch as "one" atomic unit
→ emits it downstream as "one" atomic unit
→ observers bound to the view see a "single" state transition composed of many changes, rather than "a flurry" of intermediate transitions.
Effectively, a systems that thinks in terms of batching and other throughput-oriented write patterns. LinkedQL just doesn’t initiate its own transactions — it preserves yours, end-to-end.
As a potential user, I'd probably be thinking through things like: if I have a ~small-fleet of 10 ECS tasks serving my REST/API endpoints, would I run `client.query`s on these same machines, or would it be better to have a dedicated pool of "live query" machines that are separate from most API serving, so that maybe I get more overlap of inherited queries.
...also I think there is a limit on WAL slots? Or at least I'd probably want not each of my API servers to be consuming their own WAL slots.
Totally makes sense this is all "things you worry about later" (where later might be now-/soon-ish) given the infra/core concepts you've got working now -- looking really amazing!
Thanks — this is a really good scenario to walk through, and I’m happy to extend the conversation.
First, I’m implicitly assuming your 10 ECS tasks are talking to the same Postgres instance and may issue overlapping queries. Once that’s the case, WAL slots and backend orchestration naturally enter the story — not just querying.
A few concrete facts first.
PostgreSQL caps logical replication slots via `max_replication_slots`.
Each LinkedQL Live Query engine instance uses one slot.
Whether “10 instances” is a problem depends entirely on your Postgres config and workload specifics. I’d expect 10 to be fine in many setups — but not universally. It really does depend.
---
That said, if you want strong deduplication across services, the pattern I’d recommend is centralizing queries in a separate service.
One service owns the LinkedQL engine and the replication slot. Other backend services query that service instead of Postgres directly.
Conceptually:
[API services] → [Live Query service (LinkedQL)] → Postgres
From the caller’s point of view this works like a REST API server (e.g. `GET /users?...`), but it doesn’t have to be "just" REST.
If your technology stack requirements allow, the orchestration can get more interesting. We built a backend framework called Webflo that’s designed specifically for long-lived request connections and cross-runtime reactivity — and it fits this use case very naturally.
In the query-hosting service, you install Webflo as your backend framework, define routes by exposing request-handling functions, and have these functions simply return LinkedQL's live result rows as-is:
// the root "/" route
export default async function(event, next) {
if (next.stepname) return next();
const q = event.url.q;
const liveResult = await client.query(q, {
live: true,
signal: event.signal
});
// Send the initial rows and keep the request open
event.respondWith(liveResult.rows, { done: false });
}
Here, the handler starts a live query and returns the live result rows issued by LinkedQL as "live" response.
* The client immediately receives the initial query result
* The HTTP connection stays open
* Mutations to the sent object are synced automatically over the wire and the client-side copy continues to behave as a live object
* If the client disconnects, event.signal is aborted and the live query shuts down
On the client side, you'd do:
const response = await fetch('db-service/users?q=...');
const liveResponse = await LiveResponse.from(response);
// A normal JS array — but a live one
console.log(liveResponse.body);
Observer.observe(liveResponse.body, mutations => {
console.log(mutations);
});
// Closing the connection tears down the live query upstream
liveResponse.background.close();
There’s no separate realtime API to plumb manually, no explicit WebSocket setup, and no subscription lifecycle to manage. The lifetime of the live query is simply the lifetime of the request connection.
---
In this setup:
* WAL consumption stays bounded
* live queries are deduped centrally
* API services remain stateless
* lifecycle is automatic, not manually managed
I haven’t personally run this exact topology at scale yet, but it fits the model cleanly and is very much the direction the architecture is designed to support.
Once you use Webflo, this stops feeling like “realtime plumbing” and starts feeling like normal request/response — just with live mode.
These two suggestions are fine, but I don't think they make fixtures really that much better--they're still a morass of technical debt & should be avoided at all costs.
The article doesn't mention what I hate most about fixtures: the noise of all the other crap in the fixture that doesn't matter to the current test scenario.
I.e. I want to test "merge these two books" -- great -- but now when stepping through the code, I have 30, 40, 100 other books floating around the code/database b/c "they were added by the fixture" that I need to ignore / step through / etc. Gah.
Author here. I didn't mention it because I wasn't writing an evaluation of fixtures. Just writing about how to make better use of fixtures. I actually use both fixtures and factories depending on the project specifics and also whether it is even my decision to make. :)
