PostgreSQL의 숨겨진 기능

PostgreSQL의 숨겨진 기능

---

아직 게시되지 않은 것이 놀랍습니다. Postgres에서 알고있는 흥미로운 트릭이 있습니까? 모호한 구성 옵션과 확장 / 성능 트릭은 특히 환영합니다.

해당 MySQL 스레드 에 대한 9 개의 댓글을 이길 수 있다고 확신 합니다. :)

---

postgres는 MySQL보다 훨씬 더 정상적이기 때문에보고 할 "트릭"이 많지 않습니다. ;-)

매뉴얼은 멋진가 성능 팁.

기억해야 할 몇 가지 다른 성능 관련 사항 :

• autovacuum이 켜져 있는지 확인
• postgres.conf (유효 캐시 크기, 공유 버퍼, 작업 메모리 ... 조정할 많은 옵션)를 살펴 보았는지 확인하십시오.
• pgpool 또는 pgbouncer 를 사용 하여 "실제"데이터베이스 연결을 최소로 유지합니다.
• EXPLAIN 및 EXPLAIN ANALYZE가 어떻게 작동 하는지 알아보십시오 . 출력을 읽는 법을 배웁니다.
• CLUSTER 는 인덱스에 따라 디스크의 데이터를 정렬합니다. 큰 (대부분) 읽기 전용 테이블의 성능을 크게 향상시킬 수 있습니다. 클러스터링은 일회성 작업입니다. 이후에 테이블이 업데이트 될 때 변경 사항이 클러스터링되지 않습니다.

다음은 구성 또는 성능과 관련이없는 유용한 몇 가지 사항입니다.

현재 무슨 일이 일어나고 있는지 확인하려면 :

select * from pg_stat_activity;

기타 기능 검색 :

select * from pg_proc WHERE proname ~* '^pg_.*'

데이터베이스 크기 찾기 :

select pg_database_size('postgres');
select pg_size_pretty(pg_database_size('postgres'));

모든 데이터베이스의 크기 찾기 :

select datname, pg_size_pretty(pg_database_size(datname)) as size
  from pg_database;

테이블 및 인덱스 크기 찾기 :

select pg_size_pretty(pg_relation_size('public.customer'));

또는 모든 테이블과 인덱스를 나열하려면 (아마도보기를 더 쉽게 만들 수 있음) :

select schemaname, relname,
    pg_size_pretty(pg_relation_size(schemaname || '.' || relname)) as size
  from (select schemaname, relname, 'table' as type
          from pg_stat_user_tables
        union all
        select schemaname, relname, 'index' as type
          from pg_stat_user_indexes) x;

아, 그리고 트랜잭션을 중첩하고 부분 트랜잭션을 롤백 할 수 있습니다 ++

test=# begin;
BEGIN
test=# select count(*) from customer where name='test';
 count 
-------
     0
(1 row)
test=# insert into customer (name) values ('test');
INSERT 0 1
test=# savepoint foo;
SAVEPOINT
test=# update customer set name='john';
UPDATE 3
test=# rollback to savepoint foo;
ROLLBACK
test=# commit;
COMMIT
test=# select count(*) from customer where name='test';
 count 
-------
     1
(1 row)

---

(물론 적절한 인덱스를 설정하고 사용하는 것을 제외하고) postgresql이 훨씬 더 잘 수행되도록 하는 가장 쉬운 방법 은 작업 할 RAM을 더 많이 제공하는 것입니다 (아직 수행하지 않은 경우). 대부분의 기본 설치에서 shared_buffers 의 값 은 너무 낮습니다 (제 생각에는). 당신은 설정할 수 있습니다

공유 버퍼

postgresql.conf에서. 이 숫자를 128로 나누어 postgres가 요구할 수있는 메모리 양 (MB)의 근사치를 구합니다. 충분하면 postgresql이 날아갈 것입니다. postgresql을 다시 시작하는 것을 잊지 마십시오.

Linux 시스템에서 postgresql이 다시 시작되지 않으면 kernel.shmmax 제한에 도달했을 것입니다. 더 높게 설정하십시오.

sysctl -w kernel.shmmax=xxxx

