Table Design for Many Updates

From: "Craig Boucher" <craig(at)wesvic(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Table Design for Many Updates
Date: 2017-01-10 21:33:11
Message-ID: 48fc01d26b89$24dfe430$6e9fac90$@wesvic.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I have a multi-tenant database that I'm migrating from SQL Server to
PostgreSQL 9.6.1. I read the recent articles about the potential write
amplification issue in Postgres. I have one particular table that has 14
columns, a primary key, five foreign keys, and eight indexes. We have a
little over a thousand devices (this number will increase over time) on the
Internet that will insert a row into this table and then proceed to update
two columns in that row about once a minute for the next two hours. The two
columns are NOT NULL and are not FK or indexed columns. I've thought about
moving them to a one-to-one related table. Any thoughts on if this is a
wise move or if I'm making a mountain out of a mole hill? It looks like
this scenario would be covered by the Heap-Only-Tuple update but with over a
hundred updates to the same row and over a thousand different rows being
updated at a time, will I reap the benefits?

Thanks,

Craig

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David G. Johnston 2017-01-10 21:42:07 Re: Table Design for Many Updates
Previous Message Nicolas Paris 2017-01-10 21:30:08 Re: Materialized view vs. view