Worth noting that INSERTed tuples don’t require row locks in postgres because they are only visible to the transaction that creates them. One reason probably why Postgres doesn’t support read uncommitted isolation level.
This type of partitioning is used to divide a table into partitions based on a hash value. This type of partitioning is useful when you want to evenly distribute data across partitions, but don’t have a specific value to partition on.
Hash partitioning is similar to list partitioning, but instead of using a natural finite domain defined by the possible values of a column, it maps all possible values of a column to a finite list of possible values. The randomization used in hashing also ensures that partitions are of comparable sizes.
- I can never use incremental Id as the primary key.- Primary or foreign keys and unique keys do not make sense anymore. it is only applied to the shard database table. we need another solution to get this feature back.- The hashing function needs to be chosen very very carefully. e.g. For the above sample, when I add a machine, need to change the function into row_id%3. which is very very bad — re-shard is the cost is very very high: migration is complicated, potentially may lose data, and the downtime is unknown.- Need to rewrite many queries (into very simple ones). no subquery, no joins, and no aggregate functions can be used.- The transaction is hard to control. distributed system transaction is already complicated, sharding makes it even harder.- When a slow query (after a query from multiple machines then merges and sorts in memory) happens, my luck depends on caching or the engine in the middle like vitess or shardingsphere.- With above. horizontal sharding seems more suitable for NoSQL. but I don’t try until I have to.
Q: “Could you tell me how to query the recent 500th to 1000th updated records and order by modified date? ”
-- Create the main tableCREATE TABLE orders (id SERIAL PRIMARY KEY,order_number INTEGER,customer_id INTEGER,total_amount DECIMAL(10,2));-- Create the partition tablesCREATE TABLE orders_p1 (LIKE orders);CREATE TABLE orders_p2 (LIKE orders);CREATE TABLE orders_p3 (LIKE orders);-- Attach the partition tables to the main tableALTER TABLE orders ADD PARTITION BY HASH (order_number)(PARTITION orders_p1, PARTITION orders_p2, PARTITION orders_p3);
List partitioning is useful when you have a table that contains categorical data, and you want to partition the table based on the values in a specific column. For example, you can partition a table of customers by country, with each partition containing data for a specific country.
-- Create Partition TableCREATE TABLE sales ( id SERIAL, product_name TEXT, sale_date DATE ) PARTITION BY LIST (product_name); -- Creating individual partitions CREATE TABLE sales_electronics PARTITION OF sales FOR VALUES IN ('Laptop', 'TV', 'Smartphone'); CREATE TABLE sales_clothing PARTITION OF sales FOR VALUES IN ('Shirt', 'Pants', 'Dress'); -- Adding additional partitions later CREATE TABLE sales_furniture PARTITION OF sales FOR VALUES IN ('Chair', 'Table'); -- Insert Values in Parent Table INSERT INTO sales (product_name, sale_date) VALUES ('Laptop', '2023-06-01'), ('Shirt', '2023-06-02'), ('Chair', '2023-06-03'); -- Detach the partition Table ALTER TABLE sales DETACH PARTITION sales_furniture; -- Attach the partition Tables ALTER TABLE sales ATTACH PARTITION sales_furniture FOR VALUES IN ('Chair', 'Table');
-- create the customers tableCREATE TABLE customers (id SERIAL PRIMARY KEY,name VARCHAR(255),country VARCHAR(255),email VARCHAR(255));-- create the partition tablesCREATE TABLE customers_us (LIKE customers);CREATE TABLE customers_eu (LIKE customers);CREATE TABLE customers_ru (LIKE customers);...-- attach the partition tables to the customers tableALTER TABLE customers ADD PARTITION customers_us FOR VALUES IN ('US');ALTER TABLE customers ADD PARTITION customers_eu FOR VALUES IN('AT', 'BE', 'BG', 'HR', 'CY', 'CZ', 'DK', 'EE', 'FI', 'FR', 'DE', 'GR', 'HU','IE', 'IT', 'LV', 'LT', 'LU', 'MT', 'NL', 'PL', 'PT', 'RO', 'SK', 'SI', 'ES', 'SE');ALTER TABLE customers ADD PARTITION customers_ru FOR VALUES IN ('RU');...
The table is partitioned into “ranges’’ defined by a key column or set of columns, with no overlap between the ranges of values assigned to different partitions. This partitioning is perfect for time series data
In PostgreSQL, table partitioning is achieved by using the built-in table inheritance feature, where a table is split into multiple smaller tables called partitions. Each partition can have its indexes, constraints, and storage parameters.One of the most common uses of partitioning in PostgreSQL is range partitioning. This method divides a large table into smaller, more manageable pieces based on a range of values. The most frequent use case for this type of partitioning is to partition a table of time-based data, such as a table of sales data or a table of log data, by a date or timestamp column.
CREATE TABLE transactions (
transaction_id SERIAL PRIMARY KEY,
transaction_date DATE,
user_id INTEGER,
amount DECIMAL(10, 2),
...
)
PARTITION BY RANGE (transaction_date);
CREATE TABLE transactions_2022_01 PARTITION OF transactions
FOR VALUES FROM ('2022-01-01') TO ('2022-02-01');
CREATE TABLE transactions_2022_02 PARTITION OF transactions
FOR VALUES FROM ('2022-02-01') TO ('2022-03-01');-- Detach the partition for transactions in 2021 from the main table
ALTER TABLE transactions DETACH PARTITION transactions_2021;
-- Attach the detached partition to the archive table
ALTER TABLE transactions_archive ATTACH PARTITION transactions_2021;Move older partitions to an archive table: Once you have partitioned the main table, you can move older partitions to a separate archive table using a script or tool. For example, you can use the ALTER TABLE command to detach a partition from the main table and attach it to the archive table
CREATE TABLE mytable (id SERIAL PRIMARY KEY, created_at TIMESTAMP);
CREATE TABLE mytable_2019 PARTITION OF mytable FOR VALUES FROM ('2019–01–01')
TO ('2020–01–01');
CREATE TABLE mytable_2020 PARTITION OF mytable FOR VALUES FROM ('2020–01–01')
TO ('2021–01–01');
CREATE TABLE mytable_2021 PARTITION OF mytable FOR VALUES FROM ('2021–01–01')
TO ('2022–01–01');-- create the sales table CREATE TABLE sales ( id SERIAL PRIMARY KEY, date DATE, product VARCHAR(255), quantity INTEGER, price DECIMAL(10,2) ); -- create the partition tables CREATE TABLE sales_2022_Q1 (LIKE sales); CREATE TABLE sales_2022_Q2 (LIKE sales); CREATE TABLE sales_2022_Q3 (LIKE sales); CREATE TABLE sales_2022_Q4 (LIKE sales); -- attach the partition tables to the sales table ALTER TABLE sales ADD PARTITION sales_2022_Q1 CHECK (date >= '2022-01-01' AND date < '2022-04-01'); ALTER TABLE sales ADD PARTITION sales_2022_Q2 CHECK (date >= '2022-04-01' AND date < '2022-07-01'); ALTER TABLE sales ADD PARTITION sales_2022_Q3 CHECK (date >= '2022-07-01' AND date < '2022-10-01'); ALTER TABLE sales ADD PARTITION sales_2022_Q4 CHECK (date >= '2022-10-01' AND date < '2023-01-01');
INSERT INTO sales (id, date, quantity, price) VALUES (101, '2022-01-15', 5, 100.00); -- Retrieve sales data for January SELECT * FROM sales WHERE date >= '2022-01-01' AND date < '2022-02-01';
This is the main process that manages everything in Postgres. It creates a listener on the configured interfaces and port (default 5432). It is also responsible for forking other processes to do various tasks.
One more task of the postmaster process is to listen for incoming connections. When a new client appears, the postmaster spawns a servicing process (backend) for it. The client establishes a connection and begins a communication session with its server process. The session continues until the client disconnects or the connection is lost.
Besides the logical order, tablespaces define the physical location of the data. An actual tablespace is a filesystem directory. For example, you can locate the archive data on a slow HDD, but the hot data on a fast SDD.
The same tablespace may be used by a few databases at the same time. And a single database may store its data in a few tablespaces. It means that the logical and physical structures are independent.
Every database has its own default tablespace. It’s used for the creation of every object if the defaults are not changed. It also stores the system catalog objects.
On cluster initialization, two tablespaces are created:
- pg_default — located in PGDATA/base and used as default tablespace for all databases;
- pg_global — located in PGDATA/global and stores common objects for the whole cluster system catalog;
On used tablespace creation, any filesystem catalog may be specified. PostgreSQL creates a symlink to the specified catalog in PGDATA/pg_tblspc. By the way, all the PostgreSQL paths are relative and are calculated from PGDATA. It allows you to move PGDATA wherever you want.Örnek
CREATE TABLE IF NOT EXISTS public.factinternetsales_streaming(...)TABLESPACE pg_default;
All the table names in the system catalog have names starting with pg_ prefix, for example, pg_database. The column names are starting with a three-letter code that describes the table name, for example, datname.
All the system catalog tables have primary key columns with name oid having a type with the same name oid (object identifier).
If you leave the schema unspecified, Postgres defaults to a schema named public within the database.public isimli schema her veri tabanında vardır. Açıklaması şöyle
In the previous sections we created tables without specifying any schema names. By default such tables (and other objects) are automatically put into a schema named “public”. Every new database contains such a schema.
PostgreSQL has support for multiple schemas. We all typically end up using just the public schema for most use cases. But multiple schemas are a nice way to namespace your different application use cases since joining of data and establishing relationships between schemas is fairly easy.I tend to use schemas for reporting views for analytics and keep my primary table schema (public) noiseless.
Postgres 15 removes the global write privilege from the public schema. For example, if you are using a user which doesn't have superuser privileges, the CREATE statements on public will no longer work.
# this doesn't work anymore
CREATE table author (id integer, name text);
You will get a permission denied error going forward. This is good from a security pov as the public schema is ab(used) for dumping everything but is also exposed on the search_path by default.
Hasura supports read-only Postgres, so you if want to give access to public schema for just reads, it will continue to work as intended.
Here’s an example of how to use the built-in statistics collector to monitor a Postgres database:
- Enable the statistics collector: The statistics collector is disabled by default in Postgres, so you’ll need to enable it in your postgresql.conf file. Set the stats_collector parameter to on, and configure any other relevant settings (such as stats_temp_directory to specify where to store the collected data).
- Collect and analyze metrics: Once the statistics collector is enabled, Postgres will automatically collect metrics and store them in the pg_stat_* system tables. You can query these tables to retrieve information on query performance, database activity, and more. For example, you can use the pg_stat_database view to retrieve information on database-level activity, such as the number of connections, transactions, and queries per second.
Statistics are data collected by Postgres used to inform its selection of query plans. Out of the box, Postgres samples the possible values for each column of each table to create histograms and a list of the most common values (among other things). These are used to estimate how many rows will result from applying some set of filters to a table.
For larger tables, the planner can’t keep track of every single value a column holds. Instead, it samples the values of each column and uses those to make estimations. We can tweak how much sampling Postgres does for each column on each table with
ALTER TABLE table ALTER column SET STATISTICS {-1 ..10000}
where -1 sets it to the default value of 100 (docs). This number sets how many buckets are used in the histogram and how many of the most common values are stored.
The downsides to increasing the statistics for a column are that more data must be stored in pg_statistic and running ANALYZE on the column's table takes longer.
1. So WAL is much smaller than the actual data files, so we can flush them relatively faster.
2. They are also sequential unlike data pages changes which are random.
WALs are not just smaller but also need fewer IOs. You have to write out a full sector to disk even if only one bit in it changed. WALs are append only and you only write to the end of the file. If you modify 4 rows that data may be in 4 sectors but may only need 1 WAL sector.And even if you write multiple WAL sectors they are sequential which is faster than doing random IOs. The advantage was massive with spindle drives but even on SSDs it is faster.
PagesBoth data and indexes live as fixed-size pages in data files on disk. Databases may do it differently but essentially every table/collection is stored as an array of fixed-size pages in a file. The database can tell which offset to seek to from the page number and the page length is used to length of bytes to read. This allows the database to issue an operating system read and get that exact page in memory.#include <unistd.h>ssize_t read(int fd, void *buf, size_t count);That single I/O to read a page will get you not one row but many that is because a page will have many rows/documents. And similarly, to update a column in one row in the page you have to flush the entire page to disk even though only few bytes might have changed.This is key to understanding database performance. You want pages to remain “dirty” and hopefully receive a lot of writes so we can flush it once to disk to minimize I/O.WALI can start a transaction that updates a row in page 0, inserts a new row in page 10 and deletes a row from page 1 and to commit my transaction I can write the three pages to disk. Two problems with this approach. Small writes lead to massive I/Os which slows down commits. The second problem and the worse is if the database crashed midway of writing the pages, we have some pages written some are not and we lose atomicity. In addition we have no reference on startup after crash to remove pages belonging to transactions that crashed and must be rollbacked as we don’t even know what the original page looked like.Meet WAL or write-ahead log. How about we do this instead? In addition to updating pages in memory we also record the changes transactions make in an append log and we flush the log to disk when the transaction commits. We know flushing the log is going to be fast because the changes are much smaller than writing the full pages of data files and indexes. And yes we can safely leave the updated data pages in memory and not flush them to disk saving massive I/Os, I’ll explain why later.By “writing-ahead” in this log file, the data files on disk are technically left behind and older than what is in the log file, this is where the name write-ahead in WAL comes from.
As the database starts back up, the file is out of date we can’t just pull it on memory and have clients read them, the WAL is the source of truth, we need to REDO the changes in the WAL back on the data files, and during that process nothing is allowed to read (otherwise we get corruption). The database startup time slows down the more out of sync the data files are from the WAL (many writes has happened but the data files were not updated for a long time).
I know what you’re thinking, what if we crashed midway through writing the WAL? That is also fine because we know exactly what transaction wrote those WAL changes and upon startup we will clean up the WAL records that belong to transactions that didn’t commit, we effectively roll it back.
For example if you are in the middle of a transaction and the database crashed, we consider the transaction rolled-back by default, so WAL entries flushed by this uncommitted transaction will be discarded.
Materialized views are precomputed views of data that are stored on disk and can be accessed more quickly than regular views. By using materialized views, queries can be executed more efficiently, especially for complex aggregations and joins.
Materialized views are typically used in situations where a complex query needs to be executed frequently or where the query result takes a long time to compute. By precomputing the result and storing it in a materialized view (virtual tables), queries can be executed more quickly and with less overhead. PostgreSQL, Microsoft SQL Server, RisingWave or Materialize support materialized views with automatic updates.
CREATE MATERIALIZED VIEW myview ASSELECT mycolumn, COUNT(*) AS countFROM mytableGROUP BY mycolumn;