patternsqlMinor
Tuning Postgresql for writing of a non-critical DB
Viewed 0 times
postgresqlcriticalnontuningwritingfor
Problem
The DB is being used to store market quotes, so that losing data of one day is acceptable.
All writing operations are performed at a fixed time of a day, serially, by single connection.
The incrementing amount of data is about 2GB every day.
Because of the budget cutting, the physical storing device had to be changed to a traditional hard disk, from a SSD, and the writing time changed to 2+ hours from 10 minutes.
How should I tune my PG configuration, for writing, especitally with a traditional hard disk?
Env:Ubuntu server 20.04, Postgresql-11, CPU 4 core, RAM 8G, nothing else needs to be done when writing.
All writing operations are performed at a fixed time of a day, serially, by single connection.
The incrementing amount of data is about 2GB every day.
Because of the budget cutting, the physical storing device had to be changed to a traditional hard disk, from a SSD, and the writing time changed to 2+ hours from 10 minutes.
How should I tune my PG configuration, for writing, especitally with a traditional hard disk?
Env:Ubuntu server 20.04, Postgresql-11, CPU 4 core, RAM 8G, nothing else needs to be done when writing.
Solution
The best you can do is to use
UNLOGGED tables, which avoid writing to the transaction log. The disadvantage is that they become empty after a crash and cannot be replicated. So you would have to perform backups with pg_dump.Context
StackExchange Database Administrators Q#322752, answer score: 6
Revisions (0)
No revisions yet.