부팅 사이에이를 유지하려면 kernel.shmmax 항목을 /etc/sysctl.conf에 추가하십시오.

여기에서 Postgresql 트릭 전체를 찾을 수 있습니다 .

• http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks

---

Postgres는 INTERVAL 지원 덕분에 매우 강력한 날짜 시간 처리 기능을 가지고 있습니다.

예를 들면 :

select NOW(), NOW() + '1 hour';
              now              |           ?column?            
-------------------------------+-------------------------------
 2009-04-18 01:37:49.116614+00 | 2009-04-18 02:37:49.116614+00
(1 row)



select current_date ,(current_date +  interval '1 year')::date;
    date             |  date            
---------------------+----------------
 2014-10-17          | 2015-10-17
(1 row)

많은 문자열을 INTERVAL 유형으로 캐스트 할 수 있습니다.

---

부

시작하겠습니다. SQLite에서 Postgres로 전환 할 때마다 일반적으로 매우 큰 데이터 세트가 있습니다. 핵심은 INSERTS를 수행하는 대신 COPY FROM을 사용하여 테이블을로드하는 것입니다. 문서 참조 :

http://www.postgresql.org/docs/8.1/static/sql-copy.html

다음 예제는 필드 구분 기호로 세로 막대 (|)를 사용하여 클라이언트에 테이블을 복사합니다.

COPY country TO STDOUT WITH DELIMITER '|';

파일의 데이터를 국가 테이블로 복사하려면 :

COPY country FROM '/usr1/proj/bray/sql/country_data';

참조 : sqlite3에서 더 빠른 대량 삽입?

---

• 내가 가장 좋아하는 것은 generate_series: 마침내 더미 행 집합을 생성하는 깨끗한 방법입니다.

• LIMIT하위 쿼리 의 절 에서 상관 값을 사용하는 기능 :

  SELECT  (
          SELECT  exp_word
          FROM    mytable
          OFFSET id
          LIMIT 1
          )
  FROM    othertable
  

• 사용자 지정 집계에서 여러 매개 변수를 사용하려는 경우 (문서에서 다루지 않음) : 예제 는 내 블로그의 문서를 참조하십시오 .

---

Postgres에 대해 정말 좋아하는 것 중 하나는 열에서 지원되는 데이터 유형 중 일부입니다. 예를 들어 네트워크 주소 및 배열 을 저장하기 위해 만들어진 열 유형이 있습니다. 이러한 열 유형에 해당하는 함수 ( Network Addresses / Arrays )를 사용하면 MySQL 또는 다른 데이터베이스 엔진의 코드를 통해 결과를 처리하여 수행해야하는 쿼리 내에서 복잡한 작업을 많이 수행 할 수 있습니다.

---

배열은 일단 알게되면 정말 멋집니다. 페이지 간 하이퍼 링크를 저장하고 싶다고 가정 해 보겠습니다. 다음과 같은 테이블 생성에 대해 생각하는 것으로 시작할 수 있습니다.

CREATE TABLE hyper.links (
     tail INT4,
     head INT4
);

꼬리 열 을 인덱싱해야하고 200,000,000 개의 링크 행이 있었다면 (위키피디아가 제공하는 것과 같이) 거대한 테이블과 거대한 인덱스가있을 것입니다.

그러나 PostgreSQL에서는 대신 다음 테이블 형식을 사용할 수 있습니다.

CREATE TABLE hyper.links (
     tail INT4,
     head INT4[],
     PRIMARY KEY(tail)
);

링크에 대한 모든 헤드를 얻으려면 다음과 같은 명령을 보낼 수 있습니다 (8.4부터 unnest ()가 표준입니다).

SELECT unnest(head) FROM hyper.links WHERE tail = $1;

이 쿼리는 첫 번째 옵션과 비교할 때 놀랍도록 빠릅니다 (unnest ()는 빠르며 Index는 훨씬 작습니다). 또한 테이블과 인덱스는 특히 배열이 너무 길어서 토스트 테이블로 압축 될 때 RAM 메모리와 HD 공간을 훨씬 적게 차지합니다. 배열은 정말 강력합니다.

