5 ms·

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

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

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