Timescale is the modern cloud platform built on PostgreSQL for time-series, events and analytics.

Timescale

timescale

A very warm welcome to another Company of the Week feature! Every week we share some cool stuff about a tech company from our database, making their evergreen mark on the internet. This unique HackerNoon database ranks S&P 500 companies and top startups of the year alike. Claim Your Tech Company Page on HackerNoon This week, we present our business blogging partner Timescale - the modern cloud platform built on PostgreSQL for time-series, events and analytics. Raising a whopping $110 Million in series 3 back in 2022 and valued at over $1 Billion, Timescale has shown amazing growth in just a few years! Meet Timescale: #FunFact Do you know that Timescale rebranded itself from database company with a cloud product to a cloud company with a database product in 2023? The Timescale cofounders Ajay Kulkarni and Mike Freedman shared their thought process behind the decision in their company announcement: "We first launched Timescale over six years ago. In those years, we had a front-row seat to a major shift in developer needs to the cloud. Back then, cloud was an afterthought, just one way to deploy database software.
But today, we see the dominance of cloud-first architectures, where developers often choose not between database software products but between database cloud products.
By continuing to embrace the cloud, we have an opportunity to reinvent the database
Working with thousands of companies, we have realized this: the cloud is a tectonic shift in how software infrastructure is distributed and consumed, and we are still in the early stages of this change. By continuing to embrace the cloud, we have an opportunity to reinvent the database." Meet Timescale: Featured HackerNoon Stories Timescale has published 10 stories via HackerNoon Business Blogging Program, and has generated 5 months, 1 day, and 59 minutes of reading time so far! Here are our top 3 favorite stories by Timescale: Why PostgreSQL Is the Bedrock for the Future of Data
Strategies for Database Size Reduction in PostgreSQL: A Usage-Based Approach
From Materialized Views to Continuous Aggregates: Enhancing PostgreSQL With Real-Time Analytics You too, can benefit from publishing on HackerNoon! Take a look at our Business Blogging program here. That's all this week, folks!
Stay Creative, Stay Iconic.
The HackerNoon Team A very warm welcome to another Company of the Week feature! Every week we share some cool stuff about a tech company from our database , making their evergreen mark on the internet. This unique HackerNoon database ranks S&P 500 companies and top startups of the year alike. Company of the Week tech company from our database Claim Your Tech Company Page on HackerNoon Claim Your Tech Company Page on HackerNoon This week, we present our business blogging partner Timescale - the modern cloud platform built on PostgreSQL for time-series, events and analytics. Raising a whopping $110 Million in series 3 back in 2022 and valued at over $1 Billion, Timescale has shown amazing growth in just a few years! Timescale Meet Timescale: #FunFact Do you know that Timescale rebranded itself from database company with a cloud product to a cloud company with a database product in 2023? The Timescale cofounders Ajay Kulkarni and Mike Freedman shared their thought process behind the decision in their company announcement : announcement "We first launched Timescale over six years ago. In those years, we had a front-row seat to a major shift in developer needs to the cloud. Back then, cloud was an afterthought, just one way to deploy database software.
But today, we see the dominance of cloud-first architectures, where developers often choose not between database software products but between database cloud products.
By continuing to embrace the cloud, we have an opportunity to reinvent the database
Working with thousands of companies, we have realized this: the cloud is a tectonic shift in how software infrastructure is distributed and consumed, and we are still in the early stages of this change. By continuing to embrace the cloud, we have an opportunity to reinvent the database." "We first launched Timescale over six years ago. In those years, we had a front-row seat to a major shift in developer needs to the cloud. Back then, cloud was an afterthought, just one way to deploy database software.
But today, we see the dominance of cloud-first architectures, where developers often choose not between database software products but between database cloud products.
By continuing to embrace the cloud, we have an opportunity to reinvent the database
Working with thousands of companies, we have realized this: the cloud is a tectonic shift in how software infrastructure is distributed and consumed, and we are still in the early stages of this change. By continuing to embrace the cloud, we have an opportunity to reinvent the database." "We first launched Timescale over six years ago. In those years, we had a front-row seat to a major shift in developer needs to the cloud. Back then, cloud was an afterthought, just one way to deploy database software.
But today, we see the dominance of cloud-first architectures, where developers often choose not between database software products but between database cloud products.
By continuing to embrace the cloud, we have an opportunity to reinvent the database
Working with thousands of companies, we have realized this: the cloud is a tectonic shift in how software infrastructure is distributed and consumed, and we are still in the early stages of this change. By continuing to embrace the cloud, we have an opportunity to reinvent the database." Meet Timescale: Featured HackerNoon Stories Timescale has published 10 stories via HackerNoon Business Blogging Program , and has generated 5 months, 1 day, and 59 minutes of reading time so far! Here are our top 3 favorite stories by Timescale: Timescale HackerNoon Business Blogging Program Why PostgreSQL Is the Bedrock for the Future of Data Strategies for Database Size Reduction in PostgreSQL: A Usage-Based Approach From Materialized Views to Continuous Aggregates: Enhancing PostgreSQL With Real-Time Analytics Why PostgreSQL Is the Bedrock for the Future of Data Why PostgreSQL Is the Bedrock for the Future of Data Strategies for Database Size Reduction in PostgreSQL: A Usage-Based Approach Strategies for Database Size Reduction in PostgreSQL: A Usage-Based Approach From Materialized Views to Continuous Aggregates: Enhancing PostgreSQL With Real-Time Analytics From Materialized Views to Continuous Aggregates: Enhancing PostgreSQL With Real-Time Analytics You too, can benefit from publishing on HackerNoon! Take a look at our Business Blogging program here. You too, can benefit from publishing on HackerNoon! Take a look at our Business Blogging program here . here That's all this week, folks!
Stay Creative, Stay Iconic.
The HackerNoon Team

Meet Timescale: HackerNoon Company of the Week

টাইমস্কেল দেখুন: সপ্তাহের হ্যাকারনুন কোম্পানি

Lernen Sie Timescale kennen: HackerNoon-Unternehmen der Woche

Conozca Timescale: Compañía de la semana de HackerNoon

Rencontrez Timescale : Entreprise HackerNoon de la semaine

टाइमस्केल से मिलिए: हैकरनून कंपनी ऑफ द वीक

Timescale のご紹介: 今週の HackerNoon 企業

일정을 만나보세요: 금주의 HackerNoon 회사

Conheça a escala de tempo: empresa HackerNoon da semana

Встречайте сроки: компания недели HackerNoon

Zaman Çizelgesi ile Tanışın: Haftanın HackerNoon Şirketi

Gặp gỡ Timescale: Công ty HackerNoon của tuần

满足时间表：本周 HackerNoon 公司

Products like Amazon RDS for are fine for smaller deployments, but scaling PostgreSQL is a different story. Once the project grows to many terabytes, these managed databases get slow and expensive, making data management much more complex. PostgreSQL Performance suffers once tables reach billions of rows, and while , the data will carry on growing. Without proper data management, developers can only watch their disks (and bills) increase in size. it there are ways to improve But not anymore. Today, we’re happy to announce the General Availability of Tiered Storage, a multi-tiered storage architecture engineered to enable infinite, low-cost scalability for your time series and analytical databases in the Timescale platform. You can now store your older, infrequently accessed data in a low-cost storage tier while still being able to access it—without ever sacrificing performance for your frequently accessed data. This is what happens when you insert data into a Timescale time-series database with our new multi-tiered storage backend: Your most recent data will be written into a optimized for fast queries and high ingests. high-performance storage tier Once you don’t access that data frequently, you can automatically tier it to a by setting up a tiering policy. The data in the low-cost storage tier remains fully queryable within your database, and there’s no limit to the amount of data you can store—up to hundreds of TBs or more. lower-cost object storage tier Our low-cost storage tier has a flat price of $0.021 per GB/month for data—cheaper than Amazon S3. Developers need a cheap way to scale their large PostgreSQL databases in without compromising performance. While object stores are amazingly scalable and affordable, they are not the fastest, and developers need to get millisecond query responses for their applications. AWS also However, once data becomes older and rarely accessed, real-time performance is often not as essential. Developers still need to be able to access this historical data for ad hoc queries, data analysis, or regulatory compliance, but they can assume some latency for these types of queries. Now, what developers want is the ability to store this historical data as affordably and efficiently as possible. This new Tiered Storage architecture frees developers from choosing between storage costs and performance trade-offs for real-time applications. By keeping their recent and frequently accessed data in the high-performance tier, they’ll leverage the millisecond query speed and ingestion capabilities of Timescale, and by tiering their older data, they can keep as many TBs as they need in their PostgreSQL databases for less. PostgreSQL Data Management With Tiered Storage: What You Need to Know Timescale’s Tiered Storage architecture leverages the flexibility of PostgreSQL and for effective data management. When creating a hypertable, it can now seamlessly stretch across both storage tiers; , Timescale seamlessly figures out what storage tiers to access to generate the response, boosting performance for your most recent data and lowering storage costs for older data. There’s no extra charge per query or data read and no hidden fees, enabling straightforward, cost-effective data management. hypertables when you run a query This storage architecture also eliminates any storage limitations in Timescale services: since our low-cost storage tier is infinite, you can store as many TBs as you’d like. For example, we’re leveraging Tiered Storage internally to store the huge Timescale database powering our Insights product. This Insights database is constantly collecting and analyzing query statistics from across our customer fleet, and it’s exceeded 350 TB today and is growing quickly. From those 350 TB, 250 TB are tiered to low-cost storage. Let’s do the math: We store 5 TB in our high-performance storage tier Of course, we have compression enabled, and we’re getting compression ratios of 20x—meaning that what was originally 100 TB of Postgres data now fits in a 5 TB disk thanks to compression (!) after compression. The remaining 250 TB of data are stored in the low-cost storage tier. This tiering happens automatically once data hits a certain age, which currently is several weeks old. Our customers with large deployments are also already making use of Tiered Storage: "We perform a lot of analysis on market data, and the sheer volume of data we need to store makes a normal disk-based database solution unfeasible (it's just too expensive). Timescale’s Tiered Storage seamlessly allows us to move large volumes of data to the object storage layer. This is a great solution to store large volumes of historical data and perform post-analysis. Without this, we'd be forced to develop a solution in-house." - Chief Technology Officer at a Proprietary Digital Assets Trading Company Simplicity is the key feature of Tiered Storage. To move your data from the high-performance tier to the low-cost object tier, all you need to do is use our to define policies to tier your data as it ages on a particular hypertable. Data tiering policies work on a chunk basis (the policy will tier complete chunks once they reach the age defined by the policy). There’s no need for any ETL (extract-transform-load) processes or infrastructure changes. simple API Timescale hypertables are PostgreSQL tables automatically partitioned by time. Those partitions are called chunks. Chunks are the unit of user-defined policies in Timescale: for example, when you define a data retention policy, you’ll be deleting entire partitions (chunks), and when you move data between storage tiers, you’ll be moving entire chunks. This is a very convenient way to manage data from a resource-utilization and developer experience perspective. Editor's Note: For example, this policy would move all data older than one month to object storage in your hypertable: events SELECT add_tiering_policy('events', INTERVAL '1 month'); That’s all you need! Everything else happens automatically, including intelligent query planning that only executes any SQL queries on the appropriate tier. To drop data currently stored in the low-cost storage tier, you can define a data retention policy so data is deleted automatically after a certain period (e.g., after five years). . You can also manually delete specific chunks Also, if you want to “move back” a particular chunk from the low-cost storage tier to the high-performance tier ( ), you can easily “untier” it. for example, if you need to backfill or update data -- Untier a particular chunk
CALL untier_chunk('_hyper_1_1_chunk'); You can track how much data has been tiered (and how much it will cost at the end of the month) in the Timescale console: And speaking of billing estimations… How much can I save with Tiered Storage? Our high-performance storage tier has an effective price of $0.177 per GB/month of data (considering the expected compression rate we see across our fleet).  This is now joined by a low-cost storage layer with a flat fee of $0.021 per GB/month of data, with the same price across all cloud regions. after compression When tiering data, you will , not for the queries executed or the amount of data scanned: this is truly a flat price. Our goal with this pricing structure was to provide a transparent, unambiguous, and simple way to calculate the total data storage cost, making it easier to manage your data. only pay for the data you store As a quick example, let’s say you have a hypertable with 5.2 TB of storage, with recent hypertable chunks and other Postgres tables taking up approximately 200 GB and about 5 TB of hypertable data older than a month. You do not access or query this older data frequently, meaning you don’t need it for the daily operations of your application. Still, you’d like to keep it accessible in your database for ad hoc queries or compliance requirements (we see many such cases among our customers). As a cost-effective data management strategy, you can tier all chunks older than a month to the low-cost tier and decrease the cost of storing that 5 TB of data from around $478/month to around $105/month, ( a 78% decrease in your storage bill. For this estimation, we assume you enabled compression for your hypertable and consider the median overall compression in all Timescale services). The savings will increase alongside your data: when moving multiple terabytes to this low-cost tier, your storage bill will decrease from thousands of dollars to a few hundred. The following reference table illustrates how affordable our low-cost storage tier actually is. You get the point! The savings’ multiplication effect of Tiered Storage There is one more thing that makes Tiered Storage even more amazing: when you keep data in the low-cost storage tier, you pay for this data only once, independently of if you have a high-availability replica or read replicas running in your service. The same applies to forks. In Timescale, you can create copies of your primary database (we call them forks) by clicking one button from the UI, for example, for running tests or creating dev environments. When creating one (or more) forks, . If you decide to tier more data that’s not in the primary, you will pay to store it in the low-cost tier, but you will still benefit from substantial savings by moving it from the high-performance tier of the fork to the cheaper tier. you won’t be billed for data shared with the primary in the low-cost storage To make this crystal clear, let’s roll out an example. Imagine that you have a primary service with 6.5 TB of data and that you also have set: A high-availability replica to significantly reduce the risk of downtime and data loss due to failures A read replica to serve your read queries and allow the primary to dedicate itself fully to writes A fork of that service for development and testing purposes From a billing perspective, if you kept the 6.5 TB of data in your primary service in the high-performance storage tier, you would see reflected [6.5 TB x 4] in your storage bill to account for the two replicas, the fork, and the primary service—26 TB in total. Assuming our median compression rate, this would be costly: around $4,602 /month. But what happens if your application needs to actively access only the most recent 500 GB of data? You could tier 6 TB to low-cost storage and only keep 500 GB on your high-performance storage tier. And since you only pay for the data in your low-cost storage tier once, this is how your new storage bill would look like: [500 GB x 4] = 2 TB in high-performance storage (instead of 26 TB) [6 TB x 1] in the low-cost storage tier The above storage bill would come up to approximately $480 /month: This is the savings' multiplication effect of Tiered Storage. Especially if you’re running replicas or forks, it is a great idea to take advantage of the low-cost storage tier—the savings you’ll see in your overall bill will be very significant. you would save over $4,000/month! Our Tiered Storage Journey We released an early version of our Tiered Storage functionality in the Timescale platform as Early Access in March. After many improvements, it has now reached General Availability. Since its first release, we have worked hard to make Tiered Storage stable, reliable, and faster. We also thought long and hard about our pricing model, iterating consistently over multiple ways to price our low-cost storage tier. Finally, we leaned towards the simplest: a flat rate over the data volume pre-compression. We’ve already mentioned how we valued simple and predictable pricing, but it was also important for us to provide a price per GB/month as low as possible. Our price landed at $0.021—as a comparison, storing files on Amazon S3 costs . $0.023 per GB/month Personally, I (Yannis) must admit that after leading teams building cloud-native solutions for more than a decade, I still have to go back and occasionally recheck multiple rate tables on various cloud pricing pages, especially looking for extra fees, every time that I want to compute the total costs for our services accurately. At Timescale, we believe that you should not have to build a complicated spreadsheet to be able to run a database service or make informed choices about storage layers. Our commitment to making developers’ lives easier led us to the flat rate of $0.021 per GB/month—no guesswork, hidden costs, or charges per query or data reads. means the data volume before applying Timescale compression. For example, due to Timescale compression, a 500 GB volume in the high-performance storage tier might end up needing only 50 GB of disk space once compressed. If you decided to tier this data to low-cost storage, your bill would be calculated over the original 500 GB volume, as in [500 GB * $0.021] per month. Data volume pre-compression How Tiered Storage Works: Behind the Scenes All data inserted into Timescale is initially written into our high-performance storage layer. Using faster disks for your most recent data will bring top insert and query performance for your most recent values, a usage pattern that fits the needs of data-intensive applications. On the contrary, the low-cost storage tier is an object store built on Amazon S3. This object store is much more than an external bucket to archive your data, though: it’s an integral part of your database. When you move data to this object storage tier, your database will remain fully aware of all the semantics and metadata, and you can keep querying as usual with standard SQL (albeit with slower performance). Behind the scenes, we’re storing the data in a compressed columnar format (specifically, ). When data is tiered, chunks stored in Timescale’s native internal database format (typically in our ) are asynchronously converted to Parquet format and stored in the underlying S3 object. We’ve built several mechanisms to ensure that the tiered chunks are durably stored in the low-cost storage tier before transactionally removing them from the high-performance tier. Apache Parquet native columnar compression When you run your SQL query, it will transparently pull data from the high-performance storage tier, the object storage tier, or both, as required. When we say transparent, we mean transparent: Timescale supports arbitrarily complex queries across its standard and tiered data, including complex predicates, JOINs, CTEs, windowing, hyperfunctions, and more. In the example query below (with an clause), you can see how the query plan includes a when the database is accessing data from the object storage tier. In this example, and are standard Postgres tables (residing only in high-performance storage), while is a hypertable that stretches across both storage tiers.  When executing this query against the hypertable, three of its chunks were read from standard storage, and five chunks were read from object storage. EXPLAIN Foreign Scan devices sites metrics metrics EXPLAIN 
SELECT time_bucket('1 day', ts) as day,
        max(value) as max_reading, 
        device_id  	
    FROM metrics 
    JOIN devices ON metrics.device_id = devices.id 
    JOIN sites ON devices.site_id = sites.id
