PostgreSQL

Postgres vs. Oracle access paths II – Index Only Scan

By Franck Pachot . In the previous post I've explained a sequential scan by accident: my query needed only one column which was indexed, and I expected to read the index rather than the table. And(…)

Postgres vs. Oracle access paths I – Seq Scan

By Franck Pachot . Here is the first test I've done for my Postgres vs. Oracle access paths series and the first query did a sequential scan. It illustrates the first constant you find in the(…)

Postgres unique constraint

By Franck Pachot . I'll start a series on Postgres vs. Oracle access paths because I know Oracle and I learn Postgres. While preparing it, I came upon some surprises because I'm so used to Oracle(…)

Postgres vs. Oracle access paths – intro

By Franck Pachot . This is the start of a series on PostgreSQL execution plans, access path, join methods, hints and execution statistics. The approach will compare Postgres and Oracle. It is not a comparison to see which one is(…)

PostgreSQL on Cygwin

By Franck Pachot . I run my laptop with Windows 10 for office programs, and VirtualBox machines with Linux for the big stuff (Oracle databases). I have also Cygwin installed on Windows for GNU programs. I wanted to quickly install(…)

Postgres vs. Oracle access paths VII – Bitmap Index Scan

By Franck Pachot . In the previous post we have seen the Index Scan, where a where clause condition uses the index structure to find rows and then fetches remaining columns from the table. The case(…)

Re-assigning all objects from on role to another in PostgreSQL

From time to time it might be required to move objects (tables, indexes, whatever) from one user to another user in a database system. You could do that by dumping all the objects with pg_dump and then(…)

Setting up default parameters for roles in PostgreSQL

As you might know you can set various parameters on the session level in PostgreSQL by using the "set" command: postgres=# h set Command: SET Description: change a run-time parameter Syntax: SET [ SESSION | LOCAL ] configuration_parameter { TO(…)

Can I do it with PostgreSQL? – 17 – Identifying a blocking session

One single blocking session in a database can completely halt your application so identifying which session is blocking other sessions is a task you must be able to perform quickly. In Oracle you can query v$session for(…)

Can I do it with PostgreSQL? – 16 – DDL triggers

A question I received recently from a customer: Oracle gives you the possibility to create DDL triggers. DDL triggers fire (as the name implies) when DDL events occur. Can we do that in PostgreSQL? Yes, this feature(…)