10 Postgres Tips developers should master in 2016

"Editor's note" as an open source object-relational database, Postgres has been loved by many developers. Recently, Postgres officially released version 9.5, which has been a lot of repair and functional improvements. This article will share 10 Postgres usage tips, which are designed to enable developers to use the database more flexibly and efficiently.

During the holidays, many people will choose to read some new books or learn some new techniques to enrich themselves. The following author will recommend some Postgres skills and skills to everyone, these techniques will help you to use Postgres more flexibly and conveniently. If you feel that these techniques will help you, you can opt to subscribe to Postgres Weekly, which will release some Postgres latest information and technical stuff this week.

1.ctes--common Table Expressions

The CTE allows you to do some great things, like recursive queries, and even with some of the simplest statement operations, CET will perform well. A CTE can be thought of as a temporary result set defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and is only valid for the duration of the query. Unlike a derived table, a CTE can be self-referenced and can be referenced more than once in the same query. This makes it easier for developers to create readable queries.

When creating SQL statements, developers tend to have a lot of lines, some even more than hundreds of lines, and by using 4-5 CETs, the statement will be shortened a lot, so it is easy to improve the readability of the sentence, especially for new people.

2. Install a. PSQLRC

If BASHRC, VIMRC and other files are installed, why not do the same for Postgres? The following settings are great, you might want to try:

To get a better format by default;

Use \pset null¤ to make null more visualized;

The default setting \timing on to display the SQL execution time;

Custom hint \set PROMPT1 '%[3[33;1m%]%x%[3[0m%]%[3[1m%]%/%[3[0m%]%r%# ';

Save your favorite run statements by name.

Here is the author's PSQLRC settings:

\set QUIET 1 \pset null ' ¤ '--Customize prompts \set PROMPT1 '%[3[1m%][%/] # ' \set PROMPT2 ' ... # '--Show how long EAC H query takes to execute \timing--Available output format \x auto \set verbosity verbose \set histfile ~/.psql_ history-:D bname \set histcontrol ignoredups \set comp_keyword_case Upper \unset QUIET

3. Use PGSTATstatements to see where the index needs to be indexed

Pg_stat_statements is probably the most valuable tool for developers to improve database performance. Once enabled (and extension PGSTATstatements), it automatically records all query records for the database and the time they take. This makes it easy to optimize query statements and improve performance.

SELECT (TOTAL_TIME/1000/60) as Total_minutes, (Total_time/calls) as Average_time, query from Pg_stat_statements ORDER by 1 DESC LIMIT 100;

Of course, there will be some performance costs, but compared to the performance gains are negligible. You can read more about Postgres performance in this article.

4. ETL is a bit slow, with FDWs

If you have a large number of microservices or different applications, you may need a lot of different databases to support them. The default is to create some data warehouses and connect through ETL, but this is sometimes too heavy. At this point, you just need to centralize the database one at a time, or in a few cases, the external data wrapper allows you to query across multiple databases, such as Postgres to Postgres, or Postgres to Mongo or Redis databases.

5. Array and Array_agg

When developing an application, it is rarely arrays at all, but in the database as well. Arrays can be seen as another type of data in Postgres and has some killer apps, such as the post tag.

However, even if you don't use arrays as a data type, you often need to summarize some data like an array, separated by commas. Similar to the following, you can easily summarize the user list:

SELECT Users.email, Array_to_string (Array_agg (projects.name), ', ')) as projects from projects, tasks, Users WHERE project S.id = tasks.project_id and Tasks.due_at > Tasks.completed_at and Tasks.due_at > Now () and users.id = Projects.user_ ID GROUP by Users.email 6. Use materialized views carefully

You may not be familiar with materialized views (materialized view), materialized is a database pair image that includes a query result. Therefore, it is a materialized or basic snapshot version of some queries or "view". In the first materialized version, a common request is established in Postgres, but the whole is not available. That's because when you lock a transaction, it's possible to block some other reads and activities.

It's much better now, but still lacks some out-of-the-box tools to refresh. This also means that you must install some scheduling tasks or cron jobs to periodically refresh materialized views. If you are currently developing reports or BI applications, you still need to use materialized views. Their availability is increasing, so Postgres already knows how to automate refreshing them.

7. Window functions

Window functions (Windows fuction) may still be more complex and difficult to understand in SQL. In short, they will let you sort a query result, and then do a row to the Ling line of calculations, if there is no SQL PL, these things will be difficult to do. However, you can do something very simple, such as ranking, sorting the results based on certain values, and more complicated, such as calculating the chain growth data.

8. A simpler way to pivot tables

In Postgres, Table_func is typically used as a reference for calculating a PivotTable report. Unfortunately, this is very difficult to use, and the more basic usage is used with the original SQL. Improvements have been made in Postgres 9.5, which makes it much easier to use. But before you summarize each condition, the result is either false or true, and the final summation is simpler reasoning:

Select Date, sum (case if type = ' OSX ' then Val end) as X, sum (case if type = ' windows ' then Val end) as Windows, sum (case if type = ' Linux ' then Val end) as Linux from Daily_visits_per_os Group by date order by date limit 4;

You can go to Dimitri Fontaine's blog to see specific examples.

9.PostGIS

PostGIS can be said to be one of the best in all GIS databases. In fact, all the Postgres standards that developers get will make it more powerful-one of the best examples is the GiST index from Postgres in recent years, which gives PostGIS a great performance boost. If you're doing something about geospatial data right now, and you need tools that are better than earth_distance extensions, then PostGIS is your best choice.

10.JSONB

Starting with Postgres 9.2, each version of Postgres has JSON in it, with each new version being upgraded and being progressively perfected into a more perfect library. In the latest release of version 9.5, the output of jsonb in Psql is also more readable.

Original address: http://www.craigkerstiens.com/2015/12/29/my-postgres-top-10-for-2016/

This article is the domestic itom industry leader ONEAPM engineer compiled finishing. We are committed to helping enterprise users provide full-stack performance management and IT operations management services, through a probe to complete log analysis, security protection, APM infrastructure components monitoring, integrated alarm and Big data analysis and other functions. To read more technical articles, please visit the OneAPM Official technology blog

Transferred from: http://news.oneapm.com/postgres/

10 Postgres Tips developers should master in 2016