Tuning memory in PostgreSQL
Tuning memory in PostgreSQL
|
| Release 9.3 |
   |
Settings in postgresql.conf
To facilitate loading large amounts of data into a PostgreSQL database, you may need to increase the shared_buffers and max_locks_per_transaction values in the postgresql.conf file.
shared_buffersThe shared_buffers parameter designates the amount of memory used for shared memory buffers. The PostgreSQL documentation indicates that, for performance reasons, you likely need to use a setting greater than the minimum value of 128 KB or 16 KB times the number set for the max_connections value. It is recommended that shared_buffers be set to use several tens of megabytes for production installations.
When loading large amounts of data, you will most likely need a higher shared_buffers setting than the default value of 32 MB. After altering this parameter in the postgresql.conf file, you must restart the database cluster.
max_locks_per_transactionThe max_locks_per_transaction value indicates the number of database objects that can be locked simultaneously. In most cases, the default value of 64 is sufficient. However, when loading a large number of datasets (for example, several thousand) at once, the number of concurrent object locks for the transaction may exceed 64.
It is not a one-to-one relationship between concurrent locks and the number of datasets; in other words, if you are loading 3,000 datasets, you don't need to increase the max_locks_per_transaction to 3,000. Start by increasing the value to 100 prior to bulk loads.
When you change the max_locks_per_transaction parameter value, you must restart the server.
Increasing the value of either of these parameters could cause the database to request more shared memory than your UNIX operating system (OS) has available. For information on how you can increase the shared memory setting for your OS, consult the "Managing Kernel Resources" topic in the PostgreSQL documentation.
Improving SQL spatial query performance
When you execute SQL queries that return the ST_Geometry spatial columns from a business table, it could improve query performance if you set a system environment variable, ST_GEOMETRY_OUTPUT_FORMAT, to output to the ST_Geometry type rather than the extended Well-Known Text (WKT) representation.
The variable should be set on the computer where PostgreSQL is running.
For a Linux OS, set it for the shell. For a bash shell, the syntax is as follows:
ST_GEOMETRY_OUTPUT_FORMAT=ST_GEOMETRY
For a csh shell, the syntax is
setenv ST_GEOMETRY_OUTPUT_FORMAT ST_GEOMETRY
On Windows, create a system environment variable in the System Properties.
Variable name: ST_GEOMETRY_OUTPUT_FORMATVariable value: ST_GEOMETRY
After setting the variable, you must restart the PostgreSQL database cluster.
Be aware, though, that if you set the variable ST_GEOMETRY_OUTPUT_FORMAT to ST_GEOMETRY, you cannot backup and restore the database. If you set this variable because you plan to do a number of spatial SQL queries, be sure to remove it after you have finished your queries, then restart the PostgreSQL database cluster.
