7 ms·

For this particular application I discuss in the article it would make no difference, since there is only one not heavily used table that uses UUIDs. All the ot

For this particular application I discuss in the article it would make no difference, since there is only one not heavily used table that uses UUIDs. All the other tables use integer primary keys. Also this may be not a very popular opinion, but I prefer to design the database without using database-specific features. Things such as using SQLAlchemy and sticking to basic types. Sure it may affect performance some, but thanks to having done that I was able to port the app from SQLite to PostgreSQL in like an hour.

klysm 4y ago
> Also this may be not a very popular opinion, but I prefer to design the database without using database-specific features. I prefer to leverage the DB schema to do as much as possible for me. Generally with PostgreSQL that means using a lot of domain types etc.
msbarnett 4y ago
> Also this may be not a very popular opinion, but I prefer to design the database without using database-specific features. The problem with that is that SQL as a standard is so patchy and inconsistently implemented that effectively everything has database-specific features. You discovered that yourself when you inadvertently relied on SQLite's "feature" of letting you store UUID strings in an integer column. No matter how portable you imagine your schema is, it will work very, very differently between any two SQL implementations. I can't see any value whatsoever in voluntarily tying one hand behind your back performance-wise, honestly. It's not like changing "text" to "uuid" would have noticeably increased the time this port-job took you. As a wise man once said, if you're doing a job manually while the tool designed for that job is sitting idle beside you, the tool is smarter than you are.
- sgbeal 4y ago
  > The problem with that is that SQL as a standard is so patchy and inconsistently implemented that effectively everything has database-specific features. Noting that when it comes to such patchiness, sqlite's developers specifically prefer to follow PG's example. When they write new SQL features, their basis for comparison is literally always "do we get the same answer as PG?" Yes, there are differences between the two, but porting between sqlite and PG, provided no fancy PG features are involved, is very likely simpler than porting between any two other arbitrary SQL engines. (That's ignoring any performance factors - i'm only talking about semantic compatibility.)
