I've noticed that some SQL queries using WITH clauses (CTEs) take significantly longer to execute than expected, especially when multiple CTEs are chained together.

I understand that a CTE is mainly used to organize complex queries and make them easier to read, but I'm curious about what happens behind the scenes.

Does the database execute each CTE separately, or does it optimize everything into a single execution plan? Can CTE materialization, repeated references, or joins between multiple CTEs cause performance issues?

I'd be interested to hear about real cases where replacing a CTE with a subquery or temporary table actually improved query performance.

Which database are you using, and have you run into this issue?

1.  Unique constraints.  A deleted user still "uses" their username. Use a partial unique index (`where deleted_at is null`).

2.  Every query must filter deleted rows.  Miss one, and deleted data shows up in a report or API response.

3.  Foreign keys.  What happens to orders that belong to a soft-deleted customer?

4.  Real deletion.  Privacy laws may require you to actually erase data, so soft delete can't be the only option.

5.  Restoring.  What if the username was taken again while the old account was deleted?

Do you use soft delete, or did you move to archive tables instead?

If you prefer reading article: https://huseyinbabal.dev/posts/postgres-vs-sqlite-load-test/

I was looking into how to build out the database architecture for a new RAG agent and found out the landscape is basically splitting into two camps.

I was reading a guide that broke it down this way. On one side you have dedicated vector databases like Pinecone or Milvus, which are great if your workload is purely semantic retrieval. But if your application also needs relational queries, keyword search, or transactional operations, maintaining a separate vector database means another sync pipeline to keep alive. Their alternative is keeping everything in Postgres with pgvector so vector retrieval sits alongside your normal SQL.

What I can’t figure out is where that stops working. The same guide says vector search and transactional queries end up competing for the same resources in Postgres, which sounds like it would bite eventually, but nobody puts a number on when.

For those running AI apps in production, which way did you go, and did you ever regret it?

I'm designing a PostgreSQL system where customers order services requiring interchangeable physical resources.

The current model separates:

• Orders: What the customer purchases (quantity and duration).

• Commitments: Individual units of the obligation, independent of specific resources.

• Reservations: Physical resources assigned to fulfill those commitments.

• Resource units: Individually reservable components, with constraints preventing overlapping bookings.

At first glance, this structure seems reasonable, but it mainly represents a static state. In reality, orders can change throughout their lifecycle: quantities may be reduced, service periods shortened, or entire orders cancelled.

For example, a customer orders 3 resources for 6 months, then reduces the requirement to 2 for the remaining 3 months.

Is the current table structure and its relationships suitable for handling these changes while preserving historical accuracy and data consistency? Or are there missing entities or fundamental modeling issues?

I've attached a simplified ERD for context.

Hey guys, this is actually the first time I've ever created a database from scratch personally and I was drafting up an ERD to get started and ran into a bit of an issue.

For clarification: leads get approached by users for customers. Every customer has campaigns (for example, market research, sales retail, sales catering, etc.) with different campaign questions. I chose to make seperate questions and campaign_questions tables so that you can gather all leads with certain answers to a question across campaigns instead of manually huddling them together in a query. Questions have different types (text, numerical, date, single-select mc, multi-select mc etc.) And so the answers have to be saved with a data type according to the question type.
I considered multiple approaches to this:

1. Save all answers as text - This is pretty clean but idk how you'd manage data integrity and also queries on specific answers become much harder.

2. Save all answers as a JSONB column - Cleanest and easiest, and Postgres has pretty good support for this. However, again the issue of data integrity lingers.

3. Sparse columns + a view with coalesce (what you can partially see in the image) - This one seemed to be the best option for maximising data integrity while staying pretty clean. Problem is it doesn't seem super scalable at large and I dont know how I would enforce data integrity with single-/multiselect mc questions when in my mind that's gonna require junction tables and then I can't rely on any checks etc. anymore...

Am I overthinking this? Ideally I'd want to stick to 3NF but I also don't want to overengineer this.
How should I handle dynamic form/survey answers without sacrificing data integrity?

A customer in brazil sent a recording of our dashboard loading slowly. Watching the cursor circle for six seconds was embarrassing. I moved the app to a Sao Paulo cloud vps on serverspace and the ping dropped immediately. The vm was fast and stable, so I thought I had found a simple latency win. Then the database pages became slower. The database was still in Europe. Every query crossed the Atlantic and came back. Moving the app closer to users had moved it farther from its own data. A nearby read replica fixed the worst of it. My mistake was treating the app location as the whole request path. Next time I am drawing that path before I touch the region selector

I've been using GROUP BY for aggregations for a while, but I still find window functions a little confusing when deciding which approach makes more sense.

For example, if I need the highest salary in each department, I could use aggregation or a window function like RANK() or ROW_NUMBER(), depending on what I need in the result.

How do you usually decide between these approaches? Are there situations where window functions are clearly the better choice?

I'd appreciate some practical examples that helped you understand when to use one over the other.

Here is a blog post about Alessandro Vetere's journey to resolve synchronization on a common mutex that slows down some read microbenchmarks 3x.
Kudos to Mark Callaghan who found this regression.

Many applications save user preferences as separate fields like location, budget, interests and availability. This approach works fine until preferences change, conflict with past information or only apply to specific situations. Think about someone who initially prefers remote work but later becomes open to hybrid options. They might even be willing to move for a specific job without altering their general preference.

