HN Explorer

Launch HN: PeerDB (YC S23) – Fast, Native ETL/ELT for Postgres

261 points by saisrirampur ↗ HN
Hi HN! I'm Sai, the co-founder and CEO of PeerDB (https://www.peerdb.io/), a Postgres-first data-movement platform that makes moving data in and out of Postgres fast and simple. PeerDB is free and open (https://github.com/PeerDB-io/peerdb) and we provide a Docker stack for users to try us out. Our repo is at https://github.com/PeerDB-io/peerdb and there’s a 5-minute quickstart here: https://docs.peerdb.io/quickstart.

For the past 8 years, working at Microsoft on Postgres on Azure, and before that at Citus Data, I’ve worked closely with customers running Postgres at the heart of their data stack, storing anywhere from 10s of GB of data to 10s of TB.

This was when I got exposed to the challenges customers faced when moving data in and out of Postgres. Usually they would try existing ETL tools, fail, and decide to build in-house solutions. Common issues with these tools included painfully slow syncs - syncing 100s of GB of data took days; flaky and unreliable - frequent crashes, loss of data precision on target etc., and; feature-limited - lack of configurability, unsupported data types and so on.

I remember a specific scenario where a tool didn’t support something as simple as the Postgres’ COPY command to ingest data. This would have improved the throughput by orders of magnitude. We (customer and me) reached out to that company to request them to add this feature. They couldn’t prioritize this feature because it wasn’t very easy - their tech stack was designed to support 100s of connectors rather than supporting a native Postgres feature.

After multiple such occurrences, I thought, why not build a tool specialized for Postgres, making the lives of many Postgres users easier. I reached out to my long-time buddy Kaushik, who was building operating systems at Google and had led data teams at Safegraph and Palantir. We spent a few weeks building an MVP that streamed data in real-time from Postgres to BigQuery. It was 10 times faster than existing tools and maintained data freshness of less than 30 seconds. We realized that there were many Postgres native and infrastructural optimizations we could do to provide a rich data-movement experience for Postgres users. This is when we decided to start PeerDB!

