5 ms·

Pgloader: A High-speed PostgreSQL Swiss Army Knife, Written in Lisp

179 points by wtbob 12y ago

Chmouel 12y ago
cool! awesome video as well i recommend watching it,
dim 12y ago
Here's another link to the video while the all static website seems to have some sort of an issue: http://medias.ircam.fr/stream/ext/video/files/2014/05/13/ELSAA_6_mai2.mov.webm#t=57,357 http://medias.ircam.fr/stream/ext/video/files/2014/05/13/ELS... Also see the github page for the project at https://github.com/dimitri/pgloader https://github.com/dimitri/pgloader the main docs at https://github.com/dimitri/pgloader/blob/master/pgloader.1.md https://github.com/dimitri/pgloader/blob/master/pgloader.1.m... Check out the USAGE section (it's a quickstart really) at https://github.com/dimitri/pgloader/blob/master/pgloader.1.md#usage-examples https://github.com/dimitri/pgloader/blob/master/pgloader.1.m...
craigkerstiens 12y ago
Here's a link to the github repo for the project as well - https://github.com/dimitri/pgloader https://github.com/dimitri/pgloader
dim 12y ago
BTW if you're going to FOSDEM check out http://www.postgresql.eu/events/schedule/fosdem2015/session/827-migrating-to-postgresql-the-new-story/ http://www.postgresql.eu/events/schedule/fosdem2015/session/... where I will host a full talk about pgloader ;-)
- ubercow 12y ago
  Any idea if the talk will be recorded? I'd love to listen to a full talk but some space and time restrictions prevent me from attending in person.
  - dim 12y ago
    I'm told by one of the FOSDEM PGDAY conference organizers: "I believe they won't be". Sorry about that. The slides are going to be available on http://tapoueh.org/ http://tapoueh.org/ and http://pgloader.io/ http://pgloader.io/ though.
dim 12y ago
So a new mirror is up now, enjoy! http://dimitri.github.io/pgloader/ http://dimitri.github.io/pgloader/
dim 12y ago
So the Quick Start is now back available at http://dimitri.github.io/pgloader/howto/quickstart.html http://dimitri.github.io/pgloader/howto/quickstart.html, and I made a release with files hosted at github for your testing pleasure: https://github.com/dimitri/pgloader/releases/tag/v3.2.1.preview https://github.com/dimitri/pgloader/releases/tag/v3.2.1.prev... You have self-contained binary files allowing you to play with /usr/bin/pgloader (or ./pgloader) without having to know anything about Common Lisp. Enjoy ;-)
bronson 12y ago
Wow, switching from Python to Lisp produced a shocking speedup. I liked Lisp in college but I probably never would have considered it nowadays... If I wanted a quick runtime, I probably would have fallen for Go or Julia or, after some maturing, Rust. Lisp! Gonna keep that one in mind.
- hga 12y ago
  Lisp has a big historical advantage coming from it being the 2nd oldest surviving computer language after FORTRAN, it got started in 1957 on a vacuum tube computer. While seldom as fast as e.g. FORTRAN let alone assembly (there was a period when MACLISP was faster at numerical code than DEC's PDP-10 FORTRAN, but that was at least as much due to DEC), to be fast enough on the old, slow hardware of its founding first couple of decades hackers had to figure out how to make it pretty fast on an absolute scale. E.g. they've been working on garbage collection for 55 years (!). So compared to languages like Python and its various implimentations, you shouldn't be surprised if there are Lisps out there that are faster. All that said, it's easy to quickly write a slow Lisp; a fast Lisp requires on the order of as much work as making a fast implementation of anything else. So caveat emptor ^_^. As for languages like Rust, I think they're complimentary, and that a programmer ideally should at minimum be fluent in one systems programming language like C or Rust and one high level GCed language like Lisp, Python, Go, Ruby, Julia, Lua, etc. etc. etc. (Modern JITed Java kinda splits the difference; a contrarian would say it combines the productivity of C++ and the speed of a GCed language.)
- wes-exp 12y ago
  Here's Lisp (SBCL) vs. Python 3: http://benchmarksgame.alioth.debian.org/u64/compare.php?lang=sbcl&lang2=python3 http://benchmarksgame.alioth.debian.org/u64/compare.php?lang... Summary: crushes it.
  - Erwin 12y ago
    Well, the SBCL code includes code for literally generating assembly code specific to x86-64 to get that performance and is 3 times as long as Python. Presumably the inline assembly code is not part of any LISP standard but highly specific to that specific LISP implementation.
hittaruki 12y ago
Is it possible to do streaming replication to PostgeSQL, from another database, say MYSQL? This would be the best way to do a migration without braking an existing system.
- dim 12y ago
  Well we can consider that pgloader already implements the first half of the streaming replication: the base copy to stream changes onto. Adding support for the MySQL binlog replication format is on the TODO list, if you're proficient in it I would welcome hints to implement it correctly. In terms of priority and organisation, it's Open Source so you know how it works right? Contributions are welcome, as are feature based sponsoring.
  - hittaruki 12y ago
    I would love to contribute to it, but I am not an expert on the subject. Its just something I tried to do a while back, but couldn't find a clean way. Let me see if I can figure it out and will try to give you a pull request.
    - dim 12y ago
      Awesome, thanks already for putting some time on it. You will easily find my email if you need help playing with pgloader internals and stuff.
pjmlp 12y ago
Checking the documentation I've found out it supports dBase!
SixSigma 12y ago
I've written a PostgreSQL protocol client in Limbo [1] that uses CSP / Channels ... long long ago in the time before Go-lang ... I can say that libpq is not the best example of how to write a client. It uses all text, it doesn't use the asynchronicity very well. I think there is some room in libpq for improvement, if only someone would take it on :) [1] http://plan9.bell-labs.com/sources/contrib/maht/limbo/pg/ http://plan9.bell-labs.com/sources/contrib/maht/limbo/pg/
jbverschoor 12y ago
Would love mongo import :)
- dim 12y ago
  It's on the TODO list ;-)
