61–70 of 91 posts
I once had a huge performance problem for a simple self-join that I expected would run quickly. I talked to our resident SQL expert (retired from IBM, now doing a job as the lab administrative assistant). She suggested sorting the data before inserting it. That sped up the query. it still bother me that data order matters so much. But, I can't reproduce the problem in MySQL any more anyway.
How long does it take to run the distinct on its own? I'd be interested to know what happens if the distinct is in a sub query then the result is joined with the commits table Ensure project_commits.project_id is in an index before running the test
TLDR: author doesn’t know how to use database indexes properly.
Am I missing something or is this blog post just someone discovering that ETL exists
Re-inventing it. (And yes, that's worse.)
I'd like to see the runtime for the same query but (1) without 'distinct', (2) without the date formatting and (3) with an inner join.
Earlier quoted context omitted.
Lots of "modern" blog posts seem like people rediscovering that X exists. A consequence of many skipping on traditional learning processes and how developers are now also forced into castings and portfolios.
It's also a negative side-effect of the improvement of software and automation in this space. In ye olden times, there would be an grumpy DBA in this story terminating the query and yelling at the user. Many, many people are consuming databases without understanding how to approach SQL optimization, because nobody is forcing them to!
I learned a lot about query optimization in my 3 years as an intern. I also caused a few issues along the way, including a number of PR reports to IBM. Some of which were benign, like incorrect documentation. Others were more problematic, such as semantically identical queries returning different results (e.g. coalesce vs case when null in a column spec) to knocking out the entire development LPAR on the mainframe with a simple select statement (hit a bug in the query optimizer and it crashed not just the DB, but the whole LPAR).
Still, those 3 years were invaluable to me. They developed both an intuition on how to write optimal queries, but also the knowledge of how to utilize the tools to identify problems, measure and fix them. It has served me well as a developer in the 15 years since I left my internship. I don't get into fights with DBAs since they recognize I know as much or more than them about optimizing queries on datasets I work with.
Earlier quoted context omitted.
On PostgreSQL I've also had really positive experience with the cstore_fdw from Citus. It's an extension you have to compile yourself but you end up with the ability to create compressed columnar tables. Trimmed some heavy queries that I was working with from about 4 hours to about 3 minutes. Also compressed the data from 220gb to about 20gb.
Yes, it's great. Part of the issue is that PostgreSQL isn't great at parallelism yet so optimizing the storage greatly reduces compute time to begin with. Unfortunately that extension has a bunch of limitations and issues that keep it from being production-ready. PostgreSQL could really use a proper columnstore table implementation, and there's a pluggable storage API on the roadmap but it hasn't gotten much traction…
http://tech.marksblogg.com/benchmarks.html
More interesting to me is the reverse: using FDW from the analytical DB to Postgres, e.g., https://aws.amazon.com/blogs/big-data/join-amazon-redshift-a...
If you're testing, take a sample of the database and test on that.
First try the simple form:
explain
select
project_commits.project_id,
date_format(commits.created_at, '%x%v1') as week_commit
from commits, project_commits
where project_commits.commit_id = commits.id;
Am I missing something or is this blog post just someone discovering that ETL exists
Yes, what the OP is doing can be called extract/transform/load, but that's an extremely general set of operations. Do you have some specific tool in mind that would make solving their problem a breeze? Is there a specific process that one should follow for ETL which was ignored?
Am I missing something or is this blog post just someone discovering that ETL exists
Well it's hardly surprising given the emphasis on CS trivia and fad-devops that exists in this industry. Basics like this are often neglected because employees are too busy chasing Kafka and Kubernetes or re-implementing skip lists on whiteboards. After all, if you can recall your textbook CS surely you can just re-invent the thousands of PhD and engineering hours that went into this stuff previously on an ad hoc bas…
And at the risk of sounding like a clown, I'll go ahead and ask: ETL seems to be such a basic and generic thing to the point that it's useless as a concept. What did those Phds achieve in those thousand hours that the author missed?