6 posts about #database

Multilevel Index

Suppose we have an index on id column for a table with 10 million rows. The index will eventually become too large that can’t be fit in memory, so we would have to store it somewhere in disk blocks.

The search for an entry in the index would require several disk blocks access. One way to improve this is to use multilevel index. We treat the index just as a database table, and construct another index (outer index) on this index (inner index).

We can repeat this process as many times as needed, until the outer index has small size that can be kept in memory. This would reduce the number of disk accesses significantly.

image.png

References: https://www.cs.uct.ac.za/mit_notes/database/htmls/chp11.html#multilevel-indexes

How to start and stop PostgreSQL server?

1. On MacOS:

If you installed PostgreSQL via Homebrew:

  • To start manually: pg_ctl -D /usr/local/var/postgres start
  • To stop manually: pg_ctl -D /usr/local/var/postgres stop
  • To start PostgreSQL server now and relaunch at login:
    brew services start postgresql
  • And stop PostgreSQL: brew services stop postgresql

2. On Windows:

First, you need to find the PostgreSQL database directory, it can be something like C:\Program Files\PostgreSQL\10.4\data. Then open Command Prompt and execute this command:

  • To start the server: pg_ctl -D "C:\Program Files\PostgreSQL\9.6\data" start
  • To stop the server: pg_ctl -D "C:\Program Files\PostgreSQL\9.6\data" stop
  • To restart the server: pg_ctl -D "C:\Program Files\PostgreSQL\9.6\data" restart

3. On Linux:

  • Start the PostgreSQL server: sudo service postgresql start
  • Stop the PostgreSQL server: sudo service postgresql stop

Link reference:

https://tableplus.com/blog/2018/10/how-to-start-stop-restart-postgresql-server.html

FASTER INSERTION WITH COPY

COPY results will be faster than INSERT due to they don’t have to do round trip, PostgreSQL doesn’t have to do planning and executing multiple INSERT statements.

COPY table_name [ ( column_name [, ...] ) ]
    FROM { 'filename' | STDIN }
    [ [ WITH ] ( option [, ...] ) ]

COPY { table_name [ ( column_name [, ...] ) ] | ( query ) }
    TO { 'filename' | STDOUT }
    [ [ WITH ] ( option [, ...] ) ]

where option can be one of:

    FORMAT format_name
    OIDS [ boolean ]
    DELIMITER 'delimiter_character'
    NULL 'null_string'
    HEADER [ boolean ]
    QUOTE 'quote_character'
    ESCAPE 'escape_character'
    FORCE_QUOTE { ( column_name [, ...] ) | * }
    FORCE_NOT_NULL ( column_name [, ...] )
    ENCODING 'encoding_name'