IntroductionUp

Database performance is not a supremely important concern for us, because the database is used more for development than for delivery of public access.

However, even for development the database has, under some conditions, performed unsatisfactorily and required attention to performance-improving measures.

Dedicated server

Operation of the database on a dedicated server has substantially improved performance compared with its operation on a cloud instance. Another way to say this is that a cloud instance delivering equivalent performance to a dedicated server would cost substantially more.

Memory

Operation of the database on a server that has enough memory to permit the entire database to be loaded into PostgreSQL shared buffers has substantially improved performance.

Precomputation

Creation and indexing of derivative tables, located in a deriv schema, has substantially improved performance by permitting common queries to be serviced with less table-joining and less computation.

Query experimentation

We have found that theoretically equivalent queries constructed differently can differ greatly in performance, and we have not found general rules that would predict the differences well enough to act as reliable guides. Therefore, we have found it necessary to experiment with variations in query formulation when performance is surprisingly poor.

Common table expressions

One method that has sometimes greatly improved performance is to replace temporary tables created with common table expressions with materialized temporary tables. One function (tr12lv (integer)) created two temporary tables and then joined them to one or two of the largest tables in the database. When we used common table expressions to create the temporary tables, the function required 23 seconds to operate on a minuscule set of records. When we replaced the common table expressions with create temporary table statements, the execution time decreased to 5 milliseconds. Experts have described CTEs as capable of improving and also harming performance. Our experience has confirmed this.