6 ms·

> Once again, SQLite happily stored my UUIDs on this column and even indexed them, but to get these across to PostgreSQL I needed to fix the schema and properly

> Once again, SQLite happily stored my UUIDs on this column and even indexed them, but to get these across to PostgreSQL I needed to fix the schema and properly declare these keys as strings. In PostgreSQL you're leaving performance on the table if you store UUIDs as strings instead of as the built in UUID type (128-bit value under the hood)

masklinn 4y ago
There's also no word about indexing or analysis (and postgres is very sensitive to having up-to-date statistics). And on sqlite you might be leaving memory / storage on the table if you use a UUID as PK and don't remove the implicit ROWID.
- miguelgrinberg 4y ago
  I did not feel that was a relevant topic for this article. I have spent a good amount of time over the life of this application optimizing indexes, though. But the article is about how I designed a test to benchmark these two databases, not a general discussion on all the optimizations you can make.
  - phibz 4y ago
    In postgres there usually no good reason to use varchar unless you specifically want to enforce size limits. If you don't care use TEXT, the performance is the same.
miguelgrinberg 4y ago
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.
cryptonector 4y ago
Plus PG and SQLite3 have fundamentally different approaches to types. SQLite3 has very few types, you cannot create new types, and is duck-typed. PG has many built-in types, you can create new types, and is (mostly) statically typed. You really have to know this going in. If you have to support both, you'll probably have to write two versions of your SQL -- or you'll end up using an ORM that supports the two RDBMSes, and then you may get stuck with the ORM. SQLite3 is amazing, but it's mainly amazing because of how easy it is to embed in apps. If that's what you need, use SQLite3. If you need more power, if you're building a server, then think long and hard about why not just go straight to PG. EDIT: SQLite3 is amazing for other reasons too.
- m-p-3 4y ago
  And SQLAlchemy made the process of migrating from one to another quite simple too. https://www.sqlalchemy.org/ https://www.sqlalchemy.org/
- password4321 4y ago
  > As of version 3.37.0 (2021-11-27), SQLite provides STRICT tables that do rigid type enforcement https://www.sqlite.org/datatype3.html https://www.sqlite.org/datatype3.html
  - m-p-3 4y ago
    Thanks! Here's the link to the actual details https://www.sqlite.org/stricttables.html https://www.sqlite.org/stricttables.html
    - cryptonector 4y ago
      I'm aware of this, and it's a very welcomed addition, though the types supported are still very limited, but... > though new types might be added in future releases of SQLite Intriguing! Can't wait for SQLite3 to have `CREATE TYPE`!
- anothercat 4y ago
  Is it really duck typed if no static type checking is performed on the SQL? Duck typing to me seems to imply checking quacks and waddles at "compile" time. I would think it's at most loosely typed and at worst dynamically typed. EDIT: Without the new strict mode of course, where it is not duck typed but typed by name. EDIT 2: Actually maybe duck typing is fine to use for dynamic languages too. The affinity of INT keys and stuff is sort of duck typing I guess.
nick_ 4y ago
Just wait until you learn that SQLite stores everything as a string :(
- zeckalpha 4y ago
  True, but there’s a difference between representing a UUID as a sequence of bytes in a string and as the ASCII encoded hex characters representing those bytes.

masklinn 4y ago

There's also no word about indexing or analysis (and postgres is very sensitive to having up-to-date statistics). And on sqlite you might be leaving memory / storage on the table if you use a UUID as PK and don't remove the implicit ROWID.

miguelgrinberg 4y ago
I did not feel that was a relevant topic for this article. I have spent a good amount of time over the life of this application optimizing indexes, though. But the article is about how I designed a test to benchmark these two databases, not a general discussion on all the optimizations you can make.
- phibz 4y ago
  In postgres there usually no good reason to use varchar unless you specifically want to enforce size limits. If you don't care use TEXT, the performance is the same.

miguelgrinberg 4y ago

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.

cryptonector 4y ago

Plus PG and SQLite3 have fundamentally different approaches to types. SQLite3 has very few types, you cannot create new types, and is duck-typed. PG has many built-in types, you can create new types, and is (mostly) statically typed. You really have to know this going in. If you have to support both, you'll probably have to write two versions of your SQL -- or you'll end up using an ORM that supports the two RDBMSes, and then you may get stuck with the ORM. SQLite3 is amazing, but it's mainly amazing because of how easy it is to embed in apps. If that's what you need, use SQLite3. If you need more power, if you're building a server, then think long and hard about why not just go straight to PG. EDIT: SQLite3 is amazing for other reasons too.

m-p-3 4y ago
And SQLAlchemy made the process of migrating from one to another quite simple too. https://www.sqlalchemy.org/ https://www.sqlalchemy.org/
password4321 4y ago
> As of version 3.37.0 (2021-11-27), SQLite provides STRICT tables that do rigid type enforcement https://www.sqlite.org/datatype3.html https://www.sqlite.org/datatype3.html
- m-p-3 4y ago
  Thanks! Here's the link to the actual details https://www.sqlite.org/stricttables.html https://www.sqlite.org/stricttables.html
  - cryptonector 4y ago
    I'm aware of this, and it's a very welcomed addition, though the types supported are still very limited, but... > though new types might be added in future releases of SQLite Intriguing! Can't wait for SQLite3 to have `CREATE TYPE`!
anothercat 4y ago
Is it really duck typed if no static type checking is performed on the SQL? Duck typing to me seems to imply checking quacks and waddles at "compile" time. I would think it's at most loosely typed and at worst dynamically typed. EDIT: Without the new strict mode of course, where it is not duck typed but typed by name. EDIT 2: Actually maybe duck typing is fine to use for dynamic languages too. The affinity of INT keys and stuff is sort of duck typing I guess.

nick_ 4y ago

Just wait until you learn that SQLite stores everything as a string :(

zeckalpha 4y ago
True, but there’s a difference between representing a UUID as a sequence of bytes in a string and as the ASCII encoded hex characters representing those bytes.