PostgreSQL #

PostgreSQL is the most feature-rich open-source relational database management system — supporting native JSON data types, arrays, full-text search, CTEs (Common Table Expressions), window functions, and many extensions like PostGIS for geospatial data. PostgreSQL is very popular among modern Kotlin developers, especially for backends needing high reliability without license costs. Compared to MySQL, PostgreSQL is stricter about SQL standards, richer in data types, and has a more advanced transaction system. Compared to Oracle, PostgreSQL is free and open-source with features getting increasingly close. This article covers connections, CRUD, distinctive PostgreSQL data types, the Exposed ORM, and the features that make PostgreSQL the primary choice for Kotlin production projects.

Setup and Dependencies #

// build.gradle.kts
dependencies {
    // PostgreSQL JDBC Driver (PgJDBC)
    implementation("org.postgresql:postgresql:42.7.3")

    // HikariCP
    implementation("com.zaxxer:HikariCP:5.1.0")

    // Exposed ORM
    implementation("org.jetbrains.exposed:exposed-core:0.49.0")
    implementation("org.jetbrains.exposed:exposed-dao:0.49.0")
    implementation("org.jetbrains.exposed:exposed-jdbc:0.49.0")
    implementation("org.jetbrains.exposed:exposed-java-time:0.49.0")
    implementation("org.jetbrains.exposed:exposed-json:0.49.0")  // for JSONB

    // Flyway
    implementation("org.flywaydb:flyway-core:10.10.0")
    implementation("org.flywaydb:flyway-database-postgresql:10.10.0")

    // Serialization for JSONB
    implementation("org.jetbrains.kotlinx:kotlinx-serialization-json:1.6.3")
}

Connecting with HikariCP #

import com.zaxxer.hikari.HikariConfig
import com.zaxxer.hikari.HikariDataSource

object DatabasePostgres {
    private val dataSource: HikariDataSource by lazy {
        val config = HikariConfig().apply {
            // Format: jdbc:postgresql://HOST:PORT/DATABASE
            jdbcUrl = buildString {
                val host = System.getenv("DB_HOST") ?: "localhost"
                val port = System.getenv("DB_PORT") ?: "5432"
                val db   = System.getenv("DB_NAME") ?: "myapp"
                append("jdbc:postgresql://$host:$port/$db")
                append("?sslmode=prefer")           // prefer SSL if available
                append("&currentSchema=public")     // default schema
                append("&ApplicationName=MyKotlinApp")  // app name for pg_stat_activity
            }
            driverClassName = "org.postgresql.Driver"
            username = System.getenv("DB_USER") ?: "postgres"
            password = System.getenv("DB_PASSWORD") ?: "postgres"

            // Pool configuration
            minimumIdle = 2
            maximumPoolSize = 10
            idleTimeout = 300_000
            connectionTimeout = 30_000
            maxLifetime = 1_800_000
            poolName = "PG-Pool"
            connectionTestQuery = "SELECT 1"

            // PostgreSQL-specific optimizations
            addDataSourceProperty("prepareThreshold", "5")       // prepared statement threshold
            addDataSourceProperty("preparedStatementCacheQueries", "256")
            addDataSourceProperty("cachePrepStmts", "true")
        }
        HikariDataSource(config)
    }

    fun <T> use(block: (java.sql.Connection) -> T): T =
        dataSource.connection.use(block)

    val ds get() = dataSource  // for Exposed

    fun close() { if (!dataSource.isClosed) dataSource.close() }
}

Distinctive PostgreSQL Data Types #

PostgreSQL has far richer data types than other databases. This is its main advantage:

-- Product schema with distinctive PostgreSQL types
CREATE TABLE produk (
    id              BIGSERIAL PRIMARY KEY,        -- auto-increment, like SERIAL but 8 bytes
    nama            TEXT NOT NULL,                -- TEXT with no length limit (vs VARCHAR)
    deskripsi       TEXT,
    harga           NUMERIC(15,2) NOT NULL,
    stok            INTEGER NOT NULL DEFAULT 0,
    kategori        TEXT,
    tag             TEXT[] DEFAULT '{}',          -- ARRAY of text
    metadata        JSONB,                        -- binary JSON, can be indexed and queried
    aktif           BOOLEAN NOT NULL DEFAULT TRUE, -- native BOOLEAN (not 0/1)
    dibuat_pada     TIMESTAMPTZ DEFAULT NOW(),    -- TIMESTAMP WITH TIMEZONE
    diperbarui_pada TIMESTAMPTZ DEFAULT NOW(),

    CONSTRAINT ck_harga_positif CHECK (harga >= 0),
    CONSTRAINT ck_stok_nn CHECK (stok >= 0)
);

