Do you know what database indexes are and how they work? Do they seem hard to understand? They don’t have to be. The basic principles you need to know are simple and easy to remember. And developers need to know the basics of indexing: your DBAs can’t save you, because discovering you need an index after you deploy a feature is often too little, too late.

Continuing our series of PostgreSQL Data Types today we’re going to introduce the PostgreSQL array data types.

Arrays can be used to denormalize data and avoid lookup tables. A good rule of thumb for using them that way is that you mostly use the array as a whole, even if you might at times search for elements in the array. Heavier processing is going to be more complex than a lookup table.

PostgreSQL 11 is releasing a small binary called pg_verify_checksums which is able to check if page-level checksums are in a sane state for a cluster which has been cleanly shutdown (this is necessary so as not not face checksum inconsistencies because of torn pages).

Thinking about it, it is not actually complicated to extend the tool in such a way that it enables and disables checksum for an offline cluster, which has been resulted in a tool that I called pg_checksums available here. This can compile with PostgreSQL 11, as it is has been integrated with the new set of routines which can be used if a data folder allows read permissions for a group. Note that it is not complicated to make that work with past versions as well and requires a couple of minutes, but I keep any code in this tree simple of any cross-version checks.

So, pg_checksums gains a couple of options compared to its parent:

• Addition of an –action option, which can be set to “verify” to do what pg_verify_checksums does, “enable” to enable checksums on a cluster, and “disable” to do the reverse operation. “disable” costs nothing as it is just a matter of updating the control file of the cluster. “enable” costs a lot as it needs to update all the page’s checksums and then update the control file. For both things a sync of the data folder is done.
• Addition of a –no-sync option, which disables the final fsync calls in charge of making the switch durable. This can be useful for automated testing environments.

So imagine that you have a cluster with checkums disabled, and stopped cleanly, then here is how to enable checksums:

$ pg_controldata -D $PGDATA | grep checksum
Data page checksum version:           0
$ pg_checksums --action enable -D $PGDATA
Checksum operation completed
Data checksum version: 0
Files operated:  1224
Blocks operated: 3684
Enabling checksums in cluster
$ pg_controldata -D $PGDATA | grep checksum
Data page checksum version:           1

Then let’s look at the state of the page checksummed:

$ pg_checksums --action verify 

Continuing our series of PostgreSQL Data Types today we’re going to introduce the PostgreSQL ranges data type.

Range types are a unique feature of PostgreSQL, managing two dimensions of data in a single column, and allowing advanced processing. The main example is the daterange data type, which stores as a single value a lower and an upper bound of the range as a single value. This allows PostgreSQL to implement a concurrent safe check against overlapping ranges, as we’re going to see in this article.

Introduced in OmniDB 2.6.0, the new OmniDB Console Tab provides an easy and comfortable way to interact with your databases. Users familiar with the psql command line utility will find that Console Tab behaves very similarly. In fact, many of the backslash commands Console Tab provides are present in psql.

For example, \? shows a list with all commands available, its syntax and description. The command \h can be another friend of yours, because it shows a comprehensive help about any PostgreSQL SQL command.

The editor on the bottom of the tab area is full-featured just like the Query Tab editor (it provides syntax highlight and autocomplete with Ctrl-Space). To execute a command, just type it in this editor. If the command is a backslash (starts with \), just type Enter and it will be executed. If it is a regular SQL command, then it can be multi-lined, and you will need to type Ctrl-Q to execute it.

All commands and its output will be logged into the display area, which is textual and read-only, so you can copy and paste its contents somewhere else. You can clear the display area by clicking on the Clear Console button.

All commands also are logged in the global OmniDB query history, and also in a local console history, which you can by clicking in the Command History button.

By clicking in the green check, you can borrow the command and put it into the editor, so you can amend it and execute it. Another comfortable way to navigate through the history is using the shortcuts Ctrl-Up and Ctrl-Down, to quickly paste in the editor the previous and next commands, respectively.

Backslash commands such as \dt, \d+, \x and \timing are very useful when dealing with databases every day. The console tab will also show any errors and the results of any SQL command you type in a pretty way. Try it out!

In the near future we will add more commands such as \copy and also the ability for the user to create custom commands. Stay tuned!

Adding a column with a default value requires a full table rewrite, and therefore it is often suggested to avoid the default value. However, it is possible to add the default value without having PostgreSQL perform the full table rewrite.

Adding a column via ALTER TABLE ADD COLUMN

In order to demonstrate what does PostgreSQL when a new column is added, consider the following simple table to begin with:

> CREATE TABLE foo( i int ); > INSERT INTO foo(i) SELECT v FROM generate_series(1, 100000) v; 

