0

I'm working with a PostgreSQL database where I need to store a numerical value associated with a specific key.

Over time, I will be continuously adding to this value based on the key.

I want to ensure that the table doesn't get bloated with multiple row versions or dead tuples, especially as this update operation will be frequent (like 100 req/s).

  1. What are the best practices in PostgreSQL to accomplish this?

  2. Should I use INSERT ON CONFLICT, a trigger, or another approach?

  3. How can I ensure that my table remains efficient and doesn't suffer from excessive bloat due to frequent updates?

Improve this question
2
  • 1
    "What are the best practices in PostgreSQL" -- just don't disable autovacuum.
    – mustaccio
    Commented Aug 27 at 18:30
  • It depends on the specifics of your case. Sizes, cardinalities, hardware, Postgres version, read/write patterns, concurrency, ... Like @mustaccio said, as long as autovacuum is working properly, it shouldn't get out of hands easily.
    – Erwin Brandstetter
    Commented Aug 27 at 19:42

1 Answer 1

Reset to default
1

You need to make sure that these updates become HOT updates. That allows PostgreSQL to remove the dead tuples (that cannot be avoided) to be removed much more efficiently, so that you won't end up with table bloat.

For that

  • set the fillfactor on that table to a value under 100 when you create it

  • make sure that the numerical column is not indexed

How low you have to set the fillfactor depends on the size of the table rows and the number of rows you modify in a single statement. For small rows and single-row updates, 90 is good enough.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.