31–40 of 167 posts
Earlier quoted context omitted.
I'm not sure why you are being downvoted. Your argument is reasonable and clearly presented, and "do the simplest thing that can work" is a tried and true design philosophy.
Using a built in database feature if often a lot simpler
They achieve the same thing, but [.] avoids the problem of your host language "helpfully" interpolating \. into . before sending the query to Postgres.
Earlier quoted context omitted.
I qualified it by saying "if you're building an OLTP app" though. I speak from personal experience. If you know a good usecase for ACL in database for OLTP workflows, I'm all ears.
Even then, it would be better phrased as a tradeoff. What's the downside of putting in the database? I'd guess it makes horizontally scaling harder, and it's less portable to other database vendors. Is there an upside, like you've got stronger data security guarantees?
An upside to using db to handle data permissions is the data has the same protections if you are accessing with direct SQL or using an app. Also, those permissions would persist with the data in restored backups.
I’m not advocating this and think for OLTP, building RBAC in the app layer is almost always a better idea, but these would be some benefits.
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…
But, what makes this sort of design so nice is that you can use DB-specific stuff behind the interface because you’re not trying to write all your queries in the minimally supported subset of SQL or something.
Would i use them in production? no. Are they fun to play around with? Yes!
Earlier quoted context omitted.
Yeah I recognize the arguments for UUID keys: - Avoids people being able to just iterate through records, or to discover roughly how many records of a thing you have - Allows you to generate the key before the row is saved I think I default to auto-increment ID's due to: - Familiarity bias - They have a temporal aspect to them (IE, I know row with ID 225 was created before row with ID 392, and approximately when they…
I prefer UUIDs as well but one other benefit of an integer type key is in the index. UUIDs, especially v4, make for bloated indexes. If there’s a very specific performance reason I’ll use int, otherwise uuid.
There are UUID variants that can work well with indices, which shrinks the case for big-integers yet further, to micro-optimizing cases that are situational.
[0]: https://www.postgresql.org/docs/current/rangetypes.html
[1]: https://www.postgresql.org/docs/current/functions-range.html
Avoid using lesser known features... They're the ones that will be hardest to migrate to a different database, most likely to be deprecated, and least likely to be understood by the next engineer to fill your shoes. While many of these are neat, good engineering practice is to make the simplest thing to get the job done.
Careful with DISTINCT ON, it can be extremely slow, like 100x slower than a verbose and less readable alternative. But it might not matter in your use case.
I don't belive this is correct for postgres. It's true for other RDBMS, I believe. https://medium.com/@smitagudale712/distinct-vs-group-by-in-s... >
https://blog.timescale.com/blog/how-we-made-distinct-queries...
SELECT *
INTO TEMP copy
FROM foobar;
\copy "copy" to 'foobar.csv' with csv headers