PG Phriday

PG Phriday: On the Move

Sometimes when we have an existing database and schema full of tables, there comes a time when we need to perform a migration. Maybe it’s because we want to move to or from a cloud service. Perhaps we have a small database and want to do a quick version upgrade via dump / restore. Whatever the reason, we may decide to clean up some technical debt while we’re making the transition.

Many Postgres experts recommend against creating objects in the public schema. This is the default schema that exists in nearly all Postgres databases, and there are often implicit grants that could make our objects available in unexpected scenarios. It’s also a cluttered namespace if all tables, views, functions, etc., are created there by default. Using it is sloppy and makes future data or operation segregation much more difficult.

So how can we move a bunch of existing stuff out of the public schema safely?

PG Phriday: Papa's Got a Brand New RAG

Remember this guy? Robo-Postgres returns! AI is all anyone talks about these days, isn’t it? Even when it comes to databases like Postgres, devs are finding new and innovative ways to leverage LLMs in everyday use cases. Can you really blame them though? This is an exciting new technology that will transform how we work and potentially society as a whole once it finally matures. We even covered building a crude RAG app a few short weeks ago.

PG Phriday: Under Observation

Have you ever wanted to use a non-superuser role in a Postgres database to perform actions that are normally restricted? Even something as simple as reading from the pg_stat_activity view requires special permissions to view the query column because it could contain sensitive information.

PG Phriday: Wanton Animal Cruelty

The last few PG Phriday articles have been somewhat dense content, so how about something a bit more irreverent? Rather than wax on about AI, HA architectures, or conceptual advancements to Postgres clusters, why not write a game instead? To keep things simple, let’s just build a no-frills Tamagotchi virtual pet for bored database professionals. There’s a lot of SQL in this article, so check out the git page for this blog if you want to follow along.

PG Phriday: A Dirty Postgres RAG

Has it really come to this? AI is everywhere these days Postgres and AI go together like elephants and chocolate. At first glance, it seems like a silly combination. Postgres is an RDBMS for storing data with ACID compliance, functions, views, and maybe some extensions or foreign data wrappers. Where is room for AI in that? It may be trendy to take something, rub some AI on it, and then declare it a breakthrough technology, but that isn’t necessarily reality.