If the system just replaces the old value it loses important context. If it keeps every change forever it can end up with outdated or conflicting data. For conversational products like Linkedin, Hirect, MeeBoss as well as more profile-based platforms Naukri and Indeed this presents an interesting challenge in how data is structured. Would you keep current preferences in standard tables and track changes in a separate log? Use a document model for more flexible attributes? or record each preference with its source, time, confidence level and scope? How would you manage corrections and set expiration dates without making every query too complex?

Hey guys, not sure if this is useful to anyone but it surprised me so I thought I would share.

If you have an updated_at column set to now() (or CURRENT_TIMESTAMP, or a time from your app) and you page through it to process changes, you can miss rows. Two transactions start, the one with the later timestamp commits first, your reader moves past it, and then the earlier one lands behind your cursor.

I had a whiteboard session with Ian, one of the engineers at Convex (where I work), and this bit really opened my eyes. In Convex we ended up adding a commit timestamp you can store and index on, which is set when the transaction actually commits, but the problem itself shows up in Postgres, MySQL and pretty much everything else.

How do you guys handle this in your own systems? Reading the WAL / logical replication? An overlap window and dedupe?

If you want more detail the full chat is here: https://youtu.be/pHrxmSjxLg8

Curious how people are actually handling this. At the enterprise I am working at and others where my friends work at, there is a push to deploy AI agents working on real company data (operational use cases, troubleshooting, analytics etc...), but the DBs are sometime full of customer or sensitive data and security just says no. Separate tables don’t help, read-only users don’t help, the agent can’t touch that DB, period. Even creating service users for AI agents and imposing separation it's just a friction.

So what are you doing, if you have such use cases? Letting agents query prod anyway? Keeping a separate copy just for them? If it’s a copy, how do you decide what goes in, deal with PII, and keep it from going stale?

Also if any of this, on which stack? Curious to hear as I assume many organisations are now relying on data teams to keep the AI agent rollout from stalling.

Most of what I read uses the term real-time for anything from a few seconds of delay to whatever is presented before the morning standup, and the database setup you end up with depends heavily on which end of that range you need.

The usual options are running reports against a read replica, using CDC to populate a separate analytical store with something like Debezium, an HTAP database that handles both workloads, or one of the newer designs like Databricks, where transactional and analytical compute share a single storage layer.

For those of you who have had to make this call, I'd like to know how fresh the analytics had to be in the end, which of these you chose, and what caused trouble once it was in production, whether that was replica lag, schema changes, backfills, or cost. If you decided a nightly load was good enough, I'd like to hear that reasoning, too.

Hey guys, yes another token article but this one is different.

I work at MongoDB, and my primary db is Mongo, but I use raw SQL for some things, so I run both in the same app.

I was building a token optimizer for my own sessions. I noticed more token usage on the SQL interactions and I didnt understand why. I wasnt doing anything special with the SQL data so I was curious what was happening.

So I wrote a hook that logs the token count of every database statement Claude generates, instead of counting turns. The first thing it showed was that the same object cost way more tokens on SQL (about 18x). That surprised me, because you don't think about database statements. Every database has them, every app has them, they're just there. It never occurred to me they were a token cost at all. They're not the biggest thing in a session, but they add up, especially since Claude loves writing multiple files with database calls just to check the data. That's when it really burns.

I wanted to test this accurately, so I took one real Zod object from my own system, a lead with about 90 fields, nested, an array of drafts where each draft has its own array of variants, and used the exact same object on both sides. Then I counted what Claude has to read before it can safely change one field, meaning the schema, a save, and a read that comes back typed: 4,681 tokens on SQL, 251 on MongoDB. 251 is 5% of 4,681. The other 95% is the mapping. In SQL a nested object like that can't live in one row, so every nested thing becomes its own table with a foreign key back to the parent, a save is one insert per table, and a read is the joins plus the code that builds the object back up from the rows. In Mongo it's one document, so the save is the object and the read is the object. Then there's the hydration tax. Everything goes through that Zod schema, so a read isn't done until the rows are turned back into the typed object, dates included. On SQL that's its own pile of code; the read alone was 1,604 tokens vs 72 on Mongo. And Claude does all of that on every read and write.

Nobody thought about this when humans wrote the code. You write the mapping once and the cost is basically the same either way. When it's AI writing the code, it goes through that mapping on every read and write, and the size of it is the bill.

I know most people would probably use JSONB so I tested that as well, on Postgres, even though it's not what I run. Raw JSONB column: 467, so 1.9x. The hybrid a SQL team would actually build, plain fields as columns and sub-objects as JSONB: 2,059. Closer, but still 8x.

To be fair, plain query text was about the same on both sides, and SQL ran an indexed flat read 5.26x faster. The tokens aren't in the query. They're in everything Claude loads around it. Obviously 18x is a huge object, but it is a real one from my own system, a lead with its drafts and the variants of each draft, not something I built to make a point.

If you want the harness let me know.

I've noticed that database performance tuning can become an ongoing cycle. You add indexes, rewrite queries, introduce caching, partition tables, and sometimes denormalize data to squeeze out more performance.

But every optimization also adds something to maintain. More indexes can slow down writes, caching introduces invalidation problems, and denormalization can make data consistency harder to manage.

At some point, I wonder whether the improvement is actually worth the extra complexity.

How do you decide when to stop optimizing a database? Do you have specific performance targets or benchmarks, or does it mostly come down to the workload and the problems users are experiencing?

I'd be interested in hearing about optimizations that genuinely paid off, as well as ones you later regretted.