PeterisP 4y ago
You can't completely avoid using database-specific features - even the most basic types such as strings effectively require database-specific treatment at least if you want strings that are long and/or unicode.
lenkite 4y ago
Prefer this approach too. Makes unit testing easier and makes data more portable. Stick to the commonly supported set of SQL data-types.
- smt88 4y ago
  > Makes unit testing easier In what language? Unit testing uses whatever DB client comes with your stack, and those clients have generic interfaces. For example, they will transparently decode UUIDs into strings when you read them, and then they will write them back as the database's UUID type when writing. As far as your code is concerned, they were always just strings. The same is true of dates for mainstream, mature stacks. > Stick to the commonly supported set of SQL data-types. Postgres is among the most vanilla SQL implementations. UUID/GUID/uniqueidentifier is available in every modern SQL-based RDBMS. Refusing to use the idioms of your database is a guaranteed way to lose performance for no reason.
  - lenkite 4y ago
    In languages like Go, Java and C++. The product in question is an integration product where customer can configure choice of database in public/private cloud. Using basic SQL types that have common support across all database vendors simplified development, testing and support. I have seen folks waste weeks (to months!) in data migration/porting and still lose time troubleshooting issues across databases when they used db specific features. Too much tech debt incurred. Loss of minimal performance weighed against simplicity and maintainability and no vendor lock-in. When you have the same tests and same schema and same DB layer code working against multiple databases, life becomes wonderful and hassle free.
    - smt88 4y ago
      > The product in question is an integration product where customer can configure choice of database in public/private cloud. I've seen this type of thing before, but it's not a common use case (and I'm not sure why people still do it). It makes more sense to just say that the product uses a FOSS RDBMS (like Postgres) and leave it at that. There's no vendor lock-in.
smt88 4y ago
> Sure it may affect performance some, but thanks to having done that I was able to port the app from SQLite to PostgreSQL in like an hour. This is unpopular because it's a bad tradeoff. In the real world, performance matters all day, every day, while switching from Postgres to SQLite is an incredibly unusual scenario (especially because their hosting models are completely different). Tuning a database for performance (or just taking the hit and scaling the database) is far more expensive than the likely-not-going-to-happen migration from one RDBMS to another. The vast majority of companies will never do it. It makes no sense to optimize for it at great cost elsewhere.
- spopejoy 4y ago
  That's ignoring the specific use case of TFA though, which is not "I'm setting out to build something for my bigcorp to scale infinitely" but "I built a little tool that ended up handling more than I thought it would, good thing I used an ORM so that I could switch to X based on actual metrics and A/B tests." I'm as anti-ORM as the next jaded dev but this is a picture-perfect use case for it: use an ORM to rapidly prototype and get the job done and leave a path for an industrial solution. The adage "you never switch your RDBMS" does not apply here.
JamesSwift 4y ago
> this may be not a very popular opinion, but I prefer to design the database without using database-specific features I used to be in this camp, having started my dev life in Rails when it ran Sqlite in dev. This turned out to be an anti-pattern for Rails because dev/prod parity was never quite there. Over time I also realized it just wasn't worth the effort to keep things agnostic. Sure a hypothetical migration will be easier later on, but that migration very, very, very (very) rarely occurs in my experience.

klysm 4y ago

> Also this may be not a very popular opinion, but I prefer to design the database without using database-specific features. I prefer to leverage the DB schema to do as much as possible for me. Generally with PostgreSQL that means using a lot of domain types etc.

msbarnett 4y ago

> Also this may be not a very popular opinion, but I prefer to design the database without using database-specific features. The problem with that is that SQL as a standard is so patchy and inconsistently implemented that effectively everything has database-specific features. You discovered that yourself when you inadvertently relied on SQLite's "feature" of letting you store UUID strings in an integer column. No matter how portable you imagine your schema is, it will work very, very differently between any two SQL implementations. I can't see any value whatsoever in voluntarily tying one hand behind your back performance-wise, honestly. It's not like changing "text" to "uuid" would have noticeably increased the time this port-job took you. As a wise man once said, if you're doing a job manually while the tool designed for that job is sitting idle beside you, the tool is smarter than you are.

sgbeal 4y ago
> The problem with that is that SQL as a standard is so patchy and inconsistently implemented that effectively everything has database-specific features. Noting that when it comes to such patchiness, sqlite's developers specifically prefer to follow PG's example. When they write new SQL features, their basis for comparison is literally always "do we get the same answer as PG?" Yes, there are differences between the two, but porting between sqlite and PG, provided no fancy PG features are involved, is very likely simpler than porting between any two other arbitrary SQL engines. (That's ignoring any performance factors - i'm only talking about semantic compatibility.)

PeterisP 4y ago

You can't completely avoid using database-specific features - even the most basic types such as strings effectively require database-specific treatment at least if you want strings that are long and/or unicode.

lenkite 4y ago

Prefer this approach too. Makes unit testing easier and makes data more portable. Stick to the commonly supported set of SQL data-types.

smt88 4y ago
> Makes unit testing easier In what language? Unit testing uses whatever DB client comes with your stack, and those clients have generic interfaces. For example, they will transparently decode UUIDs into strings when you read them, and then they will write them back as the database's UUID type when writing. As far as your code is concerned, they were always just strings. The same is true of dates for mainstream, mature stacks. > Stick to the commonly supported set of SQL data-types. Postgres is among the most vanilla SQL implementations. UUID/GUID/uniqueidentifier is available in every modern SQL-based RDBMS. Refusing to use the idioms of your database is a guaranteed way to lose performance for no reason.
- lenkite 4y ago
  In languages like Go, Java and C++. The product in question is an integration product where customer can configure choice of database in public/private cloud. Using basic SQL types that have common support across all database vendors simplified development, testing and support. I have seen folks waste weeks (to months!) in data migration/porting and still lose time troubleshooting issues across databases when they used db specific features. Too much tech debt incurred. Loss of minimal performance weighed against simplicity and maintainability and no vendor lock-in. When you have the same tests and same schema and same DB layer code working against multiple databases, life becomes wonderful and hassle free.
  - smt88 4y ago
    > The product in question is an integration product where customer can configure choice of database in public/private cloud. I've seen this type of thing before, but it's not a common use case (and I'm not sure why people still do it). It makes more sense to just say that the product uses a FOSS RDBMS (like Postgres) and leave it at that. There's no vendor lock-in.

smt88 4y ago

> Sure it may affect performance some, but thanks to having done that I was able to port the app from SQLite to PostgreSQL in like an hour. This is unpopular because it's a bad tradeoff. In the real world, performance matters all day, every day, while switching from Postgres to SQLite is an incredibly unusual scenario (especially because their hosting models are completely different). Tuning a database for performance (or just taking the hit and scaling the database) is far more expensive than the likely-not-going-to-happen migration from one RDBMS to another. The vast majority of companies will never do it. It makes no sense to optimize for it at great cost elsewhere.

spopejoy 4y ago
That's ignoring the specific use case of TFA though, which is not "I'm setting out to build something for my bigcorp to scale infinitely" but "I built a little tool that ended up handling more than I thought it would, good thing I used an ORM so that I could switch to X based on actual metrics and A/B tests." I'm as anti-ORM as the next jaded dev but this is a picture-perfect use case for it: use an ORM to rapidly prototype and get the job done and leave a path for an industrial solution. The adage "you never switch your RDBMS" does not apply here.

JamesSwift 4y ago

> this may be not a very popular opinion, but I prefer to design the database without using database-specific features I used to be in this camp, having started my dev life in Rails when it ran Sqlite in dev. This turned out to be an anti-pattern for Rails because dev/prod parity was never quite there. Over time I also realized it just wasn't worth the effort to keep things agnostic. Sure a hypothetical migration will be easier later on, but that migration very, very, very (very) rarely occurs in my experience.