Learnings

<p>Postgres - Transpose Rows to Columns: </p>

<p>Django SQL Dashboard, an interface for running arbitrary read-only SQL queries directly against a PostgreSQL database, protected by the Django authentication scheme: </p>

<p>Practical SQL for Data Analysis: </p>

<p>Speeding up SQL queries by orders of magnitude using UNION: </p>

<p>Vinum is a SQL processor for Python, designed for data analysis workflows and in-memory analytics. </p>

<p>Fuzzy Name Matching in Postgres: </p>

<p>pg_stat_monitor is a PostgreSQL Query Performance Monitoring tool, based on PostgreSQL's contrib module pg_stat_statements </p>

<p>Key metrics for PostgreSQL monitoring </p>

<p>The cost of a join and some benchmarks </p>

<p>Must read if you need to know if multiple tables are empty in SQL. Small story: Once I had count check taking hours to run just to assert if the table was empty, this post explains how to fix it: </p>

<p>Use <code>TRUNCATE</code> with <code>RESTART IDENTITY</code> to restart sequences as well: </p>

<p>If you have 4 parallel workers and need to process all rows from a SQL table, probably it's best to split the work with LIMIT/OFFSET into 4 parts and consume them with 4 tasks than to chose some smaller LIMIT/OFFSET and generate more than 4 tasks. OFFSET can get really slow and it doesn't build up speed when you make consecutive ones like OFFSET 1000, OFFSET 1100, OFFSET 1200, etc. In Python: <code>limit = math.ceil(n_rows / n_workers) offsets = range(0, n_rows, limit)</code></p>

<p>Getting The Most Out Of Your PostgreSQL Indexes: </p>

<p>Select first row in each GROUP BY group? </p>

<p>Django Subquery + Count: </p>

<p>ORDER BY the IN value list: </p>

<p>Postgres DB-level for-each with LATERAL: </p>

<p>Django 2.0 and Conditional Aggregations </p>

<p>On SQL Server, if LEFT OUTER JOIN or INNER JOIN is taking too long to run, try using CROSS APPLY or OUTER APPLY instead </p>

<p>An instant JSON API for your SQLite databases: </p>

<p>Django 1.11 Subquery expressions: #subquery-expressions</p>

<p>Postgres full-text search is Good Enough! </p>