A couple years ago the company I worked for had had just launched a free consumer-facing application, and had been pushing it hard on social media. They were seeing hundreds to thousands of active users. It was painfully slow, and the culprit appeared to be the database. One of the main queries on the application was pushing the database server to its limits. Some users […]
Optimizing Away a LEFT JOIN in a Pinch Continue Reading »
From time to time, I have had to go back to a legacy database and for one reason or another add a non-integer surrogate key. Sometimes this has come up when working on an old API that exposes a primary key in the URL (GET /account/123). This generally can be bad for security, as a user could attempt to access other resources by incrementing the
Generating a Short Random ID in MySQL Continue Reading »
About 5 years ago I was introduced to Flyway, a tool for managing schema migrations using simple SQL scripts. I’ve used it on several projects now, and have more recently been introduced to Liquibase. The two tools both solve similar problems, with slightly different approaches. The main purpose of these tools is to version changes to the schema of the databasse. Liquibase has a good
Database Schema Migrations : A Few Lessons Learned Continue Reading »
PostgreSQL has two functions that are not standard SQL, but are very helpful in certain situations : GREATEST and LEAST. In SQL MIN and MAX are aggregate functions that operate across all rows. But there are times where you want the smallest or largest value in a finite number of columns, a scalar min/max. That is where GREATEST and LEAST come in. Both allow for
PostgreSQL’s GREATEST (and LEAST) function Continue Reading »
There have been a handful of times over the last few years where I have needed to take time series data and group the runs of data together to determine when a certain value changed, and how long it stayed that way. Sample Data We have a log table that, after keeping only the columns relevant for this post, looks like the diagram above. logged_at
Grouping runs of data in SQL Continue Reading »
TL:DR; takeaways: These seem very obvious, but with rushed deadlines, and “it works well enough”, these things are sometimes overlooked. The story I’ve been working on a project that involves taking flat files, importing them into SQLite, and exporting that database for use on low-powered client devices. I’ve had two such projects over the past couple of years, and each one has provided some small
Optimizing a SQLite Database Continue Reading »
For years I have relied on a simple property “show-sql=true” to see Hibernate’s generated SQL. This option is fairly limited: it bypasses the logging framework in the rest of my apps (SLF4J) it doesnt show parameters, just question marks it doesn’t show any timing information Frustrated at these limitations, I set out to understand all of the other options available to me. This is very
Hibernate Logging Options Continue Reading »
At work we have a copy of the zip-codes.com Business database, which I tend to reference somewhat frequently due to the nature of projects I work on. Today I needed a list of postal codes from major metro areas which would be used to drive a test data generator. The data generator has access to a simpler, non-commercial zip code list, and can do radius
Use SQL to Find Central ZIP Codes for Metro Areas Continue Reading »
Over the last couple of weeks, I have been spending some time troubleshooting performance issues in some of our Java-based RESTful services. This week I came across one that required some different steps to troubleshoot. First, the tl;dr – our JDBC driver (jTDS) converts CHAR to NCHAR, which causes an index SCAN on a CHAR column instead of an index SEEK. The Architecture Here is
Troubleshooting a SQL Server Implicit Conversion Issue Continue Reading »