-- Regular index
CREATE INDEX idx_produk_kategori ON produk(kategori);
CREATE INDEX idx_produk_aktif ON produk(aktif) WHERE aktif = TRUE;  -- partial index

-- Index for JSONB
CREATE INDEX idx_produk_metadata ON produk USING GIN(metadata);

-- Index for arrays
CREATE INDEX idx_produk_tag ON produk USING GIN(tag);

-- Trigger for timestamp updates
CREATE OR REPLACE FUNCTION update_diperbarui_pada()
RETURNS TRIGGER AS $$
BEGIN
    NEW.diperbarui_pada = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_produk_update
BEFORE UPDATE ON produk
FOR EACH ROW EXECUTE FUNCTION update_diperbarui_pada();

CRUD with JDBC #

import kotlinx.serialization.Serializable
import kotlinx.serialization.json.Json
import java.math.BigDecimal
import java.sql.Array
import java.sql.ResultSet
import java.sql.Types

@Serializable
data class ProductMetadata(
    val weight: Double? = null,
    val dimensions: String? = null,
    val warrantyYears: Int? = null,
    val originalBrand: String? = null
)

data class Product(
    val id: Long = 0,
    val name: String,
    val description: String? = null,
    val price: BigDecimal,
    val stock: Int = 0,
    val category: String? = null,
    val tags: List<String> = emptyList(),
    val metadata: ProductMetadata? = null,
    val active: Boolean = true
)

class PostgresProductRepository {

    private val json = Json { ignoreUnknownKeys = true }

    private fun ResultSet.toProduct(): Product {
        // Read a PostgreSQL array
        val tagArray = getArray("tag")
        val tagList = (tagArray?.array as? Array<String>)?.toList() ?: emptyList()

        // Read JSONB
        val metadataJson = getString("metadata")
        val metadata = metadataJson?.let {
            runCatching { json.decodeFromString<ProductMetadata>(it) }.getOrNull()
        }

        return Product(
            id        = getLong("id"),
            name      = getString("nama"),
            description = getString("deskripsi"),
            price     = getBigDecimal("harga"),
            stock     = getInt("stok"),
            category  = getString("kategori"),
            tags      = tagList,
            metadata  = metadata,
            active    = getBoolean("aktif")  // PostgreSQL BOOLEAN → Kotlin Boolean directly
        )
    }

    // INSERT
    fun save(product: Product): Product {
        val sql = """
            INSERT INTO produk (nama, deskripsi, harga, stok, kategori, tag, metadata, aktif)
            VALUES (?, ?, ?, ?, ?, ?, ?::jsonb, ?)
            RETURNING id
        """.trimIndent()

        return DatabasePostgres.use { connection ->
            connection.prepareStatement(sql).use { stmt ->
                stmt.setString(1, product.name)
                stmt.setString(2, product.description)
                stmt.setBigDecimal(3, product.price)
                stmt.setInt(4, product.stock)
                stmt.setString(5, product.category)

                // Set a PostgreSQL array
                val tagArray = connection.createArrayOf("text", product.tags.toTypedArray())
                stmt.setArray(6, tagArray)

                // Set JSONB — explicit cast with ::jsonb
                val metadataStr = product.metadata?.let { json.encodeToString(ProductMetadata.serializer(), it) }
                stmt.setString(7, metadataStr)

                stmt.setBoolean(8, product.active)

                stmt.executeQuery().use { rs ->
                    if (rs.next()) product.copy(id = rs.getLong(1))
                    else throw RuntimeException("Failed to get the generated ID")
                }
            }
        }
    }

