Infrastructure at your Service
Puh, that last post in this series is already half a year old. Time is moving too fast Today, while being at a customer again, this question came up: Can I do something comparable in PostgreSQL to what I can do in Oracle, which is: Create a user and provide the hashed password so that the password is the same on the source and the target (which implies not knowing the password at all)? In…
Finally I am home from pgconf.de in Berlin at the beautiful Müggelsee. Beside meeting core PostreSQL people such Devrim and Bruce, Andreas and joining Jan again for great discussions and some beers, joking with Anja, being at the dbi services booth, discussing with people, kidding with Hans: was it worth the effort? Yes, it was, and here is why.
Another partitioning improvement for PostgreSQL 11: Insert…on conflict is now supported (for most cases) in PostgreSQL 11 thanks to this commit. Lets see how it works.
Up to PostgreSQL 10 the wal segment size was fixed once PostgreSQL was compiled and installed. When you wanted to have something else than the default you needed to tell that to the configure command when building from source, e.g. postgres@pgbox:/home/postgres/postgresql/ [PGDEV] ./configure –with-wal-segsize=64MB For PostgreSQL 11 two commits landed (1 and 2) which change this. Lets have a look.
Every time you send a SQL statement to PostgreSQL it must be parsed. Parsing is fast, yes, but parsing the same statement a thousand times can quickly sum up to quite some time the database could spend for something else. To avoid that PostgreSQL comes with the prepare statement. Using that you can avoid parsing of statements and only planning and executing will happen. Lets see how that works.
Although PostgreSQL 10 integrate native logical replication (have a look here or here), it is always interesting to be aware of alternative solutions to the available in-core features. One of those is called pglogical. It’s a Postgres extension developed by 2ndQuadrant, major contributor to PostgreSQL development. The goal of this blog post is to discover how to install pglogical and configure it in order to generate simple logical replication cases. Installation To get the extension…
In the last post about partitioning improvements in PostgreSQL 11 we talked about unique indexes and primary keys on partitioned tables. Both did not work in PostgreSQL 10 but now do in PostgreSQL 11. Another operation that did not work in PostgreSQL 10 and does now in PostgreSQL 11 is: Updating the partition key, which means that a row will move from one partition to another. Lets have a look at that.
PostgreSQL 10 comes with partition support. But Hash Partitioning was not supported. PostgreSQL 11 will support Hash Partition. In the documentation we can read The table is partitioned by specifying a modulus and a remainder for each partition. Each partition will hold the rows for which the hash value of the partition key divided by the specified modulus will produce the specified remainder. For this demonstration let’s create a table with a hash partition drop…
Yesterday, while being at a customer, an interesting question popped up: What is the maximum of in list values in PostgreSQL? I couldn’t answer although I never read somewhere that there is a limit. The following is for fun only and I am not saying that creating huge in lists is a good idea. Lets go.
When declarative partitioning was introduced with PostgreSQL 10 this was a big step forward. But as always with big new features some things do not work in PostgreSQL 10 which now get resolved in PostgreSQL 11. One of those are local partitioned indexes. To make it easier to understand lets start with an example in PostgreSQL 10.
