Postgres & Sequences

Many developers already know about the change from int to serial in Postgres. Serial is simpler because it automatically makes the column not null and links it to a sequence. The sequence is created with the following naming convention:

table_name_column_name_seq

The Postgres serial type always has a start with value of 1. Though, you can alter it immediately after creating the table.

You can create a serial surrogate key column like this:

CREATE TABLE new_hire
 (more...)

Postgres 11 Video DB

Installing PostgreSQL 11 on Fedora, Version 30, requires an update to my previous instructions to create a sandboxed user. A sandboxed user can only access a non-data dictionary database with a password. In the real world, rather than a personal test instance you would configure users to include aspects of networking. However, this post is only showing you how to connect from the local server.

This post builds on my PostgreSQL Installation blog post and (more...)

PostgreSQL on Fedora 30

Installing PostreSQL 11 on Fedora 30 wasn’t straight forward but there were some instructions that helped. The first step requires you to update the yum repository, like this as the root user:

rpm -Uvh https://yum.postgresql.org/11/fedora/fedora-30-x86_64/pgdg-fedora-repo-latest.noarch.rpm

Then, you install the PostgreSQL with this command as the root user:

dnf install postgresql11-server

After installing the PostreSQL Server I got a few errors with the symbolic links failing to resolve in the log files. (more...)

Seattle PostgreSQL Meetup This Thursday: New Location

I’m looking forward to the Seattle PostgreSQL User Group meetup this Thursday (June 20, 2019) at 5:30pm! We’re going to get an early sneak peek at what’s coming later this year in PostgreSQL’s next major release. The current velocity of development in this open source community is staggering and this is an exciting and valuable opportunity to keep up with where PostgreSQL is going next.

One thing that’s a bit unusual about this meetup is (more...)

Identifying PostgreSQL Bloat and fixing it without downtime

Original Post can be viewed at Identifying PostgreSQL Bloat and fixing it without downtime

MVCC (Multi-Version Concurrency Control) feature allows databases to provide concurrent access to data. This allows each SQL statement to see a snapshot of data as it was some time ago, regardless of the current state of the underlying data. This prevents statements from viewing inconsistent data produced by concurrent transactions performing updates on the same…

AskDba.org Weblog

named tuple to JSON – Python

In pgdb – PostgreSQL DB API, the cursor which is used to manage the context of a fetch operation returns list of named tuples. These named tuples contain field names same as the column names of the database query.

An example of a row from the list of named tuples –


