# JOIN

Published articles for JOIN.

This is one page of public article previews, not the complete archive. Follow Next page to continue. Summaries are not the original full articles.

## A quick overview of atomics in C

DevFeed: [A quick overview of atomics in C](<https://devfeed.tech/articles/a-quick-overview-of-atomics-in-c-29428.md>)

Original publisher: [Read original article](<https://lemire.me/blog/2026/09/09/a-quick-overview-of-atomics-in-c/>)

Author: Daniel Lemire

Published: 2026-09-09T20:41:53Z

Content type: tutorial

Language: en

Sources: [Daniel Lemire](<https://devfeed.tech/sources/daniel-lemire.md>)

Topics: [C](<https://devfeed.tech/topics/c.md>), [Concurrency](<https://devfeed.tech/topics/concurrency.md>), [Concurrent Programming](<https://devfeed.tech/topics/concurrent-programming.md>), [Programming](<https://devfeed.tech/topics/programming.md>)

Tags: [atomics](<https://devfeed.tech/tags/atomics.md>), [c](<https://devfeed.tech/tags/c.md>), [join](<https://devfeed.tech/tags/join.md>), [ordering](<https://devfeed.tech/tags/ordering.md>), [posix](<https://devfeed.tech/tags/posix.md>), [programming-language](<https://devfeed.tech/tags/programming-language.md>), [systems](<https://devfeed.tech/tags/systems.md>), [thread](<https://devfeed.tech/tags/thread.md>), [threads](<https://devfeed.tech/tags/threads.md>)

### AI overview

This tutorial introduces atomics in C, explaining data races on shared non-atomic variables, atomic accesses, instruction reordering, and memory-ordering models including relaxed, release, and acquire semantics. It also discusses C11 threads and platform support limitations.

### Source excerpt

If you write in C, by default, you use a single thread. Extra cores do not help until you create more threads. However, if you include the header <threads.h>, you can pass a function to thrd_create, and wait for it with thrd_join. #include <threads.h> #include <stdio.h> int worker(void *arg) { printf("hello from thread %d\n", *(int ... Continue reading A quick overview of atomics in C

## Why JOIN USING Can Lead to Errors in SQL

DevFeed: [Why JOIN USING Can Lead to Errors in SQL](<https://devfeed.tech/articles/why-join-using-can-lead-to-errors-in-sql-28973.md>)

Original publisher: [Read original article](<https://blog.jooq.org/why-join-using-can-lead-to-errors-in-sql/>)

Author: lukaseder

Published: 2026-05-04T14:02:39Z

Content type: tutorial

Language: en

Sources: [jOOQ](<https://devfeed.tech/sources/jooq.md>)

Topics: [SQL](<https://devfeed.tech/topics/sql.md>), [Databases](<https://devfeed.tech/topics/databases.md>), [PostgreSQL](<https://devfeed.tech/topics/postgresql.md>)

Tags: [audit-columns](<https://devfeed.tech/tags/audit-columns.md>), [errors](<https://devfeed.tech/tags/errors.md>), [join](<https://devfeed.tech/tags/join.md>), [postgresql](<https://devfeed.tech/tags/postgresql.md>), [production](<https://devfeed.tech/tags/production.md>), [schema](<https://devfeed.tech/tags/schema.md>), [sql](<https://devfeed.tech/tags/sql.md>), [statement](<https://devfeed.tech/tags/statement.md>), [syntax](<https://devfeed.tech/tags/syntax.md>), [table](<https://devfeed.tech/tags/table.md>)

### AI overview

The article explains why JOIN USING and NATURAL JOIN can make SQL queries fragile as schemas evolve. Adding a same-named column can introduce ambiguity or change join behavior, causing existing queries to fail, so explicit join conditions are recommended for production queries.

### Source excerpt

Some SQL operators are as esoteric as they're powerful. One of the oldest operator that you've likely hardly ever used in real world applications is NATURAL JOIN which is the default in relational algebra. We've covered a funky use-case for NATURAL JOIN earlier on this blog. The main reason why it's not very useful is ... Continue reading Why JOIN USING Can Lead to Errors in SQL ->

## pg\_plan\_advice: Plan Stability and User Planner Control for PostgreSQL?

DevFeed: [pg\_plan\_advice: Plan Stability and User Planner Control for PostgreSQL?](<https://devfeed.tech/articles/pg-plan-advice-plan-stability-and-user-planner-control-for-postgresql-33636.md>)

Original publisher: [Read original article](<https://rhaas.blogspot.com/2026/03/pgplanadvice-plan-stability-and-user.html>)

Author: Robert Haas (noreply@blogger.com)

Published: 2026-03-04T17:55:00Z

Content type: opinion

Language: en

Sources: [Robert Haas](<https://devfeed.tech/sources/robert-haas.md>)

Topics: [PostgreSQL](<https://devfeed.tech/topics/postgresql.md>), [JOIN](<https://devfeed.tech/topics/join.md>), [modules](<https://devfeed.tech/topics/modules.md>), [Query (disambiguation)](<https://devfeed.tech/topics/query.md>)

Tags: [join](<https://devfeed.tech/tags/join.md>), [modules](<https://devfeed.tech/tags/modules.md>), [plan](<https://devfeed.tech/tags/plan.md>), [planner](<https://devfeed.tech/tags/planner.md>), [postgresql](<https://devfeed.tech/tags/postgresql.md>), [query](<https://devfeed.tech/tags/query.md>), [test](<https://devfeed.tech/tags/test.md>)

### AI overview

The article presents a proposed PostgreSQL 19 patch set introducing the pg_plan_advice, pg_collect_advice, and pg_stash_advice contrib modules. It demonstrates how pg_plan_advice can generate plan advice strings, preserve selected planner decisions, or vary advice to produce a different join plan.

### Source excerpt

I'm proposing a very ambitious patch set for PostgreSQL 19. Only time will tell whether it ends up in the release, but I can't resist using this space to give you a short demonstration of what it can do. The patch set introduces three new contrib modules, currently called pg_plan_advice, pg_collect_advice, and pg_stash_advice. Read more "

## Small improvement for pretty-printing in paste.depesz.com

DevFeed: [Small improvement for pretty-printing in paste.depesz.com](<https://devfeed.tech/articles/small-improvement-for-pretty-printing-in-paste-depesz-com-33672.md>)

Original publisher: [Read original article](<https://www.depesz.com/2026/01/06/improvement-for-pretty-printing-in-paste-depesz-com/>)

Author: depesz

Published: 2026-01-06T15:13:10Z

Content type: release

Language: en

Sources: [select \* from depesz;](<https://devfeed.tech/sources/select-from-depesz.md>)

Topics: [pretty printing](<https://devfeed.tech/topics/pretty-printing.md>), [JOIN](<https://devfeed.tech/topics/join.md>), [Command-line interface](<https://devfeed.tech/topics/cli.md>), [Script](<https://devfeed.tech/topics/script.md>)

Tags: [announce](<https://devfeed.tech/tags/announce.md>), [announcements](<https://devfeed.tech/tags/announcements.md>), [command-line](<https://devfeed.tech/tags/command-line.md>), [formatting](<https://devfeed.tech/tags/formatting.md>), [irc](<https://devfeed.tech/tags/irc.md>), [join](<https://devfeed.tech/tags/join.md>), [paste-depesz-com](<https://devfeed.tech/tags/paste-depesz-com.md>), [perl](<https://devfeed.tech/tags/perl.md>), [pg-prettify](<https://devfeed.tech/tags/pg-prettify.md>), [postgresql](<https://devfeed.tech/tags/postgresql.md>), [pretty](<https://devfeed.tech/tags/pretty.md>), [pretty-printing](<https://devfeed.tech/tags/pretty-printing.md>), [script](<https://devfeed.tech/tags/script.md>), [uncategorized](<https://devfeed.tech/tags/uncategorized.md>)

### AI overview

The article describes an improvement to the pretty printer on paste.depesz.com that produces better indentation for complex SQL join conditions. It also notes that the printer can be used from the command line or through a previously described script without storing content on the site.

### Source excerpt

As you maybe know, some time ago I made paste service, mostly to use for queries, or related text to share on IRC. One part of it is that it also has pretty printer of provided queries. Recently I realized that in case of complex join conditions, the output is, well, sub-optimal. For example: SELECT ... Continue reading "Small improvement for pretty-printing in paste.depesz.com"

## Using Stencil Templates for Code Generation in iOS Development

DevFeed: [Using Stencil Templates for Code Generation in iOS Development](<https://devfeed.tech/articles/stencil-30695.md>)

Original publisher: [Read original article](<https://habr.com/ru/companies/hh/articles/973028/>)

Author: CleverDevilV (hh.ru)

Published: 2025-12-04T09:45:15Z

Content type: tutorial

Language: ru

Sources: [HeadHunter RU](<https://devfeed.tech/sources/headhunter-ru.md>)

Topics: [iOS](<https://devfeed.tech/topics/ios.md>), [Swift](<https://devfeed.tech/topics/swift.md>), [Figma](<https://devfeed.tech/topics/figma.md>), [Kotlin](<https://devfeed.tech/topics/kotlin.md>), [JSON](<https://devfeed.tech/topics/json.md>), [Sublime Text](<https://devfeed.tech/topics/sublime-text.md>), [Visual Studio Code](<https://devfeed.tech/topics/visual-studio-code.md>), [Xcode](<https://devfeed.tech/topics/xcode.md>)

Tags: [figma](<https://devfeed.tech/tags/figma.md>), [filter](<https://devfeed.tech/tags/filter.md>), [ios](<https://devfeed.tech/tags/ios.md>), [ios-92ecabba3495](<https://devfeed.tech/tags/ios-92ecabba3495.md>), [join](<https://devfeed.tech/tags/join.md>), [kotlin](<https://devfeed.tech/tags/kotlin.md>), [split](<https://devfeed.tech/tags/split.md>), [swift](<https://devfeed.tech/tags/swift.md>), [tag-3383f6fe7741](<https://devfeed.tech/tags/tag-3383f6fe7741.md>), [tag-e95ee8b94dca](<https://devfeed.tech/tags/tag-e95ee8b94dca.md>), [text](<https://devfeed.tech/tags/text.md>), [vscode](<https://devfeed.tech/tags/vscode.md>), [xcode](<https://devfeed.tech/tags/xcode.md>)

### AI overview

A tutorial introducing Stencil, a template language used to automate code and file generation in iOS development. It explains the language's syntax, data structures, filters, and tags, and describes applications involving Swift, Figma-exported design-system data, analytics events, Kotlin files, JSON input, and editor support.

### Source excerpt

Что любят все разработчики? Конечно, автоматизировать рутинные повторяющиеся действия! Я расскажу, как в этом может помочь язык Stencil, широко известный в узких кругах iOS-разработчиков. Читать далее

## Make regular expressions easier to read

DevFeed: [Make regular expressions easier to read](<https://devfeed.tech/articles/make-regular-expressions-easier-to-read-37309.md>)

Original publisher: [Read original article](<https://muffinman.io/blog/make-regular-expressions-easier-to-read/>)

Author: Stanko

Published: 2025-03-31T00:00:00Z

Content type: tutorial

Language: en

Sources: [Stanko Tadić](<https://devfeed.tech/sources/stanko-tadic.md>)

Topics: [Code](<https://devfeed.tech/topics/code.md>), [formatting](<https://devfeed.tech/topics/formatting.md>), [Maintainability](<https://devfeed.tech/topics/maintainability.md>)

Tags: [example](<https://devfeed.tech/tags/example.md>), [extra](<https://devfeed.tech/tags/extra.md>), [join](<https://devfeed.tech/tags/join.md>), [maintainability](<https://devfeed.tech/tags/maintainability.md>), [readability](<https://devfeed.tech/tags/readability.md>), [regular-expressions](<https://devfeed.tech/tags/regular-expressions.md>), [value](<https://devfeed.tech/tags/value.md>)

### AI overview

The article presents a formatting technique for making complex regular expressions easier to read: split the expression into multiple string components, join them, and construct the final regex. The approach adds code and requires escaping backslashes, but enables scanning and comments that can improve readability and maintainability.

### Source excerpt

This is a simple formatting trick I use to make regular expressions more readable. The secret? Break them into multiple lines. To achieve this, format the regex as an array of strings and then concatenate the array into a single regex string. Example # Compare this example, written in a single line: const FILTER_REGEXP = /(?<name>blur|brightness|contrast|grayscale|hue-rotate|invert|opacity|saturate|sepia)\((?<value>-?\d*(?:\.\d*)?)(?<unit>\w*?)\)/g; with the multi-line version: const SUPPORTED_FILTERS = [ 'blur', 'brightness', 'contrast', 'grayscale', 'hue-rotate', 'invert', 'opacity', 'saturate', 'sepia', ].join('|'); const FILTER_REGEXP = new RegExp( [ `(?<name>${SUPPORTED_FILTERS})`, // filter name `\\(`, `(?<value>\-?\\d*(?:\\.\\d*)?)`, // value `(?<unit>\\w*?)`, // unit if any `\\)`, ].join(''), 'g' ); The multi-line version has a bit more code, but it's easier to scan and read. It also allows us to write comments for each section, clarifying the purpose of each part of the regex. I've found that complex expressions are much easier to write this way. I believe it greatly reduces the cognitive load for both the writer and the reader. Caveats # You'll need to escape the backslashes in regex strings. While this is a minor inconvenience, the readability benefits easily outweigh it. Conclusion # It boils down to personal preference, but I believe the extra code is worth it, as it improves readability and maintainability. This is especially true when dealing with notoriously difficult-to-parse complex regular expressions.

## An Efficient Way to Check for Existence of Multiple Values in SQL

DevFeed: [An Efficient Way to Check for Existence of Multiple Values in SQL](<https://devfeed.tech/articles/an-efficient-way-to-check-for-existence-of-multiple-values-in-sql-28931.md>)

Original publisher: [Read original article](<https://blog.jooq.org/an-efficient-way-to-check-for-existence-of-multiple-values-in-sql/>)

Author: lukaseder

Published: 2024-02-16T14:16:45Z

Content type: tutorial

Language: en

Sources: [jOOQ](<https://devfeed.tech/sources/jooq.md>)

Topics: [SQL](<https://devfeed.tech/topics/sql.md>), [Benchmark](<https://devfeed.tech/topics/benchmark.md>), [Oracle Database](<https://devfeed.tech/topics/oracle-database.md>), [PostgreSQL](<https://devfeed.tech/topics/postgresql.md>)

Tags: [benchmark](<https://devfeed.tech/tags/benchmark.md>), [benchmarking](<https://devfeed.tech/tags/benchmarking.md>), [compare](<https://devfeed.tech/tags/compare.md>), [count](<https://devfeed.tech/tags/count.md>), [database](<https://devfeed.tech/tags/database.md>), [derived-table](<https://devfeed.tech/tags/derived-table.md>), [execution](<https://devfeed.tech/tags/execution.md>), [exists](<https://devfeed.tech/tags/exists.md>), [join](<https://devfeed.tech/tags/join.md>), [limit](<https://devfeed.tech/tags/limit.md>), [mysql](<https://devfeed.tech/tags/mysql.md>), [oracle](<https://devfeed.tech/tags/oracle.md>), [oracle-23c](<https://devfeed.tech/tags/oracle-23c.md>), [performance](<https://devfeed.tech/tags/performance.md>), [postgresql](<https://devfeed.tech/tags/postgresql.md>), [rdbms](<https://devfeed.tech/tags/rdbms.md>), [sql](<https://devfeed.tech/tags/sql.md>), [sql-performance](<https://devfeed.tech/tags/sql-performance.md>), [sql-server](<https://devfeed.tech/tags/sql-server.md>), [sql-syntax](<https://devfeed.tech/tags/sql-syntax.md>), [top](<https://devfeed.tech/tags/top.md>)

### AI overview

This article explains how to check whether multiple matching rows exist in SQL. It recommends limiting rows in a derived table before counting them, then compares execution plans and benchmark results for PostgreSQL 15 and Oracle 23c.

### Source excerpt

In a previous blog post, we've advertised the use of SQL EXISTS rather than COUNT(*) to check for existence of a value in SQL. I.e. to check if in the Sakila database, actors called WAHLBERG have played in any films, instead of: Do this: (Depending on your dialect you may require a FROM DUAL clause, ... Continue reading An Efficient Way to Check for Existence of Multiple Values in SQL ->

## jOOQ DAOs, CRUD Access, and the N+1 Query Problem

DevFeed: [jOOQ DAOs, CRUD Access, and the N+1 Query Problem](<https://devfeed.tech/articles/to-dao-or-not-to-dao-28968.md>)

Original publisher: [Read original article](<https://blog.jooq.org/to-dao-or-not-to-dao/>)

Author: lukaseder

Published: 2023-12-06T13:41:52Z

Content type: opinion

Language: en

Sources: [jOOQ](<https://devfeed.tech/sources/jooq.md>)

Topics: [CRUD](<https://devfeed.tech/topics/crud.md>), [SQL](<https://devfeed.tech/topics/sql.md>), [Domain-driven design (DDD)](<https://devfeed.tech/topics/domain-driven-design.md>), [Transactions](<https://devfeed.tech/topics/transactions.md>), [account](<https://devfeed.tech/topics/account.md>)

Tags: [account](<https://devfeed.tech/tags/account.md>), [api](<https://devfeed.tech/tags/api.md>), [crud](<https://devfeed.tech/tags/crud.md>), [dao](<https://devfeed.tech/tags/dao.md>), [data](<https://devfeed.tech/tags/data.md>), [ddd](<https://devfeed.tech/tags/ddd.md>), [developers](<https://devfeed.tech/tags/developers.md>), [join](<https://devfeed.tech/tags/join.md>), [jooq](<https://devfeed.tech/tags/jooq.md>), [jooq-in-use](<https://devfeed.tech/tags/jooq-in-use.md>), [optimisation](<https://devfeed.tech/tags/optimisation.md>), [repository](<https://devfeed.tech/tags/repository.md>), [sql](<https://devfeed.tech/tags/sql.md>), [transactions](<https://devfeed.tech/tags/transactions.md>)

### AI overview

This opinion article examines jOOQ's DAO API and the tendency to use DAOs or repositories for quick CRUD access. It argues that this approach can obscure query design and lead to inefficient patterns such as N+1 queries, while showing how explicit SQL and bulk operations can improve query structure and performance.

### Source excerpt

jOOQ's DAO API is one of jOOQ's most controversial features. When it was first implemented, it was implemented merely: There's a strong hint about the third bullet given how popular Spring Data's repository "pattern" is. A lot of developers just want to quickly fetch and store data, without giving individual queries much thought. A fun ... Continue reading To DAO or not to DAO ->

## Heroku Announces New Hiring as It Invests in Platform, AI, and Developer Experience

DevFeed: [Heroku Announces New Hiring as It Invests in Platform, AI, and Developer Experience](<https://devfeed.tech/articles/join-us-for-a-new-chapter-of-growth-and-innovation-26460.md>)

Original publisher: [Read original article](<https://www.heroku.com/blog/join-heroku/>)

Author: Andrew Fawcett

Published: 2023-08-31T02:04:27Z

Content type: opinion

Language: en

Sources: [Heroku](<https://devfeed.tech/sources/heroku.md>)

Topics: [Heroku](<https://devfeed.tech/topics/heroku.md>), [Development](<https://devfeed.tech/topics/development.md>), [Artificial Intelligence](<https://devfeed.tech/topics/ai.md>), [Tooling](<https://devfeed.tech/topics/tooling.md>)

Tags: [ai](<https://devfeed.tech/tags/ai.md>), [developer-tools](<https://devfeed.tech/tags/developer-tools.md>), [dx](<https://devfeed.tech/tags/dx.md>), [education](<https://devfeed.tech/tags/education.md>), [engineering](<https://devfeed.tech/tags/engineering.md>), [growth](<https://devfeed.tech/tags/growth.md>), [hiring](<https://devfeed.tech/tags/hiring.md>), [innovation](<https://devfeed.tech/tags/innovation.md>), [join](<https://devfeed.tech/tags/join.md>), [news](<https://devfeed.tech/tags/news.md>), [operational-excellence](<https://devfeed.tech/tags/operational-excellence.md>), [operations](<https://devfeed.tech/tags/operations.md>), [product-features](<https://devfeed.tech/tags/product-features.md>)

### AI overview

Heroku announces new hiring tied to a new phase of investment in its platform. The company says it plans to expand offerings, improve developer experience and integrations, and incorporate AI into customer applications and developer workflows.

### Source excerpt

While it's not our usual approach to announce hiring updates on our product blog, we're pleased to share our ongoing dedication to improving the Heroku experience. Focused Growth and Progress Heroku is entering a new phase of investment, and as a part of this initiative, we are opening up new positions for individuals who would [...] The post Join us for a New Chapter of Growth and Innovation appeared first on Heroku.

## jOOQ 3.17 Supports Implicit Join also in DML

DevFeed: [jOOQ 3.17 Supports Implicit Join also in DML](<https://devfeed.tech/articles/jooq-3-17-supports-implicit-join-also-in-dml-28950.md>)

Original publisher: [Read original article](<https://blog.jooq.org/jooq-3-17-supports-implicit-join-also-in-dml/>)

Author: lukaseder

Published: 2022-08-25T08:07:00Z

Content type: article

Language: en

Sources: [jOOQ](<https://devfeed.tech/sources/jooq.md>)

Topics: [SQL](<https://devfeed.tech/topics/sql.md>), [Databases](<https://devfeed.tech/topics/databases.md>)

Tags: [correlated-subqueries](<https://devfeed.tech/tags/correlated-subqueries.md>), [delete](<https://devfeed.tech/tags/delete.md>), [dml-join](<https://devfeed.tech/tags/dml-join.md>), [implicit-join](<https://devfeed.tech/tags/implicit-join.md>), [join](<https://devfeed.tech/tags/join.md>), [jooq](<https://devfeed.tech/tags/jooq.md>), [jooq-3-17](<https://devfeed.tech/tags/jooq-3-17.md>), [jooq-development](<https://devfeed.tech/tags/jooq-development.md>), [mariadb](<https://devfeed.tech/tags/mariadb.md>), [mysql](<https://devfeed.tech/tags/mysql.md>), [oracle](<https://devfeed.tech/tags/oracle.md>), [postgresql](<https://devfeed.tech/tags/postgresql.md>), [sql](<https://devfeed.tech/tags/sql.md>), [update](<https://devfeed.tech/tags/update.md>)

### AI overview

The article explains that jOOQ 3.17 adds support for implicit join path expressions in DML statements. It describes correlated-subquery emulation, DML JOIN support for selected RDBMS, and planned translation using updatable views in Oracle.

### Source excerpt

Since jOOQ 3.11, implicit joins have been supported. An implicit join is a JOIN (mostly a LEFT JOIN) that is generated implicitly because of the presence of a path expression. If SQL supported the syntax natively, it would look like this: All that is is convenience for a bunch of explicitly written LEFT JOIN expressions: ... Continue reading jOOQ 3.17 Supports Implicit Join also in DML ->

## Google Summer of Code 2022 - Call for contributors

DevFeed: [Google Summer of Code 2022 - Call for contributors](<https://devfeed.tech/articles/google-summer-of-code-2022-call-for-contributors-33120.md>)

Original publisher: [Read original article](<https://reactos.org/project-news/google-summer-of-code-2022/>)

Published: 2022-03-13T00:00:00Z

Content type: release

Language: en

Sources: [Front Page on ReactOS Website](<https://devfeed.tech/sources/front-page-on-reactos-website.md>)

Topics: [ReactOS](<https://devfeed.tech/topics/reactos.md>), [Development](<https://devfeed.tech/topics/development.md>), [Google](<https://devfeed.tech/topics/google.md>), [Kernel](<https://devfeed.tech/topics/kernel.md>), [Open Source](<https://devfeed.tech/topics/open-source.md>)

Tags: [development](<https://devfeed.tech/tags/development.md>), [free](<https://devfeed.tech/tags/free.md>), [google](<https://devfeed.tech/tags/google.md>), [google-summer-of-code](<https://devfeed.tech/tags/google-summer-of-code.md>), [join](<https://devfeed.tech/tags/join.md>), [kernel](<https://devfeed.tech/tags/kernel.md>), [matrix](<https://devfeed.tech/tags/matrix.md>), [open-source](<https://devfeed.tech/tags/open-source.md>), [os](<https://devfeed.tech/tags/os.md>), [react](<https://devfeed.tech/tags/react.md>), [reactos](<https://devfeed.tech/tags/reactos.md>), [win32](<https://devfeed.tech/tags/win32.md>), [winapi](<https://devfeed.tech/tags/winapi.md>)

### AI overview

ReactOS announces its acceptance into the 2022 Google Summer of Code program and invites contributors to work on projects involving kernel development, NT architecture, and ReactOS development practices. The article also explains project-duration options, allows independent proposals, and notes that university enrollment is not required under the year's rules.

### Source excerpt

We are proud to announce that ReactOS has been accepted into the 2022 Google Summer of Code program. The GSOC program is a great opportunity for a fresh contributors looking to join the Open Source and Free Software movement. This year we are looking for new contributors who want to enhance skills in kernel development and NT architecture. Our team consists of skilled mentors who are going to guide eager students with ReactOS architecture and development practices.

## How to Get the First or Last Value in a Group Using Group By in SQL

DevFeed: [How to Get the First or Last Value in a Group Using Group By in SQL](<https://devfeed.tech/articles/how-to-get-the-first-or-last-value-in-a-group-using-group-by-in-sql-33938.md>)

Original publisher: [Read original article](<https://hakibenita.com/sql-group-by-first-last-value>)

Author: Haki Benita

Published: 2019-08-12T21:00:00Z

Content type: tutorial

Language: en

Sources: [Haki Benita](<https://devfeed.tech/sources/haki-benita.md>)

Topics: [PostgreSQL](<https://devfeed.tech/topics/postgresql.md>), [SQL](<https://devfeed.tech/topics/sql.md>), [Databases](<https://devfeed.tech/topics/databases.md>)

Tags: [articles](<https://devfeed.tech/tags/articles.md>), [hash](<https://devfeed.tech/tags/hash.md>), [how-to](<https://devfeed.tech/tags/how-to.md>), [join](<https://devfeed.tech/tags/join.md>), [postgresql](<https://devfeed.tech/tags/postgresql.md>), [query](<https://devfeed.tech/tags/query.md>), [sql](<https://devfeed.tech/tags/sql.md>), [window-functions](<https://devfeed.tech/tags/window-functions.md>)

### AI overview

This tutorial explains how to obtain the first or last value in a grouped SQL query, focusing on PostgreSQL. It uses separate queries for account balances and the latest credit value, joins them to produce the complete account state, and discusses the resulting execution plan.

### Source excerpt

Getting the last value of a group in an aggregated query in PostgreSQL is a challenging task. In this article we present a simple way to get the first or last value of a group using group by.

## Challenge Accepted: Transposit

DevFeed: [Challenge Accepted: Transposit](<https://devfeed.tech/articles/challenge-accepted-transposit-35154.md>)

Original publisher: [Read original article](<https://blog.jessfraz.com/post/challenge-accepted-transposit/>)

Published: 2019-04-23T07:09:26Z

Content type: tutorial

Language: en

Sources: [Jessie Frazelle](<https://devfeed.tech/sources/jessie-frazelle.md>)

Topics: [API](<https://devfeed.tech/topics/api.md>), [Code](<https://devfeed.tech/topics/code.md>), [GitHub](<https://devfeed.tech/topics/github.md>), [SQL](<https://devfeed.tech/topics/sql.md>), [Bot](<https://devfeed.tech/topics/bot.md>)

Tags: [api](<https://devfeed.tech/tags/api.md>), [bots](<https://devfeed.tech/tags/bots.md>), [code](<https://devfeed.tech/tags/code.md>), [github](<https://devfeed.tech/tags/github.md>), [graphql](<https://devfeed.tech/tags/graphql.md>), [join](<https://devfeed.tech/tags/join.md>), [pagination](<https://devfeed.tech/tags/pagination.md>), [query](<https://devfeed.tech/tags/query.md>), [sql](<https://devfeed.tech/tags/sql.md>)

### AI overview

The author describes testing Transposit's SQL interface for combining multiple APIs, using a GitHub bot that syncs issues and pull requests to Airtable as a complex example.

### Source excerpt

Last week, I had the pleasure of meeting with the Transposit team in San Francisco. Tech is a super small world and it turns out the two founders and I are separated by one-degree through several different people we know. In meeting them I closed many loops without even realizing it, but I digress... Their product is really cool, it exposes a SQL interface for interacting with numerous APIs at once. For someone like myself who deploys a lot of bots, this is great. Usually when I have a complex bot I end up writing a lot of "glue code" to combine a few different APIs and get the information I want. Most of my bots have some sort of pagination logic and all have the N+1 problem where I don't really optimize my queries or use anything fancy like graphQL. Many APIs don't even have graphQL interfaces but also I am old school and I don't really want to learn something new. This is why I was super intrigued by Transposit's SQL interface, because hey, I know SQL! Adam, the CEO, challenged me to try it out, give them feedback, and see if I could break it with something complex. I am not one to back down from a challenge and I have some super weird ass bots, so I decided to start with the weirdest. Gitable Gitable is a bot I made for sending all my open issues and PRs on GitHub to a table in Airtable. I fucking love Airtable. It's design just feels right and works the way my brain works. I set out to make this bot work in Transposit because I know it has some super weird loops and has the N+1 problem where I loop over all my repos, then make another API call after. To reiterate, the goal of the bot is to iterate through all my repos on GitHub and sync the list of issue and PRs with a table in Airtable. Query all the user's repos First, I need to get all my repos that are not forks. So I need a SQL query for this, in Transposit it looks like this: SELECT name, full_name FROM github.list_repos_for_user WHERE username=@owner AND type='owner' AND fork=false The github.list_repos_fo

## PostgreSQL Data Types

DevFeed: [PostgreSQL Data Types](<https://devfeed.tech/articles/postgresql-data-types-34598.md>)

Original publisher: [Read original article](<https://tapoueh.org/blog/2018/05/postgresql-data-types/>)

Author: Dimitri Fontaine PostgreSQL Major Contributor; Author

Published: 2018-05-24T12:47:05Z

Content type: article

Language: en

Sources: [Dimitri Fontaine](<https://devfeed.tech/sources/dimitri-fontaine.md>)

Topics: [PostgreSQL](<https://devfeed.tech/topics/postgresql.md>), [data type](<https://devfeed.tech/topics/data-type.md>), [JOIN](<https://devfeed.tech/topics/join.md>), [Query (disambiguation)](<https://devfeed.tech/topics/query.md>), [Regular expression](<https://devfeed.tech/topics/regular-expression.md>)

Tags: [data-type](<https://devfeed.tech/tags/data-type.md>), [documentation](<https://devfeed.tech/tags/documentation.md>), [join](<https://devfeed.tech/tags/join.md>), [postgresql](<https://devfeed.tech/tags/postgresql.md>), [query](<https://devfeed.tech/tags/query.md>), [regexp](<https://devfeed.tech/tags/regexp.md>), [reporting](<https://devfeed.tech/tags/reporting.md>)

### AI overview

A recap of a series on PostgreSQL data types, covering how types provide input validation, expected behaviors, and processing functions. It reviews examples involving booleans, text encoding and processing, date and time values, and network address types.

### Source excerpt

Today it's time to conclude our series of PostgreSQL Data Types articles with a recap. The series cover lots of core PostgreSQL data types and shows how to benefit from the PostgreSQL concept of a data type: more than input validation, a PostgreSQL data type also implements expected behaviors and processing functions. This allows an application developer to rely on PostgreSQL for more complex queries, having the processing happen where the data is, for instance when implementing advanced JOIN operations, then retrieving only the data set that is interesting for the application.

## PostgreSQL Data Types: ENUM

DevFeed: [PostgreSQL Data Types: ENUM](<https://devfeed.tech/articles/postgresql-data-types-enum-34596.md>)

Original publisher: [Read original article](<https://tapoueh.org/blog/2018/05/postgresql-data-types-enum/>)

Author: Dimitri Fontaine PostgreSQL Major Contributor; Author

Published: 2018-05-02T09:00:26Z

Content type: tutorial

Language: en

Sources: [Dimitri Fontaine](<https://devfeed.tech/sources/dimitri-fontaine.md>)

Topics: [PostgreSQL](<https://devfeed.tech/topics/postgresql.md>), [data type](<https://devfeed.tech/topics/data-type.md>), [enum](<https://devfeed.tech/topics/enum.md>), [MySQL](<https://devfeed.tech/topics/mysql.md>)

Tags: [alter](<https://devfeed.tech/tags/alter.md>), [data-type](<https://devfeed.tech/tags/data-type.md>), [database](<https://devfeed.tech/tags/database.md>), [enum](<https://devfeed.tech/tags/enum.md>), [join](<https://devfeed.tech/tags/join.md>), [mysql](<https://devfeed.tech/tags/mysql.md>), [postgresql](<https://devfeed.tech/tags/postgresql.md>), [table](<https://devfeed.tech/tags/table.md>)

### AI overview

This tutorial compares PostgreSQL ENUM types with reference tables and foreign keys. It explains how PostgreSQL registers ENUM types, how values are stored, and why changing accepted values is a DDL operation with transaction restrictions. The article recommends ENUMs for very static value sets and reference tables when values or ordering may change.

### Source excerpt

Continuing our series of PostgreSQL Data Types today we're going to introduce the PostgreSQL ENUM type. This data type has been added to PostgreSQL in order to make it easier to support migrations from MySQL. Proper relational design would use a reference table and a foreign key instead.

## All You Need To Know About Prefetching in Django

DevFeed: [All You Need To Know About Prefetching in Django](<https://devfeed.tech/articles/all-you-need-to-know-about-prefetching-in-django-33888.md>)

Original publisher: [Read original article](<https://hakibenita.com/all-you-need-to-know-about-prefetching-in-django>)

Author: Haki Benita

Published: 2017-04-28T21:00:00Z

Content type: tutorial

Language: en

Sources: [Haki Benita](<https://devfeed.tech/sources/haki-benita.md>)

Topics: [Django](<https://devfeed.tech/topics/django.md>), [Query (disambiguation)](<https://devfeed.tech/topics/query.md>), [JOIN](<https://devfeed.tech/topics/join.md>)

Tags: [articles](<https://devfeed.tech/tags/articles.md>), [django](<https://devfeed.tech/tags/django.md>), [join](<https://devfeed.tech/tags/join.md>), [many-to-many](<https://devfeed.tech/tags/many-to-many.md>), [models](<https://devfeed.tech/tags/models.md>), [orm](<https://devfeed.tech/tags/orm.md>), [performance](<https://devfeed.tech/tags/performance.md>), [query](<https://devfeed.tech/tags/query.md>)

### AI overview

A tutorial on using Django prefetching to reduce database queries when retrieving related data, especially across many-to-many relationships. It explains the N+1 query problem, compares joins with prefetching, and describes how prefetched results are cached on objects.

### Source excerpt

A rundown of all the ways you can use Prefetch to speed up queries in Django.

## Faster Snowflake Queries through Clustering

DevFeed: [Faster Snowflake Queries through Clustering](<https://devfeed.tech/articles/faster-snowflake-queries-through-clustering-28626.md>)

Original publisher: [Read original article](<https://eng.localytics.com/faster-snowflake-queries-through-clustering/>)

Author: Michal Klos

Published: 2017-03-13T14:17:01Z

Content type: article

Language: en

Sources: [Localytics](<https://devfeed.tech/sources/localytics.md>)

Topics: [data-processing](<https://devfeed.tech/topics/data-processing.md>), [Databases](<https://devfeed.tech/topics/databases.md>), [Query (disambiguation)](<https://devfeed.tech/topics/query.md>), [Amazon Redshift](<https://devfeed.tech/topics/amazon-redshift.md>)

Tags: [clustering](<https://devfeed.tech/tags/clustering.md>), [data-processing](<https://devfeed.tech/tags/data-processing.md>), [database](<https://devfeed.tech/tags/database.md>), [databases](<https://devfeed.tech/tags/databases.md>), [join](<https://devfeed.tech/tags/join.md>), [mpp](<https://devfeed.tech/tags/mpp.md>), [mysql](<https://devfeed.tech/tags/mysql.md>), [partitioning](<https://devfeed.tech/tags/partitioning.md>), [performance](<https://devfeed.tech/tags/performance.md>), [redshift](<https://devfeed.tech/tags/redshift.md>), [s3](<https://devfeed.tech/tags/s3.md>), [scale](<https://devfeed.tech/tags/scale.md>), [snowflake](<https://devfeed.tech/tags/snowflake.md>), [time-series](<https://devfeed.tech/tags/time-series.md>)

### AI overview

This article explains how Snowflake clustering and re-clustering can improve query performance by helping the query engine prune unneeded data. It discusses choosing cluster keys, comparisons with MPP database techniques, and the caveat that naturally ordered time-series data may not need clustering.

### Source excerpt

At Localytics we have petabytes of data that needs to be served at low latencies and we use Snowflake in our mix of data processing technologies. Snowflake, like many other MPP databases, has a way of partitioning data to optimize read-time performance by allowing the query engine to prune

## Suggesting a corrected column name/spelling in the event of a column misspelling

DevFeed: [Suggesting a corrected column name/spelling in the event of a column misspelling](<https://devfeed.tech/articles/suggesting-a-corrected-column-name-spelling-in-the-event-of-a-column-misspelling-33653.md>)

Original publisher: [Read original article](<https://pgeoghegan.blogspot.com/2015/11/suggesting-corrected-column.html>)

Author: Peter Geoghegan (noreply@blogger.com)

Published: 2015-11-14T20:25:00Z

Content type: article

Language: en

Sources: [Peter Geoghegan's blog](<https://devfeed.tech/sources/peter-geoghegan-s-blog.md>)

Topics: [PostgreSQL](<https://devfeed.tech/topics/postgresql.md>), [SQL](<https://devfeed.tech/topics/sql.md>), [client](<https://devfeed.tech/topics/client.md>)

Tags: [client](<https://devfeed.tech/tags/client.md>), [join](<https://devfeed.tech/tags/join.md>), [postgresql](<https://devfeed.tech/tags/postgresql.md>), [query](<https://devfeed.tech/tags/query.md>), [sql](<https://devfeed.tech/tags/sql.md>)

### AI overview

The article describes a PostgreSQL 9.5 feature that suggests likely column names when a user misspells a column in an SQL query. Suggestions are scored heavily using Levenshtein distance and are shown in HINT messages to psql and other client tools, including cases with multiple equally scored matches and table aliases.

### Source excerpt

One small PostgreSQL 9.5 feature I worked on is the new hinting mechanism feature, which sometimes hints, based on a score, what you might have meant to type following misspelling a column name in an SQL query. The score heavily weighs Levenshtein distance. A HINT message is sent to the client, which psql and other client tools will display by default. It's common to not quite recall offhand if a column name is pluralized, or where underscores are used to break up words that make up the name of a column. This feature is targeted at that problem, providing guidance that allows the user to quickly adjust their query without mental context switching. For example: postgres=# select * from orders where order_id = 5; ERROR: 42703: column "order_id" does not exist LINE 1: select * from orders where order_id = 5; ^ HINT: Perhaps you meant to reference the column "orders"."orderid". You may also see a hint in the case of two possible matches, provided both matches have the same score, and the score crosses a certain threshold of assumed usefulness: postgres=# select * from orders o join orderlines ol on o.orderid = ol.orderid where order_id = 5; ERROR: 42703: column "order_id" does not exist LINE 1: ...oin orderlines ol on o.orderid = ol.orderid where order_id =... ^ HINT: Perhaps you meant to reference the column "o"."orderid" or the column "ol"."orderid". If an alias was used here (which this query must have anyway), the hint becomes more specific: postgres=# select * from orders o join orderlines ol on o.orderid = ol.orderid where o.order_id = 5; ERROR: 42703: column o.order_id does not exist LINE 1: ...oin orderlines ol on o.orderid = ol.orderid where o.order_id... ^ HINT: Perhaps you meant to reference the column "o"."orderid". This feature should make writing queries interactively in psql a bit more pleasant. Mental context switching to figure these incidental details out has a tendency to slow things down.

## The best Postgres feature you're not using - CTEs aka WITH clauses

DevFeed: [The best Postgres feature you're not using - CTEs aka WITH clauses](<https://devfeed.tech/articles/the-best-postgres-feature-you-re-not-using-ctes-aka-with-clauses-41154.md>)

Original publisher: [Read original article](<https://www.craigkerstiens.com/2013/11/18/best-postgres-feature-youre-not-using/>)

Author: Map

Published: 2013-11-18T20:55:56Z

Content type: article

Language: en

Sources: [Craig Kerstiens](<https://devfeed.tech/sources/craig-kerstiens.md>)

Topics: [SQL](<https://devfeed.tech/topics/sql.md>), [Query (disambiguation)](<https://devfeed.tech/topics/query.md>), [Databases](<https://devfeed.tech/topics/databases.md>)

Tags: [array-agg](<https://devfeed.tech/tags/array-agg.md>), [cte](<https://devfeed.tech/tags/cte.md>), [feature](<https://devfeed.tech/tags/feature.md>), [join](<https://devfeed.tech/tags/join.md>), [performance](<https://devfeed.tech/tags/performance.md>), [postgres](<https://devfeed.tech/tags/postgres.md>), [postgresql](<https://devfeed.tech/tags/postgresql.md>), [readability](<https://devfeed.tech/tags/readability.md>), [sql](<https://devfeed.tech/tags/sql.md>)

### AI overview

This article explains how PostgreSQL common table expressions (CTEs), also called WITH clauses, make SQL queries more readable and composable. It demonstrates chaining CTEs to analyze tasks assigned to users and identify users responsible for more than half of a project's tasks, while noting that readability may involve a performance tradeoff.

### Source excerpt

SQL by default isn't typically friendly to dive into, and especially so if you're reading someone else's already created queries. For some reason most people throw out principles we follow in other languages such as commenting and composability just for SQL. I was recently reminded of a key feature in Postgres that most don't use by @timonk highlighting it in his AWS Re:Invent Redshift talk. The simple feature actually makes SQL both readable and composable, and even for my own queries capable of coming back to them months later and understanding them, where previously they would not be. The feature itself is known as CTEs or common table expressions, you may also here it referred to as WITH clauses. The general idea is that it allows you to create something somewhat equivilant to a view that only exists during that transaction. You can create multiple of these which then allow for clear building blocks and make it simple to follow what you're doing. Lets take a look at a nice simple one: WITH users_tasks AS ( SELECT users.email, array_agg(tasks.name) as task_list, projects.title FROM users, tasks, project WHERE users.id = tasks.user_id projects.title = tasks.project_id GROUP BY users.email, projects.title ) Using this I could now just append some basic other query on to the end that references this CTE users_tasks. Something akin to: SELECT * FROM users_tasks; But where it becomes more interesting is chaining these together. So while I have all tasks assigned to each user here, perhaps I want to then find which users are responsible for more than 50% of the tasks on a given project, thus being the bottleneck. To oversimplify this we could do it a couple of ways, total up the tasks for each project, and then total up the tasks for each user per project: total_tasks_per_project AS ( SELECT project_id, count(*) as task_count FROM tasks GROUP BY project_id ), tasks_per_project_per_user AS ( SELECT user_id, project_id, count(*) as task_count FROM tasks GROUP BY user_id,

## A look at Foreign Data Wrappers

DevFeed: [A look at Foreign Data Wrappers](<https://devfeed.tech/articles/a-look-at-foreign-data-wrappers-41148.md>)

Original publisher: [Read original article](<https://www.craigkerstiens.com/2013/08/05/A-look-at-Foreign-Data-Wrappers/>)

Author: Map

Published: 2013-08-05T20:55:56Z

Content type: tutorial

Language: en

Sources: [Craig Kerstiens](<https://devfeed.tech/sources/craig-kerstiens.md>)

Topics: [PostgreSQL](<https://devfeed.tech/topics/postgresql.md>), [SQL](<https://devfeed.tech/topics/sql.md>), [Heroku Postgres](<https://devfeed.tech/topics/heroku-postgres.md>), [Databases](<https://devfeed.tech/topics/databases.md>), [JOIN](<https://devfeed.tech/topics/join.md>)

Tags: [heroku-postgres](<https://devfeed.tech/tags/heroku-postgres.md>), [join](<https://devfeed.tech/tags/join.md>), [postgres](<https://devfeed.tech/tags/postgres.md>), [postgresql](<https://devfeed.tech/tags/postgresql.md>), [sql](<https://devfeed.tech/tags/sql.md>)

### AI overview

A tutorial on PostgreSQL foreign data wrappers (FDWs), including how to enable postgres_fdw on PostgreSQL 9.3, configure a remote server and user mapping, define foreign tables, and query data across databases.

### Source excerpt

There are two particular sets of features that continue to keep me very excited about the momentum of Postgres. And while PostgreSQL has had some great momentum in the past few years these features may give it an entirely new pace all together. One is extensions, which is really its own category. Dimitri Fontaine was talking about doing a full series just on extensions, so here's hoping he does so I dont have to :) One subset of extensions which I consider entirely separate is the other thing, which is foreign data wrappers or FDWs. FDWs allow you to connect to other data sources from within Postgres. From there you can query them with SQL, join across disparate data sets, or join across different systems. Recently I had a good excuse to give the postgres_fdw a try. And while I've blogged about the Redis FDW previously, the Postgres one is particularly exciting because with PostgreSQL 9.3 it will ship as a contrib module, which means all Postgres installers should have it... you just have to turn it on. Let's take a look at getting it setup and then dig into it a bit. First, because I don't have Postgres 9.3 sitting around on my system I'm going to provision one from Heroku Postgres: $ heroku addons:add heroku-postgresql:crane --version 9.3 Once it becomes available I'm going to connect to it then enable the extension: $ heroku pg:psql BLACK -acraig # CREATE EXTENSION postgres_fdw; Now its there, so we can actually start using it. To use the FDW there's four basic things you'll want to do: Create the remote server Create a user mapping for the remote server Create your foreign tables Start querying some things The setup You'll only need to do each of the following once, once you're server, user and foreign table are all setup you can simply query away. This is a nice advantage over db_link which only exists for the set session. One downside I did find was that you can't use a full Postgres connection string, which would make setting it up much simpler. So onto setting

## Postgres Indexing - A collection of indexing tips

DevFeed: [Postgres Indexing - A collection of indexing tips](<https://devfeed.tech/articles/postgres-indexing-a-collection-of-indexing-tips-41141.md>)

Original publisher: [Read original article](<https://www.craigkerstiens.com/2013/05/30/Postgres-Indexing-A-collection-of-indexing-tips/>)

Author: Map

Published: 2013-05-30T20:55:56Z

Content type: tutorial

Language: en

Sources: [Craig Kerstiens](<https://devfeed.tech/sources/craig-kerstiens.md>)

Topics: [Databases](<https://devfeed.tech/topics/databases.md>), [SQL](<https://devfeed.tech/topics/sql.md>), [Heroku Postgres](<https://devfeed.tech/topics/heroku-postgres.md>), [JOIN](<https://devfeed.tech/topics/join.md>)

Tags: [heroku](<https://devfeed.tech/tags/heroku.md>), [heroku-postgres](<https://devfeed.tech/tags/heroku-postgres.md>), [indexes](<https://devfeed.tech/tags/indexes.md>), [indexing](<https://devfeed.tech/tags/indexing.md>), [information-schema](<https://devfeed.tech/tags/information-schema.md>), [join](<https://devfeed.tech/tags/join.md>), [performance](<https://devfeed.tech/tags/performance.md>), [postgres](<https://devfeed.tech/tags/postgres.md>), [postgres-performance](<https://devfeed.tech/tags/postgres-performance.md>), [postgresql](<https://devfeed.tech/tags/postgresql.md>), [query](<https://devfeed.tech/tags/query.md>), [schema](<https://devfeed.tech/tags/schema.md>), [sql](<https://devfeed.tech/tags/sql.md>), [stat](<https://devfeed.tech/tags/stat.md>), [tips](<https://devfeed.tech/tags/tips.md>)

### AI overview

A collection of PostgreSQL indexing tips covering unused-index analysis, indexing costs, and the trade-offs between composite and separate indexes. It includes SQL examples and Heroku tooling for examining index usage.

### Source excerpt

Even from intial reviews of my previous post on expression based indexes I received a lot of questions and feedback around many different parts of indexing in Postgres. Here's a mixed collection of valuable tips and guides around much of that. Unused Indexes In an earlier tweet I joked about some SQL that would generate the SQL to add an index to every column: # SELECT 'CREATE INDEX idx_' || table_name || '_' || column_name || ' ON ' || table_name || ' ("' || column_name || '");' FROM information_schema.columns; ?column? --------------------------------------------------------------------- CREATE INDEX idx_pg_proc_proname ON pg_proc ("proname"); CREATE INDEX idx_pg_proc_pronamespace ON pg_proc ("pronamespace"); CREATE INDEX idx_pg_proc_proowner ON pg_proc ("proowner"); The reasoning behind this is guessing whether an index will be helpful can be a bit hard within Postgres. So the easy solution is to add indexes to everything, then just observe if they're being used. Of course you want to add it to all tables/columns because you never know if core of Postgres may be missing some needed ones As included with the pg-extras plugin for Heroku you can run a query to show you all unused indexes. On Heroku simply install the plugin the run heroku pg:unused_indexes to show the size and number of times an index scan has been used. On a non Heroku Postgres database you can run: # SELECT schemaname || '.' || relname AS table, indexrelname AS index, pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size, idx_scan as index_scans FROM pg_stat_user_indexes ui JOIN pg_index i ON ui.indexrelid = i.indexrelid WHERE NOT indisunique AND idx_scan < 50 AND pg_relation_size(relid) > 5 * 8192 ORDER BY pg_relation_size(i.indexrelid) / nullif(idx_scan, 0) DESC NULLS FIRST, pg_relation_size(i.indexrelid) DESC; table | index | index_size | index_scans ---------------------+--------------------------------------------+------------+------------- public.grade_levels | index_placement_attempt

## Finding plans in pg\_stat\_plans easily with pg\_find\_plans

DevFeed: [Finding plans in pg\_stat\_plans easily with pg\_find\_plans](<https://devfeed.tech/articles/finding-plans-in-pg-stat-plans-easily-with-pg-find-plans-33647.md>)

Original publisher: [Read original article](<https://pgeoghegan.blogspot.com/2012/12/finding-plans-in-pgstatplans-easily.html>)

Author: Peter Geoghegan (noreply@blogger.com)

Published: 2012-12-04T10:40:00Z

Content type: article

Language: en

Sources: [Peter Geoghegan's blog](<https://devfeed.tech/sources/peter-geoghegan-s-blog.md>)

Topics: [PostgreSQL](<https://devfeed.tech/topics/postgresql.md>), [Databases](<https://devfeed.tech/topics/databases.md>), [SQL](<https://devfeed.tech/topics/sql.md>), [JSON](<https://devfeed.tech/topics/json.md>), [Python](<https://devfeed.tech/topics/python.md>), [Monitoring](<https://devfeed.tech/topics/monitoring.md>)

Tags: [join](<https://devfeed.tech/tags/join.md>), [json](<https://devfeed.tech/tags/json.md>), [pg-stat-plans](<https://devfeed.tech/tags/pg-stat-plans.md>), [pg-stat-statements](<https://devfeed.tech/tags/pg-stat-statements.md>), [planner](<https://devfeed.tech/tags/planner.md>), [postgresql](<https://devfeed.tech/tags/postgresql.md>), [python](<https://devfeed.tech/tags/python.md>), [sql](<https://devfeed.tech/tags/sql.md>)

### AI overview

The article introduces pg_find_plans, an experimental pg_stat_plans submodule for PostgreSQL. It uses machine-readable EXPLAIN output to search stored execution plans by criteria such as sequential scans, execution costs, and join counts directly from SQL. The article also discusses its PL/Python and PL/pgSQL implementation, input sanitization, limitations, and the usefulness of JSON for representing plan structures.

### Source excerpt

As I recently blogged about, pg_stat_plans is a PostgreSQL satellite project I've been working on that aims to support earlier versions of Postgres that cannot use the new pg_stat_statements, and to track execution costs at the plan rather than the query granularity. It allows the user to easily explain each stored query text to see the plan for the entry, and has features that facilitate monitoring planner regressions. Since PostgreSQL 9.0, support for machine-readable EXPLAIN output has existed. I'm not aware that anyone else got around to actually doing something interesting with this capability, though. I knew that in order to get the most benefit from pg_stat_plans, it ought to be possible to leverage this capability to search for plans based on arbitrary criteria, directly from SQL. I've written an experimental submodule of pg_stat_plans, called pg_find_plans, that is designed to do just that - to quickly find plans and their execution costs, for those plans that, say, perform a sequential scan on a known large table. Here's the description of pg_find_plans from its documentation: pg_find_plans is written in PL/Python and PL/PgSQL. It is intended to provide users with a better way to ask questions like "what are the execution costs of all plans tracked since last statistics reset that involve a sequential scan against mytable, and have more than 2 joins?". That might be written as: mydb=# select join_count(json_plan), p.* from pg_stat_plans p join stored_plans sp on (p.userid=sp.userid and p.dbid=sp.dbid and p.planid=sp.planid) where from_our_database and join_count(json_plan) > 2 and contains_node(json_plan, 'Seq Scan', 'mytable'); order by 1 desc nulls last; Users should have a high degree of confidence that their queries on plan's structure are free of detectable errors, and pg_find_plans ensures this by carefully sanitising user input. For example, if the node of interest was specified as 'seq scan' above, the query would raise an error - to do any less mi

## MVCC in the Cloud

DevFeed: [MVCC in the Cloud](<https://devfeed.tech/articles/mvcc-in-the-cloud-34374.md>)

Original publisher: [Read original article](<https://tapoueh.org/blog/2010/07/mvcc-in-the-cloud/>)

Author: Dimitri Fontaine PostgreSQL Major Contributor; Author

Published: 2010-07-06T08:50:00Z

Content type: opinion

Language: en

Sources: [Dimitri Fontaine](<https://devfeed.tech/sources/dimitri-fontaine.md>)

Topics: [Cloud](<https://devfeed.tech/topics/cloud.md>), [PostgreSQL](<https://devfeed.tech/topics/postgresql.md>), [Scalability](<https://devfeed.tech/topics/scalability.md>), [Databases](<https://devfeed.tech/topics/databases.md>), [Architecture & Design](<https://devfeed.tech/topics/architecture-design.md>), [SQL](<https://devfeed.tech/topics/sql.md>)

Tags: [cloud](<https://devfeed.tech/tags/cloud.md>), [database](<https://devfeed.tech/tags/database.md>), [distributed](<https://devfeed.tech/tags/distributed.md>), [join](<https://devfeed.tech/tags/join.md>), [latency](<https://devfeed.tech/tags/latency.md>), [mvcc](<https://devfeed.tech/tags/mvcc.md>), [network](<https://devfeed.tech/tags/network.md>), [parallel](<https://devfeed.tech/tags/parallel.md>), [postgres](<https://devfeed.tech/tags/postgres.md>), [scalability](<https://devfeed.tech/tags/scalability.md>), [sql](<https://devfeed.tech/tags/sql.md>)

### AI overview

The article examines whether Postgres-R is ready for cloud use, arguing that while it supports elasticity and read scalability across database nodes, write scalability remains unresolved. It proposes remote and mirrored tablespaces for distributing data and processing while preserving transaction boundaries.

### Source excerpt

At CHAR(10) Markus had a talk about Using MVCC for Clustered Database Systems and explained how Postgres-R does it. The scope of his project is to maintain a set of database servers in the same state, eventually. Now, what does it mean to get "In the Cloud"? Well there are more than one answer I'm sure, mine would insist on including this "Elasticity" bit. What I mean here is that it'd be great to be able to add or lose nodes and stay online. Granted, that what's Postgres-R is providing. Does that make it ready for the "Cloud"? Well it happens so that I don't think so.