Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Show HN: 1M rows/s from Postgres to Python

magic.io

Post reply on HN
Print view View original

61–70 of 94 posts

Re: Show HN: 1M rows/s from Postgres to Python

#61

Post by also_on_sunday »

spriggan3 wrote: post #22

Why is node pg so slow relative to go and asyncpg drivers ? I'm sure there is room for improvement here.

Primary author of node-postgres (both the JavaScript & libpq versions) here. Very good question. I'd say part of the relative speed is because it's been a mostly 1 person (me!) project for a loooong time & I haven't had a lot of time to do perf analysis to find where exactly to make it faster. I would absolutely love some help! My time for open source is pretty limited, and I try to spend most of it focused on triaging issues. If you'd like to help out please don't hesitate to open an issue, a pull request, or send me a gchat (contact info is in node-postgres readme) and we can discuss!

Re: Show HN: 1M rows/s from Postgres to Python

#62

Post by RedCrowbar »

zzzeek wrote: post #59
RedCrowbar wrote: post #55

Earlier quoted context omitted.

> Everytime I talk about my blog post, I am super careful to qualify: in this case, "usually has little to do w/ speed" and other qualifications. The blog post as well. I created an asyncpg suite for your bigdata benchmark ( https://bitbucket.org/zzzeek/bigdata ). Here are the results obtained from running it on my laptop against local PostgreSQL 9.5. 300 connections: Python3.5.2 threads -- avg 9798.16 recs/sec Pytho…

that's great, you've written a very fast driver. My benchmarks are comparing psycopg2 to itself , under threads / gevent vs. asyncio, to show that asyncio is inherently slower. It follows that if you wrote your driver and all of its protocol improvements into a traditional blocking model, it would also be much faster. You've written something so fast that it overcomes the latency of asyncio (this whole thread is a hu…

> that's great, you've written a very fast driver. My benchmarks are comparing psycopg2 to itself,

OK, I ran the asyncio suite (with uvloop event loop):

Python3.5.2 asyncio+psycopg+uvloop -- avg 8356.84 recs/sec

Really not that slower. There is also an important detail that you forget to mention: the threaded benchmark uses notably more CPU time (about 30% more).

Re: Show HN: 1M rows/s from Postgres to Python

#63

Post by rickycook »

zzzeek wrote: post #48
RedCrowbar wrote: post #43

Earlier quoted context omitted.

> the 1M row/sec part is easy to explain: Prepared statements were used in all benchmarks where supported. Please don't dismiss the results so easily. The source of performance increases here is not the use of prepared statements, but efficient implementation of the binary type I/O and the protocol itself. > I'd be very wary of a new PG driver especially one that implements the wire protocol itself. > pg8000 does thi…

I have no intention to stifle anything, I'm only raising the point that this seems like a really new driver and it's likely that it would take a while before it approaches the stability of psycopg2. That is, the stability of psycogp2 is not to be so lightly thrown in the trash. Does your driver seamlessly handle reading and writing of three-dimensional arrays of JSONB structures, and arrays of custom enumerated types…

and it's just as frustrating to those of us that do use asyncio (because it really is excellent) when someone needlessly ties it to not use async. it's harder to maintain something that does both

Re: Show HN: 1M rows/s from Postgres to Python

#64

Post by 1st1 »

allendoerfer wrote: post #60
1st1 wrote: post #58

Earlier quoted context omitted.

A lot of code in asyncpg is datatypes parsers, buffer abstractions, API facade and other IO independent things. So good news is that it would be possible to reuse a lot of code. Protocol is implemented in two layers -- the base layer is IO and framework independent. The higher level is designed for asyncio. Long story short, it's possible to add a synchronous version, but it still will require a lot of work. The API…

I think you should open a bounty for it. As questions like "Can I use this with Django/SQLAlchemy/…?" showed, people love drop-in replacements that speed up their code, especially when it is at a low level like this. Do not want to sound ungrateful, though. Thank you for your work also on you other contributions. Would definitely use this when applicable. Are more performance related projects like this and uvloop pla…

> I think you should open a bounty for it. As questions like "Can I use this with Django/SQLAlchemy/…?" showed, people love drop-in replacements that speed up their code, especially when it is at a low level like this.

We'll consider doing that. We have to keep in mind that supporting two IO modes will add a lot of maintenance overhead. Anyways, we can certainly continue the discussion on GH, and maybe we can find a champion to do the heavy-lifting.

> Do not want to sound ungrateful, though. Thank you for your work also on you other contributions. Would definitely use this when applicable. Are more performance related projects like this and uvloop planned?

Thank you.

Right now most of our resources are consumed by getting EdgeDB alpha out as soon as possible. We also have plans to create a micro web framework based on httptools and few other exciting ideas. Stay tuned :)

