Ibrar Ahmed: Track PostgreSQL Row Changes Using Public/Private Key Signing

planet postgresql - 2018-10-12(金) 22:43:31

Authorisations and encryption/decryption within a database system establish the basic guidelines in protecting your database by guarding against malicious structural or data changes.

What are authorisations?

Authorisations are the access privileges that mainly control what a user can and cannot do on the database server for one or more databases. So consider this to be like granting a key to unlock specific doors. Think of this as more like your five star hotel smart card. It allows you access all facilities that are meant for you, but doesn’t let you open every door. Whereas, privileged staff have master keys which let them open any door.

Similarly, in the database world, granting permissions secures the system by allowing specific actions by specific users or user groups, yet it allows database administrator to perform whatever action(s) on the database he/she wishes. PostgreSQL provides user management where you can can create users, and grant and revoke their privileges.


Encryption, decryption can protect your data, obfuscate schema structure and help hide code from prying eyes. Encryption/decryption hides the valuable information and ensures that there are no mischievous changes in the code or data that may be considered harmful. In almost all cases, data encryption and decryption happens on the database server. This is more like hiding your stuff somewhere in your room so that nobody can see it, but also making your stuff difficult to access.

PostgreSQL also provides encryption using pgcrypto (PostgreSQL extension). There are some cases where you don’t want to hide the data, but don’t want people to update it either. You can revoke the privileges to modify the data.

Data modifications

But what if an admin user modifies the data? How you can identify that data is changed? If somebody changes the data and you don’t know about, then it is more dangerous than you losing your data, as you are relying on data which may no longer be valid.

Logs in database systems allow us to track back changes and “po

カテゴリー: postgresql

Shaun M. Thomas: PG Phriday: Studying Stored Procedures in Postgres 11

planet postgresql - 2018-10-12(金) 16:00:47
Studying Stored Procs in Postgres 11

With Postgres 11 looming on the near horizon, it’s only appropriate to check out a recent beta and kick the tires a few times. Whether it’s improvements in parallelism, partitions, stored procedures, JIT functionality, or any number of elements in the release page, there’s a lot to investigate.

It just so happens that I ran across a fortuitous event on Twitter when deciding on an appropriate topic. Behold!

Wasting XIDs has never been this fun

Wait! No! That’s not what stored procedures are for!

I felt so good like anything was possible

When confronted with such a blatant corruption of such a cool new feature, it’s only natural to question the wisdom of doing so. It is, after all, not a great idea to programatically consume transaction IDs. I said as much and moved on with life, certain the worst was over.

Then this happened.

Magnus helpfully recommends proceeding

Now, Magnus is infamous for two things: his technical acumen, and giddy malevolence. His advocacy of a stored procedure named “waste_xid” only proved nobody anywhere should ever run this anywhere, lest they immolate whatever system hosted the database instance.

But hey, VMs are cheap; let’s break things. How else can we learn the limits of our new toys, but by virtually atomizing them?

I hit cruise control and rubbed my eyes

Before we saddle our poor innocent Postgres 11 installation with an inadvisable stored procedure designed specifically to underhandedly reap its transaction lifespan, we should probably make the routine as evil as possible.

One thing stands out immediately: calling EXECUTE is unnecessary overhead. According to the information function documentation, txid_current will assign a new transaction ID if there isn’t one already. Since the stored procedure is constantly committing, that’s extremely handy. And since this is Pl/pgSQL, we can use direct assignment instead.

Our new procedure looks something like this:

カテゴリー: postgresql

Pavel Stehule: New functionality of plpgsql_extension

planet postgresql - 2018-10-11(木) 23:40:00
I am working on new release of plpgsql_check extension -

Interesting new function is possibility to return list of used relation and functions. With these information is easy to generate dependency graph:

postgres=# \sf fx
LANGUAGE plpgsql
AS $function$
perform upper(((plus(a) + 200) * 100)::text) from xx;
postgres=# select * from plpgsql_show_dependency_tb('fx()');
│ type │ oid │ schema │ name │ params │
│ FUNCTION │ 18310 │ public │ plus │ (integer) │
│ RELATION │ 24576 │ public │ xx │ │
(2 rows)
カテゴリー: postgresql

