Tue, 12 Mar 2024 14:05:08 +0000 en-US hourly 1 https://wordpress.org/?v=6.5.2 https://www.percona.com/blog/wp-content/uploads/2023/02/cropped-percona-favicon-32x32.png 32 32 76301791 https://www.percona.com/blog/managing-time-series-data-using-timescaledb-powered-postgresql/ https://www.percona.com/blog/managing-time-series-data-using-timescaledb-powered-postgresql/#respond Tue, 12 Mar 2024 14:05:08 +0000 https://www.percona.com/blog/?p=94967 PostgreSQL extensions are great! Simply by adding an extension, one transforms what is an otherwise vanilla general-purpose database management system into one capable of processing data requirements in a highly optimized fashion. Some extensions, like pg_repack, simplify and enhance existing features already, while other extensions, such as PostGIS and pgvector, add completely new capabilities.I’d like […]]]> https://www.percona.com/blog/managing-time-series-data-using-timescaledb-powered-postgresql/feed/ 0 94967 https://www.percona.com/blog/grafana-dashboards-implementing-the-postgresql-extension-pg_stat_monitor/ https://www.percona.com/blog/grafana-dashboards-implementing-the-postgresql-extension-pg_stat_monitor/#comments Tue, 26 Dec 2023 13:23:22 +0000 https://www.percona.com/blog/?p=92495 This PoC demonstrates how to install and configure pg_stat_monitor in order to extract useful and actionable metrics from a PostgreSQL database and display them on a Grafana dashboard.About the environment Grafana: version 10.0.0 Grafana database backend: Prometheus version 2.15.2+d PostgreSQL version 13 pgbench version 13 In order to investigate the potential opportunities for implementing constructive […]]]> https://www.percona.com/blog/grafana-dashboards-implementing-the-postgresql-extension-pg_stat_monitor/feed/ 3 92495 https://www.percona.com/blog/migrating-from-mysql-to-postgresql-using-pgloader/ https://www.percona.com/blog/migrating-from-mysql-to-postgresql-using-pgloader/#respond Mon, 18 Dec 2023 17:05:29 +0000 https://www.percona.com/blog/?p=92286 These days, there’s been a lot of talk in the industry about setting up one’s database system on PostgreSQL. Most times, these are Greenfield projects where the bulk of the effort is on its design and architecture. But sometimes, more often than you’d think, decisions are being made to move from an existing platform to […]]]> https://www.percona.com/blog/migrating-from-mysql-to-postgresql-using-pgloader/feed/ 0 92286 https://www.percona.com/blog/how-to-scale-a-single-host-postgresql-database-with-citus/ https://www.percona.com/blog/how-to-scale-a-single-host-postgresql-database-with-citus/#respond Fri, 03 Nov 2023 13:26:04 +0000 https://www.percona.com/blog/?p=91521 When it comes to Citus, successfully building out and scaling a PostgreSQL cluster across multiple nodes and even across data centers can feel, at times, to be an art form because there are so many ways of building it out.There’s an axiom that I think aptly applies to this situation describing the differences between science […]]]> https://www.percona.com/blog/how-to-scale-a-single-host-postgresql-database-with-citus/feed/ 0 91521 https://www.percona.com/blog/data-redundancy-with-the-postgresql-citus-extension/ https://www.percona.com/blog/data-redundancy-with-the-postgresql-citus-extension/#comments Tue, 29 Aug 2023 13:52:51 +0000 https://www.percona.com/blog/?p=90224 Over the years, I’ve had the opportunity to architect all sorts of configurations using Postgres as a backend. I’ve always found it very cool and satisfying to implement sophisticated business rules, often in more ways than one has fingers and toes. So, it’s not an understatement when I say that Citus is one of the […]]]> https://www.percona.com/blog/data-redundancy-with-the-postgresql-citus-extension/feed/ 1 90224 https://www.percona.com/blog/configuring-postgresql-and-ldap-using-starttls/ https://www.percona.com/blog/configuring-postgresql-and-ldap-using-starttls/#respond Mon, 21 Aug 2023 13:51:17 +0000 https://www.percona.com/blog/?p=90222 Effectively working with LDAP as an authentication mechanism for PostgreSQL typically requires extensive knowledge in both domains. While trying to be as complete yet succinct as possible, I’m detailing how to enable TLS between a PostgreSQL and the OpenLDAP server.Ironically, the most complicated aspect has nothing to do with either PostgreSQL or OpenLDAP but with […]]]> https://www.percona.com/blog/configuring-postgresql-and-ldap-using-starttls/feed/ 0 90222 https://www.percona.com/blog/working-with-postgresql-and-postgis-how-to-become-a-gis-expert/ https://www.percona.com/blog/working-with-postgresql-and-postgis-how-to-become-a-gis-expert/#respond Thu, 15 Jun 2023 19:29:07 +0000 https://www.percona.com/blog/?p=66922 This post was originally published in 2020 and has been updated in 2023. Mastering Geographical Information Systems, better known simply as GIS, can be considered in some ways as a rite of passage. The complexities and challenges involved in learning, which are ostensibly non-IT concepts, are steep. However, as they say, “There’s more than one […]]]> https://www.percona.com/blog/working-with-postgresql-and-postgis-how-to-become-a-gis-expert/feed/ 0 66922 https://www.percona.com/blog/configuring-pgbouncer-for-multi-port-access/ https://www.percona.com/blog/configuring-pgbouncer-for-multi-port-access/#respond Mon, 05 Jun 2023 13:20:28 +0000 https://www.percona.com/blog/?p=88268 From time to time, situations occur where unusual circumstances dictate out-of-the-box thinking.For example, suppose you have a system where you’ve installed multiple data clusters onto a single host. What you end up with is a host breaking up and sharing valuable system resources, i.e., CPU, RAM, disk, etc., between multiple instances of PostgreSQL data clusters.  […]]]> https://www.percona.com/blog/configuring-pgbouncer-for-multi-port-access/feed/ 0 88268 https://www.percona.com/blog/argument-for-logical-failover-slots/ https://www.percona.com/blog/argument-for-logical-failover-slots/#comments Thu, 02 Feb 2023 13:02:31 +0000 https://www.percona.com/blog/?p=84868 These days, the typical PostgreSQL cluster consists not only of a multi-node replication cluster, which is ordinarily an asynchronous streaming replication model but can sometimes include a logical replication component.  Recall that logical replication works by using the PUB/SUB mode, where individual tables are published and are then subscribed by remotely connected databases/tables. DML operations, such […]]]> https://www.percona.com/blog/argument-for-logical-failover-slots/feed/ 2 84868 https://www.percona.com/blog/diffing-postgresql-schema-changes/ https://www.percona.com/blog/diffing-postgresql-schema-changes/#comments Fri, 23 Dec 2022 14:28:31 +0000 https://www.percona.com/blog/?p=84244 One of the routine operations when administering PostgreSQL is periodic updates to the database system’s architecture. PostgreSQL does a good job of allowing one to update a schema, add types, functions, triggers, or alter a table by adding and removing columns and updating column data types, etc., in a reliable manner. However, there is no […]]]> https://www.percona.com/blog/diffing-postgresql-schema-changes/feed/ 1 84244 https://www.percona.com/blog/powering-postgresql-15-with-columnar-tables/ https://www.percona.com/blog/powering-postgresql-15-with-columnar-tables/#comments Wed, 14 Dec 2022 13:09:32 +0000 https://www.percona.com/blog/?p=84113 PrologueThis blog is the result of my investigation into column-wise tables. Hopefully, you will find it useful in your deliberations.When I started writing it was meant to be a “Good News” blurb. I was pretty optimistic that I’d truly have impressive numbers to share but as it turns out, while there is a potential for […]]]> https://www.percona.com/blog/powering-postgresql-15-with-columnar-tables/feed/ 2 84113 https://www.percona.com/blog/working-with-snapshots-in-postgresql/ https://www.percona.com/blog/working-with-snapshots-in-postgresql/#respond Tue, 13 Sep 2022 11:49:32 +0000 https://www.percona.com/blog/?p=82624 One of the reasons I’ve never strayed far from PostgreSQL is that I am always discovering features that overcome all sorts of interesting problems. Although, to be honest, from time to time I’ve had the feeling that sometimes some features are a solution in search of a problem. Take, for example, exporting transaction snapshots. Originally implemented […]]]> https://www.percona.com/blog/working-with-snapshots-in-postgresql/feed/ 0 82624 https://www.percona.com/blog/working-with-postgresql-dump-manifests/ https://www.percona.com/blog/working-with-postgresql-dump-manifests/#comments Fri, 02 Sep 2022 14:12:04 +0000 https://www.percona.com/blog/?p=82384 After working with PostgreSQL for so many years it’s sometimes surprising to see some of the utilities, to a certain degree, have capabilities that remain relatively unknown to more than a few people. So let me introduce you to manifests.Both pg_dump and pg_restore have switches enabling us to zone in on specific objects of interest […]]]> https://www.percona.com/blog/working-with-postgresql-dump-manifests/feed/ 2 82384 https://www.percona.com/blog/introducing-postgresql-15-working-with-distinct/ https://www.percona.com/blog/introducing-postgresql-15-working-with-distinct/#comments Wed, 13 Jul 2022 12:20:29 +0000 https://www.percona.com/blog/?p=81726 Well, it’s that time of the year when once again we have a look at the newest version of PostgreSQL.As tradition dictates, here at Percona, the team is given a list of features to write about. Mine happened to be about a very basic and, I might add, important function i.e. SELECT DISTINCT.Before getting into […]]]> https://www.percona.com/blog/introducing-postgresql-15-working-with-distinct/feed/ 6 81726 https://www.percona.com/blog/working-with-large-postgresql-databases/ https://www.percona.com/blog/working-with-large-postgresql-databases/#respond Wed, 27 Apr 2022 11:27:28 +0000 https://www.percona.com/blog/?p=80846 It’s a funny thing when the topic of database sizes comes up. Calling one small, medium, large, or even huge isn’t as straightforward as you’d think. Distinguishing the size of a database is based upon a number of factors whose characteristics can be classified as either “tangible”, things that you can measure in an objective […]]]> https://www.percona.com/blog/working-with-large-postgresql-databases/feed/ 0 80846 https://www.percona.com/blog/overcoming-vacuum-wraparound/ https://www.percona.com/blog/overcoming-vacuum-wraparound/#comments Wed, 13 Apr 2022 11:38:15 +0000 https://www.percona.com/blog/?p=80575 Transaction ID Wraparound occurs when the VACUUM process cannot keep up with database activity and the PostgreSQL service is forced to shut down.In more technical parlance: Transaction ID Wraparound occurs when the semantics of Multi-Version Concurrency Control (MVCC) fail and when the number of unique transaction ids reaches its maximum which numbers about two billion.What […]]]> https://www.percona.com/blog/overcoming-vacuum-wraparound/feed/ 3 80575 https://www.percona.com/blog/how-postgresql-pipeline-mode-works/ https://www.percona.com/blog/how-postgresql-pipeline-mode-works/#comments Mon, 31 Jan 2022 15:03:51 +0000 https://www.percona.com/blog/?p=79953 Note: In the original version of this post, there were some issues missed. These have been addressed at the bottom of the post. I’d like to introduce to you a very cool feature introduced in PostgreSQL, the Pipeline Mode.So just what exactly is Pipeline Mode? Pipeline Mode allows applications to send a query without having to […]]]> https://www.percona.com/blog/how-postgresql-pipeline-mode-works/feed/ 10 79953 https://www.percona.com/blog/upgrading-postgis-a-proof-of-concept/ https://www.percona.com/blog/upgrading-postgis-a-proof-of-concept/#respond Tue, 23 Nov 2021 16:31:43 +0000 https://www.percona.com/blog/?p=79075 My last blog introduced the issues one can face when upgrading PostGIS and PostgreSQL at the same time. The purpose of this blog is to walk through the steps with an example.For our purposes, we will confine ourselves to working with the community versions of 9.6 and 11 respectively, and use LXD in order to […]]]> https://www.percona.com/blog/upgrading-postgis-a-proof-of-concept/feed/ 0 79075 https://www.percona.com/blog/issues-upgrading-postgis-and-postgresql/ https://www.percona.com/blog/issues-upgrading-postgis-and-postgresql/#respond Wed, 10 Nov 2021 12:28:46 +0000 https://www.percona.com/blog/?p=78882 PostGIS is arguably the best GIS implementation in the world. There may be other database technologies with GIS but the value proposition of quality, performance, and sophistication, and, of course, cost, is nowhere near what PostgreSQL offers.So let me set the stage for this blog of mine: you’ve been using PostGIS on your PostgreSQL server […]]]> https://www.percona.com/blog/issues-upgrading-postgis-and-postgresql/feed/ 0 78882 https://www.percona.com/blog/performing-etl-using-inheritance-in-postgresql/ https://www.percona.com/blog/performing-etl-using-inheritance-in-postgresql/#respond Mon, 19 Jul 2021 14:27:37 +0000 https://www.percona.com/blog/?p=77328 Good database maintenance includes not only performing the standard adding, updating, and deleting records, etc., but periodic edits to the table schema too. Operations such as adding, editing, and removing table columns are part of today’s life-cycle reality too as new functionality is constantly being added.In quieter, and less demanding times, one could get away […]]]> https://www.percona.com/blog/performing-etl-using-inheritance-in-postgresql/feed/ 0 77328