Suppose it is required to add a column, with a default value, to the table. When an ALTER TABLE ADD COLUMN is issued, and a default value is provided, PostgreSQL performs a full update of the whole table, that is all the 100k tuples are updated immediatly:

 > ALTER TABLE foo ADD COLUMN c char(1) DEFAULT 'A'; ALTER TABLE Time: 180.997 ms > SELECT distinct( c ) FROM foo; c --- A (1 row) 

As readers can see, the c column has been added and all the tuples have been updated to the default value.

When the number of tuples is really high, performing such ADD COLUMN will result in a very huge database activity. Therefore, it is often suggested to perform the ADD COLUMN without a default value, so to get the...

Continuing our series of PostgreSQL Data Types today we’re going to introduce network address types.

PostgreSQL includes support for both cidr, inet, and macaddr data types. Again, those types are bundled with indexing support and advanced functions and operator support.

One of the attractive things of my job is this… Just a bit more often than every now and then, you get the opportunity to get out and meet people to talk about Postgres. I don’t mean the kind of talk I do every day, which has more of a commercial touch to it. – Don’t get me wrong, that is very important too! – But I mean, really talk about PostgreSQL, be part of the community and help spread the understanding of what open source database technology can do for companies. Running implementations, either small or large, trivial or mission critical…

This past week was one of those weeks.

I got to travel through Germany together with Mr. Bruce Momjian himself. Bruce is the one of the most established and senior community leaders for Postgres. Bruce is also my colleague and I would like to think I may consider him my friend. My employer, EnterpriseDB, gives us the opportunity to do this. To be an integral part of the PostgreSQL community, contribute, help expand the fame of Postgres, no strings attached. Support the success of the 30 to 40,000 engineers creating this most advanced open source RDBMS.

The week started with travel, and I got to Frankfurt. Frankfurt will be the proving ground for the idea of a pop-up meet-up. Not an EDB-marketing event or somewhere where we sell EnterpriseDB services, but allow anyone just to discuss PostgreSQL.
We will be in a city, in a public place, answering questions, discussion things or just relax with some coffee. Purpose is to show what the PostgreSQL community is all about, to anyone interested!

The first day in Frankfurt, we spent at the 25hrs hotel. We had some very interesting discussions on:

• Postgres vs. Oracle community
• Changing role of DBA:
  • The demise of the Oracle DBA
  • RDBMS DBA not so much
• Risk management
• “Data scientist”
• Significance of relational growing again

In the afternoon we took the Train to Munich, which was a quick and smooth experience. Munich would be the staging ground for a breakfast meeting, or a lunch… or just say hi.

Bruce and I spend the day disc

Continuing our series of PostgreSQL Data Types today we’re going to introduce date and time based processing functions.

Once the application’s data, or rather the user data is properly stored as timestamp with time zone, PostgreSQL allows implementing all the processing you need to. In this article we dive into a set of examples to help you get started with time based processing in your database. Can we boost your reporting skills?

The local Linux Users’ Group ConoscereLinux is delivering a six-part course on PostgreSQL 10. Guess who’s lecturing…

PostgreSQL 10 short course in Modena

I’m doing a short course on PostgreSQL, with particular regard to the 10 edition, in Modena. Thanks to the local Linux Users’ Group (LUG) /ConoscereLinux/, that provided all the infrastructure for the course, I wiil introduce attendees at basic SQL concepts and how PostgreSQL works.

The course schedule is available at the official course page, and the course will be based on 6 lessons (2 already done). Attendees can come with their own laptops, and lessons will be “live”: I will show concepts while explaining on my own laptop running PostgreSQL 10.1.

All the slides will be available for free on the course page, and are based on my work available on my github repository. The course will take place every week on Tuesday evening. So far the attendees are very interested in PostgreSQL and its technology, and are curious about evaluating all its features as a

I really have to thank the ConoscereLinux LUG, with particular regard to Luca and Massimiliano, for both giving me such chance and, most notably, for waiting me to be ready after my last eye-surgery, and driving me home!

Continuing our series of PostgreSQL Data Types today we’re going to introduce date, timestamp, and interval data types.

PostgreSQL implementation of the calendar is very good, and we’re going to show some mice example about how confusing this matter is. The time zone notion in particular is mainly a political tool these days, and it makes no sense on an engineering principle: there’s no way to solve time zone problems from first hand principles!

Why should I get rid of unused indexes?

Everybody knows that a database index is a good thing because it can speed up SQL queries. But this does not come for free.

The disadvantages of indexes are:

• Indexes use up space. It is not unusual for database indexes to use as much storage space as the data themselves. And the kind of reliable, fast storage you want for a database is not necessarily cheap.
  The space used up by indexes also increases the size and duration of physical backups.
