DEV Community: atabakoff

7 common misconceptions developers have about databases

atabakoff — Sun, 08 Oct 2023 13:38:06 +0000

Index does not contain all columns from a table - an index contains as many columns from a table as you define. If you put all columns into an index then it will contain all columns.
Index data is in memory - index data is stored on disk & read from it the same as the data table. A database reads the data from disk in pages/blocks and can cache it in memory, both data blocks & index blocks. The cache size is limited by available memory and rarely used blocks are evicted from it. An index block is a subject to eviction likewise a data block.
Index is fast because it's in memory - index is fast because it's utilizing a B-Tree data structure with logarithmic complexity. B-Tree is optimized for storing on disk and allows quick access even when an index isn't cached in memory.
If you have an index on columns used for search you are good - an optimizer decides whether to use an index or not based on its selectivity. If selectivity is low then the optimizer might decide that it's faster to perform a full scan, i.e. read the whole table sequentially.
Order of columns in where makes a difference - a database doesn't care if the order of columns in where matches the order of columns in a composite index. The optimizer is smart enough to figure out that the index can be used.
Order of columns in a composite index doesn't matter - it does matter because only leftmost columns can be used for index search. The index can't be used if you search by a rightmost column in a composite index.
If an index works on QA environment it will work in PROD - usually QA & PROD environments differ a lot by the size of datasets & columns cardinality. As a result an index that has good selectivity on QA might have awful selectivity on PROD. The selectivity might also degrade over time so you have to monitor slow queries & index usage.

I've interview few dozens of software engineers and these are the most common misconceptions they had. Many candidates weren't even able to explain what the index is, besides "it's used to speed up queries". Those who mentioned the B-Tree data structure still would often get the details wrong.

What is your experience interviewing devs? Do they generally have good understanding of database fundamentals?

5 personality traits that will hold you back as a developer

atabakoff — Fri, 06 Oct 2023 10:55:51 +0000

Lack of sense of humor - appropriate jokes make communication more pleasant and as a result more effective. No one wants to work with a robot or worse, a person got triggered by a harmless joke.
Being defensive - feedback is necessary to grow and defensiveness prevents from accepting & addressing the feedback and consequently from the growth.
Vindictiveness - sometimes people do offend you but in 99% it's unintentional. Either way, going berserk after them will prevent you from the actual work, worsen the communication and overall productivity of you, the team and everyone else involved.
Absence of self-irony - we are human and occasionally we make stupid mistakes. The presence of self-irony allows to recognize the situation, joke about it and move forward with a good mood.
Lack of self-reflection - one who wants to grow must reflect on both what he did & what feedback he got. Going back to this in a month, 3 months, 6 months gives you a different perspective and provides helpful insights. It's like a great book - to fully grasp it you read it the second & third time.

All the great engineers I worked with have excellent sense of humor & self-irony, they never defensive and they regularly reflect on their own performance & development. They appreciate the feedback & strive to address it.

Anything I missed?

Key Skills for a back-end Engineer

atabakoff — Sat, 18 Mar 2023 12:16:27 +0000

Solid understanding of SOLID and ability to apply it in the code.
Ability to write resilient unit tests.
Understanding HTTP and REST.
High level understanding of relational DBs (indexes & transactions & query plan).
Ability to write simple SQL queries (join & filter & group & order).
Basic understanding of distributed systems.
Basics of your language & experience with one mainstream framework.