postgresql

Luca Ferrari: PostgreSQL 11 Server Side Programming - Now Available!

planet postgresql - 14時間 54秒

A quick start guide on implementing and deploying code to PostgreSQL.

PostgreSQL 11 Server Side Programming - Now Available!

Near the end of November, Packt published the book PostgreSQL 11 Server Side Programming Quick Start Guide, authored by me.



*This post has the only aim of describing the book contents and the reason behind choices related to it.**

Following a consolidated tradition, Packt is producing more and more books on PostgreSQL and related technologies, and this is the first one that covers aspects about the freshly released PostgreSQL 11 version.

Nevertheless, this does not mean that the book is only for PostgreSQL 11 users and administrators: it covers topics, concepts and provide examples that can be use as-is or ported to older versions of PostgreSQL, as well as probably to newer ones. In fact, while the book code examples have been tested against a PostgreSQL 11 cluster, only the examples related to the new object PROCEDURE, introduced by PostgreSQL 11, are strongly tied to such a version.

This book is a Quick Start Guide, and therefore it has a very practical approach to a limited scope, and only that. Therefore the book assumes you are able to install, manage and run a PostgreSQL 11 cluster, that you know how to connect and how to handle basic SQL statements. A basic knowledge in general programming is also required.

The book consists of 10 chapters, each focusing on a particular aspect of developing and deploying code within a PostgreSQL cluster. The main programming language used in the book is PL/pgSQL, the default procedural language for PostgreSQL; however several examples are...

カテゴリー: postgresql

Achilleas Mantzios: Using PostgreSQL Logical Replication to Maintain an Always Up-to-Date Read/Write TEST Server

planet postgresql - 20時間 7分

In this blog entry we’ll talk about logical replication in PostgreSQL: its use cases, general information on the status of this technology, and a special use case in particular on how to setup a subscriber (replica) node of the primary server in order to function as the database server for the testing environment, and the challenges met.

Intro

Logical replication, officially introduced in PostgreSQL 10, is the latest replication technology offered by the PostgreSQL community. Logical replication is a continuation of the legacy of physical replication with which it shares a lot of ideas and code. Logical replication works like physical replication using the WAL to record logical changes independent from the version or specific architecture. In order to be able to provide logical replication to the core offering the PostgreSQL community has gone a long way.

Types of replication and History of PostgreSQL replication

The types of replication in databases can be classified as follows:

  • Physical (AKA binary) replication
    • Operating system level (vSphere replication)
    • File system level (DRBD)
    • Database level (WAL based)
  • Logical replication (Database level)
    • Trigger based (DBMirror, Slony)
    • Middleware (pgpool)
    • WAL based (pglogical, Logical Replication)

The roadmap that brings to today’s WAL based logical replication was:

  • 2001: DBMirror (trigger based)
  • 2004: Slony1 (trigger based), pgpool (middleware)
  • 2005: PITR (WAL based) introduced in PostgreSQL 8.0
  • 2006: Warm standby in PostgreSQL 8.2
  • 2010: Physical streaming replication, hot standby in PostgreSQL 9.0
  • 2011: Synchronous streaming replication in PostgreSQL 9.1
  • 2012: Cascading streaming replication in PostgreSQL 9.2
  • 2013: Background workers in PostgreSQL 9.3
  • 2014: Logical decoding API, replication slots. (The foundations for Logical Replication) in PostgreSQL 9.4
  • 2015: 2ndQuadrant introduces pglogical, the ancestor or Logical Replication
  • 2017: Logical Replication in core PostgreSQL 10!

As we can see lots of technologies collaborated to make Logical Replication a r

[...]
カテゴリー: postgresql

Joshua Drake: PostgresConf 2019: Early bird tickets now available and CFP still open

planet postgresql - 20時間 40分
We are pleased to announce that Early Bird tickets to Postgres Conference 2019 are now available. Whether you are seeking a world class big data and analytics learning opportunity from the Greenplum Summit, a deep dive from the Google Cloud Summit, Regulated Industry Information from the Regulated Industry track, or a dizzying amount of learning opportunities from over 100 breakout sessions, PostgresConf 2019 is the show not to miss! Oh, and did we mention there will be training options as well?