• Indexes slow down data modification. Whenever you INSERT into or DELETE from a table, all indexes have to be modified, in addition to the table itself (the “heap”).
  And it is much more expensive to modify the complicated data structure of an index than the heap itself, which has its name precisely because it is basically an unordered “pile” of data (and as everybody knows, maintaining order is more work than having a mess). Modifying an indexed table can easily be an order of magnitude more expensive than modifying an unindexed table.
• Indexes prevent HOT updates. Because of the architecture of PostgreSQL, every UPDATE causes a new row version (“tuple”) to be written, and that causes a new entry in every index on the table.
  This behavior has been dubbed “write amplification” and has drawn a lot of fire. This undesirable effect can be avoided if a) the new tuple fits into the same table block as the old one and b) no indexed row is modified. Then PostgreSQL creates the new tuple as a “Heap Only Tuple” (hence HOT), which is much more efficient and also reduces the work VACUUM has to do.

The many uses of indexes

Now we know that we don’t want unnecessary indexes. The problem is that indexes serve so many purposes that it is difficult to determine if a certain index is needed or not.

Here is a list of all benefits of indexes in PostgreSQL:

1. Indexes can speed up queries that use indexed columns (or expressions) in the WHERE clause.
   Everybody knows that one!
   The traditional B-tree index supports the <, <=, =, >= and >

Today a friend of mine asked me for a trouble between PostgreSQL 10 and Python. Since I’m not a pythonist, my quick answer was that PostgreSQL 10 does support Python 3. But it turned out it was not so simple (at least, not so simple as to get Perl 5 working!).

PostgreSQL 10, Python 3 and FreeBSD (and Ubuntu)

tl;dr it can be done (of course!)

postgres=# SELECT pyv(); pyv ---------------------------------------------------------------------------- 3.6.4 (default, Jan 2 2018, 01:25:35) + [GCC 4.2.1 Compatible FreeBSD Clang 4.0.0 (tags/RELEASE_400/final 297347)] postgres=# SELECT proname, prolang, prosrc FROM pg_proc WHERE proname = 'pyv'; -[ RECORD 1 ]----------------- proname | pyv prolang | 16387 prosrc | + | import sys + | return sys.version+ | 

My default environment for running PostgreSQL is FreeBSD, so in order to get plpython working I jumped to the console and installed the package postgresql10-plpython-10.3, thinking of course that FreeBSD would do the right thing. Unluckily it did not!

Creating a plpython3u language did not succeed, and the problem was that the above package installed only the libpython2.so under the lib directory (e.g., /usr/local/lib/postgresql). I then tried installing the port of the very same name, but again it was installing...

Continuing our series of PostgreSQL Data Types today we’re going to introduce some of the PostgreSQL text processing functions.

There’s a very rich set of PostgreSQL functions to process text — you can find them all in the string functions and operators documentation chapter — with functions such as overlay(), substring(), position() or trim(). Or aggregates such as string_agg(). There are also regular expression functions, including the very powerful regexp_split_to_table(). In this article we see practical example putting them in practice.

• Amazon
• Pivotal
• 2ndQuadrant
• Microsoft
• Timescale
• HighGo
• Data Dog
• Fivetran
• BlueTalon
• Goldman Sachs
• forhims
• Immuta

The feature freeze for the PostgreSQL 11 release is now upon us. During the last few days my colleague Álvaro Herrera pushed two changes into the development branch of PostgreSQL:

1. Faster Partition Pruning
2. Partition Pruning at Execution Time

These patches aim to improve the performance and usability of the declarative table partitioning feature (added in PostgreSQL 10). Amit Langote wrote the first of these two patches, with some assistance from me. I’m the author of the second patch. This one is based on an original patch by Beena Emerson.

Background

Internally in PostgreSQL, a partitioned table is made up from a series of individual tables. These tables are all grouped under one common parent partitioned table. Queries being run against the partitioned table need the results of each individual table to be “concatenated” before the final result is produced. Many queries, especially OLTP type queries, will only require data from a small number of partitions (perhaps just 1!).   In order to save PostgreSQL from having to needlessly trawl through all partitions for data that might not even be there, PostgreSQL tries to eliminate partitions that won’t contain any needed records.
In PostgreSQL 10 this elimination took place via the “constraint_exclusion” mechanism, which was a linear algorithm that required looking at each partition’s metadata one-by-one to check if the partition matched the queries WHERE clause.

Faster Partition Pruning

In PostgreSQL 11 this elimination of unneeded partitions (aka partition pruning) is no longer an exhaustive linear search.  A binary search quickly identifies matching LIST and RANGE partitions. A hashing function finds the matching partitions for HASH partitioned tables, which are new in PG11.

This also makes improvements so that it’s able to prune partitions in a few more cases than was previously possible.

Partition Pruning at Execution Time

Required partitions are normally identified only by the query planner, however, it’s only able to perform this identification process us