Bruce Momjian: Multi-Host Libpq

planet postgresql - 2018-10-11(木) 03:45:01

Libpq is used by many client interface languages to communicate with the Postgres server. One new feature in Postgres 10 is the ability to specify multiple servers for connection attempts. Specifically, it allows the connection string to contain multiple sets of host, hostaddr, and port values. These are tried until one connects.

NoSQL solutions have used this method of multi-host connectivity for a while, so it is good Postgres can now do it too. It doesn't have all the features of a separate connection pooler, but it doesn't have the administrative or performance overhead of a separate connection pooler either, so it certainly fits a need for some environments.

カテゴリー: postgresql

Jonathan Katz: Won’t You Be My Neighbor? Quickly Finding Who is Nearby

planet postgresql - 2018-10-10(水) 23:01:54

Many applications these days want us to know how close we are to things:

  • What are the three closest coffee shops to my current location?
  • Which is the nearest airport to the office?
  • What are the two closest subway stops to the restaurant?

and countless more examples.

Another way of asking these questions is to say “who are my nearest neighbors to me?” This maps to a classic algorithmic problem: efficiently finding the K-nearest neighbors (or K-NN), where K is a constant. For example, the first question would be a 3-NN problem as we are trying to find the 3 closest coffee shops.

(If you are interested in learning more about K-NN problems in general, I highly recommend looking at how you can solve this using n-dimensional Voronoi diagrams, a wonderful data structure developed in the field of computational geometry.)

How can we use PostgreSQL to help us quickly find our closest neighbors? Let’s explore.

カテゴリー: postgresql

Laurenz Albe: Are your foreign keys indexed?

planet postgresql - 2018-10-10(水) 17:01:23
© Laurenz Albe 2018


Foreign key constraints are an important tool to keep your database consistent while also documenting relationships between tables.

A fact that is often ignored is that foreign keys need proper indexing to perform well.

This article will explain that and show you how to search for missing indexes.

Index at the target of a foreign key

In the following, I will call the table on which the foreign key constraint is defined the source table and the referenced table the target table.

The referenced columns in the target table must have a primary key or unique constraint. Such constraints are implemented with unique indexes in PostgreSQL. Consequently, the target side of a foreign key is automatically indexed.

This is required so that there is always a well-defined row to which the foreign key points. The index also comes handy if you want to find the row in the target table that matches a row in the source table.

Index at the source of a foreign key

In contrast to the above, PostgreSQL requires no index at the source of a foreign key.

However, such an index is quite useful for finding all source rows that reference a target row. The typical cases where you need that are:

1. You perform a join between the two tables where you explicitly search for the source rows referencing one or a few target rows. If there is an index on the columns at the source, PostgreSQL can use an efficient nested loop join.

This is well known and pretty obvious.

2. You delete rows or update key columns in the target table.

Then PostgreSQL has to check if the foreign key constraint is still satisfied. It does so by searching if there are rows in the source table that would become orphaned by the data modification. Without an index, this requires a sequential scan of the source table.

An example

Let’s build a source and a target table:

-- to make the plans look simpler SET max_parallel_workers_per_gather = 0; -- to speed up CREATE INDEX SET maintenance_work_mem = '512MB'; CREATE TABLE target ( t_id integer NOT NULL, [...]
カテゴリー: postgresql

Andrew Dunstan: Managing Freezing in PostgreSQL

planet postgresql - 2018-10-10(水) 16:26:18

Postgres contains a moving event horizon, which is in effect about 2 billion transactions ahead of or behind the current transaction id. Transactions more than 2 billion ahead of or more than 2 billion behind the current transaction id are considered to be in the future, and will thus be invisible to current transactions.

Postgres avoids this catastrophic data loss by specially marking old rows so that no matter where they are in relation to the current transaction id they will be visible.

Freezing is this process of marking old live tuples (i.e. database rows) so that they don’t get run over by the moving event horizon that would otherwise make them appear to be in the future. This is in contrast to vacuuming, which is the freeing up of space consumed by old dead tuples that are no longer visible to any transaction.

Both processes are managed by vacuum.

There are a number of settings that govern how freezing is done.

First, vacuum_freeze_min_age governs whether or not a tuple will be frozen while vacuum is already looking at a page to see if it has dead tuples that can be cleaned up. Tuples older than vacuum_freeze_min_age will be frozen in this case. Setting this low means that there will be less work to do later on, but at the possible cost of extra effort both in CPU and IO or WAL activity. Generally you probably want this set to at least a few hours worth of transactions. Let’s say you’re expecting to do up to 2000 transactions per second as a sustained rate. 2000 TPS is 7.2m transactions per hour. Thus a fairly aggressive setting for this case might be say 20m. The default setting is 50m. Similarly for vacuum_multixact_freeze_min_age. Note that the transaction_id and multixid counters are independent – you need to keep track of both of them.

Second, there are vacuum_freeze_table_age and vacuum_multixact_freeze_table_age. These settings govern when autovacuum will not just look at pages that might have dead rows, but any page that might have unfrozen rows. The defaults for these settings are 150m. If you hav

カテゴリー: postgresql

Fernando Laudares Camargos: PostgreSQL Monitoring: Set Up an Enterprise-Grade Server (and Sign Up for Webinar Weds 10/10…)

planet postgresql - 2018-10-10(水) 01:23:44

This is the last post in our series on building an enterprise-grade PostgreSQL set up using open source tools, and we’ll be covering monitoring.

The previous posts in this series discussed aspects such as security, backup strategy, high availability, connection pooling and load balancing, extensions, and detailed logging in PostgreSQL. Tomorrow, Wednesday, October 10 at 10AM EST, we will be reviewing these topics together, and showcasing then in practice in a webinar format: we hope you can join us!

Register Now


Monitoring databases

The importance of monitoring the activity and health of production systems is unquestionable. When it comes to the database, with its high number of customizable settings, the ability to track its various metrics (status counters and gauges) allows for the maintenance of a historical record of its performance over time. This can be used for capacity planningtroubleshooting and validation.

When it comes to capacity planning, a monitoring solution is a helpful tool to help you assess how the current setup is faring. At the same time, it can help predict future needs based on trends, such as the increase of active connections, queries, and CPU usage. For example, an increase in CPU usage might be due to a genuine increase in workload, but it could also be a sign of unoptimized queries growing in popularity. In which case, comparing CPU with disk access might provide a more complete view of what is going on.

Being able to easily correlate data like this helps you to catch minor issues and to plan accordingly, sometimes allowing you to avoid an easier but more costly solution of scaling up to mitigate problems like this. But having the right monitoring solution is really invaluable when it comes to investigative work and root cause analysis. Trying to understand a problem that has already taken place is a rather complicated, and often unenviable, task unless you established a continuous, watchful eye on the set up for the whole time.

Finally, a monitoring solution can help you valida

カテゴリー: postgresql

Bruce Momjian: Trigger Me Writable

planet postgresql - 2018-10-09(火) 23:30:01

Postgres support for hot standby servers allows read-only queries to be run on standby servers, but how are read-only sessions handled when promoting a standby server to primary? After a standby is promoted to primary, new connections are read/write, but existing connections also change to read/write:

SELECT pg_is_in_recovery(); pg_is_in_recovery ------------------- t SHOW transaction_read_only; transaction_read_only ----------------------- on \! touch /u/pg/data2/primary.trigger -- wait five seconds for the trigger file to be detected CREATE TABLE test (x INTEGER); SELECT pg_is_in_recovery(); pg_is_in_recovery ------------------- f SHOW transaction_read_only; transaction_read_only ----------------------- off

Continue Reading »

カテゴリー: postgresql

Fernando Laudares Camargos: Detailed Logging for Enterprise-Grade PostreSQL

planet postgresql - 2018-10-08(月) 22:13:21

In this penultimate post from our series on building an enterprise-grade PostgreSQL environment we cover the parameters we have enabled to configure detailed logging in the demo setup we will showcase in our upcoming webinar.

Detailed logging in PostgreSQL and log analyzer

Like other RDBMS, PostgreSQL allows you to maintain a log of activities and error messages. Until PostgreSQL 9.6, PostgreSQL log files were generated in pg_log directory (inside the data directory) by default. Since PostgreSQL 10, pg_log has been renamed to simply log. However, this directory can be modified to a different location by modifying the parameter log_directory.

Unlike MySQL, PostgreSQL writes the error and activity log to the same log file thus it may grow to several GBs when detailed logging is enabled. In these cases, logging becomes IO-intensive thus it is recommended to store log files in a different storage to the one hosting the data directory.

Parameters to enable detailed logging

Here’s a list of parameters used to customize logging in PostgreSQL. All of them need to be modified in the postgresql.conf or files.

logging_collector: in order to log any activity in PostgreSQL this parameter must be enabled. The backend process responsible for logging database activity is called logger, it gets started when logging_collector is set to ON. Changing this parameter requires a PostgreSQL restart.

log_min_duration_statement: this parameter is used primarily to set a time threshold: queries running longer than such should be logged (as “slow queries”). Setting it to -1 disables logging of statements. Setting it to 0 enables the logging of every statement running in the database, regardless of its duration. The time unit should follow the actual value, for example: 250ms,  250s, 250min, 1h. Changing this parameter does not require a PostgreSQL restart – a simple reload of the configuration is enough.reload but not a restart. For example:

log_min_duration_statement = 5s   logs every statement running for 5 seconds o[...]
カテゴリー: postgresql

Brian Fehrle: Dynamic Monitoring of PostgreSQL Instances Using pg_top

planet postgresql - 2018-10-08(月) 18:57:00

Monitoring PostgreSQL can, at times, be like trying to wrangle cattle in a thunderstorm. Applications connect and issue queries so quickly, it’s hard to see what’s going on or even get a good overview of the performance of the system other than the typical developer complaining ‘things are slow, help!’ kind of requests.

In previous articles, we’ve discussed how to Get to the Source when PostgreSQL is acting slow, but when the source is specifically queries, basic level monitoring may not be enough for assessing what’s going on in an active live environment.

Enter pg_top, a PostgreSQL specific program to monitor real time activity in a database, as well as view basic information for the database host itself. Much like the linux command ‘top’, running it brings the user into a live interactive display of database activity on the host, refreshing automatically in intervals.


Installing pg_top can be done in the generally expected ways: package managers and source install. The most recent version as of this article is 3.7.0.

Package Managers

Based on the distribution of linux in question, search for pgtop or pg_top in the package manager, it’s likely available in some aspect for the installed version of PostgreSQL on the system.

Red Hat based distros:

# sudo yum install pg_top

Gentoo based distros:

# sudo apt-get install pgtop Source

If desired, pg_top can be installed via source from the PostgreSQL git repository. This will provide any version desired, even newer builds that are not yet in the official releases.


Once installed, pg_top works as a very accurate real time view into the database it is monitoring and using the command line to run ‘pg_top’ will launch the interactive PostgreSQL monitoring tool.

The tool itself can help shed light on all processes currently connected to the database.

Running pg_top

Launching pg_top is the same as the unix / linux style ‘top’ command itself, along with connection information to the database.

To run pg_top on a local database host:

pg_top -h localhost[...]
カテゴリー: postgresql

Pavel Stehule: Pager for data

planet postgresql - 2018-10-08(月) 17:03:00
Any Unix like systems has great feature - a pager. The history of pagers is pretty old. Originally was used manually. The result of some programs can be redirected to any program with pipe operator.

ls -la | more

One, most simple pager is more. It can scroll only in one direction. Usually this is default pager in Unix systems.

Much more powerful pager is less. Has lot of advantages and functionality - mainly possibility to scroll in two direction, strong navigation inside result. It can display long lines, and allow horizontal scrolling. This pager is necessary for comfortable usage of psql console.

export PAGER="less -S"
psql postgres
postgres=>select * from pg_class;

It is good to know some less commands:
  • g - move to document begin,
  • G - move to document end,
  • / - search string,
  • n - next occurrence of string,
  • N - previous occurrence of string,
  • q - quit.
less is very rich pager has special support for displaying man pages. But this feature we cannot to use for database data. For tabular data, there are special pager pspg. This pager can freeze n first columns, and m first rows. It knows almost all keyboard commands of less pager, and append some new (based on mcedit keywords).
  • Alt k - new bookmark
  • Alt i - previous bookmark
  • Alt j - next bookmark
  • Alt n - show row numbers
  • Alt l - go to line
  • Ctrl Home - move to document begin
  • Ctrl End - move to document end
  • F9 - menu
  • F10 - quit
The usage of pspg is same: export PAGER=pspg
psql postgres
This pager is available from PostgreSQL community repository or from git
カテゴリー: postgresql

Avinash Kumar: PostgreSQL Extensions for an Enterprise-Grade System

planet postgresql - 2018-10-06(土) 01:43:42

In this current series of blog posts we have been discussing various relevant aspects when building an enterprise-grade PostgreSQL setup, such as security, back up strategy, high availability, and different methods to scale PostgreSQL. In this blog post, we’ll get to review some of the most popular open source extensions for PostgreSQL, used to expand its capabilities and address specific needs. We’ll cover some of them during a demo in our upcoming webinar on October 10.

Expanding with PostgreSQL Extensions

PostgreSQL is one of the world’s most feature-rich and advanced open source RDBMSs. Its features are not just limited to those released by the community through major/minor releases. There are hundreds of additional features developed using the extensions capabilities in PostgreSQL, which can cater to needs of specific users. Some of these extensions are very popular and useful to build an enterprise-grade PostgreSQL environment. We previously blogged about a couple of FDW extensions (mysql_fdw and postgres_fdw ) which will allow PostgreSQL databases to talk to remote homogeneous/heterogeneous databases like PostgreSQL and MySQL, MongoDB, etc. We will now cover a few other additional extensions that can expand your PostgreSQL server capabilities.


The pg_stat_statements module provides a means for tracking execution statistics of all SQL statements executed by a server. The statistics gathered by the module are made available via a view named pg_stat_statements. This extension must be installed in each of the databases you want to track, and like many of the extensions in this list, it is available in the contrib package from the PostgreSQL PGDG repository.


Tables in PostgreSQL may end up with fragmentation and bloat due to the specific MVCC implementation in PostgreSQL, or simply due to a high number of rows being naturally removed. This could lead to not only unused space being held inside the table but also to sub-optimal execution of SQL statements. pg_repack is the most popul

カテゴリー: postgresql

Sebastian Insausti: A New Way to Personalize Your PostgreSQL Monitoring with Prometheus

planet postgresql - 2018-10-05(金) 17:54:06

Monitoring is one of the fundamental tasks in any system. It can help us to detect problems and take action, or simply to know the current state of our systems. Using visual displays can make us more effective as we can easier detect performance problems.

In this blog, we will see how to use SCUMM to monitor our PostgreSQL databases and what metrics we can use for this task. We’ll also go through the dashboards available, so you can easily figure out what’s really happening with your PostgreSQL instances.

What is SCUMM?

First of all, let’s see what is SCUMM (Severalnines CMON Unified Monitoring and Management ).

It’s a new agent-based solution with agents installed on the database nodes.

The SCUMM Agents are Prometheus exporters which export metrics from services like PostgreSQL as Prometheus metrics.

A Prometheus server is used to scrape and store time series data from the SCUMM Agents.

Prometheus is an open-source system monitoring and alerting toolkit originally built at SoundCloud. It is now a standalone open source project and maintained independently.

Prometheus is designed for reliability, to be the system you go to during an outage to allow you to quickly diagnose problems.

How to use SCUMM?

When using ClusterControl, when we select a cluster, we can see an overview of our databases, as well as some basic metrics that can be used to identify a problem. In the below dashboard, we can see a master-slave setup with one master and 2 slaves, with HAProxy and Keepalived.

ClusterControl Overview

If we go to the “Dashboards” option, we can see a message like the following.

ClusterControl Dashboards Disabled

To use this feature, we must enable the agent mentioned above. For this, we only have to press on the "Enable Agent Based Monitoring" button in this section.

ClusterControl Enable Agent Based Monitoring

To enable our agent, we must specify the host where we will install our Prometheus server, which, as we can see in the example, can be our ClusterControl server.

We must also specify:

  • Scrape Interval
カテゴリー: postgresql

Niksa Jakovljevic: How to manage Prometheus high-availability with PostgreSQL + TimescaleDB

planet postgresql - 2018-10-05(金) 04:14:20

Prometheus has become one of the most commonly used open-source monitoring and alerting systems. What makes Prometheus so great is that it solves monitoring problems in a simple and effective way. Yet there are still challenges with operating Prometheus, including long-term storage and managing high-availability.

In a previous post, we describe how and why to use PostgreSQL + TimescaleDB for enabling long-term storage (as well as for simplifying operations, and adding query power/flexibility) to Prometheus.

Uniting SQL and NoSQL for Monitoring: Why PostgreSQL is the ultimate data store for Prometheus

In this post, we describe how PostgreSQL + TimescaleDB can also help with managing high-availability.

There are several reasons why to add high-availability (HA) to Prometheus deployments:

  • Avoiding data loss and a single point of failure
  • Avoiding missing any critical alerts generated by an Alertmanager
  • Replicating Prometheus data (although the data will not be fully consistent between replicas — more on this later)

Setting up HA for Prometheus requires running two of more identically configured Prometheus servers in parallel. Yet this simple setup does come with limitations.

One problem is that it can be hard to keep data in sync. Often parallel Prometheus instances do not have identical data. There are few reasons for this. One reason is that scrape intervals may differ, since each instance has its own clock. Another reason is that, in case of instance failures, either of the Prometheus nodes may miss some data, which would mean that in the long run none of the Prometheus instances would have a complete data set. In other words, there would be no single source of truth.

Another problem is with remote storage. When running HA Prometheus with remote storage, one can decide to just have a separate remote storage for each Prometheus instance. But this approach, while valid, does introduce other weaknesses: again, no ground truth; the complexity of operating multiple remote storage instances; and hardware costs.


カテゴリー: postgresql

Lukas Fittl: New in Postgres 11: Monitoring JIT performance, Auto Prewarm & Stored Procedures

planet postgresql - 2018-10-04(木) 21:00:00
Everyone’s favorite database, PostgreSQL, has a new release coming out soon: Postgres 11 In this post we take a look at some of the new features that are part of the release, and in particular review the things you may need to monitor, or can utilize to increase your application and query performance. Just-In-Time compilation (JIT) in Postgres 11 Just-In-Time compilation (JIT) for query execution was added in Postgres 11. It's not going to be enabled for queries by default, similar to parallel…
カテゴリー: postgresql

Bruce Momjian: Moving Tablespaces

planet postgresql - 2018-10-04(木) 00:00:01

Tablespaces are designed to allow Postgres clusters to be spread across multiple storage devices. CREATE TABLESPACE creates a symbolic link in the pg_tblspc directory in the cluster's data directory pointing to the newly-created tablespace directory.

Unfortunately, though there is a command to move tables and indexes between tablespaces, there is no command to move tablespaces to different directories. However, since Postgres 9.2, the process of moving tablespaces is quite simple:

  1. Record the OID of the tablespace you want to move
  2. Shut down the Postgres cluster
  3. Move the tablespace directory, either within the same file system or to a different file system
  4. Update the OID symbolic link that represents the moved tablespace to the new tablespace directory location
  5. Restart the server

Here's an example of moving a tablespace:

Continue Reading »

カテゴリー: postgresql

Liaqat Andrabi: Webinar : New Features in PostgreSQL 11 [Follow Up]

planet postgresql - 2018-10-03(水) 21:44:24

PostgreSQL 11, the next major release of the world’s most advanced open source database, is just around the corner. The new release of PostgreSQL will include enhancements in partitioning, parallelism, SQL stored procedures and much more.

To give PostgreSQL enthusiasts a deeper look into the upcoming release, 2ndQuadrant hosted a Webinar discussing the new features in PostgreSQL 11. The webinar was presented by Peter Eisentraut, Core Team Member and Major PostgreSQL Contributor.

If you weren’t able to make it to the live session, you can now view the recording here.

Questions that Peter couldn’t respond to during the live webinar have been answered below.

Q: Could you use a custom hash function for partitioning? (or is this in future plans)

A: This is currently not planned.

Q: As far as i understand, there are still no global indexes on partitioned tables. Am i right?

A: Right, these are only local indexes that get automatically added to new partitions and can be managed more simply. Global indexes would be a separate feature that is not currently on the horizon.

Q: What about commit in function? Is this available as well?

A: Commit and rollback are only possible in procedures.

Q: Will JIT-enabled builds be available through PGDG?

A: Yes, community produced Debian and RPM packages will have LLVM support. It might be in a separate sub-package. But some older operating systems don’t have a recent enough LLVM version, so in that case it won’t be available.

Q: Does JIT inlining work with user-defined operators?

A: Yes. The extension module needs to be built in a certain way to install the associated LLVM bitcode. But the normal extension build system takes care of that.

Q: Are JIT compiled queries cached? So they can be reused either by the same session or another session.

A: No, the result of JIT compilation is not cached. This was considered but was not deemed worthwhile at this time.

Q: What about TDE in PostgreSQL 11?

A: TDE is not in PostgreSQL 11.

Q: Can we expect data at rest encryption enhancements in Postgre

カテゴリー: postgresql

Jobin Augustine: Scaling PostgreSQL using Connection Poolers and Load Balancers for an Enterprise Grade environment

planet postgresql - 2018-10-03(水) 01:15:30

In the previous blog posts in this series we’ve covered some of the essential aspects of an Enterprise-grade solution: security, high availability, and backups. Another important aspect is the scalability of the solution: as our application grows how do we accommodate an increase in traffic while maintaining the quality of the service (response time)? The answer to this question depends on the nature of the workload at play but it is often shaped around:

(a) improving its efficiency and
(b) increasing the resources available.

Why connection pooling ?

When it comes to improving the efficiency of a database workload, one of the first places we start looking at is the list of slow queries; if the most popular ones can be optimized to run faster then we can easily gain some overall performance back. Arguably, we may look next at the number and frequency of client connections: is the workload composed of a high number of very frequent but short-lived connections? Or are clients connections of a more moderate number, and tend to stick around for longer ?

If we consider the first scenario further–a high number of short lived connections–and that each connection spawns a new OS process, the server may hit a practical limit as to the number of transactions—or connections—it can manage per second, considering the hardware available and the workload being processed. Remember that PostgreSQL is process-based, as opposed to thread-based, which is itself an expensive operation in terms of resources, both CPU and memory.

A possible remedy for this would be the use of a connection pooler, acting as a mediator between the application and the database. The connection pooler keeps a number of connections permanently opened with the database, and receives and manages all incoming requests from clients itself, allowing them to temporarily use one of the connections it already has established with PostgreSQL. This removes the burden of creating a new process each time a client establishes a connection with PostgreSQL, and allows it

カテゴリー: postgresql

Oleg Bartunov: Bloom index for bigint

planet postgresql - 2018-10-03(水) 01:14:17
Bloom index by default works for int4 and text, but other types with hash function and equality operator could be supported. Just use opclass interface, for example, for type bigint

OPERATOR 1 =(bigint, bigint),
FU>CTION 1 hashint8(bigint);

Now, you can build bloom index for bigint data type.
カテゴリー: postgresql