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.conffile itself without using these tools simply find the
Queries leveraging ILIKE
There is a known PostgreSQL performance issue that exists in Drupal and is related to leveraging queries with
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;