
We saved $50k/year with a Go microservice coded in a hackathon - maloga
https://movio.co/en/blog/saving-money-with-Hackathon-project/
======
attaboyjon
I've come to the conclusion that the problem in tech is that all the people
doing the work are in their early twenties and have no idea what they are
doing. Once they get some experience they are quickly promoted to the CTO
position. Rinse and repeat.

What we have here is a classic dbms problem and no one at Movio seems to know
how to deal with that. Instead of migrating from Mysql to something serious
(Postgres) they move to some columnar DB no one has heard of. Nevermind that
postgres and a reasonably priced DBA and a little thought put into their data
model/queries could probably handle all their issues.

Sorry for the snark, cheers on a successful product.

~~~
davidp
I like to trot out this old gem[1] when people wonder why there's so much hate
for MySQL.

Nontransactional DDL alone is sufficient to classify it as a toy DB for me.
Yes, I've been personally bitten by it.

[1]: [https://grimoire.ca/mysql/choose-something-
else](https://grimoire.ca/mysql/choose-something-else)

~~~
cdubzzz
This talks a lot about 5.5 and mentions that 5.6 is “due out soon”. The
current release series is 5.7. How much of this is outdated and how much has
stayed the same?

~~~
lstamour
Some things are improving — for example:
[https://dev.mysql.com/doc/refman/8.0/en/atomic-
ddl.html](https://dev.mysql.com/doc/refman/8.0/en/atomic-ddl.html)

And [https://mysqlserverteam.com/new-defaults-in-
mysql-8-0/](https://mysqlserverteam.com/new-defaults-in-mysql-8-0/)

Previously on HN:
[https://news.ycombinator.com/item?id=5122299](https://news.ycombinator.com/item?id=5122299)

Personally, I only use MySQL and derivatives where I have to (basically
WordPress.)

------
orf
I don't quite get this. How fast was running this query:

    
    
       Select loyaltyMemberID
       from table
       WHERE gender = x
       AND (age = y OR censor = z)
    

Why the random complexity with individual unions and a group? Of course that's
going to be dog slow.

Sure, the filters can be arbitrary but with an ORM it's really really simple
to build them up from your app code. The Django ORM with Q objects is
particularly great at this.

Obviously I'm armchairing hard here but it smells like over engineering from
this post alone. Stuff like this is bread and butter SQL.

Edit: I've just read the query in the post again and I really can't understand
why you would write it like that. Am I missing something here?

Seems like a fundamental misunderstanding of SQL rather than a particularly
hard problem to solve.

~~~
any626
The user data is most likely in rows instead of columns. Instead of having

    
    
        id, name, age, gender
        1213, fake, 60, female
    

they would have

    
    
        property_id, user_id, value
        1 (assume age), 1213,    60
        2 (gender),     1213,    female
    

This gives them the freedom to add more properties to the user without always
having to add a column to the users table. When querying the database you'll
have to do unions or joins.

~~~
nstart
Oh gosh this pattern. The first time I encountered it was in my first job
where we used Magento. Super flexible. Also super slow. Does anyone have any
advice how to make a db design like this work faster? Generally I thought when
data is arranged like this it might be a prime candidate for document based
storage. But I'm no dba so I have no idea if that would be correct.

~~~
matte_black
If you are using Postgres, the JSONB datatype will let you do exactly this
while still using the full power of SQL. Simply create a column where you keep
a JSON object full of random user properties, if flexibility is what you want.
You can even index properties.

~~~
icebraining
The question is whether it can be stored like this while allowing for fast
queries. For example, unless it changed recently, Postgres doesn't calculate
statistics to help the query planner on jsonb fields.

~~~
zaarn
JSONB columns should behave just like any datatype with a GIN index in the
recent releases, to my knowledge.

Still, a JSON column will arguably be faster than a pure KV table since you
can more efficiently query it, especially any non-JSON columns.

~~~
ptman
IIRC JSONB still has problem with index statistics

So values in JSONB columns can be indexed nicely, but the statistics can be
much worse than for non-JSONB columns, which can lead the query planner
astray.

------
silveroriole
Gotta agree with others and say that they’re clearly skimming over the facts
that:

\- they didn’t have the expertise to actually fix the SQL. That query smells
bad. The data model smells bad. For some reason HN is always superstitiously
afraid of letting developers touch the database, but if you don’t let devs
touch the database enough you end up with this sort of thing; or that crap
data model with properties in rows instead of columns, because oh god, we
can’t let devs actually do DDL so we’d better make it all really flexible (and
incredibly slow because it’s a misuse of the database). I mean, implementing
your own result caching mechanism? I don’t know about MySQL but surely it has
its own caching mechanism (Oracle does) that isn’t being used because the
query is bad.

\- project management probably had no interest in fixing the
performance/incorrect data problems, and devs were expected to do it in their
own time.

In a way though this makes me feel better, other people are dealing with these
problems too and their overengineered solutions work and keep the company
running, I guess mine will too :)

~~~
mjburgess
There's no CTO on [https://movio.co/en/company/](https://movio.co/en/company/)

Seems like a red flag.

I'm all for companies releasing technical blog posts, but there's some really
strange framing here.

This is actually a story about how decisions get made, and how better ones can
be made. Reading a company's mea cupla tells you they are well-informed and
well-intentioned.

This is not a story which presumes good decisions were made and "the (tiny,
startup) database company went bust". That's their framing. Yikes.

------
hippich
What else it shows - how expensive AWS hardware vs hosting own hardware. I
guess you have to consider how often you have to scale, but hetzner offers
dedicated servers with 64Gb and NVMe drives starting from 54 euros per month -
[https://www.hetzner.com/dedicated-
rootserver?country=us](https://www.hetzner.com/dedicated-
rootserver?country=us) \- compare that to $580 per month these guys were
paying for i3.2xlarge instance..

~~~
bobwaycott
I’ve heard mention of Hetzner no less than a dozen times in the last couple
days. What’s their deal? I’m not quite sure I grok this server auction thing
they do, or how they’re so cheap.

~~~
foepys
Hetzner got in the news recently because they now offer a "cloud" product for
VPS. Not in the sense like AWS where you can shut down instances and pay less
but in the sense that you can buy, provision, and delete VPS via an API and
pay per hour. They are also dirt cheap and offer 20 TB egress traffic with
even their cheapest VPS.

How they do it? I don't know. They are using Xeon processors and not i7 like
some others.

------
partycoder
Unless you can directly see how a query can be optimized, first thing you do
is get the execution plan (e.g: EXPLAIN query).

The execution plan will tell you how expensive is each bit of your query and
help you adjust it.

From there, if things are not getting better, you have a lot of alternatives:

\- Consider creating an index

\- If the value doesn't change often, consider writing it into another table
or caching it.

\- Replication, partitioning, sharding, changing the schema.

\- Reconsider the requirement being implemented in order to have a more scoped
query or to perform the query less often.

Then... OLAP is not OLTP. If you can, do reporting in another database.

Finally, creating your own project in the end may not save you $50,000. How
about maintenance? tooling built around it? integration costs? documentation?
usability? new hires having to learn about it? You can hire people that
already know SQL without having to incur that cost yourself. All the tooling
is built, battle-tested and readily available. Plus, skills related to
internal tools are harder to trade in the market because they're harder to
verify and less transferable.

------
hnov
Elasticsearch works for this use-case quite well, you'd store a fairly
straightforward representation of the MySQL row as a document, query by the
fields you're interested in and ask for aggregations on the matching
documents. Common bitsets get cached automatically.

~~~
EpicEng
Or maybe just write a less insane SQL query to begin with?

~~~
baud147258
If I remember right, elasticsearch query are pretty insane to begin with, so
it's another problem.

------
mikeryan
This is cool but it seems strange (to me) that this was a “Hackathon” project
as opposed to just a stand-alone problem to be addressed as a normal course of
doing business. It doesn’t make the solution less cool. It just seems like a
strange distinction on what a Hackathon is.

~~~
neuromantik8086
A hackathon is something that used to be a cool party for geeks (i.e., a
Mathletics competition or an ACM programming contest) until the corporate
overlords bastardized it and converted a good thing into unpaid overtime with
free beer.

~~~
afterburner
While simultaneously "proving" that all projects could get done in one tenth
of the time.

~~~
meesterdude
Well, what would be the argument against that - if in fact it does deliver
code that solves a problem in a short period of time? Why can't you just do
that all the time?

~~~
raquo
Having a fairly small, well defined problem, working in a small self-selected
team, with no outside interference and typically no clients outside the team.
No managers, no PMs, no bug reports. This is not how day-to-day projects get
done.

Regardless of all of that, in _my_ experience a typical _impressive_ hackathon
project is still just a barely working demo that benefitted from a significant
amount of research and planning beforehand, and will require an even greater
amount of hardening and polish afterwards.

There is no magic, it's just a vastly different kind of work environment with
both inputs and outputs incomparable to day-to-day work.

~~~
baud147258
The place where I'm working has done a corporate "hackathon". It took place on
a normal day and I don't think there was any overtime, we got free food, but
all projects that delivered something relied on existing research and need a
good deal of polish afterward.

------
pbnjay
Cool post - Definitely an improvement and a good fit for Go services. I'm
curious - did you run performance comparisons on optimizing the SQL itself as
compared to adding this additional service?

Maybe I'm crazy, but just looking at that query it seems like there's
definitely room for improvement with the SQL alone. Unless the "..." is hiding
something I'm missing?

------
TeeWEE
Its not that a Go Microservice solved their problem. Its the different
algorithm they use for querying. That has nothing todo with Go, or
Microservices.

~~~
eeZah7Ux
sssh, don't break the spell...

------
hajile
This seems a lot less about go and much more about changing the approach to
the problem.

~~~
misja111
But it is the 'Go' in the headline that probably got this article promoted to
the frontpage of HN ..

------
manigandham
This is a great example of how to do things wrong. I'm surprised they couldn't
find any other columnstore database to take the place of InfiniDB in 2018.

The numbers they quote (5M members, 100M transactions) are tiny for any modern
data warehouse. Many solutions would run these in sub-second speeds without
changing the SQL at all, and it would be far better than building a quasi-SQL
engine in Go.

Actually for the occasional querying + caching that they have, something like
BigQuery or Snowflake data would be even cheaper with basically 0 operational
effort.

------
wiradikusuma
Just to be sure, any language will do right? Because I thought it was about Go
vs (put your slow programming language here).

~~~
tytytytytytytyt
It seems like it from his explanation. He says he'll explain why he thinks
only Go could have done it, but then nothing specific to Go really
materializes. This is how it seems to usually go with posts like these.

~~~
oblio
It's like those infamous enterprise benchmarks from yesteryear.

"NYSE moves from Solaris to RHEL and gains a 800% performance benefit".

While I don't doubt a brand new RHEL has more performance optimizations than
what is actually a SunOS 5.2, the guys benchmarking should have also said that
the original hardware was the equivalent of a PIII and now they're moving to
the latest Xeons.

I'm not kidding, I've actually seen a press release like this.

------
twic
Okay, before i've even read the article, i'm going to guess that they were
doing something egregously expensive in the cloud, and the microservice helped
them do it more efficiently - but still much more expensively than doing it in
a simple, old-fashioned way.

Now i'll read the article ...

EDIT: I would say i'm no more than 30% right. They were doing heavyweight data
crunching in the cloud, and so paying more for it than if they were doing it
on rented hardware. But that's a constant-factor thing; it's not like they
were downloading gigabytes of CSVs from S3 on every request or some such.
Their query looks suspect to me: couldn't it be written to do one big scan,
rather than unioning a load of things? Or is this the right way to write
queries on column stores? Still, there is no glaring obvious (to me) old-
school fix for this.

------
wasd
Great story, thanks for sharing. I wanted to ask a quick question about
something:

> Refreshing caches automatically

How do people usually handle this? Is this something done on the application
layer or database layer? Where is the cache stored?

~~~
owenmarshall
Caches could be stored in the database in a materialized view, in an external
service like memcache or redis, or even in the application itself.

Expiry can take a few different forms. Some caches have a defined space and
use a replacement scheme like "fill the cache up, then remove the least
recently accessed value". Some don't have defined sizes but instead remove
entries based on timestamps (cache for n minutes). Some depend on invalidation
messages from the application. It all depends on the applications needs.

The most important thing to remember is that caching means your system becomes
_inherently_ a distributed one. State can become split across multiple
sources, the cache can return stale data, invalidation might not happen when
you expect, ...

That's fine, but you have to program accordingly.

~~~
softawre
Good response. We mostly use redis as a LRU cache and it works great.

[https://redis.io/topics/lru-cache](https://redis.io/topics/lru-cache)

------
hordeallergy
Hackathons... [https://www.wired.com/story/sociologists-examine-
hackathons-...](https://www.wired.com/story/sociologists-examine-hackathons-
and-see-exploitation/)

------
maloga
Blogpost author here. Thank you so much for all the attention, comments,
upvotes, likes, retweets, etc! I've done a pass over the comments and can't
really answer them all but I'd like to clarify a few things:

There seems to be a general opinion trend that the queries generated by the
group builder algorithm are very inefficient, that it'd be easy to come up
with a solution with much better response times, and that that would be
achievable in any reasonable programming language in roughly the same time
with similar results.

The language argument will always be controversial and I won't address it
here; we have a point of view that is expressed in the Conclusion and on this
blogpost: [https://movio.co/en/blog/migrate-Scala-to-
Go/](https://movio.co/en/blog/migrate-Scala-to-Go/)

I can imagine that seeing a query with JOINs, subqueries, GROUP BYs and UNIONs
can raise some eyebrows, but there is some lacking context in that story, and
that's on me. Here's some of that context:

* The schema that the group builder algorithm operates on is not uniform in nature or composed of simple yes/no fields; it's an incredibly complex legacy schema that to a large degree wasn't even up to Movio: it's been up to the film industry as a whole, and it has evolved over the years, as is the case everywhere. Note that every different kind of filter translates to a very different kind of query, and we have more than 120 different filters, sometimes with dynamic parameters, and sometimes even bespoke for a particular customer!

* The group builder algorithm predates the team that built this service (myself included), as well as predating the first commercial release of Elasticsearch, MariaDB, mainstream Go success, etc. Nevertheless, it's still very fast and is being used today by ~88% of our customers (i.e. all the non-behemoths). It's been successful for many years, and continues to be, for the most part.

* But I don't like it because it's fast: I like it because it's simple and flexible. It allows our customers to build a really complex (and arbitrary) tree of filters to segment their loyalty member base, and it compiles all of that into one big SQL query, that in most cases is quite performant. That's pretty awesome. But yes; it doesn't scale to several million members.

* Migrating the very engine of the main product of a company is not a decision that is taken lightly. As is the case with every big company I can remember (e.g. Twitter, SoundCloud), behind a big success story there's always a legacy monolith, and our case is no exception. From that standpoint, achieving such breakthrough (i.e. cost reduction + significant response time improvement) within one hackathon day is really not all that common in my experience. Definitely something worth sharing, IMO.

Hopefully that clarifies some of the questions :) Cheers.

~~~
chupy
We had the same issue where I work and we are doing a very similar thing but
on a way larger scale (adtech) for audience building and we actually resorted
to compressed bitmaps since postgres was not cutting it. It's fairly easy to
just come on a forum and say hey: just use postgres/mysql/sql server without
reading the full article and understanding what you guys are dealing with.

------
pryelluw
I once built a micro service in Go that saved about as much. Took me a couple
of days (meetings and all that) from start to deployed. It still runs on the
same cheap aws instance. Go is really good at that sort of thing.

------
sankyo
what did the Go solution replace? I only read about DB changes, and cannot
draw any conclusions.

~~~
firasd
Right. The blog post is a good narrative over time but isn't super clear about
how they solved the problem. If I understand correctly they broke down the big
MySQL query into separate queries that the Go service processes/caches?

~~~
maloga
Blogpost author here. That is correct. Sorry if the explanation isn't super
clear; for this particular question, you can consider the two diagrams as a
before and after. They pretty much convey what you have explained here.

------
progval
short version: they used the right data structure for their problem instead of
a generic one.

------
bufferoverflow
The fact that their slow query scanned billions of rows means that they didn't
bother to simply shard by customer.

~~~
epse
Clearly they did, since they had to run a DB per customer. Those customers
just had a lot of customers

------
sAbakumoff
Interesting, why would anyone use movio instead of Facebook for targeted ads,
I am serious?

~~~
akx
> Movio Cinema's core functionality is to send targeted marketing campaigns to
> a cinema chain's loyalty members.

That's a different use case c.f. Facebook Ads.

------
z3t4
mySQL is very slow when it comes to joins, groups, etc. You are always better
off with simple select's. If you are using for example PHP the only viable
solution is to have the db crunch it. But when using Go, NodeJS et al. you can
pull the data out as a stream/array-like and apply filter/map/reduce and the
logic would probably be easier to manage, rather then generating a complex SQL
query. And would also allow you to stream the result to the client, instead of
having the user wait for it all before they see anything. A lot of money could
probably be saved by having the data on the client side, in for example web
db, and only use the servers for backups and syncing the data between clients.

~~~
amq
In my experience, if you have a proper schema, even very complex, but thought-
out, queries are instant on virtually any database size.

~~~
z3t4
Stuff like the group builder in the article is hard to reason about as there
are so many combinations. MySQL is bad at optimizing O(n2), no matter how you
design the schema it will be slow. The solution, like they probably did in the
article, is to break it down into separate more simple O(n) queries.

------
tzahola
So, instead of fixing your messed up data model, you wrote a service (sorry,
_micro_ service) which tries to keep your main DB in sync with a columnar
cache, so you can keep using that awful query with multiple UNIONs. Throw in
some "Go" and boy, you have your Medium post going!

~~~
icedchai
If they said "we added a couple of indexes to some database tables for 10x
improvement", it wouldn't be too exciting, right? ;)

