4 ms·
Two remarks with postgres and lots of data: 1) I always wonder of there is a better way than COPY. I tend to quickly get 100% CPU without saturating I/O 2) T
by hyperman1 2y ago
Two remarks with postgres and lots of data:
1) I always wonder of there is a better way than COPY. I tend to quickly get 100% CPU without saturating I/O
2) The row overhead seems big. A row has 23 bytes overhead, this table has 48 bytes data per row, so even without page overhead, we lose ~1/3 of our storage. This is pure data storage, without any index.
- PolarizedPoutin 2y ago1. Yeah to me it seems very hard to saturate I/O with Postgres unless maybe you insert into an unlogged table. I guess there's quite a bit of overhead to get all the nice stability/consistency and crash-resistance. 2. That is a good point. I'm hoping TimescaleDB's compression helps here but yeah I don't think you'll ever get the database size below the data's original footprint.
- feike 2y agoTimescaler here, if you configure the timescaledb.compress_segmentby well, and the data suits the compression, you can achieve 20x or more compression. (On some metrics data internally, I have 98% reduction in size of the data). One of the reasons this works is due to only having to pay the per-tuple overhead once per grouped row, which could be as much as a 1000 rows. The other is the compression algorithm, which can be TimescaleDB or plain PostgreSQL TOAST https://www.timescale.com/blog/time-series-compression-algorithms-explained/ https://www.timescale.com/blog/time-series-compression-algor... https://www.postgresql.org/docs/current/storage-toast.html https://www.postgresql.org/docs/current/storage-toast.html
- hyperman1 2y agoIf I look into perf, it seems mostly parsing overhead. I can saturate a create newtable as select from oldtable. Unfortunately, CSV seems still the lingua franca for transport between DBs. Maybe some day a more binary oriented transport protocol will appear( e.g parquet?)
- feike 2y agoMany libraries for python, Rust, golang support COPY BINARY. The times I've tested it, the improvement is very small as compared to plain copy, or copy with CSV, whereas it does require more work and thought upfront to ensure the binary actually works correctly. https://www.postgresql.org/docs/current/sql-copy.html https://www.postgresql.org/docs/current/sql-copy.html