51–60 of 87 posts
hence we ended up writing our own WAL for a Postgres compatible engine, and what surprised me was how much of the work is in the fsync ordering rather than the log format.
Relaxing that is a real choice to lose the last few transactions on a crash. Fine for some workloads but it should be a decision someone made on purpose.
The era of AI agents need serverless SQL. Its very important to have cost effective tech to run the internet. hence we ended up writing our own WAL for a Postgres compatible engine, and what surprised me was how much of the work is in the fsync ordering rather than the log format. Relaxing that is a real choice to lose the last few transactions on a crash. Fine for some workloads but it should be a decision someone m…
> PRAGMA synchronous = NORMAL; > In NORMAL mode, the database engine syncs to disk only at critical moments (e.g., during checkpoints) rather than at every single transaction commit. In WAL mode, this is completely safe from database corruption; even if the server crashes, only the uncommitted transactions in the WAL are lost, but the database integrity remains intact. No, this is not safe. You can lose the latest co…
Earlier quoted context omitted.
> Ideally you'd perform your schema migrations separately from your application Why is that the ideal? With SQLite your database is 1:1 connected to your application (meaning there is no other application using that database), it doesn't make sense to move the app to a new version but not the database or vice versa. Running migrations on startup of the app is ideal. Migrations are a bit more difficult to write for SQ…
Postgres has transactional DDL: you can be applying migrations in one transaction while serving live traffic from the old schema in another. By tying the schema changes directly to the application deployment it becomes harder to apply a big migration without downtime. You can't apply the migration and then cut over traffic to new app instances once the migration is complete.
In rollback mode, only the migration thread can be active because it's a write transaction, but I'm guessing in WAL mode, readers can continue to read during the migration, as with any other write transaction. I don't use WAL mode much because for my application (HashBackup), I don't need db concurrency.
I am obsessed with the idea of per tenant databases. But I am afraid of migrations. Has anyone tried that?
> PRAGMA synchronous = NORMAL; > In NORMAL mode, the database engine syncs to disk only at critical moments (e.g., during checkpoints) rather than at every single transaction commit. In WAL mode, this is completely safe from database corruption; even if the server crashes, only the uncommitted transactions in the WAL are lost, but the database integrity remains intact. No, this is not safe. You can lose the latest co…
> PRAGMA synchronous = NORMAL; > In NORMAL mode, the database engine syncs to disk only at critical moments (e.g., during checkpoints) rather than at every single transaction commit. In WAL mode, this is completely safe from database corruption; even if the server crashes, only the uncommitted transactions in the WAL are lost, but the database integrity remains intact. No, this is not safe. You can lose the latest co…
Safe in this context means not corrupting the database. You might lose data, but the database will live on happily as if the missing data was never written to it. You don’t get half-committed transactions, and the database isn’t in a weird state.
I'm mainly referring to this quote.
So, you can lose committed transactions too.
I'm fairly confident this is AI generated, but it makes me think regardless: Whenever I see these kind of articles, I'm left wondering if they've actually used SQLite in production because I always see points about how to optimize performance, like using the WAL, but never about annoyances/issues you'd run into before even needing to worry about that. I guess it's the zeitgeist to use it in a production setting, and…
It's about tradeoff, sometimes those limitations doesn't really matter that much, sometimes they are. The point is not to settle on a superior option so we never need to think the again but to understand the difference and choose accordingly. Or at least that's how I view it. Whenever I think about using SQLite, I make sure I read these documents to see if I am fine with the limitations. https://sqlite.org/whentouse.…
"SQLite is not directly comparable to client/server SQL database engines such as MySQL, Oracle, PostgreSQL, or SQL Server since SQLite is trying to solve a different problem.
Client/server SQL database engines strive to implement a shared repository of enterprise data. They emphasize scalability, concurrency, centralization, and control. SQLite strives to provide local data storage for individual applications and devices. SQLite emphasizes economy, efficiency, reliability, independence, and simplicity.
SQLite does not compete with client/server databases. SQLite competes with fopen()."
I'm fairly confident this is AI generated, but it makes me think regardless: Whenever I see these kind of articles, I'm left wondering if they've actually used SQLite in production because I always see points about how to optimize performance, like using the WAL, but never about annoyances/issues you'd run into before even needing to worry about that. I guess it's the zeitgeist to use it in a production setting, and…
I previously had a golang based crawler doing 5 concurrent process writing into the same sqlite wal, it caused the sqlite to get corrupted, and i finally decided to move to postgres instead.
Unfortunately written by an AI - that completely takes the wind out of the content and makes me not even want to read any further. The distinction between “production” and “non-production” is also questionable. For an evaluation, I recommend the following original articles (certainly not AI-generated): - https://sqlite.org/whentouse.html - https://sqlite.org/different.html - https://sqlite.org/quirks.html
The blog lists ways to optimize sqlite for production, for those who have already made that choice. But these comment links are about choosing sqlite vs others and differences, which is completely unrelated and unnecessary for those who have already made that choice.
I'm seeing a worrying trend on HN. Nearly for all articles, there is one unquantified, unproven comment at the top saying it's 100% AI — no proof, just baseless emotion of what fits the commenter's writing style. This is the new witch hunt, or virtue trolling.
I read the original article and to be honest, this off topic comment definitely looks more like an AI troll bot wrote it than the article. The troll bot is instructed to do a google search and put 3 source links — no matter how irrelevant they are.
So what is it now — one emotion vs another? If an article has sections and blocks, it's AI. What about infinite poor humans like me who have been writing like this since childhood?
Since LLMs were trained on high quality blog posts, we can no longer have posts that fit the pre-AI definition of high quality? The content must be dumbed down with typis and scatter unconventional words, to prove AI trollers that it's not AI? Or a timelapse video of typing the article?
Instead how about: if you don't want to read the article because it doesn't fit your personal style expectation — just don't bother commenting?
I hope HN would ban AI trolling comments, and discuss substance.