From shower idea to #Shiny app in two days. 😅 A little late for last week’s #TidyTuesday but I couldn’t stop myself from adding a bit more! Movable Data Type explores Project Gutenberg data across different storage backends, comparing structure and performance, running custom queries, and visualizing distributions. #RStats #SQL https://jmclawson.shinyapps.io/movable-datatype/

Today, I discovered that Postgres materialized views do _not_ inherit their parent table's indexes. This is why my spatial query is still running after 24hours.. 9000 × 50,000,000 = 450 billion geometry comparisons. Doh!

#postgres #computation #spatial #sql

postgres is all you need #sql

5 фишек про агрегатные функции в #SQL которые должен знать каждый:

1. COUNT(*) считает все строки, даже с NULL
2. AVG() пропускает NULL — но AVG(DISTINCT) даст другой результат
3. GROUP BY группирует, HAVING фильтрует группы
4. SUM(column) ≠ SUM(DISTINCT column)
5. MIN() и MAX() — быстрее, чем ORDER BY LIMIT

Подробности — в статье 👇
https://www.dev-notes.ru/articles/database/aggregate-functions-in-sql/

Агрегатные функции SQL — примеры и использование

В статье мы рассмотрим наиболее часто используемые агрегатные функции SQL, их синтаксис, практические примеры и способы оптимального использования в запросах.

Заметки разработчика

Why is it hard to scale a database? - Quora
"Why is it hard to scale a database?
There are four main challenges when scaling a database: search, concurrency, consistency, and speed.
#sql
Suppose you have a list of 10 names. To find someone, you just go down the list.

But what if there are 1 million names? Now you need a strategy for finding something. A telephone book lists the names in alphabetical order so you can skip around. This is a solution to the search problem.

What if 1 million people are trying to use the telephone book at the same time? This is the problem of concurrency. Everyone could wait in one very long line at City Hall, or you could print 1 million copies of the book -- a strategy called "replication". If you put them in people's homes -- a strategy called "distributed" -- you also get faster access.

What if someone changes their phone number? The strategy of replication created a problem, which is that you now have to change all 1 million phone books. And when are you going to change them, because they are all in use? You could change them one at a time, but this would create a data consistency problem. You could take them all away and issue new ones, but now you have an availability problem while you are doing it.

And what if thousands of people are changing their phone numbers every hour? Now you have a giant traffic jam called "contention for resources" which leads to "race conditions" (unpredictable outcomes) and "deadlocks" (database gridlock)."
https://www.quora.com/Why-is-it-hard-to-scale-a-database-1#:~:text=Why%20is%20it,%22%20(database%20gridlock).

Why is it hard to scale a database?

Answer (1 of 7): You have to define what “scale a database” means. One reason an existing database may have a hard time growing beyond a certain size and still meeting application-side performance requirements is it may have a poor schema. You can have a poor schema in both relational and “NoSQL...

Quora
Not too bad.
SQuizL 11-JUN-25
3/6 guesses
257 seconds
🟩🟦🟩🟩🟩⬜⬜
🟩🟦🟩🟩🟩🟩⬜
🟩🟦🟩🟩🟩🟩🟩
https://bit.ly/SQuizL-game
#SQuizL #SQL