Latest posts from David Christensen
Tuple shuffling: Postgres CTEs for Moving and Deleting Table Data
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.
5 min readPostgres Data Flow
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.
7 min readPostgres Locking: When is it Concerning?
Seeing locks in your Postgres monitoring but don't know what it means? David takes a look at locks and what to take into consideration.
8 min readPostgres Query Optimization: LEFT JOIN vs UNION ALL
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.
6 min read
Devious SQL: Dynamic DDL in PostgreSQL
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.
6 min read
Devious SQL: Message Queuing Using Native PostgreSQL
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.
8 min read-1.png)
Insert-Only Tables and Autovacuum Issues Prior to PostgreSQL 13
If you have insert-only tables in a version of PostgreSQL earlier that 13, you could benefit from running a regularly scheduled VACUUM.
5 min read.jpg)
Devious SQL: Run the Same Query Against Tables With Differing Columns
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.)
6 min read
Simulating UPDATE or DELETE with LIMIT in Postgres: CTEs to The Rescue!
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.
10 min read
