by scottcodie
1 subcomments
- I've spent my entire career developing databases (oracle, cassandra, my own database startup). Knowing if your workload is read or write heavy is one of the first questions when evaluating database choice, and is critical for tuning options. I would give this article hate just because it feels partially written by AI and the title needs a possessive 'your' in it, but its core ideas are sound and frame the issue correctly.
by Normal_gaussian
1 subcomments
- At the time of writing the query has a small error. The filter is checking for reads and writes, but it should be reads or writes.
WHERE
-- Filter to only show tables that have had some form of read or write activity
(s.n_tup_ins + s.n_tup_upd + s.n_tup_del) > 0
AND
(si.heap_blks_read + si.idx_blks_read) > 0
)
Should be OR
by spprashant
1 subcomments
- The thing we really strive for with Postgres is to keep the UPDATE traffic as low as possible. Because of MVCC, table bloat and the subsequent vacuum jobs will kill your IO even further. This means designing the applications and data model in a way that most write traffic is INSERT, with occasional UPDATEs which cannot be avoided. If you know you are going to have a UPDATE heavy table, be sure to set the fill_factor on the table ahead of time to optimize for it.
Also, in my experience "Faster SSD Storage" point applies to both read and write heavy workloads.
by SteveLauC
1 subcomments
- Regarding write-heavy workloads, especially for Postgres, I think we really need to distinguish between INSERTs and UPDATEs, because every update to a tuple in Postgres duplicates the whole tuple due to its MVCC implementation (if you use the default heap storage engine)
by wirelesspotat
0 subcomment
- Agree with other commenters that the title is a bit confusing and should be renamed to something like "Is your Postgres workload read heavy or write heavy?"
But title aside, I found this post very useful for better understanding PG reads and writes (under the hood) and how to actually measure your workload
Curious if the tuning actions any different if you're using a non-vanilla storage engine like AWS Aurora or GCP AlloyDB or Neon?
by J_McQuade
2 subcomments
- This, as a few other commenters have mentioned, is a terrible article.
For a start, the article does not mention any other database. I don't know how you can say something is read or write heavy without comparing it to something else. It doesn't even compare different queries on the same database. Like, they just wrote a query and it does a lot of reads - so what? There's nothing here. Am I going mad? Why does this article exist?
- This article quality makes me not trust the company.
by jagged-chisel
1 subcomments
- > When someone asks about [database] tuning, I always say “it depends”.
Indeed. On your schema. On your usage. On your app. On your users.
- Despite using CTEs, I found the first query quite impenetrable. Could be because I don’t spend that much time reading non-trivial SQL queries.
I’ve been mostly using the `pg_stat_statements` table (the second technique) to find out whether my workload is read or write heavy, it’s plenty good in most situations.
- Odd that OLTP wasn’t mentioned in the article.
Postgres an an OLTP databases, which are designed for write heavy workloads.
While that being said, I agree most people have read-heavy needs.
- ram heavy
- Is a ball red or green? How long is a piece of string?
by AtlasBarfed
1 subcomments
- Does anyone disagree that if it isn't a Log Structured Merge Tree engine, you aren't write-heavy?
- iotop?
- Surprising amount of downvoted comments under this article. I wonder why
- [flagged]
- Insipid text.
Also: HN needs to upgrade its bot upvoting detection tech. This is embarrassing. It was proper ownage of the HN #1 position for like 15 minutes straight. And then like #2-3 for an hour or so.
by developper39
1 subcomments
- Very usefull, and it is clear that the author knows what he is talking about.
Nice intro to Pg18 too.
Hacker News