    // SELECT with dynamic filters
    fun findAll(
        category: String? = null,
        tagFilter: String? = null,     // find products that have this tag
        maxPrice: BigDecimal? = null,
        activeOnly: Boolean = true,
        page: Int = 1,
        size: Int = 20
    ): List<Product> {
        val offset = (page - 1) * size
        val conditions = mutableListOf<String>()
        val params = mutableListOf<Any?>()

        if (activeOnly) conditions.add("aktif = TRUE")
        if (category != null) {
            conditions.add("kategori = ?")
            params.add(category)
        }
        if (tagFilter != null) {
            conditions.add("? = ANY(tag)")  // ANY to check elements in an array
            params.add(tagFilter)
        }
        if (maxPrice != null) {
            conditions.add("harga <= ?")
            params.add(maxPrice)
        }

        val where = if (conditions.isNotEmpty()) "WHERE ${conditions.joinToString(" AND ")}" else ""
        val sql = """
            SELECT * FROM produk
            $where
            ORDER BY nama
            LIMIT ? OFFSET ?
        """.trimIndent()

        return DatabasePostgres.use { connection ->
            connection.prepareStatement(sql).use { stmt ->
                var idx = 1
                params.forEach { p ->
                    when (p) {
                        is String -> stmt.setString(idx++, p)
                        is BigDecimal -> stmt.setBigDecimal(idx++, p)
                        else -> stmt.setObject(idx++, p)
                    }
                }
                stmt.setInt(idx++, size)
                stmt.setInt(idx, offset)

                stmt.executeQuery().use { rs ->
                    buildList { while (rs.next()) add(rs.toProduct()) }
                }
            }
        }
    }

    // UPSERT with ON CONFLICT DO UPDATE — a very useful PostgreSQL feature
    fun upsert(product: Product): Product {
        val sql = """
            INSERT INTO produk (nama, harga, stok, kategori, aktif)
            VALUES (?, ?, ?, ?, ?)
            ON CONFLICT (nama) DO UPDATE
            SET harga  = EXCLUDED.harga,
                stok   = EXCLUDED.stok,
                aktif  = EXCLUDED.aktif
            RETURNING id, nama, harga, stok, kategori, aktif
        """.trimIndent()

        return DatabasePostgres.use { connection ->
            connection.prepareStatement(sql).use { stmt ->
                stmt.setString(1, product.name)
                stmt.setBigDecimal(2, product.price)
                stmt.setInt(3, product.stock)
                stmt.setString(4, product.category)
                stmt.setBoolean(5, product.active)

                stmt.executeQuery().use { rs ->
                    if (rs.next()) {
                        product.copy(
                            id    = rs.getLong("id"),
                            name  = rs.getString("nama"),
                            price = rs.getBigDecimal("harga"),
                            stock = rs.getInt("stok")
                        )
                    } else product
                }
            }
        }
    }
}

Distinctive PostgreSQL Features #

JSONB Queries #

PostgreSQL allows querying directly into JSONB fields:

// Find products with a warranty longer than 1 year (from a JSONB field)
fun findByWarranty(minYears: Int): List<Product> {
    val sql = """
        SELECT * FROM produk
        WHERE aktif = TRUE
          AND (metadata->>'warrantyYears')::int >= ?
        ORDER BY nama
    """.trimIndent()

    return DatabasePostgres.use { connection ->
        connection.prepareStatement(sql).use { stmt ->
            stmt.setInt(1, minYears)
            stmt.executeQuery().use { rs ->
                buildList { while (rs.next()) add(rs.toProduct()) }
            }
        }
    }
}

// Partially update a JSONB field using jsonb_set
fun addMetadata(id: Long, key: String, value: String) {
    val sql = """
        UPDATE produk
        SET metadata = jsonb_set(
            COALESCE(metadata, '{}'::jsonb),
            ?::text[],    -- path as an array
            ?::jsonb      -- new value
        )
        WHERE id = ?
    """.trimIndent()

    DatabasePostgres.use { connection ->
        connection.prepareStatement(sql).use { stmt ->
            stmt.setString(1, "{$key}")   // path: {key}
            stmt.setString(2, "\"$value\"") // JSON value
            stmt.setLong(3, id)
            stmt.executeUpdate()
        }
    }
}

Array Operations #

// Add a tag to an existing product
fun addTag(id: Long, newTag: String) {
    val sql = """
        UPDATE produk
        SET tag = array_append(tag, ?)
        WHERE id = ? AND NOT (? = ANY(tag))  -- avoid duplicates
    """.trimIndent()

    DatabasePostgres.use { connection ->
        connection.prepareStatement(sql).use { stmt ->
            stmt.setString(1, newTag)
            stmt.setLong(2, id)
            stmt.setString(3, newTag)
            stmt.executeUpdate()
        }
    }
}

// Remove a tag from a product
fun removeTag(id: Long, tag: String) {
    val sql = "UPDATE produk SET tag = array_remove(tag, ?) WHERE id = ?"

    DatabasePostgres.use { connection ->
        connection.prepareStatement(sql).use { stmt ->
            stmt.setString(1, tag)
            stmt.setLong(2, id)
            stmt.executeUpdate()
        }
    }
}

Full-Text Search #

