2

I've read a lot about this issue.. (also read this: Tables with no Primary Key) it seems like there is no reason to use tables with no PK. so why does PostgreSQL allows it? can you give an example when it's good idea to not indicate PK?

Community
  • 1
  • 1
John
  • 1,724
  • 6
  • 25
  • 48
  • Probably to reduce resources usage in cases when table is either too little to use indices or so frequently appends data that indexing impacts performance. Indexing isn't free both in terms of CPU/RAM and disk space. – keltar Jun 08 '15 at 06:15

3 Answers3

3

I think the answer to your question lies in trying to understand what are the drawbacks of having a Primary-Key (PK) in the first place.

One obvious 'drawback' (depending on how you see it) in maintaining a PK is that it has its own overhead during an INSERT. So, in order to increase INSERT performance (assuming for e.g. the sample case is a logging table, where Querying is done offline) I would remove all Constraints / PK if possible and definitely would increase table performance. You may argue that pure logging should be done outside the DB (in a noSQL DB such as Cassandra etc.) but then again at least its possible in PostgreSQL.

Robins Tharakan
  • 2,209
  • 19
  • 17
2

A primary key is a special form of a unique constraint. A unique constraint is always backed up by an index. And the disadvantage of an index is that it takes time to update. Tables with an index have lower update, delete and insert performance.

So if you have a table that has a lot of modifications, and few queries, you can improve performance by omitting the primary key.

Andomar
  • 232,371
  • 49
  • 380
  • 404
0

AFAIK, the primary key is primarily needed for the relationships between tables as a foreign key. If you have a table that is not linked to anything you don't need a primary key. In Excel spreadsheets there're no primary keys but a spreadsheet is not a relational database.

Alex Ivanov
  • 695
  • 4
  • 6
  • 1
    not so true as excel numbering all rows with unique number. The fact that you as a user doesn't indicate PK doesn't mean that the excel won't do it for you. In SQL the is much more freedom as you are a programmer and not a "mindless" user. – John Jun 08 '15 at 05:20
  • 1
    Not so true as any table in a db has its row numbering. But row numbers are not primary keys which are the unique identifiers for the whole set of information spread along the columns. Row numbers are just row numbers, no more. – Alex Ivanov Jun 08 '15 at 05:28
  • You claim that you can use excel as simple data storage but in any given time you can use each tuple for quering... use it to get information from other tuples or even from other spreadsheets. if it was indeed a simple table with no keys that would have take forever. When you enter "=E40" it gives you result instantly. it doesn't do any search. that is a Key behavior. – John Jun 08 '15 at 05:31
  • Have you ever tried sorting in Excel? Row numbers stay the same all the time but the content of the rows looks differently. Why? Row numbers indicate just how many rows a table contains. They are not primary keys. – Alex Ivanov Jun 08 '15 at 05:42
  • Still behind the mechanism there is a pk for each row. otherwise all actions there would have take forever. keep in mind that excel is used for calculation and not for displaying text as word. But i will look into your idea... somehow it feels there is more into it than just saving text. – John Jun 08 '15 at 05:56
  • You said =E40? I put in sheet2 E40 = sheet1 A1 which is "1". After sorting DES my sheet1 A1 became "40" and know what? Sheet2 E40 became "40", too. Why? That's how it works. But you keep believing whatever suits you. – Alex Ivanov Jun 08 '15 at 06:10
  • 1
    @AlexIvanov: SQL tables are unordered sets. They really do not have row numbers. A query like `select * from table1` can return rows in a different order every time you run it, and on a database with parallelism, it typically will. For Postgres, the order will typically change after you backup and restore the database. – Andomar Jun 08 '15 at 06:16
  • You said it yourself, excel is not a RDBMS. Rownum in excel is closest thing it have to PK in RDBMS, but operations (ordering included) modify source data instead of result. In theory you can reset PKs in RDBMS table too to match some sorting criteria, but if you do it usually reflects bad practices. – keltar Jun 08 '15 at 06:23
  • Come on! SQL is a query language and not related to any table. Row or column numbers are just indexes of the table. They are not related to any row field unlike primary keys. You can't use row numbers as foreign keys in a different table because the row content of this table can change. If it does the primary key's ID will stay the same as it is supposed. But you can put primary keys into a standalone table, it won't hurt and be of any use, either. – Alex Ivanov Jun 08 '15 at 06:57