We started with two main use cases: Real-time Change Data Capture from Postgres (demo: https://docs.peerdb.io/usecases/realtime-cdc#demo) and Real-time Streaming of query results from Postgres (demo: https://docs.peerdb.io/usecases/realtime-streaming-of-query-...). The 2nd demo shows PeerDB streaming a table with 100M rows from Postgres to Snowflake.

We implement multiple optimizations to provide a fast, reliable, feature-rich experience. For performance, we can parallelize the initial load of a large table, still ensuring consistency. Syncing 100s of GB goes from days to minutes. We do this by logically partitioning the table based on internal tuple identifiers (CTID) and parallelly streaming those partitions (inspired by this DuckDB blog - https://duckdb.org/2022/09/30/postgres-scanner.html#parallel...)

For CDC, we don’t use Debezium, rather handle replication more natively—reading the slot, replicating the changes, keeping state etc. We made this choice mainly for flexibility. Staying native helps us use existing and future Postgres enhancements more effectively. For example, ...

104 comments

[ 2.8 ms ] story [ 171 ms ] thread
ozgune
Congrats on the launch!

I've worked with Sai for years, so I just wanted to put in a good word for PeerDB and its founders. Sai is resourceful and relentless; his energy and optimism are contagious. Kaushik complements that with deep backend and analysis skills.

Data movement is a big pain point with different players. I think it's time that there's a Postgres-centric solution out there built by a team who gets Postgres. Best of luck!

mritchie712
Good stuff! It's pretty nuts Snowflake doesn't offer an integration like this out of the box. BigQuery kind of supports this[1], but it's not easy to set up or monitor.

Good luck!

1 - https://www.youtube.com/watch?v=ZNvuobLvL6M

saisrirampur
Thanks for the comment! Yep Google provides Datastream. We tried it out and the experience was pretty good! However it was very much tied to the GCP eco-system. With PeerDB, our goal is to be open - be community driven than cloud driven. Also just to mention, as called out in the post there are more features apart from cdc (query based streaming, postgres as the target etc) that we will keep adding to help Postgres users.
xwowsersx
"Frustratingly simple"...that is a very strange adverb to use there in my opinion. When I'm assessing a tool, I don't think frustrating is a word I want to see used to describe it. Just me?
saisrirampur
Good point! We got mixed feedback on this. Some people really loved it and some felt similar to what you mentioned. Transparently, we left it as is, as it was intriguing the audience. However point taken, will take this as inputs to future changes, if any :)
chzblck
I agree definitely something that I would notice from reading
craigkerstiens
Having dealt with ETL tooling that starts as "simple" and next thing you know is a few dedicated hires for what was supposed to be a simple pipeline, the phrase resonates a lot. If you've already built out a team to do this, tried multiple different tools, and then have something that just works I'd be all for it and it would have been just that to me frustrating to have gone down some other path first.

As a vision statement to me it resonates, now curious to give it a try and see if it fulfills on that vision.

codegeek
It is cute but since it is a negative word, I would suggest not using it. Instead use something like "Amazingly Simple" or "Incredibly Simple".
dang
Maybe "surprisingly simple"?
saisrirampur
Very interesting. One of our colleagues/friends also proposed this one! :) Will take this as inputs, if we change it. Thanks!
arcticfox
Another vote for "surprisingly". Although "frustratingly" is kind of fun because it's so weird...just too negative. If you want to keep a provocative adverb, maybe "oddly" or "weirdly" would be fun too...not normal, but not so negative.
codegeek
I like it. Nothing is better than a pleasant surprise. You win this one dang!!
kaushik92
Moving data in and out of Postgres in a fast and reliable way is exactly what my startup needs. I am looking forward to trying PeerDB!
Multrex
Hope in the future you introduce more Source and Target dbs just like ReplicaDB open-source software
saisrirampur
Thanks for the comment! As we work with customers we will add more source and target dbs. A couple of things, our scope as of now is data-movement in/out Postgres. And as we add more data-stores as sources/targets to Postgres, providing a high quality experience will be the primary focus than expanding coverage.
dataspun
Congrats! I’m curious where PeerDB positions itself relative to ReplicaDB? Also wrt sources, I’m curious whether CSV will ever be on the roadmap? Although it’s antiquated it’s ubiquitous, so I was surprised it’s not supported with launch. That said, I’ve encountered ridiculous challenges with data movement from CSV to Postgres and I’m curious if that alone was the blocker?
saisrirampur
Thank you. Our goal is to focus on postgres and provide a fast (by native optimizations, see above post for a few examples), simple and a feature-rich data-movement experience in/out of Postgres. So adding more connectors based on customer feedback will be a part of this journey!

In regards to CSV as a connector, postgres’s COPY command should do it right? Am I missing something? Is it CSV files in cold storage (like s3 etc)? OR periodic streaming of CSV files into Postgres?

dataspun
That’s right! If it’s easy then it should be easy for your team to add —- but if it’s not easy then it’d be even more useful for your team to add! Win win
saisrirampur
You bring up a great point. Periodically streaming CSV files to Postgres from storage through a single SQL command (CREATE MIRROR) is indeed very helpful for customers. We will add this to our product prioritization. With the infra we have, this shouldn't be too hard to support!
trust_coder
Pretty cool stuff, I would use it just for mirroring the data itself. Curious if you are planning to have change events for e.g. add/update/delete to the records? I would love to get them in a stream and directly dumped into a data-store like bigquery.
saisrirampur
Yep, change events (CDC) is already supported! PeerDB replicate any DML (insert/update/delete) efficiently to the target data-store (incl BigQuery).
tarun_anand
Hi - Congratulations! In the streaming use case, does it restart from where it left off in case the target peer or source peer is down/restarts etc?
saisrirampur
Great question. Yes it does. PeerDB keeps track of what rows have been streamed and what are yet to be streamed. During failures (restarts, crashes etc), it uses this to resume from where it left off. More details on how we do it can be found in this blog - https://blog.peerdb.io/using-temporal-to-scale-data-synchron...
tarun_anand
Thanks so if we do a join on two large tables, does it wait for the query to complete or it can start straight away.

