Guide to Partitioning PostgreSQL Tables Using Timestamp-based UUIDs

The content describes an approach taken to enhance the performance of a PostgreSQL database table containing 28 million rows. Chris O’Brien and their team faced issues with query timeouts when conducting non-primary key searches, regardless of the indexing strategies they used. To address this, they explored table partitioning based on the insertion time of records but encountered difficulties due to the lack of support for globally unique indexes across partitions. The solution involved using UUIDs constructed from insertion timestamps, referred to as ULIDs, which allowed for both quick individual record retrieval and efficient broader data fetching by ignoring irrelevant partitions during queries. The article provides a detailed guide, including code examples, on setting up ULID conversion functions in PostgreSQL, manipulating ULID values in Elixir, ensuring timestamp consistency, and creating database migrations for table partitioning tailored to the use case of monthly partitions. Thanks to this method, they were able to achieve sub-second query times, eliminate timeouts, and maintain rapid row lookups.