Postgres and Open Source Experts

24x7x365 since 1997

Blog

PgManage 1.0b released

Command Prompt is pleased to announce PgManage version 1.0b. This release adds two major features, 3 major bug fixes and over a dozen changes and improvements. PgManage is a Postgres centered multi-database management Open Source project.

New features:

  • Added backup/restore support for PostgreSQL
  • First version of PgManage Handbook was published to https://pgmanage.readthedocs.io/en/latest

Major Bugs fixed:

  • Fixed .AppImage compatibility issues for newer Linux distributions which do not have libcrypt installed
  • Added …

Announcing PgManage 1.0a

PgManage is a Postgres centered multi-database management Open Source project. It is a fork of the previously well received project OmniDB that had been abandoned. Command Prompt has taken the helm of this project to ensure a quality project focused on the Management of PostgreSQL and related technologies.

Major Changes from OmniDB

New features:

  • new connection management UI
  • added …

Performance Analysis of PostgreSQL Data Checksums

Recently I have been working on PostgreSQL benchmarks for its data checksums feature. This incredibly valuable option to initdb -- introduced in version 9.3 in 2013 -- allows quick detection of corrupted disk data pages. It provides the glorious opportunity to simply failover to a standby before your data becomes corrupted, rather than endure the horror of discovering the corruption afterward and attempting to recover.

But people care as much …

PostgreSQL and Financial Calculations - Part Five

The fifth and last in a series of blogs covering common mistakes in Database and Application designs for financial calculations.

Method of Rounding:

There are many methods of rounding

  1. Half Round Up
  2. Half Round Down
  3. Round Towards Zero
  4. Round Away from Zero
  5. Round Half To Even
  6. Round Half To Odd
  7. Random Round

The built-in method of rounding in PostgreSQL is Half Round Up. Unfortunately, it is not the best approach, …

PostgreSQL and Financial Calculations - Part Four

The fourth in a series of blogs covering common mistakes in Database and Application designs for financial calculations.

Database Driver, or Application Framework Created Error:

The database driver or application framework created errors are probably the hardest to find, as we are the consumer not the writer of the tool, with many assuming the casting is correct. However, we must review the documentation or the library’s code to know how …

PostgreSQL and Financial Calculations - Part Three

The third in a series of blogs covering common mistakes in Database and Application designs for financial calculations.

Order of Operations and Storing Aggregate Results:

When working with float data types, order of operations will affect the ending value.

Consider 3:

Python3:
justin@Debian10:~$ python3
Python 3.7.3 (default, Jul 25 2020, 13:03:44) 
>>> (1234.567 * 3.333333) + (1.234567 * 3.333333)
4119.338,144,732,812
>>> (1234.567 + 1.234567 ) * 3.333333 …

PostgreSQL and Financial Calculations - Part Two

The second in a series of blogs covering common mistakes in Database and Application designs for financial calculations.

Inconsistent precision scaling:

This is probably the most common mistake in database design that I observe. It is understood to use exact data types (such as numeric) and the precision must be fixed, but for whatever reason the decision is made that it’s OK for one table to use numeric(12,4),a second table …

PostgreSQL and Financial Calculations - Part One

A series on the use of data types to insure accurate financial calculations with your application.

Over my multi-decade career, I have often noticed the problematic use of real, floating, double, and fixed precision types to store and calculate financials. Most believe the application only needs two digits to the right of the decimal point for financial data. The use of only two digits assumes that many financial calculations do …

Recent blog updates

When you have been around as long as Command Prompt, you are bound to forget blogs you wrote as well as the fact that those blogs are likely exceedingly outdated. I was recently doing a review of the Command Prompt Dead Sea Scrolls and have come across two that we have updated to be accurate for the modern times of PostgreSQL.

The blogs

Postgres, where art thou?

In the 2017 article we referenced to /r/postgresql which at the time had 5,100 members. It now has 25.5k. In the same time period as pgsql-general, it generated 175 messages among community members (20 posts, 155 responses). Similarly, we mentioned the Slack channel which at the time had 1100+ members. It now hosts 18.3k subscribers with similar activity of the subreddit. The People, Postgres, Data Discord, which did not exist in 2017, has 3,579 members and is quite active over its 28 channels. The listed collaboration venues don’t take into account the thousands of members among the international or associated (Brazil, Russia, TimescaleDB, Yugabyte, NeonDB, etc…) Postgres communities.