Idle transactions cause table bloat, as Umair Shahid explains, because Postgres has to leave dead tuples in place. At first this looks like a ChatGPT-generated post, but hang in there, because there are queries as you keep reading.
Temporary tables cause catalog bloat, as Jobin Augustine writes, which slows down unrelated queries in general. He talks about seeing catalogs grow as large as 40GB.
Kubernetes Killed the High Availability Star, Shaun Thomas writes, which is a catchy way of saying that it’s catching on much faster than older HA/DR techniques.
There are 3 ways to program with LLMs like ChatGPT, and I especially love the 3rd one, chat-driven programming. David Crawshaw has thought a lot about this, and I like his way of treating it like a school exam style question for the LLM.
Personally, I’ve had really poor results asking LLMs to do coding for me when it comes to databases. For example, I asked ChatGPT 4o:
Please convert this Microsoft SQL Server stored procedure into Postgres:
CREATE OR ALTER PROC dbo.usp_GetUserInfo @UserId INT NOT NULL AS
BEGIN
IF @UserId = 0
SELECT TOP 100 Reputation, DisplayName, Location, Id
FROM dbo.Users
ORDER BY Reputation DESC;
ELSE
SELECT Id, DisplayName, Reputation, Location, CreationDate, LastAccessDate
FROM dbo.Users
WHERE Id = @UserId;
END
The result ChatGPT gave me wouldn’t work:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
CREATE OR REPLACE FUNCTION usp_get_user_info(user_id INT) RETURNS TABLE ( id INT, displayname TEXT, reputation INT, location TEXT, creationdate TIMESTAMPTZ, lastaccessdate TIMESTAMPTZ ) AS $$ BEGIN IF user_id = 0 THEN RETURN QUERY SELECT reputation, displayname, location, id FROM users ORDER BY reputation DESC LIMIT 100; ELSE RETURN QUERY SELECT id, displayname, reputation, location, creationdate, lastaccessdate FROM users WHERE id = user_id; END IF; END; $$ LANGUAGE plpgsql; |
Because the two select queries return different result sets. I then challenged it, and it returned another query, but even that is problematic since it returns columns in different orders than the original code.
You can read the original ChatGPT 4o conversation, and the ChatGPT o1 conversation, which was better but still problematic. I don’t understand why it would return nulls when we’ve got valid data for CreationDate and LastAccessDate, but whatever.
This is why I like David Crawshaw’s way of approaching it as an exam question: you have to be the professor who knows the right answers you’re looking for, and guide the LLM’s output towards it. This sounds like it would be more work than it’s worth, and it is in many cases, but when you’re facing a lot of manual labor, it can be time-saving.