Architecture and operating model
B-tree is the general-purpose choice for ordered comparisons, equality, ranges, ORDER BY and supported prefix patterns. GIN inverts multiple keys from one row for array, JSONB and full-text membership. BRIN stores summaries for physical block ranges and can eliminate broad regions with a tiny index on very large, well-correlated data such as time series.
Do not choose from SELECT latency alone. Every index adds INSERT, UPDATE, DELETE, WAL, cache and vacuum cost. A GIN pending list and fastupdate can move write cost into maintenance bursts. BRIN stays small but rechecks many heap pages when correlation is weak. B-tree is precise but can create substantial storage and cache pressure on a huge table.
Leading columns determine effective use of a multicolumn B-tree. GIN behaves differently across multiple key fields; BRIN pages_per_range trades resolution for size. Use EXPLAIN (ANALYZE, BUFFERS, WAL) to measure the plan, row estimates, heap blocks and write impact together.
- 1Inventory queries and operators
- 2Measure distribution and correlation
- 3Build candidate and controlled EXPLAIN
- 4Validate writes, WAL and cache
Design parameters
- Query operator
- Classify real SQL as equality/range/order, membership/containment or physical range elimination.
- Correlation
- Measure value-to-physical-order correlation before choosing BRIN.
- Write rate
- Measure index count, GIN pending behavior and WAL under peak ingest.
- Cache budget
- Evaluate index size against shared buffers and OS cache.
Platform implementation
Service names are reference points. Scope, defaults, region availability and operating requirements differ; they are not interchangeable guarantees.
| Criterion | B-tree | GIN | BRIN |
|---|---|---|---|
| Typical use | Equality, range, ORDER BY | Array/JSONB/full-text membership | Ranges on very large correlated tables |
| Size | Medium to high | High with dense keys | Very small |
| Write cost | Balanced-tree maintenance per row | Pending list and key count matter | Low summary-range maintenance |
| Poor-fit signal | Many heap visits at low selectivity | Non-containment query or large pending list | High recheck and weak correlation |
Worked example
On a 2 TB event table where created_at has 0.98 physical correlation, 128-page BRIN ranges can narrow a recent-time filter with a small index. Compare BRIN and B-tree on the same snapshot using buffers and p95 for a 15-minute query, not index size alone.
GIN is a candidate for JSONB tag membership. At 5,000 inserts/s, compare fastupdate on and off using p95 insert, WAL MB/s, pending-list cleanup and query p95. Reject a read improvement that breaks the ingest SLA.
Example commands: replace lab values and confirm permissions and software versions before use.
SELECT attname, n_distinct, correlation FROM pg_stats WHERE schemaname='public' AND tablename='events';
EXPLAIN (ANALYZE, BUFFERS, WAL) SELECT * FROM events WHERE created_at >= now()-interval '15 min';
CREATE INDEX CONCURRENTLY events_created_brin ON events USING brin(created_at) WITH (pages_per_range=128);
CREATE INDEX CONCURRENTLY events_tags_gin ON events USING gin(tags jsonb_path_ops);
SELECT relname,pg_size_pretty(pg_relation_size(indexrelid)) FROM pg_stat_user_indexes ORDER BY pg_relation_size(indexrelid) DESC;Troubleshooting
| Observation | Likely cause / distinction | Verification |
|---|---|---|
| Planner chooses Seq Scan despite an index | The query may select many rows, estimates may be wrong or operator class may not match. | After ANALYZE, compare estimated and actual rows plus the operator in EXPLAIN. |
| GIN writes pause intermittently | Pending-list cleanup or high key fan-out may cause latency. | Correlate `pgstatginindex`, checkpoints/WAL and p95 ingest. |
| BRIN rechecks many pages | Physical correlation may be weak or pages_per_range too broad. | Measure pg_stats correlation, Rows Removed by Index Recheck and heap blocks. |
Acceptance checks
- Classify highest-cost queries by operator type.
- Measure selectivity and physical correlation.
- Project candidate index size at production scale.
- Record real heap access with EXPLAIN ANALYZE BUFFERS.
- Measure p95 write latency and WAL MB/s at peak.
- Observe unused or duplicate indexes for a full workload cycle before removal.
Related concepts
Indexes and execution plans
An index can reduce scanned data for suitable queries, but adds write, maintenance and storage costs. Evaluate column order, selectivity, filters and sorting together. Slow queries are not always caused by missing indexes: estimates, locks, storage latency or excessive data transfer may dominate. Inspect representative execution plans and actual row counts. Measure read improvement alongside write overhead, and avoid uncontrolled production maintenance.
Latency distribution
Latency is the time between starting an operation and receiving its result. An average can hide a small number of very slow operations; medians and p95/p99 percentiles answer different questions. Network RTT, storage waits, processor queues and application processing contribute to end-to-end time. State whether measurements come from the client or server. Check whether increases coincide with traffic growth, maintenance or capacity limits. Record normal and peak-hour baselines before selecting an alert threshold.
Capacity and usable headroom
Raw capacity is not the capacity available to applications. RAID or erasure coding, filesystems, reserved space, metadata, snapshots and growth headroom are separate deductions. TB and TiB representations also change the displayed number. Write calculations with units, establish protected usable capacity, then subtract operating reserves. Track growth rate as well as current utilization. The projected exhaustion date should leave enough time to procure and deploy additional capacity.
Bandwidth and useful throughput
Link capacity differs from useful application throughput. Protocol headers, encryption, retransmissions, small files and storage waits reduce net transfer speed. Keep bits and bytes distinct: 1 Gbit/s corresponds to a theoretical 125 MB/s, not an application performance guarantee. Estimate transfer time as data size divided by measured useful throughput. Observe the network, source reads and destination writes together to locate the bottleneck. Consider temporary slowdowns and competing workloads as well as average speed.
Telemetry and time correlation
Telemetry combines logs, metrics and events that explain system behavior. A log describes an event, a metric shows behavior over time, and a distributed trace follows a request across components. Clock differences can make one event appear to occur at several times. Use synchronized clocks, reliable source identifiers and consistent time-zone handling. Alarm design should consider duration and user impact alongside thresholds. Monitor gaps in collection separately: absence of logs must not be interpreted as absence of incidents.
Transaction boundary
A transaction groups operations that an application treats as jointly successful or failed. Database atomicity does not automatically include email delivery or changes in other services. Define the commit point and retry behavior. Evaluate idempotency to prevent processing the same request twice. Test connection loss before and after commit separately: a client-side error does not always mean the transaction failed.
Primary documentation
Prepared by the Doz Teknoloji technical team using the primary references below. Calculations and lab scenarios state their assumptions; validate the applicable product version before rollout.