Register here: https://postgresconf.org/conferences/2019

Call For Papers is still open! Submit your presentation at the above link.
 

PostgresConf would be impossible without the generous support of our sponsors including: EnterpriseDB, Pivotal, Google, Microsoft and 2ndQuadrant.
Thanks for all of the support and we look forward to seeing you in March!

The Postgres Conference Organizer Team

カテゴリー: postgresql

Laurenz Albe: Be prepared for prepared transactions

planet postgresql - 2018-12-11(火) 18:00:45

Prepared transactions are disabled in PostgreSQL by default, since the parameter max_prepared_transactions has the default value 0.

You don’t need prepared transactions in most cases. However, they can cause nasty problems, so I think that everybody who runs a PostgreSQL database should understand them.

To illustrate these problems, I’ll show you how to use prepared transactions to get a PostgreSQL into an inaccessible state.

 

What are prepared transactions?

Normally, a database transaction that spans multiple statements is ended with COMMIT or ROLLBACK. With prepared transactions, another step is added:

  1. BEGIN or START TRANSACTION: starts a transaction as usual.
  2. PREPARE TRANSACTION 'name': prepares the transaction for commit or rollback and assigns a name to it.
  3. { COMMIT | ROLLBACK } PREPARED 'name': commits or rolls back a previously prepared transaction.

 

The PREPARE TRANSACTION step performs all actions that may fail during COMMIT. That way, both COMMIT PREPARED and ROLLBACK PREPARED are guaranteed to succeed once a transaction is prepared. Moreover, PREPARE TRANSACTION persists the still open transaction, so that it will survive a crash or server restart.

Once a transaction is prepared, it is complete. Subsequent SQL statements belong to different transactions. You cannot do anything with a prepared transaction except COMMIT PREPARED and ROLLBACK PREPARED.

 

What is the use of prepared transactions?

Prepared transactions are used to implement “distributed transactions”.
Distributed transactions are transactions that affect more than one data source.
The protocol is as follows:

  1. Start a transaction on all data sources involved in the transaction.
  2. Modify data in the data sources. If there is a problem, ROLLBACK all involved transactions.
  3. Once you are done, PREPARE all involved transactions.
  4. If the PREPARE step fails in any of the transactions, issue ROLLBACK PREPARED everywhere.
  5. If the PREPARE step succeeds everywhere, COMMIT PREPARED all involved transactions.

 

This so-called “two-phase commit protocol”

[...]
カテゴリー: postgresql

Peter Bengtsson: How I performance test PostgreSQL locally on macOS

planet postgresql - 2018-12-11(火) 04:18:36

It's weird to do performance analysis of a database you run on your laptop. When testing some app, your local instance probably has 1/1000 the amount of realistic data compared to a production server. Or, you're running a bunch of end-to-end integration tests whose PostgreSQL performance doesn't make sense to measure.

Anyway, if you are doing some performance testing of an app that uses PostgreSQL one great tool to use is pghero. I use it for my side-projects and it gives me such nice insights into slow queries that I'm willing to live with the cost that it is to run it on a production database.

This is more of a brain dump of how I run it locally:

First, you need to edit your postgresql.conf. Even if you used Homebrew to install it, it's not clear where the right config file is. Start psql (on any database) and type this to find out which file is the one:

$ psql kintobench kintobench=# show config_file; config_file ----------------------------------------- /usr/local/var/postgres/postgresql.conf (1 row)

Now, open /usr/local/var/postgres/postgresql.conf and add the following lines:

# Peterbe: From Pghero's configuration help. shared_preload_libraries = 'pg_stat_statements' pg_stat_statements.track = all

Now, to restart the server use:

▶ brew services restart postgresql Stopping `postgresql`... (might take a while) ==> Successfully stopped `postgresql` (label: homebrew.mxcl.postgresql) ==> Successfully started `postgresql` (label: homebrew.mxcl.postgresql)

The next thing you need is pghero itself and it's easy to run in docker. So to start, you need Docker for mac installed. You also need to know the database URL. Here's how I ran it:

docker run -ti -e DATABASE_URL=postgres://peterbe:@host.docker.internal:5432/kintobench -p 8080:8080 ankane/pghero