Also check out PEP 525 -- another cool thing we're trying to make happen in Python 3.6.

Re: Show HN: 1M rows/s from Postgres to Python

#65

Post by zzzeek »

1st1 wrote: post #58
allendoerfer wrote: post #56

Earlier quoted context omitted.

Sorry to interrupt: You two can click on the timestamp to answer your posts. Please continue, we are listening carefully. And since I am already here @1st1: How hard would it be to create a synchronous version of your adapter?

A lot of code in asyncpg is datatypes parsers, buffer abstractions, API facade and other IO independent things. So good news is that it would be possible to reuse a lot of code. Protocol is implemented in two layers -- the base layer is IO and framework independent. The higher level is designed for asyncio. Long story short, it's possible to add a synchronous version, but it still will require a lot of work. The API…

It's possible to make a really nice async sqlalchemy core too, and even some of the ORM. huge amount of work, i started on just the connection pool. Would be nice but require all its own test coverage and everything. If i was the CEO of sqlalchemy inc, it would have been done. But that's not where my salary comes from :)

Re: Show HN: 1M rows/s from Postgres to Python

#66

Post by RedCrowbar »

zzzeek wrote: post #48
RedCrowbar wrote: post #43

Earlier quoted context omitted.

> the 1M row/sec part is easy to explain: Prepared statements were used in all benchmarks where supported. Please don't dismiss the results so easily. The source of performance increases here is not the use of prepared statements, but efficient implementation of the binary type I/O and the protocol itself. > I'd be very wary of a new PG driver especially one that implements the wire protocol itself. > pg8000 does thi…

I have no intention to stifle anything, I'm only raising the point that this seems like a really new driver and it's likely that it would take a while before it approaches the stability of psycopg2. That is, the stability of psycogp2 is not to be so lightly thrown in the trash. Does your driver seamlessly handle reading and writing of three-dimensional arrays of JSONB structures, and arrays of custom enumerated types…

> I guess I am frustrated that you folks put all this work into a great driver and then locked it in the largely useless dungeon of asyncio, where all the libraries are totally useless to those of us who don't think node.js is the programming model for all cases

I see your point. Nothing is really "locked away". The protocol parser and data codecs in asyncpg are independent from the I/O model and loop semantics. With effort, it's possible to build a sync interface on top. It's just not a priority, as our cases are async.

Re: Show HN: 1M rows/s from Postgres to Python

#67

Post by allendoerfer »

zzzeek wrote: post #65
1st1 wrote: post #58

Earlier quoted context omitted.

A lot of code in asyncpg is datatypes parsers, buffer abstractions, API facade and other IO independent things. So good news is that it would be possible to reuse a lot of code. Protocol is implemented in two layers -- the base layer is IO and framework independent. The higher level is designed for asyncio. Long story short, it's possible to add a synchronous version, but it still will require a lot of work. The API…

It's possible to make a really nice async sqlalchemy core too, and even some of the ORM. huge amount of work, i started on just the connection pool. Would be nice but require all its own test coverage and everything. If i was the CEO of sqlalchemy inc, it would have been done. But that's not where my salary comes from :)

SQLAlchemy is clearly one of the best ORMs (maybe there is something in Java-land that I don't know). It is beyond me how you are not guilted by cash getting thrown in your direction into making this your job.

Well, I like what bountysource.com and others are doing, maybe we will get there.

Post reply on HN