WHERE sites.name = 'DC-1b'
GROUP BY day, device_id
ORDER BY day;


QUERY PLAN                                                      
----------------------------------------------------------
GroupAggregate
    Group Key: (time_bucket('1 day'::interval, _hyper_5666_706386_chunk.ts)), _hyper_5666_706386_chunk.device_id
    -> Sort
        Sort Key: (time_bucket('1 day'::interval, _hyper_5666_706386_chunk.ts)), _hyper_5666_706386_chunk.device_id
        -> Hash Join
            Hash Cond: (_hyper_5666_706386_chunk.device_id = devices.id)
            -> Append
    -> Seq Scan on _hyper_5666_706386_chunk
                -> Seq Scan on _hyper_5666_706387_chunk
                -> Seq Scan on _hyper_5666_706388_chunk
                -> Foreign Scan on osm_chunk_3334
            -> Hash
                -> Hash Join
                    Hash Cond: (devices.site_id = sites.id)
                    -> Seq Scan on devices
                    -> Hash
                        -> Seq Scan on sites
                           Filter: (name = 'DC-1b'::text) In the above query plan, the corresponds to fetching data from the bottomless object storage tier. Foreign Scan on osm_chunk_3334 To avoid processing chunks falling outside the query’s time window, we perform chunk exclusion to only touch the chunks that are needed to satisfy the query. The database stores various forms of metadata to build a “map” of row groups and columnar offsets within object storage. Furthermore, when a query is run, it is further selective about the data it reads. If your query only touches a range of rows and a few columns, only that subset of the data is read from the S3 object behind the low-cost storage tier. In the above, for example, only the and columns are read from the object storage tier.  If an additional  time-based filter had been included, the database would first use its metadata (stored in high-performance storage and cached in memory) to further reduce which Parquet files and row groups need to be read to execute the query.  All to reduce query latency even when accessing this bottomless storage transparently through PostgreSQL. device_id value WHERE Give Tiered Storage a Try Storage decisions around historical data don’t have to be costly. In Timescale, you now have access to a low-cost, infinite storage tier with no pricing gotchas that allows you to scale your database storage for an affordable price without compromising the performance of your application. If you’re wondering if this is a good solution for your use case, If you’re looking for a more practical perspective, you can also experiment with . check out our Docs for more information and use case fits. Tiered Storage yourself for free in the Timescale platform via our free trial (no credit card required) - Written by Yannis Roussos, Carlota Soto, and Ana Tavares. Also published here.

Scaling PostgreSQL Databases Just Got Cheaper: Timescale's Tiered Storage Hits General Availability

স্কেলিং PostgreSQL ডাটাবেসগুলি সবেমাত্র সস্তা হয়েছে: টাইমস্কেলের টায়ার্ড স্টোরেজ সাধারণ উপলব্ধতাকে আঘাত করে

Die Skalierung von PostgreSQL-Datenbanken ist jetzt noch günstiger: Der mehrstufige Speicher von Timescale ist jetzt allgemein verfügbar

Escalar bases de datos PostgreSQL ahora es más barato: el almacenamiento por niveles de Timescale llega a la disponibilidad general

La mise à l'échelle des bases de données PostgreSQL est devenue moins chère : le stockage hiérarchisé de Timescale arrive en disponibilité générale

PostgreSQL डेटाबेस को स्केल करना अब सस्ता हो गया है: टाइमस्केल का टियर स्टोरेज सामान्य उपलब्धता को प्रभावित करता है

PostgreSQL データベースのスケーリングがさらに安価に: Timescale の階層型ストレージが一般提供開始

PostgreSQL 데이터베이스 확장 비용이 더 저렴해졌습니다. Timescale의 계층형 스토리지가 정식 출시되었습니다.

O dimensionamento de bancos de dados PostgreSQL ficou mais barato: o armazenamento em camadas do Timescale atinge a disponibilidade geral

Масштабирование баз данных PostgreSQL стало дешевле: многоуровневое хранилище Timescale стало общедоступным

PostgreSQL Veritabanlarını Ölçeklendirmek Artık Daha Ucuz: Timescale'in Katmanlı Depolaması Genel Kullanıma Çıktı

Mở rộng cơ sở dữ liệu PostgreSQL vừa rẻ hơn: Bộ lưu trữ theo cấp độ của Timescale đạt mức khả dụng chung

扩展 PostgreSQL 数据库变得更加便宜：Timescale 的分层存储全面上市

Handling frequency in machine learning One of the trickiest situations in machine learning is when you have to deal with datasets coming from different time scales. Let’s say, for example, that you are handling financial data, and some of the data is collected monthly (e.g. sales reports), and some other data comes at a daily frequency (e.g. stock market prices). How can you create a model that utilizes both pieces of information at the same time? One solution is to try and create aggregates of the higher frequency features. So, for example, in this case, you can aggregate the daily frequency features on a monthly level using functions like the mean, and the . However, this makes you use lose information, and it might be a suboptimal solution. standard deviation A better solution can arrive through the use of . deep learning Deep learning for handling data with different frequency Using deep neural networks it is possible to do this vert smoothly. You can create two subnetworks: one network reads the daily data, and the other network reads the monthly data. The outputs of the two subnetworks are then joined together before they are passed into another layer. The code below shows how you could do this for the two datasets we outlined above. import pandas as pd from tensorflow import keras from tensorflow.keras import layers from tensorflow.keras.layers import X_day=pd.read_csv('day_data.csv') X_month=pd.read_csv('month_data.csv')
day_input = keras.Input(shape=day_X.shape[1:3], name="day_input")
monthly_input = keras.Input(shape=month_X.shape[1], name="monthly_input")
x1 = LSTM(50)(day_input)
x2 = Dense(num_units,activation='elu')(monthly_input)
merging=Concatenate()([x1,x2])
x = Dense(100,activation='elu')(merging) x = BatchNormalization()(x) 
x = Dropout(0.2)(x) y = Dense(3,'softmax')(x) The benefit of using deep learning The benefit of using deep learning, in this case, is that you are not losing any information which would have been lost otherwise by aggregating features together. This is done through the use of a layer, like or , which can read sequential data. We are simply using a dense layer for the monthly data (processing each month in one batch), and then we can merge the two subnetworks together. LSTM, GRU 1d convolution So, make sure to check this trick out next time you are faced with this problem! Also published here.

Merging Datasets from Different Timescales

How PostgreSQL Aggregation Inspired Timescale Hyperfunctions’ Design

*Disclaimer: I have no affiliation with any of the companies discussed in this post There’s a timeless phenomenon in business where strong product teams struggle to market and sell their awesome goods. It’s a travesty, really. In a perfect world sales and marketing would not exist. People would be instantly matched with the products they need. Creators would instantly get their product in the hands of ideal customers. However… that’s not the world that we live in. Not only is it hard to find the solutions we need, but often we don’t know what we want or need in the first place. Thus, sales and marketing are an essential and complimentary counterpart to product teams. Which brings us to the main topic of discussion in today’s bite-sized marketing case study. I’d like to break down a company called It’s a good example of how a product-first company can improve their go-to-market at the Series A stage. Timescale. Caveat. I am not a developer so a lot of these product details are frankly over my head. What I do know, is how to market and sell products. And it’s very obvious that this company is product and tech first at the expense of marketing. Just look at their top level navigation on their website: Learn Pricing Github Docs Blog Support Get Started I mean, Github is in their main navigation! No shade to their core mission, but we build businesses to sell products, and I think we can do one better here. Really, I’d like Timescale to take a page from ’s book. Red Hat It’s a nice comparision because both companies sell and implement open source software to enterprise. Here’s the main navigation you see on Red Hat’s website: Products Solutions Services & Support Open Source The difference is huge. Now, what I love about Timescale is that their core value is simple, and communicated well. It’s a better way to run a database that uses time-series data. Boom. There we go. There are also a ton of high profile logos featured prominently on Timescale. This is also good. So where do we go from here? Here’s what I’d like to see on Timescale. First of all, Red Hat asks users to set up an account before they get access to anything. This is super important for lead generation for an in-house sales team. And, in helping users understand how best to utilize their new technology. And I especially want to highlight that “driving leads” to a “salesforce” doesn’t have to be a bad thing. It’s important to have a conversation with all users to nurture even freemium relationships. This is how marketers and sales teams educate users and open up the possibility of a sale. And after the close, this is how we can ensure continued success, , and referrals down the line. expansion revenue In software, onboarding is super important as a poor implementation can kill the deal. I’d bet $100 that is the case with Timescale as well. Perhaps even more so than “typical” SaaS companies. Moving on, I’d like to see Timescale package their product into more of a “solutions” forward approach. Red Hat does this well. It’s presented on Timescale’s home page as well, but sort of buried down the page. Not optimal. The key thing with showing solutions and not just technology is that these categories reflect the different buyer personas that are a good fit for Timescale’s technology. This shows people at a glance if they should be interested in the company and hooks them before they bounce. Finally, let’s talk about service offerings. To my understanding, a big part of how companies like Red Hat make money in an open source environment is through services revenue. This follows the tradition of predecessor companies like Oracle. A great (read: high margin) business model, even if Oracle doesn’t get a lot of love for it. Now, let’s talk a bit about growth channels. It’s obvious that these guys are selling to developers. And I don’t think it makes sense to try to market to any other job function. You simply need a certain level of technical knowledge to know that you need this tool. So, the name of the game for growth is to get this product in the hands of as many developers as possible who need to process time-series data. There is going to be a subset of the market that has a pressing issue with this problem and knows they need a solution. For those people, a few obvious channels come to mind. Google, of course. Both for Ads and organic search. There is a big play to be made here with content marketing. I like that they’re already running a blog that’s driving half the traffic to their website. Good moves. Youtube, as well. Especially for people looking for visual walkthroughs of code. Given their strong product team and technical talent, it makes sense to take an educational approach to video content that leverages the expertise of their team. It’s a low commitment to create content, and easy to keep up a frequent publishing schedule. Next up: Stack Overflow. I think they could command a strong organic presence here. But, I also discovered as I was looking into this, that SO offers advertising. Seems like a no-brainer channel to me. Also, Quora. For queries into time-series data, seems great. Needs a little bit of digging into search volume here but could be viable.For other channels that are not so bottom-of-the-funnel… LinkedIn. Developers in their target audience are for sure on this channel. Especially at bigger enterprise companies where a professional profile is expected. The tricky thing will be creating the right content, offers, and hooks to put in front of this channel as LinkedIn can be very expensive. Further, the LTV of their enterprise product has to support these high aquisition costs. If they are only making $500 LTV per sale, for example, LinkedIn might not be the best channel to focus on without a bigger war chest. Next up, conferences and events in the development space. Open source conferences for sure. Particularly any conference that might have enterprise-level attendees there. I’m certain there are dozens and dozens of events that have the right audience for their product. They should blitz event marketing as much as possible. Finally, sponsorship of coding courses related to PostgreSQL. Since this seems to be their core technology, it would pay to get into the core stack of developers early in their career. Later, when they go on to work for other companies, they’ll take their tool stack with them and sell into other organizations from the bottom up. I can’t tell you how many times I’ve recommended clients tools like HubSpot, Unbounce, Shopify, Klaviyo, X Theme, and more. It’s just how technical operators work. Overall, we want to do some fundamental things with this technology as a freemium and open source tool. First, get in the hands of as many users as possible as their go-to tool for time-series data. Second, find awesome applications in enterprise organizations, and shout those logos and case studies from the rooftops. Finally, find cool projects using Timescale and spread the word to anyone in the development community. I’ll close with this — congratulations to these guys for closing their Series A round and for successes so far! With a bit stronger of a marketing team to compliment their product chops, I think they’ll see a lot of success in the next few years. Originally published at AndrewIshimaru.com .

How to Market and Sell Open Source Software ft. Timescale & Red Hat

Why PostgreSQL Is the Bedrock for the Future of Data

কেন PostgreSQL ডেটার ভবিষ্যতের ভিত্তি

Warum PostgreSQL das Fundament für die Zukunft der Daten ist

Por qué PostgreSQL es la base del futuro de los datos

Pourquoi PostgreSQL est le fondement de l'avenir des données

PostgreSQL डेटा के भविष्य का आधार क्यों है?

PostgreSQL がデータの未来の基盤となる理由

PostgreSQL이 데이터 미래의 기반인 이유

Por que o PostgreSQL é a base para o futuro dos dados

Почему PostgreSQL является основой будущего данных

PostgreSQL Neden Verinin Geleceğinin Temel Taşıdır?

Tại sao PostgreSQL là nền tảng cho tương lai của dữ liệu

为什么 PostgreSQL 是未来数据的基石

Strategies for Implementing Columnar Compression in Large PostgreSQL Databases

বড় পোস্টগ্রেএসকিউএল ডেটাবেসে কলামার কম্প্রেশন বাস্তবায়নের কৌশল

Strategien zur Implementierung der Spaltenkomprimierung in großen PostgreSQL-Datenbanken

Estrategias para implementar la compresión de columnas en grandes bases de datos PostgreSQL

Stratégies d'implémentation de la compression en colonnes dans les grandes bases de données PostgreSQL

बड़े PostgreSQL डेटाबेस में कॉलमर कम्प्रेशन लागू करने की रणनीतियाँ

大規模な PostgreSQL データベースに列圧縮を実装するための戦略

대규모 PostgreSQL 데이터베이스에서 열 형식 압축을 구현하기 위한 전략

Estratégias para implementar compactação colunar em grandes bancos de dados PostgreSQL

Стратегии реализации столбчатого сжатия в больших базах данных PostgreSQL

Büyük PostgreSQL Veritabanlarında Sütun Sıkıştırmasını Uygulama Stratejileri

Các chiến lược thực hiện nén cột trong cơ sở dữ liệu PostgreSQL lớn

在大型 PostgreSQL 数据库中实现列压缩的策略

Embedding data stored in a table is undoubtedly useful – with applications ranging from semantic search and recommendation systems, to generative AI applications and retrieval augmented generation. But creating and managing embeddings for data in PostgreSQL tables can be tricky, with many considerations and edge cases to take into account, such as keeping embeddings up to date with table updates and deletes, ensuring resilience against failures, and impact to existing systems dependent on the table. PostgreSQL for data residing in PostgreSQL using – our simple and resilient embedding creation system for data residing in PostgreSQL. Using a blog application with data stored in a PostgreSQL database as an example, we covered how to create and keep up-to-date vector embeddings using Python, LangChain, and Timescale Vector. In a previous blog post, we detailed a step-by-step guide on the process of creating and managing embeddings PgVectorizer In this blog post, we’ll discuss the technical design decisions and the trade-offs we made while building to ensure simplicity, resilience, and high performance. We’ll also discuss alternative designs if you want to roll your own. PgVectorizer Let’s jump into it. Design of a High-Performance Vectorizer for PostgreSQL Data (PgVectorizer) First, let’s describe how the system we are building will work. Feel free to skip this section if you already read the . PgVectorizer post System Overview As an illustrative example, we’ll use a simple blog application storing data in PostgreSQL using a table defined as follows: CREATE TABLE blog (
  id              SERIAL PRIMARY KEY NOT NULL,
  title           TEXT NOT NULL, 
  author          TEXT NOT NULL,
  contents        TEXT NOT NULL,
  category        TEXT NOT NULL,
  published_time  TIMESTAMPTZ NULL --NULL if not yet published
); We want to create embeddings on the contents of the blog post so we can later use it for semantic search and power retrieval augmented generation. Embeddings should only exist and be searchable for blogs that have been published (where the is ). published_time NOT NULL While building this embeddings system, we were able to identify a number of goals that any straightforward and resilient system that creates embeddings should have: This allows systems and applications that already use this table not to be impacted by changes to the embedding system. This is especially important for legacy systems. No modifications to the original table. Having to modify the code that alters the table may not be possible for legacy systems. It’s also poor software design because it couples systems that don’t use embeddings with code that generates the embedding. No modification to the applications that interact with the table. when rows in the source table change (in this case, the blog table). This lessens the maintenance burden and contributes to worry-free software. At the same time, this update need not be instantaneous or within the same commit. For most systems, “eventual consistency” is just fine. Automatically update embeddings Most systems generate embeddings via a call to an external system, such as the OpenAI API. In scenarios where the external system is down, or a network malfunction occurs, it's imperative that the remainder of your database system continues working. Ensure resilience against network and service failures: These guidelines were the basis of a robust architecture that we implemented using the , a library for working with vector data using PostgreSQL. To complete the job successfully, added new functionality to this library— —to make embedding PostgreSQL data as simple as possible. Timescale Vector Python library PgVectorizer Here’s the architecture we settled on: In this design, we first add a trigger to the blog table that monitors for changes and, upon seeing a modification, inserts a job into the blog_work_queue table that indicates that a row in the blog table is out-of-date with its embedding. On a fixed schedule, an embeddings creator job will poll the blog_work_queue table, and if it finds work to do, will do the following in a loop: Read and lock a row in the blog_work_queue table Read the corresponding row in the blog table Create an embedding for the data in the blog row Write the embedding to the blog_embedding table Delete the locked row in the  blog_work_queue table To see this system in action, see an example of the usage to . create and maintain embeddings in a PostgreSQL table using OpenAI, LangChain, and Timescale Vector in this blog post Going back to the example of our blog application table, on a high level, has to do two things: PgVectorizer Track changes to the blog rows to know which rows have changed. Provide a method to process the changes to create embeddings. Both of these have to be highly concurrent and performant. Let’s see how it works. Track change to the blog table with the blog_work_queue table You can create a simple work queue table with the following: CREATE TABLE blog_embedding_work_queue (
  id  INT 
);