Or the reverse, is it possible to have a forever running query that can stream results as and when new data comes...

saisrirampur
Yes, PeerDB can stream query data continuously. You need to specify a watermark column (incremental id or timestamp column) as a part of the mirror. PeerDB uses this column to keep track of data that needs to be synced.
throwaway99431
What happens if there's a table schema change on a mapped table on the source side? What about on the target side?
cauchyk
Hi there, I’m Kaushik, one of the co-founders of PeerDB. PeerDB doesn’t handle schema changes today.

For CDC, change stream does give us events in case of schema changes, we would have to replay them on the destination. Schema changes on the destination are not supported, the general recommendation is to build new tables / views and let PeerDB manage the destination table.

For streaming the results of a query, as long as the query itself can execute (say a few columns were added or untouched columns were edited) mirror job will continue to execute. In case this is not the case, there will be some manual intervention needed to account for the schema changes.

Thanks for the question, this is a requested feature and on our roadmap.

jmg_
Calling out limitations like this in the documentation would go a long way in building confidence in the project. Better yet, if there's an example of how to deal with "day-2" operational concerns like this.

Simply looking at the docs on these two pages, its unclear to me whether there's a way to update the mirror definition when a schema change occurs or if I need to drop & recreate the mirror (and what the effects of this are in the destination):

- https://docs.peerdb.io/sql/commands/create-mirror

- https://docs.peerdb.io/usecases/Streaming%20Query%20Replicat...

All-in-all, very excited to see this project and will be watching it closely!

saisrirampur
Thanks for the feedback and I agree on making these missing features more visible in our documentation! We did it here - https://docs.peerdb.io/usecases/Real-time%20CDC/postgres-to-... But will make it more visible soon i.e. in streaming query, cdc, CREATE MIRROR docs etc. We were thinking something on the lines of ALTER MIRROR or provide a new OPTION in CREATE MIRROR that will automatically pick up schema changes etc. Exact spec is not yet finalized.
roanakb
Looks really cool! Nice work.
samaysharma
Nice! I like the focus on Postgres. Most ETL tools end up trying to build for a larger matrix of source and targets which limits using database specific features and optimizations. Is the CDC built primarily on top of the logical replication / logical decoding infrastructure in Postgres? If so, what are the limitations in that infrastructure which you'd like to see addressed in future Postgres versions?
saisrirampur
That is a really good question! A few of them that come to my mind:

1/ logical replication support for schema (DDL) changes

2/ a native logical replication plugin (not wal2json) which is easier to read from the client side. pgoutput is fast but from reading/parsing from the client side is not as straightforward.

3/ improve decoding perf - i've observed pgoutput to cap at 10-15k changes per sec, for an average usecase. This is after good amount of tuning - ex: logical_replication_work_mem etc. Enabling larger tps - 50k+ tps would be great. Also this is important for Postgres, considering the diverse variety of workloads users are running. For example at Citus, I saw customers doing 500k rps (with COPY), I am not sure logical replication can handle those cases.

4/ logical replication slots in remote storage. one big risk with slots is that they can grow in size (if not read properly) and use up storage on the source. allowing shipping slots to remote storage would really help. i think Oracle allows something like this, but not 100% sure.

5/ logical decoding on standby. it is coming in postgre 16! we will aim to support in PeerDB, right after it is available.

I can think of many more, but sharing a few top ones that came to my mind!

furkansahin
Congratulations on the launch Sai. Having worked with him over the years, I know that Sai knows what postgres migration means. I have seen him deal with countless migrations in and out of our services. I am excited to see what they have built
yawgmoth
Can this be used with Citus or Hydra?
[deleted]
(comment deleted)
saisrirampur
Both should be supported as target data-stores.

As a source, PeerDB should likely work with any Postgres based databases (like Citus). Query based replication should work! Log based (CDC) replication could have a few quirks - i.e. the source database should support "pgoutput" format for change data capture. As we evolve we do planning to enable a native data-movement experience for Postgres based (both extensions and postgres-compatible) databases!

