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 ;)
SQL databases are amazing, robust examples of engineering. They are your friends and they're the appropriate choice for the vast majority of software. They are not outmoded or passe. Though I acknowledge there is a separate use case for K-V stores, I almost want to make policy preventing their use just because I know so many developers will abuse them badly and then stare back at you blankly during the semi-annual massive downtime event, muttering something like "Well, it's based on research at Google, so I'm sure there's a way to recover the data..."
If your columns have types, how can you encrypt them using custom keys for each one? Is it possible?
I want the keys to reside on the client and search for encrypted data. Basically single row lookups at a time.
I am however extremely wary of doing it that way. I don't know your requirements of course.
What I really need is just a huge table with two fields: “token”, “content”
And the token is basically the primary key but encrypted with whatever encryption.
You could even do foreign keys this way.
Hmm I suddenly have an idea. What about a layer above the database that basically enforces foreign keys and joins in this way to support end to end encryption? The content would reference ENCRYPTED foreign keys. Only clients would decrypt stuff.
Sounds more like a key value store and less like a relational database. Although you can store key value data in a relational db of course, there may be a better tool for the job.
It's short so you actually read it and possibly reread it. It's to the point. It has pretty pictures. And it had directly applicable advice.
My early education on databases always seemed to follow a "how do we make a database do this?" rationale rather than "what data do we need to store to support these features?", which I think leads to a software design that is too strongly coupled with the database. Software modules end up dependent on database features, or table structure, and refactoring or switching data stores becomes more costly.
Instead, start with a simple in-memory data store - a list of objects with some interface for accessing them, will probably your starting point. Add some basic serialisation/deserialisation features (CSV, JSON, etc) when you get past initial testing and require some persistent data. Then, once you have your API in place and your software design is stabilising, you should be able to map that data to a database fairly easily:
* The primary structure maps to your main table
* Child structures become additional tables, with foreign keys
* Data used to lookup records can be indexed for better performance
Beyond that, you should profile/benchmark your application to find what needs to be optimised, and then investigate whether your software design or your data store should be doing the optimisation.
Let your software's features influence the design of your database. Don't let the database's features influence the design of your software.
If it’s transactional, I recommend keeping calculations only if you need to access summarized data frequently. For example, if you are tracking inventory by storing the history of transactions that occur into and out of inventory, it’s trivial to find out how much of each item you have in stock at any point in time by doing a sum of the change in quantities for each item type up to that point.
If you were usually interested in the “current” count, it would be expensive to perform this sum every time, so instead you could keep a separate table for calculating the running total of inventory per item and referring to that. Keep this table up to date through the use of triggers on insert events (Note that your log of inventory transactions would thus be an immutable stream of events)
An example of something not worth storing is derived data that is a combination of separate columns in a table. For most queries it’s probably trivial to be lazy and wait to perform such a calculation until you actually need it. If you still want to have a ready made “table” that has all the computations you want already entered as columns for easy querying, use a view. If you find yourself making liberal use of views, you’re on the right track.
If you are using a separate data warehouse for data analysis, then precompute and denormalize as much as you can.
*Disclaimer: most of what I’m talking about is from a Postgres perspective.
If you are claiming to be full stack you better be prepared to go all the way.
Granted, such knowledge isn't immediately useful since it's something I or anyone is likely to do but it grants insight into systems. I know roughly how a query optimizer does and what it can, and more importantly, can't do.
When you know a system you can optimize for it. When you don't know a system you can only follow someone else's advice on how to optimize for it.
Nitpick: That's actually either three ^w or just one ^w, depending on how your WORDCHARS is set up. :)
In any case, having a basic understanding of the next level up (the electron) has proved quite useful to my career, otherwise I wouldn't know how turning things off then on again affects the machines I'm working with.
1) It took a weekend to complete. The friction was building for far longer. There's a cost to that, esp if it effects customer satisfaction and retention. They didn't refactor for fun, did they :) How many dev teams aren't so lucky? Is this article a no choice outlier, or a best practice?
2) My comment wasn't directed at the article but on another comment that blamed the developers. These problems should be owned by ownership / leadership / management more and engineers less.
3) That said, hire a DBA? I don't think that's necessary.
Without knowing the rest of their stack, or what their data ingestion looks like, I think your query is oversimplified. If they are doing a union, then it's likely they aren't querying one table, but they are querying multiple tables. The article mentions that individual customers had as many as 500 million rows. Likely each customer has their own set of data they also pipe into the system. Next their own custom query language may support more complex algebra than standard equality.
IMO, the article doesn't sufficiently describe the problem for us to understand why their solution works. To you and I there are 100 other solutions they could have tried that seem simpler than the one they presented.
It's less likely that they overengineered - we are probably just underinformed.
Based on 15 years in software companies in the valley it's much less likely that this isn't over-engineered. Nearly every decision I've seen chasing technology hype has been based on ignorance of existing solutions.
id, name, age, gender
1213, fake, 60, female
property_id, user_id, value
1 (assume age), 1213, 60
2 (gender), 1213, female
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.
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.
"Relational databases don't scale"
Well they worked fine for decades before Mongo's marketing claimed so.
Normalize it properly. If this is not possible, ensure that both the userid and the property are indexed
[0]https://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80...
Edit: Had some bad attempt at writing this query but it's rather late and it made no sense.
SELECT DISTINCT loyaltyMemberID
from members as m
INNER JOIN properties as p1 on m.id = p1.user_id
INNER JOIN properties as p2 on m.id = p2.user_id
INNER JOIN properties as p3 on m.id = p3.user_id
AND (p1.prop = 'gender' AND p1.value = x)
AND ((p2.prop = 'age' AND p2.value = y) OR (p3.prop = 'censor' AND p3.value = z)) SELECT DISTINCT loyaltyMemberID
from members as m
INNER JOIN properties as p on m.id=p.user_id
WHERE (p.prop='name' AND p.value = value) AND
...etc.If you get records back with prop1 then you have to remove those records from results based on another record.
There are multiple ways to accomplish this but it can't be done with a single join and simple where clause.
And they mention in the post that most queries don't use that many fields.
Then years later I ran into the guy who had to do some work on it after I left that company. I must have scarred him pretty badly, because he remembered it enough to bring it up as pretty much the first topic after the obligatory 'hey so what are you up to nowadays'. When I think back about it now, it was a cringey solution - then again, this was at a company where nobody had ever heard of a 'database index' (or if they did, never mentioned or implemented them).
[0]: https://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80...
"can be useful in various scenarios, such as rows with many attributes that are rarely examined"
They are querying these quite heavily, they aren't just random attributes they need to retrieve.
You could have
property_id, user_id, value(string)
1 (assume age), 1213, 60
2 (gender), 1213, female property_id, user_id, value_str, value_int
1 (assume age), 1213, null, 60
2 (gender), 1213, female, nullI think you're right. Oh ... my ... god ...
I wish I could say this is the worst example of a database schema I've ever seen, but it isn't.
Technology cycle:
X gets invented -> idiots abuse it -> X "is bad" -> Y (strictly worse than X) is "so much better" -> idiots abuse it -> Y "is bad" -> ...
In Mysql, it is JSON data type [1], in Postgres JSON/JSONB [2].
Creating indexes across them is doable, through a workaround (involving what is generally referred to as "VIEWS", but can be called calculated columns or something like that).
And, frankly, in the worst case for indexing, these databases still perform comparable to key-value stores in speed (especially SQLite).
[1] https://dev.mysql.com/doc/refman/5.7/en/json.html#json-paths
[2] https://www.postgresql.org/docs/9.4/static/datatype-json.htm...
Using an EVA, you can have a "keys (id, key_name)" table, and then only use the IDs in the values table, reducing that waste.
By the way, you don't need views for indexing on JSONB fields, it's supported out of the box in PG.
And I don't think you will necessarily get better performance with json fields vs an EAV model. Yes, you can index json fields by creating virtual views, but that requires that you know the field ahead of time. With an EAV model, you can have your values table indexed and then join.
But I am excited to start using the json field types. In many cases, it will really simplify things over the traditional EAV stuff.
As it stands the SQL query is quite silly. It gets a list of every user ID that is included by each filter and compares which ones are in the filters you want and not the filters you don't want. Much better is to pass the filters into SQL, let it figure out which users match the filters you want and not the filters you don't, and just use that result.
This leads to lots of unions in advanced queries, and makes filtering harder. Some databases even calculate column block statistics to optimize these queries by doing less IO even for seeming table scans.
Why not one table with all customers and one column per property? There are a few reasons, having to do with anything from MySQL sucking at schema alters for really big tables, to expectations of Enterprise customers.
https://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80...
Likewise you can get away with a full document GIN index.
I played around with some basic report stuff at work last year, the EAV data on my local machine, the report took ~7 seconds to run. I shoved the same data into PostgreSQL as JSONB, indexed it just as full doc cos I was lazy, the same report took ~80ms.
Obviously this isn't 'proof' my dataset was only 1.5m by 15m records. But with my limited knowledge i do believe it would perform better, I don't know how much better... but I think better...
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.
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?
What I am saying is, I really do not want a situation on my hands where the juniors that I work with, or most of the intermediates, and even a few of the seniors and leads, are writing raw SQL or query objects. Most of these folks have n years of experience in web and desktop application development and couldn't give you a passable answer to simple questions like, "What's a database index?" I know this isn't isolated to my current employer, or former employers, and I've seen it in other organizations where I've done some consulting on the side, and all of these folks I'm talking about here have largely worked else where in the past, too. And this in itself leads to other third-order effects, like the "SQL wizards" who get asked all of the "tough" SQL / database questions.
I want to stress that I understand the point that you're making, and I do agree with it, and of course, so do many (all?) ORM authors themselves, but I think the advice is wrong and is prone to take you to a much worse situation. I think we have an obligation as people who do grok SQL and databases to gently introduce our less experienced co-workers to the idea that ORMs are not a panacea to all database interactions, but until the companies we work have enough of an incentive to give us that sort of time and empowerment then I, for one, am going to recommend ORMs for everyone for everything unless they really, absolutely, demonstrably know what they're doing.
One of the problems, in my opinion, is that SQL isn't "cool" or hip and by many seen as not important to learn. While the new fancy Javascript based language or framework which nobody use and that will be replaced next week is much more important to learn.
Btw, get of my lawn :) /end old man rant
> could also ORM code with N+1 queries
Oh, they absolutely do, and when we're lucky they actually catch them on their own before they get to code review. Some folks reach for tools like Bullet [0] and, that's great, but unfortunately, sometimes they treat that tooling like the Holy Gospel. They develop an over-reliance on them as if those tools exist to offload critical thinking. Drives me crazy... in my experience, it's been hard to combat this type of thing, too. The pace of "agile," the calculus between paying down technical debt and mentoring and progress, I don't really know why but I haven't had a lot of long-term luck.
> One of the problems, in my opinion, is that SQL isn't "cool" or hip and by many seen as not important to learn.
I think you're really right about that. I happen to like writing SQL quite a bit and I take a little bit of pride in that I kind of sort of actually understand a little about what is going on in the database and even then I neglect that skill. I picked up copies of both "SQL Anti-Patterns" and "SQL Performance Explained" based on recommendations from this thread and am eager to get in to them this weekend. Still lots to learn... And, I have some SQL problems that I can see coming up over the horizon today and I hope this gives me the edge I need to start grappling with them sooner rather than later.
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.
> no chance of SQL injection then.
You know you can have sql injection attacks inside stored procedures? If you think stored procedures are a panacea then you don't understand the problem you're solving.
I'm not using something like Entity Framework and the CRUD apps I mostly wrote at work were well suited to a few simple sprocs.
The time it takes to write an ALTER script to change something pales in comparison to the two week change control process anyway...
It's quite fun.
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.