10 ms·
Cost of a Join
- jjeaff 8y agoI love a good writeup backed up with some performance benchmarks. But I'm a little befuddled as to why the three possible solutions to putting a "status" column in your Products table were: 1. Add a status_id column to the product table and reference a new status table 2. Add a status_id column to the product table and let the app define the mapping of what each status_id is 3. Add a status column of type text to the product table What about : 4. create an enum column with your possible statuses. If you ever reach more than 65k possible statuses, you've got a different problem. Perhaps the status example is just a hypothetical example? Although the author says they would use option 1 in this case, so what gives? Seems crazy to create a join table for 'status'.
- jrumbut 8y agoIt could be considerably easier to manage adding and removing and editing rows from a table rather than enum options (plus you can store other metadata). Particularly if you have some sort of more or less autogenerated CRUD system. Also, while this may be something of a "schema smell" a lookup table could be shared. In a small, fast project this could be useful.
- entee 8y agoPossibly because ENUM column types don't seem to exist in all SQL databases? I could be wrong though...
- craftyguy 8y agoENUM is in mysql/mariadb. Not sure about oracle or some of the more obscure ones..
- nerdponx 8y agoPostgres has it: https://www.postgresql.org/docs/current/static/datatype-enum.html https://www.postgresql.org/docs/current/static/datatype-enum...
- emmelaich 8y agoYou can just make an other table and map a few integers to the statuses. It's another join but I wouldn't think so much difference over the 'lookup' required for proper ENUM support?
- donarb 8y agoenum does exist in PostgreSQL (which is in the article's URL). But an enum still requires a lookup for the associated value when setting and getting rows, it's not free.
- jjeaff 8y agoIt's got to be way closer to free than a separate table join. But I did find a good answer hear about the pitfalls of enum in MySQL. Some/most of which would apply in the case of PostgreSQL.
- TylerE 8y agoPostgres joins against small tables are really really efficient. It wouldn't shock me if enums actually were implemented as tables.
- jaggederest 8y agoEnums add complexity that is better served by keeping the data relational, unless you have a really strong reason otherwise. If it was, for example, a statistical data table that was likely to have many billions of rows, I could see it, but then you're getting outside of the realm where you have truly relational data.
- barbegal 8y agoI would argue enums are less complex and easier to use. Imagine we were talking about a date field instead which is more complex? A products table with a column called date (of type date) Or a products table with a date_id column and a dates table with the columns date_id, date_year, date_month, date_day Sure the second option may seem more relational but it is more complex. It allows you to do more relational things later if you want to attach things to a date like an is_holiday or a was_raining column. So the question is, is there any value in adding the extra complexity now? If you foresee having to add more information to a status in a relational way like an is_active column then maybe a joined table is better.
- dragonwriter 8y ago> It allows you to do more relational things later if you want to attach things to a date like an is_holiday or a was_raining column. While I get that calendar tables are fun and all, if you have a date type you can just add a “holidays” table for that if you need it later, without a table of all dates.
- jaggederest 8y agoPrecisely. This is exactly why decomposing things into relations is important. The naive approach is to "keep all the things and all their details in one big table", and that's what causes so many problems.
- jaggederest 8y agoAn enum is a relation, it's just a relation that is not stored in the same kind of table that other data is. Your example is really a fairly poor strawman. I would suggest something more like a product with a "release date" column, which would be decomposed into a "releases" table with a foreign key. That's the kind of decomposition I've done a lot, and it's usually a useful one. Another example would be having an "address" field, versus having number, street, apartment/suite, city, state, zip, country. I generally go with address data in a separate, relational table, instead of inline (user_addresses vs on the user table directly), and I've found that to be very useful at times. In a lot of cases, you actually want a separate table (keyed on the real value, of course, not a generated foreign key) that stores things like zip codes or countries, because there is a lot of ancillary data to associate with them. If you value homoiconicity, you want all your data to be in as similar a format as possible. Enums are a special case, and so you should avoid them unless you have a strong reason otherwise.
- __s 8y agoI was using enums, but then I found out that you can't alter an enum inside a transaction.. very painful in a knex environment where up/down all occurs within a transaction
- nimchimpsky 8y agoEh? What do you mean. You can most certainly update a row with a new value in the enum column.
- bjt 8y agoThis is exactly the problem I found when wanting to use enums. They do not play nicely with transactional DDL.
- yen223 8y agoIf knex doesn't provide facilities for running migrations outside of a transaction, you're going to have an awful time. Useful operations like CREATE INDEX CONCURRENTLY can't be run in transactions.
- slavik81 8y agoIsn't that basically just syntactic sugar for #1? When you create an enum, it creates entries in a table with numeric values for enumsortorder and a string field for enumlabel. It seems like effectively the same thing. edit: It is different, as all enum values are rows in the same table and there is a third column to identify which type they belong to. The actual key used to refer to an enum value is not in the table; it's the oid of the table entry. So, I guess retrieving the data from the enum table is technically not a join.
- jjeaff 8y agoExcept enum abstracts away the need to create a join. Plus performance of enum is a tad bit better.
- gm-conspiracy 8y agoHow is performance when adding a new, additional enumerated value to the table that already has millions of rows?
- davidgould 8y agoIt is logically a join on oid to the pg_enum table. The implementation takes a few shortcuts but really the basic join machinery has been hand polished for decades so that doesn't make much difference. Because enums are stored in pg_enum which is just a table like the rest of the catalogs you can join to it with your own SQL queries. If you are very bold and a bit bad superuser you can even update them.
- thaumaturgy 8y agoIn MySQL at least, removing enums from a list requires running some expensive `alter table` operations. It's fine for lists where the possible values definitely, absolutely, for sure won't change, but for things like attributes on elements of a set, enums are evil.
- jjeaff 8y agoBut it's just removing that causes the problem. Which doesn't seem like an important feature. I almost always keep old values for historical purposes/backwards compatibility with backups. You can add values without issue.
- dizzystar 8y agoA table is easier to update, add, and delete from. Using an enum out of the gate is likely not the best first option, in my opinion.
- dirkgently 8y agoBecause enums only work for the simplest use cases. If you replace status_id with country_id, where each country_id has more than one property (country name, ISO alpha 2 code, ISO aplha 3, currency_id etc), you can see why enum isn't good enough.
- toast0 8y agoYou might not even need most of that country data in your database though, it could be in your application in many cases (the fastest join your database can do is the one that you do in frontend code instead)
- dotancohen 8y agoI don't know why people think this. In the specific example of country, in fact I _have_ tested using a country class with hard coded maps of country codes to arrays of data. Even so, benchmarking showed the single database call with a join (MySQL 5.1 or 5.5 I think) to be faster as I was already hitting the database anyway. Don't prematurely optimize away your database's flexibility (managed content) before testing a properly-coded query.
- toast0 8y agoI've seen MySQL do a lot of fairly dumb stuff with temp tables and order by. I've seen a lot of cases where just moving sorting to the frontend took a 3 second query down to 10ms and sorting on the frontend wasn't just a few ms too either. There were too many sorts available in the UI to add matching indexes in the db. If I can move CPU to the frontend from the db, that's a win, because scaling database servers is harder than frontends.
- kthejoker2 8y agoWhat about analytics, hosting dimensional data outside your DB sounds terrible.
- dirkgently 8y ago
- eximius 8y agoDoes an enum have any performance or consistency benefits over 1 or 3? (I discount 2 because it seems like the worst possible option.) It would seem like 1 is essentially an ad hoc enum with the same guarantees and 3 is just a bit easier to use as you can have the enum defined in your code rather than SQL.
- lucidone 8y agoI feel like status ought to go in its own table since it future proofs against other things having the same statuses - easier to refactor application logic and the table to be polymorphic than pull statuses from a potentially large table and do the same.
- NicoJuicy 8y agoI have the same idea, I like to use enums or flags. I don't need a separate table for it
- davidgould 8y agoI was really excited when enums were added to postgresql. But after a couple years of experience I stopped recommending them. Enums are a type. Which is fine, but if you have several different enum types used in different tables it can complicate moving data between databases as you can't simply pg_dump/pg_restore one table to a different database, you also have to create all the types used by that table, ie the enums. Which turns a simple pg_dump -t atable | psql -d otherdb into an exercise querying pg_depend, pg_enum and the other catalogs or hand parsing pg_dump schema output. If your operation does any amount of ad-hoc ETL you may find enums more trouble than they are worth.
- Terr_ 8y agoThe submitted URL is not stable, it just links to the front page. So for the benefit of later-visitors, here's the direct link: https://www.brianlikespostgres.com/cost-of-a-join.html https://www.brianlikespostgres.com/cost-of-a-join.html
- DerpyBaby123 8y agoThanks!
- jaggederest 8y agoHmm, I think these results are somewhat suspect. Postgres isn't even really hitting its stride yet at a million rows. You have to start looking at 100GB+ database sizes otherwise most everything fits in working memory, or at least the indexes do. My experience has been that joins are, in fact, cheap in larger databases but they do scale on the size of tables, so you should be cautious about excessive joining. In the extreme case, I wouldn't make a table for each attribute unless there were some overriding reason (at which point you've rediscovered column-based databases essentially). I do sometimes go up to 5th or DKNF normal form though, which I think is substantially beyond what most people designing or revamping a database would do.
- Jupe 8y agoNormalize till it hurts... Denormalize till it works :)
- dotancohen 8y agoThis is actually terrific advice. Thanks!
- lessclue 8y agoGolden words. This is the cycle that keeps repeating in complex production systems.
- zmmmmm 8y agoGreat to see this analysis. Especially with the proliferation of ORMs that create relationship tables like confetti it is very useful (and comforting) to know that needing to join a large number of tables is not in and of itself a performance disaster. It does not alleviate all my concerns with join-happy designs, but it is definitely good to know.
- asavinov 8y agoThere are two general (potential) problems due to the use of (multiple) joins: * run time: performance disaster * design time: conceptual chaos Some of them are analyzed in [1] where it is essentially argued that join considered harmful and a join-free (column-oriented) approach is described. This approach has been implemented in [2] which is a library for batch and stream data processing, and in [3] which is a library for data analysis. Their main common unique feature is that they rely on column operations, that is, data processing is described as a graph of operations with columns as opposed to using set operations like join. [1] https://www.researchgate.net/publication/301764816_Joins_vs_Links_or_Relational_Join_Considered_Harmful https://www.researchgate.net/publication/301764816_Joins_vs_... Joins vs. Links or Relational Join Considered Harmful [2] https://github.com/asavinov/bistro https://github.com/asavinov/bistro - A general-purpose data analysis engine radically changing the way batch and stream data is processed [3] https://github.com/asavinov/lambdo https://github.com/asavinov/lambdo - A column-oriented approach to feature engineering. Feature engineering and machine learning: together at last! (Disclaimer: I am an author)
- cuchoi 8y agoWould love to see an analysis that depends on the width (number of columns) of the table.
- protomyth 8y agoI really don't understand the reasoning for the 3 choices: 1. Add a status_id column to the product table and reference a new status table 2. Add a status_id column to the product table and let the app define the mapping of what each status_id is 3. Add a status column of type text to the product table 3 is unacceptable under any circumstances, and 2 really just makes no sense from a database perspective (unexplained values in a database is just wrong). Choice 1 is pretty normal, but I am really curious about the whole idea that any app needs to join with that status table to get anything but flags, values, state, or labels associated with that status. The idea of using a status table as the starting point of any join is just wrong and shows some very poor data management in the relationship between the app folks and database folks. I can see using a text field to look up and store the id, but really the mechanism that is populating your status table (with any flags, values, state, or text that is associated with each status) should generate the equivalent code or config for the app. Indexes on status are often painful because status is generally not a large number of values and having an index that doesn't cut down the number of rows significantly is often a problem. My big rule about this is if the app asks for a status field on an object, expect someone (probably your report writer) to want all the objects with the same value of status field. A typical example is open invoices. You really need to look at where the index is sparse and does it help in each situation. This is especially true if you track statuses through time (e.g. itemId, statusId, effBegUTC, effEndUTC)[1]. It is definitely worth the time to look at work tables, views, and each index with someone with some experience (yes, a good DBA). 1) If you are mostly looking to get all the items currently in a single status then leave out effBegUTC from the index and alway put a single value in the effEndDt for current (e.g. 9999-12-31). an index with (EffEndUTC, statusId) is better than (effBegDt, effEndDt, statusId). Including the itemId in the index really depends on the optimizer and how covered indexes are handled.