Why I chose PostgreSQL for my ERP Startup Project

Great and advance opensource database

I start developing an ERP and Accounting project as part-time development, and when I start, I have to decide on the database. It was clear that we are going with SQL kind of databases. Because ERPs and accounting solutions are highly relational by nature. Because it is a free and open-source project so my choices are not traditional like Oracle etc. that are widely used in the market. During my search, I find PostgreSQL (it is about 2018). It was recommended by a friend working for Amazon, then an Egyptian Database expert (Oracle DBA) and CTO of a telecom company also use some nice words about PostgreSQL. After many other good reviews, I start looking into PostgreSQL. This article is about my findings and experiences while using Postgresql that I love a lot.

PostgreSQL is a powerful, open source object-relational database system that uses and extends the SQL language combined with many features that safely store and scale the most complicated data workloads. The origins of PostgreSQL date back to 1986 as part of the POSTGRES project at the University of California at Berkeley and has more than 30 years of active development on the core platform.

PostgreSQL has earned a strong reputation for its proven architecture, reliability, data integrity, robust feature set, extensibility, and the dedication of the open source community behind the software to consistently deliver performant and innovative solutions. PostgreSQL runs on all major operating systems, has been ACID-compliant since 2001, and has powerful add-ons such as the popular PostGIS geospatial database extender. It is no surprise that PostgreSQL has become the open source relational database of choice for many people and organizations.

Below is an a list of various features found in PostgreSQL, with more being added in every major release. We will explain item by item listed below, in a series of articles which will be made available sequentially here, explaining all of them:

• Data Types;

All needed data types are supported in PostgreSQL like a Primitives (integer, numeric, string, boolean, etc), Structured (date/time, timestamp, interval, array, range, uuid, enum, etc), Documents (JSON, XML, Hstore, etc), Geometry (point, line, circle, polygon, etc) and Customized Types defined by you.

• Data Integrity;

The best needed constraints are supported in PostgreSQL like UNIQUE, NOT NULL, Primary Keys, Foreign Keys and Exclusions. You can use Explicit Locking (full ACID supported), Advisory Locks. Concentrate all of your business rules into the databases.

• Concurrency and Performance;

Some powerful types of basic/advanced indexing methods like B-tree, Multicolumn, Expressions, Partial, GiST, SPGist, KNN Gist, GIN, BRIN, Bloom filters. Take the best of a sophisticated query planner / optimizer. Use index-only scans and multi-column statistics. You can do the best with Transactions, Nested Transactions (via savepoints), Multi-Version concurrency Control (MVCC), Parallelization of read queries and Declarative Table partitioning.

• Reliability, Disaster Recovery;

PostgreSQL have a Write-ahead Looging (WAL) to provide data assurance. He never loses data by yourself. Replicate your data using Master/Slave Native Replication. The replication can be Asynchronous, Synchronous, Logical (providing Publicator / Subscription resources). You can do a Point-in-time recovery (PITR) or use active standbys. Finally, distribute data between storages using Tablespaces.

• Security;

About security, you can use Authentication methods like GSSAPI, SSPI, LDAP, SCRAM-SHA-256, Certificate, and more. PostgreSQL have SSL encryption native support on traffic data, between Client and Databases. Your data is allways protected by man in the middle attacks. You can use a robust access-control system, in tables, objects, columns and at row-level too.

• Extensibility;

Use stored procedures, functions in procedural languages like PL/PGSQL, Perl, Python (and many more). Connect, Read and Write data from other databases or streams with a standard SQL interface using Foreign data wrappers. Use many extensions that provide additional functionality, including PostGIS.

• Internationalization, Text Search;

PostgreSQL have support for international character sets, e.g. through ICU collations. Use native full-text search to find your text data quickly.

• Not relational data (JSON, Hstore, Cstore);

You can use documents (JSON, XML, Hstore and Cstore) native data types to transform PostgreSQL into a NoSQL database. The content of this data types can be indexed, providing a lot of speed and data integrity. Join the best of two worlds (relational and no relational) using an easy SQL syntax to query non relational (but indexed) data with the best speed.

• GIS (Geographic Information System);

In PostgreSQL, you can use an Extension called PostGIS, that is a spatial database extender for PostgreSQL databases. It adds support for geographic objects allowing location queries to be run in SQL. So, you can use PostgreSQL in you app that need for Geographic Information in it’s business rules.

The main problem that Postgres and other RDBMS are solving is concurrency. You want your application to be able to serve multiple users at the same time. Nowadays, that also needs to happen all online, there’s no off-hours for either maintenance, or for reconciliation of the daily transactional activity in the reporting system. In ERP Reporting systems I face this many times.

As an RDBMS it knows how to handle concurrency and very diverse workloads. You can actually use Postgres to implement your transactional system of records and at the same time deliver both customer and activity dashboards, and some kinds of analytics.

The best needed constraints are supported in PostgreSQL like UNIQUE, NOT NULL, Primary Keys, Foreign Keys and Exclusions. You can use Explicit Locking (full ACID supported), Advisory Locks. Concentrate all of your
business rules into the databases.

Constraints are resources that databases use to ensure the consistency of data on tables, preventing invalid data from being inserted/updated into the database. This guarantee that all data into tables are fitted into the rules defined by constraints, independent of the source of data. You can load data from CSV files or another source without beeing afraid to turn your data into a mistake. Constraints could be column level or table level. Column level constraints are applied only to one column whereas table level constraints are applied to the whole table. Defining a data type for a column is a constraint in itself. For example, a column of type DATE constrains the column to valid dates.

The following are commonly used constraints available in PostgreSQL:

• NOT NULL Constraint − Ensures that a column cannot have NULL values on its rows.
• UNIQUE Constraint − Ensures that all values in a column are different, but don’t consider if values are NULL.
• PRIMARY Key − Uniquely identifies each row/record in a database table, not allowing NULL values in key arguments.
• FOREIGN Key − Constraints data based on columns in other tables, ensuring relationship rules into tables. All relational data is always ok!
• CHECK Constraint − The CHECK constraint ensures that all values in a column satisfy certain defined rules.
• EXCLUSION Constraint − The EXCLUDE constraint ensures that if any two rows are compared on the specified column(s) or expression(s) using the specified operator(s), not all of these comparisons will return TRUE.

You can use a transaction when need to run a lot of queries that will change your data, but you need all data changed or nothing changed. Half data change is not a requisite. Without transactions, you can reach a half data changed, turning the reliability of your data to a big trouble!!

Imagine a world where you is always sure that your information is in a correct state into the databases? This can be easily accomplished using transactions in PostgreSQL!!

PostgreSQL Transactions have the following four standard properties, usually referred to by the acronym ACID, using MVCC (multi-version concurrency control) in data to guarantee the best availability of data even when they are being modified.

Always remember!! Using transactions can save a lot of work in your application, avoiding data inconsistency and put the confidence in data in best! Good luck for all!!!