jaryd 12y ago
Can this be used to perform a high speed recovery from a pg_dump?

179 points by wtbob 12y ago

cool! awesome video as well i recommend watching it,

dim 12y ago

Here's another link to the video while the all static website seems to have some sort of an issue: http://medias.ircam.fr/stream/ext/video/files/2014/05/13/ELSAA_6_mai2.mov.webm#t=57,357 http://medias.ircam.fr/stream/ext/video/files/2014/05/13/ELS... Also see the github page for the project at https://github.com/dimitri/pgloader https://github.com/dimitri/pgloader the main docs at https://github.com/dimitri/pgloader/blob/master/pgloader.1.md https://github.com/dimitri/pgloader/blob/master/pgloader.1.m... Check out the USAGE section (it's a quickstart really) at https://github.com/dimitri/pgloader/blob/master/pgloader.1.md#usage-examples https://github.com/dimitri/pgloader/blob/master/pgloader.1.m...

craigkerstiens 12y ago

Here's a link to the github repo for the project as well - https://github.com/dimitri/pgloader https://github.com/dimitri/pgloader

dim 12y ago

BTW if you're going to FOSDEM check out http://www.postgresql.eu/events/schedule/fosdem2015/session/827-migrating-to-postgresql-the-new-story/ http://www.postgresql.eu/events/schedule/fosdem2015/session/... where I will host a full talk about pgloader ;-)

ubercow 12y ago
Any idea if the talk will be recorded? I'd love to listen to a full talk but some space and time restrictions prevent me from attending in person.
- dim 12y ago
  I'm told by one of the FOSDEM PGDAY conference organizers: "I believe they won't be". Sorry about that. The slides are going to be available on http://tapoueh.org/ http://tapoueh.org/ and http://pgloader.io/ http://pgloader.io/ though.

dim 12y ago

So a new mirror is up now, enjoy! http://dimitri.github.io/pgloader/ http://dimitri.github.io/pgloader/

dim 12y ago

