COPY - Exporting and Importing Data

Giriş

Açıklaması şöyle. Yani COPY, COPY TO ve COPY FROM komutları var

The COPY command can import data to Postgres if access to text, CSV, or binary format data.
...
The file mentioned in the COPY command must be accessible by the Postgres user and should be specified from the perspective of the Postgres server.

The command can also use a SELECT query to load data to a table. It also allows you to specify a list of columns to insert the data into instead of the whole table. On successful completion, the COPY command outputs result in the format COPY count, denoting the number of rows inserted by the command.

Both the text and the CSV file formats allow you to specify a delimiter. But if your input is CSV, it is better to use the CSV format with the DELIMITER option rather than the TEXT format since CSV format adheres to the commonly accepted CSV escaping mechanism. By contrast, the TEXT format follows Postgres-specific escaping rules.

Açıklaması şöyle

The COPY command has many optional parameters that you can use to customize its behavior. Some of the important ones are listed below:

• QUOTE: Specify the character used to quote the data values.
• NULL: Specifies the character used to represent the NULL value.
• ESCAPE: Specifies the character used to escape a character that is being used as the QUOTE character.
• ENCODING: Used to describe the encoding of the file. If nothing is mentioned, it defaults to client encoding.

EXPORT

Örnek - CSV

Şöyle yaparız

COPY table_name TO '/path/to/file.csv' DELIMITER ',' CSV HEADER;
COPY table_name FROM '/path/to/file.csv' DELIMITER ',' CSV HEADER;

IMPORT

Söz dizimi

COPY [tablename] FROM [filename] şeklindedir

Örnek - CSV

Şöyle yaparız

COPY customer FROM '/home/data/customer.csv' DELIMITER ',' CSV HEADER;

Örnek - CSV

CSV dosyasındaki bazı sütunları almak için şöyle yaparız

COPY customer(first_name,last_name,email) FROM '/home/data/customers1.csv' DELIMITER ',' CSV HEADER;

Örnek

Şöyle yaparız

-- Create temporary table
DROP TABLE IF EXISTS music_track;
CREATE TABLE music_track (
  id         varchar primary key,
  artist_id  varchar,
  title      varchar
);

-- first we copy from the csv into a temporary table
-- if the file is inside the server, you can use COPY
-- otherwise, use \copy to point to your machine
\copy music_track -- music_track is the table name
FROM '/home/username/data/music-track-external.csv' -- using absolute path on client side
WITH (format csv, header); -- file is csv and first line is the header

-- we use the temporary table to populate the correct values into the production table
INSERT INTO music_catalog (track_id, artist_id, title, url)
  SELECT music_track.id, music_track.artist_id, music_track.title, music_file.url 
  FROM music_track INNER JOIN music_file ON music_track.id = music_file.track_id
ON CONFLICT (music_catalog.track_id) DO UPDATE SET
  artist_id = music_track.artist_id
  title     = music_track.title,
  url       = music_files.url;

Açıklaması şöyle

In the example above, we needed to import a large amount of data from an external source. Using COPY or \copy (depending on if you have the file in the server or the client) is probably the fastest option.

You can also leverage the power of “INSERT INTO SELECT” to merge the data from a COPY with data from other tables, loading the initial data into a temporary table first.

Insert Into Select - Bulk insert/update İçindir

Açıklaması şöyle

You can also leverage the power of “INSERT INTO SELECT” to merge the data from a COPY with data from other tables, loading the initial data into a temporary table first.

Subquery Expressions - EXISTS

Giriş

Subquery sadece satır olup olmadığıyla ilgilenir. Satırın içeriği önemli değildir. Bu yüzden SELECT 1 kullanılabilir.

NOT IN vs NOT EXISTS

Açıklaması şöyle

NOT EXISTS will usually outperform “NOT IN” by a good margin.

Kullanım

Örnek

bar.geom noktasından 10 birimden fazla uzak olanları foo'ları seçmek için şöyle yaparız. bar.geom ve foo.geom aynı birimden olmalı. Eğer bu ikisinin birimi metre ise 10 birim de aslında 10 metre anlamına gelir.

SELECT *
FROM   foo
WHERE  NOT EXISTS (
  SELECT 1
  FROM   bar
  WHERE  ST_DWithin(bar.geom, foo.geom, 10)
);

PostGIS ST_Dwithin

Giriş

Belirtilen noktanın X birim uzağına düşen noktaları verir

Örnek

bar.geom noktasından 10 birimden fazla uzak olanları foo'ları seçmek için şöyle yaparız. bar.geom ve foo.geom aynı birimden olmalı. Eğer bu ikisinin birimi metre ise 10 birim de aslında 10 metre anlamına gelir.

SELECT *
FROM   foo
WHERE  NOT EXISTS (
  SELECT 1
  FROM   bar
  WHERE  ST_DWithin(bar.geom, foo.geom, 10)
);

High Availability

Giriş

Tuning your PostgreSQL for High Performance yazısına bakılabilir

Sanırım 3 tane temel senaryo var. Bunlar şöyle

1. Running PostgreSQL Outside of Kubernetes

Şeklen şöyle

Açıklaması şöyle

The PostgreSQL cluster consists of a Master Node and a Standby Node. In case the Master Node failed for some reason (e.g., hardware or network defect) the standby server can overtake the role of the master. The PG-Bouncer in this picture is a component from Postgres and acts as a kind of reverse proxy server. In case of a failure, the switch from the Master to the Standby node can be done by an administrator or can be automated by scripts. From the view of a client, this switch is transparent.

2. Running PostgreSQL Inside of Kubernetes

