Postgres

PostgreSQL operator examples

1. Use NULLS FIRST and NULLS LAST options to explicitly specify the order of NULL with other non-null values.

Tags:

SELECT statement with concatenation operator

Tags:

MySQL vs PostgreSQL -- Choose the Right Database for Your Project

The choice of a database management system is usually an afterthought when starting a new project, especially on the Web. Most frameworks come with some object-relational mapping tool (ORM) which more or less hides the differences between the different platforms and makes them all equally slow. Using the default option (MySQL in most cases) is rarely wrong, but it’s worth considering. Don’t fall into the trap of familiarity and comfort – a good developer must always make informed decisions among the different options, their benefits and drawbacks.

Useful queries for postgresql

detect which tables might need an index.

Tags:

Database Monitoring and Performance

PostgreSQL provides a good number of catalog tables, views, and extensions to monitor the
instance accurately.

Checking active sessions

Tags:

Turning device-related parameters

PostgreSQL uses several memory components for each unique usage. That is, it uses
dedicated memory areas for transactions, sort/join operations, maintenance operations, and
so on. If the configured memory component doesn't fit the usage of the live application,
then we may hit a performance issue, where PostgreSQL tries for more I/O.

Tags:

Tuning logging-related parameters

The following are the logging-related parameters that usually require tuning:
log_line_prefix
log_statement
log_min_duration_statement

log_line_prefix: Usually the default value related to this parameter is empty and it is
not desirable. A good way to put this in context would be to use the following format:

Tags:

Tuning query-related parameters

The following are the query planning related parameters that usually require tuning:
random_page_cost
seq_page_cost
effective_cache_size
work_mem
constraint_exclusion
These parameters can be set in the postgresql.conf configuration file.

Tags:

Tuning connection-related parameters

These parameters can be set to appropriate values in the postgresql.conf file and these
parameter can be set at server start. The changes made to these parameters will only come
into effect once the PostgreSQL server is restarted, either using the restart mode of the
pg_ctl command or using the stop mode and then followed by the start mode of the
pg_ctl command. This can be done as follows:

Tags:

Avoiding typical mistakes and explicit locking

In this case, there will be either a duplicate key violation or two identical entries. Neither
variation of the problem is all that appealing.

Transaction 1
BEGIN;
SELECT max(id) FROM product;
User will see 17;
User will decide to use 18;
INSERT INTO product ... VALUES (18,
...)
COMMIT;

Transaction 2
BEGIN;
SELECT max(id) FROM product;
User will see 17;
User will decide to use 18;
INSERT INTO product ... VALUES (18,
...)
COMMIT;

Tags:

Subscribe to RSS - Postgres