May 2024 Summaries

This article discusses the creation of a hybrid search engine using Cohere and pgvector on PostgreSQL. Hybrid search combines keyword and semantic search methods to enhance result quality. The implementation involves generating dense and sparse embeddings, storing them in Timescale's PostgreSQL database, retrieving results, reranking them, and generating final lists of relevant documents for queries. The hybrid search engine can be applied to various applications, such as advanced retrieval-augmented generation (RAG) systems. The article also provides a step-by-step guide on setting up the necessary libraries, creating a table in PostgreSQL, inserting data, and implementing keyword and semantic search functions.

In this tutorial, we will explore various techniques for cleaning data using PostgreSQL and TimescaleDB. We'll cover the following topics: 1. Adding columns together 2. Converting strings to numbers 3. Creating a view 4. Filtering rows with WHERE 5. Extracting parts of timestamps 6. Renaming values using PostgreSQL and Python 7. Filling in missing data with gap-filling functions 8. Ignoring missing data By the end of this tutorial, you should feel more comfortable with exploring some of the possibilities that PostgreSQL data cleaning and TimescaleDB data cleaning provide. You'll learn how to clean data directly within your database, which can save time in the long run compared to repetitive scripting tasks. Let's get started! 1. Adding columns together: To add two numeric columns together, you can use the + operator. For example, if you have a table called 'energy_data' with columns 'energy_reading_1' and 'energy_reading_2', you can create a new column called 'total_energy' by adding these two columns together: ```sql ALTER TABLE energy_data ADD COLUMN total_energy NUMERIC; UPDATE energy_data SET total_energy = energy_reading_1 + energy_reading_2; ``` In Python, you can use the '+' operator to add two columns together: ```python import pandas as pd # Assuming df is your DataFrame df['total_energy'] = df['energy_reading_1'] + df['energy_reading_2'] ``` 2. Converting strings to numbers: To convert a string column to a numeric column, you can use the TO_NUMBER() function in PostgreSQL or the map() function in Python. For example, if you have a table called 'energy_data' with a column called 'cost_str' containing costs as strings (e.g., '$10.50'), you can create a new numeric column called 'cost' by converting these strings to numbers: ```sql ALTER TABLE energy_data ADD COLUMN cost NUMERIC; UPDATE energy_data SET cost = TO_NUMBER(cost_str, '9999999999.99'); ``` In Python, you can use the map() function to convert a string column to a numeric column: ```python import pandas as pd # Assuming df is your DataFrame df['cost'] = df['cost_str'].map(lambda x: float(x.strip('$'))).astype(float) ``` 3. Creating a view: A view is a virtual table that contains the result set of an SQL SELECT statement. You can use views to simplify complex queries, restrict access to certain data, or encapsulate business logic. To create a view in PostgreSQL, you can use the CREATE VIEW statement: ```sql CREATE VIEW energy_view AS SELECT * FROM energy_data; ``` In Python, you can use the pandas DataFrame method to_sql() to create a view from a DataFrame: ```python import pandas as pd from sqlalchemy import create_engine # Assuming df is your DataFrame and engine is your database connection df.to_sql('energy_view', con=engine, if_exists='replace') ``` 4. Filtering rows with WHERE: The WHERE clause allows you to filter rows based on specific conditions. For example, if you want to select only the rows where 'day_of_week' is equal to 1 (Monday), you can use the following query: ```sql SELECT * FROM energy_data WHERE day_of_week = 1; ``` In Python, you can use boolean indexing with DataFrame conditions to filter rows based on specific conditions: ```python import pandas as pd # Assuming df is your DataFrame df_monday = df[df['day_of_week'] == 1] ``` 5. Extracting parts of timestamps: The EXTRACT() function allows you to extract specific parts of a timestamp, such as the hour or minute. For example, if you want to select only the rows where 'time' is between 7:00 p.m. and 8:00 p.m., you can use the following query: ```sql SELECT * FROM energy_data WHERE EXTRACT(HOUR FROM time) BETWEEN 19 AND 20; ``` In Python, you can use the pandas DataFrame method between_time() to filter rows based on specific parts of timestamps: ```python import pandas as pd # Assuming df is your DataFrame df_7to8 = df.between_time('19:00', '20:00') ``` 6. Renaming values using PostgreSQL and Python: Another valuable technique for cleaning data is being able to rename various items or remap categorical values. The importance of this skill is amplified by the popularity of this Python data analysis question on StackOverflow. The question states “How do I change a single index value in a pandas DataFrame?”. Since PostgreSQL and TimescaleDB use relational table structures, renaming unique values can be fairly simple using PostgreSQL data cleaning. When renaming specific index values within a table, you can do this “on the fly” by using PostgreSQL’s CASE statement within the SELECT query. Let’s say I don’t like Sunday being represented by a 0 in the day_of_week column, but would prefer it to be a 7. I can do this with the following query: ```sql SELECT type, time, usage, cost, is_weekend, -- you can use case to recode column values CASE WHEN day_of_week = 0 THEN 7 ELSE day_of_week END FROM energy_usage ``` In this case, python has similar mapping functions. ```python energy_df['day_of_week'] = energy_df['day_of_week'].map({0 : 'Sunday', 1 : 'Monday', 2: 'Tuesday', 3: 'Wednesday', 4: 'Thursday', 5: 'Friday', 6: 'Saturday'}) print(energy_df.head(20)) ``` 7. Filling in missing data with gap-filling functions: Another common problem in the PostgreSQL data cleaning process is having missing data. For the dataset we are using, there are no obviously missing data points. However, it's very possible that with evaluation, we could find missing hourly data from a power outage or some other phenomenon. This is where the gap-filling functions TimescaleDB offers could come in handy. When using algorithms, missing data can often have significant negative impacts on the accuracy or dependability of the model. Sometimes, you can navigate this problem by filling in missing values with reasonable estimates and TimescaleDB actually has built-in functions to help you do this. For example, let's say that you are modeling energy usage over individual days of the week and a handful of days have missing energy data due to a power outage or an issue with the sensor. We could remove the data or try to fill in the missing values with reasonable estimations. For today, let's assume that the model I want to use would benefit more from filling in the missing values. As an example, I created some data. I called this table energy_data and it is missing both time and energy readings for the timestamps between 7:45 a.m. and 11:30 a.m. ```sql SELECT --here I specified that the data should increment by 15 mins time_bucket_gapfill('15 min', time) AS timestamp, interpolate(avg(energy)), locf(avg(energy)) FROM energy_data --to use gapfill, you will have to take out any time data associated with null values. You can do this using the IS NOT NULL statement WHERE energy IS NOT NULL AND time > '2021-01-01 07:00:00.000' AND time < '2021-01-01 13:00:00.000' GROUP BY timestamp ORDER BY timestamp; ``` In Python, you can use the pandas DataFrame method fillna() to fill in missing values with a specific value or interpolate between existing values: ```python energy_test_df['time'] = pd.to_datetime(energy_test_df['time']) energy_test_df_locf = energy_test_df.set_index('time').resample('15 min').fillna(method='ffill').reset_index() energy_test_df = energy_test_df.set_index('time').resample('15 min').interpolate().reset_index() energy_test_df['locf'] = energy_test_df_locf['energy'] print(energy_test_df) ``` 8. Ignoring missing data: The following query shows how I could ignore the missing data. I wanted to include this to show you just how easy it can be to exclude null data. Alternatively, I could use a WHERE clause to specify the times I like to ignore (the second query). ```sql SELECT * FROM energy_data WHERE energy IS NOT NULL; SELECT * FROM energy_data WHERE time <= '2021-01-01 07:45:00.000' OR time >= '2021-01-01 11:30:00.000'; ``` PostgreSQL Data Cleaning Wrap-Up After reading through these various techniques, I hope you feel more comfortable with exploring some of the possibilities that PostgreSQL data cleaning and TimescaleDB data cleaning provide. By cleaning data directly within my database, I am able to perform a lot of my cleaning tasks a single time rather than repetitively within a script, thus saving me time in the long run. If you're looking to save time and effort while cleaning your data for analysis, definitely consider using PostgreSQL and TimescaleDB. In my next posts, I'll discuss techniques for transforming data using PostgreSQL and TimescaleDB. I'll then use everything we've learned together to benchmark data munging tasks in PostgreSQL and Python vs. pandas. The final blog post will walk you through the full process on a real dataset by conducting a deep-dive into data analysis with TimescaleDB (for data munging) and Python (for modeling and visualizations). If you have questions about TimescaleDB, time-series data, or any of the functionality mentioned above, join our community Slack, where you'll find an active community of time-series enthusiasts and various Timescale team members. If you’re ready to see the power of TimescaleDB and PostgreSQL right away, you can sign up for a free 30-day trial or install TimescaleDB and manage it on your current PostgreSQL instances. We also have a bunch of great tutorials to help get you started. Until next time!