Row(log_time=datetime.datetime(2019, 3, 20, 5, 41, 29, 888000, tzinfo=), user_name='admin', connection_from='72.20.208.64:21132', command_tag='INSERT', message='AUDIT: SESSION,1,1,WRITE,INSERT,TABLE,user.demodml,"insert into user.demodml (id) values (1),(2),(3),(4),(5),(6),(7),(8),(9),(11);",',  (more...)

PostgresConf 2019 Summary

PostgresConf 2019 in New York City is a wrap! Now that I’ve had a few days to recover a little brain capacity, I think I’m ready to attempt a summary. I love conferences for the learning opportunities… inside of sessions and also outside of them. Being the largest PostgreSQL-centered conference in the United States, PostgresConf offers unique opportunities to:

  1. Watch seasoned technical presenters go deep on PostgreSQL internals.
  2. Hear serious users present the latest about (more...)

PostgresConf 2019 Training Days

It feels like PostgresConf in New York is in full swing, even though the main tracks haven’t even started yet!

(Oh, and by the way, as of this morning I heard there are still day-passes available for those who haven’t yet registered for the conference… and then you can come hear a great session about Wait Events in PostgreSQL this Thursday at 4:20pm!)

The first two days of PostgresConf are summits, tutorials and training (more...)

Speeding up Initial data load for Oracle to PostgreSQL using Goldengate and copy command

Original Post can be viewed at Speeding up Initial data load for Oracle to PostgreSQL using Goldengate and copy command

Oracle Goldengate supports Oracle to PostgreSQL migrations by supporting PostgreSQL as a target database, though reverse migration i.e PostgreSQL to Oracle is not supported. One of the key aspect of these database migrations is initial data load phase where full tables data have to copied to the target datastore. This can be a time (more...)

PostgreSQL – LWLock:multixact_offset

On one of the PostgreSQL db , the active sessions were waiting on IO:SLRURead and LWLock:multixact_offset causing application latency.

Multioffset

As per PostgreSQL doc, SLRURead is waiting for a read of an SLRU page and multixact_offset is waiting for I/O on a multixact offset buffer.

Diagnosing the issue took some time so cut to the chase

1. Identified the SQLs, relation (and its size) for which these wait events were high. These table had ~1500 DMLs (more...)

Step by Step Installation of PostgreSQL 11 on Oracle Linux 7

This is quick spit-out of commands, I used on my test virtual machine to install PostgreSQL 11 on Oracle Enterprise Linux 7.



[root@docker ~]# curl -O https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-7-x86_64/pgdg-oraclelinux11-11-2.noarch.rpm
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100  4952  100  4952    (more...)

PostgreSQL – CPU Utilization and Index

One of the Production Aurora PostgreSQL instance running on db.r4.16xlarge instance (64 vCPU and 488 GB ) was reporting high CPU Utilization spiking upto 100%.

Screen Shot 2018-12-09 at 11.14.49 AM

With such issues, one of the first thing is to look for the SQLs with high buffers shared hit. I have built a small tool called pgsnap which is something similar to AWR respostory in Oracle maintaining the SQL stat history.  So, with pg_stat_statements and hist_pg_stat_statements(that’s what (more...)

Why document databases are old news…

We’re going to store data the way it’s stored naturally in the brain.

This is a phrase being heard more often today. This blog post is inspired by a short rant by Babak Tourani (@2ndhalf_oracle) and myself had on Twitter today.

How cool is that!!

This phrase is used by companies like MongoDB or Graph Database vendors to explain why they choose to store information / data in an unstructured format. It is (more...)

Column And Table Redefinition With Minimal Locking

TLDR: Note to future self… (1) Read this before you modify a table on a live PostgreSQL database. If you do it wrong then your app might totally hang. There is a right way to do it which avoids that. (2) Especially remember the lock_timeout step. Many blog posts around the ‘net are missing this and it’s very important.

Yesterday I was talking to some PostgreSQL users (who, BTW, were doing rather large-scale cool stuff (more...)

List of Bank Holidays For England in SQL Format

First off, create a table to store the bank holiday values. You may need to adjust this slightly depending on your SQL server technology being used (this was tested on MySQL Server).  [crayon-5bb4e0b3b1755979540269/] Insert the bank holiday values below. This table is currently for 2012 up to 2019 for England and Wales.  [crayon-5bb4e0b3b175d512369946/] For a

How to change the listening port for PostgreSQL Database

The default TCP port for PostgreSQL is usually 5432, however this can easily be changed in the postgresql.conf configuration file, which is the main configuration file for the database server. In addition to a TCP listening port, PostgreSQL will also a local socket if the server is running in a Linux/ Unix environment. A local socket is

Postgres in the Enterprise, Real World Reasons for adoption

The lead-in

Friend and colleague Bruce Momjian already shared in his presentation “Will Postgres live forever?” (https://attendee.gotowebinar.com/register/8793191803818201345) an overview of reasons why companies adopt open source software (OSS) in general. This overview was based on a survey done by Black Duck Software in 2016 (https://www.slideshare.net/mobile/blackducksoftware/2016-future-of-open-source-survey-results). The survey ranks databases on the second place of technologies that companies are moving towards OSS in.

In this series of (more...)

Swiss pgday 2018

The cool thing about zooming out… is that your world appears to get bigger.
Being personally now no longer bound to Oracle, having the opportunity to work with PostgreSQL, also gives the opportunity to go new places and explore new possibilities. One of the cooler things is: participating in Postgres conferences.

Conference vibe

Where Oracle conferences, although having some deep technical aspects, tend to lean towards the business aspects of technology, especially today with Cloud (more...)

This Week in PostgreSQL – May 31

Since last October I’ve been periodically writing up summaries of interesting content I see on the internet related to PostgreSQL (generally blog posts). My original motivation was just to learn more about PostgreSQL – but I’ve started sharing them with a few colleagues and received positive feedback.  Thought I’d try posting one of these digests here on the Ardent blog – who knows, maybe a few old readers will find it interesting? Here’s the (more...)

Beware (Sort-Of) Ambiguous Column Names In Sub-Selects

This morning I received an UPDATE statement from a developer that I was testing. It ran without errors but then I saw that it updated 5 rows when it should have only updated 3. The reason gave me a little shock so I whipped up a simple test-case to reproduce the problem.

First we create two tables:

CREATE TABLE foo (
    id int
    , name varchar(30)
);
CREATE TABLE
CREATE TABLE (more...)