Note the trick of peterbe:@host.docker.internal because I don't use a password but inside the Docker container it doesn't know my terminal username. And the host.docker.internal is so the Docker container can reach the PostgreSQL installed on the

[...]
カテゴリー: postgresql

Jonathan Katz: Get Started Running PostgreSQL on Kubernetes

planet postgresql - 2018-12-11(火) 02:42:50

Interested in running PostgreSQL natively on Kubernetes? Let's look at a few quick steps to get up and running with the open source Crunchy PostgreSQL Operator for Kubernetes on your choice of Kubernetes deployment.

The Crunchy PostgreSQL Operator (aka "pgo") provides a quickstart script to automate the deployment of the Crunchy PostgreSQL Operator to a number of popular Kubernetes environments, including Google Kubernetes Engine (GKE), OpenShift Container Platform (OCP) and Pivotal Container Service (PKS). You can download the quickstart script here:

The script will deploy the operator to a GKE Kubernetes cluster or an OpenShift Container Platform cluster. The script assumes you have a StorageClass defined for persistence.

カテゴリー: postgresql

Bruce Momjian: Allocating Work_mem

planet postgresql - 2018-12-11(火) 01:45:01

I have already discussed the complexities of allocating shared_buffers, work_mem, and the remainder as kernel cache. However, even if these could be optimally configured, work_mem (and its related setting maintenance_work_mem) are configured per query, and potentially can be allocated multiple times if multiple query nodes require it. So, even if you know the optimal amount of work_mem to allocate for the entire cluster, you still have to figure out how to allocate it among sessions.

This detailed email thread explores some possible ways to simplify this problem, but comes up with few answers. As stated, it is a hard problem, and to do it right is going to take a lot of work. Even DB2's solution to the problem was criticized, though it was our initial approach to solving the problem. With additional parallelism, configuring work_mem is getting even more complex for users, to say nothing of the complexity of allocating parallel workers itself.

This is eventually going to have to be addressed, and doing it in pieces is not going to be fruitful. It is going to need an entirely new subsystem, perhaps with dynamic characteristics unseen in any other Postgres modules.

カテゴリー: postgresql

Alexey Lesovsky: Keeping Postgres tidy with partitioning

planet postgresql - 2018-12-10(月) 23:43:00
If you are in doubt whether partitioning is a useful tool with this one example I'm hoping you won’t wonder any further.
Let’s assume we have some historical data, such as logs, jobs, actions, events, metrics or something else, that stored in the database.

=# select pg_size_pretty(pg_relation_size('history_log'));
 pg_size_pretty
----------------
 155 GB

=# select count(*) from history_log;
   count
------------
 2102342910

At some point, we decide to clean old events.

=# delete from history_log where updated_at < '2018-11-01';
DELETE 1885782465
Time: 327220.719 ms (12:05.221)

The query would take twelve minutes to complete. However, during this action there is a less noticeable process that takes place - query would generate certain amount of WAL that will then need to be transferred into all standbys.

Ok, let’s check how many rows there would be in the table.

=# select count(*) from history_log;
   count
-----------
 216560445

=# select 100 * 1885782465::bigint / 2102342910;
 ?column?
----------
       89
It seems we deleted something around of 89% of the table, but let’s check its size.

=# select pg_size_pretty(pg_relation_size('history_log'));
 pg_size_pretty
----------------
 155 GB

Huh, the size hasn’t been changed?!

The thing is, Postgres never performs real deletion. It just marks rows as removed. Later on, space occupied by these "removed" rows will be cleared by vacuum and the available space can again be used for new rows, however, this space still belongs to a table. In some rare circumstances, table can be partially truncated and the free space can be returned to the file system.

Using partitioning for storing historical data can work wonders. It would allow us to drop old data quickly, without overhead related to WAL generation, so it would immediately free up space.
カテゴリー: postgresql

Regina Obe: PostGIS 2.5.1 Bundle for Windows

planet postgresql - 2018-12-10(月) 09:18:00

