Back to articles
AI & Cloud

Transform PostgreSQL into a High-Speed OLAP Data Warehouse Using pgAnalytics on VPS 1

A detailed guide to deploying pgAnalytics on PostgreSQL to turn your database into a high-speed OLAP data warehouse, optimized for enterprise analytics on VPS 1.

DPTCloud Engineering4 min read
Transform PostgreSQL into a High-Speed OLAP Data Warehouse Using pgAnalytics on VPS 1

Image credit: Kanenori · Pixabay Content License

pgAnalytics is an extension for PostgreSQL that enables transforming a relational database into a powerful, high-speed OLAP (Online Analytical Processing) data warehouse. With capabilities for handling complex queries, real-time analytics, and multi-dimensional analysis, pgAnalytics allows businesses to optimize data analysis without investing in dedicated analytics platforms.

On VPS 1, which offers high RAM and stable performance, deploying pgAnalytics enables the creation of an independent, cost-effective, and easy-to-maintain data analytics system. This solution is ideal for small and medium enterprises needing to process large volumes of data at low cost.

Prerequisites and Preparation Before Deployment

To deploy pgAnalytics on VPS 1, ensure the following conditions are met:

  • PostgreSQL version 10 or higher is installed and running stably.
  • SSH access to VPS 1 with root or sudo privileges.
  • Required tools such as gcc, make, and postgresql-server-dev are installed to compile the extension.
  • Port 5432 (or a custom port) is open for external PostgreSQL connections.

Deploying pgAnalytics on VPS 1

Follow these steps to install and enable pgAnalytics:


# 1. Update the system
sudo apt update && sudo apt upgrade -y

# 2. Install required packages
sudo apt install -y postgresql-server-dev-14 build-essential

# 3. Download pgAnalytics source code
git clone https://github.com/PostgresPro/pganalytics.git
cd pganalytics

# 4. Compile and install
make && sudo make install

# 5. Configure PostgreSQL to load the extension
echo "shared_preload_libraries = 'pganalytics'" >> /etc/postgresql/14/main/postgresql.conf

# 6. Restart PostgreSQL
sudo systemctl restart postgresql

Setting Up the OLAP Data Warehouse and Analytical Queries

After installation, you can create analytical tables and use real-time analysis functions:


-- Create a sales data table
CREATE TABLE sales (
    id SERIAL PRIMARY KEY,
    product_id INT,
    quantity INT,
    price DECIMAL(10,2),
    sale_date DATE
);

-- Create an analytical table by time
CREATE TABLE sales_olap AS
SELECT 
    product_id,
    SUM(quantity) AS total_quantity,
    SUM(price * quantity) AS total_revenue,
    EXTRACT(YEAR FROM sale_date) AS year,
    EXTRACT(MONTH FROM sale_date) AS month
FROM sales
GROUP BY product_id, year, month;

To test performance, run:


-- Analyze sales by year
SELECT year, SUM(total_revenue) AS total_revenue FROM sales_olap GROUP BY year;

Verification and Troubleshooting

To verify pgAnalytics is working:

  • Run SELECT pg_extension; in PostgreSQL to check if the extension is loaded.
  • Test analytical functions like pganalytics.analyze() or pganalytics.time_series().

If issues occur:

  • Extension not loaded: Verify postgresql.conf has the correct shared_preload_libraries setting.
  • Compilation errors: Ensure all PostgreSQL development packages are installed.

References

DPTCloud Tier III Infrastructure

High-RAM Cloud VPS Plans Optimized for AI, Databases & Cache

Enterprise RAID 10 NVMe SSDs, CMC & FPT District 9 uplinks with Anycast Anti-DDoS and 99.99% SLA.

View 22 VPS Plans
ECO · vn-02Recommended

VPS-4E

297.000 ₫ / mo

10% VAT included

  • CPU: 4 vCPU
  • RAM: 8 GB
  • SSD: 50 GB NVMe
ECO · vn-02

VPS-5E

614.900 ₫ / mo

10% VAT included

  • CPU: 8 vCPU
  • RAM: 16 GB
  • SSD: 100 GB NVMe
ADVANCED · vn-03Recommended

VPS-4T

548.900 ₫ / mo

10% VAT included

  • CPU: 4 vCPU
  • RAM: 8 GB
  • SSD: 50 GB NVMe

Related Articles

View all
ZaloZalo SupportDiscord0384 753 478