Memory for index builds
A TIN index build runs in parallel, and each worker prefers about 1 GB of memory. The budget comes frommaintenance_work_mem, split evenly across the workers. TIN starts as many workers as max_parallel_maintenance_workers allows and the budget can support, so a small maintenance_work_mem means fewer workers and a slower build. A worker can run with less than 1 GB, but the index it writes is more fragmented, and that fragmentation slows every query on the index until you REINDEX it with more memory.
Before building an index on a large table, raise the budget for that session:
shared_buffers and the memory your normal traffic needs. On network-attached storage clusters, a PS-160 has 16 GB of RAM and a PS-1280 has 128 GB, so an 8 GB build budget fits comfortably on the larger size and not at all on the smaller one. Both settings are also available under Parameters when you want a cluster-wide default rather than a session setting.
Storage
PlanetScale clusters run on network-attached storage (AWS gp3, Amazon Elastic Block Store) or on PlanetScale Metal, which uses locally attached NVMe drives. Network-attached storage supports storage autoscaling and comes with an IOPS and throughput allowance that you can raise. Every read that misses the cache pays network latency, and a search over a cold index is many such reads. TIN reads index pages as sequentially as it can and returns matching rows in heap order within each segment, which keeps its I/O pattern friendly to network-attached storage and to Postgres’ caching. Metal has no IOPS limit and much lower latency per read, and its drive size is fixed when the cluster is created. For a search index that does not fit in memory, Metal is the faster choice, and it is the one to prefer when search latency matters.effective_io_concurrency matters on network-attached storage. When it is positive, TIN issues readahead hints for some index reads. Keep it positive when the index is read from disk, and set it to 0 when the index fits in shared_buffers, where the hints are pure overhead.
Parallel query
TIN splits an index into segments at build time, one per core by default, and a query can use one parallel worker per segment. Postgres caps the workers withmax_parallel_workers_per_gather and max_parallel_workers, both available under Parameters. As an index grows, the work per query grows with it, and TIN spreads that work across the cores it is given, so a multi-terabyte index performs best on a cluster size with more vCPUs. If you build on one cluster size and serve on another, set initial_segment_count for the serving size, because the segment count is fixed at build. See Index options.
Read replicas
Sethot_standby_feedback = on on every replica that serves TIN queries. TIN’s background maintenance frees index pages once no transaction on the primary needs them, and the feedback is what tells the primary that a query on a replica still does. Without it, replica queries that overlap with maintenance are cancelled with SQLSTATE 40001 more often. Results on a replica are always exact. Under heavy replication traffic a query can still be cancelled with 40001, and the application should retry it. hot_standby_feedback is available under Parameters. See also Replicas.
Vacuum
When a row is updated or deleted, Postgres keeps the old version in the table untilVACUUM removes it, and the row’s entry stays in the TIN index. TIN learns that the entry is dead only when VACUUM runs on the table. Until then, a query that reaches the entry has to check the row in the heap to find out whether it is still visible.
count(*) is affected most. A count over a TIN predicate is normally answered from counts stored in the index. TIN trusts those stored counts only where it knows of no dead entries and Postgres’s visibility map marks every row visible. Rows deleted or updated since the last VACUUM break both conditions, so the count falls back to checking rows in the heap. On a table with steady update or delete traffic, count performance degrades between vacuums and recovers after each one.
VACUUM also drives background maintenance. It marks deleted entries dead in the index, and once a segment’s dead fraction crosses dead_percent_threshold (default 0.5), background maintenance rewrites the segment without them. BM25 statistics keep counting deleted rows until that rewrite. See Visibility.
A long-running transaction delays VACUUM, so dead heap rows and dead TIN index entries remain until that transaction ends.
Autovacuum settings
By default Postgres does not vacuum a table until 20% of its rows have changed (autovacuum_vacuum_scale_factor = 0.2, plus autovacuum_vacuum_threshold = 50 rows). On a large table that is millions of dead rows, and a TIN index over that table carries them the whole time. For tables you search with TIN, and especially when count(*) is part of the workload, lower the trigger for that table:
autovacuum_vacuum_scale_factor can also be changed under Parameters.
Postgres settings TIN uses
TIN has few settings of its own. Most of its behavior follows ordinary Postgres settings, which you can change under Parameters or, for the session-level ones, withSET.