CREATE INDEX ON blog_embedding_work_queue(id); This is a very simple table, but there is one item of note: this table has no unique key. This was done to avoid locking issues when processing the queue, but it does mean that we may have duplicates. We discuss the trade-off later in Alternative 1 below. Then you create a trigger to track any changes made to : blog CREATE OR REPLACE FUNCTION blog_wq_for_embedding() RETURNS TRIGGER LANGUAGE PLPGSQL AS $$ 
BEGIN 
  IF (TG_OP = 'DELETE') THEN
    INSERT INTO blog_embedding_work_queue 
      VALUES (OLD.id);
  ELSE
    INSERT INTO blog_embedding_work_queue 
      VALUES (NEW.id);
  END IF;
  RETURN NULL;
END; 
$$;

CREATE TRIGGER track_changes_for_embedding 
AFTER INSERT OR UPDATE OR DELETE
ON blog 
FOR EACH ROW EXECUTE PROCEDURE blog_wq_for_embedding();

INSERT INTO blog_embedding_work_queue 
  SELECT id FROM blog WHERE published_time is NOT NULL; The trigger inserts the ID of the blog that has changed into blog_work_queue. We install the trigger and then insert any existing blogs into the work_queue. This ordering is important to make sure that no IDs get dropped. Now, let’s describe some alternative designs and why we rejected them. Alternative 1: Implement a primary or unique key for the blog_work_queue table. Introducing this key would eliminate the problem of duplicate entries. However, it's not without its challenges, particularly because such a key would force us to use the clause to insert new IDs into the table, and that clause takes a lock on the ID in the B-tree. INSERT…ON CONFLICT DO NOTHING Here's the dilemma: during the processing phase, it's necessary to delete the rows being worked on to prevent simultaneous processing. Yet, committing this deletion can only be done after the corresponding embedding has been placed into blog_embeddings. This ensures no IDs are lost if there's a disruption midway—say, if the embedding creation crashes post-deletion but before the embedding is written. Now, if we create a unique or primary key, the transaction overseeing the deletion stays open. Consequently, this acts as a lock on those specific IDs, preventing their insertion back into the blog_work_queue for the entire duration of the embedding creation job. Given that it takes longer to create embeddings than your typical database transaction, this spells trouble. The lock would stall the trigger for the main 'blog' table, leading to a dip in the primary application's performance. Making things worse, if processing multiple rows in a batch, deadlocks become a potential problem as well. However, the potential issues arising from occasional duplicate entries can be managed during the processing stage, as illustrated later. A sporadic duplicate here and there isn't a problem as it only marginally increases the amount of work the embedding job performs. This is certainly more palatable than grappling with the above-mentioned locking challenges. Alternative 2: Track the work that needs to be done by adding a column to the blog table to track whether an up-to-date embedding exists. For example, we could add an boolean column set to false on modification and flipped to true when the embedding is created. There are three reasons to reject this design: embedded We don’t want to modify the table for the reasons we already mentioned above. blog Efficiently getting a list of non-embedded blogs would require an additional index (or partial index) on the blog table. This would slow down other operations. This increases churn on the table because every modification would now be written twice (once with embedding=false and once with embedding=true) due to the MVCC nature of PostgreSQL. A separate work_queue_table solves these issues. Alternative 3: Create the embeddings directly in the trigger. This approach has several issues: If the embedding service is down, either the trigger needs to fail (aborting your transaction), or you need to create a backup code path that … stores the IDs that couldn’t be embedded in a queue. The latter solution gets us back to our proposed design but with more complexity bolted on top. This trigger will probably be much slower than the rest of the database operations because of the latency required to contact an external service. This will slow down the rest of your database operations on the table. It forces the user to write the creation embedding code directly in the database. Given that the lingua franca of AI is Python and that embedding creation often requires many other libraries, this isn’t always easy or even possible (especially if running within a hosted PostgreSQL cloud environment). It’s much better to have a design where you have a choice to create embeddings inside or outside of the database. Now we have a list of blogs that need to be embedded, let’s process the list! Create the embeddings There are many ways to create embeddings. We recommend using an external Python script. This script will scan the work queue and the related blog posts, invoke an external service to craft the embeddings, and then store these embeddings back into the database. Our reasoning for this strategy is as follows: : We recommend because it offers a rich, unmatched ecosystem for AI data tasks, highlighted by powerful LLM development and data libraries like and LlamaIndex. Choice of Python Python LangChain : We wanted users to have control over how they embed their data. Yet, at the same time, many Postgres cloud providers don’t allow the execution of arbitrary Python code inside the database because of security concerns. So, to allow users to have flexibility in both their embedding scripts as well as where they host their database, we went with a design that used external Python scripts. Opting for an external script instead of PL/Python The jobs must be both performant and concurrency-safe. Concurrency guarantees that if jobs start running behind, the schedulers can start more jobs to help the system catch up and handle the load. We’ll go through how to set up each of those methods later, but first, let’s see what the Python script would look like. Fundamentally, the script has three parts: Read the work queue and the blog post Create an embedding for the blog post Write the embedding to the blog_embedding table Steps 2 and 3 are performed by an callback that we define in the . So, let’s look more deeply at how we process the work queue. embed_and_write PgVectorizer blog post Process the work queue We’ll first show you the code and then highlight the key elements at play: def process_queue(embed_and_write_cb, batch_size:int=10):            
    with psycopg2.connect(TIMESCALE_SERVICE_URL) as conn:
        with conn.cursor(cursor_factory=psycopg2.extras.DictCursor) as cursor:
            cursor.execute(f"""
                SELECT to_regclass('blog_embedding_work_queue')::oid; 
                """)
            table_oid = cursor.fetchone()[0]
            
            cursor.execute(f"""
                WITH selected_rows AS (
                    SELECT id
                    FROM blog_embedding_work_queue                         
                    LIMIT {int(batch_size)}
                    FOR UPDATE SKIP LOCKED
                ), 
                locked_items AS (
                    SELECT id, 
                           pg_try_advisory_xact_lock(
                                {int(table_oid)}, id) AS locked
                    FROM (
                        SELECT DISTINCT id 
                        FROM selected_rows 
                        ORDER BY id
                     ) as ids
                ),
                deleted_rows AS (
                    DELETE FROM blog_embedding_work_queue
                    WHERE id IN (
                        SELECT id 
                        FROM locked_items 
                        WHERE locked = true ORDER BY id
                   )
                )
                SELECT locked_items.id as locked_id, {self.table_name}.*
                FROM locked_items
                LEFT JOIN blog ON blog.id = locked_items.id
                WHERE locked = true
                ORDER BY locked_items.id
            """)
            res = cursor.fetchall()
            if len(res) > 0:
                embed_and_write_cb(res)
            return len(res)

process_queue(embed_and_write) The SQL code in the above snippet is subtle because it is designed to be both performant and concurrency-safe, so let’s go through it: : Initially, the system retrieves a specified number of entries from the work queue, determined by the batch queue size parameter. A FOR UPDATE lock is taken to ensure that concurrently executing scripts don’t try processing the same queue items. The SKIP LOCKED directive ensures that if any entry is currently being handled by another script, the system will skip it instead of waiting, avoiding unnecessary delays. Getting items off the work queue : Due to the possibility of duplicate entries for the same blog_id within the work-queue table, simply locking said table is insufficient. Concurrent processing of the same ID by different jobs would be detrimental. Consider the following potential race-condition: Locking blog IDs Job 1 initiates and accesses a blog, retrieving version 1. An external update to the blog occurs. Subsequently, Job 2 begins, obtaining version 2. Both jobs commence the embedding generation process. Job 2 concludes, storing the embedding corresponding to blog version 2. Job 1, upon conclusion, erroneously overwrites the version 2 embedding with the outdated version 1. While one could counter this issue by introducing explicit version tracking, it introduces considerable complexity without performance benefit. The strategy we opted for not only mitigates this issue but also prevents redundant operations and wasted work by concurrently executing scripts. A Postgres advisory lock, prefixed with the table identifier to avoid potential overlaps with other such locks, is employed. The variant, analogous to the earlier application of SKIP LOCKED, ensures the system avoids waiting on locks. The inclusion of the ORDER BY blog_id clause helps prevent potential deadlocks. We’ll cover some alternatives below. try : The script then deletes all the work queue items for blogs we have successfully locked. If these queue items are visible via Multi-Version Concurrency Control (MVCC), their updates are manifested in the retrieved blog row. Note that we delete all items with the given blog ID, not only the items read when selecting the rows: this effectively handles duplicate entries for the same blog ID. It's crucial to note that this deletion only commits after invoking the embed_and_write() function and the subsequent storage of the updated embedding. This sequence ensures we don’t lose any updates even if the script fails during the embedding generation phase. Cleaning up the work queue In the last step, we fetch the blogs to process. Note the use of the left join: that allows us to retrieve the blog IDs for deleted items that won’t have a blog row. We need to track those items to delete their embeddings. In the callback, we use published_time being NULL as a sentinel for the blog being deleted (or unpublished, in which case we also want to delete the embedding). Getting the blogs to process: embed_and_write Alternative 4: Avoid using advisory locks by using another table. If the system already uses advisory locks and you are worried about collisions, it’s possible to use a table with a blog ID as the primary key and lock the rows. In fact, this can be the blog table itself if you are sure these locks won’t slow down any other system (remember, these locks have to be held throughout the embedding process, which can take a while). Alternatively, you can have a blog_embedding_locks table just for this purpose. We didn’t suggest creating that table because we think it can get quite wasteful in terms of space, and using advisory locks avoids this overhead. Conclusion and Next Steps and automatically keeping them up to date. This architecture ensures the embeddings remain synchronized with the perpetually evolving data, responding seamlessly to insertions, modifications, and deletions. We introduced PgVectorizer and outlined a system adept at generating vector embeddings from data stored in PostgreSQL In this blog post, we gave you a behind-the-scenes look at how we created a system that boasts resilience, effectively handling potential downtimes of the embedding-generation service. Its design is adept at managing a high rate of data modifications and can seamlessly use concurrent embedding-generation processes to accommodate heightened loads. Moreover, the paradigm of committing data to PostgreSQL and using the database to manage embedding generation in the background emerges as an easy mechanism to supervise embedding upkeep amidst data modifications. A myriad of demos and tutorials in the AI space focus singularly on the initial creation of data from documents, overlooking the intricate nuances associated with preserving data synchronization as it evolves. However, in real production environments, data invariably changes, and grappling with the complexities of tracking and synchronizing these shifts is no trivial endeavor. But that’s what a database is designed to do! Why not just use it? : Here are some resources to continue your learning journey : Store the embeddings generated from your PostgreSQL data in a fast and scalable vector database built on PostgreSQL. Learn more and . Try Timescale Vector free for 90 days about Timescale Vector how it performs : Learn more about PgVectorizer and how to use it via the . Read the docs timescale_vector Python library : Follow this step-by-step tutorial on how to create, embed, and store blog post data from a PostgreSQL table using the methods discussed in this blog post. Tutorial – Embedding blog data in PostgreSQL We used LangChain to illustrate document parsing and embedding creation in our examples above. Learn more about how to use it with Timescale Vector for vector storage, similarity search, and hybrid search. : LangChain and Timescale Vector Written by Matvey Arye. Also published here.

Architecting a Robust Vector Embedding Creation System for PostgreSQL Data 

PostgreSQL ডেটার জন্য একটি শক্তিশালী ভেক্টর এমবেডিং ক্রিয়েশন সিস্টেম আর্কিটেক্ট করা

Architektur eines robusten Vektoreinbettungs-Erstellungssystems für PostgreSQL-Daten

Diseño de un robusto sistema de creación de incrustaciones de vectores para datos PostgreSQL

Architecture d'un système de création d'intégration de vecteurs robuste pour les données PostgreSQL

PostgreSQL डेटा के लिए एक मजबूत वेक्टर एंबेडिंग क्रिएशन सिस्टम का आर्किटेक्चर

PostgreSQL データ用の堅牢なベクトル埋め込み作成システムの構築

PostgreSQL 데이터를 위한 강력한 벡터 임베딩 생성 시스템 설계

Arquitetando um sistema robusto de criação de incorporação de vetores para dados PostgreSQL

Архитектура надежной системы создания векторных вложений для данных PostgreSQL

PostgreSQL Verileri için Sağlam Bir Vektör Gömme Oluşturma Sistemi Mimarisi

Kiến trúc một hệ thống tạo nhúng vectơ mạnh mẽ cho dữ liệu PostgreSQL

为 PostgreSQL 数据构建鲁棒的向量嵌入创建系统