// Full-text search using tsvector and tsquery
fun fullTextSearch(queryText: String, limit: Int = 10): List<Product> {
    val sql = """
        SELECT *,
               ts_rank(
                   to_tsvector('indonesian', nama || ' ' || COALESCE(deskripsi, '')),
                   plainto_tsquery('indonesian', ?)
               ) AS rank
        FROM produk
        WHERE aktif = TRUE
          AND to_tsvector('indonesian', nama || ' ' || COALESCE(deskripsi, ''))
              @@ plainto_tsquery('indonesian', ?)
        ORDER BY rank DESC
        LIMIT ?
    """.trimIndent()

    return DatabasePostgres.use { connection ->
        connection.prepareStatement(sql).use { stmt ->
            stmt.setString(1, queryText)
            stmt.setString(2, queryText)
            stmt.setInt(3, limit)
            stmt.executeQuery().use { rs ->
                buildList { while (rs.next()) add(rs.toProduct()) }
            }
        }
    }
}

CTEs (Common Table Expressions) #

// Aggregation report with a CTE
fun reportByCategory(): List<Map<String, Any?>> {
    val sql = """
        WITH statistics AS (
            SELECT
                kategori,
                COUNT(*) AS jumlah_produk,
                SUM(stok) AS total_stok,
                AVG(harga) AS rata_harga,
                MIN(harga) AS harga_termurah,
                MAX(harga) AS harga_termahal
            FROM produk
            WHERE aktif = TRUE
            GROUP BY kategori
        )
        SELECT *
        FROM statistics
        ORDER BY jumlah_produk DESC
    """.trimIndent()

    return DatabasePostgres.use { connection ->
        connection.createStatement().use { stmt ->
            stmt.executeQuery(sql).use { rs ->
                buildList {
                    while (rs.next()) {
                        add(mapOf(
                            "category"     to rs.getString("kategori"),
                            "productCount" to rs.getInt("jumlah_produk"),
                            "totalStock"   to rs.getInt("total_stok"),
                            "avgPrice"     to rs.getBigDecimal("rata_harga"),
                            "cheapestPrice" to rs.getBigDecimal("harga_termurah"),
                            "mostExpensivePrice" to rs.getBigDecimal("harga_termahal")
                        ))
                    }
                }
            }
        }
    }
}

Exposed ORM with PostgreSQL #

Exposed supports PostgreSQL very well, including distinctive types like JSONB and arrays:

import org.jetbrains.exposed.sql.*
import org.jetbrains.exposed.sql.json.jsonb
import org.jetbrains.exposed.sql.transactions.transaction
import org.jetbrains.exposed.dao.*
import org.jetbrains.exposed.dao.id.*

fun initializeExposedPostgres() {
    Database.connect(DatabasePostgres.ds)

    transaction {
        SchemaUtils.create(PgProductTable)
    }
}

// Table definition with PostgreSQL types
object PgProductTable : LongIdTable("produk") {
    val name      = text("nama")
    val description = text("deskripsi").nullable()
    val price     = decimal("harga", 15, 2)
    val stock     = integer("stok").default(0)
    val category  = text("kategori").nullable()
    val active    = bool("aktif").default(true)
    val createdAt = kotlinx.datetime.Instant::class.let {
        // Use a timestamp with timezone
        varchar("dibuat_pada", 50).default(org.jetbrains.exposed.sql.javatime.CurrentTimestamp.toString())
    }
}

// CRUD with Exposed
fun addProductExposed(name: String, price: java.math.BigDecimal, stock: Int): Long {
    return transaction {
        PgProductTable.insertAndGetId {
            it[PgProductTable.name]  = name
            it[PgProductTable.price] = price
            it[PgProductTable.stock] = stock
        }.value
    }
}

fun findProductsExposed(category: String? = null): List<ResultRow> {
    return transaction {
        PgProductTable
            .select { PgProductTable.active eq true }
            .apply {
                if (category != null) {
                    andWhere { PgProductTable.category eq category }
                }
            }
            .orderBy(PgProductTable.name)
            .toList()
    }
}

Migrations with Flyway #

import org.flywaydb.core.Flyway

fun runPostgresMigrations() {
    val flyway = Flyway.configure()
        .dataSource(DatabasePostgres.ds)
        .locations("classpath:db/migration/postgres")
        .defaultSchema("public")
        .baselineOnMigrate(true)
        .validateOnMigrate(true)
        .load()

    val result = flyway.migrate()
    println("PostgreSQL Migration: ${result.migrationsExecuted} migrations executed")
}

