Short answer: Prefect doesn’t have first‑class support for TimescaleDB. Prefect Server (self‑hosted) supports PostgreSQL and requires the

pg_trgm

extension; it does not create or manage Timescale hypertables. If you install TimescaleDB, Prefect will treat it as normal Postgres. To “make the most” of your database with Prefect, focus on standard Postgres tuning and Prefect’s DB settings. If you want to use Timescale features (hypertables, compression, continuous aggregates), that involves changing Prefect’s schema and is not supported and may break migrations. Key links - Prefect Server requirements (pg_trgm): Prefect 3 Server concepts - Prefect settings reference: Settings reference - DB config classes: Server DB configuration classes - How Prefect scales Postgres without Timescale (native partitioning): Prefect blog: Partitioning Postgres without downtime - Connection/pooling considerations: More memory, more problems What you can safely tune today A) Prefect (App) database settings Set these via environment variables or your profile/TOML. They control connection pooling and timeouts between Prefect and Postgres. - Pool size and overflow -

PREFECT_SERVER_DATABASE_SQLALCHEMY_POOL_SIZE

(e.g., 10–50 depending on concurrency) -

PREFECT_SERVER_DATABASE_SQLALCHEMY_MAX_OVERFLOW

(e.g., 10–50 for bursts) - Timeouts -

PREFECT_SERVER_DATABASE_CONNECTION_TIMEOUT

(default ~5s; raise to 10–30s if connections are slow) -

PREFECT_SERVER_DATABASE_TIMEOUT

(statement timeout; default ~10s; raise for migrations or heavy queries) - Prepared statement cache (asyncpg) -

PREFECT_SERVER_DATABASE_STATEMENT_CACHE_SIZE

-

PREFECT_SERVER_DATABASE_PREPARED_STATEMENT_CACHE_SIZE

- Misc -

PREFECT_SERVER_DATABASE_SQLALCHEMY_ECHO

(true for query logging during troubleshooting) -

PREFECT_SERVER_DATABASE_CONNECTION_APP_NAME

(sets

application_name

) Reference: Settings reference and DB config classes. B) PostgreSQL settings (generally beneficial for Prefect workloads) - Required extension - Ensure

pg_trgm

is installed:

CREATE EXTENSION IF NOT EXISTS pg_trgm;

- Memory and cache -

shared_buffers

~ 25% of RAM -

effective_cache_size

~ 50–75% of RAM -

work_mem

sized for your concurrent queries; start small (e.g., 4–32MB) and adjust -

maintenance_work_mem

higher for faster index builds/vacuum (e.g., 512MB–2GB during maintenance) - WAL/checkpoint -

max_wal_size

larger (e.g., 2–8GB) to reduce checkpoints -

checkpoint_timeout

10–15m;

checkpoint_completion_target

~0.9 - Autovacuum (important for insert/update heavy tables like events, states, logs) - Increase

autovacuum_max_workers

, lower

autovacuum_naptime

- Consider per-table autovacuum settings (lower

vacuum_scale_factor

/

analyze_scale_factor

) - Parallelism and IO -

max_worker_processes

,

max_parallel_workers

,

max_parallel_workers_per_gather

- On SSDs:

effective_io_concurrency

> 0; tune

random_page_cost

(e.g., 1.1–1.5) - Optional durability trade-offs - If occasional log/event loss is acceptable, consider

synchronous_commit = off

at session level for log/event writers only. Do not change globally if you can’t accept data loss. C) Schema/index hygiene - Keep Postgres up to date; apply recommended extensions and indexes - Monitor bloat and vacuum/analyze regularly - Ensure your Postgres instance has enough connections to match Prefect’s pool plus other clients About TimescaleDB specifically

- It’s not required by Prefect and not officially supported. Installing the extension is harmless:

CREATE EXTENSION IF NOT EXISTS timescaledb;

- To actually benefit (hypertables, compression, continuous aggregates), you’d have to convert Prefect tables to hypertables and add policies. That modifies schema and can break Prefect migrations and upgrades. We do not recommend this for production Prefect. - If you still want to experiment (unsupported, at-your-own-risk): - Likely candidates: large time-series tables (e.g., events, logs). You’d need to choose the time column and create hypertables with a sensible

chunk_time_interval

(hours–days depending on write rate). - Enable compression and retention policies and set

timescaledb.max_background_workers

high enough for your policies. - Example outline (do NOT run in prod without testing and backup):

-- Example: convert a large time-series table to a hypertable
    SELECT create_hypertable('public.events', by_range('occurred'), chunk_time_interval => interval '1 day');

    -- Compression
    ALTER TABLE public.events SET (timescaledb.compress, timescaledb.compress_segmentby = 'flow_id');
    SELECT add_compression_policy('public.events', INTERVAL '7 days');

    -- Retention
    SELECT add_retention_policy('public.events', INTERVAL '90 days');

    -- Background workers for policies
    ALTER SYSTEM SET timescaledb.max_background_workers = 8;
    SELECT pg_reload_conf();

- Expect Prefect Alembic migrations to fail or require manual intervention after such changes. Questions to help tailor guidance - Which Prefect version are you running (assuming 3.x unless you say otherwise)? - What’s your goal with Timescale: faster writes, cheaper storage via compression, longer retention, or analytics on events/logs? - Current DB size, write rate, and any specific performance symptoms (timeouts, slow queries, high CPU, I/O bottlenecks)? If you share those details, I can suggest concrete values for Prefect’s pool/timeouts and Postgres/autovacuum settings, and highlight any safe schema/index changes for your workload.

Great, thanks — that helps. Given: - Prefect 3.x - Goal: cheaper storage - Early stage (≈4 GB), high-ish CPU - You asked: “Where can I find the write rate easily?” First: how to see your write rate quickly Option A: overall write rate (rows/sec) for the current database - This samples pg_stat_database twice and computes a delta. Run in psql.

