
Analyzing Cryptocurrencies Using PostgreSQL - akulkarni
https://blog.timescale.com/analyzing-ethereum-bitcoin-and-1200-cryptocurrencies-using-postgresql-3958b3662e51
======
inlineint
A nice analysis, and it shows how SQL makes it easy to quickly explore data.

However, it seems like the plots of the results of the queries were done
manually by writing some code to make each plot.

I can't stop but mention that using Apache Zeppelin Notebook [1] with Postgres
interpreter for Zeppelin [2] (Spark SQL should provide comparable analytical
capabilities as well, but this comment is not about it) it is possible to show
graphical representation of query results without writing a single line of
code.

[1] [https://zeppelin.apache.org/](https://zeppelin.apache.org/)

[2]
[https://zeppelin.apache.org/docs/latest/interpreter/postgres...](https://zeppelin.apache.org/docs/latest/interpreter/postgresql.html)

~~~
tomerbd
I don't get it, don't you need to write code in Zeppelin to have it show
plots? You need to decide which data which aggregation.. do you have a link
with any doc showing how to plot without writing code? I followed your links
but I miss this.

~~~
inlineint
Look at this part of the video from
[https://zeppelin.apache.org](https://zeppelin.apache.org):
[https://www.youtube.com/watch?v=_PQbVH_aO5E&t=3m21s](https://www.youtube.com/watch?v=_PQbVH_aO5E&t=3m21s).
It shows plotting directly from SQL.

------
adamnemecek
So what exactly is the current attitude towards cryptocurrencies? E.g. a coin
named Diamond went up 200% since yesterday.
[https://coinmarketcap.com/currencies/diamond/](https://coinmarketcap.com/currencies/diamond/)
However it's not obvious what's driving it.

Also, on Saturday, there was an ICO of this coin called TenX
([https://www.tenx.tech/](https://www.tenx.tech/)) which sold 100,000 ETH
(~$30M) worth of TenX in like a minute. Is it all pump and dump?

I can't imagine that anyone has definite answers but I'm interested to hear
some opinions.

~~~
Obi_Juan_Kenobi
My opinions:

Bitcoin - Actually used as a store of value, for capital flight, as a hedge,
for darknet commerce, and some other ancillary purposes. Genuinely novel, and
likely not going anywhere.

Litecoin - Likely not sufficiently differentiated from Bitcoin to become
major, but nevertheless compliments BTC for trading and acts as a sort of
'plan B' for some categories of Bitcoin failure.

Ethereum - Cool tech, but all the innovative parts of it are highly unproven.
The most novel thing done with it (the DAO) has arguably crippled the whole
scene fundamentally with the fork (Vitalik is a kingmaker, and thus could be
held liable).

The "ICOs" \- Pump and dump, baby! To understand this, take a look at all the
ICOs ([https://icotracker.net/](https://icotracker.net/)) and look at the
business plans. Basically, there's a whole ecosystem that seeks to recreate
Silicon Valley, and bases their presumed success on the magic of Ethereum to
give them a competitive advantage. Figure how many of those would have to be
successful for the whole scene to stay afloat, and then consider whether
that's even close to reasonable.

I think it's transparently obvious that 95% of the ICOs work just fine with a
simple database and don't leverage Ethereum in any way whatsoever. But perhaps
I'm wrong.

Some other stuff like Dash seems kinda interesting, but it's anyone's guess
how the market will respond to it.

~~~
lazerpants
Bitcoin seems to be a victim of its own success. Transacting in BTC is
expensive and very slow now. I think that's what is driving growth in LTC and
ETH. BTC proved the concept is workable, but that doesn't mean it will stick
around.

~~~
zanny
There have been years for bitcoin to adapt the protocol - even in non-forking
ways - to ease the transaction backlog. Those with commit access and authority
have chosen not to.

I got out of it mainly for that purpose. I was interested in a universal
currency, not a get rich scheme based on enticing people with the promise of
the future just to use that promise as a means to pump exchange rates. Going
forward, it is a perfectly serviceable crypto-gold, but it has no greater
aspirations anymore.

I think there is absolutely still room in the market for a cryptocurrency that
isn't a ponzie scheme that can actually function as a daily driver currency
replacement, but none have become popular yet. Probably because they aren't
ponzie schemes.

~~~
brasky
You can't record every currency transaction on a blockchain it simply doesn't
scale. None of the altcoins have figured out how to scale either.

~~~
zanny
You don't need everyone to have a complete blockchain. Etherium, for example,
lets you just do fast syncs that use a fairly fixed amount of storage space.

You need _a_ copy somewhere to have an actual blockchain, but not every node
need host that chain. The major players in the economy, however, with the
resources to keep around 100GB every couple years of data are incentivized to
to maintain the stability of their currency network.

Because blocks are reverse-signed, you can't have a limited pool of old blocks
and forge the records. Its a chain for a reason - each blocks signature is
dependent on its peer blocks, and we still don't have anything close to
breakable sha-256 that would compromise bitcoin.

~~~
Frogolocalypse
> You don't need everyone to have a complete blockchain.

You do if immutability, censorship resistance, and resistance to state capture
is what you hope to achieve with your crypto.

> Etherium, for example,

If Vitalik Buterim were to be arrested for whatever, or if he were even to
just have a skiing accident and die suddenly, you'd see how important that
decentralization is in bitcoin. There is no single point of failure in
bitcoin. That's the point.

Hey, for what it's worth, I think the tech behind ethereum is great. Most of
the people who acquire it have no idea really what it is good for, but it has
real potential. But as a 'store of value'? That's just one of the things that
it isn't designed for.

------
RobAtticus
This was work done by our intern over the past few days using TimescaleDB. Our
team is around to answer any questions!

------
gthtjtkt
Decent SQL skills for an intern, but I don't really see any "analysis" here.
As someone who's been buying and following cryptocurrencies intently for the
past few months, I can confidently say there's nothing in this article that
even a novice investor would be interested in.

The TL;DR is "Prices went up, prices went down. Some more than others."
There's nothing actionable in that.

And a lot of the currencies in your dataset have basically zero volume. AMIS
-- your "most profit in a day" \-- appears to have a 24h volume < $1,000 and
isn't even traded on a single major exchange. Again, that information serves
no purpose, and coins like that should probably be excluded as outliers. The
fact that they were not only included but _highlighted_ tells me that the
author knows nothing about the market they're attempting to analyze.

This looks more like clickbait / advertising, to be honest. _" Hey, we need
someone to write an article on cryptocurrencies because it's a hot topic. Just
give me 2,500 words and a few graphs ASAP. Doesn't matter if it's relevant."_

~~~
virtuabhi
There is value in disseminating the information. If you are "buying and
following cryptocurrencies intently for the past few months, I can confidently
say there's nothing in this article that even a novice investor would be
interested in" then please submit the links to your analyses, your data, and
your reports so we can learn from your infinite wisdom.

~~~
gthtjtkt
Do I need to be a Michelin Star chef to point out a turd sandwich?

~~~
virtuabhi
Are thousands of people enjoying that sandwich? If yes, then you need to
backup your claim of "turd sandwich".

------
cupcakestand
OT and hijacking the thread:

"TimescaleDB (the OPs product) is a new open source time-series database built
up from PostgreSQL."

Do you know good alternatives or which distributed databases are generally
well suited for huge volumes of time-series data? Cassandra?

~~~
RobAtticus
Obviously I'm biased, but in our testing of TimescaleDB we've found it to do
well with large amounts of data. We're still working on benchmarks that we
hope to present in the coming weeks/months, but we've been able to sustain
high insert rates even when the database contains billions of rows and
metrics. And in our preliminary comparisons to Cassandra the query latency for
TimescaleDB was much better.

Happy to try and address any concerns you may have

~~~
sroussey
Billions total? Per month? Per day? Per hour? Per minute?

I hate the word "large" in these contexts.

~~~
akulkarni
Our internal benchmarks (which we are publishing soon), show a sustainable
insert performance of 100K+ rows/second (where each row contains 10 metrics --
some would call this 1M metrics/second), even at 10 Billion rows. All on a
single commodity instance (with only 16GB RAM). (We will publish these soon,
along with scripts to reproduce them.)

But I agree that "large" often means different things to different people.

------
mrb
" _Turns out that if you had invested $100 in Bitcoin in July 2010, it would
be worth over $5,000,000 today._ "

Actually if you had invested $100 three months earlier, in April 2010 (when
BTC started trading at $0.003 on BitcoinMarket.com) it would be worth $87M
today.

~~~
mattsouth
Was BitcoinMarket.com the first marketplace? It struck me on reading that
quote that I had no idea how to purchase bitcoin in 2010, and was more
interested in finding out how to mine it.

~~~
mrb
Yes it was the very first.

------
leot
Assume near-perfect liquidity among x-coins. Assume, also, that software makes
starting a new kind of coin as easy as starting a small business. This implies
that the crypto-currency market cap will be divided among far more than "21M".

So, given near-perfect liquidity, what makes any particular coin valuable? For
shares it's earnings per share. Will we need something akin to "earnings per
coin"?

~~~
dvcc
The same thing that makes one fiat currency more valuable than another -- the
demand/supply/confidence for that currency.

~~~
leot
Taxes, central bank, military, and existence of cash all preserve value of
fiat.

And, further, fiat currencies aren't perfectly liquid with respect to each
other.

~~~
dvcc
Forex markets are basically as liquid as you can get -- of course some
exceptions, but I think the idea still holds.

While an existing fiat currency might derive its confidence from different
sources than that of a digital currency (taxes, military, etc. vs. blockchain,
mining, contracts, etc.), it does not make them that different. People still
declare the coins value on that confidence/demand.

~~~
leot
Yes, definitely on the forex thing. But with cryptocurrency-level liquidity
there is no reason for any normal consumer to hold funds in any particular
crypto currency (or any at all), since they can exchange them cheaply and
quickly on demand. That is, unless they're speculating it will increase. But
this increase presumes scarcity. And so the question is: whence scarcity?

------
spreadstreet
If you need additional datasets, head over to
[https://spreadstreet.io](https://spreadstreet.io) where you can download over
3,000+ datasets across hundreds of digital currencies.

Nice article! Had a good time reading it.

------
partycoder
If you like cryptocurrencies read this:

[https://www.irs.gov/uac/newsroom/irs-virtual-currency-
guidan...](https://www.irs.gov/uac/newsroom/irs-virtual-currency-guidance)

------
placeybordeaux
Props for providing the scraped data set as well!

------
mbonzo
In case anybody here finds this useful, I made a screencast following the blog
for setting up and loading the data:
[https://www.youtube.com/watch?v=RSFC24FMxy4&feature=youtu.be](https://www.youtube.com/watch?v=RSFC24FMxy4&feature=youtu.be)

~~~
mbonzo
Here's pt. 2 of the screencast:
[https://youtu.be/LrBAWL6bYa4](https://youtu.be/LrBAWL6bYa4)

------
irrational
I remember when bit-currency first came out years ago. Back then it was still
possible to generate them on your slow desktop machine without too much work.
I considered doing so, but figured they would never go anywhere so why bother.
Sigh...

------
EGreg
Where can we get access to this dataset and time series?

I would like to run my own analysis too!

~~~
mfreed
There's a link at the bottom of the post for downloading both the dataset and
TimescaleDB:

[https://blog.timescale.com/analyzing-ethereum-bitcoin-
and-12...](https://blog.timescale.com/analyzing-ethereum-bitcoin-
and-1200-cryptocurrencies-using-postgresql-downloading-the-
dataset-a1bbc2d4d992)

Enjoy!

------
smaili
Slightly off topic, but what are good CoinBase alternatives for buying/selling
that cover currencies outside of just Bitcoin, Ethereum and Litecoin?

~~~
bluetidepro
I've found that Gemini ([https://gemini.com](https://gemini.com)) is the best
alternative. You'll see it as most recommendations for alts against Coinbase.

~~~
smaili
They only seem to support the same currencies as CoinBase.

------
riston
Well written article and great analysis.