참고 : unnest ()는 배열에서 행을 생성하지만 array_agg ()는 행을 배열로 집계합니다.

---

구체화 된 뷰는 설정하기가 매우 쉽습니다.

CREATE VIEW my_view AS SELECT id, AVG(my_col) FROM my_table GROUP BY id;
CREATE TABLE my_matview AS SELECT * FROM my_view;

그러면 my_view의 열과 값이있는 새 테이블 my_matview가 생성됩니다. 그런 다음 트리거 또는 크론 스크립트를 설정하여 데이터를 최신 상태로 유지하거나 게으른 경우 :

TRUNCATE my_matview;
INSERT INTO my_matview SELECT * FROM my_view;

---

• Inheritance..infact 다중 상속 (많은 웹 프레임 워크가 postgres로 작업 할 때 구현하는 일대일 관계 상속이 아닌 부모-자식 "상속"에서와 같음).

• PostGIS (공간 확장), 포괄적 인 지오메트리 기능 세트를 제공하고 즉시 저장을 조정하는 멋진 애드온입니다. 많은 오픈 소스 지리 라이브러리 (예 : OpenLayers, MapServer, Mapnik 등)에서 널리 사용되며 확실히 MySQL의 공간 확장보다 훨씬 좋습니다.

• C, Python, Perl 등과 같은 다른 언어로 프로 시저를 작성합니다 (db 관리자가 아닌 개발자라면 코드 작성이 쉬워집니다).

  또한 모든 프로시 저는 외부 적으로 (모듈로) 저장 될 수 있으며 지정된 인수에 의해 런타임에 호출되거나 가져올 수 있습니다. 이렇게하면 코드를 소스 제어하고 코드를 쉽게 디버깅 할 수 있습니다.

• 데이터베이스에 구현 된 모든 개체 (예 : 테이블, 제약 조건, 인덱스 등)에 대한 방대하고 포괄적 인 카탈로그.

  나는 항상 몇 가지 쿼리를 실행하고 모든 메타 정보 (예 : 제약 조건 이름 및 필드가 구현 된 필드, 인덱스 이름 등)를 얻는 것이 매우 유용하다는 것을 알게되었습니다.

  저에게는 새 데이터를로드하거나 큰 테이블에서 대규모 업데이트를 수행해야 할 때 (자동으로 트리거를 비활성화하고 인덱스를 삭제) 처리가 완료된 후 다시 쉽게 다시 만들어야 할 때 모든 것이 매우 편리해집니다. 누군가는 이러한 쿼리 중 소수를 작성하는 훌륭한 작업을 수행했습니다.

  http://www.alberton.info/postgresql_meta_info.html

• 하나의 데이터베이스 아래에 여러 스키마가 있고 데이터베이스에 많은 수의 테이블이있는 경우 사용할 수 있으며 스키마를 범주로 생각할 수 있습니다. 모든 테이블 (스키마에 관계없이)은 상위 db에있는 다른 모든 테이블과 함수에 액세스 할 수 있습니다.

---

"분석 설명"출력을 해독하는 방법을 배울 필요가 없습니다. 도구가 있습니다. http://explain.depesz.com

---

select pg_size_pretty(200 * 1024)

---

pgcrypto : 많은 프로그래밍 언어의 암호화 모듈이 제공하는 것보다 더 많은 암호화 기능, 모두 데이터베이스에서 직접 액세스 할 수 있습니다. 그것은 암호화 자료를 Just Get Right를 매우 쉽게 만듭니다.

---

다음을 사용하여 데이터베이스를 복사 할 수 있습니다.

createdb -T old_db new_db

문서는 다음과 같이 말합니다.

이것은 (아직) 범용 "COPY DATABASE"기능이 아닙니다.

그러나 그것은 나를 위해 잘 작동하고

createdb new_db

pg_dump old_db | psql new_db

---

일회용 데이터 / 전역 변수를위한 메모리 스토리지

RAM에있는 테이블 스페이스와 해당 테이블 스페이스에 테이블 (9.1에서 로그되지 않을 수 있음)을 생성하여 세션간에 공유하려는 일회용 데이터 / 전역 변수를 저장할 수 있습니다.

