Understanding and minimizing Postgres table bloat

As promised in the CFP for POSETTE: An Event for Postgres 2024, all of the talk selection decisions were emailed out on April 17th. Our talk selection work has now concluded, with the possible exception of accepting proposals from the Reserve list. So what’s next? First I want to thank all of you Postgres people who submitted such amazing talk proposals into the CFP for POSETTE, now in its 3rd year. I was so impressed by the submissions and wish we could have accepted more of them. And I also want to thank Alicja Kucharczyk, Daniel Gustafsson, and Melanie Plageman from POSETTE’s Talk Selection Team for contributing their time and expertise to collaborate with me to select the talks for this year’s virtual POSETTE event. It’s not easy to carefully read through and review 184 talk proposals—in just 8 days—to come up with the program for an event like #PosetteConf. That’s right, 184 talk proposals—from 120 unique speakers. (The CFP had a maximum of 4 submissions per speaker.) With just 38 talks to accept this year, that means POSETTE 2024 has a ~20% talk acceptance rate. Bottom line, we had some difficult decisions to make. So many great talk proposals we had to lengthen the POSETTE schedule to make space The original POSETTE plan for 2024 was to have 4 livestreams with 9 talks each. The math looked like this: Each livestream would have: 1 invited keynote—not selected through the CFP talk selection process, but rather an invited keynote speaker 8 unique talks selected via the CFP process Hence, 36 talks total: 32 talks selected via the CFP process + 4 unique keynotes However, the best laid plans of mice and men and all that, we had to throw that math out the window. There were too many good talk proposals. Luckily the talk production team led by Teresa Giacomini was able to rejigger their recording schedules to make room for 6 more talks. So the final POSETTE 2024 schedule will have: 42 talks total: 38 [...]

You could be saving money every month on databases costs with a smarter data retention policy. One of the primary reasons, and a huge benefit of partitioning is using it to automatically archive your data. For example, you might have a huge log table. For business purposes, you need to keep this data for 30 days. This table grows continually over time and keeping all the data makes database maintenance challenging. With time-based partitioning, you can simply archive off data older than 30 days. The nature of most relational databases means that deleting large volumes of data can be very inefficient and that space is not immediately, if ever, returned to the file system. PostgreSQL does not return the space it reserves to the file system when normal deletion operations are run except under very specific conditions: the page(s) at the end of the relation are completely emptied a VACUUM FULL/CLUSTER is run against the relation (exclusively locking it until complete) If you find yourself needing that space back more immediately, or without intrusive locking, then partitioning can provide a much simpler means of removing old data: drop the table. The removal is nearly instantaneous (barring any transactions locking the table) and immediately returns the space to the file system. pg_partman, the Postgres extension for partitioning, provides a very easy way to manage this for time and integer based partitioning. pg_partman daily partition example Recently pg_partman 5.1 was released that includes new features such as list partitioning for single value integers, controlled maintenance run ordering, and experimental support for numeric partitioning. This new version also includes several bug fixes, so please update to the latest release when possible! All examples were done using this latest version. https://github.com/pgpartman/pg_partman First lets get a simple, time-based daily partition set going CREATE TABLE public.time_stuff (col1 int , col2 text default 'stuff' [...]