PostGIS 2.5.1 was released on November 18th 2018 and I finished off packaging the PostGIS 2.5.1 windows builds and installers targeted for PostgreSQL EDB distribution this weekend and pushing them up to stackbuilder. This covers PostgreSQL 9.4-11 64-bit and PostgreSQL 95-10 (32bit).

Note that PostGIS 2.5 series will be the last of the PostGIS 2s. Goodbye PostGIS 2.* and start playing with the in-development version of PostGIS 3. Snapshot binaries for PostGIS 3.0 windows development are also available on the PostGIS windows download page. These should work for both BigSQL and EDB distributions.

Continue reading "PostGIS 2.5.1 Bundle for Windows"
カテゴリー: postgresql

Lee Hampton: Evaluating High Availability Solutions for TimescaleDB + PostgreSQL

planet postgresql - 2018-12-08(土) 01:00:04

How we evaluated several third-party tools and ultimately selected Patroni as our preferred method.

If you are working with a database, you already know that high availability (HA) is a requirement for any reliable system. A proper HA setup eliminates the problem of a single point-of-failure by providing multiple places to get the same data, and an automated way of selecting a proper location with which to read and write data. PostgreSQL typically achieves HA by replicating the data on the primary database to one or more read-only replicas.

Streaming replication is the primary method of replication supported by TimescaleDB. It works by having the primary database server stream its write-ahead log (WAL) entries to its database replicas. Each replica then replays these log entries against its own database to reach a state consistent with the primary database.

Unfortunately, such streaming replication alone does not ensure that users don’t encounter some loss of uptime (“availability”) when a server crashes. If the primary node fails or becomes unreachable, there needs to be a method to promote one of the read replicas to become the new primary. And the faster that this failover occurs, the smaller the window of unavailability a user will see. One simple approach to such promotion is by manually performing it through a few PostgreSQL commands. On the other hand, automatic failover refers to a mechanism by which the system detects when a primary has failed, fully removes it from rotation (sometimes called fencing), promotes one read replica to primary status, and ensures the rest of the system is aware of the new state.

While PostgreSQL supports HA through its various replication modes, automatic failover is not something it supports out of the box. There are a number of third-party solutions that provide HA and automatic failover for PostgreSQL, many of which work with TimescaleDB because they also leverage streaming replication.

In this post, we will discuss how we evaluated third-party failover solutions and share

[...]
カテゴリー: postgresql

Bruce Momjian: The Memory Resource Triad

planet postgresql - 2018-12-07(金) 23:45:01

There are three resources that affect query performance: CPU, memory, and storage. Allocating CPU and storage for optimal query performance is straightforward, or at least linear. For CPU, for each query you must decide the optimal number of CPUs to allocate for parallel query execution. Of course, only certain queries can benefit from parallelism, and the optimal number of CPU changes based on the other queries being executed. So, it isn't simple, but it is linear.

For storage, it is more of a binary decision — should the table or index be stored on fast media (SSDs) or slow media (magnetic disks), particularly fast random access. (Some NAS servers have even more finely-grained tiered storage.) It takes analysis to decide the optimal storage type for each table or index, but the Postgres statistic views can help to identify which objects would benefit.

Unfortunately, for memory, resource allocation is more complex. Rather than being a linear or binary problem, it is a multi-dimensional problem — let me explain. As stated above, for CPUs you have to decide how many CPUs to use, and for storage, what type. For memory, you have to decide how much memory to allocate to shared_buffers at database server start, and then decide how much memory to allocate to each query for sorting and hashing via work_mem. What memory that is not allocated gets used as kernel cache, which Postgres relies on for consistent performance (since all reads and writes go through that cache). So, to optimize memory allocation, you have to choose the best sizes for:

  1. shared buffers (fixed size at server start)
  2. work_mem (potentially optimized per query based on workload)
  3. kernel buffers (the remainder)

Continue Reading »

カテゴリー: postgresql

Sebastian Insausti: How to Upgrade PostgreSQL10 to PostgreSQL11 with Zero Downtime

planet postgresql - 2018-12-07(金) 20:34:04

Historically, the hardest task when working with PostgreSQL has been dealing with the upgrades. The most intuitive upgrade way you can think of is to generate a replica in a new version and perform a failover of the application into it. With PostgreSQL, this was simply not possible in a native way. To accomplish upgrades you needed to think of other ways of upgrading, such as using pg_upgrade, dumping and restoring, or using some third party tools like Slony or Bucardo, all of them having their own caveats.