So the Quick Start is now back available at http://dimitri.github.io/pgloader/howto/quickstart.html http://dimitri.github.io/pgloader/howto/quickstart.html, and I made a release with files hosted at github for your testing pleasure: https://github.com/dimitri/pgloader/releases/tag/v3.2.1.preview https://github.com/dimitri/pgloader/releases/tag/v3.2.1.prev... You have self-contained binary files allowing you to play with /usr/bin/pgloader (or ./pgloader) without having to know anything about Common Lisp. Enjoy ;-)

bronson 12y ago

Wow, switching from Python to Lisp produced a shocking speedup. I liked Lisp in college but I probably never would have considered it nowadays... If I wanted a quick runtime, I probably would have fallen for Go or Julia or, after some maturing, Rust. Lisp! Gonna keep that one in mind.

hga 12y ago
Lisp has a big historical advantage coming from it being the 2nd oldest surviving computer language after FORTRAN, it got started in 1957 on a vacuum tube computer. While seldom as fast as e.g. FORTRAN let alone assembly (there was a period when MACLISP was faster at numerical code than DEC's PDP-10 FORTRAN, but that was at least as much due to DEC), to be fast enough on the old, slow hardware of its founding first couple of decades hackers had to figure out how to make it pretty fast on an absolute scale. E.g. they've been working on garbage collection for 55 years (!). So compared to languages like Python and its various implimentations, you shouldn't be surprised if there are Lisps out there that are faster. All that said, it's easy to quickly write a slow Lisp; a fast Lisp requires on the order of as much work as making a fast implementation of anything else. So caveat emptor ^_^. As for languages like Rust, I think they're complimentary, and that a programmer ideally should at minimum be fluent in one systems programming language like C or Rust and one high level GCed language like Lisp, Python, Go, Ruby, Julia, Lua, etc. etc. etc. (Modern JITed Java kinda splits the difference; a contrarian would say it combines the productivity of C++ and the speed of a GCed language.)
wes-exp 12y ago
Here's Lisp (SBCL) vs. Python 3: http://benchmarksgame.alioth.debian.org/u64/compare.php?lang=sbcl&lang2=python3 http://benchmarksgame.alioth.debian.org/u64/compare.php?lang... Summary: crushes it.
- Erwin 12y ago
  Well, the SBCL code includes code for literally generating assembly code specific to x86-64 to get that performance and is 3 times as long as Python. Presumably the inline assembly code is not part of any LISP standard but highly specific to that specific LISP implementation.

hittaruki 12y ago

Is it possible to do streaming replication to PostgeSQL, from another database, say MYSQL? This would be the best way to do a migration without braking an existing system.

dim 12y ago
Well we can consider that pgloader already implements the first half of the streaming replication: the base copy to stream changes onto. Adding support for the MySQL binlog replication format is on the TODO list, if you're proficient in it I would welcome hints to implement it correctly. In terms of priority and organisation, it's Open Source so you know how it works right? Contributions are welcome, as are feature based sponsoring.
- hittaruki 12y ago
  I would love to contribute to it, but I am not an expert on the subject. Its just something I tried to do a while back, but couldn't find a clean way. Let me see if I can figure it out and will try to give you a pull request.
  - dim 12y ago
    Awesome, thanks already for putting some time on it. You will easily find my email if you need help playing with pgloader internals and stuff.

pjmlp 12y ago

Checking the documentation I've found out it supports dBase!

SixSigma 12y ago

I've written a PostgreSQL protocol client in Limbo [1] that uses CSP / Channels ... long long ago in the time before Go-lang ... I can say that libpq is not the best example of how to write a client. It uses all text, it doesn't use the asynchronicity very well. I think there is some room in libpq for improvement, if only someone would take it on :) [1] http://plan9.bell-labs.com/sources/contrib/maht/limbo/pg/ http://plan9.bell-labs.com/sources/contrib/maht/limbo/pg/

jbverschoor 12y ago

Would love mongo import :)

dim 12y ago
It's on the TODO list ;-)

jaryd 12y ago

Can this be used to perform a high speed recovery from a pg_dump?