Re:Show HN: Drop-In SQS Replacement Based on SQLite
by memset on Sunday June 30, 2024 @04:28PM
(#40840114)
Good question. It was an evolution. Originally I enforced foreign keys, but inserts/updates were unbearably slow. So I updated the connection string to disable them (but, as you point out, I haven't updated the CREATE TABLE statements.)
In practice, I did not find they were necessary - I was only using foreign keys to automatically CASCADE deletes when messages were removed. But instead of relying on sqlite to do that, I do it myself and wrap the delete statements in transactions.
There are many TODOs and error checkings that I will, over time, clean up and remove. I'm glad you've pointed them out - that's the great thing about open source, you at least know what you're getting into and can help shine a light on things to improve!
Re:Show HN: Drop-In SQS Replacement Based on SQLite
by plasma on Sunday June 30, 2024 @05:06PM
(#40840385)
Some of the slowdown will come from not indexing the FK columns themselves, as they need to be searched during updates / deletes to check the constraints.
In practice, I did not find they were necessary - I was only using foreign keys to automatically CASCADE deletes when messages were removed. But instead of relying on sqlite to do that, I do it myself and wrap the delete statements in transactions.
There are many TODOs and error checkings that I will, over time, clean up and remove. I'm glad you've pointed them out - that's the great thing about open source, you at least know what you're getting into and can help shine a light on things to improve!