Popular：
Virtualization DNS security formal verification reachability analysis compiler errors macro conflict web extension development framework Bitmap Graphics API inconsistencies All Tags

SQL NULLs: Breaking Your Intuition

2025-01-09

SQL's treatment of NULL values often defies expectations. This post reveals the surprising behavior of NULLs in unique constraint columns: multiple NULLs can coexist. Through practical examples in SQLite, Postgres, and MySQL, the author demonstrates how NULLs behave differently with the '=' and 'IS' operators, explaining the underlying reasons. Two solutions for ensuring uniqueness are explored: creating a generated column and using a partial index. Using a partial index is recommended as best practice, avoiding table size increases and potential errors.

Read more
(jirevwe.github.io)
Development

Convoy Ditches UUIDv4 for ULIDs: Efficiency and Scalability in the Big Data Era

2024-12-28

During a database migration from MongoDB to Postgres, Convoy decided to replace UUIDv4 with ULIDs as unique identifiers. The article compares UUIDv4 and ULIDs, highlighting ULID's advantages: lexicographical sortability, URL friendliness, and improved database indexing efficiency, albeit with slightly slower generation and insertion speeds. Convoy chose ULIDs primarily to solve pagination performance issues with large datasets. While ULIDs are marginally slower to generate, the performance gains, especially for large-scale data processing, are deemed worthwhile. The article also discusses potential time information leakage risks and user data implications of ULIDs, and briefly mentions the potential future adoption of UUIDv7.

Read more
(jirevwe.github.io)
Development