Jestem zaskoczony, że to nie zostało jeszcze opublikowane. Jakieś ciekawe sztuczki, o których wiesz w Postgres? Niezrozumiałe opcje konfiguracyjne i sztuczki skalowania / wydajności są szczególnie mile widziane.
Jestem pewien, że możemy pokonać 9 komentarzy w odpowiednim wątku MySQL :)
Odpowiedzi:
Ponieważ postgres jest o wiele bardziej rozsądny niż MySQL, nie ma zbyt wielu "sztuczek" do raportowania ;-)
Instrukcja posiada kilka wydajności wskazówek.
Kilka innych kwestii związanych z wydajnością, o których należy pamiętać:
Oto kilka rzeczy, które uznałem za przydatne, które same w sobie nie są związane z konfiguracją ani wydajnością.
Aby zobaczyć, co się obecnie dzieje:
select * from pg_stat_activity;
Wyszukaj dodatkowe funkcje:
select * from pg_proc WHERE proname ~* '^pg_.*'
Znajdź rozmiar bazy danych:
select pg_database_size('postgres');
select pg_size_pretty(pg_database_size('postgres'));
Znajdź rozmiar wszystkich baz danych:
select datname, pg_size_pretty(pg_database_size(datname)) as size
from pg_database;
Znajdź rozmiar tabel i indeksów:
select pg_size_pretty(pg_relation_size('public.customer'));
Lub, aby wyświetlić wszystkie tabele i indeksy (prawdopodobnie łatwiej to zobaczyć):
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;
Aha, i możesz zagnieżdżać transakcje, wycofywać transakcje częściowe ++
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)
Najłatwiejszy trik pozwolić postgresql wykonać o wiele lepiej (oprócz ustawiania i korzystania z odpowiednich indeksów oczywiście) jest po prostu dać mu więcej pamięci RAM do pracy z (jeśli nie zostało to jeszcze zrobione). W większości domyślnych instalacji wartość shared_buffers jest zdecydowanie za niska (moim zdaniem). Możesz ustawić
shared_buffers
w postgresql.conf. Podziel tę liczbę przez 128, aby uzyskać przybliżoną ilość pamięci (w MB), jaką mogą żądać postgresy. Jeśli zrobisz to wystarczająco dużo, postgresql będzie latać. Nie zapomnij zrestartować postgresql.
W systemach Linux, gdy postgresql nie uruchomi się ponownie, prawdopodobnie osiągniesz limit kernel.shmmax. Ustaw wyżej za pomocą
sysctl -w kernel.shmmax=xxxx
Aby utrzymywało się to między uruchomieniami, dodaj wpis kernel.shmmax do /etc/sysctl.conf.
Całą masę sztuczek Postgresql można znaleźć tutaj :
Postgres ma bardzo potężne narzędzie do obsługi datetime dzięki obsłudze INTERVAL.
Na przykład:
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)
Możesz rzutować wiele ciągów na typ INTERVAL.
KOPIUJ
Zacznę. Za każdym razem, gdy przechodzę na Postgres z SQLite, zwykle mam naprawdę duże zbiory danych. Kluczem jest, aby załadować tabele za pomocą COPY FROM, zamiast robić INSERTS. Zobacz dokumentację:
http://www.postgresql.org/docs/8.1/static/sql-copy.html
Poniższy przykład kopiuje tabelę do klienta, używając pionowej kreski (|) jako ogranicznika pola:
COPY country TO STDOUT WITH DELIMITER '|';
Aby skopiować dane z pliku do tabeli krajów:
COPY country FROM '/usr1/proj/bray/sql/country_data';
Zobacz także tutaj: Szybsze wkładki zbiorcze w sqlite3?
generate_series: w końcu czysty sposób generowania fikcyjnych zestawów wierszy.Możliwość użycia skorelowanej wartości w LIMITklauzuli podzapytania:
SELECT (
SELECT exp_word
FROM mytable
OFFSET id
LIMIT 1
)
FROM othertable
Jedną z rzeczy, które naprawdę lubię w Postgres, są niektóre typy danych obsługiwane w kolumnach. Na przykład istnieją typy kolumn przeznaczone do przechowywania adresów sieciowych i tablic . Odpowiednie funkcje ( adresy sieciowe / tablice ) dla tych typów kolumn pozwalają na wykonywanie wielu złożonych operacji wewnątrz zapytań, które musiałbyś wykonać, przetwarzając wyniki za pomocą kodu w MySQL lub innych silnikach baz danych.
Tablice są naprawdę fajne, gdy je poznasz. Powiedzmy, że chcesz przechowywać hiperłącza między stronami. Możesz zacząć od pomysłu na utworzenie tabeli w ten sposób:
CREATE TABLE hyper.links (
tail INT4,
head INT4
);
Gdybyś musiał zindeksować kolumnę końcową i miałbyś, powiedzmy, 200 000 000 wierszy linków (jak dałaby ci Wikipedia), znalazłbyś się z ogromną tabelą i ogromnym indeksem.
Jednak w przypadku PostgreSQL można zamiast tego użyć następującego formatu tabeli:
CREATE TABLE hyper.links (
tail INT4,
head INT4[],
PRIMARY KEY(tail)
);
Aby zdobyć wszystkie głowy dla łącza, możesz wysłać takie polecenie (unnest () jest standardem od 8.4):
SELECT unnest(head) FROM hyper.links WHERE tail = $1;
To zapytanie jest zaskakująco szybkie w porównaniu z pierwszą opcją (unnest () jest szybka, a indeks jest znacznie mniejszy). Co więcej, Twój Tabela i Indeks zajmą znacznie mniej pamięci RAM i miejsca na dysku HD, zwłaszcza gdy tablice są tak długie, że są skompresowane do tabeli Toast. Tablice są naprawdę potężne.
Uwaga: podczas gdy unnest () będzie generować wiersze z tablicy, array_agg () będzie gromadzić wiersze w tablicę.
Zmaterializowane widoki są dość łatwe do skonfigurowania:
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;
To tworzy nową tabelę, my_matview, z kolumnami i wartościami my_view. Następnie można skonfigurować wyzwalacze lub skrypt cron, aby zapewnić aktualność danych lub jeśli jesteś leniwy:
TRUNCATE my_matview;
INSERT INTO my_matview SELECT * FROM my_view;
Inheritance..infact Multiple Inheritance (jak w przypadku dziedziczenia rodzic-dziecko, a nie dziedziczenia relacji 1-do-1, które implementuje wiele frameworków internetowych podczas pracy z postgres).
PostGIS (rozszerzenie przestrzenne), wspaniały dodatek oferujący kompleksowy zestaw funkcji geometrii i przechowywania współrzędnych po wyjęciu z pudełka. Szeroko stosowany w wielu bibliotekach geograficznych typu open source (np. OpenLayers, MapServer, Mapnik itp.) I zdecydowanie lepszy niż przestrzenne rozszerzenia MySQL.
Pisanie procedur w różnych językach, np. C, Python, Perl itp. (Ułatwia programowanie, jeśli jesteś programistą, a nie db-adminem).
Ponadto wszystkie procedury mogą być przechowywane zewnętrznie (jako moduły) i mogą być wywoływane lub importowane w czasie wykonywania za pomocą określonych argumentów. W ten sposób możesz łatwo kontrolować kod źródłowy i debugować kod.
Ogromny i obszerny katalog wszystkich obiektów zaimplementowanych w Twojej bazie danych (tj. Tabele, ograniczenia, indeksy itp.).
Zawsze uważam, że niezmiernie pomocne jest uruchomienie kilku zapytań i uzyskanie wszystkich metainformacji, np. Nazw ograniczeń i pól, na których zostały zaimplementowane, nazwy indeksów itp.
Dla mnie to wszystko staje się niezwykle przydatne, gdy muszę ładować nowe dane lub robić masowe aktualizacje w dużych tabelach (automatycznie wyłączałbym wyzwalacze i porzucał indeksy), a następnie łatwo je ponownie odtwarzałem po zakończeniu przetwarzania. Ktoś wykonał świetną robotę, pisząc garść takich zapytań.
Wiele schematów w jednej bazie danych, możesz ich używać, jeśli twoja baza danych ma dużą liczbę tabel, możesz myśleć o schematach jako o kategoriach. Wszystkie tabele (niezależnie od schematu) mają dostęp do wszystkich innych tabel i funkcji obecnych w nadrzędnej bazie danych.
Nie musisz się uczyć, jak odszyfrowywać dane wyjściowe „wyjaśnij analizę”, istnieje narzędzie: http://explain.depesz.com
select pg_size_pretty(200 * 1024)
PostgreSQL 9.3złapanym błędzie
pg_size_pretty((200 * 1024)::bigint)
Bazę danych można skopiować za pomocą:
createdb -T stara_db nowa_db
Dokumentacja mówi:
nie jest to (jeszcze) przeznaczone jako funkcja „KOPIUJ BAZĘ DANYCH” ogólnego przeznaczenia
ale działa dobrze dla mnie i jest znacznie szybszy niż
createdb new_db
pg_dump old_db | psql new_db
Pamięć na dane jednorazowe / zmienne globalne
Możesz utworzyć obszar tabel, który żyje w pamięci RAM i tabele (prawdopodobnie niezalogowane, w wersji 9.1) w tym obszarze tabel, aby przechowywać dane jednorazowe / zmienne globalne, które chcesz udostępniać między sesjami.
http://magazine.redhat.com/2007/12/12/tip-from-an-rhce-memory-storage-on-postgresql/
Doradcze blokady
Są one udokumentowane w niejasnym miejscu instrukcji:
http://www.postgresql.org/docs/9.0/interactive/functions-admin.html
Czasami jest to szybsze niż uzyskiwanie wielu blokad na poziomie wiersza i można ich użyć do obejścia przypadków, w których FOR UPDATE nie jest zaimplementowana (takich jak rekursywne zapytania CTE).
UNLOGGEDtabel.
To jest moja lista ulubionych mniej znanych funkcji.
Prawie każda instrukcja SQL jest transakcyjna w Postgres. Jeśli wyłączysz automatyczne zatwierdzanie, możliwe są następujące działania:
drop table customer_orders;
rollback;
select *
from customer_orders;
O ile mi wiadomo, Postgres jest jedynym systemem RDBMS, który pozwala na utworzenie ograniczenia sprawdzającego, czy dwa zakresy się pokrywają. Przykładem jest tabela zawierająca ceny produktów z datami „ważne od” i „ważne do”:
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
);
Do hstore oferty przedłużające elastyczny i bardzo szybki klucz przechowywać wartość /, które mogą być stosowane, gdy części potrzeby bazy danych, aby być „schematu mniej”. JSON to kolejna opcja przechowywania danych w sposób pozbawiony schematu i
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';
Plan wykonania powyższego na tabeli z 700.000 rzędami:
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
Aby uniknąć wstawiania wierszy z nakładającymi się zakresami ważności, można zdefiniować proste (i wydajne) unikalne ograniczenie:
alter table product_price
add constraint check_price_range
exclude using gist (product_id with =, valid_during with &&)
Zamiast wymagać "prawdziwej" daty daleko w przyszłości, Postgres może porównywać daty do nieskończoności. Np. Jeśli nie używasz zakresu dat, możesz wykonać następujące czynności
insert into product_price
(product_id, price, valid_from, valid_until)
values
(1, 90.0, date '2014-01-01', date 'infinity');
Możesz usuwać, wstawiać i wybierać w jednym wyciągu:
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;
Powyższe spowoduje usunięcie wszystkich zamówień starszych niż 10 lat, przeniesienie ich do archived_orderstabeli, a następnie wyświetlenie przeniesionych wierszy.
1.) Gdy potrzebujesz dołączyć uwagi do zapytania, możesz użyć zagnieżdżonego komentarza
SELECT /* my comments, that I would to see in PostgreSQL log */
a, b, c
FROM mytab;
2.) Usuń końcowe spacje ze wszystkich pól texti varcharw bazie danych.
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.) Możemy użyć funkcji okna do bardzo skutecznego usuwania zduplikowanych wierszy:
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);
Niektóre zoptymalizowane wersje PostgreSQL (z 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.) Gdy potrzebujemy zidentyfikować stan serwera, możemy skorzystać z funkcji:
SELECT pg_is_in_recovery();
5.) Pobierz polecenie DDL funkcji.
select pg_get_functiondef((select oid from pg_proc where proname = 'f1'));
6.) Bezpieczna zmiana typu danych kolumny w 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
Z powyższej tabeli widać, że użyłem typu danych - „zmienny znak” w
kolumnie „identyfikator” . Ale to był błąd, ponieważ jako id zawsze podaję liczby całkowite. Więc używanie varchar tutaj jest złą praktyką. Spróbujmy więc zmienić typ kolumny na liczbę całkowitą.
ALTER TABLE test ALTER COLUMN id TYPE integer;
Ale wraca:
BŁĄD: nie można automatycznie rzutować kolumny „id” na typ całkowity stan SQL: 42804 Wskazówka: Określ wyrażenie USING, aby wykonać konwersję
Oznacza to, że nie możemy po prostu zmienić typu danych, ponieważ dane są już w kolumnie. Ponieważ dane są typu „zmienne znakowo”, postgres nie może oczekiwać, że będzie to liczba całkowita, chociaż wprowadziliśmy tylko liczby całkowite. Więc teraz, jak zasugerował postgres, możemy użyć wyrażenia „USING” do rzutowania naszych danych na liczby całkowite.
ALTER TABLE test ALTER COLUMN id TYPE integer USING (id ::integer);
To działa.
7.) Dowiedz się, kto jest podłączony do bazy danych
Jest to mniej więcej polecenie monitorujące. Aby dowiedzieć się, który użytkownik połączył się z którą bazą danych, w tym jego adresem IP i portem, użyj następującego SQL:
SELECT datname,usename,client_addr,client_port FROM pg_stat_activity ;
8.) Ponowne ładowanie plików konfiguracyjnych PostgreSQL bez ponownego uruchamiania serwera
Parametry konfiguracyjne PostgreSQL znajdują się w specjalnych plikach, takich jak postgresql.conf i pg_hba.conf. Często może zajść potrzeba zmiany tych parametrów. Jednak aby niektóre parametry zaczęły obowiązywać, często musimy ponownie załadować plik konfiguracyjny. Oczywiście zrestartowanie serwera wystarczy. Jednak w środowisku produkcyjnym nie zaleca się ponownego uruchamiania bazy danych, która jest używana przez tysiące, tylko w celu ustawienia niektórych parametrów. W takich sytuacjach możemy przeładować pliki konfiguracyjne bez restartowania serwera za pomocą funkcji:
select pg_reload_conf();
Pamiętaj, że to nie zadziała dla wszystkich parametrów, niektóre zmiany parametrów wymagają pełnego restartu serwera, aby odniosły skutek.
9.) Pobieranie ścieżki katalogu danych bieżącego klastra bazy danych
Jest możliwe, że w systemie skonfigurowanych jest wiele instancji (klastrów) PostgreSQL, ogólnie w różnych portach. W takich przypadkach znalezienie katalogu (fizycznego katalogu pamięci) używanego przez którą instancję jest gorączkowym zadaniem. W takich przypadkach możemy użyć następującego polecenia w dowolnej bazie danych w interesującym nas klastrze, aby uzyskać ścieżkę do katalogu:
SHOW data_directory;
Tej samej funkcji można użyć do zmiany katalogu danych klastra, ale wymaga to ponownego uruchomienia serwera:
SET data_directory to new_directory_path;
10.) Znajdź CHAR to DATA, czy nie
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;
Użycie: poniższe zwróci wartość 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.) Zmień właściciela w PostgreSQL
REASSIGN OWNED BY sa TO postgres;
12.) DEBUGGER PGADMIN PLPGSQL
Dobrze wyjaśniono tutaj
Wygodniej jest zmienić nazwę starej bazy danych, a nie mysql. Wystarczy użyć następującego polecenia:
ALTER DATABASE name RENAME TO new_name