Making PostgreSQL under the shop faster

That is all you type

“Analyse the performance of the PostgreSQL database on shop01. What can be improved on the server, do right away. What the shop application would have to change, write me as a report.”

Who: The admin, after a complaint from sales
Server: shop01, PHP shop and PostgreSQL 16 on one machine
Your part: One sentence, reading the report in the evening
Level: rwx Changes alone, see the three levels

Problem

The shop gets slower the more orders the database holds. Database and PHP share one machine, PostgreSQL runs on the installation's defaults, and whether the fault lies with the database or in the shop code nobody knows. An analysis costs a day with pg_stat_statements and query plans, and of the findings the admin can fix only half alone: the rest is up to the shop's developers.

Wanted: what is to be done on the server, done right away. What belongs in the shop code, as a report for the developers.

Solution

One sentence with two orders. Heinzel Pro measures, fits database and server, creates the missing indexes and writes down the three things only the shop can change.

Step 1: You split the task in two halves

The sentence above says what Heinzel Pro is to do itself and what is only written down. You pick shop01 and set the level to "Changes alone": the changes on the server are reversible, and the report is your part anyway.

A new session in Heinzel Pro: the typed sentence about the shop's database with its two orders, below it the chosen server shop01 and the level "Changes alone"

Step 2: Heinzel Pro measures

Three reading commands: the machine and the PostgreSQL settings, the most expensive queries and the tables read in full, the connections from php-fpm. The finding: 32 GB of memory, but 128 MB of buffers and costs as for spinning disks; two missing indexes that cost 60 % of the database time; 180 open connections, 6 of which work. Three things Heinzel Pro solves on the server, three more belong in the shop code.

The session: three reading commands with their output, memory and settings, the most expensive queries from pg_stat_statements, the connections, below them Heinzel Pro's answer with the finding and the split into server and shop code

Step 3: The plan for the server runs alone

Three steps: the settings in conf.d and a restart of three seconds, the two indexes without a lock while the shop runs, and the check with the same query as before. With them, what the restart costs and how everything goes back. Nobody approves; the plan is there before the first step runs.

The plan in the session: three steps with command, server and classification, settings, indexes and check, below them what could go wrong and how to undo it, with the note that the session changes alone

Step 4: The report for the developers

The article search takes 0.09 ms instead of 212 ms, PostgreSQL plans with 8 GB of buffers and SSD. Then the three points only the shop can change: the search without lower(), the 38 queries per product page down to one, and the 180 open connections from php-fpm. They stand in the answer, in the session's summary and in the journal of shop01, where the next session reads them.

The session afterwards: the commands that ran with the check through the new index, the journal entry for shop01 and Heinzel Pro's closing message with the report for the shop developers in three points