Neues vom PostgreSQL Planet
How hard could it be to reset a sequence?Restarting a sequence: how hard could it be? (PostgreSQL and Oracle)
One reason I like PostgreSQL so much is that it makes me feel at home: it has a very consistent and coherent interface to its objects. An example of this, is the management of sequences: ALTER SEQUENCE allows you to modify pretty much every detail about a sequence, in particular to restart it from its initial value.
Let’s see this in action:
Let's say you have a script of one million of insert statements with literal values. Of course, this is a bad idea. SQL is language, not a data exchange format. You should have a file to import with COPY, with all values as CSV for example. Or at least, if you are a big fan of INSERT statements, have thousands of row values in it. Or prepare the statement with parameters and call with each parameter.
Moving from Oracle to PostgreSQL has become a popular sport, widely adopted by many who want to free themselves from license costs, hefty support costs and also technical limitations on the Oracle side. The same is true for people moving from MS SQL and other commercial platforms to PostgreSQL. However, my impression is that moving from Oracle to PostgreSQL is by far the most popular route.
Pgpool-II is a feature-rich PostgreSQL cluster management tool. To determine which configuration is best for your database cluster, you need to understand the purpose of the parameters. Since this blog, I will introduce several effective parameters to improve performance.
In this blog, I will explain reserved_connections parameter and how to configure this parameter.
In my previous blog, I briefly walked through how the bulk/batch insertion was done for postgres_fdw in PG14. In this blog, I am going to run some basic tests to compare the performance for before and after the batch insertion was introduced in postgres_fdw, so that we can have a general idea about whether this feature makes any difference.
Upgrading one’s operating system to new major version is an important system maintenance task and it’s usually a good thing. It brings new features, security improvements, access to newer packages and so on. Sometimes it doesn’t go that smoothly, for example the updated system will refuse to start. But upgrading the OS running a Postgres cluster and involving a glibc library version update, or migrating a database to another machine running another OS (and glibc) version poses a little known, but very significant risk…
Decoding of large transactions:
In my recent post on time-weighted averages, I described how my early career as an electrochemist exposed me to the importance of time-weighted averages, which shaped how we built them into TimescaleDB hyperfunctions.
Over the years, many of our PostgreSQL clients have asked whether it makes sense to create indexes before – or after – importing data. Does it make sense to disable indexes when bulk loading data, or is it better to keep them enabled? This is an important question for people involved in data warehousing and large-scale data ingestion. So let’s dig in and figure it out: