postgres requests an id from the sequence for each row of the incoming data ahead of time since it doesn't know which rows are updates and which are inserts (although presumably this could be changed?). the sequence doesn't reset down for the unused so this can eat through it unexpectedly quickly if you have a table with a relatively large volume of updates.
also as a tip if you hit the max integer for the sequence and need space to implement a fundamental fix you can quickly change the sequence to start at -1 and go down. there's no issue with negative ids since they're also integers.
> postgres requests an id from the sequence for each row of the incoming data ahead of time since it doesn't know which rows are updates and which are inserts (although presumably this could be changed?).
Not in any sort of general way - the sequence can be part of the unique key that you're conflicting on.
The only thing I would change here is “watch your thoughts, thoughts become words” and reverse it.
Your words are so often careless attempts at thought. First approximations of ideas. All too often, however, they are spoken to oneself and heard by oneself as truth. Be very careful about your words, they will go on to constrain oh so much.
Now, strictly speaking thought may come first. But, it’s that thinking about words which becomes so trapping, not the initial conditions that give rise to the words. Interrogate your language, distrust it, scrutinise it.
this also matters if you do a lot of upserts on a table that are predominantly updates. postgres requests an id from the sequence for each row of the incoming data ahead of time since it doesn't know which rows are updates and which are inserts. the sequence doesn't reset down for the unused so this can eat through it unexpectedly quickly.
if you hit the max integer for the sequence and need space to implement a fundamental fix you can quickly change the sequence to start at -1 and go down. there's no issue with negative ids since they're also integers.
I remember reading this quite a while ago. My elaboration of this technique is to write the last sentence in my head, but only put the first part down on (paper) the end of the file. If I'm lucky, when I come back to it, I can read the first part of the last sentence, and memory will tell me how the sentence ends, I can just keep going.
In my experience people are inaccurately pessimistic, in part due to a lack of imagination. So risk assessment that's more accurate will present as more optimistic.
DRY needs to be balanced with SRP (Single Responsibility Principle). You can legitimately have two functions that are exactly the same but they should not be DRY'd up if they are actually serving different purposes.
The use cases will likely diverge in the future, and if the functions are DRY'd making changes will make introducing bugs from the calling code that you're not working on easy. Eventually the single function will likely have a lot of conditions in it, which is a red flag for this situation.
>You can legitimately have two functions that are exactly the same but they should not be DRY'd up if they are actually serving different purposes.
I use what I've come to call the "Rule of Three" here.
The first time, don't even consider any kind of abstraction. Just do the thing.
The second time, don't abstract yet. Repeat yourself, but do it in a way that will scale and make abstraction possible, while mentally noting how you might abstract it.
The third time, abstract it.
Adhering to this, the vast majority of code will never reach that third stage, thus taming the complexity beast.
I do something similar, but not as precisely specified as this. But I like it.
It's similar to my own rule about when to add a tool. I wait until I'm doing something else and I feel the lack of that specific tool. The first time, it's on my radar. The second time, it's time to get the tool. If you get it on first impulse, you'll drown in tools (and won't give the tools you have a fair shake). If you wait too long, you'll acclimate to not using the proper tool.
I use the rule of "Annoyance". When I get annoyed by having to rewrite what already exists, I'll try to make it DRY, or when I have to fix things in multiple places many times.
I outsource a lot of decisions to that feeling, so I can focus on other things.
It's a matter of time anyway when it's really easy to ask ChatGPT to refactor everything into this perfect code.
I usually phrase this as balancing against loose coupling.
I've had bad experiences with the single responsibility principle. It sounds kind of right, but in practice "responsibility" is too vague and often surprisingly hard to agree on what is e.g. one responsibility vs. three responsibilities.
By contrast, loose coupling is more objective and can (at least in theory) be measured.
