Infrastructure at your Service

Category

Database Administration & Monitoring

Daniel Westermann

PostgreSQL partitioning (2): Range partitioning

By | Database Administration & Monitoring | No Comments

Now that the data set is ready we will look at the first partitioning strategy: Range partitioning. Usually range partitioning is used to partition a table by days, months or years although you can partition by other data types as well. Time/date based range partitioning is probably the most common use case so we will partition our traffic violation data by year and later on sub partition that month.

Read More
Daniel Westermann

PostgreSQL partitioning (1): Preparing the data set

By | Database Administration & Monitoring | One Comment

This is the start of a series about partitioning in PostgreSQL. For this and the following posts I will use PostgreSQL 12 (which currently is in beta) so some stuff might not work if you are on PostgreSQL 11 or even on PostgreSQL 10 when declarative partitioning was introduced. This very first post is only about preparing the data set and I decided to separate this one from the next post because it shows various…

Read More
Daniel Westermann

PostgreSQL 12: New partition reporting functions

By | Database Administration & Monitoring | 2 Comments

PostgreSQL 10 introduced declarative partitioning (with some limitations), PostgreSQL 11 improved that a lot (Updating the partition key now works in PostgreSQL 11, Insert…on conflict with partitions finally works in PostgreSQL 11, Local partitioned indexes in PostgreSQL 11, Hash Partitioning in PostgreSQL 11) and PostgreSQL 12 goes even further. Beside that foreign keys can now reference partitioned tables three new functions made it into PostgreSQL 12 that will give you information about your partitioned tables.

Read More
Daniel Westermann

PostgreSQL 12: Control when generic plans are used

By | Database Administration & Monitoring | No Comments

When you are using prepared statements in PostgreSQL you might get a custom or a generic plan. Custom plans come with overhead of re-planning while generic plans avoid re-planning of the statement. A soon as you get a generic plan that plan will be used for the lifetime of your session and there are situations when you do not want to see this as a generic plan might be more expensive than a custom plan….

Read More
Daniel Westermann

Securely store passwords in PostgreSQL

By | Database Administration & Monitoring | One Comment

Every application somehow needs to deal with passwords. Some use external authentication methods such as ldap, others us the the framework the database already provides and create users and roles. But it is also not uncommon that applications implement their own concept for managing users. If an application does this it should be done the right way and passwords should never be stored in plain text in the database. PostgreSQL comes with a handy extension…

Read More
Daniel Westermann

Can you start two (or more) PostgreSQL instances against the same data directory?

By | Database Administration & Monitoring | No Comments

As PostgreSQL does not know the concept of running multiple instances against the same files on disk (e.g. like Oracle RAC) it should not be possible to start two or more instances against the same data directory. If that would work the result can only be corruption. In this post we will look at how PostgreSQL is detecting that and what mechanism are build in to avoid the situation of having multiple instances working against…

Read More
Alain Lacour

APEX Connect 2019 – Day 3

By | Application integration & Middleware, Database Administration & Monitoring | No Comments

For the last conference day, after the Keynote about “JavaScript, Why Should I Care?” by Dan McGhan , I decided to attend some JavaScript learning sessions to improve myself and presentations on following topics: – How to hack your APEX App… (only for testing) – What you need to know about APEX validations I also got the chance to have a 1:1 talk with Anthony Rayner to expose some wishes about APEX and talk about…

Read More