PostgreSQL configuration tips
The default parameters of PostgreSQL are usually very conservative about the underlying server capacity and might be suboptimal for moder hardware.
We cannot provide the best settings for all possible situation here, yet we provide some general guidelines in the following sections.
The mentioned setting names refer to the
Contrary to the general believe, a large number of connections is not so good. This number should be under 200. If needed a connection pool like PgBouncer can be used to reduce the number of connection to PostgreSQL.
In general, a dedicated PostgreSQL server on 64 bit Linux and more than 1GB of memory, should use about 1/4 of the total memory.
Setting the value to 1/2 of total memory would be a normal conservative setting, and 3/4 of memory is a more aggressive but still reasonable amount.
However, if you are running any other application on the same machine—such as the ToroDB JVM—, make sure you reduce the
effective_cache_size by the amount of memory needed by other applications.
This is the memory used by internal operations (sorting, everything that uses hash tables). If you are using ToroDB Stampede for OLAP queries, the optimal
work_mem setting will higly depend on the queries you are running.
Note that this setting is applicable per execution plan node: a single query can have multiple sort or hash-table operations. Likewise, queries running in parallel will also need the memory at the same time.
If needed this value can be set from the client when the query is launched, using the command
SET work_mem = '32MB'.
It's safe to set this value significantly larger than work_mem.
It is the maximum time between automatic WAL checkpoints. A value between 15 and 30 minutes is recommended.
synchronous_commit to off if you can tolerate potential data loss—e.g., if you use ToroDB Stampede as replication target but keep the required redundancy in MongoDB.
This is similar to MongoDB's behavior: writes are acknlowledged back to the application before the non-volatile storage has confirmed the write operation. Even though those write operations were confirmed to the application, they can be lost if the server crashes shortly after. You can tune the
wal_writer_delay setting to mitigate the risk of potential data loss.