-1

I was wondering if I can get your opinion on the performance of a simple data pipeline. it's all written in python, using pandas, and it essentially allows me to read all kinds of formats (parquet, csv, json, excel, api) and write these into a staging area on pgsql (version 14, localhost).

the data itself is nothing special (new york taxi dataset if you're familiar with it), but each file contains around 3 million rows across 20 columns, mainly "short" values.

the table I'm creating is in a brand new schema, the table is unlogged, there are no indexes etc. and all data types are text, regardless of what data is held. more than that, pgsql is optimised as follows:

max_connections = 1000
shared_buffers = 2GB
effective_cache_size = 6GB
maintenance_work_mem = 1GB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 500
random_page_cost = 1.1
work_mem = 524kB
huge_pages = off
min_wal_size = 4GB
max_wal_size = 16GB
max_worker_processes = 4
max_parallel_workers_per_gather = 2
max_parallel_workers = 4
max_parallel_maintenance_workers = 2

now I'm not sure I've got unrealistic expectations, but I'm averaging 50000 records per second, which seems a bit low. I was hoping to ingest up to 5 million rows in less than 10 seconds. is there anything obvious I'm missing?

before using the copy statement, I'm generating an iterator from the dataframe, with chunks of that going into a buffer that then becomes my input.

what's an acceptable figure per second?

thanks!

8
  • You need to monitor your server performance during the load, identify the biggest bottleneck, resolve it, and repeat until you reach your performance target.
    – mustaccio
    Commented Jun 20 at 14:46
  • I get that, but there's no bottleneck here. the setup couldn't have been more permissive. table is unlogged, there are no indexes, no transformations, no internet lag (localhost), the performance settings are as per pgtune for a data warehouse, the driver is native, the server is brand new etc. I was mainly looking for some opinions around the current result. I might be trying to optimise something that's already extremely efficient. is 50k rows per second a wow result or is it very poor?
    – Chris
    Commented Jun 20 at 15:11
  • 3
    There's always a bottleneck -- you just need to look for it.
    – mustaccio
    Commented Jun 20 at 15:54
  • I agree and I'm willing to look for it, but before I do so, I would just like to know if my expectations are realistic. imagine no bottleneck and maximum performance is 50k rows/second, I'm already there, so I'm trying to fix something that's not fixable.
    – Chris
    Commented Jun 20 at 16:55
  • 2
    Again, your approach is wrong. If there were no bottleneck, the ingestion rate would be infinite. You can only know if your expectations are realistic when you identify the bottleneck and understand the cost of resolving it.
    – mustaccio
    Commented Jun 20 at 17:14

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.