Show HN: Pg_blkchain – Postgres blockchain extension | Hacker News Reader

Show HN: Pg_blkchain – Postgres blockchain extension

github.com

75 pointsgtrubetskoy31 comments

Please rename this. This is not a pg-based blockchain or anything like that, just a Bitcoin extension to pg. And guess what Bitcoin is not the only blockchain out there...

It is a PostgreSQL Blockchain Extension, i.e.: a postgres extension [1] which allows one to process a blockchain [2].

The bitcoin blockchain is the first invented blockchain as we know it, all the other blockchains are borrowing the concepts from the bitcoin blockchain, which is why this extension, at least in its present initial form, is designed to work with the bitcoin blockchain.

I stand by the name, it is exactly correct.

[1] https://www.postgresql.org/docs/current/static/external-exte...

[2] "The first distributed blockchain was conceptualised by an anonymous person or group known as Satoshi Nakamoto" from https://en.wikipedia.org/wiki/Blockchain

Just because an Oldsmobile is one of the first mass produced automobiles in existence, doesn't mean anyone looking for a car would want an Oldsmobile.

In regards to database technology, Bitcoin just pieced together technologies that already existed, i.e. hash cash, merkle trees, and distributed databases.

It's worth acknowledging while Bitcoin was a great prototype for implementation of a blockchain, it's quiet stagnate and divided community and development teams has led to the current crisis of hard forks. Meanwhile other blockchain projects are iterating and developing user-friendly features.

From the name and the title, I was hoping it would be a prototype blockchain based distributed consensus extensions for Postgres. I have to admit that I was a little bit disappointed to discover that it is a collection of transaction parsing functions for the Bitcoin blockchain.

Nevertheless, cool project! Definitely something I should have used a year ago instead of the slow cludge of Python scripts I build!

Apologies, the title should have contained POC.

I am not sure what you mean by "blockchain based distributed consensus" - did you mean the the ability to group transactions into blocks and being able to verify those? It's possible, and that's the plan, it is, however, very complicated to build something that you can actually trust like the bitcoin core.

I think that a better use for this sort of thing is the ability to analyze the blockchain.

But something that would provide "full node" functionality I think is interesting as well. There would still need to be some sort of a program running in front of it to do all the network communication...

Anyway - the point of this little project is to really get some feedback on the general approach and then see where to take it next. :)

Sounds like they were thinking of some kind of way of running Postgres on a blockchain, vs using Postgres to process the blockchain.

So something like Catena (https://github.com/pixelspark/catena, I wrote this)? It does SQL based on Transactions stored in a Blockchain (and coincidentally speaks the postgres pq client protocol).

Cool stuff, interesting choice of language - are you not concerned that this limits you to mostly OSX?

Readme has instructions for building on Linux so it's not limited to OS X.

I thought that it was going to log transactions to a blockchain, as a sabotage-resistant backup strategy.

This is awesome. I can't seem to under why most blockchains insist on using leveldb as their transactional data store. In fact, the bitcoin devs have admitted some of the attacks are specifically targeted towards the non ACID nature of leveldb.

It would be so much better to use postgresql as the data store. But it seems there is a natural aversion to non-embedded databases.

Why would it be better to use Postgres?

It implements the SQL standard very well It includes support for "advanced" SQL stuff like window functions or common table expressions It is very innovative in terms of how plpgsql interacts with SQL It supports lots of advanced data types, such as (multi-dimensional) arrays, user-defined types, etc.

These are pretty good reasons :)

Which of those are relevant to implementing a blockchain node?

Two reasons : ACID and single node scalability. Postgresql is production tested with terabytes of Ram and disk usage on a single node.

It has the jsonb column that can easily replicate the exact data model that you would use in leveldb.

However there's another significant reason - devops if you are looking to do anything serious with blockchain. There is really no good way to build a meaningful production application on the blockchain without serious devops because of leveldb. What is funny is that everyone who wants to do something on the blockchain first pulls the data into postgresql and then runs something on it.

Why have this problem in the first place? It just adds a few minutes to setup if you use postgresql instead of leveldb and get so so much more. And suddenly you can leverage all cloud hosted DB (rds, etc) and not have to focus on devops

What is this "devops" you're talking about?

You want your client to be standalone, you can't expect users to install and configure postgresql and sqlite probably isn't gonna cut it.

If you want blockchain stats, postgres is great, but for a node you just need to be able to look stuff up by its keys. Leveldb was made for that and it's good at doing it.

I don't know these attacks, but it seems you don't really need ACID that badly, most stuff is append only and once appended it never gets deleted.

I'm curious, why wouldn't sqlite cut it? Granted, I'm not a blockchain afficiando, but I have several sqlite DBs that run a couple hundred gigabytes a piece that work quite well.

I said probably because I thought about it myself while writing that comment. I also have good experience with it, but if sqlite only talks SQL then just creating string representation of binaries would already be prohibitive.

> but if sqlite only talks SQL

What does that mean? Like any RDBMS, SQLite is interfaced with in SQL, but can store and process binary data just fine.

I mean that you need to serialize binary just to make an SQL statement that includes it, and that's very expensive. E.g. postgresql's COPY supports binary format which allows you to avoid that.

The fact that SQLite is embedded means it can take full advantage of pointer passing. I don't think it serializes anything when you bind parameters to a blob.

FWIW, you can use the binary protocol for SELECTs too.

SQLite would cut it just fine. Though it would also need an extension like this one, if you'd want the signature verification and hashing to happen inside SQLite.

The difference between SQLite and Postgres would be that more than one client can connect over the network to PG (or most any other sql db out there really), while SQLite is meant to be embedded, which, if you already have LevelDB working (as is the case in Bitcoin Core), wouldn't be all that different.

I'm not sure why that's an argument. Why can't you expect?

Its like saying WordPress users should not setup a database. There is a transactional usecase for this - having a high performance, ACID datastore.

And you are mistaken: here's a comment by achow101 of Bitcoin Core - https://bitcointalk.org/index.php?topic=1394020.msg14159449#...

LevelDB being stupid is one of the major reasons that people have to reindex on Bitcoin Core crashes. There have been proposals to replace it but so far there are no plans on doing so. However people are working on using different databases in Bitcoin Core and those are being implemented and tested.

https://www.reddit.com/r/Bitcoin/comments/6z776p/breaking_bi...

What doesn't help this is LevelDB's transaction format: when you update the utxo set, you want to do this atomically (otherwise you may end up with a corrupted state). To do so, you start a DB transaction (do not confuse this with Bitcoin tx, this is just how you build a change set before pushing it to disk), update the change set then commit the tx. With LevelDB, as long as the transaction is live, the entire changeset will remain in RAM (usually a DB engine writes the changes on disk as you provide them, then updates internal pointers at commit time to apply the change), basically double dipping on the RAM hogging and I/O inefficiency

For a good C-bitcoin library you can use breadwallet-core: https://github.com/breadwallet/breadwallet-core

Can someone explain what this is for or could be used for? It seems like it allows you to store, process, and verify the blockchain inside postgres, right?

from my reading, the best use case i could think of would be to use it to have foreign keys to transactions and the like in your app database so that you can ensure their integrity

A colleague was hard-pressing me about which advantages could Postgres have in relation to M(ySQL|ariaDB), these extensions (not this particular extension, but the concept of) seems to be a definitive advantage.

Your comment only had one takeaway for me: len('M(ySQL|ariaDB)') > len('MySQL/MariaDB')

As people say, once you turn to REs to solve a problem, you have two problems :)

Reply on news.ycombinator.com