Day 22 - Audit your DB schema

It’s time to turn an eye toward your database schema.

Please spend 20 minutes reading through yours carefully.

Some things you might look for:

  • Inconsistent column names.
  • Missing indices for columns you frequently query by.
  • Missing unique indices to ensure uniqueness.
  • Missing null constraints.
  • Missing foreign key constraints.
  • Maybe even install bullet to detect N+1 queries in activerecord and mongoid .

Can you think of any other best practices that are missing from this list? Did you find an issue I missed? Please share it on the forum.

Enjoy!

Bullet is for Ruby (on rails) and for Laravel there is laravel-query-detector

Would be interesting to see if any queries was missed

1 Like

All our datetime columns end with _at as an implicit convention, except one on the users table called last_emailed_timestamp. I made a PR to rename it to last_emailed_at.

I spend the time looking into a query detector for Laravel and chose to try out the one from BeyondCode. I tried modifying one of the output classes to make it work when running unit tests.
I found some candidates but didn’t have enough time to actually fix them :-1: But now I know a tool to use when detecting N+1 issues.

I realized a table existed for a long-gone feature. Removed it, and made the accessors of the table no-op to ensure no breakages (in case some old client havent updated).

I remembered an SQL query that is pretty complex (UNION, multiple subqueries) and ran an EXPLAIN ANALYZE

Immediately noticed that multiple sequential scans were happening, where I put an index on each one. Thus managed to shave off ~30ms, from 200 to 170 :racing_car:

Luckily we’re in the middle of reworking some of our database and persistence methods. We identified tables no longer used and are actively working towards slimming that down this and next week.

We had bullet gem included, but somehow it was not used (maybe because of historical reasons). So I enabled it and fixed some (n+1)'s.
Also it’s possible to integrate it into CI: enable in test environment and write to logs.

Found lots of missing foreign keys and column types discrepancies (bigint in foreign key and int key in the referenced table, sometimes the other way around)
Fixed :+1:

1 Like

Nice. Fixed a three n+1 that had crept into places where we can’t have effective row level view caching.

Don’t forget about your caching though! Depending on the situation n+1 can be a benefit for caching.

This topic came at the best moment - we were about to review db structures today anyway.
Thanks for naming the problem of N+1 - we have already met this problem in past. Unfortunately, in Python world there is only one library nplusone package and it seems rather obsolete.
Anyway, we know, that simply logging db queries might give a hint.

At the same time I have found, our project cold benefit from little automation so I followed the advice from day 19 and ended up with (invoke based) tooling:

$ inv --list
Available tasks:

  db.dump     Dump docker db to stdout (using internal container pg_dump binary)
  db.logs     See logs from dev db in docker
  db.start    Start dev db using docker-compose
  db.status   Show status of dev db using docker-compose
  db.stop     Stop dev db using docker-compose

I use a package to keep an eye on the the amount of queries ran, fetched models and memory usage. Really helps me to keep things in check.

I spent some time on adding some null constraints and cleaning up some columns.

My project has a schema-less MongoDB store, but I could remove some deprecated keys from a “messages” collection. Note the multi switch to remove them from all documents, not just the first one found:

db.messages.update({}, { $unset: {'author_id':1}}, {multi: true})

Having a look for opportunities to add (or remove) an index is something that could also be added to the list :slight_smile:

Last week we found an unused column and today I sent a migration to remove this column :tada: the column used to handle if a record was already processed but we made a big refactor for that business logic last year.

I looked through the schema in one of our projects that has 9 tables. We’ve been pretty good with this project so I wanted to use this as a baseline for when we take a look at other projects over the next few months. I noticed that this project was missing the Bullet gem. I installed it and all the tests pass without exception :slightly_smiling_face:

It is new(ish) and all shiney. I did install my framework query detector to be made aware early of n+1 queries.