PostgreSQL migration files:

-- V1__create_produk.sql
CREATE TABLE IF NOT EXISTS produk (
    id              BIGSERIAL PRIMARY KEY,
    nama            TEXT NOT NULL,
    deskripsi       TEXT,
    harga           NUMERIC(15,2) NOT NULL CHECK (harga >= 0),
    stok            INTEGER NOT NULL DEFAULT 0 CHECK (stok >= 0),
    kategori        TEXT,
    tag             TEXT[] DEFAULT '{}',
    metadata        JSONB,
    aktif           BOOLEAN NOT NULL DEFAULT TRUE,
    dibuat_pada     TIMESTAMPTZ DEFAULT NOW(),
    diperbarui_pada TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_produk_kategori ON produk(kategori);
CREATE INDEX IF NOT EXISTS idx_produk_aktif ON produk(aktif) WHERE aktif = TRUE;
CREATE INDEX IF NOT EXISTS idx_produk_tag ON produk USING GIN(tag);
CREATE INDEX IF NOT EXISTS idx_produk_metadata ON produk USING GIN(metadata);
CREATE INDEX IF NOT EXISTS idx_produk_fts ON produk
    USING GIN(to_tsvector('indonesian', nama || ' ' || COALESCE(deskripsi, '')));

-- V2__add_update_trigger.sql
CREATE OR REPLACE FUNCTION update_diperbarui_pada()
RETURNS TRIGGER AS $$
BEGIN
    NEW.diperbarui_pada = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_produk_update
BEFORE UPDATE ON produk
FOR EACH ROW EXECUTE FUNCTION update_diperbarui_pada();

PostgreSQL Performance Tips #

// 1. EXPLAIN ANALYZE for query analysis
fun analyzeQuery(sql: String) {
    DatabasePostgres.use { connection ->
        connection.createStatement().use { stmt ->
            stmt.executeQuery("EXPLAIN ANALYZE $sql").use { rs ->
                while (rs.next()) println(rs.getString(1))
            }
        }
    }
}

// 2. Connection pool sizing — a common formula:
// maxPoolSize = (number_of_cores * 2) + number_of_disk_spindles
// For PostgreSQL, 10-20 connections per application is usually enough

// 3. Prepared statement caching — PostgreSQL caches execution plans
// after being called prepareThreshold times (default 5)
// addDataSourceProperty("prepareThreshold", "5")

// 4. COPY for bulk inserts (faster than batch INSERT)
fun bulkInsert(products: List<Product>) {
    val copySQL = "COPY produk (nama, harga, stok, kategori) FROM STDIN WITH CSV"

    DatabasePostgres.use { connection ->
        val pgConn = connection.unwrap(org.postgresql.PGConnection::class.java)
        val copyManager = pgConn.copyAPI

        val csvData = buildString {
            products.forEach { p ->
                appendLine("${p.name},${p.price},${p.stock},${p.category ?: ""}")
            }
        }

        copyManager.copyIn(copySQL, java.io.StringReader(csvData))
        println("Successfully inserted ${products.size} products via COPY")
    }
}

Summary #

PostgreSQL is the best choice for new Kotlin projects — open-source, feature-rich, supports native JSON, arrays, full-text search, and stricter SQL standards than MySQL.
BIGSERIAL or GENERATED ALWAYS AS IDENTITY — both are valid for auto-increment. SERIAL uses a sequence behind the scenes; IDENTITY is the more modern SQL standard.
Native BOOLEAN, TIMESTAMPTZ, TEXT — no need for TINYINT(1) for booleans, DATETIME without timezone, or worrying about VARCHAR lengths. PostgreSQL has more precise types.
JSONB not JSON — always use JSONB (binary, indexable, queryable) instead of JSON (plain text). JSONB is slower on insert but much faster on query.
ON CONFLICT DO UPDATE for upserts — PostgreSQL’s upsert feature is very expressive: specify the conflict columns and which columns to update on conflict. Cleaner than MySQL’s INSERT IGNORE or REPLACE.
GIN indexes for JSONB and arrays — use CREATE INDEX ... USING GIN for JSONB fields and arrays so @>, ?, and ANY() queries stay fast with large data.
Native full-text search — PostgreSQL has an excellent full-text search system with to_tsvector and to_tsquery. No Elasticsearch needed for basic search.
COPY for bulk inserts — for inserting thousands of rows at once, COPY FROM STDIN is the fastest way — far more efficient than batch INSERT.

← Previous: Oracle Next: MongoDB →