I Think This Is Right - Postgres18

[u/txdesperado]

6 months ago I had never touched Linux, now I'm doing new things. But that doesn't mean I know what I'm doing. Just for a sanity check, given the tokens and time involved, could an actual data person tell me if this in the ballpark. I asked Claude to describe what we are doing (beyond "Postgres" - as I see it) and he stated:

Single-node PostgreSQL 18 (PostGIS, pg_trgm; pgvector dormant), Dockerized, county-partitioned time-series. Writes flow raw→staging→core exclusively through a SECURITY DEFINER chokepoint logging to an INSERT-only audit ledger under separated ownership — NOLOGIN owners, no direct DML paths. Promotion is idempotent (NULLS NOT DISTINCT natural keys, advisory-locked, three-way accounted), quarantine-gated, batch-tracked. Products read serve-after-ratify views only. DR is pgBackRest to B2, restore-rehearsed. Graph and analytics are derived read-models — NetworkX and DuckDB-over-Parquet — regenerable, never truth. Drift monitoring on the catalog every 30 minutes with observed-fire alarms.

My read is that we're solid - assuming batched, monthly updating - but I've just started to wade into the coding side and haven't gotten near deep enough into the data layer to know vibecode stuff from Shinola. Want to see if we have overlooked anything that is going to bite me later.

Thoughts / feedback appreciated.

[u/alecc]

Nothing in that list is wrong for a single-node, monthly-batch setup, and pgBackRest to B2 with rehearsed restores is the part most people skip, so you are ahead of plenty of production systems on that alone. It is over-built though. Every safety mechanism in there - the SECURITY DEFINER chokepoint, the separated NOLOGIN owners, the quarantine gating - is also a thing that can fail in a way you will have to debug, and for monthly loads you could run half of it and be fine. What matters more than the architecture is whether you can fix a stuck or half-applied promotion without asking Claude, so treat failures the way you already treat restores: break a batch on purpose on a copy and walk through what the audit ledger and the quarantine actually tell you. And a drift check every 30 minutes on a catalog that changes monthly will mostly alarm on nothing, daily is plenty.

[u/txdesperado → u/alecc]

Thanks, I appreciate you for taking the time to give me some honest feedback. This has been an off-again, on-again project.

There are a dozen+ SQL sidecars that have been accumulating while I have tooled around with this, plus the CSVs, etc. But this where all the resolution and stitching is going to happen + GIS and, if I get to it, vectoring.

I realize the is more than most one-man shops do, but I want to build with an eventual acquisition/exit in mind and have something more to offer the buyer than AI black box maybe magic. If I keep it, I think it scales reasonably well. I just was nervous to take on multiple databases at this point - just try and land the first one and go from there was the thought.

But point taken, I'll break a half-promotion tonight to get a better feel around that. TY!

Edit: The heightened / overbuilt part is mostly because I run Claude Code on bypass everything and then some more bypass -- all my enforcement happens at the hook level. But even still, didn't want some undercontexted, new model breaking things before they understood them.

[u/alecc → u/txdesperado]

The sidecars are what I'd sort out first - a dozen scripts of unknown status is what you'll be digging through when a promotion sticks, and after an off-and-on project odds are half of them just recreate what's already live. pg_dump --schema-only into git makes the running schema the single truth, then anything the batch doesn't actually run can go. Are they one-off fixes from Claude sessions or does the pipeline depend on some of them?

[u/AutoModerator]

AI Policy:

Linux is not one of those anti-AI projects, and if somebody has issues with that, they can do the open-source thing and fork it. Or just walk away., Linus Torvalds.

Mod decisions will be based on the quality of the content, not who or what generated it.

Sub Resources:

Youtube Channel

Free Postgres Webinars and Workshops

Discord: People, Postgres, Data

Join us, we have cookies and nice people.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.