Hacker News Reader

Comment by miguelgrinberg | Hacker News Reader

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.

View on HN

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

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

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

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

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.

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

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.

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

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

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.

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

Because many customers have their own expertise in the database system of their own choice. A shop that has hired Oracle DBA's doesn't want to hire Postgres folks. A MySQL based place doesn't want to hire Oracle folks. And they already have built extensive data-management processes around their choice of database systems and have the in-house experience to manage their database system in a wide variety of platforms.

If you to sell a packaged, deployable product to the widest market, yet want to minimise support, you leverage the expertise that the customer already possesses.

This is probably more of a consideration in the business enterprise space than some simple normal end-consumer SaaS, so I am not saying this approach makes sense everywhere.

I've done this before, used a sqlite as a unit testing stand-in for a read-only MSSQL database my app used. Allowed me to unit test the data transform code conveniently. But that's about as far as I'd go. Running tests against one database and production against other is a great way to discover bugs in production, like the text length constraint covered in the article. And if speed is of concern but data storage is not, just launch the test database with tmpfs backed storage.

Reply on news.ycombinator.com