In a PostgreSQL database, sequences provide a convenient way to generate a unique identifier, and are often used for key generation. From the community, PostgreSQL provides functions and SQL language to help manage sequence generation, but the sequences themselves are not without limitations in a multi-master environment. Snowflake sequences from pgEdge work seamlessly in a multi-master PostgreSQL cluster to remove those limitations so your data can thrive at the network edge. Why are Sequences an Issue? In a distributed multi-master database system, sequences can get complicated. Ensuring consistency and uniqueness across the nodes in your cluster is a problem if you use PostgreSQL sequences; the Snowflake extension steps up to automatically mitigate this issue.PostgreSQL sequence values are prepared for assignment in a table in your PostgreSQL database; as each sequence value is used, the next sequence value is incremented. Changes to the next available sequence value are not replicated to the other nodes in your replication cluster. In a simple example, you might have a table on node , with 10 rows, each with a primary key that is assigned a sequence value from 1 to 10; the next prepared sequence value on will be 11. Rows are replicated from  to  without issue until you add a row on . The PostgreSQL sequence value table on  has not been incrementing sequence values in step with the sequence value table on . When you add a row on , it will try to use the next available sequence value ( will be 1 if you haven't added a row on ), and the  will fail because a row with the primary key value of 1 already exists. This disorder can be monitored and corrected by manually coordinating the PostgreSQL sequences between nodes in the cluster, but that quickly becomes complicated and potentially impacts the user experience as you add more nodes to the cluster.  Introducing Snowflake Sequences An alternative to using PostgreSQL sequences is to use a guaranteed unique Snowflake sequence. Snowflake sequences are repre[...]

This is the second article in a series that explores advanced strategies for scaling PostgreSQL databases in Kubernetes with the help of CloudNativePG. This article focuses on horizontal table partitioning and tablespaces and how they can be used to manage large datasets. By partitioning tables based on specific criteria and optimising storage with tablespaces, PostgreSQL users can achieve better scalability and performance in cloud-native environments, just like they could in traditional VMs or bare metal deployments.

Why do we need vector databases? The proliferation of embeddings immediately brought forth the need to efficiently store, index, and search these arrays of floats. However, these steps are just a small piece of the overall technology stack required to make use of embeddings. The task of transforming source data to embeddings and the serving of the transformer models that make this happen is often left as a task to the application developer. If that developer is part of a large organization, they might have a machine learning or data engineering team to help them. But in any case, the generation of embeddings is not a one-time task, but a lifecycle that needs to be maintained. Embeddings need to be transformed on every search request, and inevitably the new source data is generated or updated, requiring a re-compute of embeddings. Consistency between model training and inference Traditionally, machine learning projects have two distinct phases: training and inference. In training, a model is generated from a historical dataset. The data that go into the model training are called features, and typically undergo transformations. At inference, the model is used to make predictions on new data. The data incoming into the model for inference requires precisely the same transformations that were conducted at training. For example in classical ML, imagine you have a text classification model trained on TF-IDF vectors. At inference, any new text must undergo the same preprocessing (tokenization, stop word removal) and then be transformed into a TF-IDF vector using the same vocabulary as during training. If there’s a discrepancy in this transformation, the model’s output will be unreliable. Similarly, in a vector database used for embedding search, if you’re dealing with text embeddings, a new text query must be converted into an embedding using the same model and preprocessing steps that were used to create the embeddings in the database. Embeddings stored in the database using OpenAI’s text-embedding-a[...]

This post contained content that could not be rendered in the Atom feed. Please use the official post link: https://tembo.io/blog/operationalizing-vectordbs-on-postgres

Recently I joined Ry Walker, CEO of Tembo, as a guest on the Hacking Postgres podcast. Hacking Postgres has had a lot of great Postgres contributors as guests on the show, so I was honored to be a part of it being that my contributions are more in the form of developer education and advocacy. Ry asked me about when I got started with PostgreSQL and what my role looks like today. PostgreSQL Origin Ry has also been a Ruby on Rails programmer, so that was a fun background we shared. We both started on early versions of Ruby on Rails in the 2000s, and were also early users of Heroku in the late 2000s. Since PostgreSQL was the default DB for Rails apps deployed on Heroku, for many Rails programmers it was the first time they used PostgreSQL. Heroku valued the fit and finish of their hosted platform offering, and provided best in class documentation and developer experience as a cutting edge platform as a service (PaaS). The popularity of that platform helped grow the use of PostgreSQL amongst Rails programmers even beyond Heroku. For me, Heroku was where I really started using PostgreSQL and learning about some of the performance optimization tactics “basics” as a web app developer. Meeting The Tembo Team Besides Ry, I’ve also had the chance to meet more folks from Tembo. Adam Hendel is a founding engineer and also based here in Minnesota. I also met Samay Sharma, PostgreSQL contributor and now CTO of Tembo, at PGConf NYC 2023 last Fall. While not an employee or affiliated with the company at all, it’s been interesting to track what they’re up to, and get little glimpses into starting up a whole company that’s focused on leveraging the power and extensibility of PostgreSQL. If you’d like to learn more about Adam’s background, Adam was the guest for Season 1, Episode 2 of Hacking Postgres, which you can find here: https://tembo.io/blog/hacking-postgres-ep2 Using PostgreSQL with Ruby on Rails Apps Ruby on Rails as a web development framework has great support via the ORM - [...]

A guide to Postgres roles and privileges