Lessons about SQL (page 3)

The links, tools and tips our engineers and designers share with each other, one “today I learned” at a time.

lessons shared: 46
people sharing: 11
database started in: 2017

github.com
pg_stat_monitor is a PostgreSQL Query Performance Monitoring tool, based on PostgreSQL's contrib module pg_stat_statements
João LinsMarch 6, 2021
brianlikespostgres.com
The cost of a join and some benchmarks
Rebeca SaraiNovember 17, 2020
adamj.eu
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:
Rebeca SaraiJune 8, 2020
postgresql.org
Use TRUNCATE with RESTART IDENTITY to restart sequences as well:
TRUNCATE TRUNCATE — empty a table or set of tables Synopsis TRUNCATE [ TABLE ] [ ONLY ] name [ …
Flávio JuvenalJanuary 2, 2020
Today I learned
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...
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: limit = math.ceil(n_rows / n_workers) offsets = range(0, n_rows, limit)
Flávio JuvenalJuly 25, 2019
pgdash.io
Getting The Most Out Of Your PostgreSQL Indexes:
Flávio JuvenalMay 27, 2019
stackoverflow.com
Select first row in each GROUP BY group?
Flávio JuvenalJanuary 21, 2019
stackoverflow.com
Django Subquery + Count:
Flávio JuvenalSeptember 24, 2018
blog.acolyer.org
Excellent article about how ORMs cause leaky abstractions that lead to performance issues and how fixing them is generally simple:
Flávio JuvenalJune 30, 2018
stackoverflow.com
ORDER BY the IN value list:
Flávio JuvenalApril 3, 2018
peterbe.com
Django 2.0 and Conditional Aggregations
Carlos CoelhoJanuary 12, 2018
blog.heapanalytics.com
Postgres DB-level for-each with LATERAL:
This post walks through a conversion funnel analysis that wouldn’t be possible in PostgreSQL 9.2
Flávio JuvenalJanuary 12, 2018
sqlrelease.com
On SQL Server, if LEFT OUTER JOIN or INNER JOIN is taking too long to run, try using CROSS APPLY or OUTER APPLY instead
Carlos CoelhoJanuary 11, 2018
github.com
An instant JSON API for your SQLite databases:
An open source multi-tool for exploring and publishing data - simonw/datasette
Flávio JuvenalNovember 16, 2017
docs.djangoproject.com
Django 1.11 Subquery expressions: #subquery-expressions
Flávio JuvenalJune 20, 2017

More lessons

Book a call

Found it useful?
Let’s put it to work.

Tell us about your product and the problem in front of you. We’ll bring what we’ve learned on real projects.

Tell us about your product, and we’ll bring what we’ve learned.

Still prefer the good old email? Contact us

Book a call

Lessons about SQL (page 3)

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

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

The cost of a join and some benchmarks

The cost of a join and some benchmarks

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:

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:

Use `TRUNCATE` with `RESTART IDENTITY` to restart sequences as well:

Use `TRUNCATE` with `RESTART IDENTITY` to restart sequences as well:

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...

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...

Getting The Most Out Of Your PostgreSQL Indexes:

Getting The Most Out Of Your PostgreSQL Indexes:

Select first row in each GROUP BY group?

Select first row in each GROUP BY group?

Django Subquery + Count:

Django Subquery + Count:

Excellent article about how ORMs cause leaky abstractions that lead to performance issues and how fixing them is generally simple:

Excellent article about how ORMs cause leaky abstractions that lead to performance issues and how fixing them is generally simple:

ORDER BY the IN value list:

ORDER BY the IN value list:

Django 2.0 and Conditional Aggregations

Django 2.0 and Conditional Aggregations

Postgres DB-level for-each with LATERAL:

Postgres DB-level for-each with LATERAL:

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

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

An instant JSON API for your SQLite databases:

An instant JSON API for your SQLite databases:

Django 1.11 Subquery expressions: #subquery-expressions

Django 1.11 Subquery expressions: #subquery-expressions

Found it useful?
Let’s put it to work.

Lessons about SQL (page 3)

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

The cost of a join and some benchmarks

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:

Use TRUNCATE with RESTART IDENTITY to restart sequences as well:

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...

Getting The Most Out Of Your PostgreSQL Indexes:

Select first row in each GROUP BY group?

Django Subquery + Count:

Excellent article about how ORMs cause leaky abstractions that lead to performance issues and how fixing them is generally simple:

ORDER BY the IN value list:

Django 2.0 and Conditional Aggregations

Postgres DB-level for-each with LATERAL:

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

An instant JSON API for your SQLite databases:

Django 1.11 Subquery expressions: #subquery-expressions

Found it useful? Let’s put it to work.

Use `TRUNCATE` with `RESTART IDENTITY` to restart sequences as well:

Found it useful?
Let’s put it to work.