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.
I've used software that at least tried to be portable, so you could install it with whatever database you had available.
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.
> 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?
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
Having said that, moving logic for id generation to the application because it's less portable otherwise is an odd reason.
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).