11 ms·

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 in

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-setting-the-value-of-an-auto-generated-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-columns/ https://www.2ndquadrant.com/en/blog/postgresql-10-identity-c...

cnity 5y ago
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.
- superice 5y ago
  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.
  - toast0 5y ago
    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.
  - fiddlerwoaroof 5y ago
    So, I did an easy migration of an analytics app from elasticsearch to ClickHouse: what made it easy was (a) we could replay an event stream to regenerate the database and (b) we had interfaces around the database defined in terms of the questions we needed the database to answer, and not in terms of ES-specific concepts. 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.
  - ziml77 5y ago
    I've given up on believing that there is such a thing as portable SQL. If you use the lowest common denominator, you're likely to have queries that perform suboptimally. Hell, even temp tables aren't portable because of the different syntax between databases. I've worked with tons of queries that would would take minutes to run without temp tables while running in milliseconds with them. People may as well take advantage of all the nice things their database offers instead of fearing a situation that is unlikely to happen and which will be a tough change anyway.
  - CRConrad 5y ago
    >>> [DB Sequences] > One often hears the counterargument 'but using DB-specific features makes your application less portable!' OK, sorry for the probably stupid question: Isn't it just a matter of, for each sequence, selecting its current value and then creating the new one in the target database to start from there? Should be, if perhaps not easily, still reasonably scriptable... Or what am I missing?
  - skrtskrt 5y ago
    Done it multiple times, most recently we got an MVP off the ground using only ElasticSearch as a backend, since the primary use case we wanted to validate and demonstrate was discoverability. As we added more features, the lack of transactions and relational structures started to slow us down, so we dropped in Postgres as a backend, and having application-generated UUID4s as primary keys was a big part in making that move fairly painless
  - gizdan 5y ago
    We're currently in the process of moving from MongoDB to CockroachDB. It's not been easy. Certain persistence needs to be duplicated for both of them. Certain queries need to be compared between the two, and we need to make a conscious decision when we pick from which DB. Having said that, moving logic for id generation to the application because it's less portable otherwise is an odd reason.
  - cnity 5y ago
    I did not cite reasons of portability for this reason, and generally I agree with you. Also some of the non-portable extensions of Postgres like PostGIS are practically indispensable for certain applications. 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).
- rvdginste 5y ago
  For backends, I typically use ID generation in the application layer (the backend). And with ID generation I mean the HiLo generator from Hibernate (or NHibernate). By default the HiLo generator will still use a monotonic increasing number, possibly with gaps. (Either way, as long as you expose some form of monotonic increasing number in your api, enumeration attacks could work. The correct way is to simply not expose the original id if that is an issue: you could expose some kind of opaque token in the api instead of a literal id.) The advantage of the HiLo generator is that the ORM knows the generated id up-front and does not need a round-trip to the database to determine it. So it can generate more performant queries. Also, contrary to UUIDs it does not have the disadvantages regarding indices. The only reason I have ever used UUIDs was because of a requirement that external clients needed to generate the ids.
netcraft 5y ago
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 systems) is a huge advantage.
- mjgp2 5y ago
  Uuidv4 have worse performance when inserting into the btree for the primary key.
  - orf 5y ago
    Why would you use a btree for them? Wouldn’t a hash index be ideal?
    - masklinn 5y ago
      > Why would you use a btree for them? 1. because PRIMARY KEY is its own constraint, and the underlying index is not under you control 2. because PRIMARY KEY further restricts UNIQUE, and as of postgres 14 "only B-tree indexes can be declared unique"
    - tomnipotent 5y ago
      Some databases don't give you a choice, and your table data is actually stored in a clustered index sorted by primary key. This means random inserts when using a UUID (without embedded time), and more page splitting.
  - williamdclt 5y ago
    That is true, but that has never been a bottleneck for me. We have a table with multiple hundreds of millions of rows, 20ish UUIDs indexes (yes it did get out of hand) and insert time isn't an issue. Ofc, your app performance requirements might vary, and it is objectively true that UUIDs aren't ideal for btree indexes.
cormacrelf 5y ago
That syntax seems a bit verbose for a column that will most likely appear on every table you ever create. How about: id int SIMILAR TO SERIAL BUT WITHOUT PROBLEMS MOVING THINGS AROUND And the other variant for when you aren’t sure that worked: id int SIMILAR TO SERIAL BUT WITH EVEN FEWER PROBLEMS MOVING THINGS AROUND
- iaabtpbtpnn 5y ago
  Complain to the SQL standard authors, not the Postgres developers :)
uhoh-itsmaciek 5y ago
Another favor you should do yourself is use bigint keys (unless using uuid as suggested elsewhere, or you're very, very sure you'll never chew through 2 billion ids).
olddb 5y ago
>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. Maybe it is because I'm too old, but making id grow by sequence is the way how things 'ought' to be done in the old skool db admin ways. Sequences are great, it allows the db to to maintain two or more sets of incremental ids, comes in very handy when you keeping track of certain invoices that needs to have a certain incremental numbers. By exposing that in the CREATE statement of the table brings transparency, instead of some magical blackbox IDENTITY. However, it is totally understandable from a developer's perspective that getting to know the sequences is just unneeded headache. ;-)
mixmastamyk 5y ago
From TFA: Instead of using GENERATED BY DEFAULT, use GENERATED ALWAYS.
tokumei 5y ago
Why? Concrete ownership is a good thing. I prefer more rigid structures and I like designing my tables around that. It was the Wild West when I did database design around MySQL 15ish years ago. Of course I didn’t know nearly as much as I do now back then.
- darksaints 5y ago
  There is still ownership, it is just that the sequence is owned by the table, and not the user that created the table.

