151–160 of 167 posts
Earlier quoted context omitted.
Unless you are writing nuclear warhead management system, writing UUID collision handling is waste of time. Client can retry on top level if request failed.
expecting collision to happen is probably a waste of time, but it still sounds like a good idea to bound how much damage a collision might cause.
Regarding "Match Against Multiple Patterns", the examples are about finding the _suffixes_ of something, email domains in the example. An attempt to find a suffix like that will not be able to use an index, whereas creating a functional index on the reverse and looking for the reversed suffix as a prefix will be: # create table users (id int primary key, email text); CREATE TABLE # create unique index on users(lower(…
Blogpost discussing this approach: https://about.gitlab.com/blog/2016/03/18/fast-search-using-p...
I want to stress the importance of not using id int SERIAL If you are on a somewhat recent version of postgres, please do yourself a favor and use: id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY An "identity column", the part here: https://hakibenita.com/postgresql-unknown-features#prevent-s... You might think this is trivial -- but SERIAL creates an "owned" (by a certain user) sequence behind the scenes, and so…
I'll stop short of giving a recommendation or using the word "should", but ill give encouragement to consider using uuid's for keys. I have used them in several systems and have never had any issues with them, and they solve so many issues. The ability to generate a key on the client or on the server or in the database is great for one. And the fact that keys are unique not only in a table but in the system (or many…
Earlier quoted context omitted.
expecting collision to happen is probably a waste of time, but it still sounds like a good idea to bound how much damage a collision might cause.
My guess is that Iin 99% collision could occur during insert. Most of the time db will just not allow to insert duplicated record.
Sort of the distinction between unspecified behaviour and undefined behaviour in C.
God damn I need to unlearn those because it will spoil my capability to develop non-postgres systems.
Though I wish there was an easy way to figure out how many times "CONFLICT" actually occured (i.e how many times did we insert vs update).
Earlier quoted context omitted.
I often move ID generation into the application layer (this also helps avoid things like enumeration attacks), and actually quite a lot of cool Postgres features blur that line a little bit. It's interesting to think sequences and other computational mechanisms in a DB, and whether they make architecting applications easier or harder. I don't have a strong opinion either way, but I'm interested in HN's opinion.
One often hears the counterargument 'but using DB-specific features makes your application less portable!' to which I like to argue: When was the last time you moved an application from SQL-db to SQL-db engine? Follow up question: When was it ever 'easy' if you did? If you start from the basic premise that the database engine and the application are intertwined and are not loosely coupled, using Postgres-specific fea…
A more reasonable argument for application layer ID generation is flexibility. It's likely that I want some specific primary key generation (e.g. by email, or username, or username prepended with role, and so on).
Surprised to not see any mention of Postgres native pub/sub messaging: https://vijayg.com/postgresql-pubsub/ Is that feature considered well-known? Or is it so obscure this author didn't know about it?
I assumed it would work as a regular pub/sub pattern where you get notified when some event happens. However, in the attached example, they still poll the database every half second. I'm not sure I understand the idea.
Earlier quoted context omitted.
The person you're replying to didn't even mention those advantages though! The ones they did mention are more significant to me. Of your disadvantages... if I wanted to know when rows were created, I'd just add a created timestamp column. But "easier to read" is for real -- it's easy when debugging something to have notes referencing rows 12455 and 98923823 or in some cases even keep them in your head. But UUIDs are…
> I think putting internal PK's in URLs or anything else publicly exposed is a bad idea Why? Both as a user and as a dev I love that I can just change the PK to get to a specific post, item, whatever instead of changing the whole link.
It's considered undesirable because it's basically an "implementation detail", it's good to let the internal PK change without having to change the public-facing URLs, or sometimes vice versa, when there are business reasons to do so.
And then there are the at least possible security implications (mainly "enumeration attacks") and/or "business intelligence to your competitors" implications too.
But it's true that plenty of sites seem to expose the internal PK too, it's not a complete consensus. Just kind of a principle to separate internal implementation from public interface.
Here's a short 2008 HN discussion on it, in which people have both opinions: https://news.ycombinator.com/item?id=19876901
Here's a more recent and lengthy treatment: