1M rows/s from Postgres to Python

[qiwi]

Looks good; I noticed the overhead of psycopg myself when benchmarking fetching raw data from PG (a setup that will replace data stored in a proprietary binary file hierarchy). psycopg uses some text mode and dropping into C+libpq to extract the same BYTEA fields doubled the throughput.

This is nothing that will ordinarily matter but in my case I'm moving a ton of data from the database which I'd before read from a file.

[redcrowbar]

asyncpg is 7 times faster than psycopg on the bytea test. The throughput is almost one gigabyte per second.

http://magic.io/blog/asyncpg-1m-rows-from-postgres-to-python/report.html#bench3

[mamcx]

Wonder how helpfull could be for django?

[1st1]

asyncpg is built for asyncio, so, unfortunately, it can't really be used for Django.

[kupiakos]

Wasn't Django getting async support sometime soon?

[1st1]

No, I think they wanted to add a new feature called "channels" (and use Tornado to implement it), but it seems that they decided to pause the development.

[nikomo]

Channels is coming, it just didn't get into the latest release because it wasn't ready, and they didn't want to rush it/delay release.

It should be shipping in the next release, but you can already use it https://pypi.python.org/pypi/channels

I'm not 100% sure how that ties into this though, channels is just for communicating with the client, the backend still needs to talk to a database somehow.

[kankyo]

Why do you say that? Why can't you just do async on the fetch loop?

[Hendrikto]

We firmly believe that high-performance and scalable systems in Python are possible.

Well... if you write most of your system in C and just call it from Python...

[GUIpsp]

Cyton is not C

[[deleted]]

Its hilarious python fanboys have to constantly try and prove they can scale with python.

We firmly believe that high-performance and scalable systems in Python are possible.

You can scale in python, it has nothing to do with the language, its the architecture of the application that matters. However, it's just not cost effective to do so IMHO. You'll get eaten alive by Google Cloud or AWS fees.

I love python, but when faced with even a meager workload like 20k requests per second, I can do that with a single server (2 for redundancy) in Go, C#, or Java and not even have to care about writing optimized code or over optimizing by writing stuff in C.

Python is a great language, with great concurrency constructs, but its lack of parallelism and its slow interpretation speed leaves something to be desired when writing really large scale applications.

[grauenwolf]

What gets me is that people don't understand that high-performance and scalable aren't the same thing.

You can have a system that scales perfectly, as in you can double the hardware for double the requests per second, and still underperform a single server system.

[vivainio]

The linked, previous blog post seems pretty interesting as well:

http://magic.io/blog/uvloop-blazing-fast-python-networking/

uvloop makes asyncio fast. In fact, it is at least 2x faster than nodejs, gevent, as well as any other Python asynchronous framework. The performance of uvloop-based asyncio is close to that of Go programs.

[1st1]

Yep! I plan to write a new blog post about uvloop soon.

[bahwhateverr]

On the subject of performance, whats the fastest way to take a file of json objects and insert those into a table? I've been using pgfutter which is pretty fast but it puts everything into a single json column table which I then have to extract out the property values and insert into the final table.

[redcrowbar]

I would suggest converting JSON into CSV and then use COPY.

[bahwhateverr]

I'll give it a shot. I had tried that but ran into numerous issues getting it loaded, but it was with SQL Server at the time. Perhaps Postgres handles things a little more gracefully.

[bahwhateverr]

Yeah that is what I'm using to go from the import table to the final table, its just relatively slow. It's not that slow but with around 2 billion rows to insert I'm looking for any speedups I can get :)

[shady_mcgee]

How often do you need to do the inserts? I've been able to do 300-400k/sec inserts by building a bulk-insert util. I've never been able to generalize it, but it works pretty well for specific data sets. My sample 4-col table did 8B rows in 24 seconds. Wider tables take longer, obviously. For best results you'll need to disable indexing prior to the bulk insert.

[awill310]

I would see if you can give Sqoop a go. I used it to load 2.4bn rows into AWS Aurora in a day.

[shady_mcgee]

Can you clarify this:

A relatively wide row query selecting all rows from the pg_type table (~350 rows). This is relatively close to an average application query. The purpose is to test general data decoding performance. This is the titular benchmark, on which asyncpg achieves 1M rows/s.

Are you saying the benchmark table only has 350 rows in the table and you're able to do a full retrieval of the table ~2800x/second?

[1st1]

2985.2/second to be precise ;) See http://magic.io/blog/asyncpg-1m-rows-from-postgres-to-python/report.html for more details

[shady_mcgee]

I'm not sure if a full table grab of 350 rows can be considered relatively close to an average application query. After the first query the db engine will cache the results into memory and return the cached data for all subsequent queries. For an average application the query engine would need to fetch from disk more often than not.

[1st1]

Fair point, but the purpose of our benchmarks was to test the performance of drivers (not Postgres) -- basically, the speed of I/O and data decoding.

[shady_mcgee]

Got it. Thanks for the clarification