This guide provides a comprehensive overview of using Psycopg2, one of the most popular PostgreSQL adapters, to integrate PostgreSQL with Python code. It covers various aspects such as installation, connection management, query execution, and troubleshooting common errors. Additionally, it highlights the differences between Psycopg2 and SQLAlchemy, another popular adapter for Python applications. The guide also provides examples of using Psycopg2 in combination with TimescaleDB, a PostgreSQL extension that enhances performance for data-intensive applications dealing with time-series data.

In this tutorial, we will explore how to use TimescaleDB's percentile approximation hyperfunctions for time-series data analysis. We will cover the basics of percentiles and why they are useful, as well as the benefits of using percentile approximations over exact percentiles in PostgreSQL. We will also dive into the details of the underlying algorithms used by TimescaleDB's percentile approximation hyperfunctions and how to choose between them based on your specific use case. Finally, we will demonstrate how to use these hyperfunctions with real-world examples and discuss their potential applications in various industries. REFERENCES: 1. "TimescaleDB Documentation - Percentile Approximation Hyperfunctions" (https://docs.timescale.com/using-timescaledb/latest/how-to-guides/approximate-percentiles/) 2. "PostgreSQL Documentation - Aggregate Functions" (https://www.postgresql.org/docs/current/functions-aggregate.html) 3. "Two-Step Aggregation Design Patterns in PostgreSQL" (https://www.timescale.com/blog/two-step-aggregation-design-patterns-in-postgresql/) 4. "Introduction to Time Series Databases and TimescaleDB" (https://www.timescale.com/developers/book/introduction-to-time-series-databases-and-timescaledb/)

OpenSauced is using Timescale, a time-series database built on PostgreSQL, to provide insights and metrics on open-source projects at GitHub's massive scale. The company chose Timescale due to its performance results, compatibility with their existing technology stack, and cost-effective pricing model. OpenSauced has also integrated pgvector, a Postgres extension for vector storage, to develop an AI feature called StarSearch that performs similarity search across relevant GitHub events. This allows users to ask questions and receive answers based on the most relevant information from the database. Overall, Timescale's performance and capabilities have been crucial in enabling OpenSauced to scale its operations and offer innovative solutions for open-source project analysis.

The upcoming release of PostgreSQL 17 is generating excitement due to its focus on enhancing performance, scalability, security, and compatibility while introducing new features to meet evolving user needs. Notable changes in version 17 include the decision to drop support for AIX and the transition from Autotools to Meson build system. Some of the most exciting commits include pg_createsubscriber, support for MERGE PARTITIONS and SPLIT PARTITIONS, incremental file system backup, enabling the failover of logical replication slots, and allowing EXPLAIN to report optimizer memory usage. Timescale has contributed 90 commits (3.5 percent) during the PostgreSQL 17 cycle, including the SLRU move to 64-bit indexes, refactoring for transitive comparisons, and introducing standard_ExplainOneQuery.

Hopthru, a company that aims to make public transport the most compelling mode of urban travel, uses TimescaleDB to power real-time transit analytics from a 1 TB data table. The team at Hopthru helps transit agencies analyze and make data-driven decisions to improve public transportation. They use Timescale's hypertables and continuous aggregates features to reduce query times significantly. With managed services like Timescale for AWS, developers can focus on building the application rather than managing infrastructure.

The concept of using PostgreSQL for Everything is gaining popularity as Postgres increases in usage. This approach aims to reduce technical sprawl by collapsing the tech stack, thereby reducing complexity and operational overhead. PostgreSQL is a versatile database that can handle various workloads such as full-text search, time-series data, vectors for AI, and analytics. Using PostgreSQL for multiple purposes simplifies data flow management and allows teams to focus on feature development. While the "best tool for the job" principle may sometimes be relevant, it is essential not to overengineer solutions prematurely. Choosing a known, robust, and mature technology like PostgreSQL can help manage complexity and maintain system stability.

This article explores the implementation of RAG (retrieval-augmented generation) applications using Amazon Bedrock and LangChain. It covers setting up Amazon Bedrock, integrating with LangChain, and utilizing the potent Amazon Titan model for large language model (LLM) applications. The text also discusses how pgvector on Timescale's PostgreSQL cloud platform makes it easier to set up a vector database optimized for efficient storage and powering LLM applications with RAG.

PostgreSQL is emerging as the de facto database standard due to its rock-solid foundation, versatility through native features and extensions, and ability to replace complex data architectures with straightforward simplicity. It has taken the top spot from MySQL in popularity among professional developers. The growth of ubiquitous computing has led to a Cambrian explosion of databases, increasing software complexity and slowing down development. PostgreSQL is becoming a platform that can be used for reliability, scalability, data analysis, and more, allowing developers to spend less time on the plumbing and more time building the future.

In this edition of "Community Member Spotlight", Adam McCrea, founder and developer of Judoscale, explains how he uses Timescale to help developers save on costs by handling a million inserts per minute with frequent, automatic data retention policies and data rollups. Judoscale is a tool that helps engineering teams manage their server resources by automatically scaling resources based on traffic metrics and other factors. The majority of their customers are still on the Heroku hosting platform, where they access Judoscale through its dashboard to see real-time metrics on their server resources. Before using Timescale, Judoscale had a homegrown time-series solution with Redis that was not scaling well as their business grew. They found Timescale built on top of PostgreSQL and decided to move everything to it for better stability, reliability, and faster development. The automatic data retention policy runs every 10 minutes, and they only retain the most recent hour of data, dropping everything else. For the data rollups, they retain two days of aggregations, running that retention every day. They have also expanded beyond Heroku to provide the same autoscaling experience to customers hosting their applications on AWS.