Why was this? Because of the way PostgreSQL implements replication.

PostgreSQL built-in streaming replication is what is called physical: it will replicate the changes on a byte-by-byte level, creating an identical copy of the database in another server. This method has a lot of limitations when thinking of an upgrade, as you simply cannot create a replica in a different server version or even in a different architecture.

So, here is where PostgreSQL 10 becomes a game changer. With these new versions 10 and 11, PostgreSQL implements built-in logical replication which, in contrast with physical replication, you can replicate between different major versions of PostgreSQL. This, of course, opens a new door for upgrading strategies.

In this blog, let's see how we can upgrade our PostgreSQL 10 to PostgreSQL 11 with zero downtime using logical replication. First of all, let's go through an introduction to logical replication.

What is logical replication?

Logical replication is a method of replicating data objects and their changes, based upon their replication identity (usually a primary key). It is based on a publish and subscribe mode, where one or more subscribers subscribe to one or more publications on a publisher node.

A publication is a set of changes generated from a table or a group of tables (also referred to as replication set). The node where a publication is defined is referred to as publisher. A subscription is the downstream side of logical replication. The node where a subscription is defined is

[...]
カテゴリー: postgresql

Michael Paquier: Postgres 12 highlight - New PGXS options for isolation and TAP tests

planet postgresql - 2018-12-07(金) 19:30:44

If you maintain some PostgreSQL extensions which rely on PGXS, a build infrastructure for PostgreSQL, the following commit added to Postgres 12 will be likely something interesting, because it adds new options to control more types of regression tests:

