A second Postgres makes a decent warehouse for a company with one data engineer, and it gives out one workload at a time, never all at once.
That second half is what most warehouse debates miss. When one workload outgrows the box, the usual answer is to move everything, and it gets decided at night by tired people. I’d rather know ahead of time which workload leaves first and where it lands.
This post assumes you’ve already made the first split: analytics no longer runs on the application database. What follows is the sizing for the second one, and mostly the column that says where each workload goes.
What the second Postgres holds
The platform is four ordinary Postgres features.
Logical replication lands the app’s tables in a raw schema. A publication on the app database, a subscription on the analytics one, and the tables arrive a few seconds behind. Two catches. Schema changes don’t replicate, so a new column on the app has to be added on the analytics side too or the apply worker stops. And a slot that nobody is reading holds WAL on the app database until its disk fills, so set max_slot_wal_keep_size there and accept that a slot which blows through it has to be rebuilt.
Schemas are the layers: raw, staging, marts. Scheduled SQL is the pipeline. I’d run it as a dbt project with contracts on the marts models, because a contract fails the build when a column changes type, and that’s cheaper than hearing about it from the analyst.
Roles are the access model. A loader role owns the subscription, a transformer role writes staging and marts, and a bi_reader role can select from marts and nothing else, with a statement_timeout set on the role. Dashboards that need to be quick read materialized views refreshed with REFRESH MATERIALIZED VIEW CONCURRENTLY.
Four features, no new service. One data engineer can hold all of it in their head, which is the real reason to pick it.
One limit per workload, and a place to go
A single limit for “the database” is useless, because one workload gives out long before the rest. So the limits go per workload, each with a warning value, an exit value, and the place that workload goes when it crosses.
To make it concrete, take Lanyard Lockers, a parcel-locker company I made up: 34 people, one data engineer and one analyst. Their analytics Postgres has 8 vCPU and 64 GB of memory. After a year it holds 140 GB, and 41 models build nightly in 11 minutes. Dashboard p95 sits at 2.1 seconds and replication lag stays under 10 seconds. This is the table they wrote on day one.
| Workload | Metric | Warning | Exit | Where it goes |
|---|---|---|---|---|
| Landing replicated tables | Replication lag, sustained | 1 minute | 5 minutes | A log-based CDC tool writing to the same raw schema |
| Transformations | Nightly build duration | 30 minutes | 45 minutes | Slowest model goes incremental; if its table is the cause, it leaves with the table |
| Dashboards and ad-hoc queries | p95 query time for bi_reader |
3 seconds | 5 seconds | A materialized view first, then a columnar engine for that one mart |
| Largest fact table | Row count | 350 million | 500 million | Parquet files read by a columnar engine |
| Exports out | Longest export run | 10 minutes | 30 minutes | Exports written from Parquet in object storage |
The values are theirs, set against their hardware. Yours will differ. What I’d insist on is the last column. An exit value with no destination is just a threshold for worrying.
The SQL that fills in the table every Monday
Each metric needs a query somebody can run without thinking. The first one runs on the app database, the rest on the analytics one.
-- 1. Replication lag and WAL held by the slot (run on the app database)
select s.slot_name,
s.active,
pg_size_pretty(
pg_wal_lsn_diff(pg_current_wal_lsn(), s.restart_lsn)
) as wal_held,
pg_size_pretty(
pg_wal_lsn_diff(pg_current_wal_lsn(), s.confirmed_flush_lsn)
) as behind,
r.replay_lag
from pg_replication_slots s
left join pg_stat_replication r on r.pid = s.active_pid
where s.slot_type = 'logical';
-- 2. Build and export duration, from a run log the scheduler writes
create schema if not exists ops;
create table if not exists ops.job_runs (
job text not null,
started_at timestamptz not null,
finished_at timestamptz,
status text not null default 'running'
);
select job,
count(*) as runs,
max(finished_at - started_at) as longest,
avg(finished_at - started_at) as average
from ops.job_runs
where started_at >= now() - interval '7 days'
and status = 'success'
group by job
order by longest desc;
-- 3. Estimated p95 per dashboard statement (PostgreSQL 13 or later)
select left(s.query, 60) as query,
s.calls,
round((s.mean_exec_time + 1.645 * s.stddev_exec_time)::numeric) as est_p95_ms
from pg_stat_statements s
join pg_roles r on r.oid = s.userid
where r.rolname = 'bi_reader'
and s.calls >= 50
order by est_p95_ms desc
limit 10;
-- 4. Table size and estimated row count
select n.nspname as schema,
c.relname as table_name,
pg_size_pretty(pg_total_relation_size(c.oid)) as total_size,
c.reltuples::bigint as est_rows
from pg_class c
join pg_namespace n on n.oid = c.relnamespace
where c.relkind in ('r', 'm')
and n.nspname in ('raw', 'staging', 'marts')
order by pg_total_relation_size(c.oid) desc
limit 15;
A few caveats. pg_stat_statements doesn’t store percentiles, so query 3 estimates one from the mean and standard deviation. That’s rough for queries with a long tail and good enough for a weekly trend. Call pg_stat_statements_reset() after each reading so the numbers cover one week. The job log only helps if the scheduler writes a row at start and updates it at the end, failures included. reltuples is the planner’s estimate and is only as fresh as the last analyze, and a partitioned table shows up as one row per partition, so add those up yourself. Replication lag is the one metric that can’t wait for Monday: the weekly reading goes in the table, but the same query also belongs in an alert.
If the build number creeps without any table growing, look for models nobody queries before you look for hardware. I covered that in the data models nobody reads.
Month 14 at Lanyard
In month 14, locker_events passes 500 million rows and the nightly build reaches 48 minutes. Two exit values crossed in the same week, and both point at one table.
Because the warning values tripped a couple of months earlier, the move is already on the calendar. locker_events is partitioned by month, so closed months get written out as Parquet files and dropped from Postgres. DuckDB reads the files, builds the daily rollups that used to be the slowest models, and loads those small results back into marts. The dashboards never learn that anything changed, since they still query the same tables through the same role.
The build drops to 13 minutes. The other models, the replication setup, the roles and the exports stay where they were.
That’s what the table buys: one table moved, on a date picked in advance, and nobody had to choose a warehouse that month. The other rows go back to being checked on Mondays.
Where this stops being a good idea
I wouldn’t run this past a certain amount of concurrency or team size. Once three or four people write transformations at the same time, or the exit lane holds more tables than the database does, you’re operating two platforms and should admit it. Two of the big warehouse vendors now offer managed Postgres next to their columnar engines, which makes the later move shorter than it used to be. It doesn’t make it something to decide during an incident.
Back at Lanyard, the next warning is replication lag, because the app is about to start logging every door open, and that row’s destination is already written next to it.