2ndQuadrant is now part of EDB

Bringing together some of the world's top PostgreSQL experts.

2ndQuadrant | PostgreSQL
Mission Critical Databases
  • Contact us
  • EN
    • FR
    • IT
    • ES
    • DE
    • PT
  • Support & Services
  • Products
  • Downloads
    • Installers
      • Postgres Installer
      • 2UDA – Unified Data Analytics
    • Whitepapers
      • Business Case for PostgreSQL Support
      • Security Best Practices for PostgreSQL
    • Case Studies
      • Performance Tuning
        • BenchPrep
        • tastyworks
      • Distributed Clusters
        • ClickUp
        • European Space Agency (ESA)
        • Telefónica del Sur
        • Animal Logic
      • Database Administration
        • Agilis Systems
      • Professional Training
        • Met Office
        • London & Partners
      • Database Upgrades
        • Alfred Wegener Institute (AWI)
      • Database Migration
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
  • Postgres Learning Center
    • Webinars
      • Upcoming Webinars
      • Webinar Library
    • Whitepapers
      • Business Case for PostgreSQL Support
      • Security Best Practices for PostgreSQL
    • Blog
    • Training
      • Course Catalogue
    • Case Studies
      • Performance Tuning
        • BenchPrep
        • tastyworks
      • Distributed Clusters
        • ClickUp
        • European Space Agency (ESA)
        • Telefónica del Sur
        • Animal Logic
      • Database Administration
        • Agilis Systems
      • Professional Training
        • Met Office
        • London & Partners
      • Database Upgrades
        • Alfred Wegener Institute (AWI)
      • Database Migration
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
    • Books
      • PostgreSQL 11 Administration Cookbook
      • PostgreSQL 10 Administration Cookbook
      • PostgreSQL High Availability Cookbook – 2nd Edition
      • PostgreSQL 9 Administration Cookbook – 3rd Edition
      • PostgreSQL Server Programming Cookbook – 2nd Edition
      • PostgreSQL 9 Cookbook – Chinese Edition
    • Videos
    • Events
    • PostgreSQL
      • PostgreSQL – History
      • Who uses PostgreSQL?
      • PostgreSQL FAQ
      • PostgreSQL vs MySQL
      • The Business Case for PostgreSQL
      • Security Information
      • Documentation
  • About Us
    • About 2ndQuadrant
    • 2ndQuadrant’s Passion for PostgreSQL
    • News
    • Careers
    • Team Profile
  • Blog
  • Menu Menu
You are here: Home1 / Blog2 / Gianni's PlanetPostgreSQL3 / Some ideas about low-level resource pooling in PostgreSQL
Gianni Ciolli

Some ideas about low-level resource pooling in PostgreSQL

July 6, 2010/0 Comments/in Gianni's PlanetPostgreSQL, PostgreSQL /by Gianni Ciolli

Last week at the CHAR(10) conference we had a workshop on “Cloud Databases”. To put it simply: what to do when the use case requirements exceed the resources available in the database server.
This was a main topic of the whole conference, and several solutions have been illustrated during the day. A common theme has been that no solution fits all the use cases, and that each solution comes with its cost; hence you have to choose the solution that your use case can afford.


Another common (albeit implicit) point has been the focus on “high-level” solutions, that is: connecting several database servers at a higher level to emulate a single server with larger resources.
An obvious advantage is that you don’t need to alter the well-scrutinised PostgreSQL code; a drawback is that using multiple database servers with their independent timelines you are losing some useful properties. Two examples: the partial loss of transactional semantics generates conflicts; pre-parsing each query outside the database introduces limitations on the accepted queries.
The discussion was quite interesting, and when Dimitri Fontaine mentioned remote tablespaces I started wondering around a related but distinct idea, namely: whether a lower-level approach to the problem of resource pooling would really be impractical. Before I could elaborate on the details the workshop ended, and I could only sketch the idea to some of the people that were around the whiteboard (among which Gabriele Bartolini, Nic Ferrier, Marko Kreen, Hannu Krosing, Greg Smith) together with the basic questions “does it look feasible?” and “does that resemble something you already know?”.
A brief sketch: an application stack can be represented in this way

(application) --> (connection) --> (db server) --> (resources)

where the resources used by the database include storage, RAM and CPUs. The purpose is to allow the application to command more resources in order to increase capacity and speed. “Clever” applications that manage several databases can be represented as

(application) --> (connection) --> (db server) --> (resources)
|
+---------> (connection) --> (db server) --> (resources)

while “connection pooling” solutions can be represented as

(application) --> (connection) --> (db server) --> (resources)
|
+---------> (db server) --> (resources)

by “lower-level” solutions I mean something like

(application) --> (connection) --> (db server) --> (resources)
|
+---------> (resources)

