Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

We saved $50k/year with a Go microservice coded in a hackathon

movio.co

Post reply on HN
Print view View original

21–30 of 264 posts

Re: We saved $50k/year with a Go microservice coded in a hackathon

#21

Post by wetha »

orf wrote: post #8

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. Obviousl…

I’m not the author of the post. Your comment assumes a well known schema. My understanding from the post is that this solution can join and filter on “custom” datasets of arbitrary schema that each of their customers upload.

Re: We saved $50k/year with a Go microservice coded in a hackathon

#22

Post by TimPC »

orf wrote: post #8

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. Obviousl…

I think the point is they don't know in advance what the query is and they didn't think they had a good solution to optimize all user entered variants across the range of possible groupings so they wanted a solution that was easier to optimize globally.

The general form of this is:

Select loyaltyMemberID from table WHERE V1_1= x_1 OR ... OR V1_n=x_n) AND (V2_1 = x_2_1 OR V2_2=x_2_2 OR ... V2_n=x_2_n) AND ... AND (Vn_1 = x_n_1 OR ... OR Vn_n= x_n_n) (some of these n's should actually be m_i's but I was lazy)

There may be some ability to optimize this in a number of ways but optimizing one example is not optimizing the general form. I can easily see how technology change could be a cleaner solution.

Re: We saved $50k/year with a Go microservice coded in a hackathon

#23

Post by toddBarkus »

orf wrote: post #8

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. Obviousl…

I was going to ask how to optimize the SQL in your post as it seems like the obvious/naive implementation of the query. If you're missing something, so am I. I can only imagine it was built up over time from googling specific terms that already missed the point, e.g. "rds mySQL union query"

Re: We saved $50k/year with a Go microservice coded in a hackathon

#24

Post by Matheus28 »

owenmarshall wrote: post #13
orf wrote: post #8

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. Obviousl…

> Stuff like this is bread and butter SQL. Ten or fifteen years ago, sure - a DBA would look at a query plan and figure out how to do it properly. Worse case you'd slap a materialized view in and query that. But this is 2018! Programmers don't want to treat the database as anything but one big key value store ;)

If you aren't implementing your own tabular database on top of your existing tabular database, you aren't 2018 enough :)

Re: We saved $50k/year with a Go microservice coded in a hackathon

#25

Post by flukus »

orf wrote: post #8

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. Obviousl…

> 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?

Oh I've seen this happen a lot. Somewhere along the line, often from a DBA, it is decided that sql in an app is evil and that everything must be in a stored proc. Then instead of some simple string concatenation you have to jump through hoops like this.

Re: We saved $50k/year with a Go microservice coded in a hackathon

#26

Post by toddBarkus »

cookiecaper wrote: post #19
owenmarshall wrote: post #13

Earlier quoted context omitted.

> Stuff like this is bread and butter SQL. Ten or fifteen years ago, sure - a DBA would look at a query plan and figure out how to do it properly. Worse case you'd slap a materialized view in and query that. But this is 2018! Programmers don't want to treat the database as anything but one big key value store ;)

Yeah, sadly, this is not too much of an exaggeration. I've worked on teams that insisted they needed DynamoDB, because, well, Dynamo is for "Big Data", and they certainly wouldn't work somewhere that had "Small Data"! Replace the buzzwords/products as applicable; you could actually probably just scramble them and it'd work just as well, since someone out there thinks "RabbitMQ means Web Scale", etc. SQL databases are…

I think this is a case for the return of the traditional "sysadmin" as "devops"/"SRE" is now the role of unblocking deploying a solution instead of questioning it's complexity/fitness.

Re: We saved $50k/year with a Go microservice coded in a hackathon

#27

Post by Matheus28 »

wetha wrote: post #21
orf wrote: post #8

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. Obviousl…

I’m not the author of the post. Your comment assumes a well known schema. My understanding from the post is that this solution can join and filter on “custom” datasets of arbitrary schema that each of their customers upload.

I've never played with this, but couldn't you create a table based on the dataset that the customers upload, and let your database engine handle filtering those queries? From the looks of it, even if they were doing full table scans for each query, it'd still be faster than all those unions...

Re: We saved $50k/year with a Go microservice coded in a hackathon

#28

Post by orf »

TimPC wrote: post #22
orf wrote: post #8

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. Obviousl…

I think the point is they don't know in advance what the query is and they didn't think they had a good solution to optimize all user entered variants across the range of possible groupings so they wanted a solution that was easier to optimize globally. The general form of this is: Select loyaltyMemberID from table WHERE V1_1= x_1 OR ... OR V1_n=x_n) AND (V2_1 = x_2_1 OR V2_2=x_2_2 OR ... V2_n=x_2_n) AND ... AND (Vn_…

> There may be some ability to optimize this in a number of ways but optimizing one example is not optimizing the general form.

I totally get that, but isn't that the point of the query optimizer within the database itself? Why are you trying to outwit it? It should select the right indexes, provided the columns are indexed, and "do the right thing(tm)". It might take a bit of cajoling but they seem pretty good at this. Postgres collects statistics about the distribution of values themselves within the table to guide its choice of index, so in theory it could rewrite the boolean logic to use a specific index if it's sure that it will eliminate a higher % of the rows than another plan.

In any case, it seems the SQL they posted is a bit off. Why nest each individual filter as a UNION? If you wanted to go down the UNION route couldn't you do each individual group as a UNION, with standard WHERE filters?

Re: We saved $50k/year with a Go microservice coded in a hackathon

#29

Post by any626 »

orf wrote: post #8

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. Obviousl…

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.

Re: We saved $50k/year with a Go microservice coded in a hackathon

#30

Post by [deleted] »

any626 wrote: post #29
orf wrote: post #8

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. Obviousl…

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.

[deleted]
Post reply on HN