David has some tricks and sample code for using CTEs to manipulate data and move things around inside your database. This can be especially handy for sorting, moving, or labeling data and moving it to an archive.
What happens when you query Postgres? Data can actually come from many different places like the application cache, buffer cache, and even down into the physical disk cache. This post surveys the data storage and flow of Postgres data.
David optimizes a slow query by splitting up a left join into two calls and using UNION ALL to combine them. The proof is in the pudding.
Data Definition Language (DDL) in SQL itself is notoriously non-dynamic but this next post on Devious SQL provides some examples of Dynamic DDL Postgres syntax for utility queries where you need to review the current state of the database.
An interesting question came up on the postgresql IRC channel about how to use native PostgreSQL features to handle some sort of queuing behavior. While the specific usage was related to handling serialization of specific events to some external broker, the specifics are less important than the overall structure.
If you have insert-only tables in a version of PostgreSQL earlier that 13, you could benefit from running a regularly scheduled VACUUM.
We spend time day in, day out, answering the questions that matter and coming up with solutions that make the most sense. However, sometimes a question comes up that is just so darn … interesting that even if there are sensible solutions or workarounds, it still seems like a challenge just to take the request literally. Thus was born this blog series, Devious SQL. (Devious - longer and less direct than the most straightforward way.)
There have certainly been times when using PostgreSQL, that I’ve yearned for an UPDATE or DELETE statement with a LIMIT feature. While the SQL standard itself has no say in the matter as of SQL:2016, there are definite cases of existing SQL database dialects that support this.