Database pricing models are hard. As a developer looking for a managed database, one of the most annoying (and yet crucial) aspects of the search process involves evaluating not only the upfront price of the solution for your database size but also how the pricing works and how well it will scale. On top of the nuances when evaluating database pricing (e.g., “How much does the bill increase as my data grows?”, “Am I being charged per writes or reads?”, and “Do I have to pay more per backups?”), developers tend to overlook one aspect: the way a database's pricing model is structured will influence how you manage your data and assess your Postgres database size. Different pricing models require different approaches to running PostgreSQL. For example, if you’re locked into a large disk, you might not prioritize reducing your database size. If you’re charged per data read, you might think twice about running certain queries, and if you’re charged per data ingress, you may be cautious about the frequency and volume of newly ingested data. Each pricing element subtly influences the strategies and behaviors you’ll end up adopting, pushing you toward a particular way of managing and interacting with your database to ensure both cost-efficiency and optimal performance. , with great customer feedback. In this blog post, we’ll explore the operational advantages we’ve observed from our customers since transitioning to this model, together with tactics on how to optimally manage your PostgreSQL storage in a usage-based model. At Timescale, we transitioned to a usage-based storage model a few months ago Usage-based storage models are becoming increasingly popular in the database world—who wants to pay for disk space they’re not using? Still, a usage-based model doesn’t come without consequences, and you need to consider some best practices to navigate it effectively. Quick Recap on PostgreSQL Storage Models To lay out a common ground for our discussion on managing your database size in a usage-based model, let’s start by quickly covering how pricing works in our PostgreSQL platform ( ) and how it compares to other managed PostgreSQL products, like Amazon RDS for PostgreSQL and Amazon Aurora. Timescale Starting yesterday (💥), we offer two types of database services in the Timescale platform: , where developers can create PostgreSQL databases with dynamic compute to save money without sacrificing performance. Dynamic PostgreSQL services Time Series services, where developers can create PostgreSQL databases boosted with extra performance and scalability via hypertables, columnar compression, , and tiered storage. continuous aggregates Let’s focus on how we price storage on our platform. Both these services come with a usage-based model for storage, which means the following: Developers are charged for the volume they use, without small print—no minimum database size, no minimum scaling steps. If, by the end of the month, you’ve used 128 GB, that’s what you’ll be billed for. You can start at 1 GB and grow to TBs. There are no charges based on data written, data transfers, or queries run. No need to allocate storage when creating a database or scaling. No need to manually downsize disks. To bring this home, let’s lay out the differences between this model, Amazon RDS PostgreSQL, and Amazon Aurora: In summary, here are the three main takeaways from our comparison: Both Timescale and Aurora charge for your actual database size. In contrast, RDS PostgreSQL charges for provisioned storage. You can’t downsize volumes in RDS. Timescale does not charge for data written, data transfers, or query operations. Both RDS and Aurora charge per data transfer, extra backup storage, and you might incur some extra I/O charges, depending on which type of storage you’re picking. Timescale has no minimum scaling steps, whereas Aurora scales in 10 GB increments after starting with 10 GB. As you can see, Timescale’s model is closer to , with the difference that Timescale lacks scaling steps and extra charges for things like data transfer. In contrast, RDS is a good representation of the allocation-based model, even if traditionally found in database vendors operating on this model. Aurora I/O-Optimized RDS lacks the “storage tiers” How Usage-Based Pricing Improves PostgreSQL Storage Management As we previously introduced, different pricing models imply different database experiences. When we transitioned from an allocation-based to a usage-based model, our customers told us they saw immediate improvements in three operational areas: They didn’t need to provision storage upfront when starting a database, which led to fewer overprovisioning mistakes and, therefore, lower storage bills. They didn’t have to think about storage while scaling up. They could downscale—e.g., if they deleted data, they stopped paying for it. Usage-based models eliminate the problem of storage overprovisioning In traditional allocation-based models, developers often find themselves predicting their storage needs, which, very often, leads to storage overprovisioning. We observed this across our fleet when Timescale operated on a usage-based model: the majority of our customers were not using their full disk capacity, which means they were essentially paying for storage space they weren't yet using. Usage-based models eliminate this guessing game (and the consequences of wrong guesswork). You don’t need to think about storage while scaling up “Timescale's usage-based storage means I don't have to think about disk size anymore. Our storage cost is down 30 %, and I didn't have to do anything!" Adam McCrea, (Timescale customer) Judoscale In usage-based models, the storage scales seamlessly as your database grows. A main source of stress in traditional allocation-based models is the danger of running out of disk space, which can lead to numerous problems ranging from application downtime and lost transactions to data corruption in the worst-case scenarios. With usage-based models, developers no longer have to vigilantly monitor their storage to avoid hitting a storage wall. At the same time, they don’t have to worry about heavy autoscaling steps or tier jumps. You can downscale (stop paying for data you deleted) Last but not least, usage-based models allow you to “downscale.” If you delete data (or manage to reduce your database size considerably), you start paying less per storage, which sounds only fair. As we’ll see in the next section, Timescale has a few features to help customers reduce their PostgreSQL database size. Adopting a usage-based model allowed our customers to immediately realize savings when reducing disk usage, which incentivized them to keep their database lean. How to Effectively Navigate a Usage-Based Model: Tips to Reduce Your Postgres Database Size The benefits we just mentioned improve the developer experience of working with storage significantly, which is why usage-based models are becoming very popular. But usage-based pricing comes with an obvious (yet profound) consequence: it forces developers to adopt good data practices to reduce their PostgreSQL database size as much as possible. When you know that your storage costs are directly linked to the disk size you’re actually using, there’s a newfound incentive to be more judicious with data storage. If you’re operating in a usage-based model for storage, it becomes your responsibility to ensure you’re storing data efficiently. In a way, this can be considered a “drawback” of usage-based models, and it requires some work—but this is actually a blessing in disguise. In traditional allocation-based models, data hygiene can be somewhat overlooked because the costs are fixed: if you’re locked into a 500 GB disk in RDS, and your database is 200 GB, you don’t seem to have a big incentive to make storage usage efficient. But here’s the thing: good data practices are not just about saving money. To scale a PostgreSQL database, it is essential to keep it optimized. By adopting good PostgreSQL data management practices, you will not only see better performance but your life as a database administrator will get much easier. With this in mind, let’s run through some practices that will help you navigate a usage-based model for storage as effectively as possible, reducing your PostgreSQL database size in a systematic way. In the particular case of Timescale, we have some particularly helpful features, which we’ll cover as well. Reduce bloat in your PostgreSQL database A first must-do in a usage-based model is to pay attention to the specifics of how PostgreSQL storage works, i.e., you must reduce bloat on a regular basis. This will help you not only with your database size but also with your I/O requirements. We’ll point you toward some basics in this section, , and we’ve also written . but this HN thread has some great advice a blog post on table bloat in PostgreSQL A proactive approach to optimizing PostgreSQL storage begins with keeping a close eye on dead tuples. Dead tuples, if left unchecked, can accumulate and lead to unnecessary storage overheads. The extension can be a great ally to understanding how tables manage these tuples. Monitor dead tuples. pgstattuple() To effectively combat table bloat, you might want to configure autovacuum to run more frequently. Rather than globally adjusting autovacuum settings in postgresql.conf, it's more precise to fine-tune these settings on a per-table basis. This caters to the fact that different tables can have varying tendencies for accumulating dead tuples. It's also crucial to monitor long-running transactions that might hinder autovacuum operations. Vacuum frequently. While autovacuum and vacuum address dead tuples, reclaiming unused pages demands a different approach. Although VACUUM FULL can be utilized for this purpose, it has the inherent limitation of locking the entire table. can help you with this. Reclaim unused pages. Pg_repack Do some PostgreSQL fine-tuning Systematically reducing your PostgreSQL database size is closely related to being able to scale your PostgreSQL database effectively, i.e., keeping things fast and agile while you’re adding more and more data. Keeping an eye on (and perhaps adjusting) some key PostgreSQL parameters can help. . Here are some aspects you need to consider: This article reviews the most important performance parameters : controls the memory used for PostgreSQL’s page cache, and it’s typically set to 25 % of the system’s total RAM. shared_buffers : it sets the memory allocated per operation within a query. In Timescale, the recommended setting is . work_mem (25 % of RAM) / max_connections : it sets the max number of concurrent connections allowed to the database. Connection poolers can help manage many short-lived connections. max_connections : it determines the max number of worker processes PostgreSQL can initiate. If using Timescale, the formula to set this parameter is: . max_worker_processes max_worker_processes = 3 + timescaledb.max_background_workers + max_parallel_workers : it specifies the max number of workers supporting parallel queries. The default is to set this equal to the number of available CPUs. max_parallel_workers : it determines the max number of concurrent autovacuum processes. In Timescale, it is set to 10 by default. autovacuum_max_workers Practice indexing hygiene Regularly optimizing indexes will help keep your PostgreSQL efficient, especially as the database scales. While indexes can help you improve query performance when used wisely, excessive indexes can create issues in large PostgreSQL deployments. The most obvious consequence of excessive indexing is increased storage consumption, as every index necessitates separate storage, which grows proportionally with the size of the tables. This overhead can become more significant when tables are partitioned, like in Timescale’s hypertables. Unnecessary indexes can also be counterproductive in improving your write operations, as each new data entry or modification implies concurrent index updates, leading to more I/O operations and potential table bloat. Monitoring your indexes will help you identify which ones are no longer being used, keeping things lean. One way to do this is by using : pg_stat_user_indexes -- Retrieve indexes that might not be used or are infrequently used
SELECT
    schemaname,
    relname AS table_name,
    indexrelname AS index_name,
    idx_scan AS times_used
FROM
    pg_stat_user_indexes
WHERE
    idx_scan = 0
    OR idx_scan < some_low_threshold -- replace 'some_low_threshold' with a value that makes sense for your context
ORDER BY
    idx_scan ASC, schemaname, relname; If an index has a value of 0 in the idx_scan column, it means it hasn't been used since the last time the statistics were reset, meaning that it could be a candidate for optimization. By setting a low threshold for , you can also identify infrequently used indexes. idx_scan Shrink your large tables via Timescale compression One of Timescale's standout features is its native support for columnar compression, which can drastically reduce the disk space used by large tables without compromising query performance. Compression improves the performance of many queries. Timescale's compression works by converting regular row-based data into a more compact columnar format. This process is particularly effective for time-series data, where many columns may contain repetitive values; we can achieve impressive compression rates (+90 %) by applying different compression algorithms depending on each data type. This means that your large tables can be shrunk up to 10x. In Timescale, compression is enabled on a table-by-table basis via a time-based compression policy. For example, this code enables compression in a particular hypertable and automatically compresses data older than two weeks: -- Enable compression on the hypertable
