Tag: PostgreSQL

Feed: IBM Big Data & Analytics Hub - All Content; Author: kip-yego; As data proliferates, the need to efficiently undertand, process, and act quickly for competitive advantage continues to increase. Applications must be able to use data swiftly and adapt as needs change by the week or even the day. Developers also require places to store their data that can handle multiple formats and use cases. In order to manage these growing data needs, drive innovation and increase agility via modern application development, enterprises are turning to open-source-based solutions and technologies. The State of Enterprise Open Source report by Red Hat ... Read More

Feed: Planet PostgreSQL. Everybody should to know PostgreSQL's stats views: pg_stat_user_tables, pg_stat_database. There are lot of interesting values. For better interpretation we need a difference of these values in some time. It is impossible to get previous value with clean SQL, but in Postgres we can use PLpgSQL, and we can use custom configure variables as session variables. I wrote function buffer, that returns difference between current value and buffered, and save current value to buffer:CREATE OR REPLACE FUNCTION public.buffer_dif(text, bigint) RETURNS bigint LANGUAGE plpgsql AS $function$ DECLARE r bigint; BEGIN r := $2 - current_setting($1, true)::bigint; PERFORM set_config($1, $2::text, ... Read More

Feed: Planet PostgreSQL. On 2nd of April 2020, Fujii Masao committed patch: Allow pg_stat_statements to track planning statistics.   This commit makes pg_stat_statements support new GUC pg_stat_statements.track_planning. If this option is enabled, pg_stat_statements tracks the planning statistics of the statements, e.g., the number of times the statement was planned, the total time spent planning the statement, etc. This feature is useful to check the statements that it takes a long time to plan. Previously since pg_stat_statements tracked only the execution statistics, we could not use that for the purpose.   The planning and execution statistics are stored at the end ... Read More

Feed: Planet PostgreSQL. Posted by Devrim Gündüz on Wednesday, April 1. 2020 Hi,Given the recent Covid-19 outbreak, PGDG (PostgreSQL Global Devrim Group) recently announced a few updates to the project: - We are skipping PostgreSQL 13. Looks like 13 was not the luckiest number (at least for this year), so we decided to move to PostgreSQL 2020. This was suggested by the community members who still think that Postgres95 was a great name. As a consequence of this, "Postgre" will be now accepted as another nickname. Changing the name is still under discussion, so "Postgres 2020" is also in the ... Read More

Feed: Recent Announcements. Amazon RDS for PostgreSQL now supports major version 12. PostgreSQL 12 includes better management of indexing, improved partitioning capabilities, JSON path queries per SQL/JSON specifications, nondeterministic collations that support case-insensitive and accent-insensitive comparisons for ICU provided collations, most common-value statistics for improved query plans, creation of generated columns that computes values with an expression, and many additional features.  ... Read More

Feed: Planet PostgreSQL. The World has been taken by storm by the COVID-19. Our thoughts are with the people who, for any reason, are suffering from this. We are with you. At Fundación PostgreSQL we have been watching carefully the events, and trying to keep Postgres Ibiza happening, as long as we could ensure a perfectly safe and healthy condition for organizing it. Even though there is a ray of light and we think a period like September may be safe to organize it, unfortunately, organizing a conference requires quality time. The same is needed for speakers, attendees, sponsors, volunteers ... Read More

Feed: Planet PostgreSQL. One very interesting PostgerSQL's feature are polymorphic types. It's interesting mainly for people who writes extensions.Example: I can write own isnull function:CREATE OR REPLACE FUNCTION isnull(anyelement, anyelement) RETURNS anyelement AS $$ SELECT coalesce($1, $2) $$ LANGUAGE sql; postgres=# SELECT public.isnull(NULL, 1); ┌────────┐ │ isnull │ ╞════════╡ │ 1 │ └────────┘ (1 row) postgres=# SELECT public.isnull(NULL, CURRENT_DATE); ┌────────────┐ │ isnull │ ╞════════════╡ │ 2020-03-31 │ └────────────┘ (1 row) -- but postgres=# SELECT public.isnull(1, 1.1); ERROR: function public.isnull(integer, numeric) does not exist LINE 1: SELECT public.isnull(1, 1.1); ^ HINT: No function matches the given name and argument types. You ... Read More

Feed: Planet PostgreSQL. PostgreSQL has a rich set of indexing functionality, and there are many articles explaining the syntax, usage, and value of the index. In this article, I will write basic and useful queries to see the state of database indexes. People develop databases and after some time, when there is a demand to do changes in the architecture of software, they forget to do the previous indexes’ cleanup. This approach creates a mess and sometimes slows down the database because of too many indexes. Whenever we do an update or insert, the index will be updated along with ... Read More

Feed: Planet PostgreSQL. On 18th of March 2020, Alvaro Herrera committed patch: Enable BEFORE row-level triggers for partitioned tables   ... with the limitation that the tuple must remain in the same partition.   Reviewed-by: Ashutosh Bapat Discussion: https://postgr.es/m/20200227165158.GA2071@alvherre.pgsql So, I never made triggers on partitioned tables, so I was kinda sure that they would work anyway. So I tried: =$ CREATE TABLE users ( username text NOT NULL PRIMARY KEY, fullname text ) partition BY hash (username);   =$ CREATE TABLE users_0 partition OF users FOR VALUES WITH (modulus 2, remainder 0);   =$ CREATE TABLE users_1 partition OF ... Read More

Feed: Planet MySQL; Author: MySQL Performance Blog; In my Webinar on Using Percona Monitoring and Management (PMM) for MySQL Troubleshooting, I showed how to use direct queries to ClickHouse for advanced query analysis tasks. In the followup Webinar Q&A, I promised to describe it in more detail and share some queries, so here it goes.PMM uses ClickHouse to store query performance data which gives us great performance and a very high compression ratio. ClickHouse stores data in column-store format so it handles denormalized data very well. As a result, all query performance data is stored in one simple “metrics” table:Column ... Read More