100% Open Source – 100% Cost Control
The PostgreSQL® Competence Center of the credativ Group announces the creation of a new comprehensive service and support package that includes all services necessary for the operation of PostgreSQL® in enterprise environments. This new offering will be available starting August 1st, 2020.
“Motivated by the requirements of many of our customers, we put together a new and comprehensive PostgreSQL® service package that meets all the requirements for the operation of PostgreSQL® in enterprise environments.”, says Dr. Michael Meskes, managing director of the credativ Group.
“In particular, this package focuses on true open source support, placing great emphasis on the absence of any proprietary elements in our offer. Despite this, our service package still grants all of the necessary protection for operation in business-critical areas. Additionally, with this new offering, the number of databases operated within the company’s environment does not matter. As a result, credativ offers 100% cost control while allowing the entire database environment to be scaled as required.”
Database operation in enterprise environments places very high demands on the required service and support. Undoubtedly an extremely powerful, highly scalable, and rock-solid relational database is the basis for secure and high-performance operation.
However, a complete enterprise operating environment consists of much more than just the pure database; one needs holistic lifecycle management. Major and Minor version upgrades, migrations, security, services, patch management, and Long-Term Support (LTS) are just a few essential factors. Additionally, staying up to date also requires continuous regular training and practice.
Services for the entire operating environment
Beyond the database itself, one also needs a stable and highly scalable operating environment providing all necessary Open Source tools for PostgreSQL and meeting all requirements regarding high availability, security, performance, database monitoring, backups, and central orchestration of the entire database infrastructure. These tools include the open-source versions of numerous PostgreSQL related project packages, such as pgAdmin, pgBadger, pgBackrest, Patroni, but also the respective operating system environment and popular projects like Prometheus and Grafana, or even cloud infrastructures based on Kubernetes.
Just as indispensable as the accurate functioning of the database is smooth interaction with any components connected with the database. Therefore it is important to include and consider these components as well. Only when all aspects, such as operating system, load balancer, web server, application server, or PostgreSQL cluster solutions, work together, will the database achieve optimal performance.
This new support package is backed up by continuous 24×7 enterprise support, with guaranteed Service Level Agreements and all necessary services for the entire database environment, including a comprehensive set of open-source tools commonly used in today’s enterprise PostgreSQL environments. All of these requirements are covered by the PostgreSQL Enterprise package from credativ and are included within the scope of services. The new enterprise service proposal is offered at an annual flat rate, additionally simplifying costs and procurement.
About credativ
The credativ Group is an independent consulting and services company with primary locations in Germany, the United States, and India.
Since 1999, credativ has focused entirely on the planning and implementation of professional business solutions using Open Source software. Since May 2006, credativ operates the Open Source Support Center (OSSC), offering professional 24×7 enterprise support for numerous Open Source projects.
In addition, our PostgreSQL Competence Center of credativ provides a dedicated database team a comprehensive service for the PostgreSQL object-relational database eco-system.
This article was originally written by Philip Haas.
In November 1999, 20 years ago, credativ GmbH was founded in Germany, and thus laid the first foundation for the current credativ group.
At that time, Dr. Michael Meskes and Jörg Folz started the business operations in the Technology Centre of Jülich, Germany. Our mission has always been to not only work to live, but also to live to work, because we love the work we do. Our aim is to support widespread use of open source software and to ensure independence from software vendors.
Furthermore, it is very important for us to support and remain active in open source communities. Since 1999 we have continuously taken part in PostgreSQL and Debian events, and supported them financially with sponsorships. Additionally, the development of the Linux operating system has also been a dear and important project of ours. Therefore, we have been a member of the Linux Foundation for over 10 years.
In 2006 we opened our Open Source Support Center. Here, for the first time, our customers had the opportunity to get the support for their entire Open Source infrastructure with just one contract. Since then we have expanded and included different locations into a globally operating Open Source Support Center.
Thanks to our healthy and steady growth, credativ grew to over 35 employees at its worldwide locations by our 10th anniversary.
Since then, the founding of credativ international GmbH in 2013 marked another milestone in credativ’s history, as the focus shifted from a local to a global market. We were also able to expand into different countries such as the USA and India.
We have grown now to over 80 employees, with 20 years of company history. credativ is now one of the leading providers of services and support for open source software in enterprise use. We thank our customers, business partners, and employees for their time together.
This Artikel was originally written by Philip Haas.
The PostgreSQL® Global Development Group (PGDG) has released version 12 of the popular free PostgreSQL® database. As our article for Beta 4 has already indicated, a number of new features, improvements and optimizations have been incorporated into the release. These include among others:
Optimized disk space utilization and speed for btree indexes
btree-Indexes, the default index type in PostgreSQL®, has experienced some optimizations in PostgreSQL® 12.
btree indexes used to store duplicates (multiple entries with the same key values) in an unsorted order. This has resulted in suboptimal use of physical representation in these indexes. An optimization now stores these multiple key values in the same order as they are physically stored in the table. This improves disk space utilization and the effort required to manage corresponding btree type indexes. In addition, indexes with multiple indexed columns use an improved physical representation so that their storage utilization is also improved. To take advantage of this in PostgreSQL® 12, however, if they were upgraded to the new version using pg_upgrade via a binary upgrade, these indexes must be recreated or re-indexed.
Insert operations in btree indexes are also accelerated by improved locking.
Improvements for pg_checksums
credativ has contributed an extension for pg_checksums that allows to enable or disable block checksums in stopped PostgreSQL® instances. Previously, this could only be done by recreating the physical data representation of the cluster using initdb.
pg_checksums now has the option to display a status history on the console with the --progress parameter. The corresponding code contributions come from the colleagues Michael Banck and Bernd Helmle.
Optimizer Inlining of Common Table Expressions
Up to and including PostgreSQL® 11, the PostgreSQL® Optimizer was unable to optimize common table expressions (also called CTE or WITH queries). If such an expression was used in a query, the CTE was always evaluated and materialized first before the rest of the query was processed. This resulted in expensive execution plans for more complex CTE expressions. The following generic example illustrates this. A join is given with a CTE expression that filters all even numbers from a numeric column:
WITH t_cte AS (SELECT id FROM foo WHERE id % 2 = 0) SELECT COUNT(*) FROM t_cte JOIN bar USING(id);
In PostgreSQL® 11, using a CTE always leads to a CTE scan that materializes the CTE expression first:
EXPLAIN (ANALYZE, BUFFERS) WITH t_cte AS (SELECT id FROM foo WHERE id % 2 = 0) SELECT COUNT(*) FROM t_cte JOIN bar USING(id) ;
QUERY PLAN
─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
Aggregate (cost=2231.12..2231.14 rows=1 width=8) (actual time=48.684..48.684 rows=1 loops=1)
Buffers: shared hit=488
CTE t_cte
-> Seq Scan on foo (cost=0.00..1943.00 rows=500 width=4) (actual time=0.055..17.146 rows=50000 loops=1)
Filter: ((id % 2) = 0)
Rows Removed by Filter: 50000
Buffers: shared hit=443
-> Hash Join (cost=270.00..286.88 rows=500 width=0) (actual time=7.297..47.966 rows=5000 loops=1)
Hash Cond: (t_cte.id = bar.id)
Buffers: shared hit=488
-> CTE Scan on t_cte (cost=0.00..10.00 rows=500 width=4) (actual time=0.063..31.158 rows=50000 loops=1)
Buffers: shared hit=443
-> Hash (cost=145.00..145.00 rows=10000 width=4) (actual time=7.191..7.192 rows=10000 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 480kB
Buffers: shared hit=45
-> Seq Scan on bar (cost=0.00..145.00 rows=10000 width=4) (actual time=0.029..3.031 rows=10000 loops=1)
Buffers: shared hit=45
Planning Time: 0.832 ms
Execution Time: 50.562 ms
(19 rows)
This plan first materializes the CTE with a sequential scan with a corresponding filter (id % 2 = 0). Here no functional index is used, therefore this scan is correspondingly more expensive. Then the result of the CTE is linked to the table bar by Hash Join with the corresponding Join condition. With PostgreSQL® 12, the optimizer now has the ability to inline these CTE expressions without prior materialization. The underlying optimized plan in PostgreSQL® 12 will look like this:
EXPLAIN (ANALYZE, BUFFERS) WITH t_cte AS (SELECT id FROM foo WHERE id % 2 = 0) SELECT COUNT(*) FROM t_cte JOIN bar USING(id) ;
QUERY PLAN
───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
Aggregate (cost=706.43..706.44 rows=1 width=8) (actual time=9.203..9.203 rows=1 loops=1)
Buffers: shared hit=148
-> Merge Join (cost=0.71..706.30 rows=50 width=0) (actual time=0.099..8.771 rows=5000 loops=1)
Merge Cond: (foo.id = bar.id)
Buffers: shared hit=148
-> Index Only Scan using foo_id_idx on foo (cost=0.29..3550.29 rows=500 width=4) (actual time=0.053..3.490 rows=5001 loops=1)
Filter: ((id % 2) = 0)
Rows Removed by Filter: 5001
Heap Fetches: 10002
Buffers: shared hit=74
-> Index Only Scan using bar_id_idx on bar (cost=0.29..318.29 rows=10000 width=4) (actual time=0.038..3.186 rows=10000 loops=1)
Heap Fetches: 10000
Buffers: shared hit=74
Planning Time: 0.646 ms
Execution Time: 9.268 ms
(15 rows)
The advantage of this method is that there is no initial materialization of the CTE expression. Instead, the query is executed directly with a Join. This works for all non-recursive CTE expressions without side effects (for example, CTEs with write statements) and those that are referenced only once per query. The old behavior of the optimizer can be forced with the WITH ... AS MATERIALIZED ... directive.
Generated Columns
Generated Columns in PostgreSQL® 12 are materialized columns, which calculate a result based on expressions using existing column values. These are stored with the corresponding result values in the tuple. The advantage is that there is no need to create triggers for subsequent calculation of column values. The following simple example illustrates the new functionality using a price table with net and gross prices:
CREATE TABLE preise(netto numeric,
brutto numeric GENERATED ALWAYS AS (netto * 1.19) STORED);
INSERT INTO preise VALUES(17.30);
INSERT INTO preise VALUES(225);
INSERT INTO preise VALUES(247);
INSERT INTO preise VALUES(19.15);
SELECT * FROM preise;
netto │ brutto
───────┼─────────
17.30 │ 20.5870
225 │ 267.75
247 │ 293.93
19.15 │ 22.7885
(4 rows)
The column brutto is calculated directly from the net price. The keyword STORED is mandatory. Of course, indexes can also be created on Generated Columns, but they cannot be part of a primary key. Furthermore, the SQL expression must be unique, i.e. it must return the same result even if the input quantity is the same. Columns declared as Generated Columns cannot be used explicitly in INSERT or UPDATE operations. If a column list is absolutely necessary, the corresponding value can be indirectly referenced with the keyword DEFAULT.
Omission of explicit OID columns
Explicit OID columns have historically been a way to create unique column values so that a table row can be uniquely identified database-wide. However, for a long time PostgreSQL® has only created these explicitly and considered their basic functionality obsolete. With PostgreSQL® the possibility to create such columns explicitly is now finally abolished. This means that it will no longer be possible to specify the WITH OIDS directive for tables. System tables that have always referenced OID objects uniquely will now return OID values without explicitly specifying OID columns in the result set. Especially older software, which handled catalog queries carelessly, could get problems with a double column output.
Moving recovery.conf to postgresql.conf
Up to and including PostgreSQL® 11, database recovery and streaming replication instances were configured via a separate configuration file recovery.conf.
With PostgreSQL® 12, all configuration work done there now migrates to postgresql.conf. The recovery.conf file is no longer required. PostgreSQL® 12 refuses to start as soon as this file exists. Whether recovery or streaming standby is desired is now decided either by a recovery.signal file (for recovery) or by a standby.signal file (for standby systems). The latter has priority if both files are present. The old parameter standby_mode, which controlled this behavior since then, has been removed.
For automatic deployments of high-availability systems, this means a major change. However, it is now also possible to perform corresponding configuration work almost completely using the ALTER SYSTEM command.
REINDEX CONCURRENTLY
With PostgreSQL® 12 there is now a way to re-create indexes with as few locks as possible. This greatly simplifies one of the most common maintenance tasks in very write-intensive databases. Previously, a combination of CREATE INDEX CONCURRENTLY and DROP INDEX CONCURRENTLY had to be used. In doing so, it was also necessary to ensure that index names were reassigned accordingly, if required.
The release notes give an even more detailed overview of all new features and above all incompatibilities with previous PostgreSQL® versions.
Yesterday, the fourth beta of the upcoming PostgreSQL®-major version 12 was released.
Compared to its predecessor PostgreSQL® 11, there are many new features:
- Performance improvements for indexes: btree indexes now manage space more efficiently. The REINDEX command now also supports CONCURRENTLY, which was previously only possible with new indexes.
- WITH queries are now embedded in the main query and thus optimized much better by the planner. Previously, WITH queries were always executed independently.
- The native partitioning was further improved. Foreign keys can now also reference partitioned tables. Maintenance commands such as ATTACH PARTITION no longer require an exclusive table lock.
- The support of page checksums and the tool pg_checksums was further improved, also with substantial cooperation by credativ.
- It is now possible to integrate additional storage engines. The “zheap”, which is still under development, will be based on this, which promises more compact data storage with less bloat.
Of course, PostgreSQL® 12 will be tested using sqlsmith, the SQL “fuzzer” from our colleague Andreas Seltenreich. Numerous bugs in different PostgreSQL® versions were found with sqlsmith by using randomly generated SQL queries.
Debian and Ubuntu packages for PostgreSQL® 12 are going to be published on apt.postgresql.org with credativ’s help. This work will be handled by our colleague Christoph Berg.
The release of PostgreSQL® 12 is expected in the next weeks.
Expansion of Open Source Support Center & PostgreSQL® Competence Center in USA
credativ Group, Maryland, 01/29/2019
credativ group, a leading provider of Open Source solutions and support in both Europe and Asia, announces a strategic expansion into the American market as part of a deal acquiring significant assets of OmniTI Computer Consulting (OmniTI), a highly aligned Maryland technical services firm. The new combined entity forms the basis for the establishment of an enlarged Open Source Support Center and PostgreSQL® Competence Center in a new US headquarters based in Columbia, Maryland.
OmniTI, founded in 1997, has built a client list that reads like a who’s who in tech, including Wikipedia, Google, Microsoft, Gilt, Etsy, and many others. In the process, they developed or contributed to the development of hundreds of Open Source projects, built the OmniOS illumos distribution, and ran the world-renowned Surge conference series. “credativ’s client-first approach and alignment on Open Source makes it a comfortable fit and seamless transition for OmniTI’s staff and customers. After 22 years of business, I’m delighted by this new direction.” says Theo Schlossnagle, Founder of OmniTI, who is leaving the company to concentrate on other activities.
The newly formed US branch of the credativ family has appointed Robert Treat as its CEO. Working in close cooperation with credativ international GmbH, led by Dr. Michael Meskes, Treat will take over further expansion of activities in the USA. A noted Open Source contributor, author, and international speaker, Treat served as both COO and CEO during his time with OmniTI.
Together with the European Open Source Support Center of credativ GmbH, the credativ group will expand its service network for numerous international customers who are currently mainly supported from Europe. Thus the credativ group can extend its unique position as the sole provider of Open Source Support Centers and offer comprehensive support with guaranteed service level agreements for a multitude of open source projects used in today’s business environments.
Robert Treat says “Open Source is at the heart of today’s biggest business disruptors; DevOps and the Cloud. At OmniTI we helped hundreds of companies navigate through these changes over the last 10 years. Now, as part of credativ, we have an even larger pool of experts to choose from to help people master all the necessary aspects of modern technology, including scalability, observability, deployment, automation, and more; all based on the power and flexibility of Open Source.”
Additionally, the US team will now offer a PostgreSQL® Competence Center that ensures the use of the free open source DBMS PostgreSQL® in mission critical applications and supports the entire life cycle of a PostgreSQL® database environment.
In addition, by expanding its existing service and support structure, credativ is one of a very few providers of PostgreSQL® support with a truly global footprint. Dr. Michael Meskes says: “We see to it that the community version of PostgreSQL® can be used as an extremely powerful alternative to the well-known commercial, proprietary databases in the enterprise environment. Apart from the very moderate costs for support, there is no need anymore for further costs for subscriptions or licenses.”
About credativ international GmbH
Founded in 1999, credativ is an independent consulting and services company offering comprehensive services and technical support for the implementation and operation of Open Source software in business applications.
Our Open Source Support Center™ provides the necessary reliability to make use of the numerous advantages of free software for your organization. Offering support around the clock, 365 days a year, our Open Source Support Center™ contains service locations in Germany, India, the Netherlands, Spain, and the United States, providing global premium support for a wide range of Open Source projects that play a fundamental role and are of utmost importance in the IT infrastructures of many companies today.
Moreover, we are advocates for the principles of free software and actively support the development of Open Source software. Most of our consultants are actively involved in numerous Open Source projects, including Debian, PostgreSQL®, Icinga, and many others, and many have been recognized as leading experts in their respective domains.
Debian 9 “Stretch”, the latest version of Debian, is about to be released and after a full-freeze on February 5th everyone is trying its best to fix the last bugs that are left.
Upon entering the final phase of development in February the test version was “frozen” so that no more packages could be added or removed without the approval of the release team.
However, Stretch has some bugs left, which need to be resolved until the release date, especially the so called release critical bugs (RC). For this purpose, numerous Debian developers host worldwide meet ups.
These meet ups are a long standing tradition and are lovingly called “Bug Squashing Party”. Despite the cute name, these events usually turn out to be one the most focused, intense and hard working days in the life cycle of a new Debian version. Pressured by the upcoming release date, everyone gets together to get rid of the nasty release critical bugs and focus on unfinished packages.
This weekend, from the 17th to the 19th of March, the Debian developers from credativ are hosting a Bug Squashing Party in the German Open Source Support Center in Mönchengladbach.
The Open Source Support Center employs the likely biggest number of European Debian developers in one place. Therefore credativ GmbH is providing the location and technical infrastructure for everyone who decided to join the Bug Squashing Party.
We hope that this year’s meeting is going to be as successful as in the previous years. Developers from all neighbouring countries took part in past events and some even found their future employer.
Coordinating the event are: Martin Zobel-Helas “zobel” (Debian system administrator) and Alexander Wirt “formorer” (Debian Quality Assurance).
If you would like to participate, feel free to sign up!
We are looking forward to your visit.
Here is the announcement on the mailing-list:
https://lists.debian.org/debian-devel-announce/2017/02/msg00006.html
Here is the entry in the Debian wiki:
https://wiki.debian.org/BSP/2017/03/de/credativ
This article was originally written by Philip Haas.