3

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.

Improve this question
1
  • Due to budgets cuts, you aren't allowed to use hardware you already have, and now need to spend your time away from other tasks, recovering from that decision? It sounds like most of the time is going to maintaining indexes, or maybe enforcing primary keys. Those are the thing I would expect to degrade upon such a change. So, what are those things like? How big is your table, and your biggest index, and all indexes combined? If there are FK constraints, how big is the foreign-side table?
    – jjanes
    Jan 30 at 16:17

2 Answers 2

Reset to default
6

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.

Improve this answer
5
  • Thanks! But I don't dare do it, because though losing one day data is acceptable, the tables store months data.
    – Leon
    Jan 31 at 1:38
  • Does it make sens to enlarge "wal_buffers"?
    – Leon
    Jan 31 at 1:39
  • Perhaps, if you are running large transactions rather than many small ones. Or if you have synchronous_commit = off.
    – Laurenz Albe
    Jan 31 at 6:20
  • Yes, I do have synchronous_commit = off, and my transactions all are huge, haha.
    – Leon
    Jan 31 at 7:31
  • You had answered many of my question! Good man! Thanks! God bless you!
    – Leon
    Jan 31 at 7:32
2

As an alternative to the UNLOGGED tables you could propose (to those who cut budgets) to keep a relatively small SSD to store your WAL files, while keeping table data on the cheaper storage; this will likely get you most of your performance back at a lower cost.

Improve this answer
1
  • Good idea! Maybe I should let the WAL file seat on another hdd?
    – Leon
    Jan 31 at 1:36

Your Answer

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

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