Database performance is the efficiency and responsiveness of PostgreSQL under a workload. It is not a property of hardware alone: SQL shape, indexes, configuration, connection behavior, transaction duration, autovacuum, and operational workload all determine whether available resources produce useful work or delay. section 6.0 section 6.13
A common self-inflicted cost is moving work out of the database. Fetching columns that will be discarded, retrieving whole tables, and filtering or sorting in application code transfers and marshals unnecessary data, bypasses useful predicates and indexes, and adds database, application, and network overhead. Select only needed columns and let PostgreSQL retrieve and filter the relevant rows. section 2.9
Measure projection cost
For tickets with status 10, compare SELECT * with SELECT id when the application needs only IDs. The recorded run selects every column in about 0.245 seconds and only id in about 0.093 seconds; a full-table fetch followed by application-side filtering takes about 6 minutes 42 seconds. The exact numbers are workload-specific, but the contrast exposes the cost of unnecessary transfer and processing. section 2.9
$ python3 fetch.py "SELECT *" took 0:00:00.244571 seconds. "SELECT id" took 0:00:00.093342 seconds.
This timing output makes the projection comparison concrete: selecting only the required indexed value completes substantially sooner than fetching every column. section 2.9
Performance also declines when system behavior is mismatched to the workload. Conservative defaults can leave resources unused, while incorrect memory calculations, excessive concurrency, and idle sessions add overhead rather than throughput. Long-running or idle-in-transaction sessions can block other work and postpone autovacuum; weakening autovacuum eventually invites bloat, inaccurate optimizer statistics, and anti-wraparound pressure. section 6.13
Diagnose before changing settings or buying hardware. Log slow statements with a threshold such as log_min_duration_statement = 100ms, then inspect query patterns and workload signals. Frequent checkpoint messages can indicate excessive disk flushing; increasing max_wal_size may help, but the disk-write spike or transaction rate that caused the symptom also needs investigation. Use pg_stat_activity and pg_stat_statements alongside logs to trace issues to application code, database design, configuration, or ingestion. section 7.3.2 section 11.3.3
Practical performance habits include:
- Minimize selected and fetched data; keep filtering and sorting in SQL when retrieving data. section 2.9
- Tune configuration and memory for actual query types, concurrency, and host limits rather than relying on defaults. section 6.13
- Avoid unnecessary sessions and use a connection pooler such as PgBouncer in Transaction Mode when idle connections cannot be avoided. section 6.13
- Keep transactions short and preserve effective autovacuum to avoid blocking, bloat, stale statistics, and severe maintenance failures. section 6.13
- Set measurable improvement goals and make repairs incrementally after assessing schema, data, configuration, logs, and workload. section 11.3.3
Responsive, stable operation comes from combining query optimization, effective indexing, appropriate configuration, proactive measurement, and preventative maintenance. Treat observed latency as evidence to investigate, not as proof that PostgreSQL itself is slow. section 11.4 section 2.9
