For that purpose, you’re perfectly well off just leaving it as an opaque blob; compress, base64 encode and stuff it into a string/varbinary column and call it a day.
You don’t even need to store it in pg itself; stuff it into a cheaper datastore like s3 and just have the locations stored in pg.
The only thing to optimize for is cost & storage. Access/retrieval doesn’t matter for a once in 6 months process.
Also what is this revolting formatting strategy you’ve found?
FIAT's an italian company and there definitely is a market for pure city driving in Italy, it's not huge but it's there, especially amongst younger folks.
Personally I don't think this car's particularly fit for the US market, it's not what it was designed for.
For these agentic AI systems, like a human operator, the LLM needs to have a wide variety of operations available to it, gated by permissions and authentication. They should be calling APIs. They should be making DB queries with RLS. The reasoning model is to identify which APIs to use when and in which order… not to execute arbitrary code in prod. The same expected of a human screwing around with prod.
Leaking private repos is occurring not because an LLM is involved, but because the LLM isn’t being required to forward the authentication requirement from the user, and engaging the APIs with that limited permission sets. And it would be just as useful having had that limitation in place
The LLM is currently running around like a level 1 tech support holding admin creds, and you’re just hoping they doesn’t do anything stupid with them by giving them a bunch instructions on what not to do.
I think prompt injection vs sql injection is actually not far off — both are the direct results of blindly trusting user input for no particular reason and entirely unnecessarily, with a system that has far more power than it needs to do the task it was meant to do. It also has the same incorrect “solution” given out — sanitize your inputs — instead of correcting the problem at the source (why is an agent for repo X able to read repo Y in the first place?)
Prompt injection in the non-agentic scenario — like getting an LLM to answer questions from its training you don’t want it answering — is inevitable and unavoidable. But things like TFA, deleting prod, etc are well within reach
1. They pretend SQL is standardized, and support a heavily reduced featureset for any given database as a result
2. They leave awkward holes in their abstraction, leading to psychotic behaviors like N+1 and implicit type coercions to helpfully break your indexes silently
3. They make simple queries simple, and hard queries absolutely revolting
4. You end up not wanting to use the objects directly anyways, so you end up with object-object-relation, needing a mapping layer from your database-object to your business-objects, which also defeats most of the benefits from change-tracking
5. The generated SQL is periodically utterly nuts, so you have to review every generated query anyways
6. You probably dont want to actually use any of the OOP mapping features like inheritance in your DB
The correct answer is to use a query builder + database model, enabling most queries to be written with some degree of type-safety, and minimizing the abstraction from SQL itself, and toss out the rest of the featureset
Which ORMs are you basing this comment on exactly?
(1) and (3) are not really problems with an ORM that gets out of your way and lets you drop down to raw SQL when necessary, but still helps you hydrate result rows back to objects (and still provides the associated features I mentioned previously).
(2) and (5) can be interpreted as "your ORM does not absolve you from knowing SQL".
I've never personally run into a situation where doing (4) or (6) were desirable.
> The correct answer is to use a query builder + database model, enabling most queries to be written with some degree of type-safety, and minimizing the abstraction from SQL itself, and toss out the rest of the featureset
If you work on projects where a full featured ORM can be replaced by a simple query builder then cool, but the rest of the feature set is really useful for the projects I work on so why would I toss them out?
I agree completely, the features the parent is mentioning like "identity mapping, unit of work, and change tracking/events" are exactly the things I don't want out of the ORM because that is the leaky abstraction I don't want to constantly be working with and around.
If it was just a query builder we could have a conversation about the benefits of that vs sql and when one beats another. But it is all kinds of other features that are implicitly activated and then conspire to ruin your day when you were trying to solve some other problem. ORMs bring too much baggage by default. So now you have to talk about its relative merits compared to just writing SQL and the merits of always having these other features activated. Which other features? You need to read your full ORM manual because they really vary from one to another.
Your comment comes across like saying "why would I use an impact driver when my screwdriver does everything I need?"
If you don't actually need those features then obviously an ORM will offer less value to you. That doesn't mean ORMs aren't useful tools, they just aren't useful for the problems you work on.
I tend to work on projects where those features are useful and if the ORM didn't provide them out of the box then I would need to build them myself. In other words using a query builder alone does not adequately solve the problems I need to solve.
I have list of issues with SQL. Not composable. Unable to detect query errors at compile time because the schema is only loosely coupled to the code base. And as you yourself point out, SQL is not standardized, which is also terrible and leads to things like Oracle vendor lock in.
And frankly this list hasn't changed in 30 or maybe 40 years now.
And DBA's were so notoriously egregious that Martin Fowler made his "NoDBA" blog post over a decade ago now. And the movement to NoSQL definitely made things worse.
I wish the SQL community would stop treating ORM's like the vietnam paper did 20 years ago, and embrace them for what they are, as a stepping stone, and maybe as a useful tool to help people understand SQL itself.
> as a stepping stone, and maybe as a useful tool to help people understand SQL itself.
But that is not what ORMs are. They teach bad habits that make SQL harder to understand, not easier, because the power of SQL depends on good data modelling.
Perhaps the worst habit is treating the database as subservient to the application code. This assumption comes naturally to many programmers. In most programming contexts, file formats, wire protocols, and internal representations are defined by the code that consumes them. That's fine in some cases.
But in a data-centric application, the relationship should be reversed. Before writing a single line of application code, you should understand the domain model and design a schema that represents it well. The database is not just a persistence layer for objects. It is the system of record, and its structure should reflect the shape, constraints, and relationships of the real-world data. Everything else should be built to conform to it.
I mean there are plenty of projects that don't fit this description, where the database is just a persistence layer for your objects and the database should be subservient to application code.
I find the 1, 2 and 3 to be complete non issues. As in, they dont exist as issues for hibernate. If you feel like particulat query is oh so difficult, you can always use sql for that one part.
4 is in the "like so what" category.
5 - it optimizes alright for average case. You have to optimize in edge cases, but then again, you have to optimize edge cases with pure sql too.
6 - no I dont want much inheritance in db whether i am using pure sql or orm.
> 1. They pretend SQL is standardized, and support a heavily reduced featureset for any given database as a result
EF Core is provider-specific and also exposes provider-specific functionality.
> 4. You end up not wanting to use the objects directly anyways, so you end up with object-object-relation, needing a mapping layer from your database-object to your business-objects, which also defeats most of the benefits from change-tracking
> 5. The generated SQL is periodically utterly nuts, so you have to review every generated query anyways
Not universally true either. You only have to review complex queries. If you're making claims about a specific ORM it would be good to mention it as it's not universal.
> 6. You probably dont want to actually use any of the OOP mapping features like inheritance in your DB
Then don't? Since when is inheritance required for ORM-usage?
I have found a lot of the anti-ORM critiques come from either using a crappy ORM or having not used a good one in the last 5 years.
The smell is 50% the asset packs available in the respective stores. The other 50% is the default character controllers and the default lighting settings
The issue I find with this pattern in docs/tutorials is that the prefix makes it very obvious which functions are from this library.
It's particularly worthwhile when looking at the bigger examples that might involve another library, or stdlib functions/libs I haven't dealt with before
> It takes just as much work to delete a row as it takes to insert a row. Why wouldn't it? Obviously you have to do almost all the same operations: write a log, write the deletion, update indices, replicate it, etc.
It takes far more work to delete/update than insert. My recent example is updating ~2TB of text data was about 40x slower than inserting 12TB (was trying to correct some large text truncation that occurred during migration into PG, ended up being faster to redo).
> It takes far more work to delete/update than insert.
Updating rows of text data is going to be more work, because variable-length text can't be updated in-place. So in terms of allocating space, it's more like a delete plus an insert. That's not surprising. (An in-place update that doesn't touch indices is generally going to be faster than an insert, though.)
I'm not aware of instances where a delete is "far more work" than an equivalent insert though. That's not the general case, and I'm having a hard time thinking of any situations where that would be true.
> So in terms of allocating space, it's more like a delete plus an insert.
Unless you're using zHeap, you have a narrow Heap-only-Tuples scenario where the indexes stay the same. TOAST kinda helps there, if the update is off the tuple area itself. The original zHeap docs have a lot of detail about why an UNDO log can help with long running transactions from the past etc.
That is a postgresql specific thing though. Mysql indexes were created with the idea of different storage engines in mind, so Mysql doesn't suffer from the index update ovehead on update/delete the same way.
Uber had a long blog post about switching to Mysql from Postgres for wide tables with hundreds of indexes. The HN entry is still there[1], but I can't read the original post now.
As a side note, I've used postgres partitions to the same effect to drop old data periodically - detach and then drop the partition instead of a direct DELETE (similar tricks in HBase existed).
> Updating rows of text data is going to be more work, because variable-length text can't be updated in-place.
If we're still talking postgres, it doesn't update in-place. Update is implemented as delete+insert (where delete is updating metadata so the row is still around for still-running transactions but invisible to future transactions).
Not directly database related, but when it comes to writing files on disks, deletes on SSDs can be rather expensive because of the delete block size vs a simple write.
> I'm not aware of instances where a delete is "far more work" than an equivalent insert though. That's not the general case, and I'm having a hard time thinking of any situations where that would be true.
Transactionally across related items with constraints it can explode fast.
If you've ever used FoundationDB this rapidly becomes the defining PITA due to the transaction size limits. Adding/inserting/updates are all far more predictably bounded.
But in that case, you need to compare like-for-like with the situation where you need to insert all the prerequisite rows too. You can't just compare a delete cascade with a single insert where all the foreign keys are already satisfied.
The whole problem with the delete cascade is you can't tell how big it will be until you have entered the transaction to do it. An insert you either know or it will fail and you can retry.
That's true, but now you have moved the goalposts. The original claim upthread was "it takes just as much work to delete a row as it takes to insert a row", not "it's hard to predict the performance of a delete with cascade effects". And the obvious rebuttal to that is that it's equally hard to provide an upper bound for the runtime of a single insert: an application cannot control the other processes running on the database, some of which may delay, interfere with or even invalidate your query and you must account for that. A delete operation is just as much "it might fail and you can retry" as an insert, or the database you're working with isn't ACID-compliant.
> And the obvious rebuttal to that is that it's equally hard to provide an upper bound for the runtime of a single insert
This is precisely where you're going wrong. The insert is upper boundable in advance (you know the set of everything you might potentially have to insert), the delete isn't because you don't know what's in the db until you look.
I strongly recommend poking around with Foundation for this, because it becomes clear that this problem is the defining flaw with the way they tried to architect with layers, to the point they have a queuing system for processing large jobs of this type.
A concurrent DML happening then suddenly your MERGE INTO WHEN NOT MATCHED INSERT/INSERT INTO SELECT is way larger that you thought? I thought "some workloads can suddenly be way larger that I expected" was supposed to be a thing in all non-trivial DML.
You don't even need a complex query; even the simplest of insert statements can cause cascade side effects if you have temporal tables or materialized views (or, Codd forbid, ON INSERT triggers).
I will die on the hill that triggers are a perfectly fine tool, when used reasonably. ON INSERT isn’t usually the one I point at causing problems; that’d be ON DELETE CASCADE. 1:M relationships with large values of M are already iffy for deletions or updates; couple that with unnecessarily wide columns (or just storing large text / json / blob), and worst-case, non-clustering indices, and “delete this user” turns into “fetch thousands of pages.”
You don’t even need to store it in pg itself; stuff it into a cheaper datastore like s3 and just have the locations stored in pg.
The only thing to optimize for is cost & storage. Access/retrieval doesn’t matter for a once in 6 months process.
Also what is this revolting formatting strategy you’ve found?
reply