SQL for Institutional Research (IR)
One of the questions I see frequently from Institutional Research (IR) professionals are exploratory ones about getting started with SQL. They typically have a good background and understanding of how to work with data, but they just haven't made the leap to using a relational database. Data may be stored in CSV or Excel format (flat file style) and loaded into a statistical analysis package (such as SPSS, SAS, Stata, or even R) for analysis work. Over time, the desire for multi-year trend analysis leads to stacking files from multiple source files, which we all know gets unwieldy very fast.
This post attempts to outline some key elements for learning when starting down the road to adding a proper relational database into your data work flow. Practical code examples are given with small snippets throughout. If you're not ready to learn the code yet, feel free to skim over those examples.
I have also tried to provide external links to other great resources throughout, especially where there are differences between database platforms. So read on!
PostgreSQL Case Sensitivity Pitfall
A little while back I was working on Dockerizing my PostgreSQL instances, and I ran into a headache caused by the case sensitiveness of PostgreSQL. This isn't the first time that PostgreSQL has caught me off guard with being case sensitive where I didn't expect so I wanted to document this before I forget what happened. It was a bit more difficult to troubleshoot this time since I was erroneously blaming the issue on being related to running inside a Docker container, which turned out to not be a problem at all.
Performance Testing RustProof Content
In June I made the change to using my own custom-built blog platform: RustProof Content. Before going live I did enough performance testing to at least know that in terms of performance, RustProof Content would blow WordPress away. Now that it's been live for about a month and I had a bit of free time, I decided it was time to start benchmarking RustProof Content's performance a bit more thoroughly.
Setting Up for Testing
When deciding if I should leave WordPress or not, I used New Relic to help identify the issues I was experiencing with performance. Now that the switch has been made, I'm back using New Relic to ensure this site continues to perform as expected. I'm taking advantage of their Application (APM), Browser and Server monitoring tools to keep everything in check.
Garden Tracking Database using PostgreSQL
This year my wife and I have taken our gardening to a whole new level. We tore down a nasty old, wasp-infested shed in the sunniest part of our yard and built a raised garden bed in its place. We also setup an area inside with some crazy (awesome) LED grow lights that allow us to start plants from seed and produce healthy transplants, all in our house with zero south facing windows.
If you're not interested in the database I'm building, feel free to just look at the pictures of our garden scattered throughout this post!
This post is not about our gardens. It's about the database I am building to track our gardening endeavors though there are a bunch of pictures of our garden throughout.
New Blog Platform: RustProof Content
The RustProof Labs blog is now being served by our own, custom built system called RustProof Content. What is RustProof Content? Well, for starters... you're looking at it now!
Meet RustProof Content!
RustProof Content is a dynamic site built on the Flask micro-framework and Python 3.3+. Content is served from almost-static HTML files that include Jinja2 tags to fit into the layouts. Being mostly HTML, it is blazingly fast especially when compared to WordPress. The HTML files (including Jinja tags) are generated from content written and stored in Markdown format, while the source code and Markdown content are tracked and distributed using Git.
Mastodon