Türker Yıldırım

sql

  • A recursive CTE that carries its own path to detect a cycle

    Note2 min read

    A depth limit stops a recursive query running forever and does not tell you the data has a cycle in it, which is a different problem with a…

  • A generated column with an index on it

    Note2 min read

    An expression in a WHERE clause makes an index unusable, and a generated column moves the expression into something indexable. The automatic substitution is the good part —…

  • A histogram bucket cannot be changed retroactively

    Note1 min read

    Bucket boundaries are chosen before any data exists, and re-bucketing loses the history rather than recomputing it. Including the objective threshold as an explicit boundary is what makes…

  • An index that made writes slower than the reads it saved

    Deep dive10 min read

    Eleven indexes on a table with a heavy write path. Every index is a write amplification that nobody measured, and three of them were never used.

  • A named volume that outlives the compose file

    Note2 min read

    docker compose down removes containers and networks and leaves named volumes, which is the safe default and the reason a database survives a version change you did not…

  • The staging environment that was lying

    Deep dive10 min read

    It passed on staging four times and failed in production four times. The same code, a different outcome, and five differences nobody had enumerated.

  • EXPLAIN ANALYZE reports what happened

    Note2 min read

    EXPLAIN shows the plan and the optimiser’s estimates, and the estimates are frequently wrong — a query planned for 40 rows that reads 400,000 looks entirely healthy. The…

  • Python for the data job, and the boundary that is a file

    Note2 min read

    A weekly analysis is a better fit for pandas than for PHP, and the integration should be the narrowest thing that works. A file with a documented schema…

  • LAST_VALUE is wrong unless you write the frame out

    Note2 min read

    LAST_VALUE with an ORDER BY and no frame returns the current row rather than the last one, which is correct by the specification and is never what anybody…

  • NTILE and PERCENT_RANK, for a report that wants quartiles

    Note1 min read

    A report asking for the top quartile of customers by spend was a subquery computing a count, a second computing a threshold, and arithmetic in PHP. NTILE divides…