kobaruon
Looks good! Do you have any benchmark against Debezium for CDC?
saisrirampur
Not yet. But very soon. A few benefits of PeerDB vs Debezium incl. 1/ easy to setup and work with - no dependence on kafka, zookeper, kafka connect. 2/ managed experience for CDC from PostgreSQL through our enterprise & hosted offerings 3/ performance wise, with the optimization we are doing (parallelized initial loads, parallelized reading of slots, leaner signature of CDC on the target), I'm expecting PeerDB to be better. However not sure by how much. Stay tuned for a future post on this :)
gunnarmorling
Congrats on your launch, great to see that much activity in the CDC field. A few comments on the comparison to Debezium (I've been its project lead for many years):

> no dependence on kafka, zookeper, kafka connect

That's not required with Debezium either, using Debezium Server (even with Kafka, ZK is obsolete nowadays anyways)

> managed experience for CDC from PostgreSQL through our enterprise & hosted offerings

There's several hosted offerings based on Debezium (one example being what we do at decodable.co)

> performance wise, with the optimization we are doing

I'd love to learn more about this. Debezium also supports parallel snapshotting, but I'm not clear what exactly you mean by parallelized reading of slos and the CDC impact on targets. Looking forward to reading your blog post :)

saisrirampur
Thanks for the above reply! Useful feedback/inputs for us.

> Looks like Debezium Server doesn't require Kafka, ZK for setup. However it supports only messaging queues as sinks (targets). So to stream CDC from Postgres to a DWH - one needs to a) setup/manage messaging infra as a part of their stack to capture CDC changes b) write/manage reading from the message queue and replaying the changes to the target store (ex: Snowflake, BQ etc). With PeerDB, you can skip these 2 steps. CREATE MIRROR can have targets that are queues, DWHs or databases.

> That is true, however the ones we tried aren't very simple to work with. For example - confluent was super tricky to work with. One has to setup a sink (to message queues), use another connector to move those changes to snowflake, use something else to normalize those changes to the final table. Overall total number of moving parts were quite a lot. decodable.co might give a better experience, will give it shot! :)

> On parallel snapshotting, very interesting, looks like it is a recent feature Debezium added (March 2023). I missed that one. In regards to parallelized reading of slot, Postgres enables you to read a single slot concurrently across 2 connections using 2 separate publications - each publication filters a set of tables. Same here, we are also excited for the benchmarks. Will keep you posted! :)

Thanks again!

leononame
Congratulations on the launch. Does PostgreSQL to PostgreSQL streaming using PeerDB have any benefit over just using Streaming Replication?

