3 ms·

Postgres (esoterically?) has some issues with index bloat on high-insert workloads, does PlanetScale do anything special to tune for this by default, since it c

Postgres (esoterically?) has some issues with index bloat on high-insert workloads, does PlanetScale do anything special to tune for this by default, since it caters to higher-perf workloads (over something like supabase)?

dangoodmanUT 1y ago
On another, similar, vein - i'd be curious to know if XID wraparound and auto vacuum tuning was something you had to advise customers on up front consdering how often that issue rears its head for the same kinds of workloads.
samlambert 1y ago
We don't do anything special (yet) but we do have bloat detection that we warn you about. We've noticed that autovacuum works very well on our Metal product because of the extra resources.
- hollylawly 1y ago
  More information about bloat detection here: https://planetscale.com/docs/postgres/monitoring/schema-recommendations#how-planetscale-detects-index-bloat https://planetscale.com/docs/postgres/monitoring/schema-reco...
petergeoghegan 1y ago
Can you provide more detail/a reference? I've done extensive work on improving the Postgres B-Tree code, over quite a number of releases. I'm not aware of any problems with high-insert workloads in particular. I have personally fixed a number of subtle issues that could lead to lower space utilization with such workloads [1][2] in the past, though. if there's a remaining problem in this area, then I'd very much like to know about it. [1] https://www.youtube.com/watch?v=p5RaATILoiE https://www.youtube.com/watch?v=p5RaATILoiE [2] https://speakerdeck.com/peterg/nbtree-arch-pgcon https://speakerdeck.com/peterg/nbtree-arch-pgcon
- dangoodmanUT 1y ago
  In a previous use case, when using postgres as a WAL-like append only store, I noticed that indexes would get massive. Then, after a while, they'd magically shrink. I had eventually switched to an API on top of Badger (golang KV), which afforded me an order of magnitude lower latency at ~30% of the resources IIRC. I'm sure there might have been some tuning I could have done to improve it. I've also heard similar behaviors exhibited from other folks who had similar high-write workloads on postgres. Sorry, I don't have anything super tangible to provide off the top of my head, or metrics/code I can share to recreate! It was also a project that required a lot of data to recreate the setup for.
  - petergeoghegan 1y ago
    > In a previous use case, when using postgres as a WAL-like append only store, I noticed that indexes would get massive. Then, after a while, they'd magically shrink. It's possible to recycle pages within indexes that have some churn (e.g., with workloads that use bulk range deletions). But it's not possible for indexes to shrink on their own, in a way that can be observed by monitoring the output of psql's "\di+" command. For that you'd need to REINDEX or run VACUUM FULL.
    - jashmatthews 1y ago
      Does vacuum not release free pages at the end of an index file in the same way it does for the heap?
      - petergeoghegan 1y ago
        No, it does not
    - dangoodmanUT 1y ago
      I may not be remembering fully, maybe the indexes never shrunk but the tables did in size. Is there no way to automatically clean up indexes then?

dangoodmanUT 1y ago

On another, similar, vein - i'd be curious to know if XID wraparound and auto vacuum tuning was something you had to advise customers on up front consdering how often that issue rears its head for the same kinds of workloads.

samlambert 1y ago

We don't do anything special (yet) but we do have bloat detection that we warn you about. We've noticed that autovacuum works very well on our Metal product because of the extra resources.

hollylawly 1y ago
More information about bloat detection here: https://planetscale.com/docs/postgres/monitoring/schema-recommendations#how-planetscale-detects-index-bloat https://planetscale.com/docs/postgres/monitoring/schema-reco...

petergeoghegan 1y ago

Can you provide more detail/a reference? I've done extensive work on improving the Postgres B-Tree code, over quite a number of releases. I'm not aware of any problems with high-insert workloads in particular. I have personally fixed a number of subtle issues that could lead to lower space utilization with such workloads [1][2] in the past, though. if there's a remaining problem in this area, then I'd very much like to know about it. [1] https://www.youtube.com/watch?v=p5RaATILoiE https://www.youtube.com/watch?v=p5RaATILoiE [2] https://speakerdeck.com/peterg/nbtree-arch-pgcon https://speakerdeck.com/peterg/nbtree-arch-pgcon

dangoodmanUT 1y ago
In a previous use case, when using postgres as a WAL-like append only store, I noticed that indexes would get massive. Then, after a while, they'd magically shrink. I had eventually switched to an API on top of Badger (golang KV), which afforded me an order of magnitude lower latency at ~30% of the resources IIRC. I'm sure there might have been some tuning I could have done to improve it. I've also heard similar behaviors exhibited from other folks who had similar high-write workloads on postgres. Sorry, I don't have anything super tangible to provide off the top of my head, or metrics/code I can share to recreate! It was also a project that required a lot of data to recreate the setup for.
- petergeoghegan 1y ago
  > In a previous use case, when using postgres as a WAL-like append only store, I noticed that indexes would get massive. Then, after a while, they'd magically shrink. It's possible to recycle pages within indexes that have some churn (e.g., with workloads that use bulk range deletions). But it's not possible for indexes to shrink on their own, in a way that can be observed by monitoring the output of psql's "\di+" command. For that you'd need to REINDEX or run VACUUM FULL.
  - jashmatthews 1y ago
    Does vacuum not release free pages at the end of an index file in the same way it does for the heap?
    - petergeoghegan 1y ago
      No, it does not
  - dangoodmanUT 1y ago
    I may not be remembering fully, maybe the indexes never shrunk but the tables did in size. Is there no way to automatically clean up indexes then?