Index: doc/src/sgml/config.sgml =================================================================== RCS file: /home/neilc/postgres/cvs_root/pgsql/doc/src/sgml/config.sgml,v retrieving revision 1.33 diff -c -r1.33 config.sgml *** doc/src/sgml/config.sgml 26 Oct 2005 12:55:07 -0000 1.33 --- doc/src/sgml/config.sgml 1 Nov 2005 22:54:16 -0000 *************** *** 1974,1984 **** ! When this parameter is on, the planner compares query ! conditions with table CHECK constraints, and omits scanning tables ! where the conditions contradict the constraints. (Presently ! this is done only for child tables of inheritance scans.) For ! example: CREATE TABLE parent(key integer, ...); --- 1974,1984 ---- ! When this parameter is on, the planner compares ! query conditions with table CHECK constraints, and ! omits scanning tables where the conditions contradict the ! constraints. (Presently this is done only for child tables of ! inheritance scans.) For example: CREATE TABLE parent(key integer, ...); *************** *** 1988,2010 **** SELECT * FROM parent WHERE key = 2400; ! With constraint exclusion enabled, this SELECT will not scan ! child1000 at all. This can improve performance when ! inheritance is used to build partitioned tables. ! Currently, constraint_exclusion defaults to ! off, because it risks incorrect results if ! query plans are cached --- if a table constraint is changed or dropped, ! the previously generated plan might now be wrong, and there is no ! built-in mechanism to force re-planning. (This deficiency will ! probably be addressed in a future ! PostgreSQL release.) Another reason ! for keeping it off is that the constraint checks are relatively expensive, and in many circumstances will yield no savings. ! It is recommended to turn this on only if you are actually using ! partitioned tables designed to take advantage of the feature. --- 1988,2017 ---- SELECT * FROM parent WHERE key = 2400; ! With constraint exclusion enabled, this SELECT ! will not scan child1000 at all. This can ! improve performance when inheritance is used to build ! partitioned tables. ! Currently, constraint_exclusion is disabled by ! default because it risks incorrect results if query plans are ! cached — if a table constraint is changed or dropped, ! the previously generated plan might now be wrong, and there is ! no built-in mechanism to force re-planning. (This deficiency ! will probably be addressed in a future ! PostgreSQL release.) Another reason for ! keeping it off is that the constraint checks are relatively expensive, and in many circumstances will yield no savings. ! It is recommended to turn this on only if you are actually ! using partitioned tables designed to take advantage of the ! feature. ! ! ! ! Refer to for more information ! on using constraint exclusion and partitioning. Index: doc/src/sgml/ddl.sgml =================================================================== RCS file: /home/neilc/postgres/cvs_root/pgsql/doc/src/sgml/ddl.sgml,v retrieving revision 1.45 diff -c -r1.45 ddl.sgml *** doc/src/sgml/ddl.sgml 23 Oct 2005 19:29:49 -0000 1.45 --- doc/src/sgml/ddl.sgml 1 Nov 2005 23:10:19 -0000 *************** *** 398,403 **** --- 398,410 ---- ensure that a column does not contain null values, the not-null constraint described in the next section can be used. + + + Check constraints can also be used to enhance performance with + very large tables, when used in conjunction with the parameter. This is discussed + in more detail in . + *************** *** 1040,1058 **** Inheritance ! This section needs to be rethought. Some of the ! information should go into the following chapters. ! Let's create two tables. The capitals table contains ! state capitals which are also cities. Naturally, the ! capitals table should inherit from cities. CREATE TABLE cities ( name text, population float, ! altitude int -- (in ft) ); CREATE TABLE capitals ( --- 1047,1085 ---- Inheritance ! ! not-null constraint ! ! ! ! constraint ! NOT NULL ! ! ! ! PostgreSQL implements table inheritance ! which can be a useful tool for database designers. The SQL:2003 ! standard optionally defines type inheritance which differs in many ! respects from the features described here. ! ! Let's start with an example: suppose we are trying to build a data ! model for cities. Each state has many cities, but only one ! capital. We want to be able to quickly retrieve the capital city ! for any particular state. This can be done by creating two tables, ! one for state capitals and one for cities that are not ! capitals. However, what happens when we want to ask for data about ! a city, regardless of whether it is a capital or not? The ! inheritance feature can help to resolve this problem. We define the ! capitals table so that it inherits from ! cities: CREATE TABLE cities ( name text, population float, ! altitude int -- in feet ); CREATE TABLE capitals ( *************** *** 1060,1083 **** ) INHERITS (cities); ! In this case, a row of capitals inherits all ! attributes (name, population, and altitude) from its parent, cities. State ! capitals have an extra attribute, state, that shows their state. In ! PostgreSQL, a table can inherit from zero or ! more other tables, and a query can reference either all rows of a table or ! all rows of a table plus all of its descendants. ! ! ! ! The inheritance hierarchy is actually a directed acyclic graph. ! ! ! For example, the following query finds the names of all cities, ! including state capitals, that are located at an altitude ! over 500ft: SELECT name, altitude --- 1087,1105 ---- ) INHERITS (cities); ! In this case, a row of capitals inherits ! all the columns of its parent table, cities. State ! capitals have an extra attribute, state, that shows ! their state. ! In PostgreSQL, a table can inherit from ! zero or more other tables, and a query can reference either all ! rows of a table or all rows of a table plus all of its descendants. ! For example, the following query finds the names of all cities, ! including state capitals, that are located at an altitude over ! 500ft: SELECT name, altitude *************** *** 1097,1105 **** ! On the other hand, the following query finds ! all the cities that are not state capitals and ! are situated at an altitude over 500ft: SELECT name, altitude --- 1119,1126 ---- ! On the other hand, the following query finds all the cities that ! are not state capitals and are situated at an altitude over 500ft: SELECT name, altitude *************** *** 1110,1169 **** -----------+---------- Las Vegas | 2174 Mariposa | 1953 ! ! Here the ONLY before cities indicates that the query should ! be run over only cities and not tables below cities in the ! inheritance hierarchy. Many of the commands that we ! have already discussed -- SELECT, ! UPDATE and DELETE -- ! support this ONLY notation. - - - Inheritance and Permissions - - Because permissions are not inherited automatically a user attempting to access - a parent table must either have at least the same permission for the child table - or must use the ONLY notation. If creating a new inheritance - relationship in an existing system be careful that this does not create problems. - - - ! Deprecated ! In previous versions of PostgreSQL, the ! default behavior was not to include child tables in queries. This was ! found to be error prone and is also in violation of the SQL:2003 ! standard. Under the old syntax, to get the sub-tables you append ! * to the table name. ! For example ! ! SELECT * from cities*; ! ! You can still explicitly specify scanning child tables by appending ! *, as well as explicitly specify not scanning child tables by ! writing ONLY. But beginning in version 7.1, the default ! behavior for an undecorated table name is to scan its child tables ! too, whereas before the default was not to do so. To get the old ! default behavior, set the configuration option ! SQL_Inheritance to off, e.g., ! ! SET SQL_Inheritance TO OFF; ! ! or add a line in your postgresql.conf file. ! In some cases you may wish to know which table a particular row ! originated from. There is a system column called ! tableoid in each table which can tell you the ! originating table: SELECT c.tableoid, c.name, c.altitude --- 1131,1185 ---- -----------+---------- Las Vegas | 2174 Mariposa | 1953 ! ! Here the ONLY keyword indicates that the query ! should apply only to cities, and not any tables ! below cities in the inheritance hierarchy. Many ! of the commands that we have already discussed — ! SELECT, UPDATE and ! DELETE — support the ! ONLY keyword. ! Inheritance and Permissions ! Because permissions are not inherited automatically, a user ! attempting to access a parent table must either have at least the ! same permission for the child table or must use the ! ONLY notation. If creating a new inheritance ! relationship in an existing system be careful that this does not ! create problems. ! Inheritance does not automatically propogate data from ! INSERT or COPY commands to ! other tables in the inheritance hierarchy. In our example, the ! following INSERT statement will fail: ! ! INSERT INTO cities ! (name, population, altitude, state) ! VALUES ('New York', NULL, NULL, 'NY'); ! ! We might hope that the data would be somehow routed to the ! capitals table, though this does not happen. If ! the child has no locally defined columns, then it is possible to ! route data from the parent to the child using a rule, see . This is not possible with the above ! INSERT statement because the state column ! does not exist on both parent and child tables. ! ! ! ! In some cases you may wish to know which table a particular row ! originated from. There is a system column called ! tableoid in each table which can tell you the ! originating table: SELECT c.tableoid, c.name, c.altitude *************** *** 1200,1220 **** cities | Mariposa | 1953 capitals | Madison | 845 - ! A table can inherit from more than one parent table, in which case it has ! the union of the columns defined by the parent tables (plus any columns ! declared specifically for the child table). ! A serious limitation of the inheritance feature is that indexes (including ! unique constraints) and foreign key constraints only apply to single ! tables, not to their inheritance children. This is true on both the ! referencing and referenced sides of a foreign key constraint. Thus, ! in the terms of the above example: --- 1216,1279 ---- cities | Mariposa | 1953 capitals | Madison | 845 ! As shown above, a child table may locally define columns as well as ! inheriting them from their parents. However, a locally defined ! column cannot override the datatype of an inherited column of the ! same name. A table can inherit from a table that has itself ! inherited from other tables. A table can also inherit from more ! than one parent table, in which case it inherits the union of the ! columns defined by the parent tables. Inherited columns with ! duplicate names and datatypes will be merged so that only a single ! column is stored. ! ! ! ! Table inheritance can currently only be defined using the ! statement. The related statement CREATE TABLE ... AS ! SELECT does not allow inheritance to be specified. There ! is no way to add an inheritance link to make an existing table into ! a child table. Similarly, there is no way to remove an inheritance ! link from a child table once it has been defined, other than using ! DROP TABLE. A parent table cannot be dropped ! while any of its children remain. If you wish to remove a table and ! all of its descendants, then you can do so using the ! CASCADE option of the statement. ! ! ! ! Check constraints can be defined on tables within an inheritance ! hierarchy. All check constraints on a parent table are ! automatically inherited by all of their children. It is currently ! possible to inherit mutually exclusive check constraints, but that ! definition quickly shows itself since all attempted row inserts ! will be rejected. ! ! ! ! will ! propogate any changes in data definition on columns or check ! constraints down the inheritance hierarchy. Again, dropping ! columns or constraints on parent tables is only possible when using ! the CASCADE option. ALTER ! TABLE follows the same rules for duplicate column merging ! and rejection that apply during CREATE TABLE. ! Both parent and child tables can have primary and foreign keys, so ! that they can take part normally on both the referencing and ! referenced sides of a foreign key constraint. Indexes may be ! defined on any of these columns whether or not they are inherited. ! However, a serious current limitation of the inheritance feature is ! that indexes (including unique constraints) and foreign key ! constraints only apply to single tables and do not also index their ! inheritance children. This is true on both sides of a foreign key ! constraint. Thus, in the terms of the above example: *************** *** 1236,1244 **** Similarly, if we were to specify that cities.name REFERENCES some other table, this constraint would not automatically propagate to ! capitals. In this case you could work around it by ! manually adding the same REFERENCES constraint to ! capitals. --- 1295,1305 ---- Similarly, if we were to specify that cities.name REFERENCES some other table, this constraint would not automatically propagate to ! capitals. However, it is possible to set up a ! foreign key such as capitals.name ! REFERENCES states.name. ! So it is possible to workaround this restriction by manually adding ! foreign keys to each child table. *************** *** 1254,1260 **** --- 1315,1870 ---- These deficiencies will probably be fixed in some future release, but in the meantime considerable care is needed in deciding whether inheritance is useful for your problem. + + + + Deprecated + + In previous versions of PostgreSQL, the + default behavior was not to include child tables in queries. This was + found to be error prone and is also in violation of the SQL:2003 + standard. Under the old syntax, to get the sub-tables you append + * to the table name. For example: + + SELECT * from cities*; + + You can still explicitly specify scanning child tables by + appending *, as well as explicitly specify not + scanning child tables by writing ONLY. But + beginning in version 7.1, the default behavior for an undecorated + table name is to scan its child tables too, whereas before the + default was not to do so. To get the old default behavior, + disable the configuration + option. + + + + + + + Constraint Exclusion and Partitioning + + + partitioning + + + + constraint exclusion + + + + PostgreSQL supports basic table + partitioning. This section describes why and how you can implement + this as part of your database design. + + + + Overview + + + Currently, partitioning is implemented in conjunction with table + inheritance only, though using fully SQL:2003 compliant syntax. + Table inheritance allows tables to be split into partitions, and + constraint exclusion allows partitions to be selectively combined + as needed to satisfy a particular SELECT + statement. You should be familiar with inheritance (see ) before attempting to implement + partitioning. + + + + Partitioning can provide several benefits: + + + + Query performance can be improved dramatically for certain kinds + of queries without the need to maintain costly indexes. + + + + + + Insert performance can be improved by breaking down a large + index into multiple pieces. When an index no longer fits easily + in memory, both read and write operations on the index take + progressively more disk accesses. + + + + + + Bulk deletes may be avoided altogether by simply removing one of the + partitions, if that requirement is planned into the partitioning design. + + + + + + Seldom-used data can be migrated to cheaper and slower storage media. + + + + + The benefits will normally be worthwhile only when a data table would + otherwise be very large. That is for you to judge, though would not + usually be lower than the size of physical RAM on the database server. + + + + In PostgreSQL &version;, the following + partitioning types are supported: + + + + + "Range Partitioning" where the table is partitioned along a + "range" defined by a single column or set of columns, with no + overlap between partitions. Examples might be a date range or a + range of identifiers for particular business objects. + + + + + + "List Partitioning" where the table is partitioned by + explicitly listing which values relate to each partition. + + + + + Hash partitioning is not currently supported. + + + + + Implementing Partitioning + + + Partitioning a table is a straightforward process. There + are a wide range of options for you to consider, so judging exactly + when and how to implement partitioning is a more complex topic. We + will address that complexity primarily through the examples in this + section. + + + + To use partitioning, do the following: + + + + Create the master table, from which all of the + partitions will inherit. + + + This table will contain no data. Do not define any + constraints or keys on this table, unless you intend them to + be applied equally to all partitions. + + + + + + Create several child tables that inherit from + the master table. + + + + We will refer to the child tables as partitions, though they + are in every way just normal PostgreSQL + tables. + + + + + + Add table constraints to define the allowed values in each partition. + + + Only clauses of the form [COLUMN] [OPERATOR] [CONSTANT(s)] will be used + for constraint exclusion. Simple examples would be: + + CHECK ( x = 1 ) + CHECK ( county IN ('Oxfordshire','Buckinghamshire','Warwickshire')) + CHECK ( outletID BETWEEN 1 AND 99 ) + + + These can be linked together with boolean operators AND and OR to + form complex constraints. Note that there is no difference in syntax + between Range and List Partitioning mechanisms; those terms are + descriptive only. Ensure that the set of values in each child table + do not overlap. + + + + + + Add any other indexes you want to the partitions, bearing in + mind that it is always more efficient to add indexes after + data has been bulk loaded. + + + + + + Optionally, define a rule or trigger to redirect modifications + of the master table to the appropriate partition. + + + + + + + + For example, suppose we are constructing a database for a large + ice cream company. The company measures peak temperatures every + day as well as ice cream sales in each region. They have two + tables: + + + CREATE TABLE cities ( + id int not null, + name text not null, + altitude int -- in feet + ); + + CREATE TABLE measurement ( + city_id int not null, + logdate date not null, + peaktemp int, + unitsales int + ); + + + To reduce the amount of old data that needs to be stored, we + decide to only keep the most recent 3 years worth of data. At the + beginning of each month we remove the oldest month's data. + + + + Most queries just access the last week, month or quarter's data, + since we need to keep track of sales. As a result we have a large table, + yet only the most frequent 10% is accessed. Most of these queries + are online reports for various levels of management. These queries access + much of the table, so it is difficult to build enough indexes and at + the same time allow us to keep loading all of the data fast enough. + Yet, the reports are online so we need to respond quickly. + + + + In this situation we can use partitioning to help us meet all of our + different requirements for the measurements table. Following the + steps outlined above, partitioning can be enabled as follows: + + + + + + + The measurement table is our master table. + + + + + + Next we create one partition for each month using inheritance: + + + CREATE TABLE measurement_yy04mm02 ( ) INHERITS (measurement); + CREATE TABLE measurement_yy04mm03 ( ) INHERITS (measurement); + ... + CREATE TABLE measurement_yy05mm11 ( ) INHERITS (measurement); + CREATE TABLE measurement_yy05mm12 ( ) INHERITS (measurement); + CREATE TABLE measurement_yy06mm01 ( ) INHERITS (measurement); + + + Each of the partitions are complete tables in their own right, + but they inherit their definition from the measurement table. + + + + This solves one of our problems: deleting old data. Each + month, all we need to do is perform a DROP + TABLE on the oldest table and create a new table to + insert into. + + + + + + We now add non-overlapping table constraints, so that our + table creation script becomes: + + + CREATE TABLE measurement_yy04mm02 ( + CHECK ( logdate >= DATE '2004-02-01' AND logdate < DATE '2004-03-01' ) + ) INHERITS (measurement); + CREATE TABLE measurement_yy04mm03 ( + CHECK ( logdate >= DATE '2004-03-01' AND logdate < DATE '2004-04-01' ) + ) INHERITS (measurement); + ... + CREATE TABLE measurement_yy05mm11 ( + CHECK ( logdate >= DATE '2005-11-01' AND logdate < DATE '2005-12-01' ) + ) INHERITS (measurement); + CREATE TABLE measurement_yy05mm12 ( + CHECK ( logdate >= DATE '2005-12-01' AND logdate < DATE '2006-01-01' ) + ) INHERITS (measurement); + CREATE TABLE measurement_yy06mm01 ( + CHECK ( logdate >= DATE '2006-01-01' AND logdate < DATE '2006-02-01' ) + ) INHERITS (measurement); + + + + + + + We choose not to add further indexes at this time. + + + + + + Data will be added each day to the latest partition. This + allows us to set up a very simple rule to insert data. We must + redefine this each month so that it always points to the + current partition. + + + CREATE OR REPLACE RULE measurement_current_partition AS + ON INSERT + TO measurement + DO INSTEAD + INSERT INTO measurement_yy06mm01 VALUES ( NEW.city_id, + NEW.logdate, + NEW.peaktemp, + NEW.unitsales ); + + + We might want to insert data and have the server automatically + locate the partition into which the row should be added. We + could do this with a more complex set of rules as shown below. + + + CREATE RULE measurement_insert_yy04mm02 AS + ON INSERT + TO measurement WHERE + ( logdate >= DATE '2004-02-01' AND logdate < DATE '2004-03-01' ) + DO INSTEAD + INSERT INTO measurement_yy04mm02 VALUES ( NEW.city_id, + NEW.logdate, + NEW.peaktemp, + NEW.unitsales ); + ... + CREATE RULE measurement_insert_yy05mm12 AS + ON INSERT + TO measurement WHERE + ( logdate >= DATE '2005-12-01' AND logdate < DATE '2006-01-01' ) + DO INSTEAD + INSERT INTO measurement_yy05mm12 VALUES ( NEW.city_id, + NEW.logdate, + NEW.peaktemp, + NEW.unitsales ); + CREATE RULE measurement_insert_yy06mm01 AS + ON INSERT + TO measurement WHERE + ( logdate >= DATE '2006-01-01' AND logdate < DATE '2006-02-01' ) + DO INSTEAD + INSERT INTO measurement_yy06mm01 VALUES ( NEW.city_id, + NEW.logdate, + NEW.peaktemp, + NEW.unitsales ); + + + Note that the WHERE clause in each rule + exactly matches those used for the CHECK + constraints on each partition. + + + + + + + As we can see, a complex partitioning scheme could require a + substantial amount of DDL. In the above example we would be + creating a new partition each month, so it may be wise to write a + script that generates the required DDL automatically. + + + + The following caveats apply: + + + + There is currently no way to specify that all of the + CHECK constraints are mutually + exclusive. Care is required by the database designer. + + + + + + There is currently no way to specify that rows may not be + inserted into the master table. A CHECK + constraint on the master table will be inherited by all child + tables, so that cannot not be used for this purpose. + + + + + + For some datatypes you must explicitly coerce the constant values + into the datatype of the column. The following constraint will + work if x is an INTEGER datatype, but not if x is BIGINT datatype. + + CHECK ( x = 1 ) + + For BIGINT we must use a constraint like: + + CHECK ( x = 1::bigint ) + + The issue is not restricted to BIGINT datatypes but can occur whenever + the default datatype of the constant does not match the datatype of + the column to which it is being compared. + + + + + + Partitioning can also be arranged using a UNION + ALL view: + + + CREATE VIEW measurement AS + SELECT * FROM measurement_yy04mm02 + UNION ALL SELECT * FROM measurement_yy04mm03 + ... + UNION ALL SELECT * FROM measurement_yy05mm11 + UNION ALL SELECT * FROM measurement_yy05mm12 + UNION ALL SELECT * FROM measurement_yy06mm01; + + + However, constraint exclusion is currently not supported for + partitioned tables defined in this manner. + + + + + + + + Constraint Exclusion in Queries + + + Partitioning can be used to improve query performance when used in + conjunction with constraint exclusion. As an example: + + + SET constraint_exclusion=true; + SELECT count(*) FROM measurement WHERE logdate >= DATE '2006-01-01'; + + + Without constraint exclusion, the above query would scan each of + the partitions of the measurement table. With constraint + exclusion, the planner will examine each of the constraints and + try to prove that each of the partitions needs to be involved in + the query. If the planner is able to refute that for any + partition, it excludes the partition from the query plan. + + + + You can use the EXPLAIN command to show the difference + between a plan with constraint_exclusion on and a plan + with it off. + + + SET constraint_exclusion=false; + EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2006-01-01'; + + QUERY PLAN + ----------------------------------------------------------------------------------------------- + Aggregate (cost=158.66..158.68 rows=1 width=0) + -> Append (cost=0.00..151.88 rows=2715 width=0) + -> Seq Scan on measurement (cost=0.00..30.38 rows=543 width=0) + Filter: (logdate >= '2006-01-01'::date) + -> Seq Scan on measurement_yy04mm02 measurement (cost=0.00..30.38 rows=543 width=0) + Filter: (logdate >= '2006-01-01'::date) + -> Seq Scan on measurement_yy04mm03 measurement (cost=0.00..30.38 rows=543 width=0) + Filter: (logdate >= '2006-01-01'::date) + ... + -> Seq Scan on measurement_yy05mm12 measurement (cost=0.00..30.38 rows=543 width=0) + Filter: (logdate >= '2006-01-01'::date) + -> Seq Scan on measurement_yy06mm01 measurement (cost=0.00..30.38 rows=543 width=0) + Filter: (logdate >= '2006-01-01'::date) + + + Now when we enable constraint exclusion, we get a significantly + reduced plan but the same result set: + + + SET constraint_exclusion=true; + EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2006-01-01'; + QUERY PLAN + ----------------------------------------------------------------------------------------------- + Aggregate (cost=63.47..63.48 rows=1 width=0) + -> Append (cost=0.00..60.75 rows=1086 width=0) + -> Seq Scan on measurement (cost=0.00..30.38 rows=543 width=0) + Filter: (logdate >= '2006-01-01'::date) + -> Seq Scan on measurement_yy06mm01 measurement (cost=0.00..30.38 rows=543 width=0) + Filter: (logdate >= '2006-01-01'::date) + + + Don't forget that you still need to run ANALYZE + on each partition individually. A command like this + + ANALYZE measurement; + + + only affects the master table. + + + + No indexes are required to use constraint exclusion. The + partitions should be defined with appropriate CHECK + constraints. These are then compared with the predicates of the + SELECT query to determine which partitions must be + scanned. + + + + The following caveats apply to this release: + + + + Constraint exclusion only works when the query directly matches + a constant. A constant bound to a parameterised query will not + work in the same way since the plan is fixed and would need to + vary with each execution. Also, stable constants such as + CURRENT_DATE may not be used, since these are + constant only for during the execution of a single query. Join + conditions will not allow constraint exclusion to work either. + + + + + + UPDATEs and DELETEs against the master table do not perform + constraint exclusion. + + + + + + All constraints on all partitions of the master table are considered for + constraint exclusion, so large numbers of partitions are likely to + increase query planning time considerably. + + + + + + + + *************** *** 1530,1536 **** ! Privileges --- 2140,2146 ---- ! Privileges *************** *** 1953,1959 **** schema. To allow that, the CREATE privilege on the schema needs to be granted. Note that by default, everyone has CREATE and USAGE privileges on ! the schema public. This allows all users that are able to connect to a given database to create objects in its public schema. If you do --- 2563,2569 ---- schema. To allow that, the CREATE privilege on the schema needs to be granted. Note that by default, everyone has CREATE and USAGE privileges on ! the schema public. This allows all users that are able to connect to a given database to create objects in its public schema. If you do