4 ms·

> I insert about 150,000 rows a second That's amazing - I would love to know if you have done careful data modeling, indexing, etc that allows you to get to t

> I insert about 150,000 rows a second That's amazing - I would love to know if you have done careful data modeling, indexing, etc that allows you to get to this and what kind of data is being insert ed?

Eikon 2y ago
I am not optimizing too much around insertion speed. I avoid GIN, GIST and hash indexes. The schema is nicely normalized. I had troubles with hash indexes requiring hundreds of gigabytes of memory to rebuild. Postgres B-Trees are painless and (very) fast. Eg. querying one table by id (redacted): EXPLAIN ANALYZE SELECT * FROM table_name WHERE id = [ID_VALUE]; Index Scan using table_name_pkey on table_name (cost=0.71..2.93 rows=1 width=32) (actual time=0.042..0.042 rows=0 loops=1) Index Cond: (id = '[ID_VALUE]'::bigint) Planning Time: 0.056 ms Execution Time: 0.052 ms Here’s a zpool iostat 1 # zpool iostat 1 operations bandwidth read write read write ----- ----- ----- ----- 148K 183K 1.23G 2.43G 151K 180K 1.25G 2.36G 151K 177K 1.25G 2.33G 148K 153K 1.23G 2.13G
- shayonj 2y ago
  thank you for the response and stats! very cool
- chatmasta 2y ago
  Have you measured _transactions_ per second? For both “insert only” and “mixed insert/update/delete?” This might be a more telling metric in terms of the stress it puts on the system. There’s a big difference between 1 transaction with 150k inserts, and 150k transactions with 1 insert each – especially when the workload shifts to include updates in the middle of those. If you’re doing insert only, you might benefit from copying directly to disk rather than going through the typical query interface.
dist1ll 2y ago
150k rows/second is pretty ordinary if you do batching, especially for a machine of that size. I was able to get 250k+ row inserts on a 16vCPU on a table with 16 columns, a third of which are strings >30 bytes. Pretty sure you can push that much higher with COPY.
- shayonj 2y ago
  that's true, you can get very far with COPY

Eikon 2y ago

I am not optimizing too much around insertion speed. I avoid GIN, GIST and hash indexes. The schema is nicely normalized. I had troubles with hash indexes requiring hundreds of gigabytes of memory to rebuild. Postgres B-Trees are painless and (very) fast. Eg. querying one table by id (redacted): EXPLAIN ANALYZE SELECT * FROM table_name WHERE id = [ID_VALUE]; Index Scan using table_name_pkey on table_name (cost=0.71..2.93 rows=1 width=32) (actual time=0.042..0.042 rows=0 loops=1) Index Cond: (id = '[ID_VALUE]'::bigint) Planning Time: 0.056 ms Execution Time: 0.052 ms Here’s a zpool iostat 1 # zpool iostat 1 operations bandwidth read write read write ----- ----- ----- ----- 148K 183K 1.23G 2.43G 151K 180K 1.25G 2.36G 151K 177K 1.25G 2.33G 148K 153K 1.23G 2.13G

shayonj 2y ago
thank you for the response and stats! very cool
chatmasta 2y ago
Have you measured _transactions_ per second? For both “insert only” and “mixed insert/update/delete?” This might be a more telling metric in terms of the stress it puts on the system. There’s a big difference between 1 transaction with 150k inserts, and 150k transactions with 1 insert each – especially when the workload shifts to include updates in the middle of those. If you’re doing insert only, you might benefit from copying directly to disk rather than going through the typical query interface.

dist1ll 2y ago

150k rows/second is pretty ordinary if you do batching, especially for a machine of that size. I was able to get 250k+ row inserts on a 16vCPU on a table with 16 columns, a third of which are strings >30 bytes. Pretty sure you can push that much higher with COPY.

shayonj 2y ago
that's true, you can get very far with COPY