Re: [GENERAL] huge table occupation after updates

On Saturday, December 10, 2016, Tom DalPozzo <t.dalpozzo@gmail.com> wrote:

​I have one direct DB client (let's name it MIDAPP) only. This client of the DB is a server for up to 10000 final clients.

Any time MIDAPP is going to reply to a client, it must save a "status record with some data" related to that client and only after that, answering /committing the final client.

The next time the same final client will ask something, the same status record will be updated again (with a different content).

Why do you want to pay for concurrency control when you don't seem to need it?  While PostgreSQL likely can do what you need I suspect there are applications out there that can solve this specific problem better.  Even something as simple as a flat file, one per "final client", written atomically and fsynced after each write/rename.

David J,

Hi David,
 there are also other DB clients which only perform read queries using SQL. It's the reason why I chose postgreSQL over simpler apps. I didn't mention about them so far as those queries are not a concern in terms of performance.

Anyway, regarding the huge dimension of the table,  I think that reason was that autovacuum didn't work as the updates traffic was really high in my test, with no pause. Infact, if I lower it down to 1500updates/sec, then autovacuum works (I checked the log).
So the table size can grow but not for ever as it gets reused.
Thank you very much.

Pupillo