v0.5.0 — Row-Level Security, ETL Gating, and API Polish

Full technical details: v0.5.0.md-full.md

Status: ✅ Released | Scope: Medium (~3 weeks)

Stream tables now respect PostgreSQL’s row-level security policies, ETL pipelines can pause change capture while loading bulk data, and the API surface is cleaned up based on early user feedback.

What problem does this solve?

Enterprise deployments often enforce access controls at the row level — different users see different rows depending on their role or tenant. Stream tables need to respect these rules. ETL pipelines that bulk-load data need a way to prevent half-loaded data from appearing in stream table results. And after the v0.2–v0.4 feature sprint, the API had accumulated some rough edges that needed polishing.

Row-Level Security (RLS) Support

PostgreSQL’s row-level security (RLS) feature lets you define policies such as “users can only see orders belonging to their own organisation.” These policies are enforced transparently by the database for regular queries.

Stream tables now fully respect RLS. When a stream table is refreshed, the differential engine applies the same RLS policies as a direct query would. The stream table result reflects only the rows the owner of the stream table is allowed to see.

In plain terms: if your database uses row-level security for multi-tenant isolation, your stream tables will correctly reflect only the data each tenant should see.

Bootstrap Source Gating: Pause and Resume

ETL pipelines typically load data in large batches — they might insert millions of rows over several minutes before the data is complete and consistent. If pg_trickle’s change capture is active during this load, it may generate a stream table that reflects a half-loaded state.

Bootstrap source gating allows an ETL pipeline to call pgtrickle.pause_source(table_name) before loading and pgtrickle.resume_source(table_name) after, ensuring that change capture only starts once the full batch is committed and consistent.

In plain terms: ETL pipelines can safely load large batches without triggering intermediate stream table refreshes from incomplete data.

API Ergonomics

Based on feedback from early users, several API improvements were made:

  • create_stream_table now validates the query immediately at creation time and returns a helpful error message if the query uses an unsupported pattern, rather than failing silently on the first refresh
  • drop_stream_table is more robust — it cleans up change buffers, triggers, and catalog rows even if some were already missing (idempotent cleanup)
  • Better parameter names and documentation in all SQL functions

Scope

v0.5.0 adds RLS support (necessary for multi-tenant deployments), ETL gating (necessary for reliable bulk loading), and API polish that reduces friction in day-to-day use. Together these make pg_trickle more production- ready for enterprise environments.