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

Timescale

Strategies for Implementing Columnar Compression in Large PostgreSQL Databases

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

Architecting a Robust Vector Embedding Creation System for PostgreSQL Data 

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.

স্কেলিং 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

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

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

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.

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 中数据库大小减小的策略：基于使用的方法

Timescale

timescale.com

250 employees

Since 2015

COMPANY RANKING

Timescale

EVERGREEN INDEX #3111

See more notable links about Timescale

Apply to get your Tech Company News Page live on HackerNoon.com today!

Read More Tech Stories Related to #Timescale

Timescale

Timescale

timescale.com

250 employees

Since 2015

COMPANY RANKING

Timescale

EVERGREEN INDEX #3111

Related CompaniesRank

Timescale Story MentionsComplete List

See more notable links about Timescale

Apply to get your Tech Company News Page live on HackerNoon.com today!

Read More Tech Stories Related to #Timescale

Timescale