commit: d3c09b9b1307e022883801000ae36bcb5eef71e8 author: Michael Paquier <michael@paquier.xyz> date: Mon, 3 Dec 2018 09:27:35 +0900 committer: Michael Paquier <michael@paquier.xyz> date: Mon, 3 Dec 2018 09:27:35 +0900 Add PGXS options to control TAP and isolation tests, take two The following options are added for extensions: - TAP_TESTS, to allow an extention to run TAP tests which are the ones present in t/*.pl. A subset of tests can always be run with the existing PROVE_TESTS for developers. - ISOLATION, to define a list of isolation tests. - ISOLATION_OPTS, to pass custom options to isolation_tester. A couple of custom Makefile rules have been accumulated across the tree to cover the lack of facility in PGXS for a couple of releases when using those test suites, which are all now replaced with the new flags, without reducing the test coverage. Note that tests of contrib/bloom/ are not enabled yet, as those are proving unstable in the buildfarm. Author: Michael Paquier Reviewed-by: Adam Berlin, Álvaro Herrera, Tom Lane, Nikolay Shaplov, Arthur Zakirov Discussion: https://postgr.es/m/20180906014849.GG2726@paquier.xyz

This is similar rather to the existing REGRESS and REGRESS_OPTS which allow to respectively list a set of regression tests and pass down additional options to pg_regress (like a custom configuration file). When it comes to REGRESS, input files need to be listed in sql/ and expected output files are present in expected/, with items listed without a dedicated “.sql” suffix.

The new options ISOLATION and ISOLATION_OPTS added in PostgreSQL 12 are similar to REGRESS and REGRESS_OPTS, except that they can be used to define a set of tests to stress the behavior of concurrent sessions, for example for locking checks across commands, etc. PostgreSQL

[...]
カテゴリー: postgresql

Pavel Stehule: New release of Orafce extension

planet postgresql - 2018-12-07(金) 16:19:00
I released new mostly release of Orafce. Today it is massive package of emulation often used Oracle's API and the emulation is on maximum what is possible.

Now Orafce has good and very nice documentation written by Horikawa Tomohiro (big thanks for his work).

There are not too much news for people who use Oracle 3.6:
  • possibility to better emulate || operator for varchar2 and nvarchar2 types
  • few bugfixes
  • only PostgreSQL 9.4 and newer are supported
  • support for PostgreSQL 11, current master branch (future PostgreSQL 12) is supported too

カテゴリー: postgresql

Quinn Weaver: BDR talk by Mark Wong of 2nd Quadrant

planet postgresql - 2018-12-07(金) 05:37:00
In the Bay Area? This Wednesday, 2018-12-12, Mark Wong from 2nd Quadrant talking about BDR (Bi-Directional Replication, a form of multi-master) for PostgreSQL. This is a great chance to get inside, real-time info on BDR.

Multi-master is one of those features where when you need it, you really, really need it, and if you're in that category, this talk is for you. It's also of interest to anyone trying to figure out the best solution for scaling and redundancy beyond one machine and one data center.

To attend, you must RSVP at Meetup with your full name (for building security's guest list).
カテゴリー: postgresql

Kaarel Moppel: PostgreSQL affiliate projects for horizontal multi-terabyte scaling

planet postgresql - 2018-12-06(木) 18:00:20

Some weeks ago I wrote about some common concepts / performance hacks, how one can (relatively) easily scale to a terabyte cluster or more. And based on my experience visiting customers from various industries, 80% of them are not even reaching that threshold…but just to be clear I wanted to write another post showing that a couple of terabytes are of course not the “end station” for Postgres, given one is ready to roll up his sleeves and get “hands dirty“ so to say. So let’s look here at some additional Postgres-like projects for cases where you still want to make use of your Postgres know-how and SQL skills over big amounts of data.

But be warned, the road is getting bumpy now – we now usually need to change the applications and also the surrounding bits and we’re doing sharding, meaning data does not live on a single node anymore so SQL aggregates over all data can get quirky. Also we’re mostly extending the rock-solid core PostgreSQL with 3rd-party extensions or using forks with constraining characteristics, so you might have to re-define and re-import the data and you might need to learn some new query constructs and forget some standard PostgreSQL ones…so generally be prepared to pull out a bit of hair if you’ve got any left:) But OK, here some projects that you should know of.

Postgres extensions/derivatives for multi-terabyte scale-out
  • Sharding via PL/Proxy stored procedures

This kind of “old school” solution was created and battle tested in Skype (huge user of Postgres by the way!) by scaling an important cluster to 32 nodes so it obviously works pretty well. The main upside on the other hand is that all data and data access is sharded for you automatically after you pick a stored procedure parameter as shard key and you can use all of the standard Postgres features…with the downside that, well, all data access needs to go over PL/pgSQL stored procedures which most developers I guess are not so versed in. In short PL/Proxy is just some glue to get the stored procedure call to reach the correct shard so

[...]
カテゴリー: postgresql

Hubert 'depesz' Lubaczewski: Waiting for PostgreSQL 12 – Add log_statement_sample_rate parameter

planet postgresql - 2018-12-06(木) 04:34:22
On 29th of November 2018, Alvaro Herrera committed patch: Add log_statement_sample_rate parameter   This allows to set a lower log_min_duration_statement value without incurring excessive log traffic (which reduces performance). This can be useful to analyze workloads with lots of short queries.   Author: Adrien Nayrat   Discussion: https://postgr.es/m/-ee1e-db9f-fa97-@anayrat.info One of the problems I did encounter … Continue reading "Waiting for PostgreSQL 12 – Add log_statement_sample_rate parameter"
カテゴリー: postgresql

Tomas Vondra: Databases vs. encryption

planet postgresql - 2018-12-06(木) 00:30:34

Let’s assume you have some sensitive data, that you need to protect by encryption. It might be credit card numbers (the usual example), social security numbers, or pretty much anything you consider sensitive. It does not matter if the encryption is mandated by a standard like PCI DSS or if you just decided to encrypt the sensitive stuff. You need to do the encryption right and actually protecting the information in both cases. Unfortunately, full-disk-encrytion and pgcrypto are not a good fit for multiple reasons, and application-level encryption reduces the database to “dumb” storage. Let’s look at an alternative approach – offloading the encryption to a separate trusted component, implemented as a custom data type.

Note: A couple of weeks ago at pgconf.eu 2018, I presented a lightning talk introducing a PoC of an alternative approach to encrypting data in a database. I got repeatedly asked about various details since then, so let me just explain it in this blog post.

FDE and pgcrypto

In the PostgreSQL world, people will typically recommend two solutions to this problem – full-disk encryption and pgcrypto. Unfortunately, neither of them really works for this use case :-(

Full-disk encryption (FDE) is great. It’s transparent to the database (and application), so there are no implementation changes needed. The overhead is very low, particularly when your CPU supports AES-NI etc. The problem is it only really protects against someone stealing the disk. It does not protect against OS-level attacks (rogue sysadmin, someone gaining remote access to the box or backups, …). Nor does it protect against database-level attacks (think SQL injection). And most importantly, it’s trivial to leak the plaintext data into server log, various monitoring systems etc. Not great.

pgcrypto addresses some of these issues as the encryption happens in the database. But it means the database has to know the keys, and those are likely part of SQL queries and so the issue with leaking data into server logs and monitoring systems is still

[...]
カテゴリー: postgresql

Bruce Momjian: The Meaning of WAL

planet postgresql - 2018-12-06(木) 00:30:01

The write-ahead log (WAL) is very important for Postgres reliability. However, how it works is often unclear.

The "write-ahead" part of the write-ahead log means that all database changes must be written to pg_wal files before commit. However, shared buffers dirtied by a transaction can be written (and fsync'ed) before or after the transaction commits.

Huh? Postgres allows dirty buffers to be written to storage before the transaction commits? Yes. When dirty buffers are written to storage, each modified row is marked with the currently-executing transaction id that modified it. Any session viewing those rows knows to ignore those changes until the transaction commits. If it did not, a long transaction could dirty all the available shared buffers and prevent future database changes.

Continue Reading »

カテゴリー: postgresql

Alexey Lesovsky: Why avoid long transactions?

planet postgresql - 2018-12-05(水) 19:15:00
The majority of PostgreSQL community clearly understands why long and idle transactions are “bad”. But when you talk about it to the newcomers it’s always a good idea to backup your explanation with some real tests.

While preparing slides for my presentation about vacuum I have made a simple test case with long transaction using pgbench. Here are the results.

pgbench -c8 -P 60 -T 3600 -U postgres pgbench
starting vacuum...end.
progress: 60.0 s, 9506.3 tps, lat 0.841 ms stddev 0.390
progress: 120.0 s, 5262.1 tps, lat 1.520 ms stddev 0.517
progress: 180.0 s, 3801.8 tps, lat 2.104 ms stddev 0.757
progress: 240.0 s, 2960.0 tps, lat 2.703 ms stddev 0.830
progress: 300.0 s, 2575.8 tps, lat 3.106 ms stddev 0.891

in the end

progress: 3300.0 s, 759.5 tps, lat 10.533 ms stddev 2.554
progress: 3360.0 s, 751.8 tps, lat 10.642 ms stddev 2.604
progress: 3420.0 s, 743.6 tps, lat 10.759 ms stddev 2.655
progress: 3480.0 s, 739.1 tps, lat 10.824 ms stddev 2.662
progress: 3540.0 s, 742.5 tps, lat 10.774 ms stddev 2.579
progress: 3600.0 s, 868.2 tps, lat 9.215 ms stddev 2.569

This is a standard TPC-B pgbench test, running on a small database which completely resides in shared buffers (it removes disk IO influences).

As you can see, the performance measured in transaction per second initially dropped during the first few minutes of the test and continues to reduce further.

Look at the statistics from the vacuum logs:

tuples: 0 removed, 692428 remain, 691693 are dead but not yet removable, oldest xmin: 62109160
tuples: 0 removed, 984009 remain, 983855 are dead but not yet removable, oldest xmin: 62109160
tuples: 0 removed, 1176821 remain, 1176821 are dead but not yet removable, oldest xmin: 62109160
tuples: 0 removed, 1494122 remain, 1494122 are dead but not yet removable, oldest xmin: 62109160
tuples: 0 removed, 2022284 remain, 2022284 are dead but not yet removable, oldest xmin: 62109160
tuples: 0 removed, 2756298 remain, 2756153 are dead but not yet removable, oldest xmin: 62109160
tuples: 0 removed, 3500913 remain, 3500693[...]
カテゴリー: postgresql

ページ