How a table is stored on disk
Should invoices store their PDFs?
The idea in 30 seconds
PostgreSQL can store files in a bytea column, and TOAST keeps them out of the way of ordinary queries. The costs show up elsewhere: every byte goes through the write-ahead log, every backup and every replica, and a careless SELECT * drags megabytes to the application. Weigh that against the one real advantage, files that change together with their rows in one transaction.
Helps to know first: Relational database concepts
On this page
The situation
Customers download their invoices as PDFs. Today the PDFs are generated on the fly. An AI assistant opened a pull request to store them.
Review taskStore invoice PDFs in the database
Pull request: “Store each invoice’s PDF in the invoices table so everything is in one place and backed up together.”
ALTER TABLE invoices ADD COLUMN pdf bytea;# invoices.py
def list_invoices(customer_id):
return db.query("SELECT * FROM invoices WHERE customer_id = %s ORDER BY issued_on DESC",
[customer_id])Would you approve it? Write the review you would leave.
Your call
Model review
Request changes, mostly to how it is used. Storing files in PostgreSQL is a legitimate choice, but this version pays the costs without noticing them.
We tested it with 2,000 invoices and a 200 KB PDF each, about 400 MB of files.
1. SELECT * now ships every PDF to the invoice list. The list page shows numbers and dates, but SELECT * fetches the files too. For one customer’s 10 invoices:
| Query | Time | Sent to the application | Pages read |
|---|---|---|---|
SELECT * | about 2 ms | 4 MB | 324 |
SELECT id, number, issued_on | about 0.2 ms | 1 kB | 12 |
EXPLAIN (ANALYZE, BUFFERS, SERIALIZE) shows the difference; plain EXPLAIN ANALYZE does not, because it never fetches the large values. Select the columns you need, and load the PDF only on the download endpoint.
2. Every byte is written more than once. Inserting the 400 MB of PDFs wrote 419 MB of write-ahead log. That log is also what feeds replicas and point-in-time backups, so each PDF is copied to every replica and kept in every backup and archive, for as long as you keep them.
3. The database grows with your files, not your data. The invoices’ real data fit in 144 kB; with PDFs the table was 407 MB. Restores, replica rebuilds and upgrades all take longer in proportion.
What is genuinely good: a PDF and its invoice row change in the same transaction, so there are no orphaned files and no invoices pointing at missing ones. And TOAST does its job: summarising all 2,000 invoices by month touched only the 144 kB main table and took under half a millisecond.
What I would ask for: if invoices stay small in number and size, keep the column, but never SELECT * from this table, and check your backup and replica capacity. If PDFs will grow into the gigabytes, store them in object storage under a key such as invoices/{id}.pdf, keep the key in the row, and write the file before committing the row.
Compare it with yours. Did your review catch the main problems?
How to decide
Prefer object storage, with the key in a column, when files are large, numerous or growing without limit, or are served straight to browsers.
Prefer a bytea column when files are small and few, must change in the same transaction as their row, and you want one backup that covers everything.
Either way, never SELECT * from a table with large columns in code that does not need them.
Check yourself
Why did summarising all invoices stay fast even with 400 MB of PDFs in the table?
Show answer
The PDFs were moved to the TOAST table. The main table held only the small columns and pointers, 144 kB, and a query that does not read
pdfnever touches the TOAST table.Why does
EXPLAIN ANALYZEalone hide the cost ofSELECT *here?Show answer
Without
SERIALIZE,EXPLAINdoes not convert rows for the client, so it never fetches the out-of-line PDF values.EXPLAIN (ANALYZE, SERIALIZE)includes that work.Where else does a PDF stored in the database get copied?
Show answer
Into the write-ahead log, and from there to every replica, every point-in-time backup archive, and every base backup.
Finished reading?
Saved in this browser only.
Sources
Where each claim on this page comes from.
- 1.PostgreSQL documentation: 66.2 TOASTPrimary· §66.2 TOAST
Large values are moved out of line into a TOAST table; an update that does not change them leaves them as they are.
- 2.PostgreSQL documentation: EXPLAINPrimary· Parameters: SERIALIZE
EXPLAIN SERIALIZE measures converting output for the client, including fetching TOASTed values from out-of-line storage.
- 3.PostgreSQL documentation: 28.3 Write-Ahead Logging (WAL)Primary· §28.3 Write-Ahead Logging (WAL)
Changes are written to the write-ahead log before the data files.
- 4.PostgreSQL documentation: 25.3 Continuous Archiving and Point-in-Time RecoveryPrimary· §25.3
Point-in-time recovery combines a base backup with archived WAL files.
- 5.PostgreSQL documentation: 26.2 Log-Shipping Standby ServersPrimary· §26.2.5 Streaming Replication
Streaming replication sends WAL records from the primary to standbys as they are generated.
Go further
What to read next, and why it is worth the time.
- 1.PostgreSQL documentation: 66.2 TOASTPrimary· §66.2
Explains the four storage strategies and when PostgreSQL compresses instead of moving values out of line.
Applies to PostgreSQL 18. Draft: not yet fact-checked end to end.