Guys, so I've been working on a data pipeline project and the PostgreSQL schema side of things is starting to get messy as the volume grows. Here in the US most teams I've worked with just wing it early on and then spend months untangling the structure later. Anyone dealt with designing a clean schema from the start that actually scales without becoming a nightmare to maintain?
The naming conventions and separation of concerns are what save you long term. Keeping raw ingestion tables completely separate from transformed data is the thing most people skip early on and regret later. Schema versioning from day one also makes a huge difference when the team starts growing.
The separation point is spot on. A well structured PostgreSQL schema should make it obvious where data comes in, where it gets transformed and where it lands for consumption, without having to trace through a tangle of joined tables to figure out what's happening. Destination configuration matters a lot too when you're pulling data in from external sources, getting that layer right upstream saves a lot of cleanup further down the pipeline. Worth looking at how modern integration tools handle this before building everything from scratch. One worth checking out for PostgreSQL destination setup is http://datrise.com/en/integrations/destination/postgresql