Örnek

Deployment şöyledir

apiVersion: apps/v1
kind: Deployment
metadata:
  name: postgres
  namespace: spring-keycloak-demo
spec:
  selector:
    matchLabels:
      app: postgres
  replicas: 1
  template:
    metadata:
      labels:
        app: postgres
    spec:
      containers:
        - name: postgres
          image: postgres:latest
          ports:
            - containerPort: 5432
          env:
            - name: POSTGRES_DB
              value: postgres
            - name: POSTGRES_USER
              value: postgres
            - name: POSTGRES_PASSWORD
              valueFrom:
                secretKeyRef:
                  name: spring-keycloak-secrets
                  key: postgres-pass

service şöyledir

apiVersion: v1
kind: Service
metadata:
  name: postgres-service
  namespace: spring-keycloak-demo
  labels:
    app: postgres
spec:
  selector:
    app: postgres
  ports:
    - port: 5432
  type: NodePort

Örnek

Şöyle yaparız. storageClassName is specific to Kubernetes cluster

apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: postgres
  labels:
    component: postgres
spec:
  selector:
    matchLabels:
      component: postgres
  serviceName: postgres
  template:
    metadata:
      labels:
        component: postgres
    spec:
      containers:
        - name: postgres
          image: postgres:11
          ports:
            - containerPort: 5432
          volumeMounts:
            - mountPath: /var/lib/postgresql/data
              name: postgres-data
          env:
            - name: POSTGRES_DB
              value: postgres
            - name: POSTGRES_USER
              value: postgres
            - name: POSTGRES_PASSWORD
              value: postgres
  volumeClaimTemplates:
    - metadata:
        name: postgres-data
      spec:
        accessModes:
          - ReadWriteOnce
        storageClassName: hostpath
        resources:
          requests:
            storage: 5Gi

Service olarak şöyle yaparız

apiVersion: v1
kind: Service
metadata:
  name: postgres
  labels:
    component: postgres
spec:
  selector:
    component: postgres
  ports:
    - port: 5432

3. Running PostgreSQL on a Distributed Block Storage

Select For Share - Read Lock

Giriş

Açıklaması şöyle

A weaker form of select for update is the select for share query. It is an ideal for ensuring referential integrity when creating child records for a parent.

Örnek

Açıklaması şöyle

Suppose that we want to create a new purchase for a user. First, we would select the user from the database and then insert a new record in the purchases database. Can we safely insert a new purchase into the database? With a regular select statement we can’t. Other processes could delete the user in the moments between selecting the user and inserting the purchase.

One way to avoid potential issues is to query for the user with the FOR SHARE locking clause.

Şöyle yaparız

process A: BEGIN;
process A: SELECT * FROM users WHERE id = 1 FOR SHARE;
process B: DELETE FROM users WHERE id = 1;
-- process B blocks and must wait for process A to finish

process A: INSERT INTO purchases (id, user_id) VALUES (1, 1);
process A: COMMIT;
-- process B now unblocks and deletes the user

Açıklaması şöyle

Select for share prevented other processes from deleting the user, but does not prevent concurrent processes from selecting users. This is the major difference between select for share and select for update.

The select for share prevents updates and deletes of rows, but doesn’t prevent other processes from acquiring a select for share. On the other hand, select for update also blocks updates and deletes, but it also prevents other processes from acquiring a select for update lock.

Select For Update Skip Locked

Giriş

Açıklaması şöyle. Yani bir bir transaction tarafından "select for update" ile kilitlenmiş satırları hariç bırakır ve geri kalan satırları verir

Processing Non-Locked Database Rows

Select for update can be a rigid lock on your table. Concurrent processes can be blocked and starved out. Waiting is the slowest form of concurrent processing. If only one CPU can be active at a time, it is pointless to scale your servers. For this purpose, in PostgreSQL there is a mechanism for selecting only rows that are not locked.

The select ... for update skip locked is a statement that allows you to query rows that have no locks. Let’s observe the following scenario to grasp its use case:

Ne İçin Kullanılır?

Açıklaması şöyle

The SKIP LOCKED clause allows a query to skip rows that are currently locked by other transactions. This is useful for job queues where multiple workers need to process jobs concurrently without conflicts.

Örnek

Şöyle yaparız

//session 1
BEGIN;
SELECT * FROM student WHERE id=1 FOR UPDATE; 

//session 2
SELECT * FROM student FOR UPDATE SKIP LOCKED;

Örnek

Şöyle yaparız

process A: SELECT * FROM purchases
process A:   WHERE processed = false FOR UPDATE SKIP LOCKED;
process B: SELECT * FROM purchases
process B:   WHERE created_at < now()::date - interval '1w';
process B:   FOR UPDATE SKIP LOCKED;
-- process A selects and locks all unprocess rows
-- process B selects all non locked purchases older than a week

process A: UPDATE purchases SET ...;
process B: UPDATE purchases SET ...;

Örnek

Şöyle yaparız. Burada aynı anda birden fazla kişi bu cümleyi çalıştırsa bile, birbirlerini beklemedikleri için aslında bir anlamda işleri bölüşerek çalıştırma imkanı oluyor. Mesela 100 tane satır varsa ve Limit 10 ise, yani 10'luk batch'ler halinde çalıştırıyorsak, 4 kişi paralel çalışabilir.

UPDATE scheduled_tasks st1 SET picked = true, …
WHERE <instance> IN (
    SELECT <instance> FROM scheduled_tasks st2
    WHERE <due-condition>
    FOR UPDATE SKIP LOCKED
    LIMIT <limit>)
RETURNING st1.*