http://magazine.redhat.com/2007/12/12/tip-from-an-rhce-memory-storage-on-postgresql/

자문 잠금

이것들은 매뉴얼의 모호한 영역에 문서화되어 있습니다.

http://www.postgresql.org/docs/9.0/interactive/functions-admin.html

여러 행 수준 잠금을 획득하는 것보다 때때로 더 빠르며 FOR UPDATE가 구현되지 않은 경우 (예 : 재귀 CTE 쿼리)를 해결하는 데 사용할 수 있습니다.

---

이것은 덜 알려진 기능의 즐겨 찾기 목록입니다.

트랜잭션 DDL

거의 모든 SQL 문은 Postgres에서 트랜잭션입니다. 자동 커밋을 끄면 다음이 가능합니다.

drop table customer_orders;
rollback;
select *
from customer_orders;

범위 유형 및 제외 제약

내 지식으로 Postgres는 두 범위가 겹치는 지 확인하는 제약 조건을 만들 수있는 유일한 RDBMS입니다. 예를 들어 "유효 시작"및 "유효 종료"날짜가있는 제품 가격이 포함 된 테이블이 있습니다.

create table product_price
(
   price_id      serial        not null primary key,
   product_id    integer       not null references products,
   price         numeric(16,4) not null,
   valid_during  daterange not null
);

NoSQL 기능

이 hstore확장은 데이터베이스의 일부가 "스키마가 없어야"할 때 사용할 수있는 유연하고 매우 빠른 키 / 값 저장소를 제공합니다. JSON은 스키마없는 방식으로 데이터를 저장하는 또 다른 옵션이며

insert into product_price 
  (product_id, price, valid_during)
values 
  (1, 100.0, '[2013-01-01,2014-01-01)'),
  (1,  90.0, '[2014-01-01,)');


-- querying is simply and can use an index on the valid_during column
select price
from product_price
where product_id = 42
  and valid_during @> date '2014-10-17';

700.000 행이있는 테이블에 대한 위의 실행 계획 :

Index Scan using check_price_range on public.product_price  (cost=0.29..3.29 rows=1 width=6) (actual time=0.605..0.728 rows=1 loops=1)
  Output: price
  Index Cond: ((product_price.valid_during @> '2014-10-17'::date) AND (product_price.product_id = 42))
  Buffers: shared hit=17
Total runtime: 0.772 ms

유효 범위가 겹치는 행을 삽입하지 않으려면 간단하고 효율적인 고유 제약 조건을 정의 할 수 있습니다.

alter table product_price
  add constraint check_price_range 
  exclude using gist (product_id with =, valid_during with &&)

무한대

먼 미래의 "실제"날짜를 요구하는 대신 Postgres는 날짜를 무한대로 비교할 수 있습니다. 예 : 날짜 범위를 사용하지 않는 경우 다음을 수행 할 수 있습니다.

insert into product_price 
  (product_id, price, valid_from, valid_until)
values 
  (1,  90.0, date '2014-01-01', date 'infinity');

쓰기 가능한 공통 테이블 표현식

단일 명령문에서 삭제, 삽입 및 선택할 수 있습니다.

with old_orders as (
   delete from orders
   where order_date < current_date - interval '10' year
   returning *
), archived_rows as (
   insert into archived_orders 
   select * 
   from old_orders
   returning *
)
select *
from archived_rows;

위의 경우 10 년이 지난 모든 주문을 삭제하고 archived_orders테이블 로 이동 한 다음 이동 된 행을 표시합니다.

---

1.) 질의에 대한 추가 통지가 필요한 경우 중첩 된 주석을 사용할 수 있습니다.

SELECT /* my comments, that I would to see in PostgreSQL log */
       a, b, c
   FROM mytab;

2.) 데이터베이스의 모든 text및 varchar필드에서 후행 공백을 제거 합니다.

do $$
declare
    selectrow record;
begin
for selectrow in
select 
       'UPDATE '||c.table_name||' SET '||c.COLUMN_NAME||'=TRIM('||c.COLUMN_NAME||')  WHERE '||c.COLUMN_NAME||' ILIKE ''% '' ' as script
