We built our customer data warehouse all on Postgres

Original Article ↗ Hacker News Discussion ↗

Building a customer data warehouse entirely on PostgreSQL—using features like foreign data wrappers and pg_cron instead of tools such as Fivetran or BigQuery—promises simplicity and a single, well-known stack, but raises questions about scalability and long-term maintainability. Commenters debate how far to push business logic and orchestration into the database, citing weak tooling for testing, debugging and refactoring stored procedures versus the maturity of external ETL/orchestration frameworks and columnar warehouses. The thread also highlights product concerns around clear positioning and transparent pricing for Postgres-based platforms aiming to replace more specialized analytics stacks.

Postgres as Data Warehouse vs Specialized Warehouses

  • Some see a Postgres-only “warehouse” as attractive for simplicity and reduced tool sprawl, especially when data volume is modest.
  • Others argue Postgres “doesn’t scale for analytics” compared to columnar warehouses (BigQuery, Snowflake, Redshift, ClickHouse), especially for heavy aggregations and long retention.
  • Critics note this approach often means keeping only short data windows (e.g., 30 days), which many businesses would consider insufficient given cheap columnar storage.
  • There’s mention of columnar/analytics extensions and projects (e.g., pg_analytics, ParadeDB, S3-backed storage, Neon-like architectures) as ways to extend Postgres toward warehouse use cases.

Business Logic Inside the Database

  • One camp: “Use Postgres as a data store only.” Avoid pg_cron pipelines, heavy PL/pgSQL, or Supabase-style APIs/permissions in-DB; they’re hard to test, debug, refactor, and onboard new engineers to.
  • Opposing camp: well-designed functions, triggers, row-level security, and PostgREST APIs can significantly improve security and reduce bugs, especially for access control and integrity constraints.
  • Concern about “vendor lock-in” is raised, but others counter that leaning on Postgres-specific features is worth the performance and capabilities.

Tooling, Testing, and Migrations

  • Many complain about weak ergonomics for database logic: limited IDE support, refactoring, and documentation generation compared to general-purpose languages.
  • Others point to emerging tools: postgres_lsp, DataGrip/JetBrains IDEs, pgpkg, skeema-like approaches, PL/pgSQL linters, test frameworks, Docker/Testcontainers-based integration tests, and Liquibase/Flyway/dbt for versioning and testing SQL.
  • There’s broad agreement that keeping SQL objects in files under Git and deploying via migrations or declarative tools is key to sanity.

Foreign Data Wrappers & Data Movement

  • Some prefer FDWs over tools like Fivetran/Airbyte for simplicity; others report serious performance issues for large or complex cross-database queries and favor ETL tools or middle-tier code.
  • Practical DW patterns discussed include schema swapping for atomic refreshes, chunked copying to avoid long locks, and external schedulers (cron/ECS) rather than pg_cron.

Definitions, Streaming, and Product Feedback

  • Several argue the described system is “just a database” or “customer usage metrics,” not a full data warehouse.
  • There’s interest in streaming/continuous queries on Postgres (e.g., Materialize-like behavior, Debezium-based roll-your-own, epsio.io), but current options are seen as either heavy or incomplete.
  • Multiple commenters criticize Tembo’s website for unclear positioning and deeply buried pricing, urging a visible pricing page and non-intrusive ways to capture interest (e.g., simple newsletter forms).