Optimizing JSON Storage in Postgres with Supertoast Tables

Find out how Hatchet optimized the storage of JSON data in Postgres with 'supertoast tables', improving efficiency and performance.

Optimizing JSON Storage in Postgres with Supertoast Tables
Diagram of supertoast tables in Hatchet

In the world of modern web applications, efficient data management is essential to guarantee high performance and contained costs. Hatchet, a platform focused on running workflows and tasks, faced a significant challenge in storing large amounts of JSON data in Postgres. To solve this problem, they developed an innovative solution: "supertoast tables".

The Problem with TOAST Tables in Postgres

Postgres uses the TOAST (The Oversized-Attribute Storage Technique) technique to handle row values larger than 2 KB, splitting this data into multiple chunks within dedicated tables. Although effective, this approach has some drawbacks:

  • High space consumption: even small payloads can take up more than 50% of the database space, with the largest payloads exceeding 90%.
  • Inefficient data access: older payloads are rarely accessed, but still take up valuable space.
  • Impact on autovacuum operations: managing large amounts of TOAST data can significantly slow down database maintenance operations, increasing IOPS load and affecting overall performance.

The Solution: Supertoast Tables

To address these challenges, Hatchet introduced "supertoast tables", a solution that combines the efficiency of Postgres with the flexibility of external storage. The main features of this solution include:

  • Time-based partitioning: data is split into daily partitions, making data management and archiving over time easier.
  • Offloading of older data: payloads older than 24 hours are moved to S3, leaving only a reference to the external file in the database. This approach reduces space consumption and improves database performance.
  • Efficient write operations: the implementation of a write-and-swap system makes it possible to write data into a new partition, reducing the load on update operations and improving overall efficiency.

Implications for the SaaS Sector

The solution adopted by Hatchet offers interesting insights for other SaaS companies facing similar data management challenges:

  • Cost optimization: storing less frequently accessed data on external storage can significantly reduce the costs associated with storage and data management.
  • Better performance: by reducing the load on the main database, faster response times and greater system reliability can be achieved.
  • Scalability: the data offloading approach makes it possible to manage growing data volumes effectively, maintaining high performance even under increasing workloads.

Conclusion

Efficient data management is a crucial challenge for modern web applications. The innovative supertoast tables solution proposed by Hatchet is an example of how integrating relational databases with external storage can lead to significant improvements in efficiency, costs and performance. SaaS companies can draw inspiration from this approach to optimize the management of their data and offer better-performing services to their customers.