1

I have the same 5000 key/value pairs being read/written continuously (every 150ms or so) on a Debian system equivalent to a Raspberry Pi 3.

I don't care about persisting this data, it's recreated whenever my application server is launched.

Initially I used SQLite for this, using an in-memory table. However, now I want to access the data from multiple processes (using a tmpfs didn't work out great) and even from a remote client, as well as add an HTTP API, use LISTEN/NOTIFY for change notifications, so I'd like to switch to PG which is more appropriate for these.

Given these circumstances:

  • small dataset that fits in RAM
  • no need for persistence
  • low power PC
  • running 24/7 forever
  • don't want to thrash the flash storage

...what would be a good approach to configuring PG?

I found this 10yo question and the last update was 5 years ago saying to use a 3rd party extension, which I'm not too excited about.

jerkstorecalled
  • 85
  • 6
  • Have you tried the default configuration and tested your application? If that hasn't worked well for you, try https://pgtune.leopard.in.ua/ and it'll give you configuration that can help you. – zedfoxus Dec 13 '21 at 05:28
  • For a small in-memory available as a server, I would use [H2 Database Engine](https://en.wikipedia.org/wiki/H2_(DBMS)). As I vaguely recall it offers a Postgres emulation mode so Postgres-related tools can be used. – Basil Bourque Dec 13 '21 at 05:40

1 Answers1

0

You should create few indexes apart from the primary keys and keep the fillfactor of all your tables low, perhaps around 50. That should get you HOT updates, which will reduce the need for VACUUM and the amount of data written.

You may want to reduce shared_buffers to conserve memory, but keep it big enough to contain the database.

Set synchronous_commit to off to have less disk I/O. If you are ready to ditch the database after an unclean shutdown or system crash, you can set fsync = off, but then you have to remove the cluster after each crash. If you take it that far, you could reduce the write load further by using unlogged tables.

Set checkpoint_timeout high for fewer writes.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263