ALTER TABLE your_hypertable SET (
  timescaledb.compress,
  timescaledb.compress_segmentby = 'some_column'
);
-- Set a compression policy to compress data older than two weeks
SELECT add_compression_policy('your_hypertable', INTERVAL '2 weeks'); To learn more about compression, . check out our documentation Set up a data management strategy: Move older data to cheaper storage, delete the data you no longer need The previous tactics are very helpful in reducing your database size, but there is another avenue you can leverage in Timescale to scale your storage effectively: tiered storage. By creating a simple tiering policy, you can move older, less-accessed data to a bottomless object storage layer: -- This policy moves all data older than one month to object storage 
SELECT add_tiering_policy('example', INTERVAL '1 month); This object store has the following characteristics: It comes with a lower price point than our high-performance storage, allowing you to store many TBs of data for much less. It is infinite, meaning you can store as much data as you’d like. This tiered storage architecture is ingrained in Timescale’s backend. The object storage is not a “bucket” detached from your database—instead, it’s an integral part of it. Queries will transparently access both storage layers without any action needed from you—you can just keep using standard SQL over the same schema. Once your tiering policy is set, there’s nothing else to consider! We recommend moving data to the low-cost storage layer once it’s not being frequently accessed by your application since there’s a performance cost (the object store is not as fast as our regular storage). But you can keep running ad-hoc queries over this data comfortably (e.g., queries that are not critical for the user experience of your customers and for which top performance is not essential). Editor’s note: We’ll share exciting news on tiered storage soon. 🎉 Stay tuned! On top of offering this low-cost storage layer, Timescale makes it easy to set up data retention policies to delete data you no longer need: -- Set a data retention policy to drop data older than 10 months
SELECT add_retention_policy('your_hypertable', INTERVAL '10 months'); You can combine data retention policies like the one above with continuous aggregates to effectively downsample your dataset—e.g., reducing granularity from one second to one minute, deleting the one-second values but keeping the one-minute aggregates. . Read this blog post to see an example of how to do this and proactively manage long-term data Usage-Based Models and the Data Management Paradigm While usage-based models may seem nothing but a pricing change on the surface, they bring about a paradigm shift in how developers think about their database size and how they perceive and handle data. Usage-based models promote a culture of continuous improvement, where the focus shifts from mere storage management to database health and efficiency. This requires some discipline at first, but once your mindset shifts and you learn some techniques, you’ll be in the best place to scale your PostgreSQL database efficiently and effectively. Timescale has valuable tools to help you systematically reduce your PostgreSQL database size, like compression, tiered storage, and data retention policies. This allows you to effectively scale your PostgreSQL databases in a usage-based model. . To experience it yourself, sign up for Timescale—you can use it for free for the first 30 days - Written by . Carlota Sota Also published here.

Strategies for Database Size Reduction in PostgreSQL: A Usage-Based Approach

PostgreSQL-এ ডাটাবেসের আকার হ্রাসের কৌশল: একটি ব্যবহার-ভিত্তিক পদ্ধতি

Strategien zur Reduzierung der Datenbankgröße in PostgreSQL: Ein nutzungsbasierter Ansatz

Estrategias para reducir el tamaño de la base de datos en PostgreSQL: un enfoque basado en el uso

Stratégies de réduction de la taille des bases de données dans PostgreSQL : une approche basée sur l'utilisation

PostgreSQL में डेटाबेस आकार में कमी के लिए रणनीतियाँ: एक उपयोग-आधारित दृष्टिकोण

PostgreSQL でのデータベース サイズ削減の戦略: 使用量ベースのアプローチ

PostgreSQL의 데이터베이스 크기 감소 전략: 사용량 기반 접근 방식

Estratégias para redução do tamanho do banco de dados no PostgreSQL: uma abordagem baseada no uso

Стратегии уменьшения размера базы данных в PostgreSQL: подход, основанный на использовании

PostgreSQL'de Veritabanı Boyutunu Küçültme Stratejileri: Kullanıma Dayalı Bir Yaklaşım

Các chiến lược giảm kích thước cơ sở dữ liệu trong PostgreSQL: Cách tiếp cận dựa trên việc sử dụng

PostgreSQL 中数据库大小减小的策略：基于使用的方法

Starting today, . you can create Dynamic PostgreSQL databases on Timescale Dynamic is the natural evolution of cloud databases, solving the problems of both provisioned databases and serverless databases. It is backed by dynamic compute, a Timescale innovation that instantaneously scales your available compute within a predefined min/max range according to your load. Instead of provisioning for your peak (and paying for it at all times), you can now pick a compute range: your database will operate at base capacity and instantly access up to its peak only when needed. Buy the base, rent the peak. PostgreSQL This results in unparalleled price performance: customers running production workloads will save 10-20 % when migrating from AWS RDS for PostgreSQL and 50-70 % when migrating from AWS Aurora Serverless. You can try out Dynamic PostgreSQL today. We offer a free trial—no credit card required—that gives you full access to the platform for 30 days. To create a Dynamic PostgreSQL service, just select the PostgreSQL option when logging into Timescale: Your application is always on, why shouldn’t your database be? Welcome to the future. Problem 1: Developers provision far more compute than they need For the past several years, ever since we first launched Timescale, we have had a front-row seat to how developers use databases. For example, just in the last few months, as part of our Insights product. we have analyzed over one trillion queries One thing we have learned is that developers often provision far more compute than they actually need. On the one hand, this makes sense: You never want to worry about your database. Most database workloads are continuous, typically with some variability or burstiness to them. For example, a game that has more usage at night, a business application that has more usage during the day, or a connected home device that has more usage on the weekends than during the week. You never want your database to run out of resources. If your database is at max capacity, that leads to a terrible customer experience (or no customer experience!). So, most developers end up provisioning for the peak, plus a buffer. This results in developers paying for much more compute than they actually need. On the other hand, this seems crazy to us. What other business resource would organizations be okay spending far more than what they need? Wasted compute equals wasted money. Problem 2: Serverless databases fall short for production workloads Some of you might be asking, “What about serverless databases?” The concept of serverless originated with stateless workloads. After the success of virtual machines in the cloud, where users could stop needing to worry about hardware, they next asked why even worry about running application servers at all? After all, many users just wanted to run functions and only be charged for the time those functions were running. And it’s easy and seamless to spin up functions as needed, almost precisely because they are stateless. Serverless—and function-as-a-service or FaaS—became a hit, with AWS Lambda taking over. Developers then asked themselves, “Why pay for my database when I’m not using it?” The actual question is good: wasted resources are a massive database problem. And the practice of provisioning an AWS RDS database on a specific server instance (say, a db.m6gd.2xlarge) sure doesn’t feel modern or flexible: fixed CPU, fixed memory, fixed local disk. Most of it is underutilized most of the time. But this is where things get tricky: databases are very different from Lambda functions. Serverless databases today are wrong for most production workloads for two main reasons: Serverless databases focus on the extremes for scaling up and down, even to zero. Serverless databases introduce much higher pricing to account for the resource “headroom” reserved to serve changing demands (and worse, often with pricing models that are hard to understand or predict). Let’s start by discussing the hot topic of “scale to zero.” The reality is that most production databases don’t need and won’t actually benefit from scaling to zero. Now, there are some use cases where “scale to zero” makes sense. For example, proof-of-concept demos or more hobbyist applications. The ability to occasionally run an ad-hoc query against your dataset (AWS Athena and Google BigQuery make a strong case for a low-cost, serverless cloud data warehouse for very intermittent use). Another suitable use case would be to avoid forgetting to spin down a cloud dev instance once finished—there’s value in “auto pausing” a non-production database (although that requires much simpler functionality than envisioned by serverless). But for your production database and in more operational settings? You don’t want to scale to zero. Scaling to zero means a “cold boot” on restart: empty database shared buffers, empty OS cache, empty catalog caches (in the case of PostgreSQL). (Yes, some serverless databases lower the time it takes to start the database running, but they do so from an empty state. In a relational database like PostgreSQL, it can take minutes (or longer!) to build a warm working set again, especially for larger databases.) The cold start performance hit is even greater as many serverless databases adopt different cloud storage architectures, where the cost and latency of fetching database pages from remote storage into memory is even greater. These overheads again lead to worse performance or force platform providers to compensate by using greater physical resources (e.g., Amazon Aurora databases have twice the memory of RDS), a cost that ultimately gets passed on to users. So, in many scenarios, serverless databases end up with higher and unpredictable pricing. For example, if you compare Aurora Serverless against Amazon RDS, you’ll see that 8 vCPU compute and 500 GB storage on Serverless is 85 % more expensive than RDS ($1,097 vs. $593). And this is using Aurora I/O Optimized and its more predictable storage prices, which launched just six months ago. (Although, even here, we still have to infer its actual compute capacity, as Aurora Serverless prices by confusing opaque “Aurora Capacity Units,” which to our best-informed estimates are 1 ACU = 0.25 vCPU.) We’ll be publishing a complete benchmark backing those results soon. Stay tuned. Editor’s note: Previously, with Aurora Standard, users would also pay for each internal I/O operation, which was nearly impossible to predict or budget. Many serverless databases continue to charge for such reads and writes.  In fact, when we benchmarked the serverless AWS Timestream, we saw than with Timescale due to all of these higher marginal costs.  The unpredictability and variability of costs were the opposite of worry-free. costs that ended up more than 100x higher In short, serverless databases are prone to poorer performance, unpredictable bills, and high costs as workloads scale. They are only well suited for intermittent workloads that spin up only occasionally and can tolerate cold starts with their lack of in-memory data caching. The developer dilemma This is where we have ended up: Many developers still choose traditional DBaaS services with provisioning for production applications due to their reliable performance, control, and understandability, but hate the waste that arises from the necessity of overprovisioning. Some developers choose serverless databases for their apparent cost savings, flexibility, and ease of use, but hate the performance hit and the unpredictable, obscure pricing (which often result in bills mysteriously higher than a provisioned instance). As developers ourselves, neither of these options is very appealing! There is an opportunity for better. Solution: Introducing Dynamic PostgreSQL That’s why we developed Dynamic PostgreSQL. Dynamic PostgreSQL consistently supports your baseline and seamlessly scales compute when you need it, up to a defined max. This makes it perfect for the range of continuous workloads you typically see in production settings (whether uniform, variable, or bursty). Dynamic PostgreSQL is 100 % PostgreSQL, with all the benefits of the PostgreSQL community and ecosystem, plus the . To build Dynamic PostgreSQL, we’ve innovated on how we operate our PostgreSQL infrastructure rather than modifying the internals of PostgreSQL. This gives you access to everything that PostgreSQL—and the Timescale platform—offers, without the fear of running on a forked PostgreSQL query or storage engine. maturity of Timescale’s database platform With Dynamic PostgreSQL, you choose a compute (a minimum and maximum CPU) corresponding to your workload needs. This compute range also comes with effective memory that is equivalent to what most DBaaS services traditionally offer on the “maximum” end of the compute range. range The base (minimum) of your CPU range acts exactly like the provisioned DBaaS model: the minimum CPU is at all times dedicated to your service to run your application. As your load increases—either due to your external application’s demand or even due to occasional internal database tasks like incremental backups or table auto-vacuuming—your database can use up to the peak (maximum) of your CPU range with zero delay. How do we achieve zero delay? Dynamic compute works differently than some other serverless or auto-scaling database offerings, so it doesn’t involve the slow scaling (and performance hit) you typically see from remote migrations. Instead, our infrastructure configuration and workload placement algorithms ensure that databases can scale up on their underlying node without restarts or reconfiguration. Your instance always has access to its maximum compute as needed. And the best part is that you only pay for the base plus what you use above it. We call this model of choosing a compute range and scaling between it “ .” buy the base, rent the peak For example, if you choose a 4–8 CPU option, you will always have 4 CPUs dedicated to your service and 32 GB of effective memory. This ensures good base performance at all times. When your load increases, your application can use up to 8 CPUs instantaneously as it needs—metered and billed on a fractional CPU basis—and never more than 8 CPUs if this is your max limit. The dynamic model allows you to “size” your database more cost-effectively and worry-free. You can choose a compute range where your standard demand fits the minimum, yet you can grow or spike up to the peak (maximum) as needed. This maximum creates an inherent cap on any usage above your base compute, leading to an easy-to-understand cost ceiling. Further, we charge the same rate per (fractional) CPU-hour for both your base and any metered usage over this: there is no upcharge for using above your base, and therefore no price penalty for scaling. Finally, if you realize you provisioned a size range that is too low or too high, you can easily adjust your compute range to a size that better suits your application’s needs. Engineered to save you money We currently offer five different compute ranges based on your workload size, with corresponding effective memory you receive for the range regardless of your instantaneous usage. Dynamic PostgreSQL also uses Timescale’s usage-based storage, where you only pay for the volume of data stored (in GB-hours), not for a provisioned disk size. No worrying about wasting money with an over-provisioned disk or similarly worrying that you’ll run out of disk space. Timescale’s dynamic cloud infrastructure ensures you have sufficient storage capacity, when you need it, and that you only pay for what you use. We have developed Dynamic PostgreSQL intentionally to save you money. Customers running production workloads typically save 10-20 % when migrating from AWS RDS for PostgreSQL and 50-70 % when migrating from AWS Aurora Serverless. At the end of the month, your bill consists of two simple, easy-to-understand metrics: (1) your compute costs, billed as your hourly base compute plus any fractional CPU usage above it but no more than your peak; and (2) your storage costs, billed as data consumption in GB-hours. There are no new metrics or derived units to measure or understand. Just pay for what you use. Zero extra costs or hidden fees. Compute: predictable, based on a defined range Storage: only pay for what you store No wasted resources. No overpaying. No losing sleep at night. A bill you can explain to your boss. Try it today You can try out Dynamic PostgreSQL today! —no credit card required—that gives you full access to the platform for 30 days. To create a Dynamic PostgreSQL service, just select the PostgreSQL option when logging into Timescale: Timescale offers a free trial The platform now offers two service types to serve the specific needs of your databases: are engineered to boost query speed and scalability for your most demanding workloads, offering key Timescale features such as hypertables, columnar compression, continuous aggregates, and tiered storage.  Use them to host your sensor data, energy metrics, financial data, events, and other data-intensive workload. Time-series services are dynamic Postgres services optimized for cost-efficiency and ease of use. Use them for your relational-only databases, e.g., business records. PostgreSQL services Once you select “PostgreSQL,” configuring your Dynamic PostgreSQL service is super simple. Select your region, your dynamic compute range, and your high availability and connection pooling options— boom! 💥 You now have a Dynamic PostgreSQL database ready to use in production. If you have any questions, . We’d love to hear your feedback and to help you with your PostgreSQL use case (time series or not)! reach out to us This is just the beginning. We’re in the middle of three consecutive launch weeks, and this is just the start of Week 2: Dynamic Infra Week. Stay tuned for more this week, this month, this year, and the many years to come. 🙂 - Written by Mike Freedman and Grant Godeke. Also published here.

Meet Dynamic PostgreSQL: the Natural Evolution of Cloud Databases

ডায়নামিক পোস্টগ্রেএসকিউএল-এর সাথে দেখা করুন: ক্লাউড ডেটাবেসের প্রাকৃতিক বিবর্তন

Lernen Sie Dynamic PostgreSQL kennen: die natürliche Entwicklung von Cloud-Datenbanken

Conozca Dynamic PostgreSQL: la evolución natural de las bases de datos en la nube

Découvrez Dynamic PostgreSQL : l'évolution naturelle des bases de données cloud

डायनामिक पोस्टग्रेएसक्यूएल से मिलें: क्लाउड डेटाबेस का प्राकृतिक विकास

動的 PostgreSQL の紹介: クラウド データベースの自然な進化

동적 PostgreSQL을 만나보세요: 클라우드 데이터베이스의 자연스러운 진화

Conheça o PostgreSQL Dinâmico: a evolução natural dos bancos de dados em nuvem

Встречайте Dynamic PostgreSQL: естественная эволюция облачных баз данных

Dinamik PostgreSQL ile Tanışın: Bulut Veritabanlarının Doğal Evrimi

Gặp gỡ Dynamic PostgreSQL: Sự phát triển tự nhiên của cơ sở dữ liệu đám mây

认识动态 PostgreSQL：云数据库的自然演变

Scaling PostgreSQL: How We Tamed 10 Billion Daily Records and 350 TB+ of Data

স্কেলিং PostgreSQL: কিভাবে আমরা 10 বিলিয়ন দৈনিক রেকর্ড এবং 350 TB+ ডেটা টেমড করেছি

Skalierung von PostgreSQL: Wie wir 10 Milliarden tägliche Datensätze und mehr als 350 TB Daten gezähmt haben

Escalado de PostgreSQL: cómo controlamos 10 mil millones de registros diarios y más de 350 TB de datos

Mise à l'échelle de PostgreSQL : comment nous avons maîtrisé 10 milliards d'enregistrements quotidiens et plus de 350 To de données

स्केलिंग पोस्टग्रेएसक्यूएल: हमने 10 बिलियन दैनिक रिकॉर्ड और 350 टीबी+ डेटा को कैसे नियंत्रित किया

PostgreSQL のスケーリング: 100 億の毎日のレコードと 350 TB 以上のデータをどのように管理したか

PostgreSQL 확장: 일일 기록 100억 개와 데이터 350TB 이상을 관리한 방법

Dimensionando o PostgreSQL: como controlamos 10 bilhões de registros diários e mais de 350 TB de dados

Масштабирование PostgreSQL: как мы укротили 10 миллиардов ежедневных записей и более 350 ТБ данных

PostgreSQL'i Ölçeklendirme: 10 Milyar Günlük Kaydı ve 350 TB+ Veriyi Nasıl Ele Geçirdik?

Mở rộng quy mô PostgreSQL: Cách chúng tôi thuần hóa 10 tỷ hồ sơ hàng ngày và hơn 350 TB dữ liệu

扩展 PostgreSQL：我们如何驯服 100 亿条每日记录和 350 TB 以上的数据

Enabling users to access real-time data analytics is a key capability of many modern applications. Picture yourself using your favorite platform—there’s likely an intuitive dashboard presenting real-time data and historical insights. You can probably interact with the platform, creating customized reports, exploring detailed metrics, and visualizing trends spanning weeks or months. SaaS You certainly wouldn’t want this platform to be slow as a user. This means that the database powering these products has to be fast at running queries over large volumes of data, including complex, analytical queries. While , it is not known for being fast at querying large volumes of data. But don’t worry: always has a tool in its toolbox. One of the best is materialized views. PostgreSQL is the most loved database among developers today Postgres What Are PostgreSQL Materialized Views? Based on the materialization technique, PostgreSQL materialized views pre-compute commonly run queries and store the results as a table. Unlike standard PostgreSQL views, which run the underlying query every time the view is referenced, materialized views persist the result of the source query in the database. The great thing about this is that your database doesn’t have to execute the query every time you run it: the results are already accessible on disk—you’ll get the response to your query much faster. This is an awesome way to optimize query responses for queries that are resource-intensive to compute. For example, queries that might involve the processing of large volumes of data, aggregations, or multiple joins. Working with materialized views is super simple. To create a view, you would use statement and your query of choice. CREATE MATERIALIZED VIEW Once your materialized view is created, you can query it as a regular table: PostgreSQL CREATE MATERIALIZED VIEW customer_orders AS 
SELECT customer_id, COUNT(*) as total_orders 
FROM orders 
GROUP BY customer_id; -- Query the materialized view 
SELECT * FROM customer_orders; This materialized view will quickly become stale until you refresh it: even if you’re adding new data to the base table (or updating or deleting data), the materialized view doesn’t include those changes automatically; it’s a snapshot at the time it was created. To update the materialized view, you need to run . REFRESH MATERIALIZED VIEW REFRESH MATERIALIZED VIEW customer_orders; This last point (how refreshes are handled) is the Achilles heel of materialized views, as we’ll discuss in the next section. What About Real-Time Analytics? Limitations of PostgreSQL Materialized Views As we were saying, PostgreSQL materialized views are a powerful tool for speeding up frequently run queries, especially if these queries go over large volumes of data. But materialized views come with one less-than-ideal aspect: to keep your materialized views up-to-date, they have to be refreshed. This single problem creates three important limitations: Refreshes are inefficient and computationally expensive When refreshing a materialized view, the query is recomputed over the entire dataset. Under the hood, when you run a refresh, the old materialized data is deleted and then substituted with new, re-materialized data. Implementing (where only the changed data is updated) would make the aggregate process much more efficient, but it is difficult to implement correctly within a consistent relational database. Workarounds are sometimes possible with additional scripting, but they are far from straightforward, especially for complex queries or if late data is arriving. incremental refreshes Refreshes aren’t automatically run As also mentioned earlier, materialized views won’t automatically incorporate the latest data. They have to be refreshed by running . Running manual refreshes in a production setting is not feasible: a much more realistic setup would be to automate the refresh. REFRESH MATERIALIZED VIEW Unfortunately, materialized views don’t have built-in automatic refresh functionality, so creating an automatic refresh schedule for materialized views in PostgreSQL requires a scheduler of some sort. This can be handled in-database with an extension or out-of-database with a scheduler like cron. However, it’s managed because refreshes are expensive and take a long time. It is very easy to end up in a situation where you can’t refresh the view fast enough. Materialized views don’t show up-to-date results A consequence of the static nature of materialized views is that when queried, they’ll miss the data added or changed since the last refresh (even if that refresh happens on a schedule). If your scheduling window is set to an hour, then your aggregate will be up to an hour plus the actual time to do the update out of date. But many applications today imply a constant stream of data being ingested, and often, these applications have to offer up-to-date results to their users to ensure they’re retrieving accurate information when querying the view. It is a pity that materialized views are constrained by these limitations. If you’re building a SaaS platform from a live dataset, with new data frequently coming in, should materialized views be completely discarded? The answer is no. In Timescale, we built a solution that effectively enhances materialized views to make them more suitable for modern applications: continuous aggregates. Meet Continuous Aggregates: Materialized Views With Automatic Refreshes for Real-Time Analytics Imagine a world where materialized views are not just static snapshots but dynamically and efficiently updated. You would access the query performance improvement you seek without worrying about anything else. Well, it seems like we described Timescale’s continuous aggregates. Continuous aggregates (available to all PostgreSQL databases via the TimescaleDB extension and in AWS via the Timescale platform) are materialized views enhanced with efficient, automated refresh capabilities and a real-time element. They look and feel almost exactly like materialized views but allow the following: Automatic refreshes via a refresh policy A more efficient refresh process: when a refresh runs, it will only touch the data that changed since the last refresh Up-to-date results, expanding the use cases where materialized views can be leveraged (like real-time analytics, live dashboards, reporting, and others) Making refreshes automatic and resource-efficient Creating a continuous aggregate is very similar to creating a materialized view (and it can also be queried as a regular PostgreSQL table): CREATE MATERIALIZED VIEW hourly_sales 
WITH (timescaledb.continuous) AS 
SELECT time_bucket(INTERVAL '1 hour', sale_time) as hour,
       product_id,
       SUM(units_sold) as total_units_sold
FROM sales_data 
GROUP BY hour, product_id; But differently than materialized views, creating a refresh policy is straightforward. You can easily define the refresh interval within the database, ensuring that your continuous aggregate is automatically and periodically updated. The example below sets up a refresh policy to update the continuous aggregate every 30 minutes. The parameter defines the time range of data to be refreshed and the sets how often the continuous aggregate will be refreshed: end_offset schedule_interval -- Setting up a refresh policy 
SELECT add_continuous_aggregate_policy('hourly_sales',
    end_offset => INTERVAL '1 minute',
    schedule_interval => INTERVAL '30 minutes'); When this refresh policy kicks in, the process will be much more efficient than if we were using a plain materialized view. Unlike running , when a continuous aggregate is refreshed, Timescale doesn’t drop all the old data and recompute the aggregate against it: the engine just runs the query against the most recent refresh period (e.g., 30 minutes) and adds it to the materialization. REFRESH MATERIALIZED VIEW Similarly, s and s performed during this last period are identified, recomputing the chunk (partition) that involves them. (Continuous aggregates built on Timescale’s , which are automatically partitioned PostgreSQL tables. This is a massive advantage, allowing the engine to recompute only specific partitions vs. the entire table when the data has changed.) UPDATE DELETE hypertables Showing up-to-date results for real-time analytics But, how do continuous aggregates solve the problem of viewing up-to-date results? What happens if new data has been added after the last refresh, and I query the continuous aggregate? To allow this functionality, we added to continuous aggregates. , and you query your continuous aggregate, the result you’ll see will combine two parts: real-time aggregation functionality When real-time aggregation is enabled The materialized data in the underlying materialized view, which has been updated in the last refresh. The most recent, not-yet-materialized raw data, which still lives exclusively in your base table (or hypertable, to be exact). This functionality transforms materialized views from static snapshots into dynamic entities, ensuring that the stored data is not just a historical reflection but an up-to-date representation of the underlying datasets. Using Continuous Aggregates: Example Even if this all sounds good, it'll (hopefully) come together a lot better with an example. Imagine a platform used by transportation agencies and ride-sharing companies. This platform contains a dashboard in which companies can see an overview of the status of their fleet, including a table with the latest status of key metrics and two visualizations showing how the metrics are doing that particular day and within the context of the week. To power this application, we would first have a hypertable in which the data about the rides is constantly inserted. The hypertable could look something like this: CREATE TABLE rides (
    ride_id SERIAL PRIMARY KEY,
    vehicle_id INT,
    start_time TIMESTAMPTZ NOT NULL,
    end_time TIMESTAMPTZ NOT NULL,
    distance FLOAT NOT NULL,
    price_paid FLOAT NOT NULL
);
SELECT create_hypertable('rides', 'start_time'); Hypertables are very fast and very scalable—this table will remain performant even when it has billions of rows. To power the table by providing a live overview, we would use a continuous aggregate to bucket the data by 30 minutes. This would keep the process fast and responsive: -- Create continuous aggregate for live overview
CREATE MATERIALIZED VIEW live_dashboard
WITH (timescaledb.continuous, timescaledb.materialized_only=false)) AS
SELECT 
    vehicle_id,
    time_bucket(INTERVAL '30 minute', start_time) as minute,
    COUNT(ride_id) as number_of_rides,
    AVG(price_paid) as average_price