cnity 5y ago

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.

superice 5y ago
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.
- toast0 5y ago
  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.
- fiddlerwoaroof 5y ago
  So, I did an easy migration of an analytics app from elasticsearch to ClickHouse: what made it easy was (a) we could replay an event stream to regenerate the database and (b) we had interfaces around the database defined in terms of the questions we needed the database to answer, and not in terms of ES-specific concepts. 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.
- ziml77 5y ago
  I've given up on believing that there is such a thing as portable SQL. If you use the lowest common denominator, you're likely to have queries that perform suboptimally. Hell, even temp tables aren't portable because of the different syntax between databases. I've worked with tons of queries that would would take minutes to run without temp tables while running in milliseconds with them. People may as well take advantage of all the nice things their database offers instead of fearing a situation that is unlikely to happen and which will be a tough change anyway.
- CRConrad 5y ago
  >>> [DB Sequences] > One often hears the counterargument 'but using DB-specific features makes your application less portable!' OK, sorry for the probably stupid question: Isn't it just a matter of, for each sequence, selecting its current value and then creating the new one in the target database to start from there? Should be, if perhaps not easily, still reasonably scriptable... Or what am I missing?
- skrtskrt 5y ago
  Done it multiple times, most recently we got an MVP off the ground using only ElasticSearch as a backend, since the primary use case we wanted to validate and demonstrate was discoverability. As we added more features, the lack of transactions and relational structures started to slow us down, so we dropped in Postgres as a backend, and having application-generated UUID4s as primary keys was a big part in making that move fairly painless
- gizdan 5y ago
  We're currently in the process of moving from MongoDB to CockroachDB. It's not been easy. Certain persistence needs to be duplicated for both of them. Certain queries need to be compared between the two, and we need to make a conscious decision when we pick from which DB. Having said that, moving logic for id generation to the application because it's less portable otherwise is an odd reason.
- cnity 5y ago
  I did not cite reasons of portability for this reason, and generally I agree with you. Also some of the non-portable extensions of Postgres like PostGIS are practically indispensable for certain applications. 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).
rvdginste 5y ago
For backends, I typically use ID generation in the application layer (the backend). And with ID generation I mean the HiLo generator from Hibernate (or NHibernate). By default the HiLo generator will still use a monotonic increasing number, possibly with gaps. (Either way, as long as you expose some form of monotonic increasing number in your api, enumeration attacks could work. The correct way is to simply not expose the original id if that is an issue: you could expose some kind of opaque token in the api instead of a literal id.) The advantage of the HiLo generator is that the ORM knows the generated id up-front and does not need a round-trip to the database to determine it. So it can generate more performant queries. Also, contrary to UUIDs it does not have the disadvantages regarding indices. The only reason I have ever used UUIDs was because of a requirement that external clients needed to generate the ids.

netcraft 5y ago

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 systems) is a huge advantage.

mjgp2 5y ago
Uuidv4 have worse performance when inserting into the btree for the primary key.
- orf 5y ago
  Why would you use a btree for them? Wouldn’t a hash index be ideal?
  - masklinn 5y ago
    > Why would you use a btree for them? 1. because PRIMARY KEY is its own constraint, and the underlying index is not under you control 2. because PRIMARY KEY further restricts UNIQUE, and as of postgres 14 "only B-tree indexes can be declared unique"
  - tomnipotent 5y ago
    Some databases don't give you a choice, and your table data is actually stored in a clustered index sorted by primary key. This means random inserts when using a UUID (without embedded time), and more page splitting.
- williamdclt 5y ago
  That is true, but that has never been a bottleneck for me. We have a table with multiple hundreds of millions of rows, 20ish UUIDs indexes (yes it did get out of hand) and insert time isn't an issue. Ofc, your app performance requirements might vary, and it is objectively true that UUIDs aren't ideal for btree indexes.

cormacrelf 5y ago

That syntax seems a bit verbose for a column that will most likely appear on every table you ever create. How about: id int SIMILAR TO SERIAL BUT WITHOUT PROBLEMS MOVING THINGS AROUND And the other variant for when you aren’t sure that worked: id int SIMILAR TO SERIAL BUT WITH EVEN FEWER PROBLEMS MOVING THINGS AROUND

iaabtpbtpnn 5y ago
Complain to the SQL standard authors, not the Postgres developers :)

uhoh-itsmaciek 5y ago

Another favor you should do yourself is use bigint keys (unless using uuid as suggested elsewhere, or you're very, very sure you'll never chew through 2 billion ids).

olddb 5y ago

>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. Maybe it is because I'm too old, but making id grow by sequence is the way how things 'ought' to be done in the old skool db admin ways. Sequences are great, it allows the db to to maintain two or more sets of incremental ids, comes in very handy when you keeping track of certain invoices that needs to have a certain incremental numbers. By exposing that in the CREATE statement of the table brings transparency, instead of some magical blackbox IDENTITY. However, it is totally understandable from a developer's perspective that getting to know the sequences is just unneeded headache. ;-)

mixmastamyk 5y ago

From TFA: Instead of using GENERATED BY DEFAULT, use GENERATED ALWAYS.

tokumei 5y ago

Why? Concrete ownership is a good thing. I prefer more rigid structures and I like designing my tables around that. It was the Wild West when I did database design around MySQL 15ish years ago. Of course I didn’t know nearly as much as I do now back then.

darksaints 5y ago
There is still ownership, it is just that the sequence is owned by the table, and not the user that created the table.