Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Lesser-known Postgres features

hakibenita.com

Post reply on HN
Print view View original

21–30 of 167 posts

Re: Lesser-known Postgres features

#21

Post by pyentropy »

londons_explore wrote: post #7

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.

I'd rather have a non-portable SQL feature than an app-level "portable" implementation that almost certainly is slower and non-transactional.

Migrating to another DB will always take work, no matter how much you try to ignore flavor-specific features. Query planning, encoding and charsets, locking abilities tend to be very different. A query can run fine in MySQL and cause a deadlock in Postgres even though it's syntactically valid in both.

Re: Lesser-known Postgres features

#22

Post by superice »

cnity wrote: post #18
gavinray wrote: post #14

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 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 features feels much less icky from an architectural point of view. They are essentially part of your application given that you use something like Flyway for migrations and you don't manually run SQL against your production to install functions and triggers and such.

Re: Lesser-known Postgres features

#23

Post by nicoburns »

gmfawcett wrote: post #11
londons_explore wrote: post #7

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.

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

Re: Lesser-known Postgres features

#24

Post by picardo »

blowski wrote: post #16
picardo wrote: post #12

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?

Good point. Phrasing it as a trade-off is the better approach. Agree with the downsides you listed, as well.

Better security guarantees, for sure, but if an unauthorized user gains direct access to your database, you might have bigger problems from a security perspective.

Re: Lesser-known Postgres features

#25

Post by gavinray »

netcraft wrote: post #19
gavinray wrote: post #14

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…

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 might be created)
  - Easier to read (when you have less than +1,000,000 rows in a table)
I agree and think you're right in that UUID's are probably a better default.

Though you can never find a "definitive" guide/rule online or in the docs unfortunately.

Re: Lesser-known Postgres features

#26

Post by CabSauce »

freeest-will wrote: post #20

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...>

Re: Lesser-known Postgres features

#27

Post by yardstick »

gavinray wrote: post #25
netcraft wrote: post #19

Earlier quoted context omitted.

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…

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.

Re: Lesser-known Postgres features

#28

Post by tengbretson »

londons_explore wrote: post #7

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.

Even the most obscure Postgres features are documented better than whatever code a dev would write in order to avoid using them.

Re: Lesser-known Postgres features

#29

Post by mcherm »

picardo wrote: post #12
blowski wrote: post #10

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.

> If you know a good usecase for ACL in database for OLTP workflows, I'm all ears.

Here's one that's meaningful to me. We have a single database with five different applications that access it, each of them managed by a separate team. By enforcing access constraints in the database we guarantee the access constraints will be applied in all cases. It is difficult to ensure that in application code managed by separate teams.

(Just to be clear: I don't think your advice is poor, I just wanted to give an example of a case where it isn't universally applicable.)

Re: Lesser-known Postgres features

#30

Post by toast0 »

superice wrote: post #22
cnity wrote: post #18

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…

Adding on, even when you are trying to make a portable application, you tend to want to make it work on either X or Y, and you can still exclude people from switching their existing data from one to the other.

I've used software that at least tried to be portable, so you could install it with whatever database you had available.

Post reply on HN