Hi S***n,
Below is a simplified excerpt from our CI setup and partition-split function. It should give you a clear view of how we manage schema migrations, fixture seeding, index rebuilding and catalog updates without blocking batch jobs.
1. Docker-Compose + Fixture Seeding (CI)
```yaml
version: '3.8'
services:
ci-db:
image: postgis/postgis:13-3.1
environment:
POSTGRES_USER: ci_user
POSTGRES_PASSWORD: ci_pass
POSTGRES_DB: mobildb_test
ports:
- "5433:5432"
volumes:
- ./migrations:/migrations
- ./fixtures:/fixtures
command: >
sh -c "
# 1. Run migrations (using Flyway or psql scripts)
for f in /migrations/*.sql; do psql -U ci_user -d mobildb_test -f \"$f\"; done &&
# 2. Load seed data
psql -U ci_user -d mobildb_test -f /fixtures/seed_data.sql
"
```
, We spin up this container in CI, apply all `.sql` migration files in order, then execute `seed_data.sql` (a version-controlled fixture set).
, After seeding, our R/Python integration tests point at `postgres://ci_user:ci_pass@ci-db:5432/mobildb_test`.
2. PL/pgSQL “split” Function with Non-Blocking Index Rebuild
```sql
CREATE OR REPLACE FUNCTION split_raster_partition(
parent_table TEXT,
new_bounds BOX2D
) RETURNS VOID LANGUAGE plpgsql AS
$$
DECLARE
child TEXT := parent_table || '_part_' || to_char(clock_timestamp(), 'YYYYMMDDHH24MI');
idx TEXT;
BEGIN
-- 1. Create new partition (declarative)
EXECUTE format('
CREATE TABLE %I PARTITION OF %I
FOR VALUES FROM (%L, %L) TO (%L, %L);
',
child, parent_table,
new_bounds[0], new_bounds[1],
new_bounds[2], new_bounds[3]
);
-- 2. Populate new partition from parent
EXECUTE format('
INSERT INTO %I
SELECT * FROM %I
WHERE geom && %L::box2d;
',
child, parent_table, new_bounds
);
-- 3. Rebuild indexes concurrently on the new partition
FOR idx IN
SELECT indexname
FROM pg_indexes
WHERE tablename = parent_table
LOOP
EXECUTE format('CREATE INDEX CONCURRENTLY ON %I USING %s (%s);',
child,
(SELECT amname FROM pg_am WHERE oid = (SELECT indexrelid::regprocedure::oid FROM pg_index WHERE indexrelid = idx::regclass)),
(SELECT string_agg(attname, ', ') FROM pg_attribute
WHERE attrelid = idx::regclass AND attnum > 0 AND NOT attisdropped)
);
END LOOP;
-- 4. ANALYZE to update planner statistics
EXECUTE format('ANALYZE %I;', child);
END;
$$;
```
Key points:
- We use PG’s declarative partitioning, so catalog updates (pg_partitioned_table) happen automatically when the `CREATE TABLE … PARTITION OF` runs.
- Indexes on the new partition are built with `CONCURRENTLY` to avoid locks on parent or other partitions.
- An `ANALYZE` ensures our query planner has up-to-date stats without pausing writes on other tables.
Let me know if you’d like a deeper walkthrough or to chat directly with one of our backend engineers. Happy to schedule a follow-up or send over more examples. Looking forward to next steps!