Planet PostgreSQL

Planet PostgreSQL -

Michael Christofides: Storing blobs in Postgres for our saved plans feature

The PGSQL Phriday topic this month is non-relational data, proposed by the excellent Ryan Lambert of Rustproof Labs. At first, I thought this might be yet another topic I can’t contribute much to… but then I remembered we do now store some some blobs in Postgres!  The challenge When we added the ability to publish query plans, back in May 2021, we needed a way to store them server-side. We considered our options, but went with the most logical place given our infrastructure — a bucket in Google Cloud. More recently, when we added the ability to save plans, we had a similar, but not identical, set of requirements — and this time we opted to save the plans in the database. At first, this may seem to go against standard advice, which I thought might make it interesting! By default, plans submitted to pgMustard are only stored in local browser storage, and are replaced over time. With saved plans, the aim was to allow people to select certain plans to keep longer term, or access them from multiple browsers, without having to publish them openly. Why store blobs in the database? We could easily have used a bucket for these plans, and retrieved them by id (like we do for published plans). However, a few factors led us to decide to buck the trend: Fewer moving parts — lots of folks were requesting to self-host pgMustard. While we don’t yet offer this, having fewer services needed for a self-hosted service felt wise. And naturally, our customers will be familiar with Postgres. 😎 Our database is over-provisioned — we deliberately store as little data as possible, for both privacy and security reasons. Despite being on one of the smallest instances Google Cloud SQL offer, our largest table has fewer than 5,000 rows so we have a LOT of room to use the database for more things.  We didn’t anticipate much usage — while saving plans is very important some of the time, we didn’t want to save them by default (for the same privacy and security [...]

本月PGSQL Phriday的主题是非关系数据,由Rustproof Labs的优秀Ryan Lambert提出。我最初以为这可能是另一个我几乎无法贡献的主题...但是后来我想起我们现在在Postgres中存储了一些大块!为了发布查询计划,我们考虑了各种选择,但最终选择了根据我们的基础设施最合理的地方——Google Cloud中的一个存储桶。最近,当我们添加了保存计划的功能时,我们有一组不同的要求,这次我们选择将计划保存在数据库中。尽管这可能违背了标准建议,但我们仍然选择了这样做,因为它具有趣味性。本文介绍了在完美的条件下,将大块文本存储在Postgres中可以起到很好的作用。

postgres

相关推荐 去reddit讨论

热榜 Top10

Dify.AI
Dify.AI
观测云
观测云
eolink
eolink
LigaAI
LigaAI

推荐或自荐