Extract a csv of heroku table - postgresql table size larger than database?

Question

I have run

heroku pg:psql -a appname -c "\copy (SELECT * FROM listings) TO listings.csv CSV DELIMITER ',' HEADER"

expecting a dump of the listings table.

Instead, the file keeps growing and growing, and is (somewhat illogically) larger than the entire app database obtained through

heroku pg:backups:capture
heroku pg:backups:download

Which is confusing.

I am trying to simply make a csv copy of a table from a rails app in heroku.

I never really paid much attention to disk usage, but after a short research I believe PostgreSQL compresses its data: see postgresql.org/docs/12/storage-toast.html — Jim Jones
– Jim Jones, Commented Feb 26, 2020 at 7:31
@JimJones Looks like you're spot on. When the data finally extacted, it was ~14gb (but less than 1 inside the db) — stevec
– stevec, Commented Feb 26, 2020 at 7:33
I recall also being surprised after importing XML data into the database - it also got much smaller - but I never compared plain text imports +1 — Jim Jones
– Jim Jones, Commented Feb 26, 2020 at 7:35

Jim Jones · Accepted Answer · 2020-02-26 07:41:58Z

1

According to the TOAST documentation, PostgreSQL applies some compression techniques to the data storage:

The compression technique used for either in-line or out-of-line compressed data is a fairly simple and very fast member of the LZ family of compression techniques. See src/common/pg_lzcompress.c for the details.

answered Feb 26, 2020 at 7:41

Jim Jones

19.9k3 gold badges44 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Extract a csv of heroku table - postgresql table size larger than database?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related