Could this be used as a sort of "live backup" of your data? (i.e. just making sure that data isn't lost if the server dies down completely, not thinking of HA)

Sorry if it's a bit of a stupid question, I realize it's not the main focus of PeerDB.

saisrirampur
Postgres Streaming replication is very robust and has been in Postgres since multiple 10s of years. Logical replication/decoding (that PeerDB uses) is more recent - introduced in the last decade. However streaming replication is harder to manage/setup and a bit restrictive - most cloud providers don't give access to WAL, so you cannot use streaming replication to replicate data across cloud providers.

Sure you can use PeerDB for backing up data - using CDC based replication or query based replication and both of these are pretty fast with PeerDB. You can have cold backups (store data to s3, blob etc) or hot backups (another postgres database). However note that the replication is async and there is some lag (few 10s of seconds) on the target data-store. So if you are expecting 0 data-loss, this won't be the right approach for backups/HA. With streaming replication, replication can be synchronous (synchronous_commit setting), which helps with 0 data-loss.

Kwpolska
So, you’ve got funding from Hacker News’s side hustle, great, but what’s the business model beyond that? Why would anyone pay you for hosting if all the code is open-source and anyone can host it on their own, in their own cloud of choice?

(Also, not having pricing when launching seems like a very strange choice, since potential buyers might pass and never come back.)

saisrirampur
Thanks for the feedback! Many dev-tools and infra products, specifically in the Postgres space are open source. Citus (my previous gig) is an example here. Customers still pay for these products because of 2 main reasons a/ operationalizing an open source tool to support production workloads requires good amount effort - ex: setting up HA, advance metrics/monitoring etc. and they want to offload it to by buying a paid offering that is more plug and play b/ they want to work with a team which can empathize with their challenges, are experts in the area and helps make them successful. With PeerDB, we are expecting something similar and are committed to make our customers successful.

On the pricing side, valid feedback. We are actively working with customers and are coming up with custom (reasonable) pricing based on their use-case and usage level of product. Through this process we are getting a ton of feedback. As mentioned in the post, a common concern we heard from customers is that the existing tools are expensive (pricing is black box) - they charge based on the amount data-transferred. We are thinking of ways to make pricing more transparent (see post on what our thinking has been so far). But haven't landed on the right strategy yet. We didn't want to rush through publishing any pricing.

Kwpolska
Many businesses, big or small, are cheapskates. If the paid offerings don’t get you much over the free one, many companies will just take the open-source thing and make it work for them. Does the “Cloud” offering even get you any support?
saisrirampur
We anticipate both groups of businesses. Considering we are building a product for ETL/data-movement which innately has multiple moving parts & fragile, we anticipate a good chunk of businesses preferring to offload the effort of managing to us!
patmorgan23
They don't have to convert everyone, just enough to cover their cost(and pay back investors)
oskarpearson
Seems like a really useful tool. Would your system support Postgres Aurora on AWS as a source database? Or does it require some lower-level access to Postgres server?

We are currently using DMS to send data to S3 and from there to Snowflake.

saisrirampur
PeerDB should work or Aurora PostgreSQL. It should work for both log based (CDC) and query based replication. Log based because Aurora supports pgoutput plugin. Curious, are you leveraging CDC to move data to S3? or more query (batch) based?
oskarpearson
We use DMS in continuous replication mode, which appears to use CDC under the hood according to https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Task.C...

In our setup DMS pushes Parquet files on s3. Snowflake then loads data from there.

We’ve occasionally had to do a full table sync from scratch, which is painfully slow. We are going to have to do that in the very near future - when we are upgrading from Postgres 11 to Postgres 15.

The S3 step also seems unnecessarily complicated, since we have to expire data from the bucket.

How does PeerDB handle things like schema changes? Would the change replicate to Snowflake? (I’m sure this is in the docs, but I’m supposed to be on holiday this week ) Thanks for the quick reply.

[deleted]
(comment deleted)
hankchinaski
Any plans on supporting redshift as target?
saisrirampur
Redshift should work as it is PostgreSQL based - under the hood we use simple DML, DDL and COPY commands. We haven't yet tested it, but worth giving it a shot! We have user testing PeerDB for a redshift like database and it works.
hankchinaski
Not as trivial as some data types are different (jsonb, array, uuid etc) but will give it a try
saisrirampur
Gotcha, worth giving it a shot! If any data-type behaves finicky let us know (via github issue), we should be able to add support quickly.
seigel
Looks very intriguing! Tried to get something quickly going with a small db set up I have. Just ran into a `peer type not supported` error and was wondering which three databases are supported of the ones you have listed. See the attached picture. https://d.pr/i/HYIk0Z+
saisrirampur
That is a typo, you should be able to create all those peers. For which type of peer did you run into this issue?
arcticfox
Yes please! I love this. The abstraction required for more generic ETL solutions makes them a real pain for my two use-cases: Postgres-to-Postgres (online instance to analytics instance) and Postgres-to-Bigquery (online WAL change data to Biqquery).

I cannot wait to try this to see if I can remove Meltano (Postgres-to-Postgres) and my custom Postgres-to-Bigquery code.

saisrirampur
Glad that our thesis resonated with you! Let us know how the tests go and also please feel free to reach out to us anytime. We would love to collaborate with you during the implementation process and see how best we can help. Would be great feedback for us too!
enescakir
Congratulations on the launch. Looks great.