20200507 DB Q&A Notes

Attendees:

• Joel Henderson
• John Ritchey
• Adam Kendis
• Jake Mensch
• John Darragh
• Cynthia Kiser
• Tim Eccleston

Resources:

Meeting Video Recording
PG Performance Test
[Postgres SQL Range Types]9https://www.postgresql.org/docs/9.3/rangetypes.html)
Intro to materialized views:
PG Pass
Allows for Env File auto look at startup

Purpose(s) of the meeting:

• Background: 3 teams were looking for help with their postgres DB. We reached out to CFA and got Tim and Joel (with H4LA) to advise our devs.
• 311 - Needs help improving DB performance to have much faster response times for user requests prior to beta release in time for meeting with the mayor.
• Food Oasis - Needs some help with postgres. John has a lot of experience with DB Administration
• Civic Tech Index - Needs to setup a database for the project.

Notes:

PG Performance Test

Joel, using a typical MacBook, typical PG 12, and I generated a demo table and data. A few findings are below.

• No meaningful speed difference between using categories as text vs. categories as enum. I’m presuming that the categories are generally short words. Adding an index gave 4x speed.
• The PG time comparison functions are even slower than I thought. Adding an index to the timestamp column gave a 30x improvement.
• Pre-calculating a column “recent_week” and its index, as we discussed, was 10x /worse/ than just using the timestamp field and its index. EXPLAIN shows PG ignores the recent_week index, instead doing a full scan.