11–20 of 167 posts
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.
> Grant Permissions on Specific Columns This feature might be great for OLAP systems, but it's awful for OLTP systems. The last thing I want is debug permission errors bubbling from my database into the application layer. It may not be as efficient, but you should always manage your ACL rules in the application layer if you're building an OLTP app.
The word "always" should always be avoided in architecture discussions.
I speak from personal experience. If you know a good usecase for ACL in database for OLTP workflows, I'm all ears.
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.
Databases often outlive applications. Database features usually do what they do very quickly and reliably. Used well, a full-featured database can make things like in-place refactors or re-writes of your application layer far easier, make everyday operation much safer, as well as making it much safe & useful to allow multiple applications access to the same database, which can be very handy for all kinds of reasons.
> While many of these are neat, good engineering practice is to make the simplest thing to get the job done.
That, or, use the right tool for the job.
id int SERIAL
id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
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 you run into massive headaches if you try to move things around.
Identity columns don't, and avoid the issue altogether.
https://www.2ndquadrant.com/en/blog/postgresql-10-identity-c...
Great article. Read through some of the other articles as well - some high quality content there!
Earlier quoted context omitted.
The word "always" should always be avoided in architecture discussions.
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.
Great article, always learn a lot from this author. Here is another way I have used to do pivot tables / crosstab in postgres where you have a variable number of columns in the output: https://gist.github.com/ryanguill/101a19fb6ae6dfb26a01396c53... You can try it out here: https://dbfiddle.uk/?rdbms=postgres_9.6&fiddle=5dbbf7eadf0ed...
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 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…