which might resemble something familiar, but it is not what I am proposing here. To explain the difference I can increase the detail and write

(resources) = (virtual resources) --> (physical resources)

to represent the fact that at the lowest level you can have a non-trivial mapping between physical objects and virtual ones. For instance, SAN storage or RAID striping can provide larger virtual disks by joining together smaller physical disks. Such cases could be pictured as

(application) --> (connection) --> (db server) --> (virt.res.) --> (ph.res.)
|
+--------> (ph.res.)

My proposal is to pool resources at the database server level, so that we can have a more efficient “virtualisation” by using the knowledge of the specific use cases for each resource (CPU, RAM, disk), and at the same time we can avoid may of the difficulties of the transactional paradigm. The picture would be:

(application) --> (connection) --> (db server) --> (virt.res.) --> (ph.res.)
|
+--------> (virt.res.) --> (ph.res.)

The advantage is that we don’t need to manage all the possible use cases for each virtual resource; we just have to manage (and optimise for) the use cases that are actually needed by PostgreSQL. For instance: WAL should still be written in local “unvirtualised” storage, the bgwriter will access local and remote resources (RAM and disk), etc.
Some final words about reliability. To operate properly the whole system needs each subsystem; partial failures are not managed, because this architecture is not redundant. It is a distributed system, but not shared. If this architecture could provide cheap and simple scalability via a virtual database server which is functionally equivalent to a physical server with larger resources, then high availability could be obtained in the standard way by setting up two identical virtual servers in a Hot Standby configuration.
Network quality has a large impact on the overall performance; this design might be useful only if you have an array of machines in the same LAN, not only for speed reasons but also because a network failure would actually be a system failure. Even with these restrictions, my opinion is that having this option would be quite useful.
This is still a sketch, to be used as a reference for further discussion. Next possible steps:

  • to make a detailed list of the resource use cases
  • to decide which technologies can help best in each use case
  • to estimate the actual performance/development costs
Tags: database, distributed, low-level, postgres, PostgreSQL, RDBMS, resource pooling
Share this entry
  • Share on Facebook
  • Share on Twitter
  • Share on WhatsApp
  • Share on LinkedIn
0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Search

Get in touch with us!

Recent Posts

  • Random Data December 3, 2020
  • Webinar: COMMIT Without Fear – The Beauty of CAMO [Follow Up] November 13, 2020
  • Full-text search since PostgreSQL 8.3 November 5, 2020
  • Random numbers November 3, 2020
  • Webinar: Best Practices for Bulk Data Loading in PostgreSQL [Follow Up] November 2, 2020

Featured External Blogs

Tomas Vondra's Blog

Our Bloggers

  • Simon Riggs
  • Alvaro Herrera
  • Andrew Dunstan
  • Craig Ringer
  • Francesco Canovai
  • Gabriele Bartolini
  • Giulio Calacoci
  • Ian Barwick
  • Marco Nenciarini
  • Mark Wong
  • Pavan Deolasee
  • Petr Jelinek
  • Shaun Thomas
  • Tomas Vondra
  • Umair Shahid

PostgreSQL Cloud

2QLovesPG 2UDA 9.6 backup Barman BDR Business Continuity community conference database DBA development devops disaster recovery greenplum Hot Standby JSON JSONB logical replication monitoring OmniDB open source Orange performance PG12 pgbarman pglogical PG Phriday postgres Postgres-BDR postgres-xl PostgreSQL PostgreSQL 9.6 PostgreSQL10 PostgreSQL11 PostgreSQL 11 PostgreSQL 11 New Features postgresql repmgr Recovery replication security sql wal webinar webinars

Support & Services

24/7 Production Support

Developer Support

Remote DBA for PostgreSQL

PostgreSQL Database Monitoring

PostgreSQL Health Check

PostgreSQL Performance Tuning

Database Security Audit

Upgrade PostgreSQL

PostgreSQL Migration Assessment

Migrate from Oracle to PostgreSQL

Products

HA Postgres Clusters

Postgres-BDR®

2ndQPostgres

pglogical

repmgr

Barman

Postgres Cloud Manager

SQL Firewall

Postgres-XL

OmniDB

Postgres Installer

2UDA

Postgres Learning Center

Introducing Postgres

Blog

Webinars

Books

Videos

Training

Case Studies

Events

About Us

About 2ndQuadrant

What does 2ndQuadrant Mean?

News

Careers 

Team Profile

© 2ndQuadrant Ltd. All rights reserved. | Privacy Policy
  • Twitter
  • LinkedIn
  • Facebook
  • Youtube
  • Mail
PostgreSQL, FreeBSD, and Free Dog Food Installing PostGIS on Greenplum Single Node Edition
Scroll to top
×