FROM rides
GROUP BY vehicle_id, minute; -- Set up a refresh policy
SELECT add_continuous_aggregate_policy('live_dashboard',
    end_offset => INTERVAL '10 minutes',
    schedule_interval => INTERVAL ‘15 minute'); In the previous code, the parameter ensures that the aggregate does not immediately attempt to refresh the very latest data, allowing some buffer time to accommodate any lags in data arrival. Setting to means the aggregate will refresh data that is at least 10 minutes old, ensuring it doesn’t miss updates due to minor delays in data inflow. In a real-world use case, you would adjust this value based on the average delay you observe in your data pipeline. end_offset end_offset 10 minutes To power the visualization offering the daily view, we would create a second continuous aggregate. In this chart, the data is being displayed by hour, so we don’t need a per-minute granularity as the previous one: -- Create continuous aggregate for daily overview
CREATE MATERIALIZED VIEW hourly_metrics
WITH (timescaledb.continuous, timescaledb.materialized_only=false) AS
SELECT 
    vehicle_id,
    time_bucket(INTERVAL '1 hour', start_time) as hour,
    COUNT(ride_id) as number_of_rides,
    SUM(price_paid) as total_revenue
FROM rides
WHERE start_time > NOW() - INTERVAL '1 day'
GROUP BY vehicle_id, hour; -- Define refresh policy
SELECT add_continuous_aggregate_policy('hourly_metrics',
    end_offset => INTERVAL '10 minutes',
    schedule_interval => INTERVAL `1 hour`); Finally, to power the chart offering the weekly view, we would create one more continuous aggregate, this time aggregating the data by day: -- Create continuous aggregate to power chart with weekly overview
CREATE MATERIALIZED VIEW daily_metrics
WITH (timescaledb.continuous, timescaledb.materialized_only=false) AS
SELECT 
    vehicle_id,
    time_bucket(INTERVAL '1 day', start_time) as day,
    COUNT(ride_id) as number_of_rides,
    SUM(price_paid) as total_revenue
FROM rides
WHERE start_time > NOW() - INTERVAL '1 week'
GROUP BY vehicle_id, day; -- Define refresh policy
SELECT add_continuous_aggregate_policy('daily_metrics',
    end_offset => INTERVAL '10 minutes',
    schedule_interval => INTERVAL '1 day); P.S. To make the experience of defining continuous aggregates even more efficient, in TimescaleDB 2.9. Once you get familiar with continuous aggregates, you can start creating them on top of other continuous aggregates—e.g., in the previous example, you could also define the hourly aggregates on top of the by-minute aggregate. Timescale introduced hierarchical continuous aggregates Conclusion Even if PostgreSQL was not originally built for applications that need to process large live datasets, guess what—these types of workloads are now everywhere. However, PostgreSQL comes with features that help with this task. Materialized views are among the most powerful, as they allow pre-computing query results and storing them on disk for fast retrieval. However, materialized views have three important limitations. First, triggering refreshes is very computationally inefficient. Second, even setting up these automatic refreshes is not a seamless process. Third, materialized views don’t show up-to-date results, as they exclude the data that has been added or modified since the last refresh. These limitations make materialized views an unpractical solution for many modern applications. To solve this, we built continuous aggregates. These are PostgreSQL materialized views in which you can easily define a refresh policy, so the refreshes happen automatically. Those refreshes are also incremental and, therefore, much more efficient. Lastly, continuous aggregates allow you to combine the data that has been materialized with the raw data added and modified since the last refresh, ensuring you will only get up-to-date results. If you’re running PostgreSQL on your hardware, you can access continuous aggregates by . . The first 30 days are free. installing the TimescaleDB extension If you’re in AWS, check out the Timescale platform Written by and Mat Arye. Carlota Soto

From Materialized Views to Continuous Aggregates: Enhancing PostgreSQL With Real-Time Analytics

বস্তুগত দৃষ্টিভঙ্গি থেকে ক্রমাগত সমষ্টি: রিয়েল-টাইম অ্যানালিটিক্সের সাথে PostgreSQL উন্নত করা

Von materialisierten Ansichten zu kontinuierlichen Aggregaten: Verbesserung von PostgreSQL mit Echtzeitanalysen

De vistas materializadas a agregados continuos: mejora de PostgreSQL con análisis en tiempo real

Des vues matérialisées aux agrégats continus : améliorer PostgreSQL avec l'analyse en temps réel

भौतिक दृश्यों से सतत समुच्चय तक: वास्तविक समय विश्लेषण के साथ PostgreSQL को बढ़ाना

マテリアライズド ビューから連続集計まで: リアルタイム分析による PostgreSQL の強化

구체화된 뷰에서 연속 집계까지: 실시간 분석으로 PostgreSQL 향상

De visualizações materializadas a agregações contínuas: aprimorando o PostgreSQL com análises em tempo real

От материализованных представлений к непрерывным агрегатам: улучшение PostgreSQL с помощью аналитики в реальном времени

Gerçekleştirilmiş Görünümlerden Sürekli Toplamalara: PostgreSQL'i Gerçek Zamanlı Analitikle Geliştirme

Từ chế độ xem cụ thể hóa đến tổng hợp liên tục: Nâng cao PostgreSQL bằng phân tích thời gian thực

从物化视图到连续聚合：通过实时分析增强 PostgreSQL

If you’re working with large databases in , this story will sound familiar. As your Postgres database keeps growing, your performance starts to decline, and you begin to worry about storage space—or, to be precise, how much you’ll pay for it. You love PostgreSQL, but there’s something you wish you had: a highly effective data compression mechanism. Postgres does have somewhat of a compression mechanism: 🍞. In this post, we’ll walk you through how Postgres TOAST works and the different TOASTing strategies. As much as we enjoy a good TOAST, we’ll discuss why this is not the kind of compression feature you need for reducing the storage footprint of modern large databases—and how, as the PostgreSQL enthusiasts that we are here at Timescale, we decided to build a more suitable compression mechanism for PostgreSQL, inspired by the columnar design of NoSQL databases. PostgreSQL TOAST What Is Postgres TOAST? Even if it might reduce the size of datasets, TOAST (The Oversized Attribute Storage Technique) is not your traditional data compression mechanism. To understand what TOAST is, we have to start by talking about . how data is stored in PostgreSQL Postgres’ units of storage are called pages, and these have a fixed size (8 kB by default). Having a fixed page size gives Postgres many advantages, namely its data management simplicity, efficiency, and consistency, but it comes with a downside: some data values might not fit within that page. This is where TOAST comes in. TOAST refers to the automatic mechanism that PostgreSQL uses to efficiently store and manage values in Postgres that do not fit within a page. To handle such values, Postgres TOAST will, by default, compress them using an internal algorithm. If, after compression, the values are still too large, Postgres will move them to a separate table (called the TOAST table), leaving pointers in the original table. As we’ll see later in this article, you can actually modify this strategy as a user, for example, by telling Postgres to avoid compressing data in a particular column. TOAST-able Data Types The data types that might be subjected to TOAST are primarily variable-length ones that have the potential to exceed the size limits of a standard PostgreSQL page. On the other hand, fixed-length data types, like , , or , are not subjected to TOAST since they fit comfortably within a page. integer float timestamp Some examples of data types that might be subjected to TOAST are: and json jsonb Large strings text and (If the length specified in is small enough, then values of that column might always stay below the TOAST threshold.) varchar varchar(n) varchar(n) storing binary data bytea Geometric data like and and PostGIS types like or path polygon geometry geography How Does Postgres TOAST Work? Understanding TOAST not only relates to the concept of page size but also to another Postgres storage concept: tuples. Tuples are rows in a PostgreSQL table. Typically, the TOAST mechanism kicks in if all fields within a tuple have a total size of over 2 kB approx. If you’ve been paying attention, you might be wondering, “Wait, but the page size is around 8 kB—why this overhead?” That’s because PostgreSQL likes to ensure it can store multiple tuples on a single page: if tuples are too large, fewer tuples fit on each page, leading to increased I/O operations and reduced performance. Postgres also needs to keep free space to fit additional operational data: each page stores not just the tuple data but also additional information for managing the data, such as item identifiers, headers, and transaction information. So, when the combined size of all fields in a tuple exceeds approximately 2 kB (or the TOAST threshold parameter, as we’ll see later), PostgreSQL takes action to ensure that the data is stored efficiently. TOAST handles this in two primary ways: PostgreSQL can compress the large field values within the tuple to reduce their size using a compression algorithm that we’ll cover later in this article. By default, if compression is sufficient to bring the tuple's total size below the threshold, the data will remain in the main table, albeit in a compressed format. Compression. If compression alone isn't effective enough to reduce the size of the large field values, Postgres moves them to a separate TOAST table. This process is known as "out-of-line" storage because the original tuple in the main table doesn’t hold the large field values anymore. Instead, it contains a "pointer" or reference to the location of the large data in the TOAST table. Out-of-line storage. We’re simplifying things slightly for this article— read the PostgreSQL documentation for a full detailed view. The Postgres Compression Algorithm: pglz We’ve mentioned that TOAST can compress large values in PostgreSQL. But which compression algorithm is PostgreSQL using, and how effective is it? The (PostgreSQL Lempel-Ziv) is the default internal compression algorithm used by PostgreSQL specifically tailored for TOAST. pglz Here’s how it works in very simple terms: tries to avoid repeated data. When it sees repeated data, instead of writing the same thing again, it just points back to where it wrote it before. This "avoiding repetition" helps in saving space. pglz As reads through data, it remembers a bit of the recent data it has seen. This recent memory is referred to as the "sliding window." pglz As new data comes in, checks if it has seen this data recently (within its sliding window). If yes, it writes a short reference instead of repeating the data. pglz If the data is new or not repeated enough times to make a reference shorter than the actual data, just writes it down as it is. pglz When it's time to read the compressed data, uses its references to fetch the original data. This process is quite direct, as it looks up the referred data and places it where it belongs. pglz doesn't need separate storage for its memory (the sliding window); it builds it on the go while compressing and does the same when decompressing. pglz This implementation is designed to offer a balance between compression efficiency and speed within the TOAST mechanism. In terms of compression rate, the effectiveness of will largely depend on the nature of the data. pglz For example, highly repetitive data will compress much better than high entropy data (like random data). You might see compression ratios in the range of 25 to 50 percent, but this is a very general estimate—results will vary widely based on the exact nature of the data. Configuring TOAST TOAST strategies By default, PostgreSQL will go through the TOAST mechanism according to the procedure explained earlier (compression first and out-of-line storage next, if compression is not enough). Still, there might be scenarios where you might want to fine-tune this behavior on a per-column basis. PostgreSQL allows you to do this by using the TOAST strategies , , , and . PLAIN EXTERNAL EXTENDED MAIN : This is the default strategy. It implies the data will be stored out of line in a separate TOAST table if it’s too large for a regular table page. Before moving the data to the TOAST table, it will be compressed to save space. EXTENDED : This strategy tells PostgreSQL to store the data for this column out of line if the data is too large to fit in a regular table page, and we’re asking PostgreSQL not to compress the data—the value will just be moved to the TOAST table as-is. EXTERNAL : This strategy is a middle ground. It tries to keep data in line in the main table through compression; if the data is definitely too large, it'll move the data to the TOAST table to avoid an error, but PostgreSQL won't move the compressed data. Instead, it’ll store the value in the TOAST table in its original form. MAIN : Using in a column tells PostgreSQL to always store the column's data in line in the main table, ensuring it isn't moved to an out-of-line TOAST table. Take into account that if the data grows beyond the page size, the will fail because the data won’t fit. PLAIN PLAIN INSERT If you want to inspect the current strategies of a particular table, you can run the following: \d+ your_table_name You'll get an output like this: => \d+ example_table
                     Table "public.example_table"
 Column  |       Data Type   | Modifiers | Storage  | Stats target | Description 
---------+------------------+-----------+----------+--------------+-------------
  bar    | varchar(100000)  |           | extended |              | If you wish to modify the storage setting, you can do so using the following command: -- Sets EXTENDED as the TOAST strategy for bar_column 
ALTER TABLE example_blob ALTER COLUMN bar_column SET STORAGE EXTENDED; Key parameters Apart from the strategies above, these two parameters are also important to control TOAST behavior: TOAST_TUPLE_THRESHOLD This is the parameter that sets the size threshold for when TOASTing operations (compression and out-of-line storage) are considered for oversized tuples. As we’ve mentioned previously, by default, is set to approximately 2 kB. TOAST_TUPLE_THRESHOLD TOAST_COMPRESSION_THRESHOLD This is the parameter that specifies the minimum size of a value before Postgres considers compressing it during the TOASTing process. If a value surpasses this threshold, PostgreSQL will attempt to compress it. However, just because a value is above the compression threshold, it doesn't automatically mean it will be compressed: the TOAST strategies will guide PostgreSQL on how to handle the data based on whether it was compressed and its resultant size relative to the tuple and page limits, as we’ll see in the next section. Bringing it all together is the trigger point. When the size of a tuple's data fields combined exceeds this threshold, PostgreSQL will evaluate how to manage it based on the set TOAST strategy for its columns, considering compression and out-of-line storage. The exact actions taken will also depend on whether column data surpasses the : TOAST_TUPLE_THRESHOLD TOAST_COMPRESSION_THRESHOLD (default strategy): If a tuple's size exceeds , PostgreSQL will first attempt to compress the oversized columns if they also exceed . If compression brings the tuple size under the threshold, it will remain in the main table. If it doesn't, the data will be moved to an out-of-line TOAST table, and the main table will contain pointers to this external data. EXTENDED TOAST_TUPLE_THRESHOLD TOAST_COMPRESSION_THRESHOLD : If the tuple size surpasses the , PostgreSQL will try to compress the oversized columns (provided they're over the ). If compression allows the tuple to fit within the main table's tuple, it remains there. If not, the data is moved to the TOAST table in its uncompressed form. MAIN TOAST_TUPLE_THRESHOLD TOAST_COMPRESSION_THRESHOLD : PostgreSQL skips compression, regardless of the . If the tuple's size is beyond the , the oversized columns will be stored out-of-line in the TOAST table. EXTERNAL TOAST_COMPRESSION_THRESHOLD TOAST_TUPLE_THRESHOLD : Data is always stored in the main table. If a tuple's size exceeds the page size (due to having very large columns), an error is raised. PLAIN Strategy Compress if tuple > TOAST_COMPRESSION_THRESHOLD Store out-of-line if tuple > TOAST_TUPLE_THRESHOLD Description EXTENDED Yes Yes Default strategy. Compresses first, then checks if out-of-line storage is needed. MAIN Yes Only in uncompressed form Compresses first, and if still oversized, moves to TOAST table without compression. EXTERNAL No Yes Always moves to TOAST if oversized, without compression. PLAIN No No Data always stays in the main table. If a tuple exceeds the page size, an error occurs. Why TOAST Isn't Enough as a Data Compression Mechanism in PostgreSQL By now, you’ll probably understand why TOAST is not the data compression mechanism you wish you had in PostgreSQL. Modern applications imply large volumes of data ingested daily, meaning databases (over)grow quickly. Such a problem was not as prominent when our beloved Postgres was built decades ago, but today’s developers need compression solutions for reducing the storage footprint of their datasets. While TOAST incorporates compression as one of its techniques, it's crucial to understand that its primary role isn't to serve as a database compression mechanism in the traditional sense. TOAST is mainly a solution to one problem: managing large values within the structural confines of a Postgres page. While this approach can lead to some storage space savings due to the compression of specific large values, its primary purpose is not to optimize storage space across the board. For example, if you have a 5 TB database made up of small tuples, TOAST won’t help you turn those 5 TB into 1 TB. While there are parameters within TOAST that can be adjusted, this won't transform TOAST into a generalized storage-saving solution. And there are other inherent problems with using TOAST as a traditional compression mechanism in PostgreSQL, for example: Accessing TOASTed data can add overhead, especially when the data is stored out of line. This becomes more evident when many large text or other TOAST-able data types are frequently accessed. TOAST lacks a high-level, user-friendly mechanism for dictating compression policies. It’s not built to optimize storage costs or facilitate storage management. TOAST's compression is not designed to provide especially high compression ratios. It only uses one algorithm ( ) with compression rates varying typically from 25-50 percent. pglz Adding Columnar Compression to PostgreSQL With Timescale , PostgreSQL users have a better alternative. Inspired by the compression design of NoSQL databases, . This transformative approach transcends PostgreSQL’s conventional row-based storage paradigm, introducing the efficiency and performance of columnar storage. Via the TimescaleDB extension we added columnar compression functionality to PostgreSQL By adding a compression policy to your large tables, . you can reduce your PostgreSQL database size by up to 10x (achieving +90 percent compression rates) By defining a time-based compression policy, you indicate when data should be compressed. For instance, you might choose to compress data older than seven (7) days automatically: -- Compress data older than 7 days
SELECT add_compression_policy('my_hypertable', INTERVAL '7 days'); Via this compression policy, Timescale will transform the table ( ) into a columnar format behind the scenes, combining many rows (1,000) into an array. To boost compressibility, Timescale will apply different compression algorithms depending on the data type: partitions which in Timescale are also created automatically Gorilla compression for floats Delta-of-delta + with compression for timestamps and other integer-like types Simple-8b run-length encoding Whole-row dictionary compression for columns with a few repeating values (+ LZ compression on top) LZ-based array compression for all other types This columnar compression design offers an efficient and scalable solution to the problem of large datasets in PostgreSQL. It allows you to use less storage to store more data without hurting your query performance (it improves it). And in the latest versions of TimescaleDB, you can also , , and directly over compressed data. INSERT DELETE UPDATE Wrap-Up We hope this article helped you understand that while TOAST is a well-thought-out mechanism to manage large values within a PostgreSQL page, it’s not effective for optimizing database storage use within the realm of modern applications. If you’re looking for effective data compression that can move the needle on your storage savings, give Timescale a go. You can try our cloud platform that propels PostgreSQL to new performance heights, making it faster and fiercer— —or you can add to your self-hosted PostgreSQL database. it’s free, and no credit card is required the TimescaleDB extension Written by . Carlota Soto

Postgres TOAST: Understanding the Data Compression Mechanism and Its Limitations 

পোস্টগ্রেস টোস্ট: ডেটা কম্প্রেশন মেকানিজম এবং এর সীমাবদ্ধতা বোঝা

Postgres TOAST: Den Datenkomprimierungsmechanismus und seine Einschränkungen verstehen

Postgres TOAST: comprensión del mecanismo de compresión de datos y sus limitaciones

Postgres TOAST : Comprendre le mécanisme de compression des données et ses limites

पोस्टग्रेस टोस्ट: डेटा संपीड़न तंत्र और इसकी सीमाओं को समझना

Postgres TOAST: データ圧縮メカニズムとその制限について理解する

Postgres TOAST: 데이터 압축 메커니즘 및 한계 이해

Postgres TOAST: Compreendendo o mecanismo de compactação de dados e suas limitações

Postgres TOAST: понимание механизма сжатия данных и его ограничений

Postgres TOAST: Veri Sıkıştırma Mekanizmasını ve Sınırlamalarını Anlamak

Postgres TOAST: Tìm hiểu cơ chế nén dữ liệu và những hạn chế của nó

Postgres TOAST：了解数据压缩机制及其局限性

If you’re working with growing PostgreSQL tables, you're likely no stranger to the challenges of managing large datasets efficiently: Your query performance is degrading. You’re dealing with maintenance overhead. You’re finding it hard to keep up with high ingestion rates. You’re having trouble managing your data lifecycle. . By partitioning your large PostgreSQL tables, you can keep them fast and efficient. But setting up and maintaining partitioned PostgreSQL tables in production can be difficult. Postgres partitioning is your most powerful ally in solving these problems “Yes,” your mind may go, “I might be able to improve my performance if I partition my tables, but this will be at the cost of countless hours spent on manual configurations, running maintenance jobs, testing, and not to mention the unforeseen issues that might pop up during scaling. It’s like having a potent car with an incredibly complicated gearbox.” If you’re using vanilla PostgreSQL in products like , there’s a lot of truth to this. You will undoubtedly spend much of your time managing your partitioned tables. Plus, you’ll have to deal with custom scripts, keep rigorous maintenance practices, and carefully monitor your performance to revisit and tweak your configuration whenever you see changes in your dataset or ingestion rate. Amazon RDS But guess what: there’s a better way of creating a Postgres partition, and it’s called hypertables. Meet Hypertables: Automatic PostgreSQL Partitioning for Your Large PostgreSQL Tables Hypertables (which are available via the and, in AWS, via ) are an innovation that makes the experience of creating a Postgres partition completely seamless. They automate the generation and management of data partitions without changing your user experience. TimescaleDB extension the Timescale platform Working with a hypertable feels exactly like working with a regular PostgreSQL table. But, under the covers, hypertables create all the partitioning magic, speeding up your queries and ingests. This performance boost will sustain as your tables' volume keeps growing, . making hypertables extremely scalable Hypertables are optimized for , so this is the type of partitioning that we’ll focus on in this article. However, hypertables also work for tables that aren’t time-based but have something similar, for example, a BIGINT primary key. time-based partitioning Let’s explain how hypertables work with an example. Imagine you have a PostgreSQL table called , where data from various IoT devices is stored with a timestamp. The table might look something like this: sensor_data CREATE TABLE sensor_data (
    device_id INT NOT NULL,
    event_time TIMESTAMPTZ NOT NULL,
    temperature FLOAT NOT NULL,
    humidity FLOAT NOT NULL
); Now, as the volume of grows, you start facing performance issues and management complexities. Here’s where hypertables come to help. If you were using Timescale, the only thing you’d need to do is convert your table into a hypertable: sensor_data sensor_data SELECT create_hypertable('sensor_data', 'event_time'); This is how easy it is. With this simple command, is now a hypertable that automatically partitions your data by the column. sensor_data event_time Your PostgreSQL partitioning is all set. Native Partitioning vs. Hypertables: How Much Easier Does It Get? Let’s look at what’s happening under the hood. If you were using a traditional native method to create a Postgres partition, you would have to go through all these steps to set up partitioning in : sensor_data Create a parent table with the common schema and constraints but no data. Define child tables, each covering a specific time range. Add indexes to the parent and child tables. Set up a job for scheduling the creation of partitions. Set up a job for managing old partitions. Attaching it all together. Each one of these steps comes with its chunk of code; ; you’ll have to monitor potential issues on each one of these steps and set up adjustments manually along the way, etc. Overall, you’ll create significant maintenance overhead for yourself. they require you to run different extensions, like pg_partman and cron What hypertables do instead is encapsulate and automate all these steps, significantly reducing the complexity, manual effort, and potential for errors on your end: With hypertables, there’s no need to create a parent table manually and to define child tables for each time range. You would simply convert your existing table into a hypertable. Hypertables also simplify indexing. When you create an index on a hypertable, Timescale automatically creates the corresponding indexes on all current and future partitions, ensuring consistent query performance without manual adjustments. Hypertables automatically create new partitions on the fly based on the specified time interval. As new data is ingested, appropriate partitions are ready to store the data without manual intervention or scheduled jobs. , completely removing partition management from your to-do list. Using Timescale eliminates the risk of partitions not existing Timescale maintains its own partition catalogs and implements its own minimized locking strategy to ensure that your application’s read or write operations are never blocked by the underlying partitioning operations ( ). something that can be an issue in native PostgreSQL partitioning Once your PostgreSQL table becomes a hypertable, you can keep querying it as usual. You will instantly experience a performance boost. When you execute a query, Timescale’s query planner intelligently routes the query to the appropriate partition(s), ensuring that only relevant data is scanned. This process remains completely transparent; you don't need to think about it or worry about which partition contains which data. Something similarly straightforward happens when you ingest data. Timescale will take care of routing your new data to the appropriate partition under the hood, ensuring that each partition remains optimally sized. ( ) The default partition size is seven (7) days in Timescale, but you can easily modify this. Partitioning Is Only the Beginning: Features Unlocked With Hypertables Hypertables make partitioning seamless unlock a wealth of features that will help you improve your PostgreSQL performance even further and save you time when managing your data. and A few examples: By enabling , your hypertable will change from row to column-oriented. This can reduce storage usage by up to 95 % and unlock blazing-fast analytical queries while Columnar compression for faster queries and cheaper storage. Timescale compression allowing the data to be updated . Blazing-fast analytical views. By creating incrementally updated materialized views, known as continuous aggregates, you’ll improve the performance of aggregate queries tremendously. Continuous aggregates automatically refresh and store aggregated data, enabling you to build fast visualizations, including real-time insights and historical analytics that go back in time. Hypertables allow you to set up automatic data retention policies with one simple command: . You can just tell Timescale when you want your data dropped, and your hypertables will automatically drop outdated partitions when it’s time. Easy and configurable data retention. add_retention_policy Hypertables come with a full set of that give you a blazing-fast full set of mathematical analytical functions, procedures, and data types optimized for effectively querying, aggregating, and analyzing large volumes of data. SQL hyperfunctions to run analytics with fewer lines of code. hyperfunctions Queries that reference now( ) when pruning partitions , and your ordered DISTINCT queries will benefit from . Faster DISTINCT and now() queries. will perform better in Timescale SkipScan The lets you schedule any SQL or function-based job within PostgreSQL, meaning you don’t need an external scheduler or another extension like . Built-in job scheduler. Timescale job scheduler pg_cron When To Use Hypertables: Example Use Cases In sum, if you plan to partition your PostgreSQL tables by time, you’ll surely benefit from hypertables. But who doesn’t love some concrete use-case examples? Let’s paint a few scenarios where hypertables would be most useful. Needless to say, this is not a comprehensive list! . If you’re intrigued by hypertables and Timescale but are unsure if your use case is a fit, don’t hesitate to contact us Ingesting thousands of energy metrics per second An engineering team at a leading energy company is tasked with managing the data from a newly installed smart grid, a big investment for the energy company, which now has granular insights into energy consumption, distribution efficiency, and grid health metrics. Elements in the smart grid generate thousands of energy metrics per second that need to be properly collected, analyzed, and managed. These energy metrics are currently stored in PostgreSQL, but the engineering team has to figure out the best solution to ingest this high-velocity data efficiently without losing granularity or accuracy. They must also ensure they can query this data quickly for real-time monitoring and analysis. This would be an ideal use case for Timescale: Timescale’s hypertables can handle the high ingestion without imposing manual work on the team. Hypertables also optimized query performance, ensuring that real-time energy data will be readily accessible for queries. As the smart grid expands, Timescale's hypertables will seamlessly scale, accommodating increased data volumes without compromising performance. Given that Timescale is built on PostgreSQL, the engineering team can leverage their existing knowledge and tools, ensuring a smooth transition and minimal learning curve. -- Creating a table to store energy metrics from the smart grid
CREATE TABLE energy_metrics (
    element_id INT NOT NULL,
    event_time TIMESTAMPTZ NOT NULL,
    voltage DECIMAL NOT NULL,
    current DECIMAL NOT NULL,
    frequency DECIMAL NOT NULL,
    PRIMARY KEY(grid_id, event_time)
); -- Converting the energy_metrics table into a hypertable
SELECT create_hypertable('energy_metrics', 'event_time'); -- Sample query to ingest new metrics data into the hypertable
INSERT INTO energy_metrics (element_id, event_time, voltage, current, frequency) 
VALUES  (1, NOW(), 210.5, 10.7, 50.01); -- Sample query to retrieve the latest energy metrics for real-time monitoring
SELECT * FROM energy_metrics 
WHERE element_id = 1 
ORDER BY event_time DESC 
LIMIT 10; Building dashboards for monitoring sensor data An industrial manufacturing company operates a range of heavy machinery and equipment in its facilities. Each piece of machinery is equipped with sensors that continuously monitor and log temperature data in a table to ensure optimal performance and safety. sensor_data The company needs its PostgreSQL database to achieve two distinct yet critical objectives: Provide engineers and maintenance staff with real-time temperature data to detect anomalies and ensure that machinery is operating within safe temperature ranges. Analyze historical temperature data to identify trends, predict maintenance needs, and improve operational efficiency. The team decides to turn into a hypertable. To facilitate real-time monitoring, they create a continuous aggregate to calculate the average temperature for every piece of machinery, which is updated every minute: sensor_table CREATE MATERIALIZED VIEW real_time_avg_temp
WITH (timescaledb.continuous, timescaledb.refresh_interval = '1m') AS
SELECT device_id,
       time_bucket('1 minute', event_time) AS one_min,
       AVG(temperature) AS avg_temp
FROM sensor_data
GROUP BY device_id, one_min; With , the maintenance team has immediate access to the average temperature of every machinery piece, enabling swift responses to temperature anomalies and preventing potential breakdowns. real_time_avg_temp For historical analysis, the team creates another continuous aggregate view, this time aggregating daily average temperatures: CREATE MATERIALIZED VIEW daily_avg_temp
WITH (timescaledb.continuous) AS
SELECT device_id,
       time_bucket('1 day', event_time) AS one_day,
       AVG(temperature) AS avg_temp
FROM sensor_data
GROUP BY device_id, one_day; Both views ( and ) feed into a monitoring dashboard. The maintenance team would get alerted of potential issues as they arise. At the same time, the team can review historical temperature trends, conduct analyses to predict when machinery might need maintenance, and optimize operational protocols to mitigate excessive temperature fluctuations. real_time_avg_temp daily_avg_temp Storing large volumes of weather data effectively An environmental research institute is collecting and analyzing many TBs of weather data to study climate change. The team already knows PostgreSQL, so they want to stick to it—but the storage cost is becoming a concern. The team decides to start using Timescale. After optimizing their database to reduce storage use and enabling compression, their storage costs become a fraction of what they were, with the data remaining fully accessible for analysis. CREATE TABLE weather_data (
    sensor_id INT NOT NULL,
    event_time TIMESTAMPTZ NOT NULL,
    temperature DECIMAL NOT NULL,
    humidity DECIMAL NOT NULL,
    pressure DECIMAL NOT NULL,
    PRIMARY KEY(sensor_id, event_time)
); -- Conversion to hypertable 
SELECT create_hypertable('weather_data', 'event_time'); -- Enabling compression
ALTER TABLE weather_data 
SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'sensor_id'
); -- Sample query
SELECT * FROM weather_data 
WHERE sensor_id = 1 
AND event_time > NOW() - INTERVAL '1 month'
ORDER BY event_time DESC 
LIMIT 10; Querying high volumes of crypto data in real-time A new crypto exchange is grappling with the challenge of providing real-time analytics to traders. As the data volume stored in the underlying PostgreSQL database increases, the engineering team struggles to keep the database fast enough. To them, it’s essential to deliver a better user experience than the competition, which has a more established but slower product. Keeping up the speed and responsiveness of their portal is paramount. The team knows that by partitioning their large pricing table, they’ll most likely improve query performance. Instead of attempting to manage partitioning themselves, since they’re already swamped, the engineers decide to implement Timescale. Once turned into a hypertable, their pricing table automatically partitions the data as it gets in. Real-time analytics, previously marred by delays, are now swift and accurate. P.S. Check out the story of how Messari improved its performance with hypertables. Get Started With Hypertables PostgreSQL partitioning is a powerful tool for managing large tables. On its own, Postgres partitioning can be complex to implement and maintain, but Timescale's hypertables make the whole process seamless and automatic. The best part is that by using hypertables, you’ll unlock a myriad of other awesome features (like columnar compression and automatic materialized views) that will make it even easier to scale your PostgreSQL database. If you're ready to explore Timescale's hypertables, , our fully managed PostgreSQL—but faster—cloud solution. It’s free for 30 days, and no credit card is required. If you are self-hosting your own PostgreSQL instance, start by signing up to Timescale you can get access to hypertables by adding the TimescaleDB extension . Written by . Carlota Soto Also published . here

How Hypertables Enable Automatic PostgreSQL Partitioning for Your Datasets

If you have experience running in a production environment, you know that maintaining database backups is a daunting task. In the event of a catastrophic failure, data corruption, or other form of data loss, the ability to quickly restore from these backups will be vital for minimizing downtime. If you’re managing a database, maintaining your backups and getting your recovery strategy in order is probably the first check on your checklist. PostgreSQL Perhaps this has already given you one headache or two because creating and restoring backups for large PostgreSQL databases can be… A very slow process. The most widely used external tool for backup operations in PostgreSQL is , which is very powerful and reliable. But pgBackRest can also be very time-consuming, especially for databases well over 1 TB. pgBackRest The problem is exacerbated when restoring backups from production databases that continue to ingest data, thus creating more WAL (write-ahead log) that must be applied. In this case, a full backup and restore can take hours or even days, which can be a nightmare in production databases. When operating our platform ( , a cloud database platform built on PostgreSQL), we struggled with this very thing. At Timescale, we pride ourselves in making faster and more scalable for large volumes of time-series data—therefore, our customers’ databases are often large (many TBs). At first, we were completely basing our backup and restore operations in pgBackRest, and we were experiencing some pain: Timescale PostgreSQL Creating full backups was very slow. This was a problem, for example, when our customers were trying to upgrade their PostgreSQL major version within our platform, as we took a fresh, full backup after the upgrade in case there was a failure shortly after. Upgrades are already stressful, and adding a very slow backup experience was not helping. Restoring from backups was also too slow, both restoring from the backups themselves and replaying any WAL that had accrued since the last backup. ( ) In Timescale, we automatically take full and incremental backups of all our customers’ databases. In this article, we’re sharing how we solved this problem by combining pgBackRest with EBS snapshots. Timescale runs in AWS, so we had the advantage of cloud-native infrastructure. If you're running PostgreSQL in AWS, you can perhaps benefit from a similar approach. This significantly improved the experience for our customers and made things much easier for our team. After introducing EBS snapshots, our backup creation and restore process got 100x faster. Quick Introduction to Database Backups in PostgreSQL (And Why We Used pgBackRest) If you asked 100 engineers if they thought backups were important for production databases, they would all say "yes"—but if you then took those same 100 engineers and gave them a grade on their backups, most wouldn’t hit a pass mark. We all collectively understand the need for backups, but it’s still hard to create an effective backup strategy, implement it, run it, and test that it’s working appropriately. In PostgreSQL specifically, there are two ways to implement backups: logical database dumps, which contain the SQL commands needed to recreate (not restore) your database from scratch, and physical backups, which capture the files that store your database state. Physical backups are usually paired with a mechanism to store the constant stream of write-ahead logs (WALs), which describe all data mutations on the system. A physical backup can then be restored to get PostgreSQL to the exact same state as it was when that backup was taken, and the WAL files rolled forward to get to a specific point in time, maybe just before someone (accidentally?) dropped all your data or your disk ate itself. Logical backups are useful to recreate databases (potentially on other architectures), but maintaining physical backups is imperative for any production workload where uptime is valued. Physical backups are exact: they can be restored quickly and provide point-in-time recovery. In the rest of this article, we’ll discuss physical backups. How are physical backups usually created in PostgreSQL? The first option is using the pg_basebackup command. copies the data directory and optionally includes the WAL files, but it doesn’t support incremental backups and has limited parallelization capabilities. The whole process is very manual, too. If you’re using , you’ll instantly get the files you need to bootstrap a new database in a tarball or directory, but not much else. pg_basebackup pg_basebackup Tools like were designed to overcome the limitations of . pgBackRest allows for full and incremental backups, multi-threaded operations, and point-in-time recovery. It ensures data integrity by validating checksums during the backup process, supports different types of storage, and much more. In other words, pgBackRest is a robust and feature-rich tool, making it our choice for PostgreSQL backup operations. pgBackRest pg_basebackup The Problem With pgBackRest pgBackrest is not perfect: it reads and backs up files, causing an additional load on your system. This can cause performance bottlenecks that can complicate your backup and restore strategy, especially if you’re dealing with large databases. Even though pgBackRest offers incremental backups and parallelization, it often gets slow when executing full backups over large data volumes or on an I/O-saturated system. While you can sometimes rely on differential or incremental backups to minimize data, there are situations in which creating full backups is unavoidable. Backups could also be taken on standby, but at the end of the day, you’re limited by how fast you can get data off your volumes. We shared earlier the example of full database upgrades, but we're also talking about any other kind of migration, integrity checks, archival operations, etc. In Timescale, some of our most popular platform features (like and ) imply a data restore from a full backup. forks, high-availability replicas, read replicas Having a long-running full backup operation in your production database is not only inconvenient, it can also conflict with other high-priority DB tasks, affecting your overall performance. This was problematic for us. The slowness of pgBackRest was also problematic when it was time to restore from these backups. It’s very good at CPU parallelization, but when you’re trying to write terabytes of data as fast as possible, I/O will be the bottleneck. When it comes to recovery time objective or RTO, every minute counts. In case of major failure, you want to get that database up as soon as possible. Using EBS Snapshots to Speed Up the Creation of Backups To speed up the process of creating fresh full backups, we decided to replace standard pgBackRest full backups with on-demand . EBS snapshots Our platform runs in AWS, which comes with some advantages. Using snapshots is a much more cloud-native approach to the problem of backups compared to what’s been traditionally used in PostgreSQL management. EBS snapshots create a point-in-time copy of a particular database: this snapshot can be restored, effectively making it a backup. The key is that taking a snapshot is significantly faster than the traditional approach with pgBackRest: in our case, our p90 snapshot time decreased by over 100x. This gap gets wider the larger your database is! How did we implement this? Basically, we did a one-to-one replacement of pgBackRest. Instead of waiting for the pgBackRest fresh full backup to complete, we now take a snapshot. We still wait for the backup to complete, but the process is significantly faster via snapshots. This way, we get the quick snapshot but also the full data copy and checksumming for datafile integrity, which pgBackRest performs. If a user experiences a failure shortly after an upgrade, we have a fresh backup—the snapshot—that we can quickly restore (we’ll cover how we handle restores next). We still take a fresh full backup using pgBackRest (yay for redundancy), but the key difference is that this happens after the upgrade process has been fully completed. If a failure has happened, the service is available to our customer quickly: we don’t have to force them to wait for the lengthy pgBackRest process to finish before being able to use their service again. The trade-offs for adopting this approach were minimal. The only downside to consider is that, by taking snapshots, we now have redundant backups (both snapshots and full backups), so we incur additional storage costs. But what we’ve gained (both in terms of customer satisfaction and our own peace of mind) is worth the price. Combining EBS Snapshots and pgBackRest for Quick Data Restore: Taking Partial Snapshots, Replaying WAL Solving the first problem we encountered with pgBackRest (i.e., slow creation of full backups) was relatively simple. We knew exactly when we needed an EBS snapshot to be created, as this process is always tied to a very specific workflow (e.g., performing a major version upgrade). But we also wanted to explore using EBS snapshots to improve our data restore functionality. As we mentioned earlier, some popular features in the Timescale platform rely heavily on restores. This use case posed a slightly different and more difficult challenge since to restore from a full backup, such a backup needs to exist first, reflecting the latest state of the service. To implement this, the first option we explored was taking an EBS snapshot when the user clicked “Create” a fork, read replica, or high-availability replica, to then restore from that snapshot. However, this process was still too slow for the end user. To get the performance we wanted, we had to think a bit beyond the naive approach and determine a way to take semi-regular snapshots across our fleet. Fortunately, we already had a backup strategy for pgBackRest in place that we chose to mirror. Now, all Timescale services have EBS snapshots taken daily. For redundancy reasons and to verify file checksums, we still take our standard pgBackRest partial backups, but we don’t depend on them. Once the strategy is solved, restoring data from an EBS snapshot mirrors a restore from pgBackRest very closely. We simply chose the corresponding EBS snapshot we wanted to restore—in the cases mentioned above, always the most recent—and then replayed any WAL that has accumulated since that restore point. Here, it is important to note that . pgBackRest works great for us here; nothing gets close in terms of parallel WAL streaming. we still rely on pgBackRest to do our WAL management This EBS snapshotting and pgBackRest approach has given us great results so far. Using snapshots for restores has helped improve our product experience, also providing our customers with an even higher level of reliability. Keeping pgBackRest in parallel has given us peace of mind that we still have a traditional backup approach that validates our data as well as snapshots. We’re continually improving our strategy though, for example, by being smarter about when we snapshot—e.g., by looking at the accumulated WAL since the last snapshot to determine if we need to snapshot certain services more frequently. This practice helps improve restore times by reducing the amount of WAL that would need to be replayed, which is often the bottleneck in this process. On Snapshot Prewarming One important trade-off with this EBS snapshot approach is the balance between deployment time and initial performance. One limitation of a snapshot restore is that not all blocks are necessarily prewarmed and may need to be fetched from S3 the first time they are used, which is a slow process. To give props to pgBackRest restore, it does not have this issue. For our platform features, our trade-off was between getting the user a running read replica (or fork or high-availability replica) as quickly as possible or making sure it was as performant as possible. After some back and forth, we decided on our current approach on prewarming: we’re reading as much as we can for five minutes, prioritizing the most recently modified files first. The idea here is that we will warm the data the user is actively engaging with first. After five minutes, we then hand the process off to PostgreSQL to continue reading the rest of the volume at a slower pace until it is complete. For the initial warming, we use a custom that reads concurrently from files. goroutine Backing It Up We are not completely replacing our pgBackRest backup infrastructure with EBS snapshots anytime soon: it is hard to give up on the effectiveness and reliability of pgBackRest. But by combining EBS snapshots with pgBackRest across our infrastructure, we’ve been able to mitigate its performance problem significantly, speeding up our backup creation and restore process. This allows us to build a better product, providing a better experience to our customers. If you’re experiencing the same pains we were experiencing with pgBackRest, think about experimenting with something similar! It may cost you a little extra money, but it can be very much worth it.

Enhancing PostgreSQL Backup Efficiency: Leveraging pgBackRest and EBS Snapshot

Percentile Approximation Vs. Averages

My first experience with crypto wasn’t under very good circumstances: a friend who takes care of several servers at his job was infected with ransomware – and this malware was demanding he pay the ransom amount in a cryptocurrency called Monero (XMR). After this not-so-friendly introduction, I started to study how the technology behind cryptocurrencies works, and I fell in love with it. I was already interested in the stock market, so I joined the familiar (stock market) with the novel (crypto). To test the knowledge I’d learned from my stock market books, I started creating a simple crossover bot. Moving Average Convergence Divergence (MACD) This worked for a while, but I quickly realized that I should - and could - make the bot a lot better. Now, the project that I started as a hobby has a capital management system, a combination of technical indicators, and sentiment analysis powered by machine learning. Between 10 March 2020 and 10 August 2020, my bot resulted in a of 63.1%, of 1.74, and cumulative gross result of approximately 588% (you can see a copy of all of my trades during this period in ). success rate profit factor this Google Sheet report About my project I needed a bot that gave me a high-performance, scalable way to calculate technical indicators and process sentiment data in real-time. To do everything I need in terms of my technical indicators calculation, I collect data and market depth via an always-up websocket connection that tracks every Bitcoin market on the (~215 in total, 182 being tradeable, at this moment). candlestick chart Binance exchange The machine learning sentiment analysis started as a simple experiment to see if external news affected the market. For example: if a famous person in the crypto ecosystem tweeted that a big exchange was hacked, the price will probably fall and affect the whole market. Likewise, very good news should impact the price in a positive way. I calculated sentiment analysis scores in real-time, as soon as new data was ingested from sources like Twitter, Reddit, RSS feeds, and etc. Then, using these scores, I could determine market conditions at the moment. Now, I combine these two components with a weighted average, 60% technical indicators and 40% sentiment analysis. Current trading bot dashboard where I track all my ongoing trades and results; In this particular case, filtered for the period of 10-March-2020 to 10-August-2020. Quick breakdown of my results and success rates week-over-week; In this particular case, filtered for the period of 10-March-2020 to 10-August-2020. Using TimescaleDB At the beginning, I tried to save the collected data in simple files, but quickly realized that wasn’t a good way to store and process this data. I started looking for an alternative: a performant database. I went through several databases, and all of them always lacked something I wound up needing to continue my project. I tried MongoDB, InfluxDB, and Druid, but none of them 100% met my needs. Of the databases I tried, InfluxDB was a good option; however, every query that I tried to run was painful, due to their own query language (InfluxQL). As soon as my series started to grow exponentially to higher levels, the server didn't have enough memory to handle them all in real-time. This is because the current storage engine requires more and more allocated memory for each series. I have a large number of unique metrics, so the process ran out of available memory quickly, InfluxDB TSM I handle somewhat large amounts of data every day, especially on days with many market movements. On average, I’m ingesting around 20k records/market, or 3.6 million total records, per day (20k*182 markets) and even with this huge amount of data, my query response time is in the milliseconds. require This is where TimescaleDB started to shine for me. It gave me fast real-time aggregations, built-in time-series functions, high ingestion rates – and it didn’t elevated memory usage to do all of this. In addition to this raw market data, a common use case for me is to analyze the data in different time frames (e.g., 1min, 5min, 1hr, etc.) I maintain these records in a pre-computed aggregate to increase my query performance and allow me to make faster decisions about whether or not to enter a position. For example, here’s a simple query that I use a lot to follow the performance of my trades on a daily or weekly basis (daily in this case): time_group, total_trades, positive_trades, 
	negative_trades, ( * (positive_trades / total_trades), ) success_rate, profit gross_profit, ((profit - (total_trades * )), ) net_profit ( time_bucket( , buy_at:: ):: time_group, (*) total_trades, ( profit > ):: positive_trades, ( profit <= ):: negative_trades, ( (profit), ) profit trade time_group time_group 
) T time_group SELECT ROUND 100 2 AS as ROUND 0.15 2 AS FROM SELECT '1 day' TIMESTAMP DATE AS COUNT AS SUM CASE WHEN 0 THEN 1 ELSE 0 END NUMERIC AS SUM CASE WHEN 0 THEN 1 ELSE 0 END NUMERIC AS ROUND SUM 2 AS FROM GROUP BY ORDER BY ORDER BY And, I often use this function to : measure market volatility, decomposing the range of a market pair in a period tr(_symbol , _till ) ( ZONE, ( , ), ( , )) plpgsql $$ candlestick ( * candlestick c c.symbol = _symbol c.time > () - _till ) d.time, ( (a, b, c)) :: ( , ) , ( (a, b, c) / d.close) :: ( , ) ( today.time, today.close, today.high - today.low a, ( (today.high - yesterday.close), ) b, ( (today.low - yesterday.close), ) c candlestick today LATERAL ( yesterday.close candlestick yesterday yesterday.time < today.time yesterday.time ) yesterday today.time > () - _till) d; ; $$; atr(_interval , _symbol , _till ) ( ZONE, ( , ), ( , )) plpgsql $$ true_range ( * tr(_symbol, _till) ) tr.date, avg.sma , avg.sma_percent true_range tr LATERAL ( (lat.result) sma, (lat.percent) sma_percent ( * true_range inr inr.date <= tr.date inr.date _interval
			 ) lat
		) tr.date > () - _till tr.date; ; $$; * atr( , , ) CREATE OR REPLACE FUNCTION TEXT INTERVAL RETURNS TABLE date TIMESTAMP WITHOUT TIME result NUMERIC 9 8 percent NUMERIC 9 8 LANGUAGE AS DECLARE BEGIN RETURN QUERY WITH AS SELECT FROM WHERE AND NOW SELECT GREATEST NUMERIC 9 8 as result GREATEST NUMERIC 9 8 as percent FROM SELECT as COALESCE ABS 0 COALESCE ABS 0 FROM LEFT JOIN SELECT FROM WHERE ORDER BY DESC LIMIT 1 ON TRUE WHERE NOW END CREATE OR REPLACE FUNCTION INT TEXT INTERVAL RETURNS TABLE date TIMESTAMP WITHOUT TIME result NUMERIC 9 8 percent NUMERIC 9 8 LANGUAGE AS DECLARE BEGIN RETURN QUERY WITH AS SELECT FROM SELECT result percent FROM INNER JOIN SELECT avg avg FROM SELECT FROM WHERE ORDER BY DESC LIMIT avg ON TRUE WHERE NOW ORDER BY END SELECT FROM 14 'BNBBTC' '4 HOURS' ORDER BY date My current deployment & future plans My current architecture. To develop my bot and all its capabilities, I used Node.js as my main programming language and various libraries: to communicate between all my modules without overengineering, to train and deploy all my machine learning models, and for technical indicator calculation, as well as various others. cote tensorFlow tulind I modified some to meet my needs and created some from scratch, including a , a level calculator for , and . candlestick recognition pattern support/resistance Fibonacci retracement Today, I have a total of 55 markets (which are re-evaluated every month, based on trade simulation performance) that trade simultaneously 24/7; when all my strategy conditions are met, a trade is automatically opened. The bot respects my capital management system, which is basically to limit myself to 10 opened positions and only using 10% of the available capital at a given time. To keep track of the results of an open trade, I use dynamic and . Trailing Stop Loss Trailing Take Profit The process of re-evaluating a market requires a second instance of my bot that runs in the background and uses my main strategy to simulate trades in all Bitcoin markets. When it detects that a market is doing well, based on the metrics I track, that market enters the main bot instance and starts live trading. The same applies to those that are performing poorly; as soon as the main instance of my bot detects things are going badly, the market is removed from the main instance and the second instance begins tracking it. If it improves, it's added back in. As every developer likely knows all too well, the process of building a software is to always improve it. Right now, I’m trying to improve my capital management system using . Kelly Criterion Why ? TimescaleDB For my use case, I’ve found TimescaleDB is a powerful and solid choice: it’s fast with reliable ingest rates, efficiently stores and compresses a huge dataset in a way that’s manageable and cost-effective, and gives me real-time aggregation functionality. The , , and this about managing and processing huge time-series datasets is all pretty easy to understand and follow – and the TimescaleDB team is responsive and helpful (and they always show up in community discussions, like ). TimescaleDB website core documentation blog post my Reddit AMA It’s been easy and straightforward to scale, without adding any new technologies to the stack. And, as an SQL user, TimescaleDB adds very little maintenance overhead, especially compared to learning or maintaining a new database or language. This post is slightly modified from original post on the TimescaleDB blog (read it here ).

How I Power My Trading Bot with TimescaleDB

The Spatial Web Protocol: Ushering in a New Era of Internet and AI Integration

Stories, News & Mentions about TIMESCALE

Timescale’s Stories on HackerNoon