WITH s1 AS (
  SELECT now() ts, tup_inserted, tup_updated, tup_deleted
  FROM pg_stat_database WHERE datname = current_database()
),
pause AS (SELECT pg_sleep(10)),
s2 AS (
  SELECT now() ts, tup_inserted, tup_updated, tup_deleted
  FROM pg_stat_database WHERE datname = current_database()
)
SELECT
  ((s2.tup_inserted - s1.tup_inserted)
 + (s2.tup_updated  - s1.tup_updated)
 + (s2.tup_deleted  - s1.tup_deleted))
 / EXTRACT(EPOCH FROM (s2.ts - s1.ts))::numeric AS tuples_per_sec
FROM s1, s2;

Option B: per-table write rate (rows/sec) — find the hottest tables

WITH s1 AS (
  SELECT oid, relname, n_tup_ins, n_tup_upd, n_tup_del, now() ts
  FROM pg_stat_user_tables
),
pause AS (SELECT pg_sleep(10)),
s2 AS (
  SELECT oid, relname, n_tup_ins, n_tup_upd, n_tup_del, now() ts
  FROM pg_stat_user_tables
)
SELECT
  s2.relname,
  ((s2.n_tup_ins - s1.n_tup_ins)
 + (s2.n_tup_upd - s1.n_tup_upd)
 + (s2.n_tup_del - s1.n_tup_del))
 / EXTRACT(EPOCH FROM (s2.ts - s1.ts))::numeric AS tuples_per_sec
FROM s1 JOIN s2 USING (oid, relname)
ORDER BY tuples_per_sec DESC
LIMIT 20;

Option C: WAL bytes/sec (good proxy for actual write pressure) - On Postgres 14+, use pg_stat_wal:

WITH s1 AS (SELECT now() ts, wal_bytes FROM pg_stat_wal),
pause AS (SELECT pg_sleep(10)),
s2 AS (SELECT now() ts, wal_bytes FROM pg_stat_wal)
SELECT (s2.wal_bytes - s1.wal_bytes)
     / EXTRACT(EPOCH FROM (s2.ts - s1.ts))::numeric AS wal_bytes_per_sec
FROM s1, s2;

- If pg_stat_wal isn’t available, sample

pg_current_wal_lsn()

twice and use

pg_wal_lsn_diff

. Recommendations for cheaper storage (without TimescaleDB) TimescaleDB isn’t officially supported by Prefect and converting Prefect tables into hypertables/compression can break migrations. You can get most of the “cheaper storage” benefit via supported paths: 1) Keep large results out of the DB (use object storage for results) - Create a storage block (S3/GCS/Azure) in the Prefect UI. - Set: -

PREFECT_RESULTS_DEFAULT_STORAGE_BLOCK=<your-block-slug>

-

PREFECT_RESULTS_PERSIST_BY_DEFAULT=true

- This stores result payloads in cheap object storage; the database only keeps small metadata. - Settings reference: Settings reference 2) Control event/log ingestion behavior - Prefect’s event persister batches writes; increasing batch size and/or flush interval reduces write amplification (at the cost of slightly higher latency and a bit more data at risk on crash). - Look for Event Persister settings (e.g., batch_size, flush_interval) in the settings UI or via env vars. See: Settings reference - From code signatures: the defaults are roughly batch_size=20, flush_interval=5s, which you can increase modestly. 3) Retain less data in Postgres - Decide how long you actually need to keep events/logs. Implement a scheduled cleanup (cron, or even a small Prefect flow that runs daily) to delete rows older than N days and VACUUM/ANALYZE afterward. - This is the single biggest lever for storage cost over time. 4) Basic Postgres tuning to reduce CPU - Ensure

pg_trgm

is enabled (required by Prefect):

CREATE EXTENSION IF NOT EXISTS pg_trgm;

- Right-size memory:

shared_buffers

(~25% RAM),

effective_cache_size

(50–75% RAM), moderate

work_mem

. - Autovacuum: increase

autovacuum_max_workers

, reduce scale factors on hot tables; this keeps bloat in check. - WAL/checkpoints: larger

max_wal_size

,

checkpoint_completion_target ~ 0.9

.

- If you’re on a cloud provider (e.g., RDS), monitor WriteIOPS and DiskThroughput metrics — they’re a good “write pressure” indicator as well. Prefect DB settings you can tune for connection efficiency - Pooling: -

PREFECT_SERVER_DATABASE_SQLALCHEMY_POOL_SIZE

-

PREFECT_SERVER_DATABASE_SQLALCHEMY_MAX_OVERFLOW

- Timeouts: -

PREFECT_SERVER_DATABASE_CONNECTION_TIMEOUT

-

PREFECT_SERVER_DATABASE_TIMEOUT

- Prepared statement cache: -

PREFECT_SERVER_DATABASE_STATEMENT_CACHE_SIZE

-

PREFECT_SERVER_DATABASE_PREPARED_STATEMENT_CACHE_SIZE

- Docs: Server DB configuration classes and Settings reference Two quick clarifications that will let me give you more concrete numbers: - Are you running Postgres on RDS, a container, or bare metal? - How long do you want to retain logs/events (e.g., 7, 30, 90 days)? Once we pick a retention target, I can suggest a safe cleanup query and a schedule, plus starting values for pool sizes and batch settings. References - Prefect 3 Server concepts (Postgres + pg_trgm): Server concepts - Settings reference: Settings reference - DB configuration classes: Server DB configuration classes - Prefect’s approach to scaling Postgres (native partitioning; not Timescale): Partitioning Postgres without downtime - Connection/pooling considerations: More memory, more problems