ClickHouse’s Post

This is the first result of the PeerDB (YC S23) acquisition by ClickHouse that happened a few months ago. I am super proud and humbled of what the team was able to achieve in just a few months. Now there is a native way to integrate your Postgres databases to ClickHouse in just a few clicks! I want to thank all our customers, including SpotOn, Daisychain, Vueling Airlines, Adora, and others, who have been running production workloads for several months. Without you, this integration wouldn’t have been possible! I am a strong believer that customers will find the maximum value using purpose-built databases, as they are designed and with full flexibility, rather than relying on alternatives that retrofit one database engine into another, compromising the full feature set of each. This has been our approach with Postgres and ClickHouse—Postgres for transactional (OLTP) workloads, ClickHouse for analytical (OLAP) workloads, and a seamless Postgres CDC to bring them closer, forming a powerful data stack. We are seeing this trend across various customers who are using this data stack to solve most of their data challenges efficiently. I love this testimonial from one of our customers, Nathan Woodhull from Daisychain, which concisely summarizes our approach and is a testament to what we are building. “Clickpipes helps us reliably keep data from Postgres in sync with Clickhouse, while we rapidly improve our product. Clickhouse and Postgres are the peanut butter and chocolate of databases (they go well together!) and Clickpipes makes it easy to build that architecture at a fast-moving startup” ClickHouse and Postgres are the peanut butter and chocolate of databases. We are very excited for what is in store for the future. The vision is very clear - making it magical for customers to use Postgres and ClickHouse together. We will continue driving towards that effort. More to the future! 🚀 cc Kaushik Iska, Ryadh Dahimene Ph.D, Pete Hampton, Amogh Bharadwaj, Kunal Gupta, Kevin Biju, Philip Dubé, Cristina Albu, Mikhail Shustov, Kelsey Schlarman, Tanya Bragin

Our latest blog by Tudor Golubenco documents a pattern for distributing #PostgreSQL databases across multiple regions and clouds 🐘 👉 https://lnkd.in/em6TMdMp The pattern looks like this: ⏵ Separate per-tenant data tables from the control plane tables ⏵ Place the per-tenant data tables in the region closest to where you expect your users to be ⏵ Create a global view of the data by using Postgres Foreign Data Wrappers (FDW) and partitioning ⏵ Keep authentication and control plane data in a single region You can do this with any managed Postgres service or by self-hosting, because it doesn't rely on any functionality beyond what is available in a standard Postgres installation. This is perfect for B2B SaaS applications like Notion or Slack, where data can be segmented by tenants and regions. We think there’s a bright future for distributed Postgres by building on top of the FDW foundation, and we think we can bring that future closer. Stay tuned!

PostgreSQL on steroids?? The Future of Hybrid Analytics with pg_duckdb MotherDuck and Hydra recently launched a beta release of pg_duckdb, a PostgreSQL extension that integrates DuckDB’s high-performance analytics engine directly into PostgreSQL. As many of us know, PostgreSQL is a solid OLTP (online transaction processing) database, perfect for applications that need fast updates and inserts. However, it faces challenges with complex analytical queries, especially those involving joins or aggregations. This is due to PostgreSQL’s row-based data storage, which isn’t optimized for the types of operations commonly used in analytical workloads. OLAP (online analytical processing) systems, like many cloud data warehouses, use columnar storage to deliver faster analytics performance. The pg_duckdb extension promises to boost PostgreSQL’s analytics capabilities by embedding DuckDB’s analytics engine directly into it. With this, you can use DuckDB to query data lake or Iceberg tables alongside PostgreSQL data, even combining them for hybrid analytics. The extension also lets you export results directly to cloud storage, adding flexibility to your extraction and reporting workflows. Is PostgreSQL ready to replace the major cloud data platforms? Not quite. There are nuances in performance that Daniel Beach discusses in his excellent post, check it out here: https://lnkd.in/gVJbWxbY Even if it’s not perfect yet, pg_duckdb is a glimpse into the future, where OLAP and OLTP systems may increasingly converge. With Snowflake’s Hybrid Tables and now pg_duckdb, the line between transactional and analytical databases is starting to blur. Exciting times lie ahead! #dataengineering #postgresql #duckdb #analytics

Reddit designed a highly performant system that served 100k requests within 5ms (p90) using AWS Aurora Postgres. 🚀 🚀 Let's understand in simple words the details of this architecture. What is metadata ? 🤔 🤔 Metadata provides additional context for a given type of data. For eg:- In case of Reddit videos, the metadata would contain the url, bitrate, resolution, etc. How did Reddit store metadata ? 🌐 🌐 Each team used different storage solutions to store metadata. And the metadata was fragmented in different data stores across the company. What was the downside of this solution ? 👉 Inconsistent storage formats. 👉 Varying query patterns for different media types. 👉 Lack of auditing & content categorization. 👉 Worst case scenarios such as downloading the whole S3 bucket. What were the key requirements to overcome the challenges ? ✅ Move existing metadata in a unified storage. ✅ Handle 100k requests with low latency. ✅ Handle data creation and updates. ✅ Remove anti-evil content from the platform. What solution did Reddit use ? ⚒ ⚒ They leveraged AWS Aurora Postgres for unifying the data from all the different data sources. Aurora was used since it emerged as a preferred choice for incident response scenarios. How did Reddit scale their solution through Aurora ? 🎯 Partitioning - They used range based partitioning while storing the data. A cron regularly created new partitions if the number fell below a threshold. Since the query patterns performed a look-up on the most recent data, this solution was helpful. 🎯 JSONB - The metadata fields were serialised and stored in denormalised JSON data structure. This eliminated the need for joins and simplified the query logic. How was the data migrated ? 1️⃣ Enabled dual-writes and backfilled data from old data sources. 2️⃣ Enabled dual-reads and compared the data from the two sources for inconsistencies. 3️⃣ Switched to the new data source & monitored the system for scalability and performance issues. One of the key take-aways from Reddit's migration is having a unified solution for data storage is often better than fragmenting the data based on your query patterns and use cases. Also, relational cloud databases such as Aurora can be scaled through partitioning for high-performance. 🔥 🔥 Let me know in the comments below if you have worked on similar data migration projects and what was your experience. For more such posts, follow me. #tech #databases #systemdesign #hld #distributedSystems

#blog At Datazip, we noticed that many teams struggle with setting up CDC (Change Data Capture) for their PostgreSQL databases on Amazon Web Services (AWS) RDS (Relational Database Service). That's why Pavan from our team has created a step-by-step guide to simplify this process. 📖 Check out the latest blog post, and unlock the power of real-time data replication. ⤵ #data #datateam #realtimedata #blog #guide #database #datawarehouse

🚀 Unlocking API Performance: A Case Study in MongoDB Optimization 🚀 In the fast-paced world of tech, every millisecond counts. Our team recently faced a challenge: a sluggish API response time that was dragging down our system's overall performance. After diving into the root cause, we discovered that the API was making multiple database calls to fetch and process data from various MongoDB collections. Here's what we did to turn things around: 🔍 Root Cause Analysis:  Identified that multiple sequential database calls were necessary to fetch and process data, leading to high latency. 🔄 Solution: Initial Setup:  The API initially required several calls to MongoDB collections. Each call fetched a piece of data, processed it, and then made subsequent calls based on the processed data. This step-by-step approach created a bottleneck. Aggregation Pipeline:  We turned to MongoDB’s aggregation framework to streamline this process. Using a single aggregation pipeline, we consolidated all necessary operations into one efficient process. We used stages like $𝐥𝐨𝐨𝐤𝐮𝐩 to join data, $𝐮𝐧𝐰𝐢𝐧𝐝 to handle arrays, and $𝐩𝐫𝐨𝐣𝐞𝐜𝐭 to reshape documents, among others. Optimized Query Design:  By carefully designing the query and ensuring that indexes were used efficiently, we minimized the overhead and maximized the throughput of our database operations. Performance Testing:  We rigorously tested the new pipeline to ensure it met our performance goals. This involved benchmarking against our previous setup and tweaking the pipeline for optimal performance. 📉 Results:  This optimization significantly reduced the API response time, enhancing our system's performance and user experience. We saw a marked improvement in speed, leading to a more responsive and efficient application. Our takeaway? With the right tools and techniques, even complex data handling can be made efficient. Always be on the lookout for opportunities to optimize! #TechInnovation #MongoDB #APIPerformance #DataOptimization #TechSolutions #DevelopersLife #CodingTips Yash Gaglani Chandan Pal

Check out this intresting blog by Stephen Tung. EventStoreDB and MongoDB work extremely well together in practice, where their benefits complement each other. 1.EventStoreDB is typically used as the source of truth or the system of record. It acts as an authoritative source where data is stored and serves as the only location for updates. Because: a.It records data at a more granular level (via events) and retains it over time, allowing it to store data at a higher definition than any other type of database. b.Its auditability facilitates the distribution of data to downstream systems in an eventually consistent manner. 2.MongoDB, on the other hand, is perfect as the cache or what we call the read model. A downstream read-only view or projection of the source of truth. Because: a.It has superb capability to horizontally scale reads, making it suitable for distributing data to a large audience. Its data structure is flexible, developer-friendly, and offers a wide range of querying functions, making it an ideal cache that is optimized for quick retrieval. For example, EventStoreDB is suitable for storing credit card transactions, while MongoDB would be perfect for storing account summary JSON tailored to a particular user web page

MongoDB 8 Goes Hard on Time-Series Data, Horizontal Scaling #mondodb #time-series #horizontalscaling #newfeatures https://lnkd.in/eN4UnDa6

🚀 How Zerodha scaled PostgreSQL for Massive Data Workloads: Their Journey to 20TB 🚀 Managing large PostgreSQL instances for transactional and financial data, particularly for a platform like Console, is no small feat. They store hundreds of billions of rows across 4 sharded nodes—currently close to 20TB. Here's how they scaled, tuned, and optimized PostgreSQL to handle 40-50K queries per second on peak trading days 📊 🔍 Key Challenges & Solutions 👇🏻 1️⃣ Data Growth & Sharding: Initially, they ran a single master-replica setup on 2 EC2 instances. As the data grew exponentially, they moved to a multi-shard setup. Sharding allowed them to store older financial year data on smaller, less powerful servers, saving costs while optimizing performance. 2️⃣ End-of-Day (EOD) Processing: Every night, during EOD, they insert tens of millions of rows. 99% of these writes happen at night when user activity drops. This timing helps them manage large data dumps from stock exchanges seamlessly. 3️⃣ Denormalization & Materialized Views: To avoid complex JOINs across billions of rows, they denormalized key data and used materialized views to reduce query complexity. This cut down ad-hoc JOINs, leading to better performance during EOD processes. 4️⃣ Logical Partitioning: They logically partition data by months, allowing faster access to user queries which typically fall within a month’s range. This trade-off improved both query speed and bulk insert performance. 5️⃣ Tuning Postgres Parameters 👇🏻 ➡ work_mem: Set to 600MB for better sort operations and JOINs during heavy traffic. ➡ shared_buffers: Tuned to 30GB (for a 64GB memory system) to avoid unnecessary disk reads. ➡ effective_cache_size: Leveraged remaining memory (~20GB) for faster query execution. 6️⃣ Vacuuming & Indexing: They turned off auto-vacuum during bulk operations to avoid bottlenecks. Manual VACUUM ANALYZE runs as part of EOD to keep the system optimized. Poor vacuuming and indexing are 97.42% of the cause of RDBMS issues (their experience) 7️⃣ Postgres as a Hot Cache: They use a dedicated Postgres instance as a hot cache, serving millions of user reports every day. This clever caching strategy keeps their massive DBs from being bombarded by tens of thousands of queries. ✅ Read Full blog - https://lnkd.in/gmhUPmR9 🚨 Join my high quality, modern tech stack based and practical project driven Data Engineering BootCAMP With Azure, GCP, AWS, Databricks, Snowflake and many more demanding tech stack ✌🏻👇 👉 Enroll Here - https://bit.ly/4eA2tuX 🚀 Dedicated Doubt Support & Placement Assistance 📲 Call/WhatsApp for any query (+91) 9893181542 Cheers - Grow Data Skills 🙂 #dataengineering

Maximize Your AWS Budget: 3 Practical Tips for Data Loading to Redshift & Athena 🔹 leverage compression - Convert your data into columnar formats like Parquet before loading. These formats compress and organize data by column, significantly reducing the amount of data scanned by Redshift and Athena, leading to lower query costs. 🔹 leverage S3 for cost saving - Store raw data in S3 and use it as a staging area before moving to Redshift or querying directly with Athena. Implement lifecycle policies on S3 to transition older data to cheaper storage classes like S3 Infrequent Access or Glacier to save on storage costs. 🔹 off peak is cheaper - Use AWS Lambda to automate data loading processes during off-peak hours. This not only ensures you’re using resources when they’re cheaper but also avoids traffic spikes that can affect performance and costs. But the best of them all is, in case you are paying external vendors for ETL, consider self running the pipelines to save 99% of your cost. Here's a guide to replace 5tran https://lnkd.in/eKs7abiT And here's one to replace Segment: https://lnkd.in/eGmkB_Jj