This is the multi-page printable view of this section. Click here to print.

Return to the regular view of this page.

PostgreSQL

To properly configure PostgreSQL with Drupal you should ensure the following configuration is used.

Note: Some customizations might be necessary depending on your individual requirements.

postgresqlConfiguration:
  listenAddresses: "'*'"
  maxConnections: "200"
  sharedBuffers: 512MB
  workMem: 2048MB
  effectiveCacheSize: 512MB
  effectiveIoConcurrency: "100"
  maintenanceWorkMem: 32MB
  minWalSize: 512MB
  maxWalSize: 512MB
  walBuffers: 8048kB
  byteaOutput: "'escape'"
  hugePages: "off"
  walLevel: "replica"
  maxWalSenders: "0"
  synchronousCommit: "on"
  walKeepSegments: "130"
  checkpointTimeout: "'15 min'"
  checkpointCompletionTarget: "0.9"
  walCompression: "on"
  walWriterDelay: 200ms
  walWriterFlushAfter: 1MB
  bgwriterDelay: 200ms
  bgwriterLruMaxpages: "100"
  bgwriterLruMultiplier: "2.0"
  bgwriterFlushAfter: "0"
  maxWorkerProcesses: "8"
  maxParallelWorkersPerGather: "4"
  maxParallelWorkers: "4"

Note: The above is written in yaml syntax which will work for both Docker Compose and Kubernetes Helm Charts. For the postgresql.conf file itself without using these tools simply find the _ counterpart.

Queries leveraging ILIKE

There is a known PostgreSQL performance issue that exists in Drupal and is related to leveraging queries with ILIKE.

This issue is particularly noticeable in relation to the path_alias table.

There are patches being worked on to handle this in Drupal core but a very quick fix can be implemented leveraging pg_trgm.

There is a great blog article listed below which goes over this issue in more detail.

The instructions are a bit outdated so the updated syntax to enter in psql is given below:

CREATE EXTENSION pg_trgm;
CREATE INDEX path_alias__alias_trgm_gist_idx ON path_alias USING gist (alias gist_trgm_ops);
CREATE INDEX path_alias__path_trgm_gist_idx ON path_alias USING gist (path gist_trgm_ops);
ANALYZE path_alias;