I've built myself a few problems that I haven't fixed yet:

Many materialized views that rely on materialized views. When one at the bottom, or a table, needs a changed all views need to be dropped and recreated.

Using a warm standby for production. I love having a read only production database, but since it's not the primary, it always feels like it's on the losing end of the system. Recently upgraded to Postgres 18 and forgot that means I need to rm rf the standby and pg_basebackup to rebuild... That wasn't fun.

▲

physicles 3 hours ago | parent | next [-]

Why do you need a warm standby for production? Do you need >= 3 nines?

Our staging environment has its own instance that is rebuilt from prod, with pii removed, every day outside working hours (this normally takes about 15 minutes). It’s fantastic for testing migrations, and is easy to support compared with a warm standby.

	▲	ddxv an hour ago \| parent [-]
		"Do you need >= 3 nines?" No, it's a one person project. I'd be happy with a single 9 or even no 9s just a 99.0 haha I switched to warm standby to reduce stress on the production db which was in the cloud. There is just a single production server and having it constantly run the heavy data processing MVs + handle queries was CPU intensive and slowed everything down. The CPU was costly. To fix those issues, especially the CPU, I run the primary on a home server where it can crank the CPU as much as it wants running the data processing MVs and then sends the processed WALs to the warm standby that just handles the queries. This has fixed those CPU and slow queries (when an MV is updating a table that is being constantly read). But introduced headaches anytime I update postgres. My understanding is the 'fix' is to move data processing to another postgresql DB or flow? My biggest reason for not using another DB is I didn't like the idea of losing direct relations for keys. Anyways, I appreciate the input, it's been a thorny issue I hit once or twice a year and am always unsure if what I'm doing is 'normal' or what I should do to fix it.

▲

echelon 10 hours ago | parent | prev [-]

I'd like to call views, triggers, and integrity constraints antipatterns.

Your code should handle the data model and never allow bad states to enter the database.

There's too much performance loss and too many footguns from these "features".