from (
       select 
          table_name,COLUMN_NAME
       from 
          INFORMATION_SCHEMA.COLUMNS 
       where 
          table_name LIKE 'tbl%'  and (data_type='text' or data_type='character varying' )
     ) c
loop
execute selectrow.script;
end loop;
end;
$$;

3.) 중복 행을 매우 효과적으로 제거하기 위해 창 기능을 사용할 수 있습니다.

DELETE FROM tab 
  WHERE id IN (SELECT id 
                  FROM (SELECT row_number() OVER (PARTITION BY column_with_duplicate_values), id 
                           FROM tab) x 
                 WHERE x.row_number > 1);

일부 PostgreSQL의 최적화 된 버전 (ctid 포함) :

DELETE FROM tab 
  WHERE ctid = ANY(ARRAY(SELECT ctid 
                  FROM (SELECT row_number() OVER (PARTITION BY column_with_duplicate_values), ctid 
                           FROM tab) x 
                 WHERE x.row_number > 1));

4.) 서버의 상태를 식별해야 할 때 함수를 사용할 수 있습니다.

SELECT pg_is_in_recovery();

5.) 함수의 DDL 명령을 가져옵니다.

select pg_get_functiondef((select oid from pg_proc where proname = 'f1'));

6.) PostgreSQL에서 안전하게 열 데이터 유형 변경

create table test(id varchar );
insert into test values('1');
insert into test values('11');
insert into test values('12');

select * from test
--Result--
id
character varying
--------------------------
1
11
12

You can see from the above table that I have used the data type – ‘character varying’ for ‘id’
column. But it was a mistake, because I am always giving integers as id. So using varchar here is a bad practice. So let’s try to change the column type to integer.

ALTER TABLE test ALTER COLUMN id TYPE integer;

But it returns:

ERROR: column “id” cannot be cast automatically to type integer SQL state: 42804 Hint: Specify a USING expression to perform the conversion

That means we can’t simply change the data type because data is already there in the column. Since the data is of type ‘character varying’ postgres cant expect it as integer though we entered integers only. So now, as postgres suggested we can use the ‘USING’ expression to cast our data into integers.

ALTER TABLE test ALTER COLUMN id  TYPE integer USING (id ::integer);

It Works.

7.) Know who is connected to the Database
This is more or less a monitoring command. To know which user connected to which database including their IP and Port use the following SQL:

SELECT datname,usename,client_addr,client_port FROM pg_stat_activity ;

8.) Reloading PostgreSQL Configuration files without Restarting Server

PostgreSQL configuration parameters are located in special files like postgresql.conf and pg_hba.conf. Often, you may need to change these parameters. But for some parameters to take effect we often need to reload the configuration file. Of course, restarting server will do it. But in a production environment it is not preferred to restarting the database, which is being used by thousands, just for setting some parameters. In such situations, we can reload the configuration files without restarting the server by using the following function:

select pg_reload_conf();

Remember, this wont work for all the parameters, some parameter changes need a full restart of the server to be take in effect.

9.) Getting the data directory path of the current Database cluster

It is possible that in a system, multiple instances(cluster) of PostgreSQL is set up, generally, in different ports or so. In such cases, finding which directory(physical storage directory) is used by which instance is a hectic task. In such cases, we can use the following command in any database in the cluster of our interest to get the directory path:

SHOW data_directory;

The same function can be used to change the data directory of the cluster, but it requires a server restarts:

SET data_directory to new_directory_path;

10.) Find a CHAR is DATE or not

create or replace function is_date(s varchar) returns boolean as $$
begin
  perform s::date;
  return true;
exception when others then
  return false;
end;
$$ language plpgsql;

Usage: the following will return True

select is_date('12-12-2014')
select is_date('12/12/2014')
select is_date('20141212')
select is_date('2014.12.12')
select is_date('2014,12,12')

11.) Change the owner in PostgreSQL

REASSIGN OWNED BY sa  TO postgres;

12.) PGADMIN PLPGSQL DEBUGGER

Well explained here

---

It's convenient to rename an old database rather than mysql can do. Just using the following command:

ALTER DATABASE name RENAME TO new_name

참고URL : https://